MySQL Bugs: #110255: mysqlsh parallel import issue with

Bug #110255	mysqlsh parallel import issue with
Submitted:	2 Mar 2023 1:55	Modified:	7 Mar 2023 6:05
Reporter:	Sanjay Gupta	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
MySQL Shell 8.0.32

mysqlsh command line weird behavior  when using command line and when using JS
Examples are shown below.
sample csv file :

0,0,0,"run_intf_filter is handling Data Source ID 15.",2023-01-25 02:32:38
0,0,0,"No records to filter for 15!",2023-01-25 02:32:38
0,0,0,"run_intf_filter is handling Data Source ID 17.",2023-01-25 02:32:38
0,0,0,"No records to filter for 17!",2023-01-25 02:32:38
0,0,0,"run_intf_filter is handling Data Source ID 21.",2023-01-25 02:32:38
0,0,0,"Calling INTF_FILTER.process_cddb2_interface()...",2023-01-25 02:32:38
0,0,0,"run_intf_filter is handling Data Source ID 23.",2023-01-25 02:32:38
0,0,0,"Calling INTF_FILTER.process_cddb2_interface()...",2023-01-25 02:32:38
0,0,0,"run_intf_filter is handling Data Source ID 47.",2023-01-25 02:32:38
0,0,0,"No records to filter for 47!",2023-01-25 02:32:38

Create Table sanjay_test1
(
INTF_ID INT,
BATCH_JOB_NO INT ,
STATUS_CODE INT ,
MESSAGE varchar(1000),
MESSAGE_TIMESTAMP DATETIME
)

How to repeat:
mysqlsh --uri=mysql://root:xxx@localhost:3306 -- util import-table "test3.csv" --schema= "test" --table= "sanjay_test1" --dialect= "csv"  --fieldsOptionallyEnclosed=true --linesTerminatedBy= "\n" --fieldsTerminatedBy= "," --fieldsEnclosedBy= '"'

WARNING: Using a password on the command line interface can be insecure.
ERROR: Missing closing quote

Same Test case passes when using usingJS of mysqlsh. You can see 10 rows imported.

MySQL  localhost:3306  JS > util.importTable("test3.csv", {schema: "test", table: "sanjay_test1", dialect: "csv", skipRows: 0, showProgress: true, fieldsOptionallyEnclosed: true, fieldsTerminatedBy: ",", linesTerminatedBy: "\n",fieldsEnclosedBy: '"'})
Importing from file '/Users/sagupta/test3.csv' to table `test`.`sanjay_test1` in MySQL Server at localhost:3306 using 1 thread
[Worker000] test3.csv: Records: 10  Deleted: 0  Skipped: 0  Warnings: 0
100% (700 bytes / 700 bytes), 0.00 B/s
File '/Users/sagupta/test3.csv' (700 bytes) was imported in 0.0889 sec at 700.00 B/s
Total rows affected in test.sanjay_test1: Records: 10  Deleted: 0  Skipped: 0  Warnings: 0

Hi Mr Gupta,

Thank you for your bug report.

However, you have several errors in your mysqlsh startup options for the import of the CSV. You have also errors in the column themselves. 

For example, your DATETIME values are all unquoted .......

Please, correct your errors and try again .......

Why datetime need to be quoted ? DateTime is not string datatype. 
Anyway, I have removed datatime from field and data but still same error so it proves that it is not datatime issue.
Here is another test case without datetime field. 

mysql> desc sanjay_test3 ;
+--------------+---------------+------+-----+---------+-------+
| Field        | Type          | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| INTF_ID      | int(11)       | YES  |     | NULL    |       |
| BATCH_JOB_NO | int(11)       | YES  |     | NULL    |       |
| STATUS_CODE  | int(11)       | YES  |     | NULL    |       |
| MESSAGE      | varchar(1000) | YES  |     | NULL    |       |
+--------------+---------------+------+-----+---------+-------+
4 rows in set (0.03 sec)

mysql> exit
Bye

 ~ % cat test4.csv
0,0,0,"run_intf_filter is handling Data Source ID 15."
0,0,0,"No records to filter for 15!"
0,0,0,"run_intf_filter is handling Data Source ID 17."
0,0,0,"No records to filter for 17!"
0,0,0,"run_intf_filter is handling Data Source ID 21."
0,0,0,"Calling INTF_FILTER.process_cddb2_interface()..."
0,0,0,"run_intf_filter is handling Data Source ID 23."
0,0,0,"Calling INTF_FILTER.process_cddb2_interface()..."
0,0,0,"run_intf_filter is handling Data Source ID 47."
0,0,0,"No records to filter for 47!"

~ % mysqlsh --uri=mysql://root:xxx@localhost:3306 -- util import-table test4.csv --schema=test --table=sanjay_test3 --dialect= "csv"  --fieldsTerminatedBy= "," --fieldsEnclosedBy= '"'  --fieldsOptionallyEnclosed=true --linesTerminatedBy= "\n"  

WARNING: Using a password on the command line interface can be insecure.
ERROR: Missing closing quote

JS mysqlsh works fine. 

MySQL  localhost:3306  JS > util.importTable("test4.csv", {schema: "test", table: "sanjay_test3", dialect: "csv", skipRows: 0, showProgress: true, fieldsOptionallyEnclosed: true, fieldsTerminatedBy: ",", linesTerminatedBy: "\n",fieldsEnclosedBy: '"',threads: 3})
Importing from file '/Users/sagupta/test4.csv' to table `test`.`sanjay_test3` in MySQL Server at localhost:3306 using 1 thread
[Worker000] test4.csv: Records: 10  Deleted: 0  Skipped: 0  Warnings: 0
100% (500 bytes / 500 bytes), 0.00 B/s
File '/Users/sagupta/test4.csv' (500 bytes) was imported in 0.0210 sec at 500.00 B/s
Total rows affected in test.sanjay_test3: Records: 10  Deleted: 0  Skipped: 0  Warnings: 0
 MySQL  localhost:3306  JS >

Hi,

Actually, the rules for the CSV file in the format you have written and rules for the import file in JS are quite different.

You should use mysqlsh in  mysql-py mode , instead of mysql-js mode, for example, like this:

mysql-py> util.import_table("test4.csv", {"schema": "test, "table": "test", "dialect": "csv-unix" , "skipRows": 1, "showProgress": True})

Hence, syntax of CSV files differ among Py and JS modes .......

You can read all about it in our Reference Manual for mysqlsh.

Not a bug.

Hi,
It looks like you are steering away from original issue which was described. First you told that it is due to DATETIME issue and now telling difference between PY and JS mode. I was just telling that csv load is working fine in JS mode

So please tell what is wrong with this syntax. This does not uses PY or JS. In this example, we even omitted datatime field from table and as well as csv.

mysqlsh --uri=mysql://root:password@localhost:3306 -- util import-table test4.csv --schema=test --table=sanjay_test3 --dialect= "csv"  --fieldsTerminatedBy= "," --fieldsEnclosedBy= '"'  --fieldsOptionallyEnclosed=true --linesTerminatedBy= "\n"  

WARNING: Using a password on the command line interface can be insecure.
ERROR: Missing closing quote

Hi Sanjay,

Lemme step in. I'm not sure what you are reporting? Can we start from the beginning. The simple import works ok:

mysql [localhost:8032] {msandbox} (test) > delete from sanjay_test3;
Query OK, 6 rows affected (0.02 sec)

mysql [localhost:8032] {msandbox} (test) > \q
Bye
[arhimed@localdev msb_8_0_32]$ ./msh --uri=mysql://root:msandbox@localhost:8032 -- util import-table /tmp/test3.csv --schema=test --table=sanjay_test3 --dialect="csv"  --fieldsTerminatedBy="," --fieldsEnclosedBy="\""  --fieldsOptionallyEnclosed=true --linesTerminatedBy= "\n"
WARNING: Using a password on the command line interface can be insecure.
Importing from multiple files to table `test`.`sanjay_test3` in MySQL Server at localhost:8032 using 8 threads
ERROR: File /home/arhimed/sandboxes/msb_8_0_32/\n does not exist.
[Worker002] test3.csv: Records: 1  Deleted: 0  Skipped: 0  Warnings: 0
100% (500 bytes / 500 bytes), 0.00 B/s
1 file(s) (500 bytes) was imported in 0.0369 sec at 500.00 B/s
Total rows affected in test.sanjay_test3: Records: 1  Deleted: 0  Skipped: 0  Warnings: 0
ERROR: File /home/arhimed/sandboxes/msb_8_0_32/\n does not exist.
[arhimed@localdev msb_8_0_32]$ ./use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost:8032] {msandbox} ((none)) > select * from test.sanjay_test3;
+---------+--------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| INTF_ID | BATCH_JOB_NO | STATUS_CODE | MESSAGE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+---------+--------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|       0 |            0 |           0 | "run_intf_filter is handling Data Source ID 15."
0,0,0,"No records to filter for 15!"
0,0,0,"run_intf_filter is handling Data Source ID 17."
0,0,0,"No records to filter for 17!"
0,0,0,"run_intf_filter is handling Data Source ID 21."
0,0,0,"Calling INTF_FILTER.process_cddb2_interface()..."
0,0,0,"run_intf_filter is handling Data Source ID 23."
0,0,0,"Calling INTF_FILTER.process_cddb2_interface()..."
0,0,0,"run_intf_filter is handling Data Source ID 47."
0,0,0,"No records to filter for 47!"
 |
+---------+--------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost:8032] {msandbox} ((none)) > \q
Bye
[arhimed@localdev msb_8_0_32]$ cat /tmp/test3.csv
0,0,0,"run_intf_filter is handling Data Source ID 15."
0,0,0,"No records to filter for 15!"
0,0,0,"run_intf_filter is handling Data Source ID 17."
0,0,0,"No records to filter for 17!"
0,0,0,"run_intf_filter is handling Data Source ID 21."
0,0,0,"Calling INTF_FILTER.process_cddb2_interface()..."
0,0,0,"run_intf_filter is handling Data Source ID 23."
0,0,0,"Calling INTF_FILTER.process_cddb2_interface()..."
0,0,0,"run_intf_filter is handling Data Source ID 47."
0,0,0,"No records to filter for 47!"
[arhimed@localdev msb_8_0_32]$

I see it loaded it all now into single record. Looks like you are having some unicode " lookalikes here :(

Hi

EOL is LF for CSV-UNIX dialect. 

mysql [localhost:8032] {msandbox} ((none)) > delete from test.sanjay_test3;
Query OK, 13 rows affected (0.01 sec)

mysql [localhost:8032] {msandbox} ((none)) > \q
Bye
[arhimed@localdev msb_8_0_32]$ ./msh --uri=mysql://root:msandbox@localhost:8032 -- util import-table /tmp/test4.csv --schema=test --table=sanjay_test3 --dialect="csv-unix"  --fieldsOptionallyEnclosed=true
WARNING: Using a password on the command line interface can be insecure.
Importing from file '/tmp/test4.csv' to table `test`.`sanjay_test3` in MySQL Server at localhost:8032 using 1 thread
[Worker000] test4.csv: Records: 10  Deleted: 0  Skipped: 0  Warnings: 0
100% (500 bytes / 500 bytes), 0.00 B/s
File '/tmp/test4.csv' (500 bytes) was imported in 0.0272 sec at 500.00 B/s
Total rows affected in test.sanjay_test3: Records: 10  Deleted: 0  Skipped: 0  Warnings: 0
[arhimed@localdev msb_8_0_32]$ ./use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 54
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost:8032] {msandbox} ((none)) > select * from test.sanjay_test3;
+---------+--------------+-------------+--------------------------------------------------+
| INTF_ID | BATCH_JOB_NO | STATUS_CODE | MESSAGE                                          |
+---------+--------------+-------------+--------------------------------------------------+
|       0 |            0 |           0 | run_intf_filter is handling Data Source ID 15.   |
|       0 |            0 |           0 | No records to filter for 15!                     |
|       0 |            0 |           0 | run_intf_filter is handling Data Source ID 17.   |
|       0 |            0 |           0 | No records to filter for 17!                     |
|       0 |            0 |           0 | run_intf_filter is handling Data Source ID 21.   |
|       0 |            0 |           0 | Calling INTF_FILTER.process_cddb2_interface()... |
|       0 |            0 |           0 | run_intf_filter is handling Data Source ID 23.   |
|       0 |            0 |           0 | Calling INTF_FILTER.process_cddb2_interface()... |
|       0 |            0 |           0 | run_intf_filter is handling Data Source ID 47.   |
|       0 |            0 |           0 | No records to filter for 47!                     |
+---------+--------------+-------------+--------------------------------------------------+
10 rows in set (0.01 sec)

mysql [localhost:8032] {msandbox} ((none)) >

I do not think this is a bug, the "ERROR: Missing closing quote" if this is what you are reporting is shell expansion issue, what quotes are you using and in combination with what. Copy/Paste from web replaces proper " with some unicode variants looks like it (when I copy/paste your code it fails, when I type it myself it works).

Yeah, you are correct
mysqlsh --uri=mysql://root:xxx@localhost:3306 -- util import-table "test4.csv" --schema="test" --table="sanjay_test3" --dialect="csv-unix" --fieldsOptionallyEnclosed=true
This worked fine

It looks like mysqlsh syntax is not forgiving e.g. dialect=csv-unix has default values for linesTerminatedBy,linesTerminatedBy etc  and if I give same default values on command line , I get some issues so bottom line is don't specify.