~jk

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 -c 2
revno: 2
committer: bk@work.mysql.com
timestamp: Mon 2000-07-31 21:29:14 +0200

Hmm, 2000? MySQL is older than that. To the archives!

mysql-4.1.1-alpha/include/mysql_com.h:#define CLIENT_LONG_PASSWORD  1   /* new more secure passwords */
mysql-4.1.0-alpha/include/mysql_com.h:#define CLIENT_LONG_PASSWORD  1   /* new more secure passwords */
mysql-3.23.49/include/mysql_com.h:#define CLIENT_LONG_PASSWORD  1   /* new more secure passwords */
mysql-3.22.32/include/mysql_com.h:#define CLIENT_LONG_PASSWORD  1   /* new more secure passwords */
mysql-3.21.33b/include/mysql_com.h:#define CLIENT_LONG_PASSWORD 1   /* new more secure passwords */

First non-match: 3.20.x. The whole feature of capabilities was added in 3.21.x and CLIENT_LONG_PASSWORD was its first use-case.

$ ls -ls mysql-3.21.33b/include/mysql_com.h
16 -rw-r--r--  1 jan  staff  5173 Jul  8  1998 mysql-3.21.33b/include/mysql_com.h

Since 14 years this is new.

more secure

More secure it says. Well, this flag is used by the function check_scramble() to flip some more bits (if set, old_ver == FALSE):

my_bool check_scramble(const char *scramble, const char *message,
                   ulong *hash_pass, my_bool old_ver)
{
  struct rand_struct rand;
  ulong hash_message[2];
  char buff[16],*to,extra;                  /* Big enough for check */
  const char *pos;

  hash_password(hash_message,message);
  if (old_ver)
    old_randominit(&rand,hash_pass[0] ^ hash_message[0]);
  else
    randominit(&rand,hash_pass[0] ^ hash_message[0],
           hash_pass[1] ^ hash_message[1]);
  to=buff;
  for (pos=scramble ; *pos ; pos++)
    *to++=(char) (floor(rnd(&rand)*31)+64);
  if (old_ver)
    extra=0;
  else
    extra=(char) (floor(rnd(&rand)*31));
  to=buff;
  while (*scramble)
  {
    if (*scramble++ != (char) (*to++ ^ extra))
      return 1;                                     /* Wrong password */
  }
  return 0;
}

That is the OLD_PASSWORD() which had a VERY_OLD_PASSWORD() variant it seems. Both of them are broken and shouldn't be used anymore.

essence

Luckily this flag lost its meaning a long time ago. Since 4.1.x it is assumed that it is always set and since the same release there is a SHA1-based hashing for your passwords. No one gets harmed.

Perhaps someone should update the comment of that flag though.

I use MacOS X for most of my development work, but part of my job is writing apps that run on all major Unixes and Win32. Most of the time bugs and memleaks are "portable", meaning that they appear on all platforms in the same way and fixing them on your dev-platforms, fixes them everywhere else.

Well, "most of the time".

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 ...

MySQL Proxy 0.8.0 has been released at http://dev.mysql.com/downloads/mysql-proxy/

While it took a while to get it out, it contains a lot of good stuff and will make a few more things possible for the future. First of all, it is the first release with a threaded network-backend allowing to use plenty of CPUs.

The other big infrastructure change is mostly for developers: out-of-tree plugins can now be written. Now you can write plugins for the MySQL Proxy against a installed MySQL Proxy. No need to have the MySQL Proxy's source code around.

I'll be at the OpenSQLCamp 2009 which is part of the FrosCon this weekend and give a talk about drumroll MySQL Proxy.

http://programm.froscon.org/2009/track/OpenSQLCamp/index.de.html has all the sessions incl. my

MySQL Proxy: a MySQL toolbox

Architecture and Concepts of misuse

It will be an extension of my MySQL Conference talk from this year with binlogs, frm-files, ... and what happens if you expose MySQLs core functionality as libraries and wrap it with a scripting layer.

In MySQL Proxy 0.8 we are added a multi-threaded network-subsystem allowing several networks events be processed in parallel. Early benchmarks show that what we have in trunk basicly works.

But the benchmarks weren't as good as we expected. That's the time where you prepare to get dirty.

On the MySQL Proxy channel we get questions from time to time if the authentication can be intercepted and replaced data from a external source.

From now on, you can. For example if you want to get data from a external source (like LDAP) or want to implement roles.

At MySQL Conference in Santa Clara this April the idea was brought up, now we do it:

a MySQL Proxy hackathon this summer in Sun Office in Hamburg, Germany.

We want bring together everyone who is interested in

• hacking on MySQL Proxy,
• sharing ideas how it is used and
• how it should develop.

More at http://forge.mysql.com/wiki/MySQL_Proxy_Hackathon_2009

In my rare spare time work on the http://jan.kneschke.de/2009/5/4/binlog-storage-engine-mysql-proxy-edition and for a few days I was wondering why my index-based JOINs didn't worked. With a index it works, with an index nothing is returned.

We are happy to announce that MySQL Proxy 0.7.1 is available in a source and binary release for many popular platforms.

This release contains a few minor bugfixes and changes in directory layout over the previous 0.7.0 release.

• moved plugins to lib/mysql-proxy/plugins
• moved lua modules to lib/mysql-proxy/lua
• moved libs to lib/

Please report any problems on http://bugs.mysql.com, our Launchpad discussion mailing list at https://launchpad.net/~mysql-proxy-discuss or on IRC: #mysql-proxy on irc.freenode.net.

Please note that the binary for Windows is currently still the old 0.6.1 release and will be updated soon.

A binlog storage engine was talked about many times already. Being able to JOIN against the binlogs, the just SELECT it, search for entries ... is not possible with current SQL commands in the MySQL Server.

As you have read the previous articles here, I wrote a binlog encoder/decoder for MySQL Proxy and wrapped with with a Lua interface. Some years ago I also wrote a scriptable storage engine which can call lua scripts to return data for SELECTs. Putting the two together gives us: a Binlog Storage Engine.

After getting the http://jan.kneschke.de/2009/4/22/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

... 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.

I just pushed the code for my replication changes on launchpad:

$ bzr branch lp:~jan-kneschke/mysql-proxy/replication

The presentation should be available ... soon.

The slides are written, uploaded and the code snippets work. I'm ready to present.

One of the topics will be merging binlogs which is what multi-master replication is all about on the low level. A common example is sharding where you have several masters which share the same table-structures, but store the data independently. This is great for scaling out, but tricky if you have to run a query over the full dataset.