Bug #107426 checksum table for innodb inconsistent
Submitted: 30 May 2022 7:59 Modified: 13 Jun 2022 12:57
Reporter: peng gao Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[30 May 2022 7:59] peng gao
Description:
Hi:
  we use checksum table to check data of innodb data,but same table use alter table row_format=dynamic checksum value inconsistent. But by default dynamic is row format for all innodb table innodb_default_row_format(ha_innobase::get_real_row_type)

in document like :
The default row format for InnoDB tables is defined by innodb_default_row_format variable, which has a default value of DYNAMIC. The default row format is used when the ROW_FORMAT table option is not defined explicitly or when ROW_FORMAT=DEFAULT is specified.

If defined explicitly ROW_FORMAT = dynamic,checksum result is inconsistent.
We analyze found ,in function mysql_prepare_create_table :

  if (create_info->row_type == ROW_TYPE_DYNAMIC)
    create_info->table_options |= HA_OPTION_PACK_RECORD;

HA_OPTION_PACK_RECORD flag will impact checksum table result. see function find_record_length for table shared build:

table.options().get("pack_record", &pack_record)

This different lead to inconsistent result for checksum table:

            if (t->s->null_bytes) {
              /* fix undefined null bits */
              t->record[0][t->s->null_bytes - 1] |= null_mask;
              if (!(t->s->db_create_options & HA_OPTION_PACK_RECORD))
                t->record[0][0] |= 1;

              row_crc = checksum_crc32(row_crc, t->record[0], t->s->null_bytes);
            }

I think if row format is dynamic(defined explicitly or not),checksum need consistent.Thanks!!

How to repeat:
8.0.28

mysql> create table myt2(id int );
Query OK, 0 rows affected (0.12 sec)

mysql> insert into myt2 values(10);
Query OK, 1 row affected (0.01 sec)

mysql> insert into myt2 values(20);
Query OK, 1 row affected (0.00 sec)

mysql> insert into myt2 values(30);
Query OK, 1 row affected (0.00 sec)

mysql> checksum table myt2;
+--------------+------------+
| Table        | Checksum   |
+--------------+------------+
| test517.myt2 | 2306435627 |
+--------------+------------+
1 row in set (0.00 sec)

mysql> alter table myt2 row_format=dynamic;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> checksum table myt2;
+--------------+------------+
| Table        | Checksum   |
+--------------+------------+
| test517.myt2 | 3470396603 |
+--------------+------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.28    |
+-----------+
1 row in set (0.00 sec)
[30 May 2022 12:09] MySQL Verification Team
Hi Mr. gao,

Thank you for your bug report.

However, there is one problem with your report.

Any DDL statement that is affecting the attributes that are pertaining to the entire table, will end up rebuilding the entire table. In your case, rebuilding took little amount of time, due to the size of the table.

The command that you used is used on many installations for optimising table structure, since the packing of records might be affected, indices may be affected etc ..... 

Hence, it does not seem that this is a bug, unless you come with some other proof. As a last resort, this could be a feature request, whereby any packing would be skipped for the same type of the row format. We do not know whether it would be accepted, due to the fact that this command is used by so many installations.

We are waiting on your feedback.
[30 May 2022 13:21] peng gao
Hi 
  This issue not only ALTER statment ,if create two table defined explicitly or not row_format=dynamic ,checksum table result is not inconsistent. 
  
mysql> create table myt10(id int ) ;
mysql> create table myt11(id int ) row_format=dynamic;
mysql> insert into myt11 values(10);
mysql> insert into myt10 values(10);
mysql> checksum table myt10;
+---------------+-----------+
| Table         | Checksum  |
+---------------+-----------+
| testpri.myt10 | 423330709 |
+---------------+-----------+
1 row in set (0.00 sec)
mysql> checksum table myt11;
+---------------+------------+
| Table         | Checksum   |
+---------------+------------+
| testpri.myt11 | 1587280197 |
+---------------+------------+
1 row in set (0.00 sec)
  
  In my case debug result , feild value is corrent and innodb row_search_mvcc get row is corrent,only one bits is inconsistent in record[0]
  Here if defined explicitly row_format=dynamic HA_OPTION_PACK_RECORD flag set, if default row_format(innodb dynamic),not set HA_OPTION_PACK_RECORD flag.

  ```
    if (create_info->row_type == ROW_TYPE_DYNAMIC )
    create_info->table_options |= HA_OPTION_PACK_RECORD;
  ```
  So checksum table result inconsistent.whether additional judgment conditions here?
  Last i think two table have some innodb row format(dynamic) and have same record,checksum table should have consistent value. 
  
  Best regards
[30 May 2022 13:38] MySQL Verification Team
Hi Mr. gao,

The informations that you have sent us is not full.

In your test case, you have used two InnoDB tables. They have differing behaviour on that DDL operation.

Hence , what we need is the output for each of those tables with commands:

SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='your_db/myt10' \G

and 

SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='your_db/myt11' \G

We are waiting on your feedback.
[30 May 2022 13:41] peng gao
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='testpri/myt10' \G
*************************** 1. row ***************************
     TABLE_ID: 3099
         NAME: testpri/myt10
         FLAG: 33
       N_COLS: 4
        SPACE: 1647
   ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single
 INSTANT_COLS: 0
1 row in set (0.03 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='testpri/myt11' \G
*************************** 1. row ***************************
     TABLE_ID: 3100
         NAME: testpri/myt11
         FLAG: 33
       N_COLS: 4
        SPACE: 1648
   ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single
 INSTANT_COLS: 0
1 row in set (0.02 sec)

Here,i think innodb have no problem.
[30 May 2022 13:48] MySQL Verification Team
Hi Mr. gao,

We have managed to repeat the behaviour.

This report is now a verified bug.

Thanks a lot .....
[30 May 2022 13:58] peng gao
Hi:
  In my case,i commented the following two lines of code and then test(only for test),checksum table result is consistent.
  
  in function mysql_prepare_create_table 

  //if (create_info->row_type == ROW_TYPE_DYNAMIC)
  //  create_info->table_options |= HA_OPTION_PACK_RECORD;
  
  
  Hope it can help you....
  
  Best regards.
[6 Jun 2022 12:11] huahua xu
Hi Mr. gao,

The issue is not simple, see my case:

mysql> create table myt1(id varchar(8));
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> insert into myt1 values('10');
Query OK, 1 row affected (0.01 sec)

mysql> insert into myt1 values('20');
Query OK, 1 row affected (0.00 sec)

mysql> insert into myt1 values('30');
Query OK, 1 row affected (0.00 sec)

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

mysql> alter table myt1 row_format=dynamic;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> checksum table myt1;
+-----------+------------+
| Table     | Checksum   |
+-----------+------------+
| test.myt1 | 3204212287 |
+-----------+------------+
1 row in set (0.00 sec)
[13 Jun 2022 12:57] peng gao
Hi huahua:
 Yes,i also noticed this problem. i thought have some diff int and varchar in this issue. 
 But i not debug detail.
 thanks!