I am testing the pt-table-checksum tool on MySQL 8.0.36 and encountered a “problem.”
On the slave, I updated 14,334 records.
UPDATE cob_cobrancaitens
SET
descricao = ‘Item Partially Updated’,
valor = 29.90,
valordesconto = 3,
logins = ‘atualizado_parcial’
WHERE coditem = 5;
Then I ran pt-table-checksum on the master, where it found several discrepancies.
After that, I ran pt-table-sync on the slave, but it failed to update 4 records.
STRUCTURE OF THE TABLE IN QUESTION:
| cob_cobrancaitens | CREATE TABLE cob_cobrancaitens (
codcobrancaitem int unsigned NOT NULL DEFAULT ‘0’,
cob_cobrancas_codcobranca int unsigned NOT NULL DEFAULT ‘0’,
coditem int unsigned NOT NULL DEFAULT ‘0’,
codtiposcobranca int unsigned NOT NULL DEFAULT ‘0’,
codcobrancagerada int unsigned NOT NULL DEFAULT ‘0’,
cob_cobrancas_codcobrancacliente int unsigned NOT NULL DEFAULT ‘0’,
descricao varchar(255) NOT NULL,
valor double NOT NULL DEFAULT ‘0’,
descricaonotafiscal varchar(255) DEFAULT NULL,
valornotafiscal double NOT NULL DEFAULT ‘0’,
quantidade double unsigned DEFAULT ‘1’,
quantidadenotafiscal double unsigned DEFAULT ‘1’,
logins text,
emails text,
dominios text,
ftps text,
internetcompartilhadas text,
genericos text,
basesdados text,
accountcodes text,
databilhetageminicial date DEFAULT ‘0000-00-00’,
databilhetagemfinal date DEFAULT ‘0000-00-00’,
especie tinyint unsigned DEFAULT ‘0’,
codinc int unsigned DEFAULT ‘0’,
produtounidadesigla varchar(255) DEFAULT NULL,
produtocodunidade int unsigned DEFAULT ‘0’,
valordesconto double DEFAULT ‘0’,
codrevenda int unsigned DEFAULT ‘0’,
PK_codCobrancaClientePlano int unsigned DEFAULT ‘0’,
codVendedor int unsigned DEFAULT ‘0’,
codigoClassificacaoItemDocumentoFiscal int unsigned DEFAULT ‘0’,
complementoNotaFiscal varchar(255) DEFAULT NULL,
telefonia_descricaoFranquiaUtilizadaNoCalculo text,
comoitemfoicalculado text,
codEspecie int unsigned DEFAULT ‘0’,
PRIMARY KEY (codcobrancaitem,cob_cobrancas_codcobranca,codtiposcobranca,codcobrancagerada,cob_cobrancas_codcobrancacliente),
KEY i_cobrancas (cob_cobrancas_codcobranca,codtiposcobranca,codcobrancagerada,cob_cobrancas_codcobrancacliente),
KEY i_codinc (codinc),
KEY i_coditem (coditem),
KEY i_codrevenda (codrevenda),
KEY i_databilhetagemfinal (databilhetagemfinal),
KEY i_databilhetageminicial (databilhetageminicial),
KEY i_especie (especie),
KEY i_codtiposcobranca (codtiposcobranca),
KEY i_codVendedor (codVendedor),
KEY i_valor (valor),
KEY I_PK_codCobrancaClientePlano (PK_codCobrancaClientePlano)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
VALIDATION ON THE MASTER:
mysql> select count() from cob_cobrancaitens where coditem = 5 and valordesconto = 3;
±---------+
| count() |
±---------+
| 0 |
±---------+
VALIDATION ON THE SLAVE:
mysql> select count() from cob_cobrancaitens where coditem = 5 and valordesconto =3;
±---------+
| count() |
±---------+
| 4 |
±---------+
COMMANDS EXECUTED:
pt-table-checksum --replicate=percona.checksums --no-check-binlog-format --progress=time,10 --user=xxxx–password=xxxxx --databases=xxxxxx --tables=cob_cobrancaitens --host=xxx.xxx.xxx.xxx --recursion-method=dsn=h=xxx.xxx.xxx.xxx,D=percona,t=checksums_slaves --socket=/var/lib/mysql/mysql.sock
pt-table-sync --execute --no-check-triggers --replicate=percona.checksums --sync-to-master h=localhost,u=xxxx,p=xxxx–socket=/var/lib/mysql/mysql.sock
He corrected most of the lines, but four were missing.
I tried passing --where=“coditem = 5 AND valordesconto = 3,” but it doesn’t seem to execute.
It finishes, but it doesn’t update the percona.checksums table.
Could the problem be in the composite primary key?
Translated with DeepL.com (free version)