- UPDATE AT THE END
I'm following this Automatic Partition Maintenance in MySQL tutorial, which details a generic method for removing and adding mySQL table partitions based on date ranges.
The idea is that you can jettison older table data automatically after a certain length of time, and create new table partitions for current data as needed.
However, since my site will likely be hosted on a "shared" provider package, it seems likely that mySQL events will be unavailable to me.
So I'm cross fertilizing the Stored Procedures described in the first tutorial, with an alternative method of invoking them using the method detailed in this Stack Overflow answer, with some modifications: Partition maintainance script for Mysql
On my local test machine, I want to run the PHP script as a CRON job from Webmin.
When I run the Stored Procedures from Adminer (which has similar functionality to phpMyAdmin), using the mySQL test database, they execute as expected - partitions are deleted, and the whole process takes a couple of minutes to complete.
However, when I run my modified PHP script from Webmin as a CRON job, nothing seems to happen. There are no errors, but the script returns immediately with "OK".
Similarly, when I run the script from my LAMP machine's shell, it immediately returns with "OK".
This is the PHP script:
#!/usr/bin/env php
<?php
$connection = mysqli_connect('localhost', 'my_username', 'my_password', 'employees');
$result = mysqli_query($connection, "CALL perform_partition_maintenance('employees', 'titles', 3, 216, 5)") or die('Query fail: ' . mysqli_error($connection));
if ($result)
echo "OK";
else
echo "FAIL";
mysqli_close($connection);
I'd be very grateful for any suggestions about where I might be going wrong.
UPDATE
In line with Nick's suggestion, I've been adding a lot of debug statements. I went a slightly different route because it was a bit easier to do - lots of new "into outfile" statements.
But what I've observed has baffled me. A small segment of the Stored Procedure is below:
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO current_partition_name;
IF done THEN
LEAVE read_loop;
END IF;
IF ! @first AND p_seconds_to_sleep > 0 THEN
SELECT CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds');
SELECT SLEEP(p_seconds_to_sleep);
END IF;
SELECT CONCAT('Dropping partition: ', current_partition_name);
...
SET @first = FALSE;
END LOOP;
CLOSE cur1;
This is all taken, unmodified, from the web tutorial at Geoff Montee's page, and works flawlessly in other contexts (i.e., within Adminer, from the sql console - just not in combination with a PHP script). However, when I comment out the line that says:
SELECT CONCAT('Dropping partition: ', current_partition_name);
Everything works just fine, but the script chokes when I put that line back in. I can't make any sense of this. Particularly since - in testing - I'm writing out "current_partition_name" into a file on disk for the first three iterations of the loop, and referencing the string in that situation doesn't cause any issues. It's very odd.
This other (apparently unresolved) stackoverflow question sounds somewhat similar.