Q&A; on MySQL 5.1

Listening to Sheeri's presentation on MySQL 5.1, I saw that there are a few questions left unanswered. I am listing here some of the questions that I found interesting, plus a few from an early webinar on the same topic.

Q: does Partitioning physically split data?

A: No. Some engines (MyISAM, Archive) do a physical split, but this is not necessary, as you see if you apply partitioning to a InnoDB table. Partitioning is a logical split of data, for easy retrieval. It is completely transparent to the user.

Q: Can you set partitions to different servers?

A: No. Partitions are logical parts of one table within one server. Partitioning through the Federated engine is not supported.

Q: How efficient are Row-Based Replication operations compared to Statement based ones?

A: RBR is faster when the insert or update is the result of an expensive operation. Otherwise, the efficiency for insertion and deletion is roughly equivalent. Updates on multiple records are usually more expensive with Row-Based Replication.

Q: Is the event scheduler polluting the Error Log?

A: yes, unfortunately. But it has been fixed in 5.1.31 (See also Bug#38066. As you can see from the discussion in the bug report page, it was object of a long and intense discussion.

Q: Can you send email through the Event Scheduler?

A: No. But you can integrate it with a hack using MySQL Proxy through Federated tables.

Q: Is there an equivalent to SHOW FULL PROCESSLIST from the INFORMATION SCHEMA?

A: Yes. Actually, SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST is equivalent to SHOW FULL PROCESSLIST. See the manual

Q: Are partitions supported in replication?

A: Yes. Partitions are fully supported in replication. The only problem you may have is when using the "DATA DIRECTORY" and "INDEX DIRECTORY" clauses, if the slave does not have the same directory structure and OS privileges as the master.

Q: Is the event scheduler supported in replication?

A: Yes. The event definition is replicated but left inactive, and the effects of the event scheduler are replicated as any other statement. When promoting a slave to master, it is necessary to manually activate the events.

Q: What happened to RENAME DATABASE? Why is not available anymore?

A: RENAME DATABASE was a command implemented in the early stages of MySQL 5.1, for the specific purpose of helping the upgrade script to set the database name with the appropriate charset. It had a deadly side effect, though. It removed all the objects associated with the database. Thus, it was removed and renamed in such a way that nobody would use it for simply renaming a database.

Q: How does the slow query log work with microseconds?

A: To catch slow queries with duration measured in less than 1 second, you can set the long_query_time global variable to a fractional value. E.g. set global long_query_time=0.5; will enable queries taking more than 1/2 second to be logged to the slow queries log.

An odd spot in relay slaves

A relay slave is a slave that writes to its binary log the stream of commands received from the master, thus enabling other slaves to replicate. This is often used to relieve the master from heavy traffic created by many slaves asking for binlog events.

A typical scenario is when a master has a few dozen slaves, and instead of dealing with all of them directly, uses four relay slaves, each one dealing with 6 slaves.
So, where's the trick? The trouble comes if you change replication format after you start the slave.
Example. One master (M), two relay slaves (R1, R2), with four slaves each (S1,S2,S3,S4, S5, S6, S7, S8).
You started working in MIXED mode, which was set in the option file of each server.
Now you want to switch to STATEMENT mode for a while.
Then, you connect to each of the 11 servers, one by one, with a script that sends to each one the command SET GLOBAL binlog_format=STATEMENT.
You close all open connections from your clients, and before issuing your command, you again connect to all servers, and run SELECT @@binlog_format to make sure that all servers have the same setup.
Your special query is a tracking command, which inserts @@server_id in a table, and in each server the table should have the server_id of its host;
However, you notice that the propagation happens until the relay slaves. All the leaf nodes have the same values of their master.
What happened? It's something that is somehow documented, but results in an unexpected result. A SET GLOBAL statement is valid for every new connection. Meaning that, if you have an open connection, the global behavior doesn't apply. In our example, we closed all client connections. But there was one hidden connection that was not closed.
The slave SQL thread was already running, and was thus unaffected by the change.
To make the change stick, and thus propagate to all the slaves, we need to start and stop the slave.
I open Bug#40106 about this behavior because, although it's is logically explainable, it goes against the user's expectation. (If I change the binlog format, I expect to affect the binary log. Stopping the slave does not occur naturally. Perhaps this is a question for the certification exam).

Decoding binlog entries with row-based replication

If you have tried using row based replication, you may have noticed two things: (1) it fixes many inconsistencies of statement-based replication, but (2) the binlog is unfit for humans. Inspecting it after a problem occurs won't provide any useful information. The instructions look like line noise, and when you don't see the result you were expecting you wonder if that's the case.

For example, after executing this code:

create table t1 (id int, c char(10), d date);
insert into t1 values (1, 'abc', '2008-01-01');
insert into t1 values (2, 'def', '2008-08-19');
insert into t1 values (3, 'ghi', current_date());
select * from t1;
+------+------+------------+
| id   | c    | d          |
+------+------+------------+
|    1 | abc  | 2008-01-01 | 
|    2 | def  | 2008-08-19 | 
|    3 | ghi  | 2008-09-12 | 
+------+------+------------+

The binlog, as shown by mysqlbinlog, gives output like

# at 182
#080912 17:15:07 server id 1  end_log_pos 289   Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1221232507/*!*/;
create table t1 (id int, c char(10), d date)
/*!*/;
# at 289
#080912 17:15:07 server id 1  end_log_pos 357   Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1221232507/*!*/;
BEGIN
/*!*/;
# at 357
# at 402
#080912 17:15:07 server id 1  end_log_pos 402   Table_map: `test`.`t1` mapped to number 21
#080912 17:15:07 server id 1  end_log_pos 443   Write_rows: table id 21 flags: STMT_END_F

BINLOG '
e4fKSBMBAAAALQAAAJIBAAAAABUAAAAAAAAABHRlc3QAAnQxAAMD/goC/goH
e4fKSBcBAAAAKQAAALsBAAAQABUAAAAAAAEAA//4AQAAAANhYmMhsA8=
'/*!*/;

This is more difficult to read than ancient Etruscan. If you are a DBA, you curse and look for help.
But now it has changed.
Starting with MySQL 5.1.28 (available since September 13th), using the "--verbose" option, mysqlbinlog will add some human readable comments after the statement.

BINLOG '
e4fKSBMBAAAALQAAAJIBAAAAABUAAAAAAAAABHRlc3QAAnQxAAMD/goC/goH
e4fKSBcBAAAAKQAAALsBAAAQABUAAAAAAAEAA//4AQAAAANhYmMhsA8=
'/*!*/;
### INSERT INTO test.t1
### SET
###   @1=1
###   @2='abc'
###   @3='2008:01:01'
# at 443

The manual has more information on this topic.
It is a much needed addition. Our support department insisted on having this feature developed before GA, and with reason. Assisting customers who have replication problems without being able to decode the binlog output is quite a hard task.
Jan Kneschke, the author of MySQL Proxy, showed the way with this proof of concept, and now the feature is out. Good job, all the developers involved!