value is "\" can not copy to mysql

[ywyt738]

What happens?

If my field's value is \ , it can not be imported to mysql with COPY command. Because the import sql is

INSERT table value ('\')

The sql will report syntax error.

To Reproduce

1. create a table in duckdb, insert a row with field's value is \
2. create the new table in mysql, use

   create table table_name as from db.table

OS:

linux & mac

DuckDB Version:

0.10.1

DuckDB Client:

cli

Full Name:

Xiaojun Huang

Affiliation:

MHP

Have you tried this on the latest nightly build?

I have not tested with any build

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• [X] Yes, I have

[szarnyasg]

Hi @ywyt738 thanks for opening this issue. Please clarify the following:

• Are you using the MySQL extension?
• What is the script that you are running? Please provide a complete MWE in the form of SQL/shell/Python scripts.

[ywyt738]

Hi @szarnyasg

1. I'm using mysql extension.

2. Here is an MWE:

   # create a table in local file database
   create table t1 (v varchar);
   # insert a data
   insert table t1 value ('\');
   # attach a mysql
   attach 'host=localhost user=root database=mysqldb' as mysql_db (type mysql_scanner);
   # use database
   use mysql_db;
   # create table in mysql from local database table "t1"
   create table t1 as from db.t1;

   The error is

   IO Error: Failed to run query "INSERT INTO `mysqldb`.`t1`  VALUES ('\')": You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''\')' at line 1

[szarnyasg]

@ywyt738 thanks! I transferred the issue to the duckdb_mysql repository. We'll take a look.

[danilobellini]

Past week I suffered from a data integrity issue where strings like 12345\0123 had the \0 replaced by the null character and strings like R\L lost the backslash while copying data from a CSV to MySQL through DuckDB. I have only noticed it because of a trailing backslash in one of these input strings, which broke the process the same way described in this issue.

[Mytherin]

Thanks for reporting! I've pushed a fix in https://github.com/duckdb/duckdb_mysql/commit/8253d614d2601c369652721206498b10a59ae0bf.