MySQL Load Data Infile with Stored Procedure

Did you ever need to run LOAD DATA INFILE in a procedure? May be to automate or dynamically perform the large data file load to the MySQL database.

In this blog post, we will walk you through how to use a stored procedure and Load Data Infile syntax to efficiently load data into MySQL. This will also provide step-by-step instructions on how to create a table, a sample file to load, a shell script, and a stored procedure. By following these instructions, you will be able to effortlessly load data into MySQL.

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/]

Update: https://web.archive.org/web/20111016004142/http://www.mysqludf.org/lib_mysqludf_sys/lib_mysqludf_sys_0.0.3.tar.gz

Extract and Install MySQL UDF

[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

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.

How to load text file to MySQL using Stored Procedure & Load Data Infile 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.

35 comments
  1. 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.

  2. 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

    1. 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..

  3. 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.

  4. 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.

  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. 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

  7. Kedar,

    First, thanks for the reply …

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

    Now working …

    Thanks…

  8. 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!

    1. 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 !!

  9. 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

    1. Hi,

      Experiencing same issue here. Tried to recompile with 64bits command in Makefile.

      Command attempted:
      gcc -m64 -fPIC -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o /usr/lib/mysql/plugin/lib_mysqludf_sys.so -L/usr/lib64/libstdc++.so.6.0.13

      Error received:
      lib_mysqludf_sys.c:40:23: error: my_global.h: No such file or directory
      lib_mysqludf_sys.c:41:20: error: my_sys.h: No such file or directory
      lib_mysqludf_sys.c:43:19: error: mysql.h: No such file or directory
      lib_mysqludf_sys.c:44:21: error: m_ctype.h: No such file or directory
      lib_mysqludf_sys.c:45:22: error: m_string.h: No such file or directory
      make: *** [install] Error 1

      Context:
      Red Hat Enterprise Linux Server release 6.8 (Santiago)

      Any idea where issue could be coming from?

      1. Hi Kedar,

        Getting the below error while extracting the tar file….
        and also let me know what will be the size after downloading ???

        [root@mysqlserver2 hguggilla]# tar -xzvf lib_mysqludf_sys_0.0.3.tar.gz

        gzip: stdin: not in gzip format
        tar: Child returned status 1
        tar: Error is not recoverable: exiting now

      2. Also getting this error,

        [root@mysqlserver2 lib_mysqludf_sys-master]# 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
        make: gcc: Command not found
        make: *** [install] Error 127
        ERROR: You need libmysqlclient development software installed
        to be able to compile this UDF, on Debian/Ubuntu just run:
        apt-get install libmysqlclient15-dev

        Note : RHEL 6.4 version it is and I installed mysql 5.7.17 version on Linux machine, I required your assistance, Please get back to me at +917347060544 / 9916406504

Leave a Reply to Kedar Cancel reply

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