MySQL Bugs: #17113: Tables crash often, server stops and errors when restoring tables

Bug #17113	Tables crash often, server stops and errors when restoring tables
Submitted:	4 Feb 2006 11:01	Modified:	5 Sep 2006 18:38
Reporter:	Einar Strömblad	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.16	OS:	Windows (Windows Server 2003/Linux)
Assigned to:	Iggy Galarza	CPU Architecture:	Any

Description:
Connections to the server is daone remotely from clients using VB6 (ADODB), from a web-server via internal network and directly on the database-server.
Very often (daily one or more times) on or more tables crash after the uppgrade from MySql 4.xx to 5.0.16.
When restoring the crshed table(s) from the backup we also get errors in the 
restore procedure (telling us to report this bug). The error-files will be attached separately.

How to repeat:
I can send you the backup-files (sql-files) separately on request.

Thank you for the bug report. Could you please provide the
backup-files for to test on our side? You can upload the
ziped file with a name like bug17113.zip at:

ftp://ftp.mysql.com:/pub/mysql/upload

Thank you in advance.

The backup-file has been uploaded as bug17113.zip (as requested).

I was unable to find the file bug17113.zip but I found bug11713.zip
it is the file you uploaded?

thanks in advance.

You are correct. I'm sorry for the mix of figures in the file name.

Try to build a dump without the information_schema:

c:\mysql\bin>mysql -uroot -e"source c:/cygwin/home/miguel/bug11713.sql"
ERROR 1044 (42000) at line 21 in file: 'c:\cygwin\home\miguel\bug11713.sql': Access denied for user 'root'
@'localhost' to database 'information_schema'

However removing the 'information_schema' from the dump I still get the
below error:

c:\mysql\bin>mysql -uroot -e"source c:/cygwin/home/miguel/bug11713.sql"
ERROR 1146 (42S02) at line 1290 in file: 'c:\cygwin\home\miguel\bug11713.sql': Table 'kdk_se.solstatus' do
esn't exist

and I was unable to find that table/view in the dump file. Do you know
how solstatus was created/removed?

Thanks in advance.

The table `solstatus` is included in the backupfile that I sent (I keep the original here). It is both created and records are inserted into it according to statements in that file. Do I misunderstand you?

I was able to restore the database however with some manual edit of
your dump file:

- Removed the information_schema stuff.
- The table `solstatus` create statement and its insert lines i moved
   to the beginning of the script just after the database create statement.

Could you please try the above. Thanks.

I haven't been able to test your proposal, but I believe you when you say it works when editing the dump file. However, the dump file is created by MySql itself and should not have to be edited, as I see it. 
The main problem is however the frequent crashes we have on single tables every now and then as previously reported. The errors (when restoring) were discovered when restoring the one or two crashed tables from the full back up. The concerned table has been restored correctly (as it seems), but I don't know what other side effects the announced errors might have.
Also the MySql server goes down fequently. Also this was almost never happening in ver 4.xx. We also almost never had any crashed tables in Ver 4.xx.

Einar

... and by the way: Could these crashes have anything with ODBC Connector ver 3.51.12 to do? In connection with the upgrade of the database we also upgraded ODBC Connector from 3.51.11 to 3.51.12. Some of our clients, who connect directly to the MySql database over Internet via special software we have developed, might still use ver 3.51.11 on their side.
Einar

I attempted to restore the attached backup file using 5.0.23 and was able to reproduce the symptoms.  I too removed the information_schema database from the dump and moved the problem view after it's dependant table (solstatus) and then was able to restore the database.  Once the dump was restored, I created a backup with a command like:

./mysqldump -u root --all-databases > test1.sql

and was able to restore that dump with a command like:

./mysql -u root < test1.sql

successfully.  It seems like the backup/restore problems are fixed in the 5.0.23 version.

Will you please provide the command used to create this backup?  

In regard to the crashing tables, can you please provide steps to reproduce the problem?

Hi,

The problem with crashing tables has decreased lately with no known reasons (no updates and no other actions more than maybe a low season for our users).

However, we have nowadays also put in a scheduled table check that runs every 1 minute to check, and if applicable, try to repair any damaged tables.  For this reason we might also not be aware of all incidents with crashed tables. The program running the table checks/repairs is programmed in VB 6 and runs on the same server.

The backups are scheduled backups created from Mysql Administrator ver 1.1.7. No external commandline commands have been used.

Einar

PS: Will be away on holidays all July. Therefore I will not be able to respond to follow ups during that period.

I am still unable to recreate the problem with crashing tables and the backup/restore problem has been resolved.  Please provide more details for How to Reproduce the problem with crashing tables.