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..
\! is a client command. It won't work inside a stored procedure.
ReplyDeleteI guess I would need this then:
ReplyDeletehttp://www.mysqludf.org/lib_mysqludf_sys/index.php
You can use formatted timestamp stored in a variable and use prepared statement!!
ReplyDelete