Bug #29188 sql_mode=STRICT_ALL_TABLES, "invalid data" still got imported using mysqlimport
Submitted: 18 Jun 2007 23:23 Modified: 19 Jun 2007 1:39
Reporter: Jean-Sebastien Bessette Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:MySQL version 5.0.41 OS:Windows (2000 SP4)
Assigned to: CPU Architecture:Any
Tags: invalid data, mysqlimport, SQL_MODE, strict_all_tables

[18 Jun 2007 23:23] Jean-Sebastien Bessette
Description:
>>> Context:
I'm using MySQL version 5.0.41 under Windows.
I'm porting an automated "data load" script from Oracle (using SQL Loader – DISCARDMAX 999 option) to MySQL platform.

What I want – the requirement is...
to be able to load many comma delimited text files with >250,000 records (once a day) into my (MySQL) database and "skipping" any records that are in error (not complying with the data integrity rules of the database); but still want to process all the input data (not just stopping at the first error).
(reference: http://dev.mysql.com/tech-resources/articles/mysql-data-integrity.html)

The problem is that event using sql_mode="STRICT_ALL_TABLES" the "invalid input data" still got imported using mysqlimport tool.
It behave the same way as if there would be no sql_mode set.

How to repeat:
>>> my.cnf
[mysqld]
basedir=e:\\mysql
datadir=e:\\mydata\\data
sql_mode="STRICT_ALL_TABLES"

>>> Connecting to the database

E:\mysql\bin>mysql -u root my_db
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.41-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

>>> checking variables

mysql> SELECT @@sql_mode;
+-------------------+
| @@sql_mode        |
+-------------------+
| STRICT_ALL_TABLES |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
| STRICT_ALL_TABLES |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
| STRICT_ALL_TABLES  |
+--------------------+
1 row in set (0.00 sec)

>>> Creating the table

mysql> CREATE TABLE TMP_TABLE1 (COL1 VARCHAR(5));
Query OK, 0 rows affected (0.11 sec)

>>> tmp_table1.txt:
column_one_header
line1
line2
line3
line4 this is too long and should be rejected
line5should be rejected too
line6
line7

>>> I'm using mysqlimport to import data to a table with following command line under windows

E:\mysql\bin>mysqlimport -u root --delete --columns=COL1 --verbose --ignore-lines=1 --lines-terminated-by="\r\n" --local my_db e:/kb/mysql/tmp_table1.txt

Connecting to localhost
Selecting database my_db
Deleting the old data from table tmp_table1
Loading data from LOCAL file: e:/kb/mysql/tmp_table1.txt into tmp_table1
my_db.tmp_table1: Records: 7  Deleted: 0  Skipped: 0  Warnings: 2
Disconnecting from localhost

>>> Looking to the content

mysql> SELECT * FROM TMP_TABLE1;
+-------+
| COL1  |
+-------+
| line1 |
| line2 |
| line3 |
| line4 |
| line5 |
| line6 |
| line7 |
+-------+
7 rows in set (0.00 sec)

>>> Manually trying the INSERT statement with "invalid data" generates and error as expected of sql_mode="STRICT_ALL_TABLES"

mysql> INSERT INTO TMP_TABLE1 (COL1) VALUES ('line4 this is too long and should be rejected');
ERROR 1406 (22001): Data too long for column 'COL1' at row 1

Suggested fix:
Having sql_mode="STRICT_ALL_TABLES" in my.cnf and importing "invalid input data" using mysqlimport should result in giving "errors" and not "warnings with adjusted data".
[19 Jun 2007 1:39] MySQL Verification Team
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.