[MXS-365] Load data local infile connection abort when loading certain files Created: 2015-09-15 Updated: 2017-03-03 Resolved: 2015-11-04 |
|
| Status: | Closed |
| Project: | MariaDB MaxScale |
| Component/s: | Core, mariadbbackend, readwritesplit |
| Affects Version/s: | 1.2.0, 1.2.1 |
| Fix Version/s: | 1.3.0 |
| Type: | Bug | Priority: | Major |
| Reporter: | ThomasStrang | Assignee: | markus makela |
| Resolution: | Fixed | Votes: | 0 |
| Labels: | None | ||
| Environment: |
Load Balancer: DB Servers: Using readwritesplit router |
||
| Attachments: |
|
||||||||||||
| Issue Links: |
|
||||||||||||
| Description |
|
Hello, When Using Load Data Local to load certain csv files the connection is dropped on both the client and server side and without any data being inserted, I guess it fails before even the first transaction auto commit. The client receives: And the server Logs: When issuing the same Load Data Local statement against one of the galera nodes directly all rows are inserted as expected (with expected warnings): Sample CSV file Attached as Appearances.csv (sampled from http://seanlahman.com/baseball-archive/statistics/), Table Create Statement (innodb): CREATE TABLE BaseBallTest (id INT(5) PRIMARY KEY AUTO_INCREMENT, yearID INT(5),teamID VARCHAR(10),lgID VARCHAR(10),playerID VARCHAR(15),G_all INT(5),GS INT(5),G_batting INT(5),G_defense INT(5),G_p INT(5),G_c INT(5),G_1b INT(5),G_2b INT(5),G_3b INT(5),G_ss INT(5),G_lf INT(5),G_cf INT(5),G_rf INT(5),G_of INT(5),G_dh INT(5),G_ph INT(5),G_pr INT(5) ); CSV Loaded with: LOAD DATA LOCAL INFILE 'Appearances.csv' INTO TABLE BaseBallTest FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS (yearID,teamID,lgID,playerID,G_all,GS,G_batting,G_defense,G_p,G_c,G_1b,G_2b,G_3b,G_ss,G_lf,G_cf,G_rf,G_of,G_dh,G_ph,G_pr); Interestingly I have also tested with a larger csv (about 9MB) consisting of the first 50 or so rows repeating with all values included which completed successfully. BEGIN Maxscale Config:=================================== [maxscale] [Splitter Service] [Splitter Listener] [server-db1] [server-db2] [server-db3] [Galera Monitor] [CLI] [CLI Listener] END Maxscale Config:=================================== |
| Comments |
| Comment by ThomasStrang [ 2015-09-16 ] | |||||||||
|
I should also add loading that csv file with the readconnroute router seems to work fine as well | |||||||||
| Comment by ThomasStrang [ 2015-09-17 ] | |||||||||
|
Hello, I have been able to spend more time on this and appear to have narrowed down where the problem lies, I have also attached: | |||||||||
| Comment by ThomasStrang [ 2015-10-05 ] | |||||||||
|
Just tested 1.2.1 and the problem is still present | |||||||||
| Comment by markus makela [ 2015-10-26 ] | |||||||||
|
This is most likely due to data being misinterpreted as MySQL commands. From the trace log:
It seems it thinks that it's receiving separate queries although it is actually the same LOAD DATA LOCAL INFILE. That COM_DEBUG is something that shouldn't be there and is what's causing it to break. | |||||||||
| Comment by markus makela [ 2015-11-03 ] | |||||||||
|
Added proper tracking of LOAD DATA LOCAL INFILE state. | |||||||||
| Comment by markus makela [ 2015-11-04 ] | |||||||||
|
The state of a LOAD DATA LOCAL INFILE is now properly tracked in the readwritesplit router and the whole transmission will be sent to the master. | |||||||||
| Comment by Marcelo [ 2017-03-03 ] | |||||||||
|
Found exactly the same issue using the schemarouter module. Works fine if the ReadWritesplit module is used. Is this a limitation of schemarouter? Tested with the latest 2.1.0 version and also with the previous ones. 2017-03-03 09:32:15 error : [qc_sqlite] The query could not be parsed. Response not valid. | |||||||||
| Comment by markus makela [ 2017-03-03 ] | |||||||||
|
That seems like a bug in the schemarouter. Please open a new bug report for it and we'll take a look at it. |