[MDEV-35867] mariadb-check --repair does not repair sequences, only changes the status to OK - Jira

Details

Type: Bug
Status: Open (View Workflow)
Priority: Critical
Resolution: Unresolved
Affects Version/s: 11.8.0
Fix Version/s: 11.8
Component/s: None
Labels:
None

Description

mariadb-check --repair does not repair sequences, only changes the status to OK for engine Aria and MyISAM, and delete fist row (main) for InnoDB.

Case:
Create tables and convert them into sequences:

CREATE TABLE `s1` (

  `next_not_cached_value` bigint(21) NOT NULL,

  `minimum_value` bigint(21) NOT NULL,

  `maximum_value` bigint(21) NOT NULL,

  `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',

  `increment` bigint(21) NOT NULL COMMENT 'increment value',

  `cache_size` bigint(21) unsigned NOT NULL,

  `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',

  `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'

) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

insert into s1 values (1,1,9223372036854775806,1,1,1000,0,0);

insert into s1 values (2,1,9223372036854775806,1,1,1000,0,0);

CREATE TABLE `s2` (

  `next_not_cached_value` bigint(21) NOT NULL,

  `minimum_value` bigint(21) NOT NULL,

  `maximum_value` bigint(21) NOT NULL,

  `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',

  `increment` bigint(21) NOT NULL COMMENT 'increment value',

  `cache_size` bigint(21) unsigned NOT NULL,

  `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',

  `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

insert into s2 values (1,1,9223372036854775806,1,1,1000,0,0);

insert into s2 values (2,1,9223372036854775806,1,1,1000,0,0);

CREATE TABLE `s3` (

  `next_not_cached_value` bigint(21) NOT NULL,

  `minimum_value` bigint(21) NOT NULL,

  `maximum_value` bigint(21) NOT NULL,

  `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',

  `increment` bigint(21) NOT NULL COMMENT 'increment value',

  `cache_size` bigint(21) unsigned NOT NULL,

  `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',

  `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'

) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

insert into s3 values (1,1,9223372036854775806,1,1,1000,0,0);

insert into s3 values (2,1,9223372036854775806,1,1,1000,0,0);

alter table s1 sequence=1;

alter table s2 sequence=1;

alter table s3 sequence=1;

Call the sequence and make sure that the records in the tables have been updated:

MariaDB [test]> select nextval(s1);

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

| nextval(s1) |

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

|           1 |

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

1 row in set (0,001 sec)

MariaDB [test]> select nextval(s2);

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

| nextval(s2) |

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

|           1 |

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

1 row in set (0,001 sec)

MariaDB [test]> select nextval(s3);

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

| nextval(s3) |

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

|           1 |

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

1 row in set (0,000 sec)

MariaDB [test]> select * from s1;

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

| next_not_cached_value | minimum_value | maximum_value       | start_value | increment | cache_size | cycle_option | cycle_count |

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

|                  1001 |             1 | 9223372036854775806 |           1 |         1 |       1000 |            0 |           0 |

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

1 row in set (0,000 sec)

MariaDB [test]> select * from s2;

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

| next_not_cached_value | minimum_value | maximum_value       | start_value | increment | cache_size | cycle_option | cycle_count |

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

|                  1001 |             1 | 9223372036854775806 |           1 |         1 |       1000 |            0 |           0 |

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

1 row in set (0,000 sec)

MariaDB [test]> select * from s3;

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

| next_not_cached_value | minimum_value | maximum_value       | start_value | increment | cache_size | cycle_option | cycle_count |

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

|                  1001 |             1 | 9223372036854775806 |           1 |         1 |       1000 |            0 |           0 |

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

1 row in set (0,000 sec)

MariaDB [test]> select TABLE_NAME, TABLE_TYPE , ENGINE  from information_schema.tables where table_schema='test';

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

| TABLE_NAME | TABLE_TYPE | ENGINE |

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

| s3         | SEQUENCE   | MyISAM |

| s2         | SEQUENCE   | InnoDB |

| s1         | SEQUENCE   | Aria   |

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

3 rows in set (0,001 sec)

Check database and try to repair (mariadb-check does not return warning for incorrect sequence with engine InnoDB - MDEV-35866):

mariadb-check --databases test

test.s1

Warning  : More than one row in the table

status   : OK

test.s2                                            OK

test.s3

Warning  : More than one row in the table

status   : OK

mariadb-check  --databases test  --repair

test.s1                                            OK

test.s2                                            OK

test.s3                                            OK

Check what happened to the extra records in the tables.
For this convert sequences to the table type:

MariaDB [test]> alter table s1 sequence=0;

Query OK, 0 rows affected (0,002 sec)

Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> alter table s2 sequence=0;

Query OK, 0 rows affected (0,003 sec)

Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> alter table s3 sequence=0;

Query OK, 0 rows affected (0,001 sec)

Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from s1;

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

| next_not_cached_value | minimum_value | maximum_value       | start_value | increment | cache_size | cycle_option | cycle_count |

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

|                  1001 |             1 | 9223372036854775806 |           1 |         1 |       1000 |            0 |           0 |

|                     2 |             1 | 9223372036854775806 |           1 |         1 |       1000 |            0 |           0 |

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

2 rows in set (0,001 sec)

MariaDB [test]> select * from s2;

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

| next_not_cached_value | minimum_value | maximum_value       | start_value | increment | cache_size | cycle_option | cycle_count |

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

|                     2 |             1 | 9223372036854775806 |           1 |         1 |       1000 |            0 |           0 |

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

1 row in set (0,001 sec)

MariaDB [test]> select * from s3;

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

| next_not_cached_value | minimum_value | maximum_value       | start_value | increment | cache_size | cycle_option | cycle_count |

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

|                  1001 |             1 | 9223372036854775806 |           1 |         1 |       1000 |            0 |           0 |

|                     2 |             1 | 9223372036854775806 |           1 |         1 |       1000 |            0 |           0 |

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

2 rows in set (0,001 sec)

According the results only for innodb there was an attempt to repair the sequence, but the wrong record was deleted. For Aria and MyISAM nothing was changed

Attachments

Issue Links

relates to

MDEV-22491 Support mariadb-check and CHECK TABLE with SEQUENCE

In Testing

MDEV-35933 repair removes wrong line for sequence with engine InnoDB

Open

MariaDB Server

mariadb-check --repair does not repair sequences, only changes the status to OK

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration