Sheeri Dot Org

It has been over a year since the last OurSQL podcast. First, I want to thank everyone who has written in to tell me how much they loved the OurSQL podcast, and how they want it back. Plans are in the works for that, mostly I got busy writing a book on MySQL geared towards folks who are new to MySQL, but not necessarily new to databases. The book is coming out in May and can be pre-ordered at http://tinyurl.com/mysqlbook.

But enough about the past…..In this first Drizzle podcast, Jay Pipes and I talk about what Drizzle is and how Drizzle is different from MySQL both technically and from a community standpoint.

The podcast can be downloaded (5.76 Mb as an mp3 file) or played right through your browser at http://technocation.org/content/drizzle-podcast-%25231. The show notes are also on that page.

I have been talking more and more with colleagues about the Open Source community and licenses. Zak Greant recently wrote in Free Culture vs. Fear Culture vs. Fee Culture that, “People with bad intentions will do bad things . . . often regardless of the license on the work.”

And, unfortunately, he is right. If I release an article or presentation video with a Creative Commons license, it is still possible for my work to be plagiarized, and if it is, I will still feel violated.

Many of us who use Creative Commons or MySQL have an Open Source mentality. We often do not see value in pirating software—why would we use Microsoft Word (a legally licensed copy, or pirated) if we can use OpenOffice or NeoOffice?

In the same manner as Steal This Book, we rebel. But instead of stealing, we make things with less restrictive licenses, so that we can give them away for free, and so that people don’t bear the stigma of having to steal to get what they should rightfully have (good, accessible software). We encourage feed aggregators such as planetmysql to re-use our content.

Of course, we believe that just because we are “long-haired sandal-wearing hippies”, everyone else involved in Open Source is, too. Much as the preface to Steal This Book asserts, we believe:

Our moral dictionary says no heisting from each other. To steal from a brother or sister is evil.

So it surprises me when I encounter people in an Open Source community who have, in fact, stolen from others in their own community. It’s so easy to not steal, that I am amazed that there are people who actually go out of their way to steal on purpose.

According to Wikipedia, “Plagiarism is the use or close imitation of the language and ideas of another author and representation of them as one’s own original work.” For example, had I not quoted Wikipedia as the source of the definition, and just stated the definition without any citation, I would have committed an act of plagiarism.

Had I done the same with the MySQL Manual, I would be guilty also of plagiarism and copyright infringement, because the MySQL Manual is copyrighted; permission to use the material in the MySQL Manual must be granted.

Arjen Lentz wrote an article about MySQL AB’s expected employee principles, and asked what people would add to the list. Included on the list is, “Aim to be good citizens.”

Good citizens do not steal or plagiarize. Good citizens treat others how they themselves want to be treated, and this goes beyond stealing and plagiarism—if someone hurts or maligns him or her, good citizens will keep on doing what they’re doing. Hate and vengeance have no place in their hearts. To (probably misquote) Sun Tzu: “Never explain. Your friends do not need it, and your enemies will not believe it anyway.”

The points I am trying to drive home are that a good citizen is not merely someone who adds value to the community; a good citizen also does not detract from the community by stealing, plagiarizing, or spreading hate.

As many of us know, the 5th annual MySQL Conference and Expo is happening April 20-23rd, 2009 in Santa Clara, California. The theme is Innovation Everywhere, and this year the conference organizers have taken an innovative page from OSCon and decided to host a free “camp” during the conference.

As far as I know, MySQL Camp is the only free, non-commercial programming occurring. We already have a fantastic lineup of speakers and last week I was surprised with another bounty — MySQL Camp has been extended to Thursday!

While you are making your travel and lodging arrangements, remember that on Sunday April 19th on the mezzanine of the conference hotel there will be a Games Day from 12 noon – 12 midnight. Stop by, play a game (bring your own if you want), or just watch. Find a dinner buddy, meet up with old friends or just start meeting new people before the conference starts on Monday. If you want to find me, I will be the one who’s trying to knit and play a game at the same time.

The speakers and MySQL Camp sessions are listed at http://forge.mysql.com/wiki/MySQLCamp2009Sessions. If that’s too long to remember, http://tinyurl.com/mysqlcamp2009 is a custom alias that goes there.

I am excited to be running MySQL Camp, but I will not be spending the entire time at Camp — I am speaking at the conference as well! I will be presenting:

Together with Patrick Galbraith – a Monday tutorial, “Understanding How MySQL Works by Understanding Metadata”. The description we wrote sums it up best: “You don’t have to read source code — understanding how MySQL works can be achieved through the system data. Learn a wide range of topics in this 3-hour tutorial, and leave armed with tons of knowledge about how MySQL 5.0, 5.1 and 6.0 work. You will take home a healthy understanding of performance tuning, storage engines, replication and many tips and tricks to help you be a better DBA.”

On Tuesday, April 21st at 11:55 am, Laine Campbell and I will present “Agile Environments and DBAs”, which is a summary of the tips and tricks we have learned on how to be a good DBA in an Agile environment, where frequent code changes often require frequent schema and data changes.

And finally, on Wednesday April 22nd I will present “Connect and Replicate Securely: How to Use MySQL with SSL”. This session will show you how to set up SSL connectivity with MySQL, and how to use an encrypted connection, including how to replicate using an encrypted connection.

Feel free to comment with any feedback; and if you see me at the MySQL Conference or MySQL Camp, say “hi”, sit down at the lunch table with me, and give me your opinions in person.

Slaves can be used for:

1. Horizontal read scalability — take the load off a master database by spreading reads to a replicated slave.

2. Disaster recovery — some disasters, such as a hardware failure, can be solved by having a slave ready to propagate to a master. This technique also works to make offline changes to a master/slave pair without having database downtime (see below).

3. Consistent Backups — without disrupting production usage, a slave can be used to take a consistent backup by stopping the database and copying the database files. This is a free and uncomplicated way to get consistent backups (innodb hot backup is not free, and using a snapshotting tool (such as LVM’s snapshot capability) can be complex. Not everyone wants to manage snapshots.)
   

Be careful when using a slave for more than one purpose. Using a slave for more than one purpose can be done, but carefully. For example:

If one slave instance serves the needs of 1 and 2 — Where do the reads go when the slave is promoted to a master (or taken offline for maintenance)?

If one slave instance serves the needs of 1 and 3 — can the read slave be down for the length of time it takes to take a backup? If not, this solution is not appropriate.

If one slave instance serves the needs of 2 and 3 — Where does the backup get taken from when the slave is promoted to master (or taken offline for maintenance)?

There are certainly workarounds. We either create a propagation procedure (whether for maintenance or in an emergency) or assess an existing one, to cover these cases to make sure any dual-use of a slave does not leave a client hanging. We regularly test whether slaves are in sync, to ensure accuracy no matter what purposes replication slaves are used for. We also go through actual slave-to-master propagations. This can be useful as a drill, and can also be useful to avoid downtime.

Avoiding Downtime with Replication

In many cases, using replication can avoid downtime during an offline database change (for example, an OPTIMIZE TABLE to defragment a table). To implement this, the slave is taken out of service, the change performed on the slave, the slave is put back into service and propagated to be the master. Then the previous master is taken offline, the change applied to the previous master, and the previous master can return to service (either as a slave of the current master, or by propagating the previous master to be the current master.

• Consistent backup:
  A consistent backup is one that represents a snapshot of all data at a point in time. Consistent backups are used for disaster recovery. An inconsistent backup can be useful for retrieving partial data — for example, if a long-time customer accidentally deletes information from his profile, you can go back to an inconsistent backup and retrieve that information. It is not important that all the data be consistent with each other when retrieving a partial amount of point-in-time data.

• Logical backups:
  Logical backups are backups that contain SQL statements to create and populate tables. In MySQL, logical backups can only be done while mysqld is running, and are usually done with the mysqldump tool. mysqldump can logically export (”dump”) multiple databases, tables, views, stored routines, triggers and events. It can also export partial tables using the –where option to filter out rows. mysqldump will always output all the columns in a table.

  SELECT ... INTO OUTFILE can also be used for logical backups, but that’s used less frequently. SELECT ... INTO OUTFILE does not back up table structure, nor anything else that cannot be retrieved by a SELECT statement (ie, view schema, stored routine definitions, etc). SELECT ... INTO OUTFILE is used for backing up specific columns in a table.

  Logical backups can be used with a version control system to be able to audit approximately when changes occurred.

• Physical backups:
  Physical backups (also called raw backups) are backups that contain the physical files. These are usually done by using a file copy utility (such as scp or rsync).

• Cold backups:
  Cold backups are done when mysqld is shut down. Currently only physical backups can be done as a cold backup (logical backups cannot be done as a cold backup).

• Hot backups:
  Hot backups are done when mysqld is running, with minimal impact. An inconsistent logical backup with mysqldump is an example of a hot backup.

• Warm backups:
  Warm backups are done when mysqld is running, but there may be major impact. For example, to get a consistent, warm backup it is necessary to ensure that tables are not written to, so for the duration of the backup a shared (read) lock will be put on all tables. Writes (including replication) will not be able to occur during the backup window, but reads can continue as usual.

  mysqld is still running (so it’s not a cold backup) but there is impact to the database (so it’s not a hot backup).

If you are using InnoDB Hot Backup and a recent version of mysqld (at least 5.0.67 or higher, including 5.1.30, though it may be later versions), your backup will run fine and output OK! at the end, as it should.

Except for one thing.

The binary log file and position do not appear in their rightful place. Here’s a snippet of the output from the backup:

innobackup: MySQL binlog position: filename 'Warning', position (Code 1287):
'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead
090210 03:55:04  innobackup: innobackup completed OK!

That’s pretty misleading — looks like the backup completed OK, but it did not show us the binary log position. What about the ibbackup_binlog_info file?

[mysql@db3:~] more ibbackup_binlog_info
Warning (Code 1287): 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead
[mysql@db3:~]

Yikes! What happened to the binary log position and file information?

Have no fear, it’s actually in another file.

I was extremely happy to find the information in mysql-stdout:

[mysql@db3:~] more mysql-stdout
innobackup hello 3
innobackup hello 3
innobackup hello 4
innobackup hello 4
innobackup hello 5
innobackup hello 5
Note (Code 1051): Unknown table 'ibbackup_binlog_marker'
innobackup hello 6
innobackup hello 6
Warning (Code 1287): 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine'
instead
innobackup hello 7
innobackup hello 7
innobackup hello 8
innobackup hello 8
innobackup hello 9
innobackup hello 9
innobackup hello 10
innobackup hello 10
innobackup hello 11
innobackup hello 11
File    Position        Binlog_Do_DB    Binlog_Ignore_DB
db2-binlog.001980       495985099
innobackup hello 12
innobackup hello 12
innobackup hello 13
innobackup hello 13
innobackup hello 14
innobackup hello 14

I filed a bug report here:
http://bugs.mysql.com/bug.php?id=42812

The bug is an artifact of the eventual deprecation of TYPE in favor of ENGINE. There’s no reason for ibbackup to continue to use TYPE; while the product works well, it is much more expensive than it warrants.

(While I searched for this bug before creating it, I would like to say that it’s completely possible that this is “well-known” and “well-documented”, and I did not find it. Or, that this client is using an old version of ibbackup. However, there may be many folks in this same position, and this is a very subtle bug — the backup looks fine, it’s a full backup, it will restore properly, etc. but if you want to start replicating the backed up server, you will have a hard time finding the binary log information.

innobackup: MySQL binlog position: filename 'Warning', position (Code 1287):
'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead
090210 03:55:04  innobackup: innobackup completed OK!

[mysql@db3:~] more ibbackup_binlog_info
Warning (Code 1287): 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead
[mysql@db3:~]

[mysql@db3:~] more mysql-stdout
innobackup hello 3
innobackup hello 3
innobackup hello 4
innobackup hello 4
innobackup hello 5
innobackup hello 5
Note (Code 1051): Unknown table 'ibbackup_binlog_marker'
innobackup hello 6
innobackup hello 6
Warning (Code 1287): 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine'
instead
innobackup hello 7
innobackup hello 7
innobackup hello 8
innobackup hello 8
innobackup hello 9
innobackup hello 9
innobackup hello 10
innobackup hello 10
innobackup hello 11
innobackup hello 11
File    Position        Binlog_Do_DB    Binlog_Ignore_DB
db2-binlog.001980       495985099
innobackup hello 12
innobackup hello 12
innobackup hello 13
innobackup hello 13
innobackup hello 14
innobackup hello 14

At the January 2009 Boston User Group I presented a session on the new partitioning feature in MySQL 5.1. I go through how to define partitions, how partitioning makes queries faster, the different types of partitioning and when to use each type, and the restrictions and limitations of partitioning.

The slides are available at http://www.technocation.org/files/doc/2009_01_Partitioning.pdf. The video is embedded at the bottom of this post, and is also available at youtube at http://www.youtube.com/watch?v=zvN9XI-FraI.

Notes:

The partitioning part of the MySQL Manual is at: http://dev.mysql.com/doc/refman/5.1/en/partitioning-overview.html.

The functions that are not allowed in partitioning expressions are listed at: http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html

We also had an interesting development — according to the manual, an INSERT to a partitioned table that includes values that do not have a partition should insert all values up to the failure point. The example I used (and that you can see in the video) is:

insert into nums_list_mod (id) VALUES (4001, 4004, 4002);

Where 4001 and 4002 fit into partitions but there is no partition defined that will accept 4004. According to the manual, the value 4001 should be inserted, but 4004 and 4002 should not. Instead, we got the behavior that nothing was inserted (sql_mode=” and this was not within a transaction).

Enjoy the presentation!

So, a colleague ran into issues booking the Santa Clara Hyatt Regency Hotel—apparently there were no rooms with 2 double beds left for the MySQL 2009 Conference and Expo.

I could not imagine that the conference hotel would sell out so quickly!

The conference’s travel/hotel web page links directly to the online reservations page at http://santaclara.hyatt.com/groupbooking/clara2009orea.

I went there and did a little detective work. What I found was:

• When I chose Mon Apr 20 – Thu Apr 23, there were rooms with 2 double beds available.
  
• When I chose Sun Apr 19 – Thu Apr 23, there were rooms with 2 double beds available.
  
• When I chose Sat Apr 18 – Thu Apr 23, there were rooms with 2 double beds available.
  
• When I chose Mon Apr 20 – Fri Apr 24, there were NO rooms with 2 double beds available.
  

Aha! The problem is that there are no rooms with 2 double beds available for Thursday night.

Most likely the problem is that there are no rooms with 2 double beds at the conference rate for Thursday night, and the booking system can’t handle two different room rates as part of one reservation—most people don’t do that anyway (i.e., have a AAA discount one night but not the next).

I left voice mail for someone at the Hyatt Santa Clara and am waiting for a callback to make sure that I can add Thursday night (even if it’s a different price!) to my stay.

However, if you are having problems trying to book a room with two beds (in other words, to share with a co-worker or colleague), you may be having the same issue I had at first—if you end your stay on Thursday, you may be able to make a successful reservation, but you should either change your flight arrangements, or do your own followup with the hotel regarding room availability/price for Thursday night.

I realized tonight exactly why MySQL’s default behavior of silent truncation bothers me.

It reminds me of people who use a ticketing system and close every ticket as soon as they are done working on the issue instead of actually asking the other party if they are satisfied, because closing more tickets make it look like they’re doing more work.

It reminds me of workers at fast food restaurants who hit the button to make the order disappear as if they have already served me my food, because then their throughput times are faster.

Similarly, with MySQL’s default behavior of silent truncation, it’s as if the database server is saying “the fewer database errors raised, the better.” As in the previous two examples, the metrics do not matter if the quality of service is poor — particularly when the quality of service is poor specifically *because* people are trying to meet the metrics instead of the actual goal — customer satisfaction.

While it’s true that the goal is fewer database errors, the idea is to have fewer errors because there are fewer problems — not because the problems are not being reported!!!

You probably know that mysql -h host_or_ip can connect you to a remote host.

But did you know that you can change the host you are connected to from within mysql?

The undocumented (as far as I can tell, in the MySQL manual and in the “help” on the mysql command line) CONNECT statement can help.
(more…)