Details
-
Bug
-
Status: Open (View Workflow)
-
Critical
-
Resolution: Unresolved
-
11.8.0
-
None
-
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
-
-
-
- Open
-
Activity
Hi sanja, can you advise whether this issue should be a new feature or bug and if it is a bug what should be the fixversions? Thanks.
Taking into account that I have not found a word in our documentation that checking do not check sequences, I'd consider this as a bug (forgotten part of the feature no one thought about), but maybe serg has other point of view taking into account that it is really part of feature was forgotten (the other way is to change documentation).
I'd say that "REPAIR marks a broken sequence OK" is a bug, it should either repair it to fail with "ER_UNSUPPORTED", for example. Implementing REPAIR for sequences is a feature though.
That is, it could be enough to throw an error "repair is not supported" to fix this bug and implement the actual repairing as a separate feature.
Should this issue block the release of MDEV-22491? I suppose it depends on whether users are likely to encounter this bug. If users are much more likely to issue REPAIR TABLE or mariadb-check --repair with the knowledge that they can now CHECK sequence tables and get confused then it should block. Otherwise since MDEV-22491 does nothing to the effect of repair and it should not block.