Proxysql: Load Data Local Infile

[jeniok]

Hi Rene,
I'm using ProxySQL with Percona MySQL. I'm doing LOAD DATA LOCAL INFILE which works without ProxySQL but the same command when run through ProxySQL returns not found (Errcode: 2).

Please advise
thanks

Jenia

[renecannao]

Hi Jenia,

Thank you for the report.
Indeed, LOAD DATA LOCAL INFILE is yet not supported.
For reference, this is a link that describes the protocol.

I have to test it, but reading the protocol implementation and the error you reported I assume the error is generated this way:

1. the client sends the request to the proxy
2. the proxy sends the same request to the backend
3. the backend requests the file from the proxy
4. the proxy cannot find the file (locally to the proxy)

I will work this issue in two stages:

• [ ] prevent the execution of LOAD DATA LOCAL INFILE returning immediately an error to the client stating that is yet not supported: this has high priority and will be implemented in all current versions
• [ ] add a new state, allowing the transfer of file from the client to the backend: this will be implemented in 1.4.x

Thanks,
René

[jeniok]

thanks a lot, you are doing a great job!

[hobbsm]

Hi Rene,

Do you know if a fix is likely to come soon?

We're rolling out ProxySQL as a replacement for MaxScale and this is a small corner case in our code.

We can develop a workaround using a shared filesystem or similar but I just wanted to check a fix wasn't imminent.

Thanks,
Mike

[renecannao]

Hi Mke,

unfortunately, support for LOAD DATA LOCAL INFILE is not in the short term roadmap: isn't trivial to implement.
Thanks

[hobbsm]

No problem, we'll work around it.
Thanks!

[techwizardg]

@hobbsm how did you work around the problem load data local infile as we want to use ProxySQL and this is becoming a showstopper

[shrestha-s]

Hi Rene,

This query succeeds when fast_forward is ON. Does ProxySQL transfer the file to the Proxy node for this to happen? Or is there another mechanism by which Proxy is able to load the file?

Thanks!

[Dieken]

@renecannao

Is it possible to temporarily enable fast_forward for "LOAD DATA LOCAL INFILE" ?

[yenlian]

Hi, I'm currently using ProxySQL version 2.0.4-116-g7d371cf

When tried to issue LOAD DATA LOCAL INFILE command it prompts error

ERROR 13 (HY000): File '/home/abc/file.csv' not found (Errcode: 13)

It works on version ProxySQL version 1.4.13-15-g69d4207

[mcrauwel]

We are seeing a crash of the proxy related to LOAD DATA LOCAL INFILE

First there are a lot of errors logged where it says File not found which was to be expected but then we see a crash happening:

2019-09-05 14:10:13 MySQL_Session.cpp:2934:handler(): [WARNING] Error during query on (0,xx.xx.xx.xx,3306): 2, File '[somepath-to-a-file]' not found (Errcode: 2)
2019-09-05 14:10:13 MySQL_Session.cpp:2934:handler(): [WARNING] Error during query on (0,xx.xx.xx.xx,3306): 2, File '[somepath-to-a-file]' not found (Errcode: 2)
2019-09-05 14:10:13 MySQL_Session.cpp:2934:handler(): [WARNING] Error during query on (0,xx.xx.xx.xx,3306): 2, File '[somepath-to-a-file]' not found (Errcode: 2)
2019-09-05 14:10:13 MySQL_Session.cpp:2934:handler(): [WARNING] Error during query on (0,xx.xx.xx.xx,3306): 2, File '[somepath-to-a-file]' not found (Errcode: 2)
2019-09-05 14:10:13 MySQL_Session.cpp:2934:handler(): [WARNING] Error during query on (0,xx.xx.xx.xx,3306): 2, File '[somepath-to-a-file]' not found (Errcode: 2)
2019-09-05 14:10:13 MySQL_Session.cpp:2934:handler(): [WARNING] Error during query on (0,xx.xx.xx.xx,3306): 2, File '[somepath-to-a-file]' not found (Errcode: 2)
2019-09-05 14:10:13 MySQL_Session.cpp:2934:handler(): [WARNING] Error during query on (0,xx.xx.xx.xx,3306): 2, File '[somepath-to-a-file]' not found (Errcode: 2)
2019-09-05 14:10:13 MySQL_Session.cpp:2934:handler(): [WARNING] Error during query on (0,xx.xx.xx.xx,3306): 2, File '[somepath-to-a-file]' not found (Errcode: 2)
2019-09-05 14:10:13 MySQL_Session.cpp:2934:handler(): [WARNING] Error during query on (0,xx.xx.xx.xx,3306): 2, File '[somepath-to-a-file]' not found (Errcode: 2)
2019-09-05 14:10:13 MySQL_Session.cpp:2934:handler(): [WARNING] Error during query on (0,xx.xx.xx.xx,3306): 2, File '[somepath-to-a-file]' not found (Errcode: 2)
Error: signal 11:
/usr/bin/proxysql(_Z13crash_handleri+0x1a)[0x44c87a]
/lib64/libc.so.6(+0x36280)[0x7fbcc73b1280]
/usr/bin/proxysql[0x618156]
/usr/bin/proxysql(mysql_handle_local_infile+0x85)[0x6182f5]
/usr/bin/proxysql(mthd_my_read_query_result+0x85)[0x609fb5]
/usr/bin/proxysql[0x60f86f]
/usr/bin/proxysql(my_context_spawn+0x41)[0x60ec81]
2019-09-05 14:11:03 main.cpp:910:ProxySQL_daemonize_phase3(): [ERROR] ProxySQL crashed. Restarting!
2019-09-05 14:11:03 [INFO] Angel process started ProxySQL process 80629

The version used is

# proxysql --version
ProxySQL version 1.4.14-percona-1.1, codename Truls

We've currently worked around it by blocking the LOAD DATA LOCAL INFILE queries using the query-rules to firewall out the these queries. But I wanted to alarm here that the implementation of the first stage of the solution seems not entirely bulletproof...

mysql> SELECT rule_id, active, apply, match_pattern, error_msg FROM mysql_query_rules;
+---------+--------+-------+-------------------------+--------------------------------------------------------------+
| rule_id | active | apply | match_pattern           | error_msg                                                    |
+---------+--------+-------+-------------------------+--------------------------------------------------------------+
| 1       | 1      | 1     | ^LOAD DATA LOCAL INFILE | LOAD DATA LOCAL INFILE commands are currently not supported! |
+---------+--------+-------+-------------------------+--------------------------------------------------------------+
1 row in set (0.00 sec)

[dejwsz]

Is "DATA LOAD INFILE" still unsupported (in version 2.0.X) ?

[leo8504]

Facing this issue in Debian 10. Same Proxysql version was working fine in Debian 8:
ERROR 2 (HY000) at line 1: File './upfront.csv' not found (Errcode: 2)
Proxysql version:
$ proxysql --version ProxySQL version 2.0.14-70-g7b02b19c, codename Truls

We found the issue while upgrading from Debian 8 to Debian 10. Found a couple of mentions online to apparmor causing this issue but haven't found anything in the logs suggesting this is the case.

Thanks.

[dejwsz]

So it means that proxysql itself supports it now but other security mechanisms can restrict it in some cases, yes?

[jpschultz]

I'm running into the same issue. Running Ubuntu 18.04.4 and trying to load it from the command line or from python via sqlalchemy:

LOAD DATA LOCAL INFILE 'filename.csv' INTO TABLE schmea.table_name CHARACTER SET UTF8 FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 LINES;

keeps failing with:

ERROR 2 (HY000): File 'filename.csv' not found (Errcode: 2)

Obviously filename.csv is my real filename and it does exist and i'm pointing to a table that also exists.

ProxySQL version: 2.0.14-70-g7b02b19, codename Truls

[dejwsz]

Can anyone confirm it is possible to enable this feature with ProxySQL 2.x ? Does ProxySql support it by itself ?

[droh99]

@renecannao please can you confirm or decline this is implemented in ProxySQL 2.x? Or please give us some update when it will be fixed. Thank you!

[leo8504]

An update here. The issue happened when we tried to load a file that was stored in a home directory, like /home/lolo no matter what permissions the file had it didn't work. So we tried placing the file and a different path. We moved the file to /opt and it worked just fine. It seems there is some security around home directories in Debian 10.

[dejwsz]

@leo8504 so it means it works in ProxySQL 2.x and it is a case of some security related things. I tested it in docker containers as a part of percona xtradb operator and there it is not working. From your update I can conclude there are some security related things than proxysql shortage of support for this.

[Hornswoggles]

I verified that LOAD DATA LOCAL INFILE does not work on 2.0.17 nor 2.1.0

Connecting through proxysql

mysql> LOAD DATA LOCAL INFILE './test.csv' into table t1 fields terminated by ',' (a,b,c);
ERROR 2 (HY000): File './test.csv' not found (Errcode: 2)

Connecting directly to mysql

mysql> LOAD DATA LOCAL INFILE './test.csv' into table t1 fields terminated by ',' (a,b,c);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

Proxysql Log message:

2021-03-23 18:46:40 MySQL_Session.cpp:3897:handler_minus1_LogErrorDuringQuery(): [WARNING] Error during query on (1,10.10.27.220,11016,3746): 2, File './test.csv' not found (Errcode: 2)

[droh99]

Yes, unfortunately this problem is still actual. We already have a workaround using prepared statements and executemany method in our source codes. It would be nice to have LOAD DATA LOCAL INFILE back, but we can live without it for now...