I’ve been working on various different MySQL related issues and maintenance procedures some of which have not gone according to plan.  Here is a recipe that may help you avoid wasting a lot of time, especially if your database is large.

In order to do some of these tests make tests against a server configured identically to the one you plan to work on but instead which has no data. That is the mysql database needs to be complete but the other databases need to be dumped with the –no-data or -d options.  Don’t forget to also include any triggers or stored routines.

Now run the “procedure” on this “emtpy instance”. As it has no data most things run very quickly. So if you have issues you can repeat the procedure in no time. Restoring the instance too is easy as it’s tiny. This makes the whole procedure scriptable and you can be confident in the results.

Once you are …

Absolutely nothing?

5.4.0 was released with a change in the MySQL Binary distributions, delivering only 1 64bit Linux platform and two Sun Solaris platforms. This was officially announced on April 21 2009 however the 5.4.0 Release Notes state 05 April 2009. So it’s not a big deal, but consistency would be nice.

I’ve seen in a few posts 5.4.1, so I decided to try it out. Spending the time to read what’s changed in 2 months with the 5.4.1 Release Notes before I go downloading and installing, you read.

This release does not differ from 5.4.0 …

Look at MySQL bug reports of course? Well actually I’m writing multiple blog posts, and I was confirming additional reference sources and links when I came across MySQL Bug #29847 - Large CPU usage of InnoDB crash recovery with a big buf pool.

Taking the time to actually read the information exchange I stumble upon.

[8 Jun 23:29] liz drachnik

Hello Heikki - 

In order for us to continue the process of reviewing your contribution to MySQL - We need
you to review and sign the Sun|MySQL contributor agreement (the "SCA")

The process is explained here:
http://forge.mysql.com/wiki/Sun_Contributor_Agreement

Getting a signed/approved SCA on file will help us facilitate your contribution-- this
one, and others in the future.

Thank you ! 

Liz Drachnik  - Program Manager - MySQL

Oops. Well it made me laugh out loud for so many reasons. First your talking to the …

Recently with a client while running a number of disaster recovery tests I came across a nasty situation which was not part of the original plan and provided a far worse disaster situation then expected.

I should preface this condition with some of the environment conditions.

• MySQL 5.0 Enterprise 5.0.54
• RHEL 5 64bit
• Master and 2 Slaves
• MySQL Data and MySQL Binary Logs/MySQL Error Logs are on separate disk partitions

While running stress tests under high load, we tested the filling of partition containing the logs. This partition included the binary log and MySQL error log.

The observed output was.

• An error message was written to the MySQL error log. See below.
• Application throughput dropped, but did not stop.
• Binary logs stopped occuring.
• MySQL proactively stopped logging but continued to process …

As I had written earlier in Understanding InnoDB MVCC, I am trying to understand why InnoDB is taking a lock on data during an UPDATE when I do not expect this to happen.

Not wanting to go looking at the InnoDB source code to understand the problem, I’m endeavouring to possibly use existing MySQL monitoring to try and understand the problem better. In this case, I’m going to investigate SHOW ENGINE INNODB STATUS first. An old but still relevant article for reference is SHOW INNODB STATUS walk through. The MySQL High Performance book is also a good starting reference.

I’ve just installed 5.1.36 on a new Linux 64 bit laptop for this test.

For now all I’ve done is ensure the innodb_buffer_pool_size is sufficient …

Multi versioning concurrency control (MVCC) is a database design theory that enables relational databases to support concurrency, or more simply multiple user access to common data in your database.

In MySQL the InnoDB storage engine provides MVCC, row-level locking, full ACID compliance as well as other features.

In my understanding of database theory, access to modify independent sections of unique data (e.g. UPDATE) under MVCC should fully support concurrency. I have however experienced a level of exclusive locking under Innodb.

I wanted to clearly document this situation so I could then seek the advice of the guru’s in InnoDB Internals such as Mark Callaghan, Percona and the Innodb development team for example. I’m happy …

The MySQL KILL command as the name suggests kills queries that are running.

After identifying the Id using the SHOW PROCESSLIST command, the User of the connection/thread or a database user with SUPER privileges can execute KILL [id]; to remove the connection/thread.

However, there is an ability to kill just the query that is being executed rather the entire connection. The default when not specified is to kill the connection, however you can optional specify the CONNECTION or QUERY keywords.

For example, below is an interactive test.

Thread 1:

mysql> select sleep(10);

Thread 2:

mysql> show processlist;
+----+------+-----------+------+---------+------+-----------+------------------+
| Id | User | Host      | db   | Command | Time | State     | Info             | …

There are many features that are similar in MySQL and Drizzle. There are also many that are not.

I’ve previously discussed topics like Datatypes and tables, SQL_MODE and SHOW.

A key difference in Drizzle is the definition of utf8 as 4 bytes, not 3 bytes as in MySQL. This combined with no other character sets leads to an impact on the length in keys supported in Innodb.

During a recent test with a client, I was unable to successfully migrated the schema and provide the same schema due to unique indexes defined for utf8 VARHAR(255) fields.

Here is the problem. …

I was trying to demonstrate to a client how to monitor queries that generate internal temporary tables. With an EXPLAIN plan you see ‘Creating temporary’. Within MySQL you can use the SHOW STATUS to look at queries that create temporary tables.

There is the issue that the act of monitoring impacts the results, SHOW STATUS actually creates a temporary table. You can see in this example.

mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 5.1.31-1ubuntu2 |
+-----------------+
1 row in set (0.00 sec)

mysql> show global status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 48    |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 155   |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> show global status like 'created_tmp%';
+-------------------------+-------+
| …

It”s not the most common task in the world, but you might want to view processes from a particular user and once in a while you might even need to kill processes from a single user, be it during an attack or because you simply got a bug in an application bombarding your db server with connections!

Here is a small stored procedure which does exactly that!

call process_list("show","username","hostname");

– shows all processes owned by username@hostname

call process_list("kill","username","hostname");

– kills all processes owned by username@hostname

The code for this stored procedure can be found below. If you have any comments / suggestions feel free to comment below.

######################################################################
##                                        …