MySQL Bugs: #14582: Getting a message that Table doesn't exist after upgrading to 5.0.15

Bug #14582	Getting a message that Table doesn't exist after upgrading to 5.0.15
Submitted:	2 Nov 2005 17:32	Modified:	3 Nov 2005 15:20
Reporter:	Rob Simmermon	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.15-standard	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
I just upgraded mysql-standard-5.0.11-beta-linux-i686 to mysql-standard-5.0.15-linux-i686.  For some reason there are several tables in one of my schemas that get listed in MySql Query Browser and PHP MyAdmin.  However, when clicking on those tables, or trying to query them, I get an error that Table "table_name" doesn't exist.

Can someone, anyone please suggest something.  I don't know for sure, but it may be all the InnoDB tables that are doing this.  I can't remember which ones were InnoDB and which were MyISAM.

Thanks for any help.

How to repeat:
Not sure

Suggested fix:
Don't know

Thank you for a problem report. Please, check filenames in your database directory, like that (for test database):

[openxs@Fedora 5.0]$ ls var/test
TESTCASE.MYD             my_tables.frm  tbi.MYD     tt.MYD
TESTCASE.MYI             pretty_t.MYD   tbi.MYI     tt.MYI
TESTCASE.frm             pretty_t.MYI   tbi.frm     tt.frm
Table1.frm               pretty_t.frm   tbl1.MYD    ttccom100090.frm
fleets.frm               pretty_v.frm   tbl1.MYI    ttccom120090.frm
history.MYD              road.MYD       tbl1.frm    ttccom122090.frm
history.MYI              road.MYI       td.MYD      ttdpur400090.frm
history.frm              road.frm       td.MYI      ttdpur401090.frm
klient.frm               t.MYD          td.frm      ttt.MYD
konto.frm                t.MYI          test.MYD    ttt.MYI
link.MYD                 t.frm          test.MYI    ttt.frm
link.MYI                 t1.frm         test.frm    users.frm
link.frm                 t2.MYD         test2.MYD   x.MYD
log_cookie_fragment.MYD  t2.MYI         test2.MYI   x.MYI
log_cookie_fragment.MYI  t2.frm         test2.frm   x.frm
log_cookie_fragment.frm  table1.MYD     test_1.MYD  y.MYD
logclients.MYD           table1.MYI     test_1.MYI  y.MYI
logclients.MYI           table1.frm     test_1.frm  y.frm
logclients.frm           table2.MYD     test_2.MYD  z.MYD
my_tables.MYD            table2.MYI     test_2.MYI  z.MYI
my_tables.MYI            table2.frm     test_2.frm  z.frm 

Those tables that have *.frm correspondent file only may be InnoDB ones, others are surely MyISAM ones. Please, guess the engine for the missed tables based on this suggestion.

Please, describe the exact steps you performed to upgrade. Additional information about your harware and OS (uname -a resuts, at least) may be useful too.

I've attached a picture that lists all of the tables that are not being shown.  It does appear to only be InnoDB tables.  The tables that are not being shown are showing up as Views in "MySql Administrator" as you can see from the picture.

Here's how I did the upgrade.
I had 5.0.11 beta working fine.
I untarred the 5.0.15 in /usr/local
I shutdown mysql.
I removed the mysql link and recreated to point to the new Mysql 5.0.15 dir.
I copied the "data" dir from the mysql 5.0.11 dir to the 5.0.15 mysql dir, overwriting the one that was there.
I then followed all steps in the INSTALL-BINARY as follows:
     shell> cd mysql
     shell> scripts/mysql_install_db --user=mysql
     shell> chown -R root  .
     shell> chown -R mysql data
     shell> chgrp -R mysql .

Then I started mysql by running "/etc/init.d/mysql.server start"

I'm sorry, I forgot to include 

Linux trxfw.telelect.com 2.4.20-28.8bigmem #1 SMP Thu Dec 18 11:50:42 EST 2003 i686 i686 i386 GNU/Linux

It's Redhat Linux 8.

Thank you for the additional information. This step:

shell> scripts/mysql_install_db --user=mysql

is not needed when you use the old data (upgrade). It is for fresh installation only...

So, please, copy your data directory from 5.0.11 to 5.0.15 once more and try to work with your tables. Inform about the results.

Actually I already fixed it another way since it was very critical to get running.  I recreated the empty tables using standard SQL create syntax.  Then I copied the files for the InnoDB tables back to the data directory in the proper place.  Then it worked.

Thank you for following up with this, so I know next time what to do.

Closed as not a bug, but just a wrong sequence of actions while upgrading. Fixed by the bug reporter.