pt-table-sync : does not work on primary with truncated value

Bug #1742218 reported by E. MAS on 2018-01-09
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit moved to https://jira.percona.com/projects/PT
New
Undecided
Unassigned

Bug Description

My command line was :

pt-table-sync --verbose --print h=db2.local,D=test,t=pt_test_primary h=localhost --chunk-size=400000 --nocheck-triggers --nocheck-master --nocheck-slave --defaults-file=/root/my-password.cnf

The message i get :

Cannot nibble table `test`.`pt_test_primary` because MySQL chose no index instead of the `PRIMARY` index at /usr/bin/pt-table-sync line 5541. while doing test.pt_test_primary on localhost

Root cause is the primary key us a truncated field .

1) My example table definition :

create table test.pt_test_primary (
  ticket int(11) NOT NULL ,
  name text NOT NULL,
  value text,
  PRIMARY KEY ( ticket,name(166))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

You need some rows to trigger this behavior .

2) To populate my example table :

truncate table test.pt_test_primary ;

insert into test.pt_test_primary values ( 0, '0',NULL) ;
insert into test.pt_test_primary values ( 0, '1',NULL) ;
insert into test.pt_test_primary values ( 0, '2',NULL) ;
insert into test.pt_test_primary values ( 0, '3',NULL) ;

update test.pt_test_primary set value = concat(SHA1( ticket ) , SHA1(name) ) ;

And run 10 times this query :
insert into test.pt_test_primary ( select b.baset+a.ticket,concat(b.baset,a.name) as name,concat(SHA1(b.baset+a.ticket),SHA1(concat(b.baset,a.name)),value) as value from ( select max(ticket)+1 as baset from test.pt_test_primary ) as b cross join test.pt_test_primary a ) ;

Other information :
  Debian jessie with DB is mysql 5.5.58-0 (5.5.58-rel38.10-1.jessie )

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-1469

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers