I have a MEMORY table that is replicated and seems to be fine, but when I run pt-table-checksum on the database I receive this message: “Aborting table VXXX.JXXxxxx at chunk 1 because it is not safe to chunk. Chunking should use the PRIMARY index, but MySQL chose no index.” The PRIMARY index is the only index on the table. I don’t always monitor the output of ptc as it is produced, so I’m not sure if this is a recent development or has existed for some time. Is there a better set of parameters than " pt-table-checksum --replicate=percona.checksums --create-replicate-table --empty-replicate-table --user=xxx --password=xxxxxxxx --no-check-binlog-format --nocheck-replication-filters --nocheck-plan --chunk-size=50000 --recursion-method=hosts --recurse=1 --databases CXX,DXXDXX,PXX,SXX,VXX dbxx"? The table contains roughly 450K rows. Thanks for any assistance.
@pblazek I would remove the --no-check-plan option so that ptosc can evaluate the effectiveness of the SELECT queries before they are executed. I would also remove --chunk-size and let ptosc manage this for you. It’s possible that the combination of these parameters is messing up the query plan for that MEMORY table.
Hi @pblazek the issue here is that memory tables use HASH index for the primary key. pt-table-checksum relies on BTREE indexes to efficiently resolve range queries. The optimizer here will do a full table scan instead, as hash indexes cannot be used for range queries. I think as a workaround you can try to explicitly create a separate BTREE index for your pk, e.g.
create index VXXX on JXXXXX(pk_column) using btree;
let me know if that helps
I made the two changes recommended. The error message now says: " Error checksumming table Vxxxx.Jxxxxxxx: Cannot determine the key_len of the chunk index because MySQL chose no index instead of the PRIMARY index for the first lower boundary statement. See --[no]check-plan in the documentation for more information. at /usr/bin/pt-table-checksum line 11122."
Hi @pblazek I think you should add again the --no-check-plan option in this case. Also you might want to create the btree index as unique and drop the hash primary key to see if it makes any difference.
@igroene thanks for your help. I added a UNIQUE index on the same column as the PRIMARY key and it worked fine.