The way online schema changes have historically worked with statement based replication is to create an empty table on a slave, setup triggers to capture changes to a log table, copy the old table to the new table, apply the changes from the log table and atomic rename. This process breaks when using statement based replication because triggers don’t fire on events replicated to the slave. Triggers will fire on the master and the row events for any modified tables just replicate to the slave. This makes traditional online schema change break with statement based replication.

The workaround I’ve seen a few times at the conference is to run schema changes on the master when using row based replication. This is means either significantly reducing replication capacity during a schema change or having the change run really slowly so replication can keep up. Neither of these are optimal. I would like to submit an alternate method. Rather than …

Configuring the character set for MySQL is confusing. It is so confusing that there are roughly 25 different places to configure a character set. Don’t believe me? Add them up. The real number may be closer to 30. I realize a lot of this is due to the age of MySQL and the extent of it’s character set support. MySQL does support character set configuration in many different places which is usually a good thing.

I often complain about defaults that make no sense like lock_wait_timeout=1 year. In this case there is a default that makes absolutely no sense to me. The manual says that select into outfile is the complement of load data infile. It isn’t completely true. They differ in one key aspect, the default character set!. By default select into outfile now does the right thing by using binary character set and dumping the raw bytes to the file. Load data infile defaults to the value of the character_set_database variable which defaults …

Occasionally it is useful to know what a MySQL command is doing internally. Just looking into the MySQL source directory can be overwhelming. Knowing the basics of the handler interface and the sql parser can be a great start for reading the source code to understand what MySQL does under the hood. Here I will cover a little bit about how the SQL syntax is defined.

Everything starts with lex.h and sql_yacc.yy in the sql/ dir. lex.h contains all the functions and symbols used to make up the SQL syntax. The sql_yacc.yy file describes the relationships between these symbols and the C functions responsible for executing them. I’m not sure why some symbol definitions end in _SYM and others don’t. Looking in lex.h “FLUSH” is defined as FLUSH_SYM. To see all the places where flush is allowed in the SQL go back to sql_yacc.yy and grep for it.

The first important section looks like this:

/* flush things */ …

This is the required post about things I observed during this years MySQL conference.

Things that are awesome:

• The tables in sessions. I think these were here last year. They are still awesome this year.
• The new style power plugs. They solved the problem of people tripping over daisy chained power strips and the strips being accidentally turned off.
• Massive quantities of coffee and real cream.

Things that can be improved:

• Lunch tickets. I overheard the same conversation a dozen times about people not being able to find their lunch tickets or not really knowing about them.
• Make badges reversible. A badge under observation will be facing the wrong way.

Things that just bumped me:

• The music is different this year. Now it makes me feel like a teenager struggling with a breakup.
• My secret clean bathroom has been …

Some show commands support a where clause. The column name that can be used in the expression for the where clause depends on the result of the show command. For example in show tables the column is Tables_in_foo where foo is the database name.

MariaDB [test]> show tables where Tables_in_test = ‘t';
+—————-+
| Tables_in_test |
+—————-+
| t |
+—————-+
1 row in set (0.00 sec)

This is a problem with the show databases command because databases aren’t really *in* anything. Database is a reserved word so this happens.

MariaDB [(none)]> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| test |
+——————–+
3 rows in set (0.00 sec)

MariaDB [(none)]> show databases where database=’test';
ERROR 1064 …

How do you shutdown mysqld? I tend to use SIGTERM. People where I work tend to use mysqladmin shutdown. When things get bad I use SIGKILL. These three methods will end up with a dead mysqld but the one you choose depends on the situation and can even result in lost data. On Linux the difference between SIGTERM and SIGKILL is significant and often times misunderstood.

Before processes start to die it is important to understand the relationship between mysqld_safe and mysqld. mysqld_safe is the watchdog script for mysqld. It is responsible for starting mysqld and keeping an eye on it. It does this by waiting for mysqld to exit then checking the return code. On a safe shutdown such as one done by mysqldadmin or a SIGTERM mysqld will return zero. When mysqld_safe sees a zero return code it will also exit. If the return code is anything else then mysqld_safe assumes mysqld crashed and starts a new instance of mysqld. This difference can help …

These are things I like that I consider differences from last years conference. There are plenty of other things I like that don’t need to be listed here.

• The overall tone and feel of the conference was much less marking and much more technical
• Refreshingly honest keynotes. There was a lot of coming clean about the history of MySQL and the conference.
• Percona is very technical but it is also a business. They are very good about bringing out the technical and not being pushy about the business.
• No ice cream social. A thousand people shaking sticky hands with each other is never a good idea.
• percona.tv
• The conference was busy but never crowded

Now for the dislike:

• Only one song before every session.
• The chairs. Damn the chairs.
• Wifi failed more often than it worked. Most of the time I was tethered to my phone. …

[ Note: I haven't fully investigated this problem but the post has been hanging around in my queue for months. Rather than have it rot there I am publishing what I know in hopes that it helps someone else. ]

There are a lot of different kinds of locks in MySQL. Some of these locks are exposed to users such as intention locks, table locks, and row locks. There are other locks that aren’t exposed as well. These are mutexes that MySQL uses internally to protect resources from being modified by more than one thread at a time. These locks are numerous and complicated. When these locks deadlock mysql can stop dead in it’s tracks. The last deadlock I found happens when flush logs, show processlist, and a slave reconnect happen at the same time. I don’t have a core from the mysqld process, only stack traces. The breakdowns of stack traces are locks that I’m pretty sure the threads own and ones that they may be stuck trying on. I am working …

The manual says bytes but sometimes it is measured in characters. It seems like group_concat_max_len is in bytes when being passed through a temporary table and in characters otherwise. This works fine when using latin1 but when converting to utf8 mysql must reserve 3 bytes per character when setting types in a temporary table. This is yet another reason to dislike group_concat..

mysql> create table group_concat_bug (str1 varchar(255), str2 varchar(255), str3 varchar(255)) charset=utf8 engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> show create table group_concat_bug;
+——————+———————————————————————————————————————————————————————————–+
| Table | Create Table |
…

These are the rules of the Freenode #mysql drinking game.

1. Any non op posts a pastebin link either either the query and no error or the error and no query drink
2. Any non op posts a query with, “why doesn’t this work?” without any explanation about the results they want or what they’re getting drink
3. Domas trolls the channel with a legit issue drink
4. When a postgres guy answers a question about sqlite in #mysql 3 drinks
5. Someone answers a question with, “kill yourself” drink
6. Someone asks a phpmyadmin question
7. Someone asks a workbench question
8. Someone can’t figure out how to reset the root password
9. Someone says they are getting an access denied error but they insist the username and password are correct.
10. Someone asks a mssql question but tries to disguise it as a mysql question because there is no mssql channel.
…