Mysql – Unable to run update_thesql in MySQL 5.5

You need to monitor that progress outside of all mysql client programs.

For this example, let's say your MyISAM table is named workingset in the database mydata and the datadir is the default, /var/lib/mysql

Sidenote : If you are using Windows, run this SQL command to get your datadir

SHOW VARIABLES LIKE 'datadir';

You will have three files representing the table

/var/lib/mysql/mydata/workingset.frm
/var/lib/mysql/mydata/workingset.MYD
/var/lib/mysql/mydata/workingset.MYI

Try a temp table approach to loading the table workingset

use mydata
DROP TABLE IF EXIST workingsetload;
CREATE TABLE workingsetload LIKE workingset;
ALTER TABLE workingsetload DISABLE KEYS;
Perform 7 million row insert into workingsetload here, preferably LOAD DATA INFILE
ALTER TABLE workingsetload ENABLE KEYS;
ALTER TABLE workingset RENAME workingsetold;
ALTER TABLE workingsetload RENAME workingset;
DROP TABLE workingsetold;

During the load of the table workingsetload, there will be a temp table whose first five(5) characters are "#sql-". You can visually monitor in the Linux OS the progress of the loading of workingsetload like this:

cd /var/lib/mysql/mydata
watch -n 1 "ls -l workingsetload.MY[ID] *sql-*.MY[ID] | awk '{print $5,$9}'"

You could script this in linux like this:

cd /var/lib/mysql/mydata
ls -l workingsetload.MY[ID] *sql-*.MY[ID] | awk '{print $5,$9}' > /tmp/IndexRebuild.txt

You could then parse the columns accordingly and subtract the .MYD sizes to know when the data portion of the MyISAM table is complete. Subsequently, you could then parse the columns accordingly and subtract the .MYI sizes to know when the index portion of the MyISAM table is complete.

For Windows, you could script this using Perl (ActivePerl for Windows) or just have a DOS Batch loop over and over again.

Regardless of OS, once the "ls -l #sql-*.MY[ID]" or "if not exist #sql-*.MY*" comes back with nothing, the reload and indexing are complete.

Give it a Try !!!

UPDATE 2011-07-20 16:55 EDT

Monty has successfully implemented this feature in MariaDB. Check it out !!!

I did some real digging in the bug list for event scheduler issues.

It seems that a certain time calculation for the event scheduler was not portable. This bug was fixed as of MySQL 5.1.48 (Fixed June 2, 2010).

There was a past issue about SHOW EVENTS not getting events from the correct database. This bug was fixed as of MySQL 5.1.57 (Fixed May 5, 2011).

The latest scheduler bug was fixed July 5, 2011 in MySQL 5.1.58

You are using MySQL 5.1.41. You may want to upgrade to the latest version of MySQL 5.1 which is 5.1.58. No scheduler bugs exists as of today.

CAVEAT

On another note, I would change the SQL query to not only do less work

Instead of your DELETE query:

delete from ta_table where timestampdiff(minute,ti_time,now())>120;

Restructure it as follows:

delete from ta_table where ti_time < (now() - interval 2 hour);

Your DELETE will calculate against every row in the table. This new DELETE stops short when comparing ti_time against a time value (now() - interval 2 hour) instead of computing timestampdiff on every row.

Make sure ti_time is indexed. If not, do this:

ALTER TABLE ta_table ADD INDEX (ti_time);

Assuming the table is MyISAM, you may also want to periodically shrink the table every month like this:

ALTER TABLE ta_table ENGINE=MyISAM;

I hope this information helps !!!

UPDATE 2011-07-19 08:00 EDT

From the last chat room session lovesh and I had, here is the example I ran to create the event on my PC running MySQL 5.5.12:

drop database lovesh;
create database lovesh;
use lovesh
create table mydata (id int not null auto_increment primary key,ti_time timestamp DEFAULT current_timestamp) ENGINE=MyISAM;
DELIMITER $$
DROP PROCEDURE IF EXISTS `lovesh`.`LoadMyData` $$
CREATE PROCEDURE `lovesh`.`LoadMyData` ()
BEGIN
    DECLARE NDX INT;
    SET NDX = 0;
    WHILE NDX < 100 DO
        INSERT INTO mydata (ti_time) VALUES (NOW() - INTERVAL CEILING(14400*RAND()) SECOND);
    SET NDX = NDX + 1;
    END WHILE;
END $$
DELIMITER ;
show create table mydata\G
SHOW CREATE PROCEDURE LoadMyData\G
CALL lovesh.LoadMyData();
CREATE TABLE ta_table LIKE mydata;
ALTER TABLE ta_table DISABLE KEYS;
INSERT INTO ta_table SELECT SQL_NO_CACHE * FROM mydata;
ALTER TABLE ta_table ENABLE KEYS;
CREATE EVENT ev
    ON SCHEDULE
      EVERY 1 MINUTE
      STARTS (NOW() + INTERVAL 1 MINUTE)
    DO
      DELETE FROM ta_table WHERE ti_time > NOW() - INTERVAL 2 HOUR;
SELECT COUNT(1) FROM ta_table;
SELECT SLEEP(62);
SELECT COUNT(1) FROM ta_table;

This worked for me when ta_table was MyISAM. It just kept running using InnoDB. This may be the sticking point.