MySQL Bugs: #61886: Lost Connection to server when reading large datapacket by client

Bug #61886	Lost Connection to server when reading large datapacket by client
Submitted:	16 Jul 2011 14:38	Modified:	15 Feb 2013 13:44
Reporter:	Thomas Rupprecht	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	5.1.57, 5.5.24	OS:	Windows (win 7 64 bit Professional)
Assigned to:		CPU Architecture:	Any
Tags:	Lost connection, network problem

Description:
I have installed MySQL 5.1.57 64 bit version on Windows 7 64 bit Professional-Version. When I try to read a large set of rows with a client (>15000 rows) I already get the error "Lost connection to server". This happens in mysql console client, mysqldump, workbench, query-browser, jdbc-driver when connection via TCP/IP from loacal or remote client. I searched the web for that problem. I found a lot of network problems and I tried all the suggestes solutions. Everytime the same result. There is no difference on the database engine used (same result on InnoDB and MyISAM).

During my research I figured that the debug version of the server (5.1.57) started with the same configuration file runs fine - slower but it doesn't crash. Huge sets of rows (>100000) can be read.

It looks that there might be a difference in compiling/otpmizing/libraries used for the production and the debug version.

I made a test with the 32 bit version 5.1.57 on Windows 7 64 bit Professional. This version also crashs when try to read a large dataset. Using this version on XP 32 bit and Vista 32 it works fine and doesn't crash.

Using other database, ftp-server on that system run fine. Megabyte of data are transfered over the network adapter without any problem.

How to repeat:
Windows 7 64 bit German
MySQL 5.1.57 64 bit
Database with a tables which contaisn 500.000 rows (a few columns filled with random data) MyISAM or InnoDB.

Try to run mysqldump, read that table with MySQLQueryBrowser, dump the database with Workbench or try to read that table via JDBC.

Suggested fix:
Use debug server version instead of production server. No crash, 3-4 times slower as production server.

Thank you for the bug report. Could you please look for the *.err file located in the datadir directory and search for the call stack or error/warning messages when the crash occurred with the release server. Print them here. Thanks in advance.

Content of the Errorfile (service stopped, errorfile deleted, service started, error occured).

110717  6:44:54 [Note] Plugin 'FEDERATED' is disabled.
110717  6:44:54  InnoDB: Initializing buffer pool, size = 2.0G
110717  6:44:55  InnoDB: Completed initialization of buffer pool
110717  6:44:55  InnoDB: Started; log sequence number 0 368423468
110717  6:44:55 [Note] Event Scheduler: Loaded 0 events
110717  6:44:55 [Note] C:\Program Files\MySQL\Server\bin\mysqld: ready for connections.
Version: '5.1.57-community'  socket: ''  port: 3306  MySQL Community Server (GPL)

Example for the problem: I started mysqldump serveral times on the same database. No other request/client ist running on the database. It crashed everytime but never on the same row:

E:\>mysqldump -u root -p --result-file=logfiles.sql logfiles
mysqldump: Error 2000: Unknown MySQL error when dumping table `srequest` at row: 468411

E:\>mysqldump -u root -p --result-file=logfiles.sql logfiles
mysqldump: Error 2000: Unknown MySQL error when dumping table `arequest` at row: 286190

E:\>mysqldump -u root -p --result-file=logfiles.sql logfiles
mysqldump: Error 2000: Unknown MySQL error when dumping table `request` at row: 183849

E:\>mysqldump -u root -p --result-file=logfiles.sql logfiles
mysqldump: Error 2000: Unknown MySQL error when dumping table `request` at row: 798398

E:\>mysqldump -u root -p --result-file=logfiles.sql logfiles
mysqldump: Error 2000: Unknown MySQL error when dumping table `arequest` at row: 233584

E:\>mysqldump -u root -p --result-file=logfiles.sql logfiles
mysqldump: Error 2000: Unknown MySQL error when dumping table `arequest` at row: 739276

E:\>mysqldump -u root -p --result-file=logfiles.sql logfiles
mysqldump: Error 2000: Unknown MySQL error when dumping table `arequest` at row: 1995286

E:\>mysqldump -u root -p --result-file=logfiles.sql logfiles
mysqldump: Error 2000: Unknown MySQL error when dumping table `request` at row: 2304648

Please, send your my.ini file content and the results of:

show create table request\G
show create table arequest\G
show table status like '%request';

Ini-File (comment lines removed):

[client]
port=3306
[mysql]
default-character-set=utf8
max_allowed_packet=75M
[mysqld]
max_allowed_packet=75M
port=3306
basedir="C:/Programme/MySQL/Server/"
datadir="C:/MySQL-DataFiles"
default-character-set=utf8
default-storage-engine=MYISAM
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=50
query_cache_size=367M
table_cache=256
tmp_table_size=369M
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=738M
key_buffer_size=577M
read_buffer_size = 256K
read_rnd_buffer_size = 512K
sort_buffer_size=256K
innodb_additional_mem_pool_size=24M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=12M
innodb_buffer_pool_size=2G
innodb_log_file_size=256M
innodb_thread_concurrency=18
net_buffer_length = 16384
enable-named-pipe
innodb = ON
optimizer_search_depth = 62
thread_handling = one-thread-per-connection

show create table request;
-> Result:
CREATE TABLE `request` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `action` varchar(10) NOT NULL DEFAULT '',
  `pageid` int(10) unsigned NOT NULL DEFAULT '0',
  `state` int(10) unsigned NOT NULL DEFAULT '0',
  `transfered` int(10) unsigned NOT NULL DEFAULT '0',
  `refererid` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `Index_2` (`date`) USING BTREE,
  KEY `index_3` (`pageid`),
  KEY `Index_4` (`refererid`),
  KEY `Index_5` (`date`,`pageid`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=27534128 DEFAULT CHARSET=latin1;

show create table arequest;
Result ->
CREATE TABLE `arequest` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL DEFAULT '0000-00-00',
  `pageid` int(10) unsigned NOT NULL DEFAULT '0',
  `count` int(10) unsigned NOT NULL DEFAULT '0',
  `transfered` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `Index_2` (`date`) USING BTREE,
  KEY `index_3` (`pageid`),
  KEY `Index_5` (`date`,`pageid`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=4766814 DEFAULT CHARSET=latin1;

mysql> show table status like '%request';
Result ->
+--------------+--------+---------+------------+----------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| 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_options | Comment |
+--------------+--------+---------+------------+----------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| arequest     | MyISAM |      10 | Fixed      |  3898475 |             20 |    77969500 | 5629499534213119 |    176574464 |         0 |        4766814 | 2011-06-14 06:53:15 | 2011-07-16 19:36:45 | 2011-06-14 06:53:26 | latin1_swedish_ci |     NULL |                |         |
| request      | MyISAM |      10 | Dynamic    | 27534127 |             35 |   983985184 |  281474976710655 |   1913621504 |         0 |       27534128 | 2011-06-14 06:05:00 | 2011-07-16 19:35:31 | 2011-06-14 06:06:07 | latin1_swedish_ci |     NULL |                |         |
| srequest     | MyISAM |      10 | Dynamic    |  5150350 |             35 |   184878064 |  281474976710655 |    355218432 |         0 |        5150351 | 2011-06-14 06:10:35 | 2011-07-16 19:35:33 | 2011-06-14 06:10:53 | latin1_swedish_ci |     NULL |                |         |
+--------------+--------+---------+------------+----------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
4 rows in set (0.01 sec)

Please, set

max_allowed_packet=256M

in both [mysql] and [mysqld] sections of my.ini, restart server and check if this will change anything.

I changed the max_allowed_packet value to 256M and restartet the server.

Then I executed ten times the same mysqldump command in a commandline without anny other system activity beetwen. The result is very, very strange. 2 Times there is no error reported. 8 Times there is a error reported (this is the result out of the command line window - no lines are removed or added):

E:\>mysqldump -u root -pRl0bisxa --result-file=logfiles.sql logfiles
mysqldump: Error 2000: Unknown MySQL error when dumping table `arequest` at row: 2065154

E:\>mysqldump -u root -pRl0bisxa --result-file=logfiles.sql logfiles
mysqldump: Error 2000: Unknown MySQL error when dumping table `request` at row: 4008729

E:\>mysqldump -u root -pRl0bisxa --result-file=logfiles.sql logfiles
mysqldump: Error 2000: Unknown MySQL error when dumping table `request` at row: 3655076

E:\>mysqldump -u root -pRl0bisxa --result-file=logfiles.sql logfiles
mysqldump: Error 2000: Unknown MySQL error when dumping table `request` at row: 2017444

E:\>mysqldump -u root -pRl0bisxa --result-file=logfiles.sql logfiles
mysqldump: Error 2000: Unknown MySQL error when dumping table `request` at row: 842562

E:\>mysqldump -u root -pRl0bisxa --result-file=logfiles.sql logfiles

E:\>mysqldump -u root -pRl0bisxa --result-file=logfiles.sql logfiles
mysqldump: Error 2000: Unknown MySQL error when dumping table `request` at row: 3560650

E:\>mysqldump -u root -pRl0bisxa --result-file=logfiles.sql logfiles
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `request` at row: 5825883

E:\>mysqldump -u root -pRl0bisxa --result-file=logfiles.sql logfiles
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `arequest` at row: 771599

E:\>mysqldump -u root -pRl0bisxa --result-file=logfiles.sql logfiles

E:\>

And still nothing in the error log of MySQL server even when error 2013 happens?

How long do that failed mysqldump attempts usually work before the failure?

Still no entry in the error log. No hint for an external error (like os error or so). Mysqldump runs a few seconds then fails.

Additonal I uninstalled the virus-scanner and rerun all tests. Debug version works fine, production version fails but everytime on a different row.

My guess why the debug build works and the non-debug build does not is that is debug build is *slower*. The complete randomness here seems to indicate that.

But whether this ia a problem with the server, with mysqldump, an OS (multithreading) issue or a hardware issue - sorry I have no clue. 

But since you are using Windows and have the server on Windows as well (as I understand) I would try if some GUI client runs into same issue whne creating a SQL dump (there are more than 5 of GUI clients that can generate a SQL-dump very similar to mysqldump).

Peter
(not a MySQL person)

Please, check if the same problem still happens with a newer version, 5.1.59.

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

UP! The same problem I have
once out of ten wrote a message:
mysqldump: Error 2000: Unknown MySQL error when dumping table `changes` at row: XXX

mysql -V
mysql  Ver 14.14 Distrib 5.5.20, for FreeBSD9.0 (sparc64) using  5.2

What wrong? (

Andrey,

your problem is likely not this bug, but misconfiguration. Please check all questions which we already asked Thomas, especially value of max_allowed_packet.

Thomas,

please check with current version 5.1.61 and if problem repeatable send us your table files (*.MYD, *.MYI and *.frm) compressed: I could not repeat described behavior.

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

The problem isn't solved even in the newest version (5.5.24). I installed it on windows 7 64-bit. Transfering large amount of data over TCP/IP is not possible. The only way is to connect via pipe. The everything works fine and mysqldump is possible.

A few days ago I setup a Linux System (Ubuntu) and configured the mysqlserver (5.5.22 on ubuntu 12.04 64 bit). I transfered the database (approx 30 million records in major table). The tests show the same result as on Windows. Running a view (calculcation some statistics) with a resultset of approx. 100.000 records crashes when transfering the data to the client.

The client is on a diffrent computer (windows), I tried different clients. I changed parameters on server (max_packet_allowed from 256M to 1G) increase thread_stack but change in behavior.

When I restrict the resultset in the query (via limit xxxx) to 20.000-30.000 records then everything is ok. Otherwise I receive MySQL Error 2000, 2027 malformed package, Lost connection to mysqlserver during query.

Finally I transfered to database to an old computer running vista (mysql 5.5.24). Running the same view shows me all records of the resultset correctly (mysquery browser). Very, very strange. I have absolut no clue why it runs on vista, crash on windows7 and linux.

I tried the same parameters as on vista and tried mysql-monitor, mysql query browser and heidi sql. Using all clients shows the same result - server crash after delivering a few thousand record set.

I am still thinking, that is a poblem of sending data too fast. Both computers are fast with GigaBit Network, the vista computer ist old and not too fast.

Can anyone give me a hint how to continue ?

Thank you for the feedback.

Please send us your table files (*.MYD, *.MYI and *.frm) compressed: I could not repeat described behavior with generic data.

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

I installed current version 5.6.10 64 bit on the same computer using the same OS (Windos 7 Prof 64 bit) and did the same tests. I am stunned, it works fine over the network ! No lost connection when reading und dumping a database with 33 million records.

Thank you for the feedback.

Closing as "Can't repeat" since problem is not repeatable anymore.