[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:
Centos 7
3.10.0-229.11.1.el7.x86_64 #1 SMP Thu Aug 6 01:06:18 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

DB Servers:
Mariadb 10.0.21-MariaDB-wsrep-log 3 node galera cluster wsrep_25.10.r4144
3.10.0-229.11.1.el7.x86_64 #1 SMP Thu Aug 6 01:06:18 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

Using readwritesplit router


Attachments: File Appearances.csv     HTML File Appearances00-116     HTML File Appearances00-117     HTML File Appearances00-117-all     File debug-log-anon.log     File trace-log-anon.log    
Issue Links:
Relates
relates to MXS-593 Load data local infile returns before... Closed
relates to MXS-1160 Load infile not working on Schemarouter Closed

 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:
ERROR 2013 (HY000): Lost connection to MySQL server during query

And the server Logs:
150915 14:55:29 [Warning] Aborted connection 566976 to db: 'thomas' user: 'linuxtest' host: '10.98.1.216' (Unknown error)

When issuing the same Load Data Local statement against one of the galera nodes directly all rows are inserted as expected (with expected warnings):
Query OK, 99466 rows affected, 65535 warnings (1.29 sec)
Records: 99466 Deleted: 0 Skipped: 0 Warnings: 213769

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]
threads=1

[Splitter Service]
type=service
router=readwritesplit
router_options=disable_sescmd_history=true,disable_slave_recovery=true,master_accept_reads=true
servers=server-db1,server-db2,server-db3
user=maxscale-lb
passwd=SCRUBBED

[Splitter Listener]
type=listener
service=Splitter Service
protocol=MySQLClient
port=3306
socket=/tmp/ClusterMaster

[server-db1]
type=server
address=10.98.1.221
port=3306
protocol=MySQLBackend

[server-db2]
type=server
address=10.98.1.222
port=3306
protocol=MySQLBackend

[server-db3]
type=server
address=10.98.1.223
port=3306
protocol=MySQLBackend

[Galera Monitor]
type=monitor
module=galeramon
disable_master_failback=1
monitor_interval=2500
servers=server-db1,server-db2,server-db3
user=maxscale-lb
passwd=SCRUBBED

[CLI]
type=service
router=cli

[CLI Listener]
type=listener
service=CLI
protocol=maxscaled
address=localhost
port=6603

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 attached some more files:
Appearances-116 - is a subset of Appearances.csv has 1166 lines and is 65478 bytes - Loads successfully
Appearances-117 - is a subset of Appearances.csv has 1167 lines and is 65538 bytes - Loads unsuccessfully, connection aborted
Appearances00-117-all - is the same as Appearances-117 except it has the fields that were previously empty filled with 0's has 1167 lines and is 67807 bytes, Loads successfully

I have also attached:
trace-log-anon.log: slightly anonymized trace log of the failed load
debug-log-anon.log: slightly anonymized debug log of the failed load.

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:

2015-09-17 10:30:35   [4]  > Autocommit: [enabled], trx is [not open], cmd: COM_DEBUG, type: QUERY_TYPE_SESSION_WRITE, stmt: 
 
2015-09-17 10:30:35   [4]  Session write, routing to all servers.
2015-09-17 10:30:35   [4]  Route query to master 	10.98.1.223:3306 
2015-09-17 10:30:35   [4]  Route query to slave 	10.98.1.222:3306 
2015-09-17 10:30:35   [4]  > Autocommit: [enabled], trx is [not open], cmd: UNKNOWN MYSQL PACKET TYPE, type: N/A, stmt:  
2015-09-17 10:30:35   [4]  Route query to master 	10.98.1.223:3306 <
2015-09-17 10:30:35   [4]  Backend 10.98.1.222:3306 processed reply and starts to execute active cursor.
2015-09-17 10:30:36   [4]  Stopped Splitter Service client session [4]

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.
Follows the error log:

2017-03-03 09:32:15 error : [qc_sqlite] The query could not be parsed. Response not valid.
2017-03-03 09:32:15 error : [qc_sqlite] The provided buffer does not contain a COM_QUERY, but a UNKNOWN MYSQL PA
CKET TYPE. (subsequent similar messages suppressed for 10000 milliseconds)
2017-03-03 09:32:15 error : [qc_sqlite] The query could not be parsed. Response not valid. (subsequent similar m
essages suppressed for 10000 milliseconds)
2017-03-03 09:32:15 error : [schemarouter] Failed to execute

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.

Generated at Thu Feb 08 03:58:45 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.