Not the answer you need?
Register and ask your own question!

MEMORY table causing error in pt-table-checksum

pblazekpblazek EntrantCurrent User Role Patron

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.

Best Answer

  • pblazekpblazek Entrant Current User Role Patron
    Accepted Answer

    @igroene thanks for your help. I added a UNIQUE index on the same column as the PRIMARY key and it worked fine.

Answers

  • matthewbmatthewb Senior [email protected] Percona Staff Role

    @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.

  • igroeneigroene Percona Staff Role

    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

  • pblazekpblazek Entrant Current User Role Patron

    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."

  • igroeneigroene Percona Staff Role
    edited November 26

    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.

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.