Suddenly your application starts throwing "error 157" and performance degrades or is non-existing. It is easy to panic then and try all sorts of actions to get past the problem. We have seen several users doing:

• rolling restart
• stop cluster / start cluster

because they also see this in the error logs:

120828 13:15:11 [Warning] NDB: Could not acquire global schema lock (4009)Cluster Failure

That is not a really a nice error message. To begin with, it is a WARNING when something is obviously wrong. IMHO, it should be CRITICAL. Secondly, the message ‘Cluster Failure’ is misleading.  The cluster may not really have failed, so there is no point trying to restart it before we know more.

So what does error 157 mean and what can we do about it?

By using perror we can get a hint what it means:

$ …

Read the original article at A CTO Must Never Do This…

A couple years back I was contacted to look at a very strange problem.

The firm ran flash sales. An email goes out at noon, the website traffic explodes for a couple of hours, then settles back down to a trickle.

Of course you might imagine where this is going. During that peak, the MySQL database was brought to its knees. I was asked to do analysis during this peak load, and identify and fix problems. Make it go faster, please!

First day on the job I’m working with a team of outsourced DBAs. I was also working with a sort of swat team chatting on SKYPE, while monitoring the systems closely.

…

When I install a MySQL package using MySQL Sandbox, if everything goes smoothly, I get an informative message on standard output, and I keep working.

This is OK

$HOME/opt/mysql/5.5.15/scripts/mysql_install_db --no-defaults \
  --user=$USER --basedir=$HOME/opt/mysql/5.5.15 \
  --datadir=$HOME/sandboxes/msb_5_5_15/data \
  --lower_case_table_names=2
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/Users/gmax/opt/mysql/5.5.15/bin/mysqladmin -u root password 'new-password'
/Users/gmax/opt/mysql/5.5.15/bin/mysqladmin -u root -h gmac4.local password 'new-password'

Alternatively you can run: …

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