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 successfullyPlease provide your MySQL root password
Enter password:
MySQL UDF installed successfullyCreate 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=MyISAMStep-2. Create a sample file to load:
vi loadtest.txt
1
2
3Step-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
Not able to download Mysql UDF from below your mentioned url and getting below error
$ wget http://www.mysqludf.org/lib_mysqludf_sys/lib_mysqludf_sys_0.0.3.tar.gz
–10:28:38– http://www.mysqludf.org/lib_mysqludf_sys/lib_mysqludf_sys_0.0.3.tar.gz
=> `lib_mysqludf_sys_0.0.3.tar.gz’
Resolving http://www.mysqludf.org... done.
Connecting to http://www.mysqludf.org[192.30.252.154]:80... connected.
HTTP request sent, awaiting response… 404 Not Found
10:28:39 ERROR 404: Not Found.
can somebody please create batch file command for load.sh given above, thanks in advance.
Great post.
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
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.
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.sqlThe 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
Nice one Jake… After those permutations it must be a What the Fun moment for you 🙂
Cheers.
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
How about on Windows?
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.
I am trying to call a batch file from sys_exec as
do sys_exec(‘E:\load.bat’)
but it is not giving any result
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.
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.