MySQL Bugs: #62474: Optimize table dropping big tables

Bug #62474	Optimize table dropping big tables
Submitted:	19 Sep 2011 14:12	Modified:	8 Aug 2012 17:51
Reporter:	Eber M. Duarte	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S1 (Critical)
Version:	5.1.56-log	OS:	Linux (Red Hat 5.7 Tikanga)
Assigned to:		CPU Architecture:	Any
Tags:	Linux hera 2.6.18-274.el5 #1 SMP Fri Jul 8 17:36:59 EDT 2011 x86_64 x86_64 x86_6

Description:
Hi,

Recently I've upgraded MySQL from 4.1 to 5.1.56 and since that I started facing problems during OPTIMIZE process that I run every week on my slaves. When I execute optimize table, some big tables (> 1GB data file - ibd) simply disappear. It looks like MySQL is deleting old table and failing in renaming new table. As we see in data directory, temporary file is there:

[root - ativos_curriculo]# ls -la
total 1542348
drwx------  2 mysql mysql       4096 Sep 19 09:27 .
drwxr-xr-x 85 mysql mysql       4096 Aug 11 11:43 ..
-rw-rw----  1 mysql mysql       8672 Sep 18 08:55 ativos.frm
-rw-rw----  1 mysql mysql       8690 Sep 18 08:55 ativos_historico.frm
-rw-rw----  1 mysql mysql   88080384 Sep 19 06:49 ativos.ibd
-rw-rw----  1 mysql mysql       8600 Sep 18 09:07 ativos_status.frm
-rw-rw----  1 mysql mysql      98304 Sep 18 09:08 ativos_status.ibd
-rw-rw----  1 mysql mysql       8642 Sep 18 09:07 ativos_totais.frm
-rw-rw----  1 mysql mysql     589824 Sep 18 09:08 ativos_totais.ibd
-rw-r-----  1 mysql mysql         65 Jun 21 09:14 db.opt
-rw-rw----  1 mysql mysql 1488977920 Sep 18 09:08 #sql-1624_82d1b34.ibd <== temporary file

It's clear that ativos_historico.ibd is missing and temporary file is still in the data directory.
Looking at error log we can see errors related to replication since table doesn't exists and threre is an insert on it.

110919  5:15:21 [ERROR] Cannot find or open table ativos_curriculo/ativos_historico from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
how you can resolve the problem.

110919  5:15:21 [ERROR] Slave SQL: Error 'Table 'ativos_curriculo.ativos_historico' doesn't exist' on query. Default database: 'ativos_curriculo'. Query: 'insert ativos_curriculo.
ativos_historico(usr_id,status_id_new,status_id_old,data) values (11865,3,4,now()) /* --- /usr/virtuais/orion/seguro/htdocs/cron/cron_distrincha_ativos.php*/', Error_code: 1146
110919  5:15:21 [Warning] Slave: Table 'ativos_curriculo.ativos_historico' doesn't exist Error_code: 1146
110919  5:15:21 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'zeus-bin.000943' po
sition 20078260
110919  5:24:10 [Warning] Aborted connection 137574454 to db: 'grupo_midia' user: 'new_site' host: '10.41.20.95' (Got timeout reading communication packets)
110919  5:34:38 [Note] Slave SQL thread initialized, starting replication in log 'zeus-bin.000943' at position 20078260, relay log '/logfiles/mysql/relay/relay-bin.002823' positio
n: 20069780
110919  5:34:38 [ERROR] Cannot find or open table ativos_curriculo/ativos_historico from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
how you can resolve the problem.

How to repeat:
I don't know how to repeat, since when a run optimize I have this issue with different tables, different executions come with errors in different tables, but all of theses tables are InnoDB and have big data files (> 1GB).

How exactly you upgraded? Had you dumped your data from 4.1 with mysqldump and reloaded them in 5.1? Had you executed mysql_upgrade script in 5.1 after upgrade?

I dumped the whole databases in 4.1 with mysqldump and reloaded them in 5.1, except for mysql database. For this database I exported grants (show grants) and re-executed in this new fresh installation. That is, no file from 4.1 was reused in this new version. No mysql_upgrade involved, since I loaded data from dump.

Let me know in case I can give you more info.

Thanks.

Eber.

Thank you for the feedback.

Could you send us dump of one of tables which disappeared? Thanks in advance.

See also bug #62100

Here is table structure for one of the disappeared tables:

 CREATE TABLE `ativos_historico` (
  `usr_id` int(11) unsigned NOT NULL DEFAULT '0',
  `status_id_new` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `status_id_old` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `data` date NOT NULL DEFAULT '0000-00-00',
  KEY `usr_id_status_Id_new_status_Id_old_ix` (`usr_id`,`status_id_new`,`status_id_old`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='historico do ativos' ;

Some data inside the table:

INSERT INTO `ativos_historico` VALUES (4164745,5,9,'2009-04-17');
INSERT INTO `ativos_historico` VALUES (5017793,5,9,'2009-04-17');
INSERT INTO `ativos_historico` VALUES (6013421,5,9,'2009-04-17');
INSERT INTO `ativos_historico` VALUES (7089545,5,9,'2009-04-17');
INSERT INTO `ativos_historico` VALUES (9502463,5,9,'2009-04-17');
INSERT INTO `ativos_historico` VALUES (10313503,5,9,'2009-04-17');
INSERT INTO `ativos_historico` VALUES (9020949,5,3,'2009-04-17');
INSERT INTO `ativos_historico` VALUES (4940758,5,9,'2009-04-18');
INSERT INTO `ativos_historico` VALUES (5861024,5,9,'2009-04-18');
INSERT INTO `ativos_historico` VALUES (8003320,5,9,'2009-04-18');
INSERT INTO `ativos_historico` VALUES (8803974,5,9,'2009-04-18');
INSERT INTO `ativos_historico` VALUES (8895588,5,9,'2009-04-18');
INSERT INTO `ativos_historico` VALUES (9870793,5,9,'2009-04-18');
INSERT INTO `ativos_historico` VALUES (10098489,5,9,'2009-04-18');
INSERT INTO `ativos_historico` VALUES (10133520,5,9,'2009-04-18');
INSERT INTO `ativos_historico` VALUES (10202964,5,9,'2009-04-18');
INSERT INTO `ativos_historico` VALUES (10258813,5,9,'2009-04-18');
INSERT INTO `ativos_historico` VALUES (10299457,5,9,'2009-04-18');
INSERT INTO `ativos_historico` VALUES (10317216,5,9,'2009-04-18');
INSERT INTO `ativos_historico` VALUES (10317528,5,9,'2009-04-18');
INSERT INTO `ativos_historico` VALUES (10347637,5,9,'2009-04-18');
INSERT INTO `ativos_historico` VALUES (1272044,5,9,'2009-04-18');
INSERT INTO `ativos_historico` VALUES (1310840,5,9,'2009-04-18');
INSERT INTO `ativos_historico` VALUES (1562677,5,9,'2009-04-18');
INSERT INTO `ativos_historico` VALUES (1780976,5,9,'2009-04-18');
INSERT INTO `ativos_historico` VALUES (1865419,5,9,'2009-04-18');
INSERT INTO `ativos_historico` VALUES (1880729,5,9,'2009-04-18');
INSERT INTO `ativos_historico` VALUES (1924414,5,9,'2009-04-18');
INSERT INTO `ativos_historico` VALUES (2021616,5,9,'2009-04-18');

Thanks e let me know in case you need anything else.

Eber.

Looks very similar to verified bug #62100. Need to check when it is fixed.

Please, check if the same problem still happens with a newer version, 5.1.63 (bug Sveta mentioned above is fixed there)

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