Last Updated: February 25, 2016
·
6.728K
· cyberix

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

3 Responses
Add your response

Did you try to change sql_mode of the migration session?

over 1 year ago ·

Can you give me an example of what you are meaning?

over 1 year ago ·

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.

over 1 year ago ·