Description:
Hello
I have table file_scan in database
| file_scan | CREATE TABLE `file_scan` (
`file_scan_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`date_scanned` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`file_id` bigint(20) unsigned NOT NULL,
`scanner_id` int(10) unsigned NOT NULL DEFAULT '0',
`result` varchar(250) DEFAULT NULL,
`scansystem_id` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`file_scan_id`),
UNIQUE KEY `iall` (`file_id`,`scanner_id`),
KEY `idate` (`date_scanned`),
KEY `iscanner_id_result` (`scanner_id`,`result`),
KEY `iall_result` (`file_id`,`scanner_id`,`result`(1)) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=747742660 DEFAULT CHARSET=latin1 |
This table is filled by triggers:
CREATE TABLE `file_scan_change` (
`file_scan_change_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`date_scanned` datetime DEFAULT NULL,
`file_id` bigint(20) unsigned NOT NULL,
`scanner_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'scanner',
`result` varchar(250) DEFAULT NULL,
`scansystem_id` int(10) unsigned DEFAULT NULL COMMENT 'OBSOLETE',
`old_result` varchar(250) DEFAULT NULL COMMENT 'result before update',
PRIMARY KEY (`file_scan_change_id`)
) ENGINE=InnoDB AUTO_INCREMENT=635261638 DEFAULT CHARSET=latin1 COMMENT='Holds changes of file_scan table' |
CREATE TRIGGER `file_scan_insert` BEFORE INSERT ON `file_scan` FOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above this one
INSERT INTO file_scan_change (date_scanned, file_id, scanner_id, result, old_result, scansystem_id) VALUES (NEW.date_scanned, NEW.file_id, NEW.scanner_id, NEW.result, result, NEW.scansystem_id)
CREATE TRIGGER `file_scan_update` AFTER UPDATE ON `file_scan` FOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above this one
INSERT INTO file_scan_change (date_scanned, file_id, scanner_id, result, scansystem_id) VALUES (NEW.date_scanned, NEW.file_id, NEW.scanner_id, NEW.result, NEW.scansystem_id)
This is mysql version:
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.6.19 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.19-log |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | debian6.0 |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)
we use innodb_autoinc_lock_mode=2 and row based replication
There were 2 different procedures running at the same time one with this insert:
INSERT INTO file_scan(file_id, scanner_id, date_scanned, result, scansystem_id)
SELECT b.file_id, 2, IFNULL(b.file_scan__date_scanned, NOW()), b.file_scan__result_release,
b.file_scan__scansystem_id
FROM bulk b WHERE b.file_id IS NOT NULL AND b.file_scan__result_release IS NOT NULL
ON DUPLICATE KEY UPDATE file_scan.date_scanned = IFNULL(b.file_scan__date_scanned, NOW()),
file_scan.result = b.file_scan__result_release, file_scan.scansystem_id = b.file_scan__scansystem_id;
other with this insert:
INSERT INTO file_scan(file_id, scanner_id, date_scanned, result, scansystem_id)
SELECT b.file_id, 1, IFNULL(b.file_scan__date_scanned, NOW()), b.file_scan__result_devel,
b.file_scan__scansystem_id
FROM bulk b WHERE b.file_id IS NOT NULL AND b.file_scan__result_devel IS NOT NULL
ON DUPLICATE KEY UPDATE file_scan.date_scanned = IFNULL(b.file_scan__date_scanned, NOW()),
file_scan.result = b.file_scan__result_devel, file_scan.scansystem_id = b.file_scan__scansystem_id;
At the same time, 2 bulk loads do database were running in 2 different threads (from 2 different source application systems)
thread 1 inserted record for file_id 17958372. It assigned file_scan_id 731658437 to this record. Thread 2 was inserting data for file_id 309441063. It inserted data for this file_id to file_scan_id 731658437 and caused, that record for file_id 17958372. It looks to me that both threads received same new file_scan_id for inserted row, and "ON DUPLICATE KEY" was used.
This is pretty big problem for us (data inconsistency always is), and we need to know how to prevent this.
I attached shrinked --verbose binlog that is showing the part that caused problem. Is this problem of innodb_autoinc_lock_mode=2 with combination with ON DUPLICATE KEY UPDATE ?
thanks for advice
Juraj
How to repeat:
run this type of query many times on this version. We detected this error only once (does not mean no more occured, but we did not bumped to it).
Description: Hello I have table file_scan in database | file_scan | CREATE TABLE `file_scan` ( `file_scan_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `date_scanned` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `file_id` bigint(20) unsigned NOT NULL, `scanner_id` int(10) unsigned NOT NULL DEFAULT '0', `result` varchar(250) DEFAULT NULL, `scansystem_id` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`file_scan_id`), UNIQUE KEY `iall` (`file_id`,`scanner_id`), KEY `idate` (`date_scanned`), KEY `iscanner_id_result` (`scanner_id`,`result`), KEY `iall_result` (`file_id`,`scanner_id`,`result`(1)) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=747742660 DEFAULT CHARSET=latin1 | This table is filled by triggers: CREATE TABLE `file_scan_change` ( `file_scan_change_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `date_scanned` datetime DEFAULT NULL, `file_id` bigint(20) unsigned NOT NULL, `scanner_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'scanner', `result` varchar(250) DEFAULT NULL, `scansystem_id` int(10) unsigned DEFAULT NULL COMMENT 'OBSOLETE', `old_result` varchar(250) DEFAULT NULL COMMENT 'result before update', PRIMARY KEY (`file_scan_change_id`) ) ENGINE=InnoDB AUTO_INCREMENT=635261638 DEFAULT CHARSET=latin1 COMMENT='Holds changes of file_scan table' | CREATE TRIGGER `file_scan_insert` BEFORE INSERT ON `file_scan` FOR EACH ROW -- Edit trigger body code below this line. Do not edit lines above this one INSERT INTO file_scan_change (date_scanned, file_id, scanner_id, result, old_result, scansystem_id) VALUES (NEW.date_scanned, NEW.file_id, NEW.scanner_id, NEW.result, result, NEW.scansystem_id) CREATE TRIGGER `file_scan_update` AFTER UPDATE ON `file_scan` FOR EACH ROW -- Edit trigger body code below this line. Do not edit lines above this one INSERT INTO file_scan_change (date_scanned, file_id, scanner_id, result, scansystem_id) VALUES (NEW.date_scanned, NEW.file_id, NEW.scanner_id, NEW.result, NEW.scansystem_id) This is mysql version: +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | innodb_version | 5.6.19 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.19-log | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | debian6.0 | +-------------------------+------------------------------+ 7 rows in set (0.00 sec) we use innodb_autoinc_lock_mode=2 and row based replication There were 2 different procedures running at the same time one with this insert: INSERT INTO file_scan(file_id, scanner_id, date_scanned, result, scansystem_id) SELECT b.file_id, 2, IFNULL(b.file_scan__date_scanned, NOW()), b.file_scan__result_release, b.file_scan__scansystem_id FROM bulk b WHERE b.file_id IS NOT NULL AND b.file_scan__result_release IS NOT NULL ON DUPLICATE KEY UPDATE file_scan.date_scanned = IFNULL(b.file_scan__date_scanned, NOW()), file_scan.result = b.file_scan__result_release, file_scan.scansystem_id = b.file_scan__scansystem_id; other with this insert: INSERT INTO file_scan(file_id, scanner_id, date_scanned, result, scansystem_id) SELECT b.file_id, 1, IFNULL(b.file_scan__date_scanned, NOW()), b.file_scan__result_devel, b.file_scan__scansystem_id FROM bulk b WHERE b.file_id IS NOT NULL AND b.file_scan__result_devel IS NOT NULL ON DUPLICATE KEY UPDATE file_scan.date_scanned = IFNULL(b.file_scan__date_scanned, NOW()), file_scan.result = b.file_scan__result_devel, file_scan.scansystem_id = b.file_scan__scansystem_id; At the same time, 2 bulk loads do database were running in 2 different threads (from 2 different source application systems) thread 1 inserted record for file_id 17958372. It assigned file_scan_id 731658437 to this record. Thread 2 was inserting data for file_id 309441063. It inserted data for this file_id to file_scan_id 731658437 and caused, that record for file_id 17958372. It looks to me that both threads received same new file_scan_id for inserted row, and "ON DUPLICATE KEY" was used. This is pretty big problem for us (data inconsistency always is), and we need to know how to prevent this. I attached shrinked --verbose binlog that is showing the part that caused problem. Is this problem of innodb_autoinc_lock_mode=2 with combination with ON DUPLICATE KEY UPDATE ? thanks for advice Juraj How to repeat: run this type of query many times on this version. We detected this error only once (does not mean no more occured, but we did not bumped to it).