Description:
After data is exported to a local CSV file through mysql 'select into outfile' function, we cannot query the file directly through the mysql CSV engine
How to repeat:
create table t1 (id varchar(20));
insert into t1 values ("aa\n");
insert into t1 values ("aa\\n");
select * from t1 into outfile 'outfile/t0.CSV' COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n';
create table t0 (id varchar(20)) engine = csv;
## Then go to the data directory of MySQL and replace the current empty file t0.CSV with the exported ”outfile/t0.CSV.“
Later the query will report an error.
```
mysql> flush table t0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t0;
ERROR 1194 (HY000): Table 't0' is marked as crashed and should be repaired
```
Suggested fix:
The root cause is that '\n' is translated into '\\n' when select into outfile is exported, and mysql's csv engine is translated into '\\n' when it writes '\n'. The two are completely incompatible.
Description: After data is exported to a local CSV file through mysql 'select into outfile' function, we cannot query the file directly through the mysql CSV engine How to repeat: create table t1 (id varchar(20)); insert into t1 values ("aa\n"); insert into t1 values ("aa\\n"); select * from t1 into outfile 'outfile/t0.CSV' COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n'; create table t0 (id varchar(20)) engine = csv; ## Then go to the data directory of MySQL and replace the current empty file t0.CSV with the exported ”outfile/t0.CSV.“ Later the query will report an error. ``` mysql> flush table t0; Query OK, 0 rows affected (0.00 sec) mysql> select * from t0; ERROR 1194 (HY000): Table 't0' is marked as crashed and should be repaired ``` Suggested fix: The root cause is that '\n' is translated into '\\n' when select into outfile is exported, and mysql's csv engine is translated into '\\n' when it writes '\n'. The two are completely incompatible.