Category Archives: Security

Question #2: Trigger on One Table To Insert Data into Another

Question #2 from the September MySQL User Group was whether or not a TRIGGER can affect a different table. Apparently the documentation (perhaps for an earlier version??) specified this was not possible. Tom Hanlon, MySQL employee, put up this example (modified from the original, special thanks to Ralph Navarro for copying it down):

Basically, this trigger will insert the current user and timestamp into another table.

mysql> delimiter $$
mysql> CREATE TRIGGER BIcity BEFORE INSERT ON city
-> FOR EACH ROW BEGIN
-> INSERT INTO citytest (name,happened) values (current_user(),now());
-> END;
-> $$
Query OK, 0 rows affected (0.03 sec)

mysql> delimiter ;
mysql> create table citytest (
name varchar(60) not null default '',
happened datetime not null);
Query OK, 0 rows affected (0.19 sec)

mysql> describe city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql> insert into city (Name) values ('tomtown');
Query OK, 1 row affected (0.02 sec)

mysql> select * from citytest\G
*************************** 1. row ***************************
name: root@localhost
happened: 2006-09-07 21:45:14
1 row in set (0.01 sec)

Performance Question #1

I promised to write this up for for the folks who attended the Boston MySQL September User Group meeting, so here’s performance question #1 that was asked:

How can a bulk insert be speeded up?
We discussed disabling keys with

ALTER TABLE tblname DISABLE KEYS;
loading the data
ALTER TABLE tblname ENABLE KEYS;

However, as a post by Frank Mash not too long ago and comments explain, this has no effect on InnoDB tables.

For InnoDB tables, you can load the data in primary key order, which makes the loading much faster. Basically, InnoDB stores the data in primary key order on disk. If there is no primary key specified, the internal engine makes one anyway, so you might as well specify one and take advantage of it.

As well, you can SET UNIQUE CHECKS=0 before the load and SET UNIQUE CHECK=1 after the load if there are unique constraints. The final suggestion is to SET AUTOCOMMIT=0 before the load and SET AUTOCOMMIT=1 after the load, again to speed things up.

FREE MySQL Performance Help, Food and Networking — TOMORROW: Thu Sept. 7 2006 7:00 pm, Cambridge MA

What: Free MySQL help with Tom Hanlon, MySQL employee.
at the Boston MySQL User Group
When: Thursday, September 7, 2006, 7:00 PM
Where: MIT Building E51, Room 372
Wadsworth and Amherst Streets
Cambridge , MA 02117
Cost: Free
Transportation: 1 block from Kendall Square T station;
free parking (MIT does not enforce their lot restrictions in the evenings,
so any signs except handicapped parking can safely be ignored)
RSVP: Free pizza and soda will be served, so please RSVP accurately.
To RSVP anonymously, go to http://www.meetup.com/login/ ,
login with the e-mail address “admin at sheeri dot com”
and the password “guest”, then click on “Add Guests”
and add 1 to the number of guests.

The September Boston MySQL User Group Meeting will feature Tom Hanlon, MySQL employee, answering questions about performance issues (or anything else). Please feel free to bring theoretical questions as well as actual issues you’ve been having.

If you have a specific question, please bring descriptions of all relevant queries, tables, data samples, etc if you have a complex question. (see ********** below for more details)

You may submit a question to awfief@gmail.com or just bring it to the user group meeting.

We will be meeting on MIT campus, close to the Kendall stop on the Red Line (subway). There is also plenty of free parking — you can park in ANY MIT lot after 3 pm, even if it says “parking by permit only”. We are in building E51, room 372.

Here is the URL for the MIT Map with the location of this building:
http://whereis.mit.edu/map-jpg?selection=E51&Buildings=go

This map shows the MBTA Kendall Stop:
http://whereis.mit.edu/map-jpg?selection=L5&Landmarks=go
(the stop is in red on that map, and you can see E51 in the bottom right)

Here are the URL’s for the parking lots:
http://whereis.mit.edu/map-jpg?selection=P4&Parking=go
http://whereis.mit.edu/map-jpg?selection=P5&Parking=go

Free pizza and soda will be served, so please RSVP accurately.

To RSVP anonymously, please login to the Meetup site with the e-mail address “admin at sheeri dot com” and the password “guest”.

More information:

http://mysql.meetup.com/137/events/4976426/

**********
What to bring:
If you are submitting materials, you must submit your materials by noon the day of the user group meeting.
1) Either submit materials to Sheeri at awfief@gmail.com ahead of time or bring them on your laptop and be prepared to connect to the projector (we have the cables, just bring your laptop). Alternatively, you can make overhead projector slides and bring those.

2) Descriptions of relevant tables. Run the following for each table and bring the output:
SHOW CREATE TABLE tbl1\G
SHOW CREATE TABLE tbl2\G

3) Sample data for relevant tables. Run the following for each table and bring the output:
SELECT * FROM tbl1 ORDER BY RAND() LIMIT 5;
SELECT * FROM tbl2 ORDER BY RAND() LIMIT 5;

4) Query descriptions. Run the following for each query and bring the output:
The actual query, ie, “SELECT name FROM addresses WHERE city=’Boston’;”
The EXPLAIN output for the query, ie, EXPLAIN SELECT name FROM addresses WHERE city='Boston';
What you expect to get (data if the issue is inaccurate results, or a time estimate if the issue is slowness)
What you actually get (data if the issue is inaccurate results, or a time estimate if the issue is slowness)

Awkward JDBC API and MySQL User Group Meeting

Life has been super busy, but I have plenty of posting to catch up on. Fear not, there will be more content soon (after Monday, hopefully); I am working on editing a very relevant book, and I hope to be able to share my excitement after I finish.

Also on Monday is the next Boston MySQL User Group, which will go through how to do capacity planning in MySQL with very little pain and effort. In the beginning we will have 10 minutes for user questions, so we can all benefit from each other’s knowledge. I already have a user with a great question!

We have rebuilding our product all summer, with a deadline of releasing the product in the next 2 months. Our lead developer had put a surrogate key in our new schema about a month ago. He said he needed the surrogate key because “the ORM requires it.” I just accepted it.

My mistake was that I made an assumption. The table already had a primary key, but it was a composite key. I assumed that the ORM required a unique key to be one field, and thus I assumed he combined the 2 values in the primary key to get the unique key.

I was wrong. In adding the final subsystems into the schema this week, I noticed that the surrogate key was an auto-increment field. I also noticed he put it in every single table he dealt with. We had hours of meetings about schema, and this was NEVER put in as a requirement. Thus, today we had over three hours of agonizing discussions, including a back-and-forth of “If it’s going into the database I need to understand why,” and the lead developer saying “No you don’t.”

I severely wanted to say “If I don’t understand it, it does not go in the database,” but resisted. I asked him to send me the ORM so I could understand why it required the field. At first he said he would, and then kept talking to me about why I don’t need to understand the field; it didn’t add more overhead, it didn’t change the way the data relate to each other, etc.

I need to understand this because there may be other, similar requirements in the future. Or perhaps I’ll find a better way to do it (maybe a VIEW would work). Perhaps I’ll find other places where other fields need to be added. He finally explained that the API JDBC was using was awkward — it needs to retrieve basically the row number of any row it’s looking at, and if it deletes or changes the row number it uses the row number as the key to find the row.

Aha! That makes sense. However, why do the row numbers need to be in the database? Can’t it just retrieve the rows and put a row number in its own copy? Apparently, not. I cannot imagine that a mature technology would require something like that. It’s not that difficult to do. I said this, and the lead developer was insanely frustrated by it.

So I said, “Are you using Connector/J?” He was confused, but asked, “Is that JDBC?”

“Yes,” I replied. “Oh, then yes, we’re using it.”

“I don’t think so. If the interface is awkward, you’re not using Connector/J.”

He left my office. So I type in “Connector/J” into my MySQL Community Toolbar (I love it!) and find the following on http://www.mysql.com/products/connector/j/

New features from the JDBC-3.0 API in the latest production version of MySQL Connector/J include getGeneratedKeys which allows users to retrieve auto-increment fields in a non-database-specific way. Auto-increment fields now work with object-relational mapping tools, as well as Enterprise Java Beans (EJB) servers with Container Managed Persistence (CMP) that support JDBC-3.0.

Hrm….retrieve auto-increment fields in a non-database-specific way? I think that solves our problem!!!

[EDIT: I am, apparently wrong….but I cannot imagine that anyone using JDBC specifies an auto-increment field for EVERY SINGLE TABLE their application will touch. Do people actually do this?!?!?]

Jim Starkey Speaks, July Boston MySQL User Group Meeting

Please feel free to forward to interested parties.

Who: Jim Starkey at the Boston MySQL User Group
What: Falcon, the new MySQL storage engine
When:
Monday, July 10, 2006 at 7:00 PM
Where:
MIT Building E51, Room 372
Wadsworth and Amherst Streets
Cambridge, MA 02117
Steps from the Red line, plenty of free parking.

The July Boston MySQL User Group’s topic is Falcon, the new storage engine for MySQL. Creator Jim Starkey will speak. Jim Starkey has been writing database software for 20 years. He created BLOBs, multi-versioning concurrency for relational databases, cascading update triggers, event alerters, and more. Read more about him at http://tinyurl.com/lno4p and http://tinyurl.com/mym7d.

We will be meeting on MIT campus, close to the Kendall stop on the Red Line (subway). There is also plenty of free parking — you can park in ANY MIT lot after 3 pm, even if it says “parking by permit only”. We are in building E51, room 372.

Here is the URL for the MIT Map with the location of this building:
http://whereis.mit.edu/map-jpg?selection=E51&Buildings=go

This map shows the MBTA Kendall Stop:
http://whereis.mit.edu/map-jpg?selection=L5&Landmarks=go
(the stop is in red on that map, and you can see E51 in the bottom right)

Here are the URL’s for the parking lots:
http://whereis.mit.edu/map-jpg?selection=P4&Parking=go
http://whereis.mit.edu/map-jpg?selection=P5&Parking=go

Free pizza and soda will be served, so please RSVP accurately and arrive a few minutes early.

To RSVP anonymously, please login to the Meetup site (http://mysql.meetup.com/137) with the e-mail address “admin at sheeri dot com” and the password of “guest”.

Sponsors welcome, call Sheeri at 857-205-9786 for details.

How I have a successful MySQL User Group

Note the title is “How I have a successful MySQL User Group.” There’s more than one way to do it, I’m sure. There are 3 basic principles:

1) Try to do as little work as possible.

2) Make your colleagues do as little work as possible.

3) Always have a topic/presentation

These three principles will get you far, and should be weighted equally. Do not use principle # 1 as an excuse to not follow principle #3. As well, “doing work” includes “paying money”. With that being said:

  1. Make your user group easy to get to. This has different meanings for different areas. It may mean near a major highway interchange, it may mean near a mass transit station. Whatever it means for you, make it easy.
  2. When the Boston MySQL User Group first started, we had free space in an office building right in the city of Boston.

    Pros Cons
    Free Not enough parking
    Close to the subway and train station No free parking
    The street was clearly labeled We had to have a person stand by the door to let people in
    The building was clearly labeled
    There was a great pub next door

    Then we moved to a space at MIT:

    Pros Cons
    Free Passers-by eat the pizza and drink the soda
    Close to the subway and train station We had to have good maps to find the location
    Plenty of free parking

    Now, you may not be so lucky to find a place that will give you space for free. Consider local universities. Also, local libraries (university or otherwise) usually have some meeting space where they might be able to host you. Our first space, an office space, was gotten through a contact at MySQL. Contact some companies who use MySQL and ask if they’ll lend you space — most tech companies have folks there at night anyway, and it’s free advertising for the company! There is a book company near you, or a Pearson VUE center. Remember: it does not hurt to ask. The worst people can say is “no”, and if it’s contributing toward education, a book company (Pearson Education, Apress, O’Reilly, etc) might sponsor it.

    Getting a free space is key, particularly if it has A/V equipment for you to use (if you want to have lectures).

    Granted, if you want to just have a freeform discussion, any pub or coffee shop or location will do.

    You may think about having it in your home. Consider issues of personal safety, domestic distractions (kids, pets, etc), parking and transit, and the fact that most people feel comfortable going to their first meeting on “neutral” ground. However, if the culture surrounding where you live encourages it, go for it!

    If you must pay for a location, get a company to sponsor it. Get their monetary and time commitment in writing if you can (ie, they will sponsor for a year).

  3. Make the meetings easy to remember. Have them on the same day of the month (ie, 2nd Monday). Do not make them conflict with something similar (ie, PHP group, linux group). Try to have it at the same place each time.
  4. Figure out what your group wants and give it to them.
  5. Do they want lectures on specific topics? Maybe go to a place with wireless access and a “troubleshooting” meeting every so often. Maybe they want a blog with news, or war stories. Maybe a lending library would work. Anyone that comes to your meeting is looking for something. Ask each new member what they’re looking for. If they say “to learn more” then go with lectures — make sure to accomodate all skill sets, and have advanced lectures as well as beginning lectures. If they want to get to know each other better, go to a pub and talk about MySQL while you down a pint. Or run a charity fundraiser and donate the proceeds to a not-for-profit like the Electronic Freedom Foundation. There’s plenty the group can do, including having contests, or even starting a business together if you’re plucky.

  6. Have incentives.
  7. But remember rule #2, so keep costs as low as possible. Ask companies to sponsor dinner or light refreshments. Don’t be shy; ask book companies for books, T-shirts and buttons to give away as prizes. Again, the worst they can say is “no”, and I’ve found book companies and MySQL AB to be VERY accomodating, as well as local businesses. Ask company techies to present. Ask MySQL to send someone out to present.

  8. Be available, and follow up when you say you will. Often times folks will have a question. Being able to respond in a timely manner is important. But if you speak out and make regular annoucements, and solicit feedback, folks will know you are accomodating.
  9. Do not do work others can do. When someone asks if you can forward a job listing, have them post it to your group members. Or, better yet, have them come to your meeting and make an announcement in the first 5 minutes. This includes advertising! Find your local MySQL Sales Rep and ask them to help promote your group. Promote it on any site you can think of — in a blog, on Craigslist, heck, make flyers and post it around town if you have to. Get the word out there!
  10. Have the right attitude. It’s not your meeting, it’s your group’s meeting. If they want different topics, ask someone to research and present. It’s OK if the research isn’t perfect or complete; many times your group will fill in with their experiences.
  11. It’s OK to be wrong. No, really. If you don’t know the answer to a question, look it up later on, and follow up. Or ask the group if anyone knows. Also, presentations take time to make, and if you feel compelled to get every single detail right, they’ll take a lot longer. Keep an offline copy of the manual so you can look up anything you need to.
  12. Ask others for help. Even if you think you can do it better or faster. Your group should be able to survive without you, so if you can’t make a meeting all is not lost. This includes asking others to do a presentation of any length — it can be a 10 minute “this is how we use MySQL and this is what our setup looks like” to an hour or more. It does not have to be intimidating. Ask folks to present workshops they’re presenting at conferences, or present notes from conferences they go to. People need to feel like part of the group if the group is to be successful.
  13. If you can’t get a presentation, download one. MySQL is excellent at providing past webinars. Download it to your computer, and have a free lecture from an expert, that you can pause and rewind if you need to. MySQL Webinars. The Boston MySQL User Group videorecords the presentations and puts them online — see the links under “Presentations” at http://www.sheeri.net

Note: The Boston MySQL User Group is successful, but we don’t quite do everything above. I’d love to have more socialization, and I think once summer comes I’ll think of something geeky and fun to do outdoors, with little cost. Or maybe just a bowling trip.

Boston MySQL User Group a Success!

Yet again, the Boston MySQL User Group was a success. Larry Stefonic, Senior Vice President, Worldwide OEM & Embedded Sales and President, MySQL K.K. (Japan) was on hand to witness the event. There were 15 people total at the user group, which is our lowest yet, but I’ll get to why I was not disappointed at all later on.

However, the topic was advanced: “Measuring MySQL Server Performance for the Sensor Data Stream Processing” presented by faithful MySQL User Group attendee Jacob Nikom, from MIT Lincoln Labs. (he’s been to every user group meeting!)

And I was very impressed with the questions folks asked of Jacob — the group was half the size of what we usually are, but we were all fully engaged. I’m very proud of the MySQL group! We have a diverse range of skills and I’m glad we can accomodate all of them.

Next month’s topic will be “Storing images in a database,” which was pushed out of the way in April to make room for Jay Pipes’ wonderful presentation. As well, we hope to have a special User Group before then with an extra special MySQL guest. More details on that when they happen.

MySQL Users Group BOF

Do you have a users group near you? Is it thriving? In conjunction with Mike Kruckenberg, I’ve been running the Boston MySQL Users Group for 6 months. Every meeting draws 30-60 members, and members overall are quite satisfied.

How do we do it? If you’re at the MySQL Users Conference, come find out at the MySQL Users Group BOF to get tips and tricks, or to share your own success stories and pitfalls. 8:30 -9:30 pm, Tuesday April 25th.

Boston MySQL User Group

Jay Pipes gave an amazing tutorial on optimizing performance in MySQL by choosing your indexes and joins well. There were plenty of questions from the audience which he handled well, and in style. Over 50 people attended! And I really feel as though everyone learned a lot. The tutorial ran for 3 hours, but we were engaged until the last, and there were 15-20 people even at the bitter end.

Thanx, Jay! I can’t wait to see how the tutorial goes over at the MySQL Users Conference.

Pictures, video, slides and presentations will be up when we get to it.

So You’ve Inherited a MySQL Instance

The first hour of a 2-hour workshop on what to do with a MySQL instance you’ve inherited. Very detailed, goes into real examples using shell prompts.

Unfortunately, the settings were incorrect on the video recorder, so only the 1st hour was captured. Presented at the Boston MySQL User Group on February 13th, 2006.

Special thanks to Tom C. for securing the MIT space.

google video

flash slides

pdf slides (this might be broken, please let me know)

worksheet

As always, stylistic criticism as well as content criticism is appreciated.