SQL command checksum returns inconsistent result

[mariadb-DanielLee]

With a newly made project, I started mysqld with rocksdb as the default engine, then ran a quick Random Query Generator test. I then check the checksum on one of the tables few times, the first return value is different from the rest.

1. Random Query Generator test command

perl gentest.pl --dsn=dbi:mysql:host=localhost:port=3306:user=root:database=test:mysql_socket=/tmp/mysql.sock --gendata=conf/examples/example.zz --grammar=conf/examples/example.yy --threads=2

1. mysql.cnf file

[mysqld]
datadir=/home/myrocks/builds/mytest/install.db
socket=/tmp/mysql.sock
gdb
language=./share/english
default-storage-engine=rocksdb
skip-innodb

default-storage-engine=innodb

default-tmp-storage-engine=MyISAM
rocksdb

1. MySQL client output

mysql> checksum table table0_int_autoinc;
+-------------------------+------------+
| Table | Checksum |
+-------------------------+------------+
| test.table0_int_autoinc | 1585077795 |
+-------------------------+------------+
1 row in set (0.00 sec)

mysql> checksum table table0_int_autoinc;
+-------------------------+-----------+
| Table | Checksum |
+-------------------------+-----------+
| test.table0_int_autoinc | 265692401 |
+-------------------------+-----------+
1 row in set (0.00 sec)

mysql> checksum table table0_int_autoinc;
+-------------------------+-----------+
| Table | Checksum |
+-------------------------+-----------+
| test.table0_int_autoinc | 265692401 |
+-------------------------+-----------+
1 row in set (0.00 sec)

mysql> checksum table table0_int_autoinc;
+-------------------------+-----------+
| Table | Checksum |
+-------------------------+-----------+
| test.table0_int_autoinc | 265692401 |
+-------------------------+-----------+

[mariadb-DanielLee]

The table is in the 'test' database.

[yoshinorim]

Are these tables all MyRocks? CHECKSUM TABLE is not compatible between InnoDB and MyRocks.

[mariadb-DanielLee]

It is the same table. I executed CHECKSUM TABLE right after logging into the database and got one checksum number, executing the same CHECKSUM TABLE command again and got a different number. The 2nd one and on returned the same result.

[spetrunia]

Ok, the reason behind different checksum being returned is that CHECKSUM TABLE code also calculates checksum over "value" bytes of fields that have NULL values.

MyRocks doesn't set these bytes so their value may vary across executions. I'll check what InnoDB does.

[spetrunia]

InnoDB sets them in row_sel_store_mysql_rec, row_sel_store_mysql_field_func:

        if (len == UNIV_SQL_NULL) {
            /* MySQL assumes that the field for an SQL
            NULL value is set to the default value. */
            ut_ad(templ->mysql_null_bit_mask);

            UNIV_MEM_ASSERT_RW(prebuilt->default_rec
                       + templ->mysql_col_offset,
                       templ->mysql_col_len);
            mysql_rec[templ->mysql_null_byte_offset]
                |= (byte) templ->mysql_null_bit_mask;
            memcpy(mysql_rec + templ->mysql_col_offset,
                   (const byte*) prebuilt->default_rec
                   + templ->mysql_col_offset,
                   templ->mysql_col_len);
            return(TRUE);
        }

default_rec comes from here:

    prebuilt->default_rec = table->s->default_values;

lengths are calculated as follows:

    templ->mysql_col_offset = (ulint) get_field_offset(table, field);
    templ->mysql_col_len = (ulint) field->pack_length();

where get_field_offset() returns this:

    return((uint) (field->ptr - table->record[0]));

[spetrunia]

https://reviews.facebook.net/D51525