MySQL Bugs: #25101: MySQL server keeps crashing unexpectedly

Bug #25101	MySQL server keeps crashing unexpectedly
Submitted:	15 Dec 2006 17:30	Modified:	24 Apr 2007 14:00
Reporter:	Bob Dankert	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	5.0.27	OS:	Windows (Windows 2003 Enterprise x86)
Assigned to:		CPU Architecture:	Any
Tags:	crash, MySQL

Description:
I have been dealing with issues on our MySQL server crashing several (5-10, sometimes more) times per day unexpectedly.  This happened ever since the server unexpectedly reboot.  I have gone through every database on the server (about 20-30 databases) and performed an extended ananlyze table and repair table on every table in every database and this has not helped (there no problems beign reported anymore).  I have moved the database server to another Windows server and this has not helped either (the data was moved to the new server).  I have since moved it to a third brand new Windows 2003 Enterprise Server and this has not helped - the process simply crashes unexpectedly on each box.  There is nothing recorded in the .err file in the data directory for the server and there is nothing recorded in the event viewer other than an error from the Service Control Manager with text like "The MySQL service terminated unexpectedly.  It has done this 7 time(s)."  I am sure the problem is related to some sort of problem with the data somewhere, but I can not find it and it is crashing without any errors or symptoms.

How to repeat:
Run a MySQL server with our data.

Suggested fix:
Not sure

I should elaborate on my comment that there is "nothing" recorded in the .err file:  There is stuff recorded, but nothing that appears useful.  There is nothing at the time of the crash, only when it comes back up.  When it comes back up, it has standard stuff after a crash, such as:

061215 11:18:11  InnoDB: Started; log sequence number 0 419363729
061215 11:18:11 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections.
Version: '5.0.27-community-nt'  socket: ''  port: 3306  MySQL Community Edition (GPL)
061215 11:20:22  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
<and additional lines of crash recovery work>

As an added note, the server crashes nearly 100% of the time when I drop a schema from the database (using MySQL Administrator to drop the schema).

Thank you for the bug report. I assume you will able to set up a test machine
to repeat this issue again and starting the server as standalone than as
service and enabling the General Query log. So:

Edit your my.ini file to add a line with the query log variable, i.e:

C:\Arquivos de programas\MySQL\MySQL Server 5.0>cat my.ini | findstr log=
log=c:/temp/query-log.txt

On my side when the server start creates a file named query-log.txt in
the c:\temp directory.

The above is with the aim to tracking a possible query which cause the
server crash.

Be sure the MySQL service is stopped and open a DOS prompt screen and go
to the install \bin directory. Start the server i.e:

C:\Arquivos de programas\MySQL\MySQL Server 5.0\bin>mysqld-nt --defaults-file="c:\Arquivos de programas\MySQL\MySQL Server 5.0\my.ini" --standalone --console
061215 16:06:08  InnoDB: Started; log sequence number 0 15268117
061215 16:06:08 [Note] mysqld-nt: ready for connections.
Version: '5.0.27-community-nt-log'  socket: ''  port: 3307  MySQL Community Edition (GPL)

Now begin to use the server as you did before and looking the above screen
for any error/message displayed.

If the crash happens, look in the query log file for the last query and
try to reproduce the crash with the same query.

Thanks in advance.

Following the instructions, MySQL crashed while dropping one of the schema's (drop schema from MySQL Administrator).  This is a schema that was just created by restoring data from a previous backup created by MySQL Administrator.  The result in the console was:

InnoDB: Error: trying to access page number 4205479978 in space 0,
InnoDB: space name .\ibdata1,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10.
InnoDB: If you get this error at mysqld startup, please check that
InnoDB: your my.cnf matches the ibdata files that you have in the
InnoDB: MySQL server.
061215 15:09:16InnoDB: Assertion failure in thread 2348 in file .\fil\fil0fil.c
line 3959
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: about forcing recovery.
InnoDB: Thread 3172 stopped in file .\os\os0sync.c line 487
InnoDB: Thread 1580 stopped in file .\sync\sync0arr.c line 126

I have looked at the page referenced and am guessing that this may be due to corruption in my InnoDB tablespace?  It seems weird that MySQL would let this happen when running as a service without generating any errors either in an error log or in the event viewer.

Thank you for the feedback. It is possible for you to provide the backup
file when restored and a database deleted provokes that InnoDB engine
assert (you can attach the zipped file as private which only MySQL
developers will have access)?.

Also have you verified in the data directory if there is a log error
file generally called host_name.err and if the same error message
was logged?

Thanks in advance.

Well, it turned out that it seems everything related to InnoDB is corrupt.  Updating any table that is using the InnoDB storage engine (insert record, delete record, modify the table, etc...) causes the MySQL server to crash with this error.  The crash is not immediate but a few seconds after the change is made.  After going through all of my databases and converting all of the tables from InnoDB to MyISAM I found that MySQL no longer crashes no matter what I do to it now, so I am convinced it is a InnoDB problem.  

There is an .err file, but the error displayed previously is not recorded to this file.  

After changing the storage engine from InnoDB to MyISAM on all of the tables I tried creating new InnoDB based tables to see if it would have the same problem.  As soon as I created a couple tables and deleted the tables the MySQL server crashed (again, it takes a few seconds for it to crash so I am not sure which command caused the crash).  

After reading about the InnoDB storage engine, I see reference to a my.cnf file, however I do not have a file by this name anywhere in the file system the server resides on.  Could this possibly be related to the problem?

Bob

Thank you for the feedback. To try to repeat this issue I still need
a dump of the database affected and queries samples which provokes
the crash (you can enable the query log) and the my.ini
file. Thanks in advance.

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".

It is still doing this, I just converted a table from MyISAM to InnoDB and it crashed within seconds (before I could run an insert).  I have uploaded the my.ini as well as a dump of this database and the command I used to crash the server (although I can crash it using lots of methods).

Database backup using MySQL Adminitrator - currently in MyISAM Format

Attachment: test-db-backup (text/plain), 1.92 KiB.

(Previous file, the SQL file, meant to say DB is in InnoDB format, not MyISAM) These show how to crash the TEST database

Attachment: crash-queries.txt (text/plain), 282 bytes.

The my.ini file for the server

Attachment: my.ini (application/octet-stream, text), 9.04 KiB.

Thank you for the feedback. If you are still using the version
5.0.27 could you please upgrade to latest released version,
try again and comment the results. Thanks in advance.

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.0.37-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Database changed
mysql> show create table a\G
*************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `a` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> ALTER TABLE `test`.`a` ADD COLUMN `b` VARCHAR(45) NOT NULL AFTER `a`,
    -> ENGINE = InnoDB ROW_FORMAT = DYNAMIC;
Query OK, 5 rows affected (0.18 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> update test.a set a = 'f' where id = 1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

I have upgraded to 5.0.37 and have the same problem.  When I change the engine from MyISAM to InnoDB it crashes and when I change it back from InnoDB to MyISAM it crashes.  Once it is MyISAM it no longer crashes, however.  When it is InnoDB if I do anything such as an alter table, update, or insert the server will crash, but queries do not crash it (same behavior I expereienced previously).  Again, the crash does not happen immediately but after 2 or 3 seconds.

Log (the first update I tried I typed the wrong table name):

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10 to server version: 5.0.37-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Database changed
mysql> ALTER TABLE `test`.`a` ENGINE = InnoDB;
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> update test set a = 'g' where id = 1;
ERROR 1146 (42S02): Table 'test.test' doesn't exist
mysql> update a set a = 'g' where id = 1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2003 (HY000): Can't connect to MySQL server on 'krustytheclown' (10061)
ERROR:
Can't connect to the server

mysql>

Thank you for the feedback. Still I am not able to repeat the behavior
reported:

mysql> ALTER TABLE `test`.`a` ENGINE = InnoDB;
Query OK, 5 rows affected (0.38 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> update a set a = 'g' where id = 1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.40    |
+-----------+
1 row in set (0.00 sec)