MySQL Bugs: #12319: mysqld.exe crashes on SHOW TABLE STATUS FROM `databasename`;

Bug #12319	mysqld.exe crashes on SHOW TABLE STATUS FROM `databasename`;
Submitted:	2 Aug 2005 14:19	Modified:	26 Aug 2005 9:34
Reporter:	MySQL-Front Team	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.10a-beta	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
Sometimes SHOW TABLE STATUS FORM `databasename`; forces a crash of the mysqld.exe.

Unfortunately I don't have a reproduceable procedure for this.

Within 5.0.9-beta this problem didn't occurs.

Inside the database "databasename" I create a trigger - I'm unsure, if this relates to this problem, since I saw this problem only on this database...

How to repeat:
???

Could you please write here table definition and command to create trigger ?

The example from the MySQL manual:

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  b4 INT DEFAULT 0
);

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;  
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END;

After this problem occurs one time I can restart mysqld.exe - but the problem will occurs further more some times. (I can't find out how often...)

While this problem is present this bug occurs additional: http://bugs.mysql.com/12320

Thank you for your bug report. I tried to reproduce your test case both on 5.0.9-beta (you are right, problem, did not occured there), and on the latest 5.0.11-beta (still can't repeat):

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

mysql> use test
Database changed
mysql> CREATE TABLE test1(a1 INT);
CREATE TAQuery OK, 0 rows affected (0.02 sec)

mysql> BCREATE TABLE test2(a2 INT);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREQuery OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE test4(
    ->   a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   b4 INT DEFAULT 0
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //
mysql> CREATE TRIGGER testref BEFORE INSERT ON test1
    ->   FOR EACH ROW BEGIN
    ->     INSERT INTO test2 SET a2 = NEW.a1;
    ->      DELETE FROM test3 WHERE a3 = NEW.a1;
    ->     UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
    ->   END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> SHOW TABLE STATUS FROM `test`;
+-------+--------+---------+------------+------+----------------+-------------+-
-----------------+--------------+-----------+----------------+------------------
---+---------------------+------------+-------------------+----------+----------
------+---------+
| Name  | Engine | Version | Row_format | Rows | Avg_row_length | Data_length |
Max_data_length  | Index_length | Data_free | Auto_increment | Create_time
   | Update_time         | Check_time | Collation         | Checksum | Create_op
tions | Comment |
+-------+--------+---------+------------+------+----------------+-------------+-
-----------------+--------------+-----------+----------------+------------------
---+---------------------+------------+-------------------+----------+----------
------+---------+
| test1 | MyISAM |      10 | Fixed      |    0 |              0 |           0 |
1970324836974591 |         1024 |         0 |           NULL | 2005-08-02 19:09:
27 | 2005-08-02 19:09:27 | NULL       | latin1_swedish_ci |     NULL |
      |         |
| test2 | MyISAM |      10 | Fixed      |    0 |              0 |           0 |
1970324836974591 |         1024 |         0 |           NULL | 2005-08-02 19:09:
27 | 2005-08-02 19:09:27 | NULL       | latin1_swedish_ci |     NULL |
      |         |
| test3 | MyISAM |      10 | Fixed      |    0 |              0 |           0 |
1970324836974591 |         1024 |         0 |              1 | 2005-08-02 19:09:
27 | 2005-08-02 19:09:27 | NULL       | latin1_swedish_ci |     NULL |
      |         |
| test4 | MyISAM |      10 | Fixed      |    0 |              0 |           0 |
2533274790395903 |         1024 |         0 |              2 | 2005-08-02 19:09:
27 | 2005-08-02 19:09:27 | NULL       | latin1_swedish_ci |     NULL |
      |         |
+-------+--------+---------+------------+------+----------------+-------------+-
-----------------+--------------+-----------+----------------+------------------
---+---------------------+------------+-------------------+----------+----------
------+---------+
4 rows in set (0.02 sec)

So, I just recommend you to use 5.0.11.

Like reported this bug occurs randomly.

In this case only to write "can't repeat" it the worsted kind handling a crash of the database.

I CAN'T BELEAVE THIS IS YOUR KIND HANDLING CRITICAL BUGS!!!

Please, be patient.

"In this case only to write "can't repeat" it the worsted kind handling a crash
of the database."

I tried to reproduce it and had no luck, using all the information you provided. "Sometimes" is "never" for any developer - how they should reproduce it? So, we, you and me, have to try to create a repeatable, simple test case. Mostly you, because I see no bugs in your case.

Do you have any .err files in the data directory? MySQL should write something into its error log in case of crash. See http://dev.mysql.com/doc/mysql/en/error-log.html for details.

I need your feedback on any additional steps you do when this crash "sometimes" occures.

I still recommend you to upgrade to 5.0.11, though.

Error log

Attachment: desktop.err (application/octet-stream, text), 22.28 KiB.

I'm not sure how to submit a file - I hope it works...

Yes, i've got the file desktop.err you successfully uploaded. According to the log, you started using 5.0.10a-beta today. Am I right?

---
050802 11:36:22 [Note] C:\Programme\MySQL\bin\mysqld.exe: ready for connections.
Version: '5.0.9-beta'  socket: ''  port: 3306  Official MySQL binary
050802 13:21:50 [Note] C:\Programme\MySQL\bin\mysqld.exe: Normal shutdown

050802 13:21:50  InnoDB: Starting shutdown...
050802 13:21:54  InnoDB: Shutdown completed; log sequence number 0 385219404
050802 13:21:54 [Note] C:\Programme\MySQL\bin\mysqld.exe: Shutdown complete

050802 13:24:19  InnoDB: Started; log sequence number 0 385219404
050802 13:24:19 [Note] C:\Programme\MySQL\bin\mysqld.exe: ready for connections.
Version: '5.0.10a-beta'  socket: ''  port: 3306  Official MySQL binary

runtime error R6025
- pure virtual function call
050802 15:38:19  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
050802 15:38:20  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 385225017.
InnoDB: Doing recovery: scanned up to log sequence number 0 385225017
InnoDB: Last MySQL binlog file position 0 0, file name 
050802 15:38:20  InnoDB: Started; log sequence number 0 385225017
050802 15:38:20 [Note] C:\Programme\MySQL\bin\mysqld.exe: ready for connections.
Version: '5.0.10a-beta'  socket: ''  port: 3306  Official MySQL binary

runtime error R6025
- pure virtual function call
050802 15:38:44  InnoDB: Database was not shut down normally!
---
So, until 13:21 it was 5.0.9, then you installed 5.0.10 (5.0.11 was already available at that time from MySQL...). And your "sometimes" crashes happened around the times when "runtime error R6025" appears in the log?

Still recommend you to try 5.0.11 (There are other bugs fixed in it in any case)
Please, reread our bug reporting advisory: http://bugs.mysql.com/how-to-report.php. You need to follow it to get useful answers and bug fixes.

> you started using 5.0.10a-beta today. Am I right?

Yes.

> So, until 13:21 it was 5.0.9, then you installed 5.0.10 (5.0.11 was
> already available at that time from MySQL...).

On http://dev.mysql.com/doc/mysql/en/ the 5.0.10 is marked as "Not yet released" - but I found them on http://dev.mysql.com/downloads/mysql/5.0.html.

I don't know how to find the 5.0.11.

> And your "sometimes" crashes happened around the
> times when "runtime error R6025" appears in the log?

Yes.

> Please, reread our bug reporting advisory:
> http://bugs.mysql.com/how-to-report.php. 
> You need to follow it to get useful answers and bug fixes.

Hey guy: Before you write something like this you should make sure the bug reporters are able to find newer releases!

I follows MySQL recommention only using the compiled versions and not to compile my own server.

... seems your are not interessed to find this bug. This is Ok for me - I will use the 5.0.9 and I will wait you fixed this bug.

Sorry, I thought MySQL is interessed to get bug notifications - I understand you: I'm wrong in this case.

MySQL-Front Team,

The latest pre-built build available is 5.0.10a, from http://dev.mysql.com/downloads/mysql/5.0.html . I see that you already have that.

It appears that this bug might be the one which was very recently reported and fixed in bug 11633 ( http://bugs.mysql.com/bug.php?id=11633 ). I don't see a note that it has been fixed in the changelog for 5.0.10a so it appears that it didn't make it before the last code change date for that build.

Because we think this has already been fixed, it would be helpful if you could either test with 5.0.11 when it is released or with your own compiled version of the latest version from BitKeeper. Intructions for getting that are at http://dev.mysql.com/doc/mysql/en/installing-source-tree.html . Has to be from BitKeeper because it appears that it is not yet in any full release build.

Thanks for your report and we'll appreciate any assistance you can offer in confirming that it does or doesn't still exist in 5.0.11 once it is available.

I was unable to reproduce this problem using 5.0.10 and the latest pull from our 5.0bk tree in both winxp & linux.

Can you please upload your full data directory, as well as your my.ini file to see if that helps reproducing the problem ?

Liken mentioned this bug only occur randomly. Because of this it's hard to test: Not only for me - also it's very hard for you. I know this problem!

Since I know this all I can do for you is offering all informations about the bug I know / I think which could relate to this problem.

Normally I update MySQL with each new release.

This bug occured on my end starting from 5.0.10a. All were fine untiln 5.0.9.

Maybe this bug relates to the Trigger queries - I never used them within 5.0.9 since the SHOW TRIGGER was missing.

Last evening I worked with creating / dropping Triggers several times - but this bug doesn't occured a further time. :-(

If I find out anything else about this problem I will let you know this.

PS
It's no problem for me to wait for 5.0.11. I know 5.0.10 is still a beta release.

Database I used for the triggers. But the SHOW TABLES STATUS failed on this and all other databases.

Attachment: test.zip (application/zip, text), 5.44 KiB.

Thanks for database, but unfortunately I was not able to reproduce the bug on your data. 

mysql> show table status from test;
+-------------+--------+---------+------------+------+----------------+---------
----+------------------+--------------+-----------+----------------+------------
---------+---------------------+---------------------+-----------------+--------
--+----------------+-----------------------------------------+
| Name        | Engine | Version | Row_format | Rows | Avg_row_length | Data_len
gth | Max_data_length  | Index_length | Data_free | Auto_increment | Create_time
         | Update_time         | Check_time          | Collation       | Checksu
m | Create_options | Comment                                 |
+-------------+--------+---------+------------+------+----------------+---------
----+------------------+--------------+-----------+----------------+------------
---------+---------------------+---------------------+-----------------+--------
--+----------------+-----------------------------------------+
| cms_content | NULL   |    NULL | NULL       | NULL |           NULL |        N
ULL |             NULL |         NULL |      NULL |           NULL | NULL
         | NULL                | NULL                | NULL            |     NUL
L | NULL           | Table 'test9.cms_content' doesn't exist |
| test1       | MyISAM |      10 | Fixed      |    0 |              0 |
  0 | 1970324836974591 |         1024 |         0 |           NULL | 2005-08-02
17:35:07 | 2005-08-02 13:35:00 | NULL                | utf8_general_ci |     NUL
L |                |                                         |
| test3       | MyISAM |      10 | Fixed      |    0 |              0 |
  0 | 1970324836974591 |         1024 |         0 |              1 | 2005-08-02
17:35:07 | 2005-08-02 13:35:00 | NULL                | utf8_general_ci |     NUL
L |                |                                         |
| test4       | MyISAM |      10 | Fixed      |    0 |              0 |
  0 | 2533274790395903 |         1024 |         0 |              2 | 2005-08-02
17:35:08 | 2005-08-02 13:35:00 | NULL                | utf8_general_ci |     NUL
L |                |                                         |
| utf8_test   | MyISAM |      10 | Dynamic    |    3 |             38 |
116 |  281474976710655 |         5120 |         0 |           NULL | 2005-07-25
16:00:30 | 2005-07-25 12:00:00 | 2005-07-31 14:36:36 | utf8_general_ci |     NUL
L |                | utf 8 test                              |
+-------------+--------+---------+------------+------+----------------+---------
----+------------------+--------------+-----------+----------------+------------
---------+---------------------+---------------------+-----------------+--------
--+----------------+-----------------------------------------+
5 rows in set (0.00 sec)

Please read my last comments. I've answered your comment several times before.

PS
Seems we have a great difference about your job:
You thinks: "I can't reproduce the bug - my job is done."
I think: "You know there is a bug. You have to hunt them. After you found and fixed them your job is done."

Sorry. if I can't reproduce the bug so deveropers can't fix one. Probably the bug was fixed on 5.0.11. If you can find when the bug occur stability I will be very glad.

If you can't repeat a bug this does not shows that the bug is fixed or that a developer can't repreat them too.

If you can't repeat a bug, logged in the error log, it only shows only >YOU< are unable to repeat them. Or with other words: >YOU< are unable to do your job!

MySQL-Front Team,

It might help the bugs people if you have a script which creates and deletes repeatedly until the problem happens. Once you have proved that this always creates the problem eventually on your system, that will give the bugs people something to run.

Can argue about who should do it but you can probably understand that with 5.0 release due very soon the bugs people appeciate any assistance in producing reliable test cases so they can get more bugs in the fix queue.

I think, the bug is really related to the upgrade of the existing databases from 5.0.9 or other previous version to post-5.0.9. The only reliable way to do it in case of triggers, SPs and functions where defined in 5.0.9 (according to my experience) is to make a dump, then make a fresh install of, say, 5.0.11, and then restore. This set of steps should always work (but some restrictions were added on SPs in a newer versions, so, please, check the http://dev.mysql.com/doc/mysql/en/news-5-0-x.html before upgrading).

Multiple unsuccessfull attempts to repeat the described behaviour proves this idea - we almost always use fresh installations for bugs verification...