[MDEV-20070] cannot insert into write locked tables with sequences Created: 2019-07-16  Updated: 2023-09-06

Status: Open
Project: MariaDB Server
Component/s: Locking, Sequences
Affects Version/s: 10.3, 10.4
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: miracee Assignee: Anel Husakovic
Resolution: Unresolved Votes: 1
Labels: None
Environment:

Debian buster and Ubuntu 19.04 and 18.04

Issue Links:
Duplicate
duplicates MDEV-20071 mysqldump won't work correct on seque... Closed
Relates
relates to MDEV-21786 mysqldump will forget sequence defini... Closed
relates to MDEV-30155 SEQUENCEs dumped with mariadb-dump ca... Open

 Description    

TEST:
 
DROP DATABASE IF EXISTS test1;
 
DROP DATABASE IF EXISTS test2;
 
CREATE DATABASE test1;
 
CREATE DATABASE test2;
 
USE test1;
 
CREATE SEQUENCE seq_t_i INCREMENT 1 START WITH 1;
 
CREATE TABLE t(
 
i integer DEFAULT nextval(seq_t_i),
 
j integer 
);
 
INSERT INTO t VALUES (1,1),(2,2),(3,3),(4,4);
 
SELECT SETVAL(seq_t_i, 4);
 
INSERT INTO t(j) VALUES (5),(6),(7);
 
SELECT SETVAL(seq_t_i,200);
 
INSERT INTO t(j) VALUES (201),(202),(203);
 
 
 
$ mysqldump test1 > foo.sql
 
$ mysql test2 < foo.sql
 
 
 
ERROR 1100 (HY000) at line 66: Table 'seq_t_id' was not locked with LOCK TABLES

Looks like mysqldump not working correct on sequences.

Best regards,

Susanne

 Comments   
Comment by Elena Stepanova [ 2019-07-21 ]

I'm not sure it's mysqldump to blame.

MariaDB [db]> create sequence s;
 
Query OK, 0 rows affected (0.314 sec)
 
 
 
MariaDB [db]> create table t (i int default nextval(s));
 
Query OK, 0 rows affected (0.395 sec)
 
 
 
MariaDB [db]> lock table t write;
 
Query OK, 0 rows affected (0.002 sec)
 
 
 
MariaDB [db]> insert into t values (1);
 
ERROR 1100 (HY000): Table 's' was not locked with LOCK TABLES

The questions are:

  • shouldn't the sequence be locked automatically as needed;
  • should it be used in this case at all, given that we are inserting an explicit value into the column.
Comment by Anel Husakovic [ 2020-08-27 ]

Quick search of documentation: According to the documentation of sequences standards-compliance 

However as SEQUENCE is implemented as a special kind of table, it uses the same namespace as tables. 
.
 
Since sequence objects act as regular tables in many contexts, they will be affected by LOCK TABLES. This is not the case in other DBMS, such as Oracle, where LOCK TABLE does not affect sequences.

Lock tables docs says only that triggers are implicitly locked with explicit LOCK TABLES on a table.
So for example above this should work

MariaDB [db]> lock table t write,s write; # table depends on sequence.
 
MariaDB [db]> insert into t values (1);

Now if we look into dumped file from mysqldump we will get:

--
 
-- Table structure for table `t`
 
--
 
 
 
DROP TABLE IF EXISTS `t`;
 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 
/*!40101 SET character_set_client = utf8 */;
 
CREATE TABLE `t` (
 
  `t` int(11) DEFAULT nextval(`test`.`s`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
/*!40101 SET character_set_client = @saved_cs_client */;
 
 
 
--
 
-- Dumping data for table `t`
 
--
 
 
 
LOCK TABLES `t` WRITE;
 
/*!40000 ALTER TABLE `t` DISABLE KEYS */;
 
INSERT INTO `t` VALUES (20),(20),(20);
 
/*!40000 ALTER TABLE `t` ENABLE KEYS */;
 
UNLOCK TABLES;
 
 
 
--
 
-- Table structure for table `s`
 
--
 
 
 
DROP TABLE IF EXISTS `s`;
 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 
/*!40101 SET character_set_client = utf8 */;
 
CREATE TABLE `s` (
 
  `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 SEQUENCE=1;
 
/*!40101 SET character_set_client = @saved_cs_client */;
 
 
 
--
 
-- Dumping data for table `s`
 
--
 
 
 
LOCK TABLES `s` WRITE;
 
/*!40000 ALTER TABLE `s` DISABLE KEYS */;
 
INSERT INTO `s` VALUES (1001,1,9223372036854775806,1,1,1000,0,0);
 
/*!40000 ALTER TABLE `s` ENABLE KEYS */;
 
UNLOCK TABLES;

where we see that the lock is done on table t, inserted the values and after that lock on table/sequence s and obtained the lock.
According to the example above, insert should precede lock on both table and sequence and maybe easiest is to solve in mysqldump the problem.

Comment by Daniel Black [ 2022-09-16 ]

> shouldn't the sequence be locked automatically as needed;

Yes, I think some sequence lock is probably desirable as the intent to write to the table should provide some availability of the sequence. But I don't think it should be be an exclusive read or write lock. A sequence can be used by two different tables so maybe just a metadata lock? So guidance form someone more familar with the range of table locks would be appreciated.

stack before error where adjustment of locking could be done

 
#0  add_internal_tables (thd=0x7f4cc8000da0, prelocking_ctx=0x7f4cc8004b98, tables=0x7f4cc80316f8) at /home/dan/repos/mariadb-server-10.3/sql/sql_base.cc:4513
 
#1  0x00000000006ec435 in DML_prelocking_strategy::handle_table (this=0x7f4d40b9f7a0, thd=0x7f4cc8000da0, prelocking_ctx=0x7f4cc8004b98, table_list=0x7f4cc80138c0, need_prelocking=0x7f4d40b9f326) at /home/dan/repos/mariadb-server-10.3/sql/sql_base.cc:4667
 
#2  0x00000000006e978e in extend_table_list (thd=0x7f4cc8000da0, tables=0x7f4cc80138c0, prelocking_strategy=0x7f4d40b9f7a0, has_prelocking_list=false) at /home/dan/repos/mariadb-server-10.3/sql/sql_base.cc:3495
 
#3  0x00000000006eb4f7 in open_and_process_table (thd=0x7f4cc8000da0, tables=0x7f4cc80138c0, counter=0x7f4d40b9f73c, flags=0, prelocking_strategy=0x7f4d40b9f7a0, has_prelocking_list=false, ot_ctx=0x7f4d40b9f690) at /home/dan/repos/mariadb-server-10.3/sql/sql_base.cc:3785
 
#4  0x00000000006e9cc0 in open_tables (thd=0x7f4cc8000da0, options=@0x7f4cc8005dc0: {m_options = DDL_options_st::OPT_NONE}, start=0x7f4d40b9f750, counter=0x7f4d40b9f73c, flags=0, prelocking_strategy=0x7f4d40b9f7a0) at /home/dan/repos/mariadb-server-10.3/sql/sql_base.cc:4206
 
#5  0x00000000006ee37f in open_and_lock_tables (thd=0x7f4cc8000da0, options=@0x7f4cc8005dc0: {m_options = DDL_options_st::OPT_NONE}, tables=0x7f4cc80138c0, derived=true, flags=0, prelocking_strategy=0x7f4d40b9f7a0) at /home/dan/repos/mariadb-server-10.3/sql/sql_base.cc:5146

I started with the following as a test, still not right as at some point the table_list was non-exhaustively copied.

diff --git a/sql/sql_base.cc b/sql/sql_base.cc
 
index 60b0b88bf5f..8186fdd07a1 100644
 
--- a/sql/sql_base.cc
 
+++ b/sql/sql_base.cc
 
@@ -1754,6 +1754,8 @@ bool open_table(THD *thd, TABLE_LIST *table_list, Open_table_context *ot_ctx)
 
     */
 
     if (thd->locked_tables_mode == LTM_PRELOCKED)
 
       my_error(ER_NO_SUCH_TABLE, MYF(0), table_list->db.str, table_list->alias.str);
 
+    else if (table_list->sequence)
 
+      DBUG_RETURN(FALSE);
 
     else
 
       my_error(ER_TABLE_NOT_LOCKED, MYF(0), alias);
 
     DBUG_RETURN(TRUE);
 
diff --git a/sql/table.cc b/sql/table.cc
 
index 506195127b2..db405d755d4 100644
 
--- a/sql/table.cc
 
+++ b/sql/table.cc
 
@@ -3245,6 +3245,7 @@ unpack_vcol_info_from_frm(THD *thd, TABLE *table,
 
     TABLE_LIST *sequence= lex.current_select->table_list.first[0].next_global;
 
     sequence->next_global= table->internal_tables;
 
     table->internal_tables= sequence;
 
+    sequence->sequence= true;
 
   }
 
 
   vcol_storage.vcol_info->set_vcol_type(vcol->get_vcol_type());

> should it be used in this case at all, given that we are inserting an explicit value into the column.

Your right, the current location where it errors is without consideration of the field of the insert.

bt 10.3-32bab2ce0518d829b4f97a272fc2431169744c75 at which the error occurs

 
#0  my_error (nr=1100, MyFlags=0) at /home/dan/repos/mariadb-server-10.3/mysys/my_error.c:113
 
#1  0x00000000006e5c71 in open_table (thd=0x7f1fa4000da0, table_list=0x7f1fa4014168, ot_ctx=0x7f20209d3690) at /home/dan/repos/mariadb-server-10.3/sql/sql_base.cc:1760
 
#2  0x00000000006eb39b in open_and_process_table (thd=0x7f1fa4000da0, tables=0x7f1fa4014168, counter=0x7f20209d373c, flags=0, prelocking_strategy=0x7f20209d37a0, has_prelocking_list=false, ot_ctx=0x7f20209d3690) at /home/dan/repos/mariadb-server-10.3/sql/sql_base.cc:3733
 
#3  0x00000000006e9d00 in open_tables (thd=0x7f1fa4000da0, options=@0x7f1fa4005dc0: {m_options = DDL_options_st::OPT_NONE}, start=0x7f20209d3750, counter=0x7f20209d373c, flags=0, prelocking_strategy=0x7f20209d37a0) at /home/dan/repos/mariadb-server-10.3/sql/sql_base.cc:4208
 
#4  0x00000000006ee3bf in open_and_lock_tables (thd=0x7f1fa4000da0, options=@0x7f1fa4005dc0: {m_options = DDL_options_st::OPT_NONE}, tables=0x7f1fa40138c0, derived=true, flags=0, prelocking_strategy=0x7f20209d37a0) at /home/dan/repos/mariadb-server-10.3/sql/sql_base.cc:5148
 
#5  0x000000000069b13e in open_and_lock_tables (thd=0x7f1fa4000da0, tables=0x7f1fa40138c0, derived=true, flags=0) at /home/dan/repos/mariadb-server-10.3/sql/sql_base.h:503
 
#6  0x000000000074a9ee in mysql_insert (thd=0x7f1fa4000da0, table_list=0x7f1fa40138c0, fields=@0x7f1fa4005a28: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x208d1f8 <end_of_list>, last = 0x7f1fa4005a28, elements = 0}, <No data fields>}, values_list=@0x7f1fa4005a70: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7f1fa4013fe8, last = 0x7f1fa4013fe8, elements = 1}, <No data fields>}, update_fields=@0x7f1fa4005a58: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x208d1f8 <end_of_list>, last = 0x7f1fa4005a58, elements = 0}, <No data fields>}, update_values=@0x7f1fa4005a40: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x208d1f8 <end_of_list>, last = 0x7f1fa4005a40, elements = 0}, <No data fields>}, duplic=DUP_ERROR, ignore=false) at /home/dan/repos/mariadb-server-10.3/sql/sql_insert.cc:760
 
#7  0x000000000079f75d in mysql_execute_command (thd=0x7f1fa4000da0) at /home/dan/repos/mariadb-server-10.3/sql/sql_parse.cc:4505
 
#8  0x0000000000796f75 in mysql_parse (thd=0x7f1fa4000da0, rawbuf=0x7f1fa40137e8 "insert into t values (1)", length=24, parser_state=0x7f20209d5d88, is_com_multi=false, is_next_command=false) at /home/dan/repos/mariadb-server-10.3/sql/sql_parse.cc:7871
 
#9  0x0000000000791150 in dispatch_command (command=COM_QUERY, thd=0x7f1fa4000da0, packet=0x7f1fa4008e41 "insert into t values (1)", packet_length=24, is_com_multi=false, is_next_command=false) at /home/dan/repos/mariadb-server-10.3/sql/sql_parse.cc:1852
 
#10 0x0000000000795014 in do_command (thd=0x7f1fa4000da0) at /home/dan/repos/mariadb-server-10.3/sql/sql_parse.cc:1398
 
#11 0x0000000000948e1f in do_handle_one_connection (connect=0x448e970) at /home/dan/repos/mariadb-server-10.3/sql/sql_connect.cc:1403
 
#12 0x0000000000948bea in handle_one_connection (arg=0x448e970) at /home/dan/repos/mariadb-server-10.3/sql/sql_connect.cc:1308
 
#13 0x00007f202428ce2d in start_thread () from /lib64/libc.so.6
 
#14 0x00007f20243121b0 in clone3 () from /lib64/libc.so.6

Generated at Thu Feb 08 08:56:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.