MySQL Bugs: #66257: LOAD DATA INFILE issues after update Mysql from 5.3.6 to 5.5.25a

Bug #66257	LOAD DATA INFILE issues after update Mysql from 5.3.6 to 5.5.25a
Submitted:	8 Aug 2012 8:51	Modified:	8 Aug 2012 17:22
Reporter:	Narbot Vincent	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.5.25a	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I use the LOAD DATA INFILE instruction to load a CSV file into a table. In the MyPHP release 5.3.6 the import worked correctly but with the MyPHP release 5.5.25a I have a error report :
Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'Qty' at row 1' in C:\Program Files\EasyPHP-12.0\www\Rapport\upload.php:112
The column Qty is an integer in my table.
In the CSV file there is no value for this column.
=> With the old release, the columns were automatically filled with the value 0
=> With the new release I have the error report

How to repeat:
create a table with 3 columns : a column with varchar(5), and 2 columns with int(8)
Create a CVS file with 2 lines:
AA;0;0
;;0
in a PHP programm run the fonction
$reponse = $bdd->query('LOAD DATA INFILE \'Myfile.csv\' INTO TABLE Import_Table FIELDS TERMINATED BY \';\' ');
An error will be reported on the 2nd line of the import file for the integer column without data.

Please, send the output of:

show create table Import_Table\G

from mysql command line client.

CSV file

Attachment: Report.csv (application/vnd.ms-excel, text), 13 bytes.

php programm to import the data

Attachment: index.php (application/octet-stream, text), 964 bytes.

I created a database my_base_temp with the table ...

CREATE TABLE IF NOT EXISTS `import_table` (
  `Text_1` varchar(5) NOT NULL,
  `Inte_1` int(8) NOT NULL,
  `Inte_2` int(8) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I sent 2 files : the csv file and the PHP programm to execute the LOAD DATA INFILE instruction.

Let us discard PHP for the disucssion. This is about SQL and not a specific client environment.  On the SQL statement

LOAD DATA INFILE 'Report.csv' INTO TABLE import_table FIELDS TERMINATED BY ';'

.. I get the warning (in non-strict mode using MySQL 5.5.23): "Incorrect integer value: '' for column 'Inte_1' at row 2;".

Data get inserted as 

Text_1  Inte_1  Inte_2  
------  ------  --------
AA           1         2
             0         3

Everything is as expected as far as I can see.  'empty string' in 2nd line and 2nd position of your file gets truncated and inserts as "0" to the `Inte_1` column (with a warning).

Maybe you are running another sql_mode with the new server than before (strict mode now - non-strict mode before)?

Peter
(not a MySQL/Oracle person)

In the my.ini file, I put in comments the line 
#sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
and I have now the same result as you.
I did not know this variable, and it was not present in the previous version which I used.
Thank you

If you have a strict mode, I believe that you will get an *error* and not a *warning* and data from the file will not insert completely. 'empty string' cannot be converted to "0" in strit mode.

Thank you for help, Peter.

Report closed as "Not a Bug".