pt-table-sync: Cannot nibble table

[Cedato Ops]

pt-table-sync --replicate vrtb.checksums h=db1.cedato.com,u=compare,p= --function=md5 --wait=180 --chunk-size=10000 --set-vars innodb_lock_wait_timeout=180 --databases=vrtb --tables=playerVastsStatsByDomain --execute --verbose
# Syncing via replication h=10.146.37.91,p=...,u=compare
# DELETE REPLACE INSERT UPDATE ALGORITHM START END EXIT DATABASE.TABLE
# 0 0 0 0 Nibble 03:43:14 03:43:15 0 vrtb.playerVastsStatsByDomainCannot nibble table `vrtb`.`playerVastsStatsByDomain` because MySQL chose no index instead of the `PRIMARY` index at /usr/bin/pt-table-sync line 5515. while doing vrtb.playerVastsStatsByDomain on 10.146.37.91

Getting error "Cannot nibble table" for few 0.5% of chunks that were synced.

[Cedato Ops (cedato)]

I see the root cause of the issue is:

"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '20170507'op=start&id=250292394')"

 # TableSyncNibble:5579 10946 DBD::mysql::db selectall_arrayref failed: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '20170507'op=start&id=250292394') OR (`day` = '547781985' AND `playerid` = 'http:' at line 1 [for Statement "EXPLAIN SELECT /*nibble boundary 0*/ `day`,`playerid`,`subid`,`vastid`,`domain`,`requests`,`impressions`,`imptime`,`completed`,`errors`,`rev`,`loadreqs`,`loaded`,`actualrequests`,`firstquarter`,`midpoint`,`thirdquarter`,`click`,`viewable`,`mouse_event`,`custom1`,`custom2` FROM `vrtb`.`playerVastsStatsByDomain` FORCE INDEX (`PRIMARY`) WHERE (((`day` > '20170507') OR (`day` = '20170507' AND `playerid` > '547024644') OR (`day` = '20170507' AND `playerid` = '547024644' AND `subid` > '') OR (`day` = '20170507' AND `playerid` = '547024644' AND `subid` = '' AND `vastid` > '1884830571') OR (`day` = '20170507' AND `playerid` = '547024644' AND `subid` = '' AND `vastid` = '1884830571' AND `domain` >= 'kpoplyrics.net')) AND ((`day` < '20170507') OR (`day` = '20170507' AND `playerid` < '547781985') OR (`day` = '20170507' AND `playerid` = '547781985' AND `subid` < 'http://anything2mp3.com/ar/batch'20170507'op=start&id=250292394') OR (`day` = '547781985' AND `playerid` = 'http://anything2mp3.com/ar/batch'859147246'op=start&id=250292394' AND `subid` = '20170507' AND `vastid` < '547781985') OR (`day` = 'http://anything2mp3.com/ar/batch'859147246'op=start&id=250292394' AND `playerid` = '$$custom_param_esc(pageurl)$$' AND `subid` = ? AND `vastid` = ? AND `domain` <= ?))) ORDER BY `day`,`playerid`,`subid`,`vastid`,`domain` LIMIT 9999, 1"] at /usr/bin/pt-table-sync line 5576.

[Cedato Ops (cedato)]

Attached debug

[Cedato Ops (cedato)]

Checksum table:

MariaDB [(none)]> select * from vrtb.checksums where this_crc<>master_crc;
+------+--------------------------+-------+------------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+----------+------------+------------+---------------------+
| db | tbl | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | master_crc | master_cnt | ts |
+------+--------------------------+-------+------------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------...

[Cedato Ops (cedato)]

Is it possible to prioritize it? It is impacts us a lot, thank you in advance

[Jericho Rivera (jericho-rivera)]

We'll need to reproduce this case, please share the following:
- mysql/mariadb version used on master and slave
- pt-table-checksum/pt-table-sync versions used
- table definition for vrtb.playerVastsStatsByDomain

[Carlos Salguero (carlos-salguero)]

It looks like the problem might be the single quotes inside the subid field.

`subid` < 'http://anything2mp3.com/ar/batch'20170507'op=start&id=250292394')

[Carlos Salguero (carlos-salguero)]

Single quotes are not the problem.
I've tested using this:

drop schema if exists test;
create schema if not exists test;
CREATE TABLE test.`nokeys` (
  `subid` varchar(45) DEFAULT NULL,
  `some_other_field` int(11) DEFAULT NULL,
  UNIQUE KEY `index1` (`subid`,`some_other_field`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Then I put 1000 rows having random string including quotes and it works OK.
We need more info to reproduce this issue.

[Cedato Ops (cedato)]

Thanks a lot,

#1
mysql Ver 15.1 Distrib 10.1.14-MariaDB, for Linux (x86_64) using readline 5.1

#2 (Also checked few prev. version it didn't work as well)
[root@db1 ~]# pt-table-sync --version
pt-table-sync 3.0.2
[root@db1 ~]# pt-table-checksum --version
pt-table-checksum 3.0.2

#3
MariaDB [(none)]> show create table vrtb.playerVastsStatsByDomain\G
*************************** 1. row ***************************
       Table: playerVastsStatsByDomain
Create Table: CREATE TABLE `playerVastsStatsByDomain` (
  `day` int(11) NOT NULL DEFAULT '0',
  `playerId` int(11) NOT NULL DEFAULT '0',
  `subid` char(128) NOT NULL DEFAULT '',
  `vastId` int(11) NOT NULL DEFAULT '0',
  `domain` varchar(128) NOT NULL DEFAULT '',
  `requests` int(11) DEFAULT NULL,
  `impressions` int(11) DEFAULT NULL,
  `impTime` int(11) DEFAULT NULL,
  `completed` int(11) DEFAULT NULL,
  `errors` int(11) DEFAULT NULL,
  `rev` decimal(13,3) DEFAULT NULL,
  `loadReqs` int(11) DEFAULT NULL,
  `loaded` int(11) DEFAULT NULL,
  `actualRequests` int(11) DEFAULT NULL,
  `firstQuarter` int(11) DEFAULT NULL,
  `midPoint` int(11) DEFAULT NULL,
  `thirdQuarter` int(11) DEFAULT NULL,
  `click` int(11) DEFAULT NULL,
  `viewable` int(11) DEFAULT NULL,
  `mouse_event` int(11) DEFAULT NULL,
  `custom1` int(11) DEFAULT NULL,
  `custom2` int(11) DEFAULT NULL,
  PRIMARY KEY (`day`,`playerId`,`subid`,`vastId`,`domain`),
  KEY `idx_playerVastsStatsByDomain_pl` (`playerId`),
  KEY `idx_playerVastsStatsByDomain_vastId` (`vastId`),
  KEY `day` (`day`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (`day`)
(PARTITION p20170416 VALUES LESS THAN (20170416) ENGINE = InnoDB,
 PARTITION p20170417 VALUES LESS THAN (20170417) ENGINE = InnoDB,
 PARTITION p20170418 VALUES LESS THAN (20170418) ENGINE = InnoDB,
 PARTITION p20170419 VALUES LESS THAN (20170419) ENGINE = InnoDB,
 PARTITION p20170420 VALUES LESS THAN (20170420) ENGINE = InnoDB,
 PARTITION p20170421 VALUES LESS THAN (20170421) ENGINE = InnoDB,
 PARTITION p20170422 VALUES LESS THAN (20170422) ENGINE = InnoDB,
 PARTITION p20170423 VALUES LESS THAN (20170423) ENGINE = InnoDB,
 PARTITION p20170424 VALUES LESS THAN (20170424) ENGINE = InnoDB,
 PARTITION p20170425 VALUES LESS THAN (20170425) ENGINE = InnoDB,
 PARTITION p20170426 VALUES LESS THAN (20170426) ENGINE = InnoDB,
 PARTITION p20170427 VALUES LESS THAN (20170427) ENGINE = InnoDB,
 PARTITION p20170428 VALUES LESS THAN (20170428) ENGINE = InnoDB,
 PARTITION p20170429 VALUES LESS THAN (20170429) ENGINE = InnoDB,
 PARTITION p20170430 VALUES LESS THAN (20170430) ENGINE = InnoDB,
 PARTITION p20170501 VALUES LESS THAN (20170501) ENGINE = InnoDB,
 PARTITION p20170502 VALUES LESS THAN (20170502) ENGINE = InnoDB,
 PARTITION p20170503 VALUES LESS THAN (20170503) ENGINE = InnoDB,
 PARTITION p20170504 VALUES LESS THAN (20170504) ENGINE = InnoDB,
 PARTITION p20170505 VALUES LESS THAN (20170505) ENGINE = InnoDB,
 PARTITION p20170506 VALUES LESS THAN (20170506) ENGINE = InnoDB,
 PARTITION p20170507 VALUES LESS THAN (20170507) ENGINE = InnoDB,
 PARTITION p20170508 VALUES LESS THAN (20170508) ENGINE = InnoDB,
 PARTITION p20170509 VALUES LESS THAN (20170509) ENGINE = InnoDB,
 PARTITION p20170510 VALU...

[Carlos Salguero (carlos-salguero)]

I still cannot reproduce the problem.

[Cedato Ops (cedato)]

You may see here it falls here, it looks like where condition cause this issue:

MariaDB [vrtb]> EXPLAIN SELECT /*nibble boundary 0*/ `day`,`playerid`,`subid`,`vastid`,`domain`,`requests`,`impressions`,`imptime`,`completed`,`errors`,`rev`,`loadreqs`,`loaded`,`actualrequests`,`firstquarter`,`midpoint`,`thirdquarter`,`click`,`viewable`,`mouse_event`,`custom1`,`custom2` FROM `vrtb`.`playerVastsStatsByDomain` FORCE INDEX (`PRIMARY`) WHERE (((`day` > '20170507') OR (`day` = '20170507' AND `playerid` > '547781985') OR (`day` = '20170507' AND `playerid` = '547781985' AND `subid` > 'http://anything2mp3.com/batch'20170507'op=start&id=250351607') OR (`day` = '547781985' AND `playerid` = 'http://anything2mp3.com/batch'859147246'op=start&id=250351607' AND `subid` = '20170507' AND `vastid` > '547781985') OR (`day` = 'http://anything2mp3.com/batch'859147246'op=start&id=250351607' AND `playerid` = '$$custom_param_esc(pageurl)$$' AND `subid` = ? AND `vastid` = ? AND `domain` >= ?)) AND ((`day` < '20170507') OR (`day` = '20170507' AND `playerid` < '547781985') OR (`day` = '20170507' AND `playerid` = '547781985' AND `subid` < 'http://anything2mp3.com/batch'20170507'op=start&id=250360017') OR (`day` = '547781985' AND `playerid` = 'http://anything2mp3.com/batch'2147470405'op=start&id=250360017' AND `subid` = '20170507' AND `vastid` < '547781985') OR (`day` = 'http://anything2mp3.com/batch'2147470405'op=start&id=250360017' AND `playerid` = '$$custom_param_esc(pageurl)$$' AND `subid` = ? AND `vastid` = ? AND `domain` <= ?))) ORDER BY `day`,`playerid`,`subid`,`vastid`,`domain` LIMIT 99999, 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '20170507'op=start&id=250351607') OR (`day` = '547781985' AND `playerid` = 'http:' at line 1
MariaDB [vrtb]>

I don't see why 20170507 appears in the middle of following fields for example:

'http://anything2mp3.com/batch'20170507'op=start&id=250351607')

Do u see that?

[Cedato Ops (cedato)]

it looks like symbols ? cause to mess things ...

see an example, i replaced inappropriate valued insert to ? and put ? in quotes.

MariaDB [vrtb]> EXPLAIN SELECT /*nibble boundary 0*/ `day`,`playerid`,`subid`,`vastid`,`domain`,`requests`,`impressions`,`imptime`,`completed`,`errors`,`rev`,`loadreqs`,`loaded`,`actualrequests`,`firstquarter`,`midpoint`,`thirdquarter`,`click`,`viewable`,`mouse_event`,`custom1`,`custom2` FROM `vrtb`.`playerVastsStatsByDomain` FORCE INDEX (`PRIMARY`) WHERE (((`day` > '20170507') OR (`day` = '20170507' AND `playerid` > '547781985') OR (`day` = '20170507' AND `playerid` = '547781985' AND `subid` > 'http://anything2mp3.com/batch?op=start&id=250351607') OR (`day` = '547781985' AND `playerid` = 'http://anything2mp3.com/batch?op=start&id=250351607' AND `subid` = '20170507' AND `vastid` > '547781985') OR (`day` = 'http://anything2mp3.com/batch?op=start&id=250351607' AND `playerid` = '$$custom_param_esc(pageurl)$$' AND `subid` = '?' AND `vastid` = '?' AND `domain` >= '?')) AND ((`day` < '20170507') OR (`day` = '20170507' AND `playerid` < '547781985') OR (`day` = '20170507' AND `playerid` = '547781985' AND `subid` < 'http://anything2mp3.com/batch?op=start&id=250360017') OR (`day` = '547781985' AND `playerid` = 'http://anything2mp3.com/batch?op=start&id=250360017' AND `subid` = '20170507' AND `vastid` < '547781985') OR (`day` = 'http://anything2mp3.com/batch?op=start&id=250360017' AND `playerid` = '$$custom_param_esc(pageurl)$$' AND `subid` = '?' AND `vastid` = '?' AND `domain` <= '?'))) ORDER BY `day`,`playerid`,`subid`,`vastid`,`domain` LIMIT 99999, 1;
+------+-------------+--------------------------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------------------+-------+---------------+---------+---------+------+-------+-------------+
| 1 | SIMPLE | playerVastsStatsByDomain | range | PRIMARY | PRIMARY | 270 | NULL | 17393 | Using where |
+------+-------------+--------------------------+-------+---------------+---------+---------+------+-------+-------------+

[Carlos Salguero (carlos-salguero)]

I cannot reproduce the problem. I created the table, loaded 100K rows with random data including single quotes, double quotes, question marks, etc, but I am still not getting an error.

Could you please share the steps to reproduce the problem?
How you ran pt-table-checksum? How many slaves do you have? Are they delayed?

[Cedato Ops (cedato)]

i'll try to identify problematic dataset.

We are talking about simple Master->Slave replication constellation without delay.

P

[Shahriyar Rzayev (rzayev-sehriyar)]

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