0

mysqlsh --version mysqlsh Ver 8.0.32 for macos13 on x86_64 - for MySQL 8.0.32 (MySQL Community Server (GPL))

This is working from mysql and records get loaded in table

datafile test_sanjay.csv is like this

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

LOAD DATA INFILE '/Users/sagupta/test_sanjay.csv' 
INTO TABLE sanjay_test 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

I am looking to use mysqlsh ( parallel import option)

After connecting to mysqlsh prompt ( trying Py mode mysqlsh --socket=/Applications/XAMPP/xamppfiles/var/mysql/mysql.sock -uroot -p --py

util.import_table("/Users/sagupta/test_sanjay.csv", {"schema": "test", "table": "sanjay_test",  "skipRows": 1, "showProgress": True , "dialect": "csv" }) ;

Importing from file '/Users/sagupta/test_sanjay.csv' to table test.sanjay_test in MySQL Server at /Applications%2FXAMPP%2Fxamppfiles%2Fvar%2Fmysql%2Fmysql.sock using 5 threads 0% (0 bytes / 216.60 MB), 0.00 B/s File '/Users/sagupta/test_sanjay.csv' (216.60 MB) was imported in 1.3652 sec at 158.66 MB/s Total rows affected in test.sanjay_test: Records: 0 Deleted: 0 Skipped: 0 Warnings: 0

So no records gets uploaded.

I have tried with dialect: csv and used but then I get errors

util.import_table("/Users/sagupta/test_sanjay.csv", {"schema": "test", "table": "sanjay_test",  "skipRows": 1, "showProgress": True , "linesTerminatedBy": "\n" , "fieldsTerminatedBy": "," , "fieldsEnclosedBy": '"' })

Importing from file '/Users/sagupta/test_sanjay.csv' to table `test`.`sanjay_test` in MySQL Server at /Applications%2FXAMPP%2Fxamppfiles%2Fvar%2Fmysql%2Fmysql.sock using 5 threads
ERROR: [Worker003] test_sanjay.csv: MySQL Error 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytes @ file bytes range [69, 50000085): LOAD DATA LOCAL INFILE '/Users/sagupta/test_sanjay.csv' INTO TABLE `test`.`sanjay_test` FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\' LINES STARTING BY '' TERMINATED BY '\n'



mysqlsh --uri=mysql://root:xxxx@localhost:3306 -- util import-table test_sanjay.csv \
    --schema=test \
    --table=sanjay_test \
    --fields-terminated-by=',' \
    --fields-enclosed-by='"' \
    --lines-terminated-by='\n' \
    --skipRows=1

WARNING: Using a password on the command line interface can be insecure. ERROR: Argument options: Separators cannot be the same or be a prefix of another.

Tried other options

cat test3.csv
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

mysql> desc test.sanjay_test1
-> ;
+-------------------+---------------+------+-----+---------+-------+
| 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 | |
| MESSAGE_TIMESTAMP | datetime | YES | | NULL | |
+-------------------+---------------+------+-----+---------+-------+
5 rows in set (0.03 sec)

mysql>

Tried 3 different options so even single quote and double quotes errors are different.

mysqlsh --uri=mysql://root:xxx@localhost:3306 -- util import-table test3.csv --schema=test table=sanjay_test1 --linesTerminatedBy=$'\n' --fieldsTerminatedBy=','

WARNING: Using a password on the command line interface can be insecure.
ERROR: The following option is invalid: --linesTerminatedBy=

mysqlsh --uri=mysql://root:xxx@localhost:3306 -- util import-table test3.csv --schema=test table=sanjay_test1 --linesTerminatedBy=$"\r\n" --fieldsTerminatedBy=","

WARNING: Using a password on the command line interface can be insecure.
ERROR: Target table is not set. The target table for the import operation must be provided in the options.

mysqlsh --uri=mysql://root:xxx@localhost:3306 -- util import-table test3.csv --schema=test table=sanjay_test1 --linesTerminatedBy=$"\n" --fieldsTerminatedBy=","

WARNING: Using a password on the command line interface can be insecure.
ERROR: Target table is not set. The target table for the import operation must be provided in the options.

Finally this works fine using mysqlsh and JS command prompt

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: '"',threads: 3})
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 'test3.csv' (700 bytes) was imported in 0.0909 sec at 700.00 B/s
Total rows affected in test.sanjay_test1: Records: 10  Deleted: 0  Skipped: 0  Warnings: 0

Without JS mode, still issues with ERROR Missing closing quote
mysqlsh --uri=mysql://root:xxx@localhost:3306 -- util import-table test3.csv --schema=test --table=sanjay_test1 --dialect= "csv"  --fieldsTerminatedBy= "," --fieldsEnclosedBy= '"'  --fieldsOptionallyEnclosed=true --linesTerminatedBy= "\n"   

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

Any idea what is wrong with syntax when using mysqlsh without JS mode? Thanks a lot

Sanjay
  • 63
  • 7

0 Answers0