Bug #67178 Error on handle LOAD DATA INFILE LOCAL through lua script
Submitted: 10 Oct 2012 12:58 Modified: 12 Jan 2013 7:46
Reporter: Daniele Rondina (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Proxy Severity:S3 (Non-critical)
Version:0.8.3 OS:Linux
Assigned to: Assigned Account CPU Architecture:Any
Tags: Contribution, LOAD_DATA_INFILE_LOCAL
Triage: Needs Triage: D2 (Serious)

[10 Oct 2012 12:58] Daniele Rondina
Description:
Currently LOAD DATA INLINE FILE is not usable under lua script. It works only without lua script.

How to repeat:
It is needed only execute a LOAD DATA INFILE LOCAL and add a simply lua script that remap target table to another table with some columns.
[10 Oct 2012 12:59] Daniele Rondina
This patch to proxy-plugin permit to fix this bug

Attachment: mysql-proxy-0.8.3-load_data_infile_local.patch (text/x-patch), 4.07 KiB.

[10 Oct 2012 13:00] Daniele Rondina
This patch clean sources and split event handler to permit an more readable code

Attachment: mysql-proxy-0.8.3-event_handler_split.patch (text/x-patch), 127.20 KiB.

[10 Oct 2012 13:02] Daniele Rondina
Attached patch permit now to handle on lua script LOAD DATA query and change target table.
A future enhancement could be pass infile data to lua script and permit parsing of the incoming file.

Regards,
[10 Oct 2012 15:14] Todd Farmer
Hello Daniele,

First, thank you for your bug report and patch submission.  Please note that we cannot review or consider your patch unless it is submitted under the terms of the Oracle Contributor Agreement.  Please see the Contributions tab for details:

http://bugs.mysql.com/bug.php?id=67178&contribs=1

Thanks!
[30 Oct 2012 12:36] Daniele Rondina
Hi,

I sent email with OCA signed without answer. Any suggestions?

Thanks
[23 Nov 2012 9:08] Daniele Rondina
Add a new version of previous patch without printf for debug

Attachment: mysql-proxy-0.8.3-event_handler_split.patch (text/x-patch), 126.85 KiB.

[14 Dec 2012 17:30] Sveta Smirnova
Thank you for the report.

Please explain how do you use LOAD DATA INFILE under lua script. How do you start proxy?
[15 Dec 2012 18:03] Daniele Rondina
Example of lua script

Attachment: example_load_data_infile.lua (text/x-lua), 1.88 KiB.

[15 Dec 2012 18:32] Daniele Rondina
Currently, with my patch it is possible only change destination table of the LOAD DATA. Moreover, destination table must be with same columns of the real target table.
It isn't possible for now to edit data directly but through and injection of a procedure/function in same session it easy process data saved to table.
So, in summary for now it is possible:
- catch LOAD DATA LOCAL INFILE for block query;
- catch LOAD DATA LOCAL INFILE and change destination table;
- add injection query on LOAD DATA LOCAL INFILE.

On attachment example it is possible to see how change destination table and inject a query.

Regards.
[18 Dec 2012 16:16] Sveta Smirnova
Thank you for the feedback.

I can not repeat described behavior if paste definition of function split from here: http://lua-users.org/wiki/SplitJoin into your lua script

Query got rewritten fine and I can insert values into table NEW_TABLE. Which issues do you experience?
[22 Dec 2012 0:55] Daniele Rondina
I'm sorry. Add this function:

-- Split function
function split(str, pat)
   local t = {}  -- NOTE: use {n = 0} in Lua-5.0
   local fpat = "(.-)" .. pat
   local last_end = 1
   local s, e, cap = str:find(fpat, 1)
   while s do
     if s ~= 1 or cap ~= "" then
       table.insert(t,cap)
     end
     last_end = e+1
     s, e, cap = str:find(fpat, last_end)
   end
   if last_end <= #str then
     cap = str:sub(last_end)
     table.insert(t, cap)
   end
   return t
end

Issue is that without my patch on mysql-proxy-0.8.3 it isn't possible inject query on load data infile query. Code for this that is been added with my patch.
[24 Dec 2012 16:50] Sveta Smirnova
Thank you for the feedback.

But I can inject queries for LOAD DATA LOCAL INFILE even without your patch!

Which version of MySQL Server do you use? Please add debugging information to your script like:

function read_query(packet)
  if packet:byte() ~= proxy.COM_QUERY then return end

  local q = packet:sub(2)

  if DEBUG then
    log( "Received query: " .. q, MSG_IMPORTANT )
  end

  if string.find( string.lower( q ), 'load data local' ) then

print("Found LOAD DATA query: " .. q)

    local table_name = split( q, "%s" )
    local tbl_name  = string.gsub( table_name[8], "`", "" )
    local file_name = string.gsub( table_name[5], "'", "" )

    local tmp_table = "NEW_TABLE"

print("Injecting new query: " .. "LOAD DATA INFILE '" .. file_name .. "' INTO TABLE `" .. tmp_table .. "`")

and send us output of MySQL Proxy
[2 Jan 2013 15:39] Daniele Rondina
Hi,

currently I use version 0.8.3 as indicated on bug ticket.

Can you send my an example that works?

I don't understand how could be possible that my example works if on proxy-plugin.c under proxy_send_local_infile_result function it isn't present code that handle injected query queued after LOAD DATA INFILE LOCAL. Code instead fixed on my code.

Bye
[10 Jan 2013 18:20] Sveta Smirnova
Thank you for the feedback.

I used your Lua script with few more print functions for debugging and fresh 0.8.3 MySQL Proxy.

See file attached.

Closing report as "Can't repeat"
[10 Jan 2013 18:21] Sveta Smirnova
Test in my environment

Attachment: cantrepeat.txt (text/plain), 7.88 KiB.

[10 Jan 2013 22:19] Daniele Rondina
Hi,

your test is wrong!!!

On your cantrepeat.txt you use LOAD DATA INFILE and NOT
LOAD DATA LOCAL INFILE!!!

With LOAD DATA INFILE file that must be loaded is already on server and so communication between client and server it is only relative to LOAD statement (without data).
Instead, with LOAD DATA LOCAL INFILE file that must be loaded is on client computer and not on server machine.

1) try to use LOAD DATA LOCAL INFILE (and not LOAD DATA INFILE)
2) use two different machine for server Mysql and client. From your output it seems that mysql client is on same machine of the mysql server.
[12 Jan 2013 7:46] Sveta Smirnova
Thank you for the feedback.

With remote host issue is perfectly repeatable.

I got following errors:

2013-01-12 09:45:45: (critical) network-mysqld.c:613: received packet-id 2, but expected 1 ... out of sync.
2013-01-12 09:45:45: (critical) network-mysqld.c.1728: network_mysqld_read(CON_STATE_READ_QUERY_RESULT) returned an error