Incorrect "UPDATE" SQL being generated where "--columns" are specified

[Robin Twiggs]

pt-table-sync 2.2.14.
Table structure on each server:

[MySQL version()=5.5.14-log]
CREATE TABLE `base` (
  `assetid` int(10) unsigned NOT NULL DEFAULT '0',
  `symbol` varchar(100) DEFAULT '',
  `ic_symbol` varchar(100) DEFAULT NULL,
  `exchange` char(1) DEFAULT '',
  `shortname` varchar(200) DEFAULT NULL,
  `longname` varchar(200) DEFAULT NULL,
  `supplier` int(11) DEFAULT NULL,
  `deletedid` int(10) unsigned NOT NULL DEFAULT '0',
  `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`assetid`,`deletedid`),
  UNIQUE KEY `symbol_ind` (`symbol`,`deletedid`),
  UNIQUE KEY `ic_symbol` (`ic_symbol`,`deletedid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

and

[version()=10.0.21-MariaDB-log]
'CREATE TABLE `asx_base` (
  `assetid` int(10) unsigned NOT NULL DEFAULT ''0'',
  `symbol` varchar(100) DEFAULT '''',
  `ic_symbol` varchar(100) DEFAULT NULL,
  `exchange` char(1) DEFAULT '''',
  `shortname` varchar(200) DEFAULT NULL,
  `longname` varchar(200) DEFAULT NULL,
  `supplier` int(11) DEFAULT NULL,
  `deletedid` int(10) unsigned NOT NULL DEFAULT ''0'',
  `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`assetid`,`deletedid`),
  UNIQUE KEY `symbol_ind` (`symbol`,`deletedid`),
  UNIQUE KEY `ic_symbol` (`ic_symbol`,`deletedid`)
) ENGINE=TokuDB DEFAULT CHARSET=latin1 `compression`=''tokudb_zlib''';

These two tables are not being replicated.
Here is the comand being run:
pt-table-sync \
--defaults-file=/root/.my3306.cnf \
--function=MD5 --ignore-columns=timestamp,time_stamp,lastupdated,UTC_Updated \
--ignore-tables=barcharts.fx_fix_min_20150722,barcharts.us_fix_minute_20150722,barcharts.us_history_no_vol \
--pid=pt-toolkit-base.pid --recursion-method=none --no-foreign-key-checks --no-check-triggers --float-precision=9 \
--set-vars=wait_timeout=24000 \
--print --hex-blob h=10.188.0.1 h=127.0.0.1 \
--databases=barcharts \
--tables=asx_base --verbose --columns=assetid,symbol

The SQL being generated is of the pattern "UPDATE base SET WHERE ..." with no name=value pairs containing values.
When I remove the "--columns=assetid,symbol" the SQL generated works.

I have unfortunately fixed the tables on the run without "--columns" but as an afterthought decided I would report this issue.

[Robin Twiggs (rtwiggs)]

I had a closer look at this issue.

What is occurring is that there is no key for the two columns we are restricting to "--columns=assetid,symbol".
The primary key "PRIMARY KEY (`assetid`,`deletedid`)" is being selected by pt-table-sync

In "sub make_where_clause":
  the line "my $val = $row->{$_};" is returning:
    "0" on first iteration
    "undef" on second iteration

The "undef" is most probably caused by column 'deletedid' not being in "--columns=assetid,symbol" as std error (attached) returns for first row incorrectly printed (assetid 1012042):

"# TableSyncNibble:5622 36717 State 2 , key cols assetid, deletedid
# ChangeHandler:3410 36717 undef UPDATE where `assetid`='1012042' AND `deletedid` IS NULL"

and here is the SQL generated (incorrect SQL "SET WHERE" should be "SET ... WHERE"):

"UPDATE `barcharts`.`asx_base` SET WHERE `assetid`='1012042' AND `deletedid` IS NULL LIMIT 1 /*percona-toolkit src_db:barcharts src_tbl:asx_base src_dsn:F=/my3306.cnf,h=192.168.10.10 dst_db:barcharts dst_tbl:asx_base dst_dsn:F=/my3306.cnf,h=127.0.0.1 lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:36717 user:root host:server.domain.com*/;"

[Robin Twiggs (rtwiggs)]

I can confirm that adding the second column of the primary key pt-table-sync selected as the key to use (see my previous comment) caused correct SQL to be generated:

"--columns=assetid,symbol" changed to "--columns=assetid,symbol,deletedid"

and SQL generated is:

"UPDATE `barcharts`.`asx_base` SET `symbol`='CBASO1.AX', `ic_symbol`='CBASO1', `exchange`='v', `shortname`='', `longname`='', `supplier`='0', `time_stamp`='2015-08-25 22:53:04'
WHERE `assetid`='1012042' AND `deletedid`='0' LIMIT 1 /*percona-toolkit ..."

[Shahriyar Rzayev (rzayev-sehriyar)]

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