Tuesday, September 23, 2008

MySQL Cluster: No more room in index file

Recently we were migrating an InnoDB/MyISAM schema to NDB. I was too lazy to calculate all the needed MySQL Cluster parameters (for example with ndb_size.pl) and just took my default config.ini template.
Because I am really lazy I have a little script doing this for me (alter_engine.sh).

But suddenly my euphoria was stopped abruptly by the following error:

MySQL error code 136: No more room in index file

The usual command that helps me in such a situation is a follows:

# perror 136
MySQL error code 136: No more room in index file

But in this case it is not really helpful. Also

# perror --ndb 136

does not bring us further. Strange: Index file... We are converting from MyISAM/InnoDB to NDB. Why the hell is he using an index file for this operation? It seems to be clearly a mysqld error message and not a MySQL Cluster error message. And we are also not using MySQL Cluster disk data tables.

After bothering a bit MySQL support I had the idea to do the following:

# ndb_show_tables | grep -ic orderedindex
127

The MySQL online documentation clearly states:

MaxNoOfOrderedIndexes
...
The default value of this parameter is 128.

So this could be the reason! When I have changed this parameter followed by the common rolling restart of the MySQL Cluster I could continue to migrate my schema into cluster...

Conclusion
MySQL errors can be related to cluster errors and do not necessarily point to the source of the problem. The error:

MySQL error code 136: No more room in index file


means just MaxNoOfOrderedIndexes is too small!


I hope that I can safe you some time with this little article.

3 comments:

Matthew Montgomery said...

After any error in mysqld dealing with NDB tables you should always run "SHOW WARNINGS". MySQL will only show the last error, which is the mysqld error, "no more room in index file" at execution time. This means that the NDB error which bubbled up to mysqld won't be displayed unless you use the "SHOW WARNINGS" command.

Matthew Montgomery said...

In this example I lowered the MaxNoOfOrderedIndexes to 25 and created a bunch of tables, then I got.

mysql> create table foo23 like foo1;
ERROR 1005 (HY000): Can't create table './test/foo23' (errno: 904)
mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------+
| Error | 1296 | Got error 904 'Out of fragment records (increase MaxNoOfOrderedIndexes)' from NDB |
| Error | 1005 | Can't create table './test/foo23' (errno: 904) |
| Error | 6 | Error on delete of './test/foo23.MYI' (Errcode: 2) |
+-------+------+-----------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

SHOW WARNINGS really makes a big difference in figuring out what the errors 'really' mean.

Shinguz said...

Hi Matthew

Thanks for your comment. This could have helped a lot. I do NOT ask why there was no message like:

Query OK, 1 row affected, 1 warning (0.10 sec)

or why the real cause was not displayed at all...