On my last project, I needed to create tables periodically on a database, to partition data in several identical tables, named according to the year quarter. Instead of using MySQL 5.1 automatic partition, I preferred doing it in a more manual way, to allow accessing individual partitions if suitable to speed up queries.
So I wrote a php script that would run with a cron job and every three months, create a new table, add it some triggers, and remap a MyISAM merged table on another database. Clear and easy, at the beginning. Everything went right until I went for the triggers creation…
First of all, there is no way to use the values returned from a SELECT or SHOW statement on MySQL stored procedures: that means that tasks like remapping the merged table cannot be done with stored procedures on 5.1. Well, I would need a PHP script anyway so this wasn’t really disappointing. After all, I would create a couple of stored procedures (one to create table, another to create triggers) on the database definition, so that I would never need to know both table and triggers structure from any external script.
That run with the table creation statement, but not with the triggers. As I used a prepared statement on MySQL to allow dynamic definition of tables and triggers (every quarter, a new table name), these technique has some limitations. One of them is as follows: prepared statements do not support yet create trigger sentences. So finally I had to move this part to the external script.
And then started fun: mysql_query or mysqli_query php functions do not support multiple – statements (and triggers often use several because delimiters and some code is executed). What would I do?
Well, perhaps there are smarter solutions, but I chose to write first a temporary file, and use it then as source for MySQL. Using ‘SOURCE file.sql’ was tricky, because SOURCE is an instruction available on the MySQL client, not the server, so PHP cannot execute it. ‘LOAD DATA INFILE’ was suggested in some forum’s solutions but this is limited to only data – and I wanted advanced SQL statements.
Finally, the function ‘system’ on PHP core gave me the solution: if I can call server’s system, I can just run mysql client telling it where the source SQL file is. This way ‘mysql -u user –password=pass < file.sql’ is actually mysql client who is doing the job for me.
It is running, and working 🙂