[#MDEV-17913] Encrypted transactional Aria tables remain corrupt after crash recovery, automatic repairment does not work

10.2 5ec9b88e1111
2018-12-05 21:46:24 140049823393600 [Note] mysqld: Aria engine: starting recovery
recovered pages: 0% 10% 20% 30% 40% 50% 60% 70% 80% 90% 89% 99% 100% (17.7 seconds); transactions to roll back: 2 1 0 (0.0 seconds); tables to flush: 54 53 52 51 50 49 48 47 46 45 44 43 42 41 40 39 38 37 36 35 34 33 32 31 30 29 28 27 26 25 24 23 22 21 20 19 18 17 16 15 14 13 12 11 10 9 8 7 6 5 4 3 2 1 0 (1.8 seconds);
2018-12-05 21:46:44 140049823393600 [Note] mysqld: Aria engine: recovery done
2018-12-05 21:46:44 140049823393600 [Note] InnoDB: !!!!!!!! UNIV_DEBUG switched on !!!!!!!!!
2018-12-05 21:46:44 140049823393600 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-12-05 21:46:44 140049823393600 [Note] InnoDB: Uses event mutexes
2018-12-05 21:46:44 140049823393600 [Note] InnoDB: Compressed tables use zlib 1.2.11
2018-12-05 21:46:44 140049823393600 [Note] InnoDB: Using Linux native AIO
2018-12-05 21:46:44 140049823393600 [Note] InnoDB: Number of pools: 1
2018-12-05 21:46:44 140049823393600 [Note] InnoDB: Using SSE2 crc32 instructions
2018-12-05 21:46:44 140049823393600 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2018-12-05 21:46:44 140049823393600 [Note] InnoDB: Completed initialization of buffer pool
2018-12-05 21:46:44 140049095739136 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2018-12-05 21:46:44 140049823393600 [Note] InnoDB: Highest supported file format is Barracuda.
2018-12-05 21:46:44 140049823393600 [Note] InnoDB: Starting crash recovery from checkpoint LSN=1620006
2018-12-05 21:46:44 140049823393600 [Note] InnoDB: 128 out of 128 rollback segments are active.
2018-12-05 21:46:44 140049823393600 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2018-12-05 21:46:44 140049823393600 [Note] InnoDB: Creating shared tablespace for temporary tables
2018-12-05 21:46:44 140049823393600 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2018-12-05 21:46:44 140049823393600 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2018-12-05 21:46:44 140049823393600 [Note] InnoDB: Waiting for purge to start
2018-12-05 21:46:44 140049823393600 [Note] InnoDB: 5.7.24 started; log sequence number 1620015
2018-12-05 21:46:44 140048867514112 [Note] InnoDB: Loading buffer pool(s) from /data/bld/10.2/data/ib_buffer_pool
2018-12-05 21:46:44 140049823393600 [Note] Plugin 'FEEDBACK' is disabled.
2018-12-05 21:46:44 140049823393600 [Note] Recovering after a crash using tc.log
2018-12-05 21:46:44 140049823393600 [Note] Starting crash recovery...
2018-12-05 21:46:44 140048867514112 [Note] InnoDB: Buffer pool(s) load completed at 181205 21:46:44
2018-12-05 21:46:44 140049823393600 [Note] Crash recovery finished.
2018-12-05 21:46:44 140049823393600 [Note] Server socket created on IP: '::'.
2018-12-05 21:46:44 140049823393600 [Note] Reading of all Master_info entries succeded
2018-12-05 21:46:44 140049823393600 [Note] Added new Master_info '' to hash table
2018-12-05 21:46:44 140049823393600 [Note] /data/bld/10.2/bin/mysqld: ready for connections.
Version: '10.2.20-MariaDB-debug' socket: '/data/bld/10.2/data/tmp/mysql.sock' port: 3306 Source distribution

MariaDB [test]> check table oltp16;

+-------------+-------+----------+-------------------------+

| Table       | Op    | Msg_type | Msg_text                |

+-------------+-------+----------+-------------------------+

| test.oltp16 | check | error    | Found 2315 keys of 2319 |

| test.oltp16 | check | error    | Corrupt                 |

+-------------+-------+----------+-------------------------+

2 rows in set (0.02 sec)

MariaDB [test]> repair table oltp16;

+-------------+--------+----------+-----------------------------------------------------------+

| Table       | Op     | Msg_type | Msg_text                                                  |

+-------------+--------+----------+-----------------------------------------------------------+

| test.oltp16 | repair | warning  | Duplicate key  1 for record at 3111 against record at 361 |

| test.oltp16 | repair | warning  | 1 records have been removed                               |

| test.oltp16 | repair | warning  | Number of rows changed from 2319 to 2317                  |

| test.oltp16 | repair | status   | OK                                                        |

+-------------+--------+----------+-----------------------------------------------------------+

4 rows in set (0.18 sec)

MariaDB [test]> check table oltp16;

+-------------+-------+----------+-----------------------------------------------------------+

| Table       | Op    | Msg_type | Msg_text                                                  |

+-------------+-------+----------+-----------------------------------------------------------+

| test.oltp16 | check | warning  | Found row where the auto_increment column has the value 0 |

| test.oltp16 | check | status   | OK                                                        |

+-------------+-------+----------+-----------------------------------------------------------+

2 rows in set (0.00 sec)

MariaDB [test]> show create table oltp16 \G

*************************** 1. row ***************************

       Table: oltp16

Create Table: CREATE TABLE `oltp16` (

  `k` int(10) unsigned NOT NULL DEFAULT 0,

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `pad` char(60) NOT NULL DEFAULT '',

  `c` char(120) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`),

  KEY `k` (`k`)

) ENGINE=Aria AUTO_INCREMENT=1891959922 DEFAULT CHARSET=latin1 CHECKSUM=1 PAGE_CHECKSUM=0 ROW_FORMAT=PAGE TRANSACTIONAL=1

1 row in set (0.00 sec)

--aria-encrypt-tables=1 --plugin-load-add=file_key_management --file-key-management-filename=`pwd`/mysql-test/std_data/keys.txt

https://github.com/MariaDB/randgen --branch elenst-jira-refs b1facb0360

perl ./runall-new.pl  --basedir=/data/bld/10.2 --vardir=/dev/shm/vardir --duration=250 --threads=6  --scenario=CrashUpgrade   --redefine=conf/mariadb/bulk_insert.yy  --engine=Aria --grammar=conf/mariadb/generic-dml.yy --gendata=conf/mariadb/oltp-aria.zz --mysqld=--aria-encrypt-tables=1 --grammar=conf/mariadb/generic-dml.yy --gendata=conf/mariadb/oltp-aria.zz --mysqld=--plugin-load-add=file_key_management --mysqld=--file-key-management-filename=/data/bld/keys.txt

MariaDB [(none)]> check table test.oltp105;

+--------------+-------+----------+-----------------------------------------------------------+

| Table        | Op    | Msg_type | Msg_text                                                  |

+--------------+-------+----------+-----------------------------------------------------------+

| test.oltp105 | check | warning  | Found row where the auto_increment column has the value 0 |

| test.oltp105 | check | error    | Checksum for key:  2 doesn't match checksum for records   |

| test.oltp105 | check | error    | Corrupt                                                   |

+--------------+-------+----------+-----------------------------------------------------------+

3 rows in set (0.048 sec)

MariaDB [(none)]> check table test.oltp105;

+--------------+-------+----------+-----------------------------------------------------------+

| Table        | Op    | Msg_type | Msg_text                                                  |

+--------------+-------+----------+-----------------------------------------------------------+

| test.oltp105 | check | warning  | Table is marked as crashed                                |

| test.oltp105 | check | warning  | Found row where the auto_increment column has the value 0 |

| test.oltp105 | check | error    | Checksum for key:  2 doesn't match checksum for records   |

| test.oltp105 | check | error    | Corrupt                                                   |

+--------------+-------+----------+-----------------------------------------------------------+

4 rows in set (0.005 sec)

MariaDB [(none)]> repair table test.oltp105;

+--------------+--------+----------+----------+age done

| Table        | Op     | Msg_type | Msg_text |

+--------------+--------+----------+----------+

| test.oltp105 | repair | status   | OK       |

+--------------+--------+----------+----------+

1 row in set (0.152 sec)

MariaDB [(none)]> check table test.oltp105;

+--------------+-------+----------+-----------------------------------------------------------+

| Table        | Op    | Msg_type | Msg_text                                                  |

+--------------+-------+----------+-----------------------------------------------------------+

| test.oltp105 | check | warning  | Found row where the auto_increment column has the value 0 |

| test.oltp105 | check | status   | OK                                                        |

+--------------+-------+----------+-----------------------------------------------------------+

2 rows in set (0.002 sec)

MariaDB [(none)]> check table test.oltp90 extended;

+-------------+-------+----------+-----------------------------------------------------------+

| Table       | Op    | Msg_type | Msg_text                                                  |

+-------------+-------+----------+-----------------------------------------------------------+

| test.oltp90 | check | warning  | Table is marked as crashed                                |

| test.oltp90 | check | warning  | Found row where the auto_increment column has the value 0 |

| test.oltp90 | check | error    | Record at: 1:171  Can't find key for index:  2            |

| test.oltp90 | check | error    | Corrupt                                                   |

+-------------+-------+----------+-----------------------------------------------------------+

4 rows in set (0.042 sec)

MariaDB [(none)]> repair table test.oltp90;

+-------------+--------+----------+----------+tage done

| Table       | Op     | Msg_type | Msg_text |

+-------------+--------+----------+----------+

| test.oltp90 | repair | status   | OK       |

+-------------+--------+----------+----------+

1 row in set (0.158 sec)

MariaDB [(none)]> check table test.oltp90 extended;

+-------------+-------+----------+-----------------------------------------------------------+

| Table       | Op    | Msg_type | Msg_text                                                  |

+-------------+-------+----------+-----------------------------------------------------------+

| test.oltp90 | check | warning  | Found row where the auto_increment column has the value 0 |

| test.oltp90 | check | status   | OK                                                        |

+-------------+-------+----------+-----------------------------------------------------------+

2 rows in set (0.032 sec)

MariaDB [(none)]> check table test.oltp35 extended;

+-------------+-------+----------+-------------------------------------------------+

| Table       | Op    | Msg_type | Msg_text                                        |

+-------------+-------+----------+-------------------------------------------------+

| test.oltp35 | check | error    | Record-count is not ok; found 18  Should be: 19 |

| test.oltp35 | check | error    | Key pointers and record positions doesn't match |

| test.oltp35 | check | error    | Corrupt                                         |

+-------------+-------+----------+-------------------------------------------------+

3 rows in set (0.011 sec)

MariaDB [(none)]> check table test.oltp35 extended;

+-------------+-------+----------+-------------------------------------------------+

| Table       | Op    | Msg_type | Msg_text                                        |

+-------------+-------+----------+-------------------------------------------------+

| test.oltp35 | check | warning  | Table is marked as crashed                      |

| test.oltp35 | check | error    | Record-count is not ok; found 18  Should be: 19 |

| test.oltp35 | check | error    | Key pointers and record positions doesn't match |

| test.oltp35 | check | error    | Corrupt                                         |

+-------------+-------+----------+-------------------------------------------------+

4 rows in set (0.010 sec)

MariaDB [(none)]> repair table test.oltp35;

+-------------+--------+----------+--------------------------------------+

| Table       | Op     | Msg_type | Msg_text                             |

+-------------+--------+----------+--------------------------------------+

| test.oltp35 | repair | warning  | Number of rows changed from 19 to 18 |

| test.oltp35 | repair | status   | OK                                   |

+-------------+--------+----------+--------------------------------------+

2 rows in set (0.132 sec)

MariaDB [(none)]> check table test.oltp35 extended;

+-------------+-------+----------+----------+e done

| Table       | Op    | Msg_type | Msg_text |

+-------------+-------+----------+----------+

| test.oltp35 | check | status   | OK       |

+-------------+-------+----------+----------+

1 row in set (0.001 sec)

MariaDB [(none)]> check table test.oltp97 extended;

+-------------+-------+----------+-------------------------------------------------------------------------------------------+

| Table       | Op    | Msg_type | Msg_text                                                                                  |

+-------------+-------+----------+-------------------------------------------------------------------------------------------+

| test.oltp97 | check | error    | Page at 40960 is not delete marked                                                        |

| test.oltp97 | check | error    | Invalid key block position: 4854317448351973378  key block size: 8192  file_length: 65536 |

| test.oltp97 | check | error    | key delete-link-chain corrupted                                                           |

| test.oltp97 | check | error    | Corrupt                                                                                   |

+-------------+-------+----------+-------------------------------------------------------------------------------------------+

4 rows in set (0.014 sec)

MariaDB [(none)]> check table test.oltp97 extended;

+-------------+-------+----------+-------------------------------------------------------------------------------------------+

| Table       | Op    | Msg_type | Msg_text                                                                                  |

+-------------+-------+----------+-------------------------------------------------------------------------------------------+

| test.oltp97 | check | warning  | Table is marked as crashed                                                                |

| test.oltp97 | check | error    | Page at 40960 is not delete marked                                                        |

| test.oltp97 | check | error    | Invalid key block position: 4854317448351973378  key block size: 8192  file_length: 65536 |

| test.oltp97 | check | error    | key delete-link-chain corrupted                                                           |

| test.oltp97 | check | error    | Corrupt                                                                                   |

+-------------+-------+----------+-------------------------------------------------------------------------------------------+

5 rows in set (0.040 sec)

MariaDB [(none)]> repair table test.oltp97;

+-------------+--------+----------+----------+tage done

| Table       | Op     | Msg_type | Msg_text |

+-------------+--------+----------+----------+

| test.oltp97 | repair | status   | OK       |

+-------------+--------+----------+----------+

1 row in set (0.141 sec)

MariaDB [(none)]> check table test.oltp97 extended;

+-------------+-------+----------+-----------------------------------------------------------+

| Table       | Op    | Msg_type | Msg_text                                                  |

+-------------+-------+----------+-----------------------------------------------------------+

| test.oltp97 | check | warning  | Found row where the auto_increment column has the value 0 |

| test.oltp97 | check | status   | OK                                                        |

+-------------+-------+----------+-----------------------------------------------------------+

2 rows in set (0.046 sec)

--mysqld=--aria-encrypt-tables=1 --mysqld=--file-key-management-filename=`pwd`/std_data/keys.txt --mysqld=--plugin-load-add=file_key_management

CREATE TABLE table15_aria_int_autoinc (col_int_not_null_key int not null,

 col_char_10_default_null char(10) default null,

 col_enum_not_null enum ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z') not null,

 col_char_255_not_null_key char(255) not null,

 col_enum enum ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z'),

 col_int int,

 col_enum_not_null_key enum ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z') not null,

 col_enum_key enum ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z'),

 col_char_255 char(255),

 pk integer auto_increment,

 col_bigint_not_null bigint not null,

 col_char_10_key_default_null char(10) default null,

 col_int_not_null int not null,

 col_int_key int,

 col_char_10_key char(10),

 col_bigint bigint,

 col_char_255_key_default_null char(255) default null,

 col_char_255_key char(255),

 col_char_10 char(10),

 col_bigint_default_null bigint default null,

 col_enum_default_null enum ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z') default null,

 col_char_255_default_null char(255) default null,

 col_enum_key_default_null enum ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z') default null,

 col_char_10_not_null char(10) not null,

 col_char_10_not_null_key char(10) not null,

 col_int_default_null int default null,

 col_bigint_not_null_key bigint not null,

 col_bigint_key bigint,

 col_bigint_key_default_null bigint default null,

 col_char_255_not_null char(255) not null,

 col_int_key_default_null int default null,

 primary key (pk),

 key (col_bigint_not_null_key),

 key (col_int_key_default_null)) ENGINE=aria;

CREATE VIEW view_table15_aria_int_autoinc AS SELECT * FROM table15_aria_int_autoinc;

ALTER TABLE table15_aria_int_autoinc DISABLE KEYS;

INSERT /*! IGNORE */ INTO table15_aria_int_autoinc VALUES (0, 'l', 'n', 'c', 'v', -1835728896, 'm', 't', 'CVOMM', NULL, 6675742022646628352, 'LTXLF', 1, 2070020096, 'b', NULL, 'THDMO', 'ROQWW', 'intervention', NULL, 'f', 'distant', 'd', 'ZMZRV', 'VFGCO', 1, 7, NULL, 4, 'IYZYS', NULL) , (1, 'r', 'h', 'remind', 'l', 0, 'i', 'i', 'headline', NULL, -2839801040033808384, 'i', 5, NULL, 't', NULL, 'capacity', 't', 'LSVNX', NULL, 'y', 'include', 'i', 'islamic', 'retail', -457703424, -2941132031649644544, 2570992437275131904, -4687965737115975680, 'BYRAM', -1145110528) , (-1904869376, 'NTWRB', 'd', 'OQHJN', 'l', 2, 'f', 'k', 'HVMYO', NULL, 3486630536514895872, 'e', 1373700096, -712179712, 'local', 6, 'net', 'slave', 'runner', 0, 't', 'boat', 'm', 'second', 'v', 1, 2, -573927477513027584, 6203989961679568896, 'dig', NULL) , (-571604992, 'abandon', 'h', 'w', 'b', 1, 'p', 'j', 'OIFZP', NULL, 0, 'w', 0, NULL, 'aid', NULL, 'x', 'n', 'v', 0, 'x', 'WGYBY', 'n', 'YEWFF', 'IHWLZ', 1990590464, -730709039540862976, 9, NULL, 'XULUQ', 5) , (9, 'ROBKH', 'v', 'JFHXB', 't', 1414201344, 'k', 'n', 'n', NULL, 8, 'v', 7, 2, 'adult', NULL, 'h', 'z', 'UOGLU', 5167036147477512192, 'u', 'pregnant', 'p', 'great', 'b', 723976192, -8798907771975106560, 8, NULL, 'a', 9) , (1837236224, 's', 'z', 'compound', 'o', NULL, 'b', 'c', 'tag', NULL, 4, 'x', -167772160, 6, 'frustrate', 7956734641656823808, 'STWTQ', 'suburban', 's', 1, 'p', 'purse', 'n', 'p', 'OFMNH', 2053373952, 6, 0, 8005148337651056640, 't', NULL) , (827981824, 'GXRFA', 'k', 'f', 'd', 4, 'k', 'b', 'impress', NULL, 1, 'patent', 9, 0, 'clothes', 1249748896595312640, 'a', 'belly', 'rescue', NULL, 'r', 'XXSIR', 'y', 'i', 'sail', NULL, -526921156402348032, -8901927613451206656, NULL, 'p', 8) , (2, 'l', 'o', 'constant', 'l', 5, 'e', 'u', 'e', NULL, -2768869345902723072, 'HFABS', 3, 1840447488, 'blessing', NULL, 'then', 'n', 'a', NULL, 'b', 'l', 's', 'UPCKJ', 'prisoner', NULL, 2, NULL, 7, 'ALBPZ', 5) , (1, 'museum', 't', 'VUXDP', 'r', NULL, 'y', 'm', 'SHWMZ', NULL, 2, 'KHQAD', 866910208, 7, 'DZCIO', NULL, 'KEIQK', 'onion', 'q', 8, 'r', 'RYTHA', 'f', 'fault', 'quite', NULL, 5, 5566730614406643712, -6727533418361389056, 'funny', -1188757504) , (6, 'EXTQM', 'i', 'OXWKI', 'o', -347865088, 'g', 'f', 'AHVQM', NULL, 2, 'h', -2028011520, -474218496, 'n', NULL, 'PGVKW', 'atop', 'FNSMX', NULL, 'k', 'potato', 'm', 'h', 'relatively', 2, 0, -3742772765321592832, -640074097040031744, 'RECPT', NULL) , (140312576, 'y', 'g', 'closed', 'h', 3, 'j', 'n', 'b', NULL, 6, 'b', 732889088, 1229586432, 'HHFES', -2262495862800252928, 'a', 'XVERJ', 'd', 7, 'i', 'weigh', 'n', 'g', 'f', 1474232320, 1850697971872563200, 6, NULL, 'm', 9) , (8, 'j', 'h', 'profession', 'j', 8, 'c', 'm', 'precious', NULL, 2813623867199717376, 'sanction', 3, 9, 'BHHZS', 3194740985665945600, 'u', 'd', 't', -4959307614665048064, 'p', 'c', 'y', 'XCZWZ', 'z', 4, -1364872162069970944, 3529696207951626240, -6403274245190713344, 'diagnose', 5) , (-1245380608, 'jaw', 'x', 'beef', 'a', 5, 's', 'e', 'VUUOY', NULL, 0, 'market', -537919488, NULL, 'advertising', 4, 'listener', 'c', 'world', 9, 'l', 'sunlight', 'z', 'spring', 'VAIUX', -1327169536, 5, NULL, 4, 'OCDVU', 3) , (4, 'PTIOK', 'k', 'c', 'a', NULL, 'm', 'z', 'e', NULL, -6102377495087022080, 'r', -311099392, 5, 'z', -7298927621084020736, 'JZTEJ', 'm', 'storm', -8550365367539597312, 'w', 'author', 'n', 'TJGHL', 'RQBFZ', 355729408, 4541880224203145216, 0, 7254736049740447744, 'spring', NULL) , (8, 'portfolio', 'z', 'b', 'f', NULL, 'q', 'n', 'a', NULL, -8173470373724028928, 'frankly', 6, -1373372416, 'OTJOE', 6, 'z', 'v', 'u', 5, 'h', 'z', 'r', 'HYFQL', 'habit', NULL, 809522033019846656, 9, NULL, 's', 1675165696);

ALTER TABLE table15_aria_int_autoinc ENABLE KEYS;

UPDATE IGNORE view_table15_aria_int_autoinc SET col_bigint_not_null_key = DEFAULT;

--let $shutdown_timeout= 0

--source include/restart_mysqld.inc

CHECK TABLE test.table15_aria_int_autoinc EXTENDED;

CHECK TABLE test.table15_aria_int_autoinc EXTENDED;

Table	Op	Msg_type	Msg_text

test.table15_aria_int_autoinc	check	error	Record at: 1:0  Can't find key for index:  2

test.table15_aria_int_autoinc	check	error	Corrupt

[MDEV-17913] Encrypted transactional Aria tables remain corrupt after crash recovery, automatic repairment does not work Created: 2018-12-05 Updated: 2021-12-15 Resolved: 2021-04-06
Status:	Closed
Project:	MariaDB Server
Component/s:	Encryption, Storage Engine - Aria
Affects Version/s:	10.2, 10.3, 10.4, 10.5
Fix Version/s:	10.4.19, 10.5.10

[MDEV-17913] Encrypted transactional Aria tables remain corrupt after crash recovery, automatic repairment does not work Created: 2018-12-05 Updated: 2021-12-15 Resolved: 2021-04-06