30 Nov

Using LOAD DATA INFILE with Stored Procedure Workaround-MySQL

Okay! So here we will use Load Data syntax to load file into MySQL Server in a Stored procedure. Yep! It’s a workaround.

Download MySQL UDF:

[root@localhost kedar]# wget http://www.mysqludf.org/lib_mysqludf_sys/lib_mysqludf_sys_0.0.3.tar.gz

[refer: http://www.mysqludf.org/]

Extract and Install:

[root@localhost kedar]# tar -xzvf lib_mysqludf_sys_0.0.3.tar.gz
install.sh
lib_mysqludf_sys.c
lib_mysqludf_sys.html
lib_mysqludf_sys.so
lib_mysqludf_sys.sql
Makefile

[root@localhost kedar]# sh install.sh
Compiling the MySQL UDF
gcc -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o /usr/lib/lib_mysqludf_sys.so
MySQL UDF compiled successfully

Please provide your MySQL root password
Enter password:
MySQL UDF installed successfully

Create function sys_exec as follows:

CREATE FUNCTION sys_exec RETURNS INT SONAME ‘lib_mysqludf_sys.so';

sys_exec – executes an arbitrary command, and returns it’s exit code.

You also can similarly create functions:
sys_eval – executes an arbitrary command, and returns it’s output.
sys_get – gets the value of an environment variable.
sys_set – create an environment variable, or update the value of an existing environment variable.

Example – How to load txt file to MySQL using Stored Procedure & Load Data syntax:

Step-1. Creating table:

CREATE TABLE `t` ( `id` int(2) default NULL ) ENGINE=MyISAM

Step-2. Create a sample file to load:

vi loadtest.txt
1
2
3

Step-3. Create a shell script:

vi /tmp/load.sh
mysql -u mysql_user -p mysql_password -e “load data local infile \”$1\” into table $2;”

Step-4. Create a Stored Procedure:

DELIMITER $$
DROP PROCEDURE IF EXISTS `load_data_SP` $$
CREATE PROCEDURE `load_data_SP` (in_filepath varchar(100),in_db_table varchar(100))
BEGIN
declare exec_str varchar(500);
declare ret_val int;
set exec_str=concat(“sh /tmp/load.sh “,in_filepath,” “, in_db_table);
set ret_val=sys_exec(exec_str);
if ret_val=0 then
select “Success” as Result;
else
select “Please check file permissions and paths” as Result;
end if;
END $$
DELIMITER ;

Step 5. Execute:

CALL `load_data_SP`(‘/tmp/loadtest.txt’ , ‘test.t’);

…and that’s it Stored Procedure will return Success or Failure accordingly.

Make sure you’re having file permissions well set and MySQL can access the files.
Here I’ve kept the files under /tmp directory with chmod 777 & chown mysql:mysql to remove the permission-issue possibility.

Hope this helps.

29 thoughts on “Using LOAD DATA INFILE with Stored Procedure Workaround-MySQL

  1. Just a quick information for those using Ubuntu 10.04 64bits and has compiled mysql 5.5.9 manually:

    – Edit Makefile:
    –> Replace LIBDIR=/usr/lib by LIBDIR=/usr/local/mysql/lib/plugin
    –> Replace gcc -fPIC -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o $(LIBDIR)/lib_mysqludf_sys.so by gcc -fPIC -Wall -I/usr/local/mysql/include -I. -shared lib_mysqludf_sys.c -o $(LIBDIR)/lib_mysqludf_sys.so

  2. Kedar,

    First let me say that I am a Windows user …

    So …

    I generated a ‘dll’ file from the C, as Roland Bouman taught in his article:
    http:// rpbouman.blogspot.com/2007/09/creating-mysql-udfs-with-microsoft.html

    But when I try to adapt your Stored Procedure with a file “.bat” could not run Load Data …

    So … the implementation varies from one operating system to another, or the structure is the same that you used?

    Do you have any examples of use in windows?

    Thanks!

    • Sipauba,

      Just verify:
      – Make sure your dll / udf is installed & working.
      – What’s your bat file? Does it execute the load command when run manually, from outside the SP?
      – You changed SP, no path issues and all?
      – Check if mysql can access your bat file (are you on Win7/Vista)

      If sys_exec is running fine on your windows than there shouldn’t be any issue for it to run in above procedure.

      I’m sorry I haven’t tried it on windows (as I’m mostly on *nix) but if you can post your changes in above code I may try to lookup !!

  3. Kedar,

    First, thanks for the reply …

    That’s what you said, sys_exec not access the file, but for my mistake …

    Now working …

    Thanks…

  4. For my Ubuntu 11.04 (64 bit) I had to edit the Makefile and change

    LIBDIR=/usr/lib to LIBDIR=/usr/lib/mysql/plugin

    Also make sure that gcc has the -fPIC option ie:

    gcc -fPIC -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o $(LIBDIR)/lib_mysqludf_sys.so

  5. Below code may usefull to window user.

    Below is some predefine condition to start this utility.

    1) File Execute.bat must be exist at path(C:\Program Files\MySQL\MySQL Server 5.0\data\hvl)
    2) Backup folder must be exist on path(C:\Program Files\MySQL\MySQL Server 5.0\data\hvl)
    3) C:\Program Files\MySQL\MySQL Server 5.0\data\hvl\*.asc file must be unix format.
    4) C:\Program Files\MySQL\MySQL Server 5.0\data\hvl\*.asc with full rights.
    5) mysql must be installed.

    What Execute.bat is do.
    ************************

    1) Pointer move to specific path.
    2) Collect all (*.asc) files.
    3) Start the loop for one by one .asc file.
    4) If file not found, exit the loop.
    5) copy files to backup tables
    6) Execute mysql command.
    7) Select specific database (with “use” mysql command)
    8) Read parameter file name and load this data to mysql table(Table name is hardcoded)
    9) Delete file from source folder as we have already take the backup in step#5.

    Execute.bat file code

    cd “C:\Documents and Settings\sandip”
    cd “C:\Program Files\MySQL\MySQL Server 5.0\data\hvl\”
    FOR %%a IN (*.asc) DO (
    IF %%a==null GOTO :end
    copy %%a “C:\Program Files\MySQL\MySQL Server 5.0\data\hvl\Backup”
    mysql -e “use hvl; LOAD DATA INFILE ‘%%a’ INTO TABLE file_lists FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ IGNORE 1 LINES;”
    del %%a
    echo %%a)
    :end

    Table file_lists
    ——————–

    create table file_lists
    (
    FILE_DATA VARCHAR(4000),
    LOAD_FLAG VARCHAR(1)
    )

  6. Creat Test.asc file with below data.

    FIRST_FILE,0
    SECOND_FILE,0
    THIRD_FILE,0
    FOUR_FILE,1
    FIVE_FILE,1
    SIX_FILE,0
    SEVEN_FILE,1

    After execute the Execute.bat file it will insert the data into file_lists table.

    Regards,
    Sandip.

  7. Hi sandip,

    Yes, your code is work, This is the way to load the data into file…

    Thank you very much, This is very useful information for me.

    Regards,
    vijay.

  8. Hi Kedar,

    I tried executing your code on my database which is installed on RHEL 5.4 and MYSQL5.1.
    But I am getting the follwong error

    FUNCTION dbname.sys_exec does not exist Errorno :1305

    Please let me know how to fix this.
    Regards,
    Salini Joseph

    • Hi Salini,

      I’m not sure if you’ve installed the udf !!
      Try creating function as follows:

      CREATE FUNCTION sys_exec RETURNS INT SONAME ‘lib_mysqludf_sys.so';

      Thanks for pointing…updated above..

  9. Hey Kedar,

    I refered to your link kedar.nitty-witty.com/blog/using-load-data-infile-with-stored-procedure-workaround-mysql.

    But the thing is I am using Windows XP. Can you tell me what should i do to make this work in Windows? I am a new bee to my sql.

  10. I try to execute sys_exec with string (not from file).
    but it gives me error ‘Please check file permissions and paths’

    please help how to solve this ?.Thanks

    • Hey Emmanuel,

      Can you tell me what exactly you’re doing?
      You can also debug this by checking out what exact value is being passed in the exec_str by adding an extra line to the procedure as follows:

      set exec_str=concat(“sh /tmp/load.sh “,in_filepath,” “, in_db_table);
      select exec_str;
      set ret_val=sys_exec(exec_str);

      Let me know,
      Thanks.

    • Hey Red83,

      Did you check the comments? I’m pasting it here again for your convenience:

      Just verify:
      – Make sure your dll / udf is installed & working.
      – What’s your bat file? Does it execute the load command when run manually, from outside the SP?
      – You changed SP, no path issues and all?
      – Check if mysql can access your bat file (are you on Win7/Vista)

      If sys_exec is running fine on your windows than there shouldn’t be any issue for it to run in above procedure.

      I’m sorry I haven’t tried it on windows (as I’m mostly on *nix) but if you can post your changes in above code I may try to lookup !!

      Let me know.

    • Hi Uma,

      I’ve not tried this on Windows though I expect it should be working… would you mind sharing your attempt or error you faced!?

      Regards,
      Kedar.

  11. Hi
    Did anyone manage to get this to work on a windows server?
    I’ve tried but unsuccessful.
    I created a SP with the following:
    declare exec_str varchar(500);
    set exec_str="cmd c:/devc_import2.txt";
    do sys_exec(exec_str);

    The dvc_import.txt is as follows:


    mysql --user=username --password=password --database=bd_name -e "load data local infile 'c:/Allproperties.csv' into table devc_import FIELDS TERMINATED BY ','ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 ROWS (dc_PropertyID,@DevelopmentID,@RegionID,@HouseTypeID,......)set dc_DevelopmentID = IF(@DevelopmentID='',null,@DevelopmentID),......;"

    The csv file I’m trying to import has only 410 rows but has approx. 145 columns.

    I can run the contents od the txt file directly form a command window and it works great but when I run the SP the browser just waits. Looking in task manager I see the cmd process but it doing nothing.

    Any help most appreciated.

    Jake

  12. Hi all

    Got it working :)

    I created a batch file called import.bat
    The contents are:

    mysql --user=username --password=password --database=db_name < c:/devc_import2.sql

    The sql file is the script I created in Workbench.

    In the SP the only change I made is: set exec_str="c:/import.bat";

    It worked!

    Hope this helps – I spent a full day trying so many permutations and it turned out to be easy.

    Jake

  13. Thanks for the information Kedar ……

    “mysql> CALL sp_load_data(‘/tmp/loadtest.txt’ , ‘optimuserp_gdm.t’);
    +————————————————————-+———+
    | exec_str | ret_val |
    +————————————————————-+———+
    | sh /home/tsd/tmp/load.sh /tmp/loadtest.txt optimuserp_gdm.t | 32256 |
    +————————————————————-+———+
    1 row in set (0.05 sec)

    +—————————————–+
    | Result |
    +—————————————–+
    | Please check file permissions and paths |
    +—————————————–+
    1 row in set (0.05 sec)

    Query OK, 0 rows affected (0.05 sec)

    mysql>

    please help how to solve this ?.Thanks

    • Hi Anirudhdha,

      I’d encourage you to debug the issue here as such system call fails. Can you confirm you shell script is running fine manually?

      See too run it from prompt:
      sh /home/tsd/tmp/load.sh /tmp/loadtest.txt optimuserp_gdm.t

      Thanks.

  14. Hi Kedar,

    mysql -uusername -ppassword -e “load data local infile \”$1\” into table $2;”
    In load.sh file, replaced only “ to ” and also debugging the all scenarios.

    Done…. your code is work :)

    Thank you very much…

    Regards,
    Aniruddha

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

-- Kedar Vaijanapurkar --