Stathis Andronikos Stathis Andronikos - 2 months ago 8
MySQL Question

Mysql CHECKSUM TABLE not working for ndbcluster

In mysql the command

CHECKSUM TABLE table1name
is working for InnoDB & MyISAM but not for
ndbcluster
. Is any other way to find the checksum or something relevant attribute so as to know if the table is the same after any interaction upon it.

DNT DNT
Answer

If I understand the question correctly, you are asking how to obtain a checksum of a mysql table when it is based on NDB engine. The following query will do the trick:

SELECT @crc as crc FROM
(
   select @crc := ''
) tmp,
(
  SELECT min(
          length(
             @crc := sha1(
                      concat(
                        @crc, sha1(
                          concat_ws('#', field_1, field_2, ...)
                                  ) 
                            )
                         )
                 )
               ) 
            )
            FROM 
            (
                SELECT field_1, field_2, ... FROM your_table
                ORDER BY yourtable_some_key_id
            ) tmp_1
) final

Under InnoDB this would not need the second sub-query and would work, but then the native CHECKSUM TABLE your_table would also work. Despite any ordering you may specify in an ORDER BY clause, the internal processing occurs before the sorting of the results, and on NDB this order is not always the same. This being the case, we need to force it, hence the second sub-query with the specific sort ordering.

The first price you pay for getting this to work on NDB is the internal ordered temporary table created by the second sub-query which is as large as your table, and this is one thing to keep in mind when using this query.

The second price paid is the time it takes to create this temporary ordered table. In my tests on a table with 100K rows, the query took about 1.5 to 2 seconds with the sub-query, and approximately 100 msec without it.

This solution is based on a more general one found at this blog

Hope this helps.

Comments