MySQL Bugs: #67178: Error on handle LOAD DATA INFILE LOCAL through lua script

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

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.

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.

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.

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,

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!

Hi,

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

Thanks

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.

Thank you for the report.

Please explain how do you use LOAD DATA INFILE under lua script. How do you start proxy?

Example of lua script

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

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.

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?

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.

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

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

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"

Test in my environment

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

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.

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