Roland opened a bug "Documentation indicates some MYSQL_FIELD->flags are deprecated which aren't."

The MySQL protocol uses the string values of ENUM- and SET-fields. To indicate that this string originated from a ENUM- or SET-column the ENUM_FLAG and SET_FLAG are used in the Column Defintion

Same as Roland I wonder why they are deprecated. Is it something new? Something old?

Searching through the history

In an attempt to find out since when ENUMs are sent as MYSQL_TYPE_STRING + ENUM_FLAG I grepped through the oldest MySQL release that can be found in the bzr tree:

mysql-3.23.22-beta$ find . -type f | xargs grep -n ENUM_FLAG …

Now that forge.mysql.com is shutdown the protocol documentation found a new/old home at:

http://dev.mysql.com/doc/internals/en/client-server-protocol.html

It documents the early days of MySQL 3.20 as seen in:

• Protocol V9 (used by MySQL 3.20 and later)

up to the features added MySQL 5.6.x:

• COM_BINLOG_DUMP_GTID
• SHA256 auth

Today a little history lesson.

I was on a quest to find the origin of this line in include/mysql_com.h:

#define CLIENT_LONG_PASSWORD    1       /* new more secure passwords */

"new" ? "more secure" ?

These CLIENT_* flags are used between client and server to negotiate what capabilities they support. It allows the server to add new features to the protocol and let's the client add support for it along the way. MySQL 5.5.x has 21 of these flags.

But back to CLIENT_LONG_PASSWORD and its wonderful comment. What would one expect if someone reads "new" and "more secure"?

new

Well, let's check the file history:

$ bzr blame include/mysql_com.h
2            bk@work | #define CLIENT_LONG_PASSWORD 1   /* new more secure passwords */
$ bzr log …

I'm preparing the code for my MySQLConf 2010 session "MySQL Proxy meets: Memcache" where I'll present how to replicate from MySQL to memcache by using the MySQL Proxy.

Part of it will be using the semi-sync replication support for MySQL 5.5 to implement a synchronous MySQL-to-Memcache replication. All I need is the network protocol definition for semi-sync ...

The semi-sync replication is a implemented as replication plugin and lives in:

plugins/semisync/

The master sends a binlog event with a indication that it wants to get a reply for it. Compare these two events:

[0000]  28 00 00 01|00|00 00 00 00 04 01 00 00 00 27 00  (.............'.
[0010]  00 00 00 00 00 00 20 00 14 01 00 00 00 00 00 00  ...... .........
[0020]  6c 6f 63 61 6c 2e 30 30 30 30 30 31              local.000001

... and now with semi-sync:

[0000]  2a 00 00 01|00|ef 00|00 00 00 00 04 01 00 00 00  *...............
[0010] …

After getting the Druckbetankung stuff working and discussing the idea with the replication team we figured out that my presented approach has a nice hack-value, but otherwise is perhaps a bit too complicated. The same result can be achieved by a more simplified approach:

• turn our input data into a RBR based base64-encoded BINLOG-stmt using binlog
• use the mysql client to push the data into the server

The BINLOG stmt is used by mysqlbinlog to pass the RBR-events through the mysql client to the mysql-server. Usually it wraps a FORMAT_DESCRIPTION_EVENT and the TABLE_MAP_EVENT and the *_ROWS_EVENTS. BUT ... it can handle any binlog-event. Just try

$ mysqlbinlog …

... or how to misuse RBR to fill a MySQL table in the hopefully fastest way.

In my session yesterday I didn't had time to talk about this. The slide-desk covers it and I still wanted to document the idea at least.

The starting point is "Row Based Replication stores data in internal MySQL row-format." The slave can apply these events without any SQL parsing involved.

The idea is to create a master plugin that

• takes a file or something else that you want to INSERT into the mysql-server
• creates a binlog-stream using row-based replication events from it
• expose the binlog-stream and pretend to be a mysql-master (listen on port :3306 and handle COM_BINLOG_DUMP)
• let the MySQL-server connect to master-plugin using CHANGE MASTER TO and …

Inspired by http://blogs.sun.com/thava/entry/dump_mysql_frm_file_header I jumped into http://forge.mysql.com/wiki/MySQL_Internals_File_Formats and tried to write a decoder for the .frm files. Sadly the internals document is missing all the interesting parts.

So it was time to get the hands dirty and get into the code ... it got really dirty. But I found a little gem in there.

If you are interested take a look at open_binary_frm() or create_frm() in sql/table.cc or mysql_create_frm() in sql/unireg.cc. It has all the glory. You may have to wipe off the dust a bit has this code is (I bet) as old as MySQL is.

.frm-files are from a time when Monty wrote Unireg …

In my recent work I had to dive into the Internals of the MySQL protocol.

Most of the protocol is described in Chapter 8. MySQL Client/Server Protocol. "Most" as it documents the state of MySQL 4.1.x before it was stable. It is interesting for all developers who want to extend the mysql-server or want to write a native connector to MySQL which isn't using libmysqlclient.

Note: the protocol definition and the derived work at released under the terms of the GPL. See 8.1. Licensing Notice

Connection States

The MySQL protocol has two phases: auth and …