Wednesday, April 8, 2015

MySQL error converting MyISAM to InnoDB for tables with over 1000 columns

My company has an app that allows customers to create their own custom columns on their data model. I think this was an absolutely horrible architectural decision but it was done years before I ever started working at the company. We've had a number of clients create over 1000 columns. These tables are MyISAM so this is allowed. We decided it was finally time to switch over to InnoDB but kept getting an error when trying to converting the tables. The annoying thing about these errors is they don't tell you what table was having problems. I was converting the entire MySQL instance to InnoDB by querying the Information Schema for any tables that were still MyISAM and generating the ALTER statements to do the conversion like this:

SELECT @@hostname, table_schema, table_name, create_time, update_time, CONCAT("ALTER TABLE `",table_schema,"`.`",table_name,"` ENGINE = InnoDB;")
FROM INFORMATION_SCHEMA.TABLES
WHERE engine = 'MyISAM'
AND table_schema NOT IN ('information_schema','mysql');

After the error, I re-ran this query and looked at the first table at the top of the list. I did a SHOW CREATE TABLE <table_name> and saw that the number of columns was over 1000.

ERROR 1005 (HY000) at line 1: Can't create table 'database_1.#sql-9b4_ad23c1a' (errno: 139)
ERROR 1005 (HY000) at line 2428: Can't create table 'database_2.#sql-6228_506cb9b1' (errno: 139)
ERROR 1005 (HY000) at line 205: Can't create table 'database_3.#sql-8311_fc9ed4b' (errno: 139)

No comments:

Post a Comment