Lightning talks at Percona Live MySQL Conference and Expo 2013

It happened last year at the previous Conference, and it's going to happen this year as well.

There will be a session of lightning talks during the conference, for the joy of both the audience and the speakers.

Lightning talks are fun and instructional micro events. Their official purpose is to give the audience a chance to learn something in a very limited amount of time. The real purpose is for the speaker to be as entertaining and memorable as possible within the allocated time.

The call for participation is open from now until February 10th. If you have something that you want to say in not more than 5 minutes, you can SUBMIT A PROPOSAL.

In addition to propose a compelling description that will make us choose your proposal, be aware of the lightning talks rules:

• All slides will be loaded into a single computer, to minimize delays between talks;
• Someone (probably me) will nag the speakers until they either surrender their slides or escape to Mexico;
• All speakers will meet 15 minutes before the start, and be given the presentation order. Missing speakers will be replaced by reserve speakers;
• The speaker will have 5 minutes to deliver the talk.
• When one minute is left, there will be a light sound to remind of the remaining time.
• When 10 seconds are left, most likely the audience will start chanting the countdown.
• When the time is finished, the speaker must leave the place to the next one.

BTW, if lightning talks are not for you, you may try Birds of a feather (BoF) sessions instead. Also for BoFs, the deadline is February 10th.

Easily testing MySQL 5.6 GTID in a sandbox

MySQL 5.6 seems to be ready for GA. I have no inside information about it, but from some clues collected in various places I feel that the release should not be far away. Thus, it's time for some serious testing, and for that purpose I have worked at updating MySQL Sandbox with some urgent features.

I have just released MySQL Sandbox 3.0.28, with more support for MySQL 5.6. Notably in this release, there is suppression of MySQL 5.6 annoying verbosity, additional suppression of more annoying warnings ( actually a bug) when using empty passwords on the command line.

There is also an enhancement to the 'clear' command. In previous versions of MySQL, this command removed everything from the data directory, leaving the server ready for a clean start. In MySQL 5.6, this is not feasible, because there are innodb tables in the mysql schema. Therefore, what happens now is that, immediately after creating the sandbox users, the installation program stores a dump of the 'mysql' schema. The 'clear' command will remove the innodb tables from mysql, and the 'start' command will notice that and reload the schema from the dump.

More interesting, though, the replication installer creates a file (only if the MySQL server is 5.6.9 or higher) called 'enable_gtid' which restarts the replication cluster with Global Transaction Identifiers enabled.

Let's see an example session:

$ make_replication_sandbox 5.6.9
installing and starting master
installing slave 1
installing slave 2
starting slave 1
... sandbox server started
starting slave 2
... sandbox server started
initializing slave 1
initializing slave 2
replication directory installed in $HOME/sandboxes/rsandbox_5_6_9

$ cd $HOME/sandboxes/rsandbox_5_6_9

$ ./check_slaves 
slave # 1
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 2590
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 2590
slave # 2
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 2590
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 2590

Now we use the 'enable_gtid' command. It will simply restart the cluster with the appropriate options.

$ ./enable_gtid 
executing "stop" on slave 1
executing "stop" on slave 2
executing "stop" on master
executing "start" on master
. sandbox server started
executing "start" on slave 1
. sandbox server started
executing "start" on slave 2
. sandbox server started

$ ./check_slaves 
slave # 1
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 151
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 151
slave # 2
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 151
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 151

Now, let's see if Global Transaction IDs are enabled.

$ ./m -e 'create table test.t1(i int not null primary key)'
$ ./m -e 'insert into test.t1 values (1)'
$ ./m -e 'insert into test.t1 values (2)'
$ ./m -e 'show master status\G'
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 825
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: C0DF6B8A-5823-11E2-BC44-3970854BE7A5:1-3

$ ./m -e 'show binlog events in "mysql-bin.000002"'
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000002 |   4 | Format_desc    |         1 |         120 | Server ver: 5.6.9-rc-log, Binlog ver: 4                           |
| mysql-bin.000002 | 120 | Previous_gtids |         1 |         151 |                                                                   |
| mysql-bin.000002 | 151 | Gtid           |         1 |         199 | SET @@SESSION.GTID_NEXT= 'C0DF6B8A-5823-11E2-BC44-3970854BE7A5:1' |
| mysql-bin.000002 | 199 | Query          |         1 |         317 | create table test.t1(i int not null primary key)                  |
| mysql-bin.000002 | 317 | Gtid           |         1 |         365 | SET @@SESSION.GTID_NEXT= 'C0DF6B8A-5823-11E2-BC44-3970854BE7A5:2' |
| mysql-bin.000002 | 365 | Query          |         1 |         440 | BEGIN                                                             |
| mysql-bin.000002 | 440 | Query          |         1 |         540 | insert into test.t1 values (1)                                    |
| mysql-bin.000002 | 540 | Xid            |         1 |         571 | COMMIT /* xid=26 */                                               |
| mysql-bin.000002 | 571 | Gtid           |         1 |         619 | SET @@SESSION.GTID_NEXT= 'C0DF6B8A-5823-11E2-BC44-3970854BE7A5:3' |
| mysql-bin.000002 | 619 | Query          |         1 |         694 | BEGIN                                                             |
| mysql-bin.000002 | 694 | Query          |         1 |         794 | insert into test.t1 values (2)                                    |
| mysql-bin.000002 | 794 | Xid            |         1 |         825 | COMMIT /* xid=29 */                                               |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+

Good. The master has stored the GTID in the binary logs. What about the slaves?

$ ./s1 -e 'show slave status\G' |grep 'Running:\|Gtid'
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
           Retrieved_Gtid_Set: C0DF6B8A-5823-11E2-BC44-3970854BE7A5:1-3
            Executed_Gtid_Set: C0DF6B8A-5823-11E2-BC44-3970854BE7A5:1-3

$ ./s2 -e 'show slave status\G' |grep 'Running:\|Gtid'
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
           Retrieved_Gtid_Set: C0DF6B8A-5823-11E2-BC44-3970854BE7A5:1-3
            Executed_Gtid_Set: C0DF6B8A-5823-11E2-BC44-3970854BE7A5:1-3

Also the slaves are collecting GTIDs. All is well.

Notice that this method is only safe because we are using a system with no traffic at all. If your replication is under load, then you need to follow the method described in the MySQL 5.6 user manual.

Fun with MySQL options

While testing MySQL 5.6, I came across some curious values for the new values used to set the crash-safe slave tables. To get safety, we need to set relay_log_info_repository and master_info_repository to 'TABLE'. That way, the replication information, instead of going to a file, will be saved to two tables in the mysql schema (mysql.slave_relay_log_info and mysql.slave_master_info).

So I was setting these values back and forth between 'FILE' and 'TABLE', until I made a "mistake." Instead of typing

set global relay_log_info_repository='table';

I wrote

set global relay_log_info_repository=1;

To my surprise, it did what I wanted!

show variables like '%repository%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| master_info_repository    | FILE  |
| relay_log_info_repository | TABLE |
+---------------------------+-------+

It seems that the server accepts 1 for 'TABLE' and 0 for 'FILE'. But this is not a firm rule. I tried the same thing with another variable that supports 'FILE' and 'TABLE':

set global log_output=0; show variables like 'log_output';
ERROR 1231 (42000): Variable 'log_output' can't be set to the value of '0'
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

set global log_output=1; show variables like 'log_output';
Query OK, 0 rows affected (0.00 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | NONE  |
+---------------+-------+
1 row in set (0.00 sec)

 set global log_output=2; show variables like 'log_output';
Query OK, 0 rows affected (0.00 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

set global log_output=3; show variables like 'log_output';
Query OK, 0 rows affected (0.00 sec)

+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| log_output    | NONE,FILE |
+---------------+-----------+
1 row in set (0.00 sec)

set global log_output=4; show variables like 'log_output';
Query OK, 0 rows affected (0.00 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+
1 row in set (0.00 sec)
set global log_output=5; show variables like 'log_output';
Query OK, 0 rows affected (0.00 sec)

+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| log_output    | NONE,TABLE |
+---------------+------------+
1 row in set (0.00 sec)

set global log_output=6; show variables like 'log_output';
Query OK, 0 rows affected (0.00 sec)

+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| log_output    | FILE,TABLE |
+---------------+------------+
1 row in set (0.00 sec)

set global log_output=7; show variables like 'log_output';
Query OK, 0 rows affected (0.00 sec)

+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| log_output    | NONE,FILE,TABLE |
+---------------+-----------------+
1 row in set (0.00 sec)

The manual does not mention multiple values for this variable. The fact that the server accepts such value is strange enough. What is more strange is that in one case, option 1 resolves to 'TABLE' (but not the quoted '1', which is rejected), while in the other case, 'TABLE' is number 4.

I don't think this is intentional. But surely it's confusing.

Using a password is insecure, but no password is OK?

I have been preaching since 2003 that the default deployment of MySQL (where root can access without password) should be changed to something more sicure.

Yet, MySQL 5.6 still uses the same defaults.

$ mysql --no-defaults -u root --port=5000 -h 127.0.0.1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.9-rc MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set password=password('oh-come-on');
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

I have installed MySQL 5.6. Now I access as root without password. Not a word of complaint. Not a warning. Nothing.
But what happens when I set a password and use it?

$ mysql --no-defaults -u root --port=5000 -h 127.0.0.1 -poh-come-on
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.9-rc MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

WTF? So a password is insecure, but no password is OK?
I know the risks of using a password at the command line, thanks for telling me. Now I don't want to see this message anymore.

I wonder how I can remove this warning. Scripted testing gets borked horribly with it.