The latest from the MySQL community

I ran into a rather interesting situation today with a client. It seems that the mysqld daemon stopped with no errors in the error log. I ran through the obvious problems … not enough disk space, memory utilization etc and came up empty.

The server was running MySQL 4.1 on Fedora Core 5. We can save the discussion about running your database on reasonable up to date hardware and operating system for another post. Core 5 runs the GNU/Linux kernel 2.4 along with the ext3 filesystem and so the thought was in the back of my mind that it might be an issue with file size. Well, as Sun’s own documentation shows this shouldn’t be the case.

During the investigation it was uncovered that the general query log was not only enabled but 16 gigabytes in size. Aside from being so large it was absolutely useless for anything, it was the obvious culprit for …

A long standing bugAmong the many outstanding bugs for MySQL, there is one that has sparked a fierce discussion, not only in the bug report itself, but also in blogs, forums, mailing lists.
Bug #19027: MySQL 5.0 starts even with Fatal InnoDB errors was neglected for long time, until finally it got fixed, and it is available in MySQL 5.1.36.
First off, what is it about?
In short, if an engine doesn't initialize correctly, the MySQL server starts anyway, without the offending engine. Depending on how you use the failing engine, this could be either a minor annoyance or a complete disaster.
Annoyance: ARCHIVE fails to initialize, and you create tables with the default engine (usually MyISAM), and after a while you realize that the disk is filling up faster than you expected. You will find out eventually.
Disaster: ARCHIVE fails to …

This is sort of a self-documenting post, and a self-support group about ill-behaved tomcat apps. Sometimes, you have multiple nodes accesing your MySQL server (or any kind of server, for that matter) concurrently. Eventually, software in one or more of these nodes might do nasty things (you know who you are buddy:)) MySQL provides a … Continue reading iptables trick to limit concurrent tcp connections →

Related posts:

1. Using MySQL Proxy to benchmark query performance By transparently sitting between client and server on each request,...

YARPP powered by AdBistroPowered by

If the Cluster fails to start, what can you do and what to check?

Initial system start
If it is the first time you start up the cluster and it fails, then check the following:

• Network - Check /etc/hosts
  You should have (on the line for localhost)
  127.0.0.1 localhost
  and nothing else! Then the normal IP address host mapping follows..
  REDHAT and others can add a lot of other things to that line, and this results in that the nodes won't be able to connect to each other (they will be stuck in phase 0 or phase 1)
  
• Network - Check if you can ping the machines
  
• Network - Check if you have any firewalls enables ( e.g check with /sbin/iptables -L)
  Disable the firewall in that case. Exactly how depends on OS and Linux distribution.
  On Redhat systems, then SELinux might be enabled. Googling "disable firewall <your …

Last year, Erik Ljungstrom (sensei in the #ourdelta IRC channel on Freenode) created patch for a bug Arjen identified with the handling of (among others) the open-files-limit option; the patch was first included in the OurDelta build of MySQL 5.0.67.

Now, Sun engineer Guilhem Bichot has committed the (modified) patch into the 6.0-maria tree, so it should appear in 6.0 and potentially 5.4. That’s good.

See http://bugs.mysql.com/40368 for details and history.

If you are going to load a lot of records into Cluster, don't forget to set max_rows!
My colleague, Yves at BigDBAhead, has also blogged about this, but I also ran into the same problem recently.

I did try to populate 100M records on a 4 node cluster, and the data nodes went down with the following error message in the error logs:

"2304 Array index out of range"

So the error message is crap - and my opinion is that there should be a proper error message propagated up to the mysql server. There is a bug report on this.

Simplified, what the error message means is that you have run out of "index slots" in the Hash Table storing the hashes of the Primary Keys. This is because each table is divided into a number of partitions, and each partition …

In MySQL 5.1.33 there is a fix for an apparently innocuous bug.
Bug #36540 CREATE EVENT and ALTER EVENT statements fail with large server_id.
This is a usability bug, that makes the DBA life unnecessarily hard. The reason for having a large server_id is because a DBA might want to use the IP address as server ID, to make sure that there are unique IDs, and to have an easy way of identifying the server through the IP.
All is well until you mix the server_id assignment with event creation:

 select version();
+-----------+
| version() |
+-----------+
| 5.1.32    | 
+-----------+
1 row in set (0.00 sec)

 set global server_id =inet_aton('192.168.2.55');
Query OK, 0 rows affected (0.00 sec)

 select @@server_id;
+-------------+
| @@server_id |
+-------------+
| …

It’s a fairly simple rule, and something that should be obeyed for your health and sanity.

There are a couple of bugs which you could run into, when quoting large numbers. First of all, Bug #34384. This is concerning quoting large INTs in the WHERE condition of an UPDATE or DELETE. It seems that this will cause a table scan, which is going to be slooooow on big tables.

Similarly, there is the more recently discovered Bug #43319. You can run into this if you quote large INTs in the IN clause of a SELECT … WHERE. For example:

mysql> EXPLAIN SELECT * FROM a WHERE a IN('9999999999999999999999')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        TABLE: NULL
         type: NULL
possible_keys: NULL
          KEY: NULL
      key_len: NULL
          ref: …

At a customer yesterday, I confirmed what Jonas suspected and what is probably related to bug 42474. Scroll down and look for the output of SHOW PROCESSLIST.
It seems that TRIGGERs causes Table Locks to be taken when used with Ndb cluster tables!! I have created a bug report .

If we do an update on a table that has an update trigger the trigger will upon execution lock the entire table that is affected by the trigger.

This was verified by having several threads updating random records in a table. When one update gets to execute, the trigger will block the other updates from happening. This was shown using SHOW FULL PROCESSLIST¸ which shows a bunch of statements being in …

Recently my attention was brought to this bug which is a nightmare bug for any consultant.

Working with production systems we assume reads are reads and if we're just reading we can't break anything. OK may be we can crash the server with some select query which runs into some bug but not cause the data loss.

This case teaches us things can be different - reads can in fact cause certain writes (updates) inside which add risk, such as exposed by this bug.

This is why transparency is important - to understand how safe something is it is not enough to know what is this logically but also what really happens inside and so what can go wrong.

Entry posted by peter | 19 comments

Add to: …