MySQL Got a packet bigger than ‘max_allowed_packet’ bytes

We were doing a big database import in MySQL and we got this error message during the process.

Got a packet bigger than ‘max_allowed_packet’ bytes.

Here are the changes we needed to make it work. We are running on CentOS.

1. Edit /etc/my.cnf and add the following lines. The packet value we want for this example is 500M.

[mysqld]
max_allowed_packet=500M

2. Restart MySQL.

> service mysqld restart

3. Run the MySQL import process with the max_allowed_packet parameter. Replace USERNAME, DATABASE and DUMP with the appropriate values.

> mysql --max_allowed_packet=500M -u USERNAME -p DATABASE < DUMP.sql

Wait for the import process to complete and that should be it.


mysqldump: Couldn’t execute ‘SELECT @@GTID_MODE’: Unknown system variable ‘GTID_MODE’ (1193)

If you are using the MySQL 5.6 version of mysqldump on an older MySQL database, you might get the error message.

mysqldump: Couldn't execute 'SELECT @@GTID_MODE': Unknown system variable 'GTID_MODE' (1193)

This error is in part due to the introduction of Global Transaction Identifiers (GTIDs) in MySQL 5.6. GTIDs make it simple to track and compare replication across a master-slave topology.

mysqldump tries to query this system variable, which doesn’t exist in earlier versions, and then fails. The solution is to add –set-gtid-purged=OFF in the mysqldump command. It should look something like

mysqldump -h dbHost -u dbuser dbName --set-gtid-purged=OFF

MySQL 5.6 – Specified key was too long; max key length is 767 bytes

We recently upgraded to MySQL 5.6, and one of our web applications started failing our automated tests. We have a test that drops all our tables and recreates them again, all done using Maven and Liquibase. The error we were getting was something like this.

SEVERE 1/1/13 12:00 PM:liquibase: Change Set /liquibase/tableName.sql::6::teamextension failed. Error:
Error executing SQL alter table tableName 
    add index indexName (columnName): Specified key was too long; max key length is 767 bytes

After we few Google searches, the found the issue to be described in the
Upgrading from Previous to Current Unicode Support page on the MySQL website. This boils down to the Unicode changes in MySQL 5.6.

So what’s the fix? We basically were indexing a very long column, and earlier MySQL versions were not complaining. The quickest fix is to tell MySQL to index only the first parts of the column.

    add index indexName (query(255));

But we have another problem. We were using Liquibase, with formatted SQL changelogs, and we cannot just change old changelogs. That will give errors when Liquibase runs. We started looking around the Liquibase documentation and saw the validCheckSum element, which could allow us to update old changelogs. Unfortunately, this only works with the XML changelogs. A little more digging and we came across the runOnChange attribute. Now we changed our changelog to:

--changeset teamextension:6 runOnChange
	alter table tableName
		add index indexName (columName(255));

Now things work again! If anyone has a better solution, please let us know. Thanks.