Description:
An update query takes unlimited time, which becomes immediate if the same query is broken in two.
The query is:
UPDATE events e, runs r, shows s
SET some_flag = false
WHERE e.run_id = r.id
AND r.show_id = s.id
AND s.name LIKE '%first_pattern%' or s.name LIKE '%second_pattern%'
AND s.id >= 700000;
This took undefined time on two servers, that are Linux ubuntu 8.x and Mac OS 10.5.
If the query is broken in two:
UPDATE events e, runs r, shows s
SET some_flag = false
WHERE e.run_id = r.id
AND r.show_id = s.id
AND s.name LIKE '%first_pattern%'
AND s.id >= 700000;
UPDATE events e, runs r, shows s
SET some_flag = false
WHERE e.run_id = r.id
AND r.show_id = s.id
AND s.name LIKE '%second_pattern%'
AND s.id >= 700000;
It takes a very short time.
The bug has been reproduced every time.
How to repeat:
Just run the query.
Here there are the table details; please note that there are other columns that haven't been included:
CREATE TABLE `runs` (
`id` int(11) NOT NULL auto_increment,
`show_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `index_runs_on_show_id` (`show_id`),
) ENGINE=InnoDB AUTO_INCREMENT=709661 DEFAULT CHARSET=latin1
CREATE TABLE `shows` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=708431 DEFAULT CHARSET=utf8
CREATE TABLE `events` (
`id` int(11) NOT NULL auto_increment,
`some_flag` tinyint(1) default '0',
`run_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `index_events_on_run_id` (`run_id`),
) ENGINE=InnoDB AUTO_INCREMENT=709045 DEFAULT CHARSET=utf8
Suggested fix:
Since this is the 4th bug in the optimizer I found in 6 months, I suppose the most productive action is to switch to a s database like Postgres.
Description: An update query takes unlimited time, which becomes immediate if the same query is broken in two. The query is: UPDATE events e, runs r, shows s SET some_flag = false WHERE e.run_id = r.id AND r.show_id = s.id AND s.name LIKE '%first_pattern%' or s.name LIKE '%second_pattern%' AND s.id >= 700000; This took undefined time on two servers, that are Linux ubuntu 8.x and Mac OS 10.5. If the query is broken in two: UPDATE events e, runs r, shows s SET some_flag = false WHERE e.run_id = r.id AND r.show_id = s.id AND s.name LIKE '%first_pattern%' AND s.id >= 700000; UPDATE events e, runs r, shows s SET some_flag = false WHERE e.run_id = r.id AND r.show_id = s.id AND s.name LIKE '%second_pattern%' AND s.id >= 700000; It takes a very short time. The bug has been reproduced every time. How to repeat: Just run the query. Here there are the table details; please note that there are other columns that haven't been included: CREATE TABLE `runs` ( `id` int(11) NOT NULL auto_increment, `show_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `index_runs_on_show_id` (`show_id`), ) ENGINE=InnoDB AUTO_INCREMENT=709661 DEFAULT CHARSET=latin1 CREATE TABLE `shows` ( `id` int(11) NOT NULL auto_increment, `name` varchar(255) NOT NULL default '', PRIMARY KEY (`id`), ) ENGINE=InnoDB AUTO_INCREMENT=708431 DEFAULT CHARSET=utf8 CREATE TABLE `events` ( `id` int(11) NOT NULL auto_increment, `some_flag` tinyint(1) default '0', `run_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `index_events_on_run_id` (`run_id`), ) ENGINE=InnoDB AUTO_INCREMENT=709045 DEFAULT CHARSET=utf8 Suggested fix: Since this is the 4th bug in the optimizer I found in 6 months, I suppose the most productive action is to switch to a s database like Postgres.