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