Disabling MySQL Strict Mode

Question

I am scanning 5000 csv files in a database. Unfortunately, the files have '' for 0. Thus, when I run my script, it crashes. I heard that this error can be avoided by simply disabling strict mode. So I tried disabling strict mode to allow me to read an empty string as 0 for my numeric fields. However, the error persisted.

Is it possible to disable strict mode in order to "read" in the int field? ('' these are two qoutes, i.e. an empty string)

If so, why setup

sql_mode = ''

in the configuration file my.ini does not fix the problem.

Thanks!

Answer 1

I think you are importing a CSV file using the LOAD DATA INFILE . Before executing this command, enter:

 SET sql_mode = ''; 

More information about the various SQL modes can be found in the documentation.

Answer 2

You can change you LOAD DATA INFILE Statement to adjust values. Something in this direction should work.

 LOAD DATA INFILE 'filepath.csv' INTO TABLE MyTable(Column1,@Col2,@Col3) SET Column2=CASE WHEN @Col2 = '' THEN 0 ELSE @Col2 END ,Column3=CASE WHEN @Col3 = '' THEN 0 ELSE @Col3 END; 

This query imports the value as indicated in column1, and corrects the values ​​for columns 2 and 3. Using this, you do not need to turn off strict mode, and you actually control what data arrives in your database, they can fix it in a reliable way. You can also use this function to change date formats or import hex encoded blob values. Very useful feature.

Answer 3

I need to do this and work correctly:

• To disable SQL strict mode, SSH on your server with root privileges
• Create this file: /etc/mysql/conf.d/disable_strict_mode.cnf

• Open the file and enter the following two lines:

  [num] sql_mode = IGNORE_SPACE, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION

• Restart MySQL with this command: sudo service mysql restart

Answer 4

I put this in response, as I still need a representative to comment on your next question about settings that are not “held”. My experience with MySQL was that the settings in the configuration file are only read when the server starts, so you have to restart the server for the new settings to take effect.

A server is a daemon that the command line client interacts with when running commands, so restarting the command line client does not actually restart the server itself.

This answer provides a more detailed explanation: https://serverfault.com/a/79051 .