I recently came across a dev VM running MySQL 5.0.77 (an old release, 28 January 2009) that didn’t have InnoDB available. skip-innodb wasn’t set, SHOW VARIABLES LIKE '%innodb%' looked as expected, but with one exception: the value of have-innodb was DISABLED.

I confirmed this with SHOW ENGINES:

(root@localhost) [(none)]> show engines;
+------------+----------+----------------------------------------------------------------+
| Engine     | Support  | Comment                                                        |
+------------+----------+----------------------------------------------------------------+
| MyISAM     | DEFAULT  | Default engine as of MySQL 3.23 with great performance         |
| MEMORY     | YES      | Hash based, stored in memory, useful for temporary tables      |
| InnoDB     | DISABLED | Supports transactions, row-level locking, and …

The attached storage used by Amazon’s managed Relational Database Service has a known issue where the bytes per inode ratio is very high (default on RHEL5 systems is 4096, to be found in /etc/mke2fs.conf). Amazon does not allow any administrative access to these instances so there is no way to reformat the filesystem to allocate more inodes, or attach storage the user can format with a different ratio.

This becomes problematic for databases that have many small tables (generally MyISAM tables, or InnoDB with the innodb_file_per_table setting) which quickly consume the available inodes. When the inode allocation is exhausted MySQL responds with

"ERROR 1030 (HY000): Got error 28 from storage engine"

The only current solution is to increase the size of attached storage, which increases the number of inodes (at the same …

Here’s an old-but-still-relevant (re)post from Major Hayen on improving MySQL performance. It’s a neat, concise reference guide for MySQL emergencies!

Original post

We added a 500GB 7.2K SATA/300 Hitachi Deskstar E7K500 16MB disk to one of our dev servers and partitioned using fdisk and formatted the partition with etx3. When we tried mounting the same, we got the following error :

[root@xyz user]# mount -t ext3  /dev/sdb1 /mysql
mount: /dev/sdb1 already mounted or /mysql busy

lsof didn’t provide any open files that might be linked to this problem or there was any “famd” running. Finally doing the following steps to remove the logical devices from the device-mapper driver helped us fix the problem.

[root@xyz user]# dmsetup ls
ddf1_44656c6c202020201028001510281f033832b7a2f6678dab   (253, 0)
ddf1_44656c6c202020201028001510281f033832b7a2f6678dab1  (253, 1)

[root@xyz user]# dmsetup remove ddf1_44656c6c202020201028001510281f033832b7a2f6678dab1
[root@xyz user]# dmsetup ls
…

When you are importing large amounts of data from other sources LOAD DATA is a common method of inserting data into a table.
It is one of the old commands implemented in MySQL. As such it is very fast, and it has been optimized for both MyISAM and InnoDB.
All is well when you are loading data into a standalone server. All is almost well when you are using replication. LOAD DATA used to be a problem in old versions of MYSQL, prior to 4.1. With recent versions of MySQL, it is replicated correctly, and sometimes efficiently.
The trouble starts when the data file is big. The definition of big and the amount of trouble that you can get depends on many factors. That's why users may not realize that this problem exists, even with fairly large files, and then being hit by this disaster when the file is only a little larger than the previous …

Here is another interesting problem:When trying to set up master to master replication from Server A to Server B, the replication user can login from A to B, but not the other way. The error thrown out by MySQL:ERROR 1130 (HY000): Host '' is not allowed to connect to this MySQL serverThings to check:1. Replication user password and host2. Ability to ping the remote server3. nslookup the remote

Sometimes you just have to laugh at the crazy things that can kill a good evening. I had this brilliant idea to change our replication setup on one of our Master-Master replication server setups this week. I got sick of having to restart MySQL every time we wanted to add a new database and have it included in the list of replicated databases - we were using replicate-do-db in our configs.So it

It's always fun when you come into work and notice that one of your database monitors/graphs has changed from showing almost no row accesses/sec to 40,000 rows/sec in a matter of minutes. And then by lunch time they are showing 90,000 rows/sec. What's up with that?MySQL Enterprise Monitor Row Accesses:Well, of course the first thing you do is check all your cacti monitors, because there is

Wheel of protocol, turn turn turn.
Tell us the lesson that we should learn. (with apologies to the original source)

Writing a book comes with many challenges. For me, writing a MySQL book for MySQL beginners, who may or may not be database beginners, has fed my compulsion to research and test bizarre interactions.

Today’s lesson is on what protocol is used when connecting to a local mysqld instance on a non-Windows machine. The TCP/IP protocol is used by default when connecting on a Windows machine, and connecting from any operating system to a non-local mysqld instance. I am assuming the connections are being made by a command line client such as mysql, mysqladmin or mysqldump. Connections made via …

I was working on a client’s server today to troubleshoot some variances between the result timing of some queries. Guess what I came across - the profiler is not available in certain enterprise releases but it is available on community versions of the same release number.

I can understand if that feature was something that wasn’t fully tested in the enterprise code base and thus was only released in the community version - but if that’s the case then I don’t understand why the same version releases of Community and Enterprise can have different feature sets. That goes against the whole idea of versioning. Someone correct me if I’m wrong here but that is very frustrating.