Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

pt-upgrade ignores the primary column autoincrement once DEFAULT keyword used to INSERT record

Description

Table Structure 

CREATE TABLE `requests` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `verb` varchar(10) COLLATE latin1_general_ci NOT NULL, `domain` char(4) COLLATE latin1_general_ci NOT NULL DEFAULT 'none', `http_status` char(3) COLLATE latin1_general_ci NOT NULL DEFAULT '504', `ip` bigint(20) NOT NULL DEFAULT '0', `path` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT '', `user` int(11) NOT NULL DEFAULT '0', `aggregator` int(11) NOT NULL DEFAULT '0', `transaction` int(11) NOT NULL DEFAULT '0', `request_query` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, `request_body` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, `request_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, `error_dump` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, `response_body` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, `execution_time_ms` bigint(20) DEFAULT NULL, `received_at` datetime NOT NULL, `responded_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `aggregator` (`aggregator`,`domain`,`transaction`), KEY `transaction` (`transaction`), KEY `aggregator_only` (`aggregator`), KEY `path` (`path`), KEY `ip` (`ip`), KEY `http_status` (`http_status`,`path`), KEY `execution_time_ms` (`execution_time_ms`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

Once I run the below query inside the MySQL prompt, there is no issue with inserting multiple times/records. Each record gets inserted with the next autoincrement values. (which is expected)

# User@Host: testuser[testsuser] @ 1.3.6.8 [] # Thread_id: 1615929 # Query_time: 0.016991  Lock_time: 0.000101  Rows_sent: 0  Rows_examined: 0 use test; INSERT INTO `requests` (`id`,`verb`,`domain`,`path`,`user`,`ip`,`aggregator`,`transaction`,`received_at`,`http_status`,`execution_time_ms`) VALUES (DEFAULT,'POST','none','/api/charge','0',875670953,'0','0','2022-11-28 22:00:02','504','0');

But once i run the same query using the pt-upgrade, we get the "Duplicate entry '0' for key 'PRIMARY'" error. 
 

pt-upgrade h=127.0.0.1,u=msandbox,p=msandbox,P=5643 h=127.0.0.1,u=msandbox,p=msandbox,P=5725 wr-test.log --no-read-only On both hosts: DBD::mysql::st execute failed: Duplicate entry '0' for key 'PRIMARY' [for Statement "INSERT INTO `requests` (`id`,`verb`,`domain`,`path`,`user`,`ip`,`aggregator`,`transaction`,`received_at`,`http_status`,`execution_time_ms`) VALUES (DEFAULT,'POST','none','/api/charge','0',875670953,'0','0','2022-11-28 22:00:02','504','0')"] INSERT INTO `requests` (`id`,`verb`,`domain`,`path`,`user`,`ip`,`aggregator`,`transaction`,`received_at`,`http_status`,`execution_time_ms`) VALUES (DEFAULT,'POST','none','/api/charge','0',875670953,'0','0','2022-11-28 22:00:02','504','0')

Here I am also sharing the PTDEBUG log as attachment.

pt-upgrade version : 3.5.1 

 

 

Environment

None

Attachments

1
  • 01 Feb 2023, 08:29 AM

Activity

Show:

Aaditya Dubey March 6, 2023 at 1:00 PM

Hi ,

 

Thank you for the report.
Verified as described.

insert into test.`requests` (`id`,`verb`,`domain`,`path`,`user`,`ip`,`aggregator`,`transaction`,`received_at`,`http_status`,`execution_time_ms`) values (default,?,?,?,?,?,?,?,?,?,?) ## ## SQL errors: 3 ## -- 1. On both hosts: DBD::mysql::st execute failed: Duplicate entry '0' for key 'requests.PRIMARY' [for Statement "INSERT INTO test.`requests` (`id`,`verb`,`domain`,`path`,`user`,`ip`,`aggregator`,`transaction`,`received_at`,`http_status`,`execution_time_ms`) VALUES (DEFAULT,'POST','none','/api/charge','0',875670953,'0','0','2022-11-28 22:00:02','504','0')"] INSERT INTO test.`requests` (`id`,`verb`,`domain`,`path`,`user`,`ip`,`aggregator`,`transaction`,`received_at`,`http_status`,`execution_time_ms`) VALUES (DEFAULT,'POST','none','/api/charge','0',875670953,'0','0','2022-11-28 22:00:02','504','0') -- 2. On both hosts: DBD::mysql::st execute failed: Duplicate entry '0' for key 'requests.PRIMARY' [for Statement "INSERT INTO test.`requests` (`id`,`verb`,`domain`,`path`,`user`,`ip`,`aggregator`,`transaction`,`received_at`,`http_status`,`execution_time_ms`) VALUES (DEFAULT,'POST','none','/api/charge','0',875670953,'0','0','2022-11-28 22:00:02','504','0')"] INSERT INTO test.`requests` (`id`,`verb`,`domain`,`path`,`user`,`ip`,`aggregator`,`transaction`,`received_at`,`http_status`,`execution_time_ms`) VALUES (DEFAULT,'POST','none','/api/charge','0',875670953,'0','0','2022-11-28 22:00:02','504','0') -- 3. On both hosts: DBD::mysql::st execute failed: Duplicate entry '0' for key 'requests.PRIMARY' [for Statement "INSERT INTO test.`requests` (`id`,`verb`,`domain`,`path`,`user`,`ip`,`aggregator`,`transaction`,`received_at`,`http_status`,`execution_time_ms`) VALUES (DEFAULT,'POST','none','/api/charge','0',875670953,'0','0','2022-11-28 22:00:02','504','0')"] INSERT INTO test.`requests` (`id`,`verb`,`domain`,`path`,`user`,`ip`,`aggregator`,`transaction`,`received_at`,`http_status`,`execution_time_ms`) VALUES (DEFAULT,'POST','none','/api/charge','0',875670953,'0','0','2022-11-28 22:00:02','504','0')

Details

Assignee

Reporter

Priority

Components

Affects versions

Needs QA

Yes

Smart Checklist

Created February 1, 2023 at 8:31 AM
Updated February 29, 2024 at 8:40 PM