I had 3 or 4 questions where i thought that the answers (or the question) were not correct or no unmistakable but maybe i just didn't get it :)

MySQL 5.6-DBA and Developer-Certification in Beta-Status (low price!)

Recently Todd blogged about why you should register for MySQL 5.6 certification exams now: http://mysqlblog.fivefarmers.com/2013/10/04/why-you-should-register-for-mysql-5-6-certification-exams-now/ and i also think that now is a good opportunity to renew or take a new mysql certification.

The most interesting point in my opinion is that you can save a lot of money if you take the exam NOW! The exam price is 50 dollar (39 Euro) till 14.12.2013.

So, if you want to update your, probably years old, mysql certification now is the time! You can search for a test center and register for the exam here:

http://pearsonvue.com/oracle/

Be aware that you won't get your results directly but after a few weeks. Also there are about 150-220 questions! Phew! All information about the exam can be found here:

I'll take the exam in November and will let you know my opinion about the exam.

New MySQL 5.6-Feature host_cache_size does not work

Today as i was learning for the new MySQL 5.6-certification (more about that in a later post) i stumbled again across the host_cache-Table that was added to MySQL in 5.6.5. Before that you had no chance to check which hosts are already known by the MySQL-server.

So thats a pretty good feature for us. And even better: you could resize the size of the host_cache now! Whoohoo, awesome! As we have a lot of servers that need to connect to one of our MySQL-server and we could not switch to ip-based-authentication we were really happy to tune the host_cache-size without recompiling MySQL.

Unfortunately i noticed that the performance_schema.host_cache table only holds 128 rows and that the content was changing everytime i checked. So i logged in to a server that wasn't already in the host_cache-table, made a connection attempt to the mysql server and checked again the host_cache-table:

The server was now in the host_cache-table but still the table held only 128 rows though the host_cache_size-variable was set to 2000. :/

You can see my bug report here: http://bugs.mysql.com/bug.php?id=70552

Hopefully it won't take Oracle years to fix this ;)

Running out of Disk Space? Move innodb-tables to another partition (with MySQL 5.6)

Recently i had to manage big database installation that was running out of disk space. The partition on which the mysql datadir was located only had a few gigabytes free. Resizing the partition was not possible without a long downtime so that was no option. The installation had only innodb-tables so using symlinks was also not possible.

Luckily they were using MySQL 5.6 and the server had another partition with more than enough disk space available so i decided to use that partition for the biggest tables.

Please keep in mind that if you use binary logging that your binary logs gonna grow very big so maybe you want to purge them afterwards:

http://dev.mysql.com/doc/refman/5.6/en/purge-binary-logs.html

phpMyAdmin breaks Replication in MySQL 5.6

Recently i updated to MySQL 5.6 and we were really excited about the very good overall performance. But beside a major bug concerning wrong results when running a SELECT that includes a HAVING based on a function (see http://bugs.mysql.com/bug.php?id=69638) we also noticed that from time to time the replication breaks with the following error:

After some investigation it seemed like this happens if one modifies some user privileges, so we stumbled upon http://bugs.mysql.com/bug.php?id=68892.

Essentially the bug report says that if you use the wrong syntax for GRANT-statements the replication will break. So far, so bad. I told everyone who had the privileges to modify user privileges that they should really watch what they are doing and inform us if they accidentially used the wrong syntax.

Unfortunately that didn't help. Not because they didn't inform us but because phpMyAdmin sends a

REVOKE GRANT OPTION ON `database`@`table_name` FROM `user`@`host`;

if you modify the user-privileges for a specific table for example. If the user has no GRANT Option on that table the replication also breaks. MySQL throws the Error:

ERROR 1141 (42000): There is no such grant defined for user 'user' on host 'host'

And the SLAVEs go out of sync. This should really be fixed as soon as possible but MySQL-Developers marked the bug only as "S3 (Non-critical)" so it seems that we gonna have to fix the replication very often in the next months or give console access to everyone who can grant/revoke privileges.