Skip to content

MySQL has a long history since its birth 18 years ago when Monty Widenius and David Axmark started it.
Planet MySQL started in 2004 and all its history is archived.  Let us dig in it.
It is always good to look back at the past to get some lessons for the future. Here are the 20 most positively voted blog posts since Planet MySQL birth. By positive blog post I mean the one with the biggest positive number of votes versus negative ones. So inside this chart you have false positive which are also very controversial subject ( lots of + and lots of  - with a majority of +).

Planet MySQL is like military archives that you can open after some time when passions have cooled down  (it might not be true for everyone or for every subject ;-))

First column is the excess of plus vote versus negative ones.
61     / 2009-12-21      What do MySQL staff think of the acquisition?    - Ronald Bradford
38     / 2011-03-08      The MySQL Council addresses the public bug database issue   - Giuseppe Maxia
30     / 2009-12-30      Save MySQL save the world   - Mark Callaghan
27     / 2010-10-12      Welcome SkySQL!   - Gary Pendergast
25     / 2009-12-29      Save MySQL by letting Oracle keep it GPL   - Sheeri K. Cabral
24     / 2011-08-09      Santa Clara MySQL Conference 2012: Unity or division?   - Kaj Arno
22     / 2011-08-10      Call for disclosure on MySQL Conference 2012   - Giuseppe Maxia
22     / 2010-10-19      Using MySQL as a NoSQL - A story for exceeding 750000 qps on a commodity server   - Yoshinori Matsunobu
20     / 2011-01-13      Temporary files binlog_cache_size and row-based binary logging  - Chris Calender
19     / 2010-01-03     Tales of the Trade #2: The Oracle-Sun deal   - Shlomi Noach
18     / 2010-12-15      MySQL 5.5 is GA!   - Oracle MySQL Group
17     / 2011-02-21      Where have the bugs gone?   - Mark Callaghan
17     / 2010-09-29      The MySQL swap insanity problem and the effects of the NUMA architecture   - Jeremy Cole
16     / 2009-08-04      XtraDB has been commited to MariaDB   - MySQL Performance Blog
16     / 2010-07-09      Using EXPLAIN EXTENDED / SHOW WARNINGS to Help Troubleshoot Inefficient Queries   - Chris Calender
16     / 2010-09-28      I need a new keyboard   - Domas Mituzas
16     / 2011-08-10      What is happening with the MySQL conference?   - Michael
16     / 2012-08-17      (less) open source   - Mark Callaghan
16     / 2010-08-28      MySQL 5.1 Plugins Development Published   - Andrew Hutchings
15     / 2011-02-22      Oracle introduces new levels of sucking to new versions of old software   - Monty Taylor

First kudos to Shlomi who wrote 2009-12-21 : "Ronald: you will remain #1 of all times for long time to come!" This was a good prediction as this blog post is still number one. I love  Tales of the Trade #2: The Oracle-Sun deal where this prediction happened.

Second lesson learned is not to trust the internet. For example  MySQL 5.1 Plugins Development Published  does not point any more to where it should. This is unfortunate that  there is nothing like  UUID  URI. Internet memories are not garantied : all MySQL DBA knows that : do backups 🙂 And  "MySQL 5.1 Plugins Development" remains an excellent book even if the pointer is gone.

This also illustrates the ability of a crowd to make important  choices  : "I need a new keyboard"  got a very good ranking. I really hope Domas that you got your new keyboard. Is it wireless ?

Some question should be hard as it seem they remain unanswered :  Where have the bugs gone? was raised  2011-02-21.

Some titles can be misinterpreted : Oracle introduces new levels of sucking to new versions of old software . That is a good lesson if you make book reviews. You should not just read the title . What sucked for Monty Taylor at that time is not what sucks for us now.

Some question do have an answer : What is happening with the MySQL conference? Well, It is taking place in April in Santa Clara but it is now called "Percona Live". It could have had a different answer and in the future it might have another different one. Who knows ? Shlomi you who made a good prediction  : Do you have any prediction, hint, advices ?

Enjoy 🙂

 

MySQL 5.6 has introduced a new unit test framework beside the existing ones.
The googletest test framework is now part of the MySQL test framework for 5.6.
GoogleTest is a C++ Testing Framework that helps  write better C++ tests.

I realized that it was used when i tried to buid a MySQL release from sources on a fedora platform. I got the following error :

-- Googletest was not found. gtest-based unit tests will be disabled. 
You can run cmake . -DENABLE_DOWNLOADS=1 to automatically download and build required components from source.

I tried tu install the package through yum :

$ sudo yum install gtest

But it did not solve the issue. I had to run cmake to allow cmake to download the googletest sources so that it can be compiled within  the MySQL distribution.

cmake .  -DENABLE_DOWNLOADS=1
...
-- Successfully downloaded http://googletest.googlecode.com/files/gtest-1.5.0.tar.gz
 to /home/sfrezefo/Downloads/mysql/mysql-5.6.8-rc/source_downloads
...

and then as usual :

make
sudo make install

Tests are a very fondamental element of MySQL to validate the compiled software behave correctly.
It is also very fondamental for developpers, contributor or people building MySQL on a specific platform or OS.
This is the only way to validate that there is no regressions on a particular platform.
If you fix a bug or if you develop a new functionality it is fondamental that all the tests go well.
Of course for that to work nicely all test cases have to be fairly released when bugs are fixed. 🙂

For a full description of all the components of The MySQL Test Framework :
http://dev.mysql.com/doc/mysqltest/2.0/en/

MySQL 5.6 has introduced a set of new features related to security (default randomised root password at install, password Validation Plugin ...). MySQL now also provides a method for storing authentication credentials securely in an option file named .mylogin.cnf.
This new feature has been introduced to avoid exposing password when running MySQL scripts. Its behaviour and limitations have been well described here :
http://mysqlblog.fivefarmers.com/2012/08/16/understanding-mysql_config_editors-security-aspects/

In all previous version and also in 5.6 if you run a MySQL script without interactively asking for password  the password is exposed on the  commands line and it can be accessed though a "ps" command or through history files.
With MySQL 5.6 it is now possible to transform a command like this :

mysql -hlocalhost -uroot -pmypass

into :

mysql --login-path=safelogin

To create this login-path containing the credential

mysql_config_editor set --login-path=safelogin --host=localhost --user=root --password

The login file must be readable and writeable to the current user, and inaccessible to other users. Otherwise, mysql_config_editor ignores it, and the file is not used by client programs, either.
-rw-------.  1 sfrezefo sfrezefo        152 15 févr. 21:25 .mylogin.cnf

To list the content of this login file you can run mysql_config_editor and through this command you will not see the password :

[sfrezefo@serge ~]$ mysql_config_editor  print --all
[local]
user = localuser
password = *****
host = localhost
[safelogin]
user = root
password = *****
host = localhost

So one of the principal benefits of mysql_config_editor is ease-of-use. But users must realize that even though it uses AES for encrypting the credentials security is not the main point. In fact the key is left on the door you just have to turn it to enter.
I have developed a very basic program "mysql_showconfigpwd" that expose the credentials. This could be useful if you have forgotten your encrypted credentials.

[sfrezefo@serge ~]$ mysql_showconfigpwd
File exists.
file_buff [local]
user = localuser
password = manager1
host = localhost
[safelogin]
user = root
password = manager1
host = localhost

The way mysql_config_editor works is quite simple : All the information associated with a login path is stored encrypted through AES. A random key is generated which is used to encrypt all the login path information (password included).
All these information are stored in a file. But the key used to encrypt the login path is store in clear.
The OS protection is thus the only thing that protect the content of the file. This is the same issue you get with ssh keys except in that case you have the idea of key pair.

This is even more critical because beside the operating system the database itself could get access to the login file.
A database user with the FILE privilege can do a LOAD DATA INFILE and then get access to all the encrypted password. This problem is not an easy one and we always fall back to the egg and hen problem 🙂 .All mechanism based on private key encryption meet these issue of where to sore the key. How does it work with other databases. Oracle 11G has the same concept "Secure External Password Store". The only difference is that being closed source  it create a false feeling of security  🙂

Here is the code of "mysql_showconfigpwd.cc" :

#include "my_config.h"
#include "my_aes.h"
#include "client_priv.h"
#include "my_default.h"
#include "my_default_priv.h"
 
#define MY_LINE_MAX 4096
#define MY_LOGIN_HEADER_LEN (4 + LOGIN_KEY_LEN)
static int g_fd;
static size_t file_size;
static char my_login_file[FN_REFLEN];
static char my_key[LOGIN_KEY_LEN];
 
int main(int argc, char *argv[]) {
 
DYNAMIC_STRING file_buf, path_buf;
char cipher[MY_LINE_MAX], plain[MY_LINE_MAX];
uchar len_buf[MAX_CIPHER_STORE_LEN];
int cipher_len = 0, dec_len = 0, total_len = 0;
MY_STAT stat_info;
const int access_flag = (O_RDWR | O_BINARY);
 
init_dynamic_string(&path_buf, "", MY_LINE_MAX, MY_LINE_MAX);
init_dynamic_string(&file_buf, "", file_size, 3 * MY_LINE_MAX);
 
if (!my_default_get_login_file(my_login_file, sizeof(my_login_file))) {
  fprintf(stderr, "Error! Failed to set login file name.\n");
  goto error;
}
 
if (my_stat(my_login_file, &stat_info, MYF(0))) {
  fprintf(stderr, "File exists.\n");
  file_size = stat_info.st_size;
  if ((g_fd = my_open(my_login_file, access_flag, MYF(MY_WME))) == -1) {
    fprintf(stderr, "Error! Couldn't open the file.\n");
    goto error;
  }
}
 
if (my_seek(g_fd, 4, SEEK_SET, MYF(MY_WME)) != 4) {
  fprintf(stderr, "Error! Couldn't seek 4.\n");
  goto error;
}
 
if (my_read(g_fd, (uchar *) my_key, LOGIN_KEY_LEN, MYF(MY_WME)) != LOGIN_KEY_LEN) {
  fprintf(stderr, "Failed to read login key.\n");
  goto error;
}
 
if (file_size) {
  while (my_read(g_fd, len_buf, MAX_CIPHER_STORE_LEN, MYF(MY_WME)) == MAX_CIPHER_STORE_LEN) {
    cipher_len = sint4korr(len_buf);
 
    if (cipher_len > MY_LINE_MAX) {
      fprintf(stderr, "Error!cipher_len > MY_LINE_MAX.\n");
      goto error;
    }
    if ((int) my_read(g_fd, (uchar *) cipher, cipher_len, MYF(MY_WME)) == cipher_len) {
      if ((dec_len = my_aes_decrypt(cipher, cipher_len,(char *) plain, my_key, LOGIN_KEY_LEN)) < 0) {
        fprintf(stderr, "Error! failed to decrypt the file.\n");
        goto error;
      }
      total_len += dec_len;
      plain[dec_len] = 0;
      dynstr_append(&file_buf, plain);
    }
  }
  fprintf(stderr, "file_buff %s \n", file_buf.str);
}
 
error:
dynstr_trunc(&file_buf, 0);
dynstr_trunc(&path_buf, 0);
my_close(g_fd, MYF(MY_WME));
dynstr_free(&file_buf);
dynstr_free(&path_buf);
 
exit(0);
}

Tu use it install this file in the MySQL source tree (mysql-5.6.8-rc/client in my case).

Add these 2 lines to the CMakeLists.txt in this folder.

MYSQL_ADD_EXECUTABLE(mysql_showconfigpwd mysql_showconfigpwd.cc)
TARGET_LINK_LIBRARIES(mysql_showconfigpwd mysqlclient)

Go to the root of your MySQL source tree and do :

rm CMakeCache.txt
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql56
make -j 8
sudo make install

Following my discussion with Mikael Ronstrom regarding Parallel databases through comments on a old blog post ( mysql-for-a-massively-parallel-database/#comments ) I want to clarify why i like the Shard-Query tool.

Shard-Query is a open source distributed parallel query engine for MySQL. It offers a way to answer the question of parallel query execution across shards http://code.google.com/p/shard-query/.

But first what about MySQL Cluster as a parallel database.
MySQL Cluster has effectively a very efficient MPP architecture. It is a remarkable product for massive write and primary key lookups. Its fast failover, thanks to it true shared nothing architecture is quite unique. With respect to these two points it is much better that other architectures. Oracle RAC with its shared disk architecture, distributed cache and distributed Lock manager is much less efficient on both points.

Bust as usual you have the default associated with your qualities. Regarding complex read queries MySQL cluster has always been weak.I agree that some great progress have been with condition pushdown and Adaptive Query localization that allow to push joins and filtering to the data nodes. Both mechanism alleviate the sql node from doing work (filtering, joins) and also reduce network bandwidth consumption.

But this is far from what do parallel databases like Greenplum or Microsoft Parallel Datawarehouse Solution. To have a truly parallel database you really need to analyze the query and split it in pieces. There is a lot of intelligence to be put at the SQL node to split the query plan into subtasks. You sometime even have to implement a multi path approach(a map reduce pattern) to fully execute the request. In that case the sql node need to implement a lot of intelligence regarding parallel query execution. The MySQL Cluster sql node does not currently do it. If this work was ever to happen in the SQL layer it would be great to have it work with various storage engines. it should be abstracted from any particular storage engine like NDB.

Shard-Query presented by Justin Swanhart at FOSDEM 2013 (slides) does it at the client layer. It is using php and Gearman to spread the work in parallel to various MySQL shards. This is not perfect but this is a nice approach that can greatly help in many cases. It can parallelize across shards or using MySQL partitioning. Another interesting point about Shard-Query is that it can also works with MySQL compliant column store.

I would prefer to have this intelligence implemented in the MySQL server with full guaranty of correctly supporting the client/server protocol but this is a much harder approach.

This was nice to see all the MySQL ecosystem in the same very crowded room :
Oracle, Percona, MariaDB, SkySQL. We definitely need this kind of open event. MySQL Connect (owned by Oracle) or Percona Live (owned by Percona) are 2 great events but they do not gather the same variety of speakers and attendees. Thanks to the FOSDEM MySQL Dev room review committee the agenda reflected this openness.

The "MySQL and friends dev room" was crowded which was for me a surprise as I expected most people to be at NoSQL or Cloud topics rooms ;-). This reflect the strong attraction of MySQL with its constant innovation momentum. The challenge for presenters was the short 30 mns format. This has a big advantage that speakers have to get rid of marketing bullshit and concentrate on key points.

Collin Charles gave a pres on MHA solution. It nice to hear that it is a very active project. It is a proven technology used at Facebook. It allows very versatile usage from a manual switch-over to fully automatic failover. It can be combine with a clusterware like pacemaker. This is a technology we fully support at SkySQL and that we have deployed successfully at many customers.

Peter Boros gave a very interesting talk on an ongoing project at the Groupon company. The solution address the automatic warmup of a slave server during binlog apply. it is mix of a tool that read and stream the slow query log with the percona playback tool to warmup slave. I definitely would like to know more. I really like when people address an old problem with a new creative approach. The part streaming the slow query log should be open sourced soon.

Lars Thalmann presented a full picture of Oracle innovation around MySQL. There was a nice question about why Oracle which has released a migration toolkit does not address Oracle migration. Lars was surprised too 🙂 But I think it is fair to leave this part be developed by the community ;-). Lars presented all the progress made around windows platform. They are impressive. This is a good point as Windows remain important as the entry point for adoption, development and testing.

Luis Soares who manage the replication at Oracle described in detailed the new features of 5.6. He gave a workaround on how to implement multi master replication.
MariaDB will offer natively this feature (Thanks to TAOBAO contribution).

Stephane Combaudon had the task to chose the 5 tools he considered the most useful in the percona toolkit. The nominees were :
pt-query-digest, pt-online-schema-change, pt-table-checksum, pt-stalk, pt-archiver
Stephan insisted on the fact that it is really important to understand how the tools work to avoid damaging mistakes. This is in particular true for pt-archiver which is a nice tool to purge old data in MySQL.

Maciej Dobzranski gave a talk on MySQL and security. Tis subject is a hot one. The key take away is "MySQL is not secure out of the box! Many users just leave it at that.". A lot of tips and valuable examples of security challenges faced with MySQL.

When I read the schedule I saw there was no pause for lunch for the MySQL Dev Room ! Unacceptable for a french guy 😉 ! I saw Justin Swanhart pres title "Divide and conquer in the cloud" I thought I might use this slot to go as I expected classic marketing about how the cloud is going to save our life. I bought my sandwich and run back to the MySQL Dev Room as I wanted to have for my money (kidding : FOSDEM is free with no need for registration 😉 and the beer is cheap).

Justin Swanhart was presenting "Shard-Query" which is a framework that add parallelism and improve query performance based on SQL language constructs. This is a great tool. Some nice ideas mentioned like using Shard-Key-Mapper in conjunction with mysqlnd plugins (PHP connector) . Justin gave example of query speedup for a SUM aggregate. He mention that MEAN and STDDEV were not candidate to parallelization. I think it can be parallelized in a multi pass approach. I will blog on it later.

Other interesting pres by Andrew Morgan, Sveta Smirnova, Oystein Grovlen, Giuseppe Maxia, Henrik Ingo, Raghavendra Prabhu.

I am sure that next year FOSDEM MySQL Dev Room will have more people as this is a really great event. MySQL remain a hot topic with a lot of innovation to come.
FOSDEM is definitely the right place for all the MySQL community to gather !

MySQL developers will cover upcoming features.   The MySQL engineering team is driving MySQL forward and users feedback is welcomed. For those having tested the Development Milestone releases this is a good opportunity to share your feedback and ideas.

The agenda includes :

  • InnoDB online operations
  • Replication :
    Global transaction IDs for replication and failover
    Multi-Threaded Slaves and Group Commit
  • NoSQL interfaces to InnoDB and NDB
  • Performance :
    PERFORMANCE_SCHEMA improvements
    Optimizer enhancements
  • Best Practices for Using MySQL in the Cloud

It is a physical event in Redwood Shores on Tuesday, June 05, 2012 8:00 AM – 5:30 PM
But there is also a live webcast option ! Register
I will definitely follow it.