3

I have a Percona 5.1 server in production that uses MyISAM tables in our production database. To support DB transactions I need to update the tables to InnoDB. We're currently using MySQL 5.5 in development and the migration script runs fine with simple ALTER TABLE xyz ENGINE=InnoDB; queries. However in production tests (against a copy of the production database) we're getting an error:

mysql> ALTER TABLE `xyz` ENGINE=InnoDB;
ERROR 1005 (HY000): Can't create table 'InnoTest.#sql-644_dd133' (errno: 1478)

On our development server, using the same database dump as our production tests:

mysql> ALTER TABLE `xyz` ENGINE=InnoDB;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 2

mysql> show warnings;
+---------+------+------------------------------------------------------------+
| Level   | Code | Message                                                    |
+---------+------+------------------------------------------------------------+
| Warning | 1478 | InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. |
| Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT.                       |
+---------+------+------------------------------------------------------------+

And the stats:

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+-------------------------------------------+
| Variable_name           | Value                                     |
+-------------------------+-------------------------------------------+
| innodb_version          | 5.1.73-14.11                              |
| protocol_version        | 10                                        |
| version                 | 5.1.73-rel14.11-log                       |
| version_comment         | Percona Server (GPL), 14.11, Revision 603 |
| version_compile_machine | x86_64                                    |
| version_compile_os      | unknown-linux-gnu                         |
+-------------------------+-------------------------------------------+

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+-------------------------+
| Variable_name           | Value                   |
+-------------------------+-------------------------+
| innodb_version          | 5.5.38                  |
| protocol_version        | 10                      |
| slave_type_conversions  |                         |
| version                 | 5.5.38-0ubuntu0.12.04.1 |
| version_comment         | (Ubuntu)                |
| version_compile_machine | x86_64                  |
| version_compile_os      | debian-linux-gnu        |
+-------------------------+-------------------------+

Debugging for gloomy.penguin:

mysql> ALTER TABLE `xyz` ENGINE=InnoDB;
ERROR 1005 (HY000): Can't create table 'InnoTest.#sql-644_df08c' (errno: 1478)
mysql> show errors;
+-------+------+------------------------------------------------------------+
| Level | Code | Message                                                    |
+-------+------+------------------------------------------------------------+
| Error | 1005 | Can't create table 'InnoTest.#sql-644_df08c' (errno: 1478) |
+-------+------+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table visit;
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | xyz | CREATE TABLE `xyz` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `some_field` int(11) DEFAULT NULL,
  `some_field` tinyint(2) DEFAULT '0',
  `some_field` enum('a','b') DEFAULT 'b',
  `some_field` varchar(200) DEFAULT NULL,
  `some_field` date DEFAULT NULL,
  `some_field` time DEFAULT NULL,
  `some_field` datetime DEFAULT NULL,
  `some_field` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |
1 row in set (0.02 sec)

mysql> SELECT @@GLOBAL.sql_mode;
+-------------------+
| @@GLOBAL.sql_mode |
+-------------------+
|                   |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT @@SESSION.sql_mode;
+--------------------+
| @@SESSION.sql_mode |
+--------------------+
|                    |
+--------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE `xyz` ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
ERROR 1005 (HY000): Can't create table 'InnoTest.#sql-644_df08c' (errno: 1478)
mysql> ALTER TABLE `xyz` ENGINE=InnoDB ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

The development server doesn't have any InnoDB settings in my.cnf (default Ubuntu 12.04 mysql-server install), production has these:

innodb                         = FORCE
innodb_strict_mode             = 1
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 64M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 592M
DaveO
  • 1,909
  • 4
  • 33
  • 63

1 Answers1

6

k... so OP isn't responding. that's cool. this is the mysql documentation on that error... http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-compression-syntax-warnings.html

  • run ALTER TABLE xyz ENGINE=InnoDB; in prod again.

  • then do show errors;.

  • do a show create table xyz;

  • see if you're in innodb strict mode... (i would do this in both prod and non-prod to see any difference) SELECT @@GLOBAL.sql_mode; SELECT @@SESSION.sql_mode;

  • from docs: If you are running in InnoDB strict mode, the combination of a KEY_BLOCK_SIZE with any ROW_FORMAT other than COMPRESSED generates an error, not a warning, and the table is not created.

  • use that info you got and the info at the link to determine how you should set your key_block_size and row_format to get it to take it.

the non-prod db works because:

  • Specifying a KEY_BLOCK_SIZE with any other ROW_FORMAT generates a warning that you can view with SHOW WARNINGS. However, the table is non-compressed; the specified KEY_BLOCK_SIZE is ignored). (in non-innodb-strict mode)

if you want more help, post the info you get and i can definitely suggest things... which would probably be alter table xyz engine=innodb ROW_FORMAT=COMPRESSED; and/or making the innodb mode setting equal to what your non-prod db is set with.

key_block_size

more than you ever wanted to know on row formats

for real, though... it sounds like prod is in innodb strict mode and non-prod isn't.

found this:

innodb_strict_mode:

The innodb_strict_mode option controls whether InnoDB operates in strict mode, 
where conditions that are normally treated as warnings, cause errors instead 
(and the underlying statements fail).

This mode is the default setting in MySQL 5.5.5 and higher.

One of your versions is above 5.5.5 and the other is below. That default might be the discrepancy....

gloomy.penguin
  • 5,833
  • 6
  • 33
  • 59
  • i guess `ROW_FORMAT=COMPACT` might be a viable option, too. – gloomy.penguin Oct 20 '14 at 02:45
  • Thanks, I've added more info. I couldn't see much info from the debugging you asked for, but COMPACT works. Don't know why, will start doing some reading. – DaveO Oct 20 '14 at 21:03
  • The `select @@GLOBAL.sql_mode` worked for me... here's some more info about checking it: http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-other-changes-strict-mode.html I really think that Prod was able to do the row format conversion for you b/c it's not in strict mode. But dev is, so it can't assume that conversion. – gloomy.penguin Oct 20 '14 at 21:18
  • @DaveO - what are your settings in ` my.cnf` like for innodb? – gloomy.penguin Oct 20 '14 at 21:23
  • added the info above. It appears it's a row format problem caused by innodb_strict_mode set in production. As above it should be the default in development as it's 5.5.38 but I'm getting just the warnings. As noted by http://www.percona.com/blog/2014/01/14/innodb-file-formats-here-is-one-pitfall-to-avoid/ you get these warnings with innodb_strict_mode set to OFF. – DaveO Oct 20 '14 at 22:46
  • Actually what I don't get is `SHOW VARIABLES LIKE 'innodb_file_format';` shows both databases are using Antelope and if I select the information schema for a production table the ROW_FORMAT is Dynamic, but according to http://dev.mysql.com/doc/innodb/1.1/en/innodb-row-format-dynamic.html "DYNAMIC and COMPRESSED row formats for InnoDB tables. You can only create these kinds of tables when the innodb_file_format configuration option is set to Barracuda.". So somehow the file format for the production tables have been set to Barracuda even though innodb_file_format is Antelope. – DaveO Oct 20 '14 at 23:39
  • From what I can gather the straight `ALTER TABLE` fails because the existing `ROW_FORMAT` is Dynamic and the `innodb_file_format` is set to Antelope (supports only Compact and Redundant). In test this results in warnings and it converts to tables to InnoDB with Compact. In production as `innodb_strict_mode` is set this instead fails. Trying `ROW_FORMAT=Dynamic` fails, probably because `innodb_file_format` is Antelope. If I understand right I need to set `innodb_file_format` in production to Barracuda and it should work, converting tables to InnoDB, keeping Dynamic format. Would have to test. – DaveO Oct 21 '14 at 00:14
  • `ALTER TABLE table_name ENGINE=InnoDB ROW_FORMAT=DEFAULT ` worked for me – Faraz Jun 13 '16 at 12:23