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
With this simplified testcase
--source include/have_innodb.inc
|
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;
|
|
|
insert into s2 values (1,1,9223372036854775806,1,1,1000,0,0);
|
insert into s2 values (2,1,9223372036854775806,1,1,1000,0,0);
|
|
|
alter table s2 sequence=1;
|
|
|
select nextval(s2);
|
|
|
select * from s2;
|
|
|
repair table s2;
|
|
|
alter table s2 sequence=0;
|
|
|
select * from s2;
|
|
|
drop table s2;
|
I get segv at repair table t2; at the MDEV-33449 commit c4cad8d50c23ae7dc77d6bed1593c9359121331d. 41 commits later at b6f6a5dc545e6aa3f066c2484f54970fe4f8e060, the segv remains.
The problem with engines Aria and MyISAM (repair does nothing ) repeats in all supported versions (for now 10.5, 10.6, 10.11, 11.4, 11.7)
As ycp said the strange behavior for InnoDB started with the commit c4cad8d50c23ae7dc77d6bed1593c9359121331d (repair table t2 fails with segv )
Commit 25b4000290d43faa03a56a39ce918a1bd789f9fc fixed problem with segv, but after fix it becomes obvious that the repair is not working properly: the first row is deleted from the table instead of the second
For the incorrect behavior of repair sequence with engine InnoDB created MDEV-35933
Following up on discussions with lstartseva, this issue is now tracking the following fix:
REPAIR TABLE on a sequence table should in general* fix the number of rows (i.e. remove all but the first row), or if that is not possible, output an error message.
*: for non-innodb tables at least. For innodb and 11.7+ it is tracked by MDEV-35933 as mentioned in the previous comment.
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.
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.
The MDEV-22491 patch did not change how repair works, and indeed this bug can be reproduced at the current main 11a6c1b30a12c448ddfe05e1b818a6a228e90e43.
Perhaps it is caused by
MDEV-33449instead - let me check.