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
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
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!