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

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
Fix Version/s: 10.4.19, 10.5.10
Component/s: Encryption, Storage Engine - Aria
Labels:
- affects-tests

Description

Upon next startup after an intentional crash (during DML flow), Aria recovery does not return any errors:

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

However, further CHECK on a table shows a problem:

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)

Manual repair works:

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)

Setting --aria-recover-options to non-default NORMAL or FORCE does not change anything.

The datadir after the initial crash, before any attempt to recover, is here:
ftp://ftp.askmonty.org/public/mdev17913-data.tar.gz

Unpack and start the server with

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

Adjust the path to keys.txt if needed.
You can also add aria-recover-options of your choice.

After recovery, run

check table oltp16;

The general log for the test flow prior to the initial crash is in mysql.log.

The test to run the complete flow (might require several trials):

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

RQG branch and revision are important here, you might not have some of the required files in other branches.
Adjust the paths to basedir and keys file on the command line.

Couldn't reproduce on 10.1 with the given test, but possibly there is some difference in the test flow.
Couldn't reproduce so far without encryption.

Attachments

Issue Links

causes

MDEV-18187 Aria engine: Redo phase failed with "error 192 when executing record redo_index_new_page" upon startup on a restored datadir

Closed

relates to

MDEV-17864 Run regular crash recovery tests for Aria tables (system and user tables)

Closed

MDEV-17912 Aria with encryption fails upon crash recovery: Redo phase failed, "Got error 192"

Stalled

MDEV-20313 Transactional Aria table stays corrupt after crash-recovery: error : Found 2275 keys of 2274

Open

MDEV-25507 CHECK on encrypted Aria table complains about "Wrong LSN"

Closed

MDEV-25866 Upgrade from pre-10.5.10 to 10.5.10 causes CHECK errors on encrypted Aria tables

Closed

(1 relates to)

Activity

Ascending order - Click to sort in descending order

Elena Stepanova added a comment - 2019-10-01 22:08 - edited

Other errors happening in similar circumstances (failed automatic recovery of encrypted transactional tables):

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)

... and many more variations, I won't list them all. After this bug is fixed, workarounds from my tests (forced explicit REPAIR) will need to be removed, and tests to be run again, to see if any variations remain.

Elena Stepanova added a comment - 2020-12-21 23:26

The raw MTR test below currently reproduces at least one of the problems every time.
It needs to be run with

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

Elena Stepanova added a comment - 2020-12-21 23:26 The raw MTR test below currently reproduces at least one of the problems every time. It needs to be run with --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

Michael Widenius added a comment - 2021-04-02 19:03

This error happened because of a wrong test in encryption code that wrote a random number over the LSN for pages for transactional Aria tables during repair. The effect was that after an ALTER TABLE ENABLE KEYS of a encrypted
recovery of the tables would not work.

I have fixed this and also fixed that CHECK TABLE will report if there are any wrong LSN's stored in the table.
One of the main reason this was hard to find and fix was that CHECK TABLE reported that the table was fine,
even if crash recovery would not work on it.

Michael Widenius added a comment - 2021-04-02 19:03 This error happened because of a wrong test in encryption code that wrote a random number over the LSN for pages for transactional Aria tables during repair. The effect was that after an ALTER TABLE ENABLE KEYS of a encrypted recovery of the tables would not work. I have fixed this and also fixed that CHECK TABLE will report if there are any wrong LSN's stored in the table. One of the main reason this was hard to find and fix was that CHECK TABLE reported that the table was fine, even if crash recovery would not work on it.

Michael Widenius added a comment - 2021-04-02 19:04

Fix pushed to 10.4. Fix with extended check table will shortly be pushed to 10.5 (now in testing)

Michael Widenius added a comment - 2021-04-02 19:04 Fix pushed to 10.4. Fix with extended check table will shortly be pushed to 10.5 (now in testing)

Michael Widenius added a comment - 2021-04-06 13:51

Pushed bug fix to 10.4 and extended version, with test and enhanced aria_chk, to 10.5

Michael Widenius added a comment - 2021-04-06 13:51 Pushed bug fix to 10.4 and extended version, with test and enhanced aria_chk, to 10.5

People

Assignee:: Michael Widenius

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2018-12-05 19:52

Updated:: 2021-12-15 13:04

Resolved:: 2021-04-06 13:51

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server