MySQL 5.6 doesn't accept empty strings on numerical fields
I was using the following command inside one migration to populate a table after its creation.
Fixtures.create_fixtures('test/fixtures', 'one_csv_to_import')
It was working fine with older versions of MySQL, but now on version 5.6 it started to abort the migration whenever is found a NULL value for a decimal column.
Like this:
CREATE TABLE `example_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`one_column` decimal(6,2) DEFAULT 0.00,
`another_column` int(11) DEFAULT NULL
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
So having an input file like this one_csv_to_import.csv
:
id,one_column,another_column
1,,2
And running a migration, it will fail saying
Mysql::Error: Incorrect decimal value: '' for column 'one_column' at row 1: INSERT INTO `example_table` (`id`, `one_column`, `another_column`) VALUES (1, '', 2)
The only way I found to workaround was to replace the Fixtures.create_fixtures
by
# setting default values for created_at and updated_at
execute <<-SQL
ALTER TABLE example_table MODIFY COLUMN created_at DATETIME NOT NULL DEFAULT now()
SQL
execute <<-SQL
ALTER TABLE example_table MODIFY COLUMN updated_at DATETIME NOT NULL DEFAULT now()
SQL
# using the MySQL-way to import CSVs
ActiveRecord::Base.connection.execute <<-SQL
LOAD DATA LOCAL INFILE 'test/fixtures/one_csv_to_import.csv'
INTO TABLE example_table
FIELDS
TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(
id,one_column,another_column
);
SQL
If you find some other solution to make MySQL accept this values please comment below.
Thanks,
NC
Written by Nuno Costa
Related protips
3 Responses
Did you try to change sql_mode of the migration session?
Can you give me an example of what you are meaning?
I had the same problem after upgrading to mysql 5.6.20. I found that turning off the "STRICT TRANS TABLES" mode in /etc/my.conf fixes the problem.