7 Feb 2012

LOAD DATA INFILE to resolve locks

In relation to this MPB post, there is a command to output data into a file and then to load it back into MySQL to resolve locks. (An example of a really big reporting query that could be quite heavy, can be found here.)

There is an issue with outputting data into a file through MySQL if the file already exists. For example:


mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed 
mysql> select * from user into outfile '/tmp/user.sql';
Query OK, 10 rows affected (0.00 sec) 
mysql> select * from user into outfile '/tmp/user.sql';
ERROR 1086 (HY000): File '/tmp/user.sql' already exists


So to resolve this, I played around with the \! command (which lets you run commands on your OS):

mysql> \! rm -rf /tmp/user.sql
mysql> select * from user into outfile '/tmp/user.sql';
Query OK, 10 rows affected (0.00 sec) 
mysql> \! rm -rf /tmp/user.sql
mysql> select * from user into outfile '/tmp/user.sql';
Query OK, 10 rows affected (0.00 sec)

This seemed to have worked.

Apart from security issues and OS specific commands, anyone see anything wrong with this approach?
Perhaps I should wrap it around in a stored procedure..

3 comments:

  1. \! is a client command. It won't work inside a stored procedure.

    ReplyDelete
  2. I guess I would need this then:
    http://www.mysqludf.org/lib_mysqludf_sys/index.php

    ReplyDelete
  3. You can use formatted timestamp stored in a variable and use prepared statement!!

    ReplyDelete