Lost connection in 5.5.30 and 5.5.32

[Ratheesh Kaniyala]

I am not able to create an index on a 50G innodb table. I tried using Percona 5.5.30 and 5.5.32 (latest stable) release.
I tried everything from increasing the timeouts to maxing out the max_allowed_packet size.

I am seeing the below error after around 500 sec of execution:
ERROR 2013 (HY000): Lost connection to MySQL server during query

I am doing a benchmark project and am using linkbench from facebook. I am seeing the error while creating the below index post data loading.

alter table linkdb.linktable add key `id1_type` (`id1`,`link_type`,`visibility`,`time`,`version`,`data`);

[Valerii Kravchuk (valerii-kravchuk)]

Please, send the entire error log compressed), your my.cnf file content and the output of:

show create table linktable\G

from that linkdb database.

[Ratheesh Kaniyala (ratheesh-cse)]

Ideally this is how the Create table should look like:

mysql> show create table linkdb.linktable;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table

             |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| linktable | CREATE TABLE `linktable` (
  `id1` bigint(20) unsigned NOT NULL DEFAULT '0',
  `id2` bigint(20) unsigned NOT NULL DEFAULT '0',
  `link_type` bigint(20) unsigned NOT NULL DEFAULT '0',
  `visibility` tinyint(3) NOT NULL DEFAULT '0',
  `data` varchar(255) NOT NULL DEFAULT '',
  `time` bigint(20) unsigned NOT NULL DEFAULT '0',
  `version` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id1`,`id2`,`link_type`),
  KEY `id1_type` (`id1`,`link_type`,`visibility`,`time`,`version`,`data`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

But since I am not able to create the index 'id1_type' post data loading (I
dropped the index before loading to speed up the loading), the create table
is now:

mysql> show create table linkdb.linktable;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table

             |
+-----------+---------------------------------...

[Valerii Kravchuk (valerii-kravchuk)]

This is suspicious in your my.cnf:

ibdata1:10M:autoextend:max:100G

You explicitly limit shared tablespace (where UNDO information is stored even with innodb_file_per_table=1).

Please, send the output of:

ls -l /var/lib/mysql

I still miss the entire error log content. Messages there may be useful to find out the reason of the problem.

[Ratheesh Kaniyala (ratheesh-cse)]

ls -l /var/lib/mysql
---------------------------
total 25983084
-rw-r----- 1 mysql root 2413 Jul 19 09:29 bba402.err
-rw-rw---- 1 mysql mysql 196 Jul 19 09:29 bba402-slow.log
-rw-rw---- 1 mysql mysql 4901044224 Jul 19 13:54 ibdata1
-rw-rw---- 1 mysql mysql 268435456 Jul 19 13:55 ib_logfile0
-rw-rw---- 1 mysql mysql 268435456 Jul 19 13:49 ib_logfile1
-rw-rw---- 1 mysql mysql 12435456 Jul 22 09:50 ib_lru_dump
drwx------ 2 mysql mysql 141 Jul 19 09:38 linkdb
-rw-rw---- 1 mysql mysql 1073744109 Jul 19 10:52 mint1-bin.000001
-rw-rw---- 1 mysql mysql 1073743339 Jul 19 10:59 mint1-bin.000002
-rw-rw---- 1 mysql mysql 1073765661 Jul 19 11:07 mint1-bin.000003
-rw-rw---- 1 mysql mysql 1073750046 Jul 19 11:14 mint1-bin.000004
-rw-rw---- 1 mysql mysql 1073743363 Jul 19 11:21 mint1-bin.000005
-rw-rw---- 1 mysql mysql 1073755636 Jul 19 11:29 mint1-bin.000006
-rw-rw---- 1 mysql mysql 1073742793 Jul 19 11:36 mint1-bin.000007
-rw-rw---- 1 mysql mysql 1073748243 Jul 19 11:47 mint1-bin.000008
-rw-rw---- 1 mysql mysql 1073742784 Jul 19 11:57 mint1-bin.000009
-rw-rw---- 1 mysql mysql 1073744343 Jul 19 12:07 mint1-bin.000010
-rw-rw---- 1 mysql mysql 1073745388 Jul 19 12:18 mint1-bin.000011
-rw-rw---- 1 mysql mysql 1073742351 Jul 19 12:28 mint1-bin.000012
-rw-rw---- 1 mysql mysql 1073743958 Jul 19 12:39 mint1-bin.000013
-rw-rw---- 1 mysql mysql 1073745060 Jul 19 12:48 mint1-bin.000014
-rw-rw---- 1 mysql mysql 1073747603 Jul 19 12:56 mint1-bin.000015
-rw-rw---- 1 mysql mysql 1073750666 Jul 19 13:04 mint1-bin.000016
-rw-rw---- 1 mysql mysql 1073743291 Jul 19 13:13 mint1-bin.000017
-rw-rw---- 1 mysql mysql 1073746122 Jul 19 13:21 mint1-bin.000018
-rw-rw---- 1 mysql mysql 1073742425 Jul 19 13:30 mint1-bin.000019
-rw-rw---- 1 mysql mysql 754752329 Jul 19 13:37 mint1-bin.000020
-rw-rw---- 1 mysql mysql 380 Jul 19 13:30 mint1-bin.index
drwx------ 2 mysql root 4096 Jul 19 09:29 mysql
drwx------ 2 mysql mysql 4096 Jul 19 09:29 performance_schema

About the error log: I had to replace the mysql data dir and hence I do not
have the original error log. However today I tried to drop the index and re
create it with data already loaded in the linktable. I got a different
error today after the alter table ran for about 1000 Secs:

mysql> alter table linktable drop key `id1_type`;
Query OK, 0 rows affected (1.45 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table linktable add key `id1_type`
(`id1`,`link_type`,`visibility`,`time`,`version`,`data`);
mysql> alter table linktable add key `id1_type`
(`id1`,`link_type`,`visibility`,`time`,`version`,`data`);
ERROR 1034 (HY000): Incorrect key file for table 'linktable'; try to repair
it

Could this be related to the ibdata1 setting which is limited to 100G?

And here is the error log contents for today when I tried this operation:

cat bba402.err
-------------------------
130719 09:29:24 mysqld_safe Starting mysqld daemon with databases from
/var/lib/mysql
130719 9:29:24 [ERROR] An old style --language value with language
specific part detected: /usr/share/mysql/english/
130719 9:29:24 [ERROR] Use --lc-messages-dir without language specific
part instead.
130719 9:29:25 [Note] Plu...

[Launchpad Janitor (janitor)]

[Expired for Percona Server because there has been no activity for 60 days.]

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2992