When you're upgrading to MySQL 5.6 you may notice strict mode is turned on by default. You can disable it, but now might be a good time to get your schemas strict, to ensure smooth upgrade paths in the future.

One particularly common failure, is columns that:

• have no default (e.g. "")
• do not allow NULL

Now when MySQL has to create a record in which you have omitted such a field, it has to guess what to store there. For VARCHARS it'll save "", for integers 0.

Guesswork is best not left to your database engine as this could lead to ambiguity and unexpected results, which is why when strict mode is on (you can check with SELECT @@sql_mode), MySQL will error out:

mysql> INSERT INTO accounts VALUES();
ERROR 1364 (HY000): Field 'name' doesn't have a default value

I have found that the least intrusive way to make my schemas strict is to allow NULL (but your mileage may vary).

If you want to set all problematic field to allow NULL, I wrote a query that generates the appropriate statements:

SELECT
 CONCAT('ALTER TABLE `', TABLE_NAME, '` MODIFY `', COLUMN_NAME, '` ', COLUMN_TYPE, '; ') as strict_schema_changes
FROM `columns`
WHERE 1=1
 AND IS_NULLABLE = 'NO'
 AND COLUMN_DEFAULT IS NULL
 AND TABLE_SCHEMA= 'transloadit';
 -- You'll have to change `transloadit` to your database name.

This will for instance generate:

ALTER TABLE `accounts` MODIFY `name` varchar(200);
ALTER TABLE `accounts` MODIFY `company` varchar(70);
ALTER TABLE `assemblies` MODIFY `updated` datetime;
ALTER TABLE `blog_posts` MODIFY `title` varchar(256);
ALTER TABLE `countries` MODIFY `is_eu` tinyint(1);
ALTER TABLE `credits` MODIFY `created` datetime;
ALTER TABLE `invoices` MODIFY `to_vat_id` varchar(30);

Note that allowing NULL is the default when modifying columns (as opposed to specifying NOT NULL in your declaration).

Obviously you'll need to carefully test your app and revert your migrations if anything breaks, but this could give you a head start.

Hope this helps!