MySQL Bugs: #12295: Lost Connection During Query MyISAM <-> InnoDb Problem

Bug #12295	Lost Connection During Query MyISAM <-> InnoDb Problem
Submitted:	1 Aug 2005 9:15	Modified:	14 Oct 2005 8:58
Reporter:	Martin Rode	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.11-Debian_1-log	OS:	Linux (Linux 2.6.11)
Assigned to:		CPU Architecture:	Any

Description:
Our test scenario here is pretty straight forward and I hope I can get you guys enough information to nail this bug:

We have a Apache/PHP script which selects data from InnoDB tables, re-arranges the data and inserts that new data in a MyISAM table.

After 3000-5000 iterations (out of 800000) mysql dies with "Lost connection during query". There is only one thread running, there are no other users, this is a dedicated test machine. 

Now, if I start our same script and insert the new data into a InnoDB table instead (same schema), everything works fine and the script runs through all of the 800K iterations.

I did not find anything in the logs. What information can I provide to help you solving this bug? Our test setup here is pretty big, so it is not a matter of pasting a few lines of code and a small database dump.

Take Care,
Martin

relevent stuff from my.conf

# log-bin
key_buffer              = 256M
max_allowed_packet      = 32M
thread_stack            = 256K
query_cache_limit       = 1048576
query_cache_size        = 16777216
query_cache_type        = 1
log-slow-queries        = /var/log/mysql/mysql-slow.log
skip-bdb
innodb_data_file_path = ibdata1:5010M;ibdata2:4000M;ibdata3:4000M;ibdata4:4000M;ibdata5:50M:autoextend
innodb_buffer_pool_size=200M
innodb_additional_mem_pool_size=80M
innodb_log_buffer_size=8M

The table which gets the inserts:

CREATE TABLE `test_index` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `internal_ui` varchar(255) NOT NULL default '',
  `type` enum('text','keyword') NOT NULL default 'text',
  `keystr` varchar(255) NOT NULL default '',
  `value` text,
  `idx_options` int(11) default NULL,
  `status` varchar(50) default NULL,
  PRIMARY KEY  (`id`),
  KEY `key1` (`internal_ui`)
) ENGINE=[InnoDB|MyISAM] DEFAULT CHARSET=latin1

How to repeat:
--

Suggested fix:
--

Just curious... Where your data directory is located? What are the results of df -k? I mean, you created several large files for InnoDB, but is there any space left for MyISAM tables?

# df -k
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/hda1             28834716  15474888  11895104  57% /

So, yes there is enough space for MyISAM tables.

Is there any <your_hostname>.err file in your data directory created during this insert into MyISAM table attempts? If yes, please, send its content. 

Please, try to reproduce this behaviour on the latest  binaries (4.1.13) from MySQL (available http://dev.mysql.com/downloads/mysql/4.1.html).

I need a sample INSERT statement generated in you system to try to reproduce this behaviour myself. Yo can capture them. See http://dev.mysql.com/doc/mysql/en/query-log.html for details. Your private data are not needed, but a representative sample may be useful.

Nothing in the <hostname>.err log.

One intersting thing I remember from our application log (which got overwritten): Mysql could not insert a row because of a duplicate key. But that table (see my original post) has only one unique key, which is an auto_increment column (id), which the application does not set itself. This was AFTER the application saw the "lost connection during query" and a "mysql server died" later. safe_mysqld must have restartet the daemon, because the application continued running (producing the duplicate key error all the time).

I will try to get you all the requested information asap, I hope next Monday (I cannot update the DB server this week). Would 4.1.12 also do, it is in Debian ;-) ?

I'll think about these:

"But that table (see my original post) has only one unique key, which is
an auto_increment column (id), which the application does not set
itself. This was AFTER the application saw the "lost connection during
query" and a "mysql server died" later."

and share my ideas, if any. In any case, we have to find a way to reproduce this "lost connection during query" in a simple test to claim this a bug and solve the problem.

You may even have 4.1.13 for Debian till Monday... But I'll recommend to try 4.1.13 Linux (x86, glibc-2.2, static (Standard only), gcc) .tar.gz from MySQL download page I mentioned, or build 4.1.13 from sources yourself. MySQL do not support packages bundled into Debian distribution, as far as I know.

Martin,

What about the additional information I requested on August 1st? 

Have you tried to repeat the described behaviour on the newer versions?

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