Thursday, June 21, 2007

MySql merge table problems

While upgrading fom MySql 3 to Myql 5 our only real problem was with merge tables. We have two in the system, both did not work. Createing the merge table did not give an eror, bt when we tried to select from it, we get the error message 1168:
Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

This kind of error message gives my the creeps. Or?? Why doesn't it say which one of the cases it is? Surely these are three different cases and it should be easy to separate them. Is it difficult to add error messages to MySql? Or a lazy programmer??

OK, we try to look at the table definition i MySql's own tool the Query browser. But if you try to do 'Edit Table' in the Query Browser, you get the following error message:
A MySql eror message was encountered. The eror message is:
Cannot fetch table information
The following error occurred:Unable to open underlying table which is differently
defined or of non-MyISAM type or doesn't exist (1168)
OK, we knew that, but we wanted to take look at the definition to check that it matches the underlying table.

OK time to check for bugs. We finally find one, saying that of our three possible causes, it is actually a fourth: the table does exist, is of MyISAM type, and definitions match. But it is of an old MyISAM version... Fix is to make an ALTER TABLE on all the underlying tables, this will fix the version also, and everything is OK.

For the future I think we will look into other solutions. Merge tables are very restrictive and for our purposes a view with UNON ALL would work just as well, since we already make inserts into the underlying tables and have no updates. A quic test also says that the performance is about the same. Or, possibly, we will wait for MySql 5.1 with partitions.


Steeve Knight said...

Interesting. Further: I'm getting that error creating a merged table in a database, from 3 identical tables in yet 3 other databases. It fails on the SELECT every time. However, if I create said merged table in any of the other db, all is fine. All 4 db's in question are on the same server btw.
So, I know that all underlying tables are in fact fine. Simply (and buggily?) that 4th database just doesn't like it.

Eelkonio said...

Thanks, man! Thought you'd like to know: this little post just saved my day.