If there is something that I love about information technology is the incredible amount of unpredictable ways that bugs can bite you where and when you less expect it.

This one I'm about to describe is a serious one, yet there has been a very quiet response from Percona bug team at Launchpad, where I first submitted it (because we use Percona server here). If you have read my other posts you know by now that I am a laz... err, busy guy so bear with me if only today I have verified that this is, in fact, an upstream bug and have raised a bug to Oracle too.
It affects 5.6.33, latest version at the time of this article.

Anyways, the bug started to manifest itself in the form of complete stalls of our main cluster, which was happening at different times and apparently in a way that was unrelated to the amount of traffic hitting the master. When stalling, system CPU time was topping 100% of total available …

A question which would come sometimes to mind when starting with MySQL is whether I should use DATETIME or TIMESTAMP data type since both appear to store same date and time component.


Similarities between datetime and timestamp:
1. Values contain both date and time parts.
2. Format of retrieval and display is "YYYY-MM-DD HH:MM:SS".
3. Can include a trailing fractional seconds part in up to microseconds (6 digits) precision.
4. With the fractional part included, the format for these values is "YYYY-MM-DD HH:MM:SS[.fraction]".
5. Both the TIMESTAMP and (as of MySQL 5.6.5) DATETIME offer automatic initialization and updating to the current …

Auf der FrOSCon 10 in St. Augustin habe ich kürzlich ein Update zu unseren Erfahrungen mit dem Thema "Private Cloud mit OpenSource" gegeben. Leider sind noch nicht alle Probleme, über die letztes Jahr berichtet wurde, behoben, aber wir sind schon ein gutes Stück weiter und haben neue Stolperfallen gefunden und z. T. auch überwunden.

Leider habe ich mich mit der Zeit ein wenig getäuscht, da ich den Talk vorher schon einmal in gekürzter Form in 40 Minuten unterbringen musste, aber in der Präsentation den Countdown für die FrOSCon wieder auf 60 Minuten zu stellen vergessen hatte. Zwischenzeitlich war ich deswegen der Meinung, ziemlich hinterherzuhängen... Hoffe, es macht trotzdem ein bisschen Spaß, so blieb am Ende mehr Zeit für Fragen und Gespräche :)

Hier noch die Folien auf Slideshare:

…

Over on the codecentric blog I just published a new post on True KVM Live Migration with OpenStack Icehouse and Ceph based VM storage, outlining the process it took me to get this actually working. There were several issues to work around, some of them bugs in OpenStack, some of them home-grown issues.

I provide a few patches to OpenStack classes as well as a description of what is actually going wrong and how to circumvent the problems. So head right over and have fun reading :)

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 …

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:

Last_SQL_Errno: 1590
Last_SQL_Error: The incident LOST_EVENTS occured on the master. Message: error writing to the binary log

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 …

This is MySQL's Bad Idea #573 (after #384, which I've blogged about before) I've just had a terrible experience with a bug report from the jOOQ User Group, related to escaping of backslashes in string literals in MySQL. First, I thought to myself, whatever. SQL doesn't escape backslashes. The only escape character within a string … Continue reading MySQL Bad Idea #573 →

Here is a fix for the MySQL/TokuDB/MariaDB bug I reported earlier today.  I think this fix is correct (it is only one line) but I don’t delve into the storage engine very often (and particularly not into ha_partition.cc) so I think it would be good to wait for Oracle (or Percona, MariaDB, or Tokutek) to validate that it is correct before using it.

diff -u ha_partition.cc /tmp/ha_partition.cc 
--- ha_partition.cc 2013-04-05 05:27:18.000000000 -0700
+++ /tmp/ha_partition.cc 2013-05-27 02:45:01.680676228 -0700
@@ -6455,9 +6455,11 @@
void ha_partition::get_dynamic_partition_info(PARTITION_STATS *stat_info,
uint part_id)
{
+
handler *file= m_file[part_id];
DBUG_ASSERT(bitmap_is_set(&(m_part_info->read_partitions), part_id));
- file->info(HA_STATUS_CONST | HA_STATUS_TIME | HA_STATUS_VARIABLE |
+ 
+ info(HA_STATUS_CONST | HA_STATUS_TIME | HA_STATUS_VARIABLE |
HA_STATUS_VARIABLE_EXTRA | HA_STATUS_NO_LOCK);
stat_info->records= …

Recently I happen to setup a new MySQL instance with my tools – a standard MySQL 5.1+, xtrabackup setup and last-hotbackup.tar.gz. To restore from the backup we used xtrabackup binaries…

The post xtrabackup_51: not found & no ‘mysqld’ group in MySQL options first appeared on Change Is Inevitable.

Send to Kindle

Got a packet bigger than ‘slave_max_allowed_packet’ bytes and binlog_format=STATEMENT|MIXED

Since version 5.1.64 MySQL introduces a new variable named slave_max_allowed_packet, which was introduced to allow large updates using row-based replication do not cause replication to fail when exceeded max_allowed_packet.

The problem is if you have you replication using binlog_format=STATEMENT or binlog_format=MIXED it ignores this option and use as limit for queries what is on max_allowed_packet variable but still reporting on slave_max_allowed_packet causing the IO_THREAD to report the wrong message.

Solution:
Run the …