Tuesday, May 6, 2014

MySQL ERROR 1118 (42000) MySQL 5.0 to MySQL 5.5 or above

So I recently had a TBs database that that I had to upgrade from MySQL 5.0 to MySQL 5.5.
This blog post will touch on the following:
  • sql_mode 
  • innodb_strict_mode 
During the mysql_upgrade process (which does a mysqlcheck) I quickly noticed the following error:

ERROR 1118 (42000) at line 23: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

So I started I had to check a few things first.

# The master DB was > select @@sql_mode; 
| @@sql_mode | 
| | 
New DB was
mysql> select @@sql_mode; 
| @@sql_mode | 
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | +--------------------------------------------+ 
OK I would rather have something for sql_mode over a empty value.
 So I let that go.
select @@innodb_strict_mode; 
ERROR 1193 (HY000): Unknown system variable 'innodb_strict_mode' 

# NEW SERVER mysql> select @@innodb_strict_mode; 
| @@innodb_strict_mode | 
| 1 | 
You can read more about this setting here:
mysql> SET GLOBAL innodb_strict_mode=0;

The above command allowed me to at least mysql upgrade an check the tables. To be safe I still set up a bash script to dump and load all of the tables. (yes all the TBS of them)

 Do not take a short cut and assume all is ok.

If you are getting errors mysqldump and reload the files. Better to be safe than sorry later.

 Once the data is loaded into 5.5+ I can review and adjust variables.

So needless to say it is going to take some time to dump and load TBs of data. I want to give the slave all the opportunity I can to catch up as fast as possible. While my shell scripts are dumping and loading the data, there is no reason the database cannot be gathering the binlogs in the meantime.

So now while my processlist will show:

SELECT /*!40001 SQL_NO_CACHE */ * 
the slave status shows:
Slave_IO_Running: Yes
 Slave_SQL_Running: No

So I am gathering logs while I clean the database.
This should allow the database to catch up quickly once I am ready to go.

No comments:

Post a Comment