MySQL Bugs: #37114: sql_mode NO_BACKSLASH_ESCAPES does not work properly with LOAD DATA INFILE

Bug #37114	sql_mode NO_BACKSLASH_ESCAPES does not work properly with LOAD DATA INFILE
Submitted:	31 May 2008 1:59	Modified:	15 Oct 2008 17:29
Reporter:	Chris Calender	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0, 5.1, 6.0	OS:	Any
Assigned to:	Tatiana Azundris Nuernberg	CPU Architecture:	Any
Tags:	LOAD DATA, LOAD DATA INFILE, NO_BACKSLASH_ESCAPES, sql mode, SQL_MODE

Description:
Setting the sql_mode to NO_BACKSLASH_ESCAPES does not affect data inserted by LOAD DATA INFILE.

A similar bug was fixed in the client in 5.0.25, but that fix does not affect LOAD DATA INFILE.

http://bugs.mysql.com/bug.php?id=20103

How to repeat:
<create file.txt with '1 \aa' as the contents (sans the 's)>

CREATE DATABASE test2;

USE test2;

CREATE TABLE t1 (id INT, val1 CHAR(3)) ENGINE=MyISAM;

SET sql_mode = 'NO_BACKSLASH_ESCAPES';

LOAD DATA INFILE 'C:/users/chris/desktop/file.txt' 
REPLACE INTO TABLE t1
FIELDS TERMINATED BY ' ';

SELECT * FROM t1;

INSERT INTO t1 (id, val1) VALUES (1, '\aa');

SELECT * FROM t1;

SET sql_mode='';

INSERT INTO t1 (id, val1) VALUES (1, '\aa');

SELECT * FROM t1;

--- Here it is with the output (and comments from me) ---

C:\Program Files\MySQL\mysql-5.0.60\bin>mysql --version
mysql  Ver 14.12 Distrib 5.0.60, for Win32 (ia32)

<You can see it is the current client>

C:\Program Files\MySQL\mysql-5.0.60\bin>mysql -u root -P 3374
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.60-enterprise-gpl-nt MySQL Enterprise Server (GPL)

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

mysql> SELECT VERSION();
+--------------------------+
| VERSION()                |
+--------------------------+
| 5.0.60-enterprise-gpl-nt |
+--------------------------+
1 row in set (0.00 sec)

<You can see it is the current server>

mysql> CREATE DATABASE test2;
Query OK, 1 row affected (0.00 sec)

mysql> USE test2;
Database changed

mysql> CREATE TABLE t1 (id INT, val1 CHAR(3)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql> SET sql_mode = 'NO_BACKSLASH_ESCAPES';
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA INFILE 'C:/users/chris/desktop/file.txt'
    -> REPLACE INTO TABLE t1
    -> FIELDS TERMINATED BY ' ';
Query OK, 1 row affected (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM t1;
+------+------+
| id   | val1 |
+------+------+
|    1 | aa   |
+------+------+
1 row in set (0.00 sec)

<the above should show the '\'>

mysql> INSERT INTO t1 (id, val1) VALUES (1, '\aa');
Query OK, 1 row affected (0.00 sec)

<Now, let's do an INSERT to make sure I'm not crazy>

mysql> SELECT * FROM t1;
+------+------+
| id   | val1 |
+------+------+
|    1 | aa   |
|    1 | \aa  |
+------+------+
2 rows in set (0.00 sec)

<Ok, not crazy>

mysql> SET sql_mode='';
Query OK, 0 rows affected (0.00 sec)

<But, let's just change the sql_mode back to be 100% sure>

mysql> INSERT INTO t1 (id, val1) VALUES (1, '\aa');
Query OK, 1 row affected (0.01 sec)

<One last insert, now the actual '\' should be gone in the 3rd record, as we set the sql_mode to ''.>

mysql> SELECT * FROM t1;
+------+------+
| id   | val1 |
+------+------+
|    1 | aa   |
|    1 | \aa  |
|    1 | aa   |
+------+------+
3 rows in set (0.00 sec)

<And that is what we see>

Suggested fix:
Please ensure that LOAD DATA INFILE can use the sql_mode of NO_BACKSLASH_ESCAPES with the expected results.

Verified on 5.0.60 (and 5.0.50).

Also verified on 5.1.23-rc.

Verified on 5.0.62 and 6.0.4.

This bug also affects mysqlimport, as the NO_BACKSLASH_ESCAPES mode is ignored when using it also.

** Important Consideration / Question ** 

With this bug fixed, will it be possible to continue to use existing line terminators, such as \n, or will this require one to use a different line terminator, such as '|', or some other character that does not begin with '\'?

sql_yacc.yy:
load_data:
          load_data_lock opt_local INFILE TEXT_STRING_filesystem
          {
            LEX *lex=Lex;
            lex->sql_command= SQLCOM_LOAD;
            lex->lock_option= $1;
            lex->local_file=  $2;
            lex->duplicates= DUP_ERROR;
            lex->ignore= 0;
            if (!(lex->exchange= new sql_exchange($4.str, 0)))
              MYSQL_YYABORT;
          }

sql_class.cc:
static String default_escaped("\\",default_charset_info);

sql_exchange::sql_exchange(char *name,bool flag)
  :file_name(name), opt_enclosed(0), dumpfile(flag), skip_lines(0)
{
  field_term= &default_field_term;
  enclosed=   line_start= &my_empty_string;
  line_term=  &default_line_term;
  escaped=    &default_escaped;
  cs= NULL;
}

sql_load.cc:
int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list,
	        List<Item> &fields_vars, List<Item> &set_fields,
                List<Item> &set_values,
                enum enum_duplicates handle_duplicates, bool ignore,
                bool read_file_from_client)
{
  ...
  String ...,*escaped=ex->escaped;

So yeah, totally ignoring SQL_MODE.

If we heed SQL_MODE, we should do it as a (changed) default while parsing: mysql_load() has no way of telling whether an escape of \ in the parameters is a default that we'll have to fix if SQL_MODE is NO_BACKSLASH_ESCAPE, or whether the user explicitly used LOAD DATA ... ESCAPED BY '\\', which I believe should still override whatever default SQL_MODE suggests.

Anyway, changing for both LOAD DATA and SELECT/OUTFILE passes all the existing INFILE/OUTFILE/NO_BACKSLASH_ESCAPE tests ( bootstrap ctype_big5 distinct loaddata outfile outfile_loaddata ps query_cache sp sp-error subselect union view  character_set_database_func ctype_big5 distinct federated fulltext3 gis loaddata local_infile_basic local_infile_func mysqlbinlog mysqlbinlog_base64 outfile_loaddata ps_1general ps_ddl sp-error trigger variables view warnings mysql select sql_mode sql_mode_basic sql_mode_func system_mysql_db_fix50030 system_mysql_db_fix50117 ), but let's make sure we have definitely a correct OUTFILE/INFILE cycle with and without NO_BACKSLASH_ESCAPES before we attach the patch, shall we?

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/47852

2661 Tatiana A. Nurnberg	2008-06-13
      Bug#37114: sql_mode NO_BACKSLASH_ESCAPES does not work properly with LOAD DATA INFILE
      
      NO_BACKSLASH_ESCAPES was not heeded in LOAD DATA INFILE
      and SELECT INTO OUTFILE.  It is now.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/53527

2715 Tatiana A. Nurnberg	2008-09-08
      Bug#37114: sql_mode NO_BACKSLASH_ESCAPES does not work properly with LOAD DATA INFILE
      
      NO_BACKSLASH_ESCAPES was not heeded in LOAD DATA INFILE
      and SELECT INTO OUTFILE.  It is now.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/54235

2680 Tatiana A. Nurnberg	2008-09-17
      Bug#37114: sql_mode NO_BACKSLASH_ESCAPES does not work properly with LOAD DATA INFILE
      
      NO_BACKSLASH_ESCAPES was not heeded in LOAD DATA INFILE
      and SELECT INTO OUTFILE.  It is now.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/54278

2682 Tatiana A. Nurnberg	2008-09-18
      Bug#37114: sql_mode NO_BACKSLASH_ESCAPES does not work properly with LOAD DATA      Bug#37114: sql_mode NO_BACKSLASH_ESCAPES does not work properly with LOAD DATA INFILE
      
      tweaked test to make embedded server happy

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/54285

2682 Tatiana A. Nurnberg	2008-09-18
      Bug#37114: sql_mode NO_BACKSLASH_ESCAPES does not work properly with LOAD DATA      Bug#37114: sql_mode NO_BACKSLASH_ESCAPES does not work properly with LOAD DATA INFILE
      
      tweaked test to make embedded server happy

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/54301

5.0.70, 5.1.30, 6.0.8

Pushed into 5.1.30  (revid:azundris@mysql.com-20080918092450-v3hqnpgo8wb7v5oy) (version source revid:matthias.leich@sun.com-20080918130519-n5rkfl809hokgnp3) (pib:4)

This is actually pushed to 5.1.29, not 5.1.30.

Noted in 5.0.70, 5.1.29, 6.0.8 changelogs.

The NO_BACKSLASH_ESCAPES SQL mode was ignored for LOAD DATA INFILE
and SELECT INTO ... OUTFILE. The setting is taken into account now.

Pushed into 6.0.8-alpha  (revid:azundris@mysql.com-20080918092450-v3hqnpgo8wb7v5oy) (version source revid:matthias.leich@sun.com-20080918121317-gcwf7sntty3hw8lj) (pib:5)

Pushed into 5.1.29-ndb-6.2.17  (revid:azundris@mysql.com-20080918092450-v3hqnpgo8wb7v5oy) (version source revid:tomas.ulin@sun.com-20081028140209-u4emkk1xphi5tkfb) (pib:5)

Pushed into 5.1.29-ndb-6.3.19  (revid:azundris@mysql.com-20080918092450-v3hqnpgo8wb7v5oy) (version source revid:tomas.ulin@sun.com-20081028194045-0353yg8cvd2c7dd1) (pib:5)

Pushed into 5.1.29-ndb-6.4.0  (revid:azundris@mysql.com-20080918092450-v3hqnpgo8wb7v5oy) (version source revid:jonas@mysql.com-20081101082305-qx5a1bj0z7i8ueys) (pib:5)

Does this fix also work for LOAD DATA LOCAL INFILE (from a LOCAL file)? Even with NO_BACKSLASH_ESCAPES, we have backslashes in Active Directory domain accounts which don't get loaded into the tables.

For anyone else running across this issue searching for an answer when either no rows or only one row is getting loaded... Because characters preceded by a backslash are no longer treated as special characters, you have to change "LINES TERMINATED BY '\r\n'" to "LINES TERMINATED BY X'0d0a'". Otherwise, the input file is treated as a single line and, if you are skipping a header row, no rows get loaded!