MySQL: changing ROW FORMAT to DYNAMIC or COMPRESSED
If you've changed your database properties to use innodb_large_prefix = 1 and innodb_file_format = Barracuda in my.cnf as below:
innodb_large_prefix = 1 innodb_file_format = Barracuda
you may want to convert all your existing tables to use DYNAMIC or COMPRESSED ROW_FORMAT.
For a single table, you need to run the following:
mysql> ALTER TABLE test ROW_FORMAT=DYNAMIC;
To convert all tables in a given database, here is a short bash script to do it:
#!/bin/bash DATABASE=some_db ROW_FORMAT=DYNAMIC #ROW_FORMAT=COMPRESSED TABLES=$(echo SHOW TABLES | mysql -s $DATABASE) for TABLE in $TABLES ; do echo "ALTER TABLE $TABLE ROW_FORMAT=$ROW_FORMAT;" echo "ALTER TABLE $TABLE ROW_FORMAT=$ROW_FORMAT" | mysql $DATABASE done
The script assumes you can use mysql without specifying a password (i.e. password added to .my.cnf).