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.

Kedar

Related Tags

35 comments

Madhura says:

November 18, 2011 at 6:35 am

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.

Reply
Salini says:

July 21, 2011 at 6:45 am

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

Reply
1. Kedar says:
  
  July 21, 2011 at 6:59 am
  
  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..
  
  Reply
vijay says:

June 24, 2011 at 9:30 am

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.

Reply
Sandip says:

May 16, 2011 at 1:43 pm

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.

Reply
1. Arun says:
  
  June 24, 2011 at 9:33 am
  
  Excellent code sandip !
  
  Really it’s very useful for me….
  
  You are great…
  
  Reply
Sandip says:

May 16, 2011 at 1:40 pm

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

Reply
John says:

May 12, 2011 at 11:08 am

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

Reply
Sipauba says:

April 17, 2011 at 8:54 pm

Kedar,

First, thanks for the reply …

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

Now working …

Thanks…

Reply
1. Kedar says:
  
  April 18, 2011 at 6:03 am
  
  Sipaubha,
  
  Glad you verified it on windows 🙂
  
  Cheers,
  Kedar.
  
  Reply
2. prashant says:
  
  December 16, 2016 at 8:10 am
  
  dear friend
  can you please share your code as well as dll with me, I am also a windows user. thanks
  
  Prashant
  
  Reply
Sipauba says:

April 17, 2011 at 2:44 am

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!

Reply
1. Kedar says:
  
  April 17, 2011 at 5:19 am
  
  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 !!
  
  Reply
Nympheasi says:

March 10, 2011 at 5:53 pm

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

Reply
1. Kedar says:
  
  March 10, 2011 at 5:57 pm
  
  Hey Nympheasi,
  
  Thanks for the information.
  
  Cheers,
  Kedar.
  
  Reply
2. Alassane says:
  
  October 31, 2017 at 1:30 am
  
  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?
  
  Reply
huarong says:

December 8, 2010 at 12:49 pm

boring

Reply
1. Kedar says:
  
  December 9, 2010 at 5:24 am
  
  boring!!?? but it solves problem.
  What interests you Huarong?
  
  Reply
  1. Harikrishna Guggilla says:
    
    January 31, 2019 at 10:31 am
    
    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
    
    Reply
  2. Harikrishna Guggilla says:
    
    January 31, 2019 at 10:35 am
    
    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
    
    Reply