MySQL Bugs: #56561: "Show table status" query, workbench "alter table" kills MySQL

Bug #56561	"Show table status" query, workbench "alter table" kills MySQL
Submitted:	4 Sep 2010 17:51	Modified:	16 Oct 2010 19:41
Reporter:	Tim Boura	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.1.50 x64	OS:	Windows (Vista)
Assigned to:		CPU Architecture:	Any
Tags:	crash, show table status

Description:
I recently had to re-install my development machine. I installed MySQL and imported a database that I'd previously set up. This included amongst other things a set of phpBB tables.

I've since made a number of modifications to other tables, adding, removing, renaming, etc.

Today I had cause to access the admin control panel on the phpBB forums for the first time since the reinstall and when I tried to access it I got the following error:

SQL ERROR [ mysql4 ]

MySQL server has gone away [2006]

SQL

SHOW TABLE STATUS FROM `herodex`

I then got a message from windows that said "mysqld.exe has stopped working" and I discovered that the MySQL had died completely.

I restarted the MySQL server from the SQL Workbench and everything was fine until I again tried to access the admin control panel whereupon the exact same symptoms appeared again.

I then restarted the server again and this time tried running the query manually from within the MySQL Workbench Editor, both selecting the schema and running just SHOW TABLE STATUS and without selecting it and doing SHOW TABLE STATUS FROM X.

If I try "show table status from mysql" though that completes fine, so it is something specific to the herodex schema.

I then tried doing a database dump of all the tables. Twice in a row the database crashed when it tried to export the 40th table.

Select * from that table worked fine as did show full columns from, using the gui and selecting alter table though produced the same crash.

Dropping that table worked and allowed the export to continue but it then found another corrupted table which showed the same behaviour - attempting to alter the table from the workbench killed the server.

Interestingly both of the tables in question were originally imported with data inside them from the backup and while they contained no foreign keys they were the target of foreign keys. The foreign key into the first table had been deleted earlier while testing something else, however I went to edit the table which had a foreign key into the second table. It opened up the list of foreign keys fine, but then when I right clicked the key both workbench and the MySQL server crashed simultaneously!

The server died as before, the workbench threw up a window saying "MySQL Workbench has encountered a problem. External component has thrown an exception" that then asked me to report the bug.

I deleted both those tables and I was finally able to complete the database dump and to run show table status.

I've flagged this as serious as while I've now found a way around it in order to do so I had to completely delete 3 tables from my database and could not even dump those tables out to a backup before I did so.

Fortunately this happened on my development machine but had it happened in a development environment it could have been very serious indeed - at the very least we would have needed to roll back to the most recent backup.

Thanks,
Tim

How to repeat:
Unfortunately while I could repeat the problem every time while it was happening, in order to get my machine functional I had to delete the tables responsible for causing the problem and as stated above the symptoms prevented me from exporting those tables.

I had done nothing unusual though - simply created them from backup, made a few modifications, removed some foreign key references to them, etc.

One thing that does occur to me is that I'd been renaming fields in tables yesterday and was having trouble doing that - which seemed to be connected to foreign keys. I went through and deleted most of the foreign keys in the database so that I could then rename the fields and will be recreating the foreign keys later once the database re factoring is complete.

Suggested fix:
I don't have much to suggest and I realise an un-reproducible case isn't very helpful. Hopefully this information might give someone an idea though - or at least if someone else runs into the problem give another perspective on it.

Please, send the entire error log of your MySQL server (usually this is <hostname>.err file in the data directory).

Error file added. Stupid vista was hiding it from me and hiding the options to show it again or I'd have included it earlier. I finally managed to beat the relevant settings into submission 

I think the most interesting thing is a comment about being unable to find the AUTOINC column id...and then later on the stack trace is:

100904 15:52:27 - 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.

key_buffer_size=57671680
read_buffer_size=65536
max_used_connections=4
max_threads=100
threads_connected=3
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 89228 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x572e880
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
0000000140217C54    mysqld.exe!ha_innobase::innobase_peek_autoinc()[ha_innodb.cc:7972]
0000000140219CE5    mysqld.exe!ha_innobase::info()[ha_innodb.cc:6583]
0000000140089326    mysqld.exe!get_schema_tables_record()[sql_show.cc:3758]
000000014009575F    mysqld.exe!get_all_tables()[sql_show.cc:3527]
00000001400975BC    mysqld.exe!get_schema_tables_result()[sql_show.cc:6175]
0000000140174F6A    mysqld.exe!JOIN::exec()[sql_select.cc:1846]
000000014017613A    mysqld.exe!mysql_select()[sql_select.cc:2519]
0000000140176536    mysqld.exe!handle_select()[sql_select.cc:269]
0000000140069D90    mysqld.exe!execute_sqlcom_select()[sql_parse.cc:5109]
000000014006CD5C    mysqld.exe!mysql_execute_command()[sql_parse.cc:2293]
0000000140071700    mysqld.exe!mysql_parse()[sql_parse.cc:6036]
00000001400723D8    mysqld.exe!dispatch_command()[sql_parse.cc:1262]
0000000140072E67    mysqld.exe!do_command()[sql_parse.cc:888]
0000000140099F47    mysqld.exe!handle_one_connection()[sql_connect.cc:1136]
000000014031E265    mysqld.exe!pthread_start()[my_winthread.c:85]
00000001402E8317    mysqld.exe!_callthreadstart()[thread.c:295]
00000001402E83E5    mysqld.exe!_threadstart()[thread.c:275]
0000000076FEBE3D    kernel32.dll!BaseThreadInitThunk()
0000000077126A51    ntdll.dll!RtlUserThreadStart()
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 000000000DD67DC0=SHOW TABLE STATUS
						FROM `herodex`
thd->thread_id=41
thd->killed=NOT_KILLED

Thank you for the feedback.

Have you created these tables in earlier version of MySQL, then upgraded? Could you please send us output of SHOW CREATE TABLE for problem tables and full ALTER STATEMENT you used?

Unfortunately as said above I had to drop the tables in order to continue with my work and I've not seen the problem since.

The tables were originally created in a previous version of MySQL (probably around 1.5 years old) and exported as a .SQL file. They were then imported using the MySQL workbench into a fresh install of the latest available version of MySQL.

I don't have an exact list of what changes had been made as this is a development machine and I was experimenting with various things. At the very least tables and fields were renamed, foreign keys were deleted and added, etc. I also don't know at what point the failure happened as it involved an operation I hadn't tried to do up until the point I first discovered the problem.

This is the fragment of the database backup that created two of the faulty tables.

--
-- Definition of table `pds_wallet_transfer_history`
--

DROP TABLE IF EXISTS `pds_wallet_transfer_history`;
CREATE TABLE `pds_wallet_transfer_history` (
  `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `WalletId` bigint(20) unsigned NOT NULL,
  `OtherWalletId` bigint(20) unsigned NOT NULL,
  `Amount` bigint(20) NOT NULL,
  `Reason` int(10) unsigned NOT NULL,
  `Balance` bigint(20) NOT NULL,
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`),
  KEY `FK_pds_wallet_transfer_history_reason` (`Reason`),
  KEY `FK_pds_wallet_transfer_history_source` (`WalletId`) USING BTREE,
  KEY `FK_pds_wallet_transfer_history_destination` (`OtherWalletId`) USING BTREE,
  CONSTRAINT `FK_pds_wallet_transfer_history_otherwalletid` FOREIGN KEY (`OtherWalletId`) REFERENCES `pds_wallets` (`PDS_Id`),
  CONSTRAINT `FK_pds_wallet_transfer_history_reason` FOREIGN KEY (`Reason`) REFERENCES `pds_wallet_transfer_reasons` (`Id`),
  CONSTRAINT `FK_pds_wallet_transfer_history_walletid` FOREIGN KEY (`WalletId`) REFERENCES `pds_wallets` (`PDS_Id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `pds_wallet_transfer_history`
--

/*!40000 ALTER TABLE `pds_wallet_transfer_history` DISABLE KEYS */;
/*!40000 ALTER TABLE `pds_wallet_transfer_history` ENABLE KEYS */;

--
-- Definition of table `pds_wallet_transfer_reasons`
--

DROP TABLE IF EXISTS `pds_wallet_transfer_reasons`;
CREATE TABLE `pds_wallet_transfer_reasons` (
  `Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Description` varchar(45) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

Thank you for the feedback.

This looks like table format upgrade problem. Do you still have backup file?

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