Saturday, June 21, 2008

Creation of Temp Table Anomaly

One of our replication slave broke last night. This is another strange one. I have multiple slaves running under version 4.1.19 (yup, this is an old one, hehe). But for some strange reason, only one replication slave exhibited the following behavior: It would seem like it cannot create a temporary table while other replication slaves have no problem with it. See below for the example.

mysql> create temporary table abc (id int);
ERROR 1046 (3D000): No database selected

mysql> \u customer
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> create temporary table abc (id int);
ERROR 1049 (42000): Unknown database 'customer'

mysql> create table abc (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> \u test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> create temporary table abc (id int);
ERROR 1049 (42000): Unknown database 'test'

mysql> create table abc (id int);
Query OK, 0 rows affected (0.01 sec)


As you can see, creating permanent tables is not a problem. But creating temporary tables on different databases is an issue. I wonder if anyone has seen this? And take note that only one replication slave is showing this error, while other slaves are working just fine.

4 comments:

Anonymous said...

It looks like the tmp directory was physically missing. Once this was created, everything became alright.

What was weird was when I tried to bounce the database, it did not report that the tmp directory was missing.

Also, one would think that since it is a temporary table that MySQL will not use a physical file unless needed.

And the SQL error was misleading as well, Unknown database 'customer'? But in reality it was the tmp directory that was missing.

Sheeri K. Cabral said...

In general you have to be careful with temporary tables:

http://dev.mysql.com/doc/refman/5.0/en/replication-features-temptables.html

Generic Cialis said...

I tried to create the temp, but I dont know why I cant do it, Ill start form zero again.

Robert said...

THANK YOU! I was tearing my hair out last night trying to track down that "unknown database" error. Today I narrowed it to the CREATE TEMPORARY TABLE statement, and then I found your blog. You were spot on -- I had deleted MySQL's "temp" directory.