MySQL Bugs: #45637: Can't export a table that is relatively small. Many queries give me Err: 2013

Bug #45637	Can't export a table that is relatively small. Many queries give me Err: 2013
Submitted:	21 Jun 2009 10:28	Modified:	19 Sep 2009 10:13
Reporter:	Manuel Aude	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S2 (Serious)
Version:	5.0.75	OS:	Linux (Ubuntu Jaunty)
Assigned to:		CPU Architecture:	Any
Tags:	2013, error

Description:
This is the first bug I submit, and I think I shall start by saying sorry if I don't provide enough information.

I'm in Ubuntu Jaunty, using MySQL 5.0.75.

I got a very simple database with 4 tables, and when I exported them (I did each at once) with mysqldump, the first three exported without problems, but then the fourth said:

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `users` at row: 166045

It's always the same row. I found it weird, because two of the 3 previously dumped tables are much bigger than this one (one is 17+ million rows).

After that, I started to try different things, but nothing worked.

Doing any of the following gave me the error 2013:

CHECK TABLE users;
create table users2 select * from users;
OPTIMIZE TABLE users;
SELECT * FROM users;

And by trying to make a workaround, this happened:

mysql> create table users2 select * from users limit 163671;
Query OK, 163671 rows affected (0.58 sec)
Records: 163671  Duplicates: 0  Warnings: 0

mysql> insert into users2 select * from users limit 163672, 400000;
ERROR 2013 (HY000): Lost connection to MySQL server during query

I can, without any problem, use: SELECT count(*) FROM users; which came as a surprise to me, after trying the previous queries.

My my.cnf file is at the end (notice, I have AppArmor disabled).
The table has InnoDB as it's engine and has roughly 332 thousand rows.
I'm not sure what other information is needed, but of course I will provide it as soon as I know =)

[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /media/MySQL
#datadir	= /var/lib/mysql
tmpdir		= /tmp
language	= /usr/share/mysql/english
skip-external-locking

net_read_timeout = 1024
innodb_buffer_pool_size=450M
innodb_additional_mem_pool_size=20M
bind-address		= 127.0.0.1
key_buffer		= 100M
key_buffer_size		= 250M
tmp_table_size		= 48M
sort_buffer_size 	= 16M
read_rnd_buffer_size	= 256K
max_allowed_packet	= 16M
thread_stack		= 128K
thread_cache_size	= 8
myisam-recover		= BACKUP
ft_min_word_len		= 4
table_cache            = 2K
query_cache_limit       = 100M
query_cache_size        = 16M
log_slow_queries	= /var/log/mysql/mysql-slow.log
long_query_time = 2
expire_logs_days	= 10
max_binlog_size         = 100M
skip-bdb
skip-federated

[mysqldump]
quick
quote-names
max_allowed_packet	= 16M

[mysql]

[isamchk]
key_buffer		= 32M
!includedir /etc/mysql/conf.d/

How to repeat:
I wish I knew

Suggested fix:
No idea, again

Also, the line:

net_read_timeout = 1024

Was added after trying all the queries. Even if it wasn't there, this would happen, and if it is, things don't change (apparently)

Thank you for the problem report. Please, send the results of 

SHOW CREATE TABLE users\G
SHOW TABLE STATUS LIKE 'users'\G

Also, please, upload your entire error log (compressed).

mysql> show create table users;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL,
  `username` varchar(20) NOT NULL,
  `post_count` mediumint(9) NOT NULL default '0',
  `post_distribution` smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `usuario` (`username`),
  KEY `post_count` (`post_count`),
  KEY `post_distribution` (`post_distribution`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show table status like 'users';
+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+-------------------------+
| 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                 |
+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+-------------------------+
| users | InnoDB |      10 | Compact    | 399261 |             61 |    24690688 |               0 |     27262976 |         0 |           NULL | 2009-06-11 18:55:34 | NULL        | NULL       | utf8_general_ci |     NULL |                | InnoDB free: 3174400 kB | 
+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+-------------------------+
1 row in set (0.93 sec)

As I said, a very simple database =) And the error log, I'm not sure which one you want. Since I'm in Ubuntu, you probably want the one at /var/log/syslog?

Since I'm not really sure which one you want, I will wait a bit until I'm sure about it.

Thank you very much for such a quick response.

mysql> analyze table users;
+-----------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------------+---------+----------+----------+
| gamefaqs_oracle.users | analyze | status | OK |
+-----------------------+---------+----------+----------+
1 row in set (0.29 sec)

I hadn't tried that before. I'm surprised it didn't throw me error 2013 too.

Thank you for the report.

Error log contains information about table is corrupt.

Did you do binary upgrade before starting to experience crashes? If so please run mysql_upgrade and inform us if it solves the problem.

Otherwise please read http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html, start mysqld with innodb_force_recovery > 0, dump the table, then restore and inform us if crashes are still repeatable with restored table.

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