MySQL Bugs: #42118: CREATE TABLE TableA SELECT * FROM TableB fails because TableA doesn't exist

Bug #42118	CREATE TABLE TableA SELECT * FROM TableB fails because TableA doesn't exist
Submitted:	14 Jan 2009 16:30	Modified:	8 Oct 2010 10:07
Reporter:	Bob Hansen	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.48	OS:	Windows (7 Ultimate 64-bit)
Assigned to:		CPU Architecture:	Any
Tags:	create, does not, doesn't, error, exist, fails, SELECT, table

Description:
The following syntax fails on select occasions:

CREATE TABLE TableA SELECT * FROM TableB

The error I get is:

Table 'Schema.TableA' doesn't exist

I have had this happen about a dozen times in the last week. This morning it was
CREATE TABLE 109_0910_CourseSections SELECT * FROM 109_0809_CourseSections
This creates a 2009 table and copies 2008 contents into it. I'm positive that the 2009 tables do not exist. Interestingly, if this happens when I'm debugging, I can re-send the statement a second time and it works the second time.

How to repeat:
-Restore schema 'move' from backup
(move contains 0809 tables only, no 0910 tables)
-Run Converter program of ours
(calls many statements, mostly CREATE TABLE but also others)
-Once in a while a CREATE TABLE 0910_xx FROM 0809_xx will fail with above error

Suggested fix:
I am guessing that when you restore from backup that the database is not immediately refreshed on its own state. I think if I restore a backup and don't run the Converter program right away, (wait an hour), then this doesn't happen. Not positive though.

If you run FLUSH TABLES immediately after the restore does it work each time?  If it works after an hour, I'm guessing it's because "flush_time" is 1800 seconds on windows.  Are you restoring using physical files, or some sql statements ?

Backups are made using MySQL Administrator. Then use MySQL Administrator to restore the .sql file. Version 1.2.15

You might be on the right track with FLUSH_TABLES. The only thing about that that doesn't fit in my mind is this. I run the Converter app immediately after I do a database Restore in MySQL Administrator. The Converter calls CREATE TABLE many times, and fails on a random call not the first call. That looks something like this:

CREATE TABLE 0910_Students SELECT * FROM 0809_Students
//succeeds
CREATE TABLE 0910_Courses SELECT * FROM 0809_Courses
//succeeds
CREATE TABLE 0910_CourseSections SELECT * FROM 0809_CourseSections
//fails: 0910_CourseSections doesn't exist

If the tables were not flushed then I would expect it to fail on the very first call.

By the way, that "1 hour" figure was just a guess. All I really was saying is that this process seemed to work if I waited a while before running the Converter. If I tried to run the Converter immediately it had a higher probability of failure.

Backups are made using MySQL Administrator. Then use MySQL Administrator to restore the .sql file. Version 1.2.15

You might be on the right track with FLUSH_TABLES. The only thing about that that doesn't fit in my mind is this. I run the Converter app immediately after I do a database Restore in MySQL Administrator. The Converter calls CREATE TABLE many times, and fails on a random call not the first call. That looks something like this:

CREATE TABLE 0910_Students SELECT * FROM 0809_Students
//succeeds
CREATE TABLE 0910_Courses SELECT * FROM 0809_Courses
//succeeds
CREATE TABLE 0910_CourseSections SELECT * FROM 0809_CourseSections
//fails: 0910_CourseSections doesn't exist

If the tables were not flushed then I would expect it to fail on the very first call.

By the way, that "1 hour" figure was just a guess. All I really was saying is that this process seemed to work if I waited a while before running the Converter. If I tried to run the Converter immediately it had a higher probability of failure.

New info this morning.

I restored the database last week. It is now Monday morning. I executed the statement:

CREATE TABLE 109_0910_Lock_Prerequisites LIKE 109_0809_Lock_Prerequisites

and got the error

Table '109_0910_Lock_Prerequisites' already exists

Looking at the database, 109_0910_Lock_Prerequisites does NOT exist and 109_0809_Lock_Prerequisites DOES exist. Re-executing the statement gives the same error. Calling 'FLUSH TABLES' doesn't help. Any ideas?

Scratch that last one. It is a valid error, mistake on my part.

Thank you for the feedback.

In the last comment you said "Scratch that last one. It is a valid error, mistake on my part." Does this mean this is not a MySQL bug anymore? If not did you wait when restore finishes before running  Converter app?

In the last comment you said "Scratch that last one. It is a valid error, mistake on my
part." Does this mean this is not a MySQL bug anymore? If not did you wait when restore
finishes before running  Converter app?

"Scratch that last one" meaning that the very last comment was incorrect and wasn't meant to be left. The original problem still exists.

I have not tried the case where I do a restore, then wait, then run the Converter app. I suspect that I will get a chance to try more cases in the coming weeks. Will keep you posted. Thanks for keeping in touch.

Bob,

thank you for the feedback.

> I have not tried the case where I do a restore, then wait, then run the Converter app.

If you run the Converter app while restore is running it can happen what Converter app calls CREATE TABLE ... SELECT * FROM ... while table it selects from does not exists. It is correct behavior.

So. please test with following actions: do restore, wait when restore finishes, run Converter app and update the report.

When you say "wait when restore finishes", how long do you want me to wait?

I used the Converter once today. I dropped the database, recreated it empty, opened query browser did FLUSH TABLES, waited 15 minutes, ran Converter with no problems.

Thank you for the feedback.

> When you say "wait when restore finishes", how long do you want me to wait?

I mean don't run restore and Converter in parallel: wait when program which does restore indicate restore is complete first. Time depends from the size of your database.

> I used the Converter once today. I dropped the database, recreated it empty, opened query
browser did FLUSH TABLES, waited 15 minutes, ran Converter with no problems.

So it looks like you experienced issues, because restore and Converter were running in parallel? I close the report as "Not a Bug" for now. If you can repeat it again while you sure restore is completed before Converter started feel free to reopen the report.

I did not run the Converter while the Restore was still running.

More naughty behavior today.

I ran the Converter which makes a 0910_Students table like the 0809_Students table. Then I alter 0910_Students to add new fields, in this case I add DbBuildingNumber.

If I have any problems or have to stop to debug, I use Administrator to drop the entire database, recreate it, and restore it fresh from backup. Then I start the Converter again from the beginning.

In this case I left Query Browser and Administrator up as I went to run the Converter a second time. It created 0910_Students like 0809_Students like normally, but I noticed the first difference. I have the table names option set to option 2: "Store as created, but retrieve case insensitive". Strangely my 0910_Students table was named 0910_students (lower case 's') in the Query Browser which is unlike the rest of the tables. Double checking my sytax shows that I executed it with an upper-case 'S'.

I ignored it and continued to add the new field to 0910_Students again just like the last time. This time I got the error
"Duplicate column name 'DbBuildingNumber'"
on the execution of
ALTER TABLE movepella.0910_Students ADD COLUMN DbBuildingNumber bigint DEFAULT 0 NOT NULL AFTER IsTitle1Other

I double checked in Query Browser by doing an Edit Table on 0910_students and DbBuildingNumber is not listed as a column in the table.

So I stopped the Converter, dropped the table, and ran the Converter starting at the 0910_Students table. It created it with the proper name 0910_Students and DIDN'T have an error when it added the column.

I'm starting to wonder if this has something to do with either MySql Administrator or Query Browser putting a lock on data behind the scenes. At first I thought I was whigging out but now I'm certain that something isn't working as it should. Right now my guesses are:

1) It is time-related. If you do work items A to G, stop, clear G, then go do G again; work item G might fail. If you re-do G immediately it is most likely to fail. If you wait a while, 10 minutes or so, G will almost certainly work.

2) It might have something to do with MySQL Query Browser locking the data in G. You can be looking at the contents of a table, drop the table, you are still viewing it, and then editing the table will fail. It is another possibility although in my mind #1 seems more likely.

Thank you for the feedback.

To repeat it on your side we need repeatable test case from you. I.e. minimized dump and Converter app. Please consider how to provide it. Instructions about how to upload big files and/or upload files in such a way what nobody except MySQL developers will see them are at "Files" tab.

Getting Error in Query Browser

Attachment: reportsettings not exist.JPG (image/jpeg, text), 141.56 KiB.

Table is indeed missing

Attachment: reportsettings not exist 2.JPG (image/jpeg, text), 140.59 KiB.

Converter app in Xcode gets same error

Attachment: Xcode006.pdf (application/pdf, text), 9.90 KiB.

Backup file has been uploaded:
table-doesnt-exist-42118.zip

I am using MySQL Server 5.1.30 
and MySQL Administrator 1.2.15 
on Windows XP Pro SP2.

Thank you for the feedback.

We still need source code for Converter app, so we can be sure this is MySQL error and not error in your application.

Additionally I don't see table 0809_ReportSettings in the dump provided. Is it supposed to be there or Converter app should create it?

The Converter app should create 0809_ReportSettings. I'm not sure if I can get you the source code for the Converter app.

Actually this isn't the Converter app this is our "Office" app which is quite big and complicated. It updates the database before logging in to the main screen which is failing.

Here, this is making it happen for me every time.

Restore the database then execute

CREATE TABLE 0809_ReportSettings SELECT * FROM ReportSettings;

and I get that same error

Table 'movemontezuma.0809_reportsettings' doesn't exist

I found this in the error log this morning:

090219  8:38:30 [ERROR] Cannot find or open table movemontezuma/0809_reportsettings from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
how you can resolve the problem.

090219  8:41:29 [ERROR] Table .\movemontezuma\0809_reportsettings has a primary key in InnoDB data dictionary, but not in MySQL!
090219  8:41:29 - mysqld got exception 0xc0000005 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

Thank you for the feedback.

This error means you removed frm file while table 0809_reportsettings is still in InnoDB dictionary. I only can suggest here either dump all tables, manually remove all InnoDB files, then reload tables. Or try to create in test environment frm for 0809_reportsettings table, load it to the data directory, then issue DELETE TABLE 0809_reportsettings query.

The problem is, I don't know what a frm file is nor have I deleted any. I don't know what you mean by "remove all InnoDB files"? I do all my database changes via syntax.

To me, this error is quite unexpected and undesirable behavior. If a user has to browse a to a directory on the hard disk and make hand edits to fix something that was done by MySQL, I find it hard to believe that such behavior is not classified as a bug.

Thank you for the feedback.

But error you have in the error log means you already manually removed 0809_reportsettings.frm file while table 0809_reportsettings is still in InnoDB dictionary. 

If you believe you did not so we need repeatable test case.

I have not removed any .frm files, I only modify the database through syntax.

I do not have a good repeatable test case since this behavior is somewhat sporratic. It happens the most when I use the utility which I will be using again this summer. If I can come up with a good test case I will pass it forward. Thank you.

Thank you for the feedback.

We will wait test case from you.

This issue occurred again this morning.

I Restored a database from a starting point. I ran the converter app which creates lots of new tables. Something failed. I fixed the problem. I dropped the database. I Restored the database again. I ran the converter app and partway through I executed:

CREATE TABLE movedikenh.409_0910_CourseSections SELECT * FROM movedikenh.409_0809_CourseSections

I got the error:

Table 'movedikenh.409_0910_coursesections' doesn't exist

This seems to be common, that if the converter app doesn't succeed completely on the first pass, and I have to drop and re-create the database, that the second pass has a high likelihood of getting this error.

-----------------------

I changed syntax from
CREATE TABLE tableA SELECT * FROM tableB

to do instead
CREATE TABLE tableA LIKE tableB
INSERT INTO tableA SELECT * FROM tableB

and now it seems to be working just fine. I guess I'll just use that instead from now on.

Similar issues today. I am in a debugging situation. I am dropping the database, and then restoring it completely, over and over again. After a couple iterations it begins not restoring properly.

What I'm doing is 
1. Restore a database. Start with nothing and restore all contents using MySQL Administrator.
2. Do some work on the database. Change some field names, add some fields, change some field types, etc.
(for example, fix the spelling on a misspelled field name; "Eligable" to "Eligible")
3. Drop the database completely using MySQL Administrator.
4. Restore the database, same as before.
5. Do some work on the database, same as before. Only this time it might saying something like
"There is no field named Eligable." So I look for the field and it is actually already named Eligible. This shouldn't be the case since the restore file has it spelled as Eligable.

I think this is related to the prior issues I was having with CREATE TABLE because the database hasn't "refreshed" between DROP DATABASE and CREATE DATABASE and somehow the new database still has information from the old database.

It's a little soon to supply a test case but if I can get consistent behavior I'll be sure to get you a case. Hopefully this will ring a bell for someone as to what might be going on. Thanks.

Please, check if any of the problems discussed here is repeatable with 5.1.41, and send a test case if it is.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I now have a repeatable test case. Same behavior using 
64-bit Window 7 on MySQL 5.1.46 
as I had using 
32-bit Windows XP on MySQL 5.1.30

First restore backup database. Then run script file to create new tables using existing tables. For me it always breaks at 802_1011_Activities_Commendations. I will attach two files shortly.

The last submission was a valid error. Please wait for a better test case. Sorry about that.

Don't forget to check your repeatable test case with a recent version, 5.1.50.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".