Description:
We have two identical tables. One is an archive for inactive records. We move data back and forth between the two. We have triggers on the active table. We occasionally (not always as other bugs about this error seem to indicate) get the error: "Explicit or implicit commit is not allowed in stored function or trigger."
The tables are:
CREATE TABLE `coupon` (
`coupon_id` int(10) unsigned NOT NULL auto_increment,
`article_id` int(10) unsigned NOT NULL default '0',
`vendor_id` int(10) unsigned NOT NULL default '0',
`type` enum('FIXED','PERCENT','SHIPPING','FREETEXT') NOT NULL default 'FIXED',
`discount` decimal(14,2) NOT NULL default '0.00',
`minimum` decimal(14,2) NOT NULL default '0.00',
`shipping` decimal(14,2) NOT NULL default '0.00',
`customer_type` enum('NEW','OLD','ALL') NOT NULL default 'ALL',
`code` varchar(64) NOT NULL default '',
`url` varchar(255) NOT NULL default '',
`tos` varchar(255) NOT NULL default '',
`author_login` varchar(10) NOT NULL default '',
`editor_login` varchar(10) NOT NULL default '',
`mod_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`expiration_time` timestamp NOT NULL default '0000-00-00 00:00:00',
`requested_publish_time` timestamp NOT NULL default '0000-00-00 00:00:00',
`requested_end_time` timestamp NOT NULL default '0000-00-00 00:00:00',
`first_publish_time` timestamp NOT NULL default '0000-00-00 00:00:00',
`latest_publish_time` timestamp NOT NULL default '0000-00-00 00:00:00',
`status` char(2) NOT NULL default '',
`comments` varchar(255) NOT NULL default '',
`rating` decimal(14,2) NOT NULL default '0.00',
`tip_id` int(10) unsigned NOT NULL default '0',
`freetext` varchar(255) NOT NULL default '',
`pop_score` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`coupon_id`),
KEY `article_id` (`article_id`),
KEY `first_publish_time` (`first_publish_time`),
KEY `latest_publish_time` (`latest_publish_time`),
KEY `requested_publish_time` (`requested_publish_time`),
KEY `mod_time` (`mod_time`),
KEY `vendor_id` (`vendor_id`),
KEY `type` (`type`),
KEY `status` (`status`),
KEY `expiration_time` (`expiration_time`),
KEY `discount` (`discount`),
KEY `minimum` (`minimum`),
KEY `shipping` (`shipping`),
KEY `tip_id` (`tip_id`),
KEY `pop_score` (`pop_score`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `coupon_archive` (
`coupon_id` int(10) unsigned NOT NULL auto_increment,
`article_id` int(10) unsigned NOT NULL default '0',
`vendor_id` int(10) unsigned NOT NULL default '0',
`type` enum('FIXED','PERCENT','SHIPPING','FREETEXT') NOT NULL default 'FIXED',
`discount` decimal(14,2) NOT NULL default '0.00',
`minimum` decimal(14,2) NOT NULL default '0.00',
`shipping` decimal(14,2) NOT NULL default '0.00',
`customer_type` enum('NEW','OLD','ALL') NOT NULL default 'ALL',
`code` varchar(64) NOT NULL default '',
`url` varchar(255) NOT NULL default '',
`tos` varchar(255) NOT NULL default '',
`author_login` varchar(10) NOT NULL default '',
`editor_login` varchar(10) NOT NULL default '',
`mod_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`expiration_time` timestamp NOT NULL default '0000-00-00 00:00:00',
`requested_publish_time` timestamp NOT NULL default '0000-00-00 00:00:00',
`requested_end_time` timestamp NOT NULL default '0000-00-00 00:00:00',
`first_publish_time` timestamp NOT NULL default '0000-00-00 00:00:00',
`latest_publish_time` timestamp NOT NULL default '0000-00-00 00:00:00',
`status` char(2) NOT NULL default '',
`comments` varchar(255) NOT NULL default '',
`rating` decimal(14,2) NOT NULL default '0.00',
`tip_id` int(10) unsigned NOT NULL default '0',
`freetext` varchar(255) NOT NULL default '',
`pop_score` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`coupon_id`),
KEY `article_id` (`article_id`),
KEY `first_publish_time` (`first_publish_time`),
KEY `latest_publish_time` (`latest_publish_time`),
KEY `requested_publish_time` (`requested_publish_time`),
KEY `mod_time` (`mod_time`),
KEY `vendor_id` (`vendor_id`),
KEY `type` (`type`),
KEY `tip_id` (`tip_id`),
KEY `pop_score` (`pop_score`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
with these triggers:
*************************** 1. row ***************************
Trigger: queue_coupon_insert
Event: INSERT
Table: coupon
Statement: insert ignore into coupon_publish_queue values (NEW.coupon_id, 1, now())
Timing: AFTER
Created: NULL
sql_mode:
Definer: admin@10.1.%
*************************** 2. row ***************************
Trigger: queue_coupon_update
Event: UPDATE
Table: coupon
Statement: insert ignore into coupon_publish_queue values (NEW.coupon_id, 1, now())
Timing: AFTER
Created: NULL
sql_mode:
Definer: admin@10.1.%
*************************** 3. row ***************************
Trigger: queue_coupon_delete
Event: DELETE
Table: coupon
Statement: insert ignore into coupon_publish_queue values (OLD.coupon_id, 1, now())
Timing: AFTER
Created: NULL
sql_mode:
Definer: admin@10.1.%
coupon_publish_queue is as follows:
CREATE TABLE `coupon_publish_queue` (
`coupon_id` int(10) unsigned NOT NULL default '0',
`priority` tinyint(3) unsigned NOT NULL default '0',
`queue_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`priority`,`queue_time`,`coupon_id`),
UNIQUE KEY `coupon_id` (`coupon_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
How to repeat:
using tables above with about 1000 rows in each table, run:
replace into coupon Select * from coupon_archive where coupon_id in (48811, 48812, 49540, 50844, 51018, 52443, 52455, 52467, 52475, 52524, 52537, 52551, 52559)
This query (with different coupon_ids) runs 1000's of times per day. We see maybe 10-20 of these errors.
Suggested fix:
Well, the other bug reports and pages I have found talking about this error seemed to indicate you should see it all the time. I do not, so I assume its an error.
Description: We have two identical tables. One is an archive for inactive records. We move data back and forth between the two. We have triggers on the active table. We occasionally (not always as other bugs about this error seem to indicate) get the error: "Explicit or implicit commit is not allowed in stored function or trigger." The tables are: CREATE TABLE `coupon` ( `coupon_id` int(10) unsigned NOT NULL auto_increment, `article_id` int(10) unsigned NOT NULL default '0', `vendor_id` int(10) unsigned NOT NULL default '0', `type` enum('FIXED','PERCENT','SHIPPING','FREETEXT') NOT NULL default 'FIXED', `discount` decimal(14,2) NOT NULL default '0.00', `minimum` decimal(14,2) NOT NULL default '0.00', `shipping` decimal(14,2) NOT NULL default '0.00', `customer_type` enum('NEW','OLD','ALL') NOT NULL default 'ALL', `code` varchar(64) NOT NULL default '', `url` varchar(255) NOT NULL default '', `tos` varchar(255) NOT NULL default '', `author_login` varchar(10) NOT NULL default '', `editor_login` varchar(10) NOT NULL default '', `mod_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `expiration_time` timestamp NOT NULL default '0000-00-00 00:00:00', `requested_publish_time` timestamp NOT NULL default '0000-00-00 00:00:00', `requested_end_time` timestamp NOT NULL default '0000-00-00 00:00:00', `first_publish_time` timestamp NOT NULL default '0000-00-00 00:00:00', `latest_publish_time` timestamp NOT NULL default '0000-00-00 00:00:00', `status` char(2) NOT NULL default '', `comments` varchar(255) NOT NULL default '', `rating` decimal(14,2) NOT NULL default '0.00', `tip_id` int(10) unsigned NOT NULL default '0', `freetext` varchar(255) NOT NULL default '', `pop_score` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`coupon_id`), KEY `article_id` (`article_id`), KEY `first_publish_time` (`first_publish_time`), KEY `latest_publish_time` (`latest_publish_time`), KEY `requested_publish_time` (`requested_publish_time`), KEY `mod_time` (`mod_time`), KEY `vendor_id` (`vendor_id`), KEY `type` (`type`), KEY `status` (`status`), KEY `expiration_time` (`expiration_time`), KEY `discount` (`discount`), KEY `minimum` (`minimum`), KEY `shipping` (`shipping`), KEY `tip_id` (`tip_id`), KEY `pop_score` (`pop_score`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `coupon_archive` ( `coupon_id` int(10) unsigned NOT NULL auto_increment, `article_id` int(10) unsigned NOT NULL default '0', `vendor_id` int(10) unsigned NOT NULL default '0', `type` enum('FIXED','PERCENT','SHIPPING','FREETEXT') NOT NULL default 'FIXED', `discount` decimal(14,2) NOT NULL default '0.00', `minimum` decimal(14,2) NOT NULL default '0.00', `shipping` decimal(14,2) NOT NULL default '0.00', `customer_type` enum('NEW','OLD','ALL') NOT NULL default 'ALL', `code` varchar(64) NOT NULL default '', `url` varchar(255) NOT NULL default '', `tos` varchar(255) NOT NULL default '', `author_login` varchar(10) NOT NULL default '', `editor_login` varchar(10) NOT NULL default '', `mod_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `expiration_time` timestamp NOT NULL default '0000-00-00 00:00:00', `requested_publish_time` timestamp NOT NULL default '0000-00-00 00:00:00', `requested_end_time` timestamp NOT NULL default '0000-00-00 00:00:00', `first_publish_time` timestamp NOT NULL default '0000-00-00 00:00:00', `latest_publish_time` timestamp NOT NULL default '0000-00-00 00:00:00', `status` char(2) NOT NULL default '', `comments` varchar(255) NOT NULL default '', `rating` decimal(14,2) NOT NULL default '0.00', `tip_id` int(10) unsigned NOT NULL default '0', `freetext` varchar(255) NOT NULL default '', `pop_score` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`coupon_id`), KEY `article_id` (`article_id`), KEY `first_publish_time` (`first_publish_time`), KEY `latest_publish_time` (`latest_publish_time`), KEY `requested_publish_time` (`requested_publish_time`), KEY `mod_time` (`mod_time`), KEY `vendor_id` (`vendor_id`), KEY `type` (`type`), KEY `tip_id` (`tip_id`), KEY `pop_score` (`pop_score`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 with these triggers: *************************** 1. row *************************** Trigger: queue_coupon_insert Event: INSERT Table: coupon Statement: insert ignore into coupon_publish_queue values (NEW.coupon_id, 1, now()) Timing: AFTER Created: NULL sql_mode: Definer: admin@10.1.% *************************** 2. row *************************** Trigger: queue_coupon_update Event: UPDATE Table: coupon Statement: insert ignore into coupon_publish_queue values (NEW.coupon_id, 1, now()) Timing: AFTER Created: NULL sql_mode: Definer: admin@10.1.% *************************** 3. row *************************** Trigger: queue_coupon_delete Event: DELETE Table: coupon Statement: insert ignore into coupon_publish_queue values (OLD.coupon_id, 1, now()) Timing: AFTER Created: NULL sql_mode: Definer: admin@10.1.% coupon_publish_queue is as follows: CREATE TABLE `coupon_publish_queue` ( `coupon_id` int(10) unsigned NOT NULL default '0', `priority` tinyint(3) unsigned NOT NULL default '0', `queue_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`priority`,`queue_time`,`coupon_id`), UNIQUE KEY `coupon_id` (`coupon_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 How to repeat: using tables above with about 1000 rows in each table, run: replace into coupon Select * from coupon_archive where coupon_id in (48811, 48812, 49540, 50844, 51018, 52443, 52455, 52467, 52475, 52524, 52537, 52551, 52559) This query (with different coupon_ids) runs 1000's of times per day. We see maybe 10-20 of these errors. Suggested fix: Well, the other bug reports and pages I have found talking about this error seemed to indicate you should see it all the time. I do not, so I assume its an error.