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.
Saturday, June 21, 2008
Subscribe to:
Post Comments (Atom)
4 comments:
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.
In general you have to be careful with temporary tables:
http://dev.mysql.com/doc/refman/5.0/en/replication-features-temptables.html
I tried to create the temp, but I dont know why I cant do it, Ill start form zero again.
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.
Post a Comment