I finally wrote fix_archive_alerts.php. That script automates the process described below. Sometimes archive_alerts.php will encounter problems. It will shut down and you have to clean up before you run again. It seems pretty predictable, and the cleanup could probably be scripted. Here's an outline of that script, followed by the exact actions that I took recently. I got an email saying: > Cron Daemon root@its-the-cops.trade-ideas.com > 6:43 PM (2 hours ago) > > to me, michael > No work for clamps. > Error copying tables. I could see that clamps was ridiculously far behind. I waited for it it catch up. You need to fix the underlying problem, in this case just waiting, before you try again. This procedure only undoes the partial work done by archive_alerts.php. After seeing the error I verified that some entries in alert_shards had a status of "in progress". And I verified that the /tmp/ directory contained a file named archive_alerts_error. I looked at a few examples of the the table marked as "in progress". On clamps it was empty. Another machine was having trouble. It had a lot of alerts in that table (archive.alerts_2012_07_09) but not as many as the master did. Clearly restarting from scratch was a good idea. That said, the script is complicated and starting from scratch is probably the only good idea ever. On the master I deleted the table (drop table if exists archive.alerts_2012_07_09). Then I removed the entry from alert_shards. Then I set the autoincrement field so we'd use the same day number next time. (alter table alert_shards AUTO_INCREMENT = 539) If you skipped the last step it wouldnt' be an error, but it seems like this helps spread out the work correctly over the various databases. Then I removed the error message (rm /tmp/archive_alerts_error). This is what keeps you from accidentally running it again without cleaning up. Finally, I reran the scrip (php archive_alerts.php). [phil@chuck-liddell Logs]$ ssh its-the-cops Last login: Sat Jun 30 14:33:47 2012 from shauna [phil@its-the-cops ~]$ ls alert_archive fake_values.php fake_values.php~ front keys my.cnf mysql SharedDocs TradeIdeas UpdateEarnings.php~ [phil@its-the-cops ~]$ mysql -uroot -pxxx123 -hroberto; Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 22874907 Server version: 5.5.13-log MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select * from alert_shards limit 1; ERROR 1046 (3D000): No database selected mysql> use mydb Database changed mysql> select * from alert_shards limit 1; +-----+------------+---------------------------------+------------+------------+------+ | day | date | table_name | first_id | last_id | live | +-----+------------+---------------------------------+------------+------------+------+ | 1 | 2010-06-14 | alert_archive.alerts_2010_06_14 | 5955472924 | 5961661978 | N | +-----+------------+---------------------------------+------------+------------+------+ 1 row in set (0.01 sec) mysql> select * from alert_shards order by day desc limit 5; +-----+------------+---------------------------+------------+------------+-------------+ | day | date | table_name | first_id | last_id | live | +-----+------------+---------------------------+------------+------------+-------------+ | 539 | 2012-07-09 | archive.alerts_2012_07_09 | NULL | NULL | in progress | | 538 | 2012-07-06 | archive.alerts_2012_07_06 | 9757162251 | 9763221472 | Y | | 537 | 2012-07-05 | archive.alerts_2012_07_05 | 9750606490 | 9757162250 | Y | | 536 | 2012-07-04 | archive.alerts_2012_07_04 | 9750098261 | 9750606489 | Y | | 535 | 2012-07-03 | archive.alerts_2012_07_03 | 9745079962 | 9750098260 | Y | +-----+------------+---------------------------+------------+------------+-------------+ 5 rows in set (0.00 sec) mysql> select count(*) from archive.alerts_2012_07_09; +----------+ | count(*) | +----------+ | 6307370 | +----------+ 1 row in set (1.83 sec) mysql> Bye [phil@its-the-cops ~]$ mysql -uroot -pxxx123 -hcrushinator ; Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 258 Server version: 5.5.18-log MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use mydb Database changed mysql> select * from alert_shards order by day desc limit 5; +-----+------------+---------------------------+------------+------------+-------------+ | day | date | table_name | first_id | last_id | live | +-----+------------+---------------------------+------------+------------+-------------+ | 539 | 2012-07-09 | archive.alerts_2012_07_09 | NULL | NULL | in progress | | 538 | 2012-07-06 | archive.alerts_2012_07_06 | 9757162251 | 9763221472 | Y | | 537 | 2012-07-05 | archive.alerts_2012_07_05 | 9750606490 | 9757162250 | Y | | 536 | 2012-07-04 | archive.alerts_2012_07_04 | 9750098261 | 9750606489 | Y | | 535 | 2012-07-03 | archive.alerts_2012_07_03 | 9745079962 | 9750098260 | Y | +-----+------------+---------------------------+------------+------------+-------------+ 5 rows in set (0.00 sec) mysql> select count(*) from archive.alerts_2012_07_09; +----------+ | count(*) | +----------+ | 6307134 | +----------+ 1 row in set (1.14 sec) mysql> select count(*) from archive.alerts_2012_07_09; +----------+ | count(*) | +----------+ | 6307134 | +----------+ 1 row in set (0.00 sec) mysql> Bye [phil@its-the-cops ~]$ mysql -uroot -pxxx123 -hroberto ; Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 22876141 Server version: 5.5.13-log MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> Bye [phil@its-the-cops ~]$ mysql -uroot -pxxx123 -hclamps ; Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 124297 Server version: 5.5.11-log MySQL Community Server (GPL) by Remi Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use mydb Database changed mysql> select count(*) from archive.alerts_2012_07_09; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) mysql> select * from alert_shards order by day desc limit 5; +-----+------------+---------------------------+------------+------------+-------------+ | day | date | table_name | first_id | last_id | live | +-----+------------+---------------------------+------------+------------+-------------+ | 539 | 2012-07-09 | archive.alerts_2012_07_09 | NULL | NULL | in progress | | 538 | 2012-07-06 | archive.alerts_2012_07_06 | 9757162251 | 9763221472 | Y | | 537 | 2012-07-05 | archive.alerts_2012_07_05 | 9750606490 | 9757162250 | Y | | 536 | 2012-07-04 | archive.alerts_2012_07_04 | 9750098261 | 9750606489 | Y | | 535 | 2012-07-03 | archive.alerts_2012_07_03 | 9745079962 | 9750098260 | Y | +-----+------------+---------------------------+------------+------------+-------------+ 5 rows in set (0.00 sec) mysql> Bye [phil@its-the-cops ~]$ mysql -uroot -pxxx123 -hroberto ; Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 22876428 Server version: 5.5.13-log MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use mydb Database changed mysql> drop table if exists archive.alerts_2012_07_09; Query OK, 0 rows affected (2.66 sec) mysql> Bye [phil@its-the-cops ~]$ mysql -uroot -pxxx123 -hcrushinator ; Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 260 Server version: 5.5.18-log MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select count(*) from archive.alerts_2012_07_09; ERROR 1146 (42S02): Table 'archive.alerts_2012_07_09' doesn't exist mysql> Bye [phil@its-the-cops ~]$ mysql -uroot -pxxx123 -hroberto ; Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 22877018 Server version: 5.5.13-log MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show create table alert_shards; ERROR 1046 (3D000): No database selected mysql> use mydb; Database changed mysql> show create table alert_shards; +--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | alert_shards | CREATE TABLE `alert_shards` ( `day` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `date` date NOT NULL, `table_name` varchar(50) DEFAULT NULL, `first_id` bigint(20) DEFAULT NULL, `last_id` bigint(20) DEFAULT NULL, `live` enum('Y','N','in progress') NOT NULL DEFAULT 'N', PRIMARY KEY (`day`), UNIQUE KEY `date` (`date`), KEY `last_id` (`last_id`) ) ENGINE=InnoDB AUTO_INCREMENT=540 DEFAULT CHARSET=latin1 | +--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> delete from alert_shards where day=539; Query OK, 1 row affected (0.02 sec) mysql> alter table alert_shards AUTO_INCREMENT = 539; Query OK, 536 rows affected (0.38 sec) Records: 536 Duplicates: 0 Warnings: 0 mysql> Bye [phil@its-the-cops ~]$ ls /tmp archive_alerts_error archive_alerts.php.start GetFromStockTwits.txt pulse-3VmMltEZPwc1 UpdateEarnings.log archive_alerts.php.end crontab_submit_log.txt ift_diffs.txt stocktwits_multi.txt [phil@its-the-cops ~]$ rm /tmp/ar archive_alerts_error archive_alerts.php.end archive_alerts.php.start [phil@its-the-cops ~]$ rm /tmp/archive_alerts_error [phil@its-the-cops ~]$ crontab -l|less [phil@its-the-cops ~]$ cd /var/www/Supporting_Scripts [phil@its-the-cops Supporting_Scripts]$ less arch archive_alerts_helper* archive_alerts_helper.1* archive_alerts_on_server.php archive_alerts.php [phil@its-the-cops Supporting_Scripts]$ less archive_alerts.php [phil@its-the-cops Supporting_Scripts]$ php archive_alerts.php [phil@its-the-cops Supporting_Scripts]$