[MDEV-22491] Support mariadb-check and CHECK TABLE with SEQUENCE Created: 2020-05-07  Updated: 2024-01-25

Status: Open
Project: MariaDB Server
Component/s: Sequences, Server, Storage Engine - Sequence
Fix Version/s: N/A

Type: Task Priority: Minor
Reporter: Pramod Mahto Assignee: Michael Widenius
Resolution: Unresolved Votes: 1
Labels: Compatibility

Issue Links:
Problem/Incident
is caused by MDEV-10139 Support for SEQUENCE objects Closed

 Description   

SEQUENCE is based on Storage engine like InnoDB or MyISAM but CHECK TABLE or mysqlcheck fail/not supported for such sequence.

MariaDB [test]> show create sequence a;
+-------+--------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------+
| a     | CREATE SEQUENCE `a` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB |
+-------+--------------------------------------------------------------------------------------------------------------------------+
 

 
MariaDB [test]> show create sequence b;
+-------+--------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------+
| b     | CREATE SEQUENCE `b` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM |
+-------+--------------------------------------------------------------------------------------------------------------------------+

MariaDB [test]> select TABLE_NAME, TABLE_TYPE , ENGINE  from information_schema.tables where table_schema='test';
+------------+------------+--------+
| TABLE_NAME | TABLE_TYPE | ENGINE |
+------------+------------+--------+
| a          | SEQUENCE   | InnoDB |
| t1         | BASE TABLE | InnoDB |
| b          | SEQUENCE   | MyISAM |
| t2         | BASE TABLE | MyISAM |
+------------+------------+--------+
 
 
MariaDB [test]> check table a;
+--------+-------+----------+--------------------------------------------------------+
| Table  | Op    | Msg_type | Msg_text                                               |
+--------+-------+----------+--------------------------------------------------------+
| test.a | check | note     | The storage engine for the table doesn't support check |
+--------+-------+----------+--------------------------------------------------------+
1 row in set (0.000 sec)
 
MariaDB [test]> check table b;
+--------+-------+----------+--------------------------------------------------------+
| Table  | Op    | Msg_type | Msg_text                                               |
+--------+-------+----------+--------------------------------------------------------+
| test.b | check | note     | The storage engine for the table doesn't support check |
+--------+-------+----------+--------------------------------------------------------+
1 row in set (0.000 sec)
 
MariaDB [test]> check table t1;
+---------+-------+----------+----------+
| Table   | Op    | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.t1 | check | status   | OK       |
+---------+-------+----------+----------+
1 row in set (0.001 sec)
 
MariaDB [test]> check table t2;
+---------+-------+----------+----------+
| Table   | Op    | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.t2 | check | status   | OK       |
+---------+-------+----------+----------+
1 row in set (0.001 sec)
 
 
[root@master_node_01 test]# mysqlcheck --databases test
test.a
note     : The storage engine for the table doesn't support check
test.b
note     : The storage engine for the table doesn't support check
test.t1                                            OK
test.t2                                            OK



 Comments   
Comment by Michael Widenius [ 2020-06-03 ]

Hi!

The idea with mysqlcheck is executed on all tables matching the command line options. Some options, like --repair or --optimize doesn't do anything for tables from some engines and mysqlcheck will write that on the output as a note.

Some examples when this happens:
--optimize with sequence tables
--repair with InnoDB tables.

I think this is the right behavior and the note's are needed to inform the user what happened.
If not, some users may wonder why show table status doesn't show a changed Check_time for
some tables.

In effect, a user can ignore all rows in 'note' in the Msg_type

For users that wants to avoid those extra information lines, we could consider adding a new option to mysqlcheck that would suppress all 'note' messages.

mysqlcheck --notes=0|1
mysqlcheck --skip-notes would work automatically

Regards,
Monty

Generated at Thu Feb 08 09:15:07 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.