[MDEV-20070] cannot insert into write locked tables with sequences - Jira

Details

Type: Bug
Status: Confirmed (View Workflow)
Priority: Critical
Resolution: Unresolved
Affects Version/s: 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.5, 10.6, 10.11, 11.4
Component/s: Locking, Sequences
Labels:
None
Environment:
Debian buster and Ubuntu 19.04 and 18.04

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

Attachments

Issue Links

duplicates

MDEV-20071 mysqldump won't work correct on sequences

Closed

relates to

MDEV-21786 mysqldump will forget sequence definition details on --no-data dump

Closed

MDEV-30155 SEQUENCEs/VIEWS/TRIGGERS/PROCEDURES dumped with mariadb-dump cannot be restored in different DB

Confirmed

Activity

Ascending order - Click to sort in descending order

miracee created issue - 2019-07-16 09:48

Elena Stepanova made changes - 2019-07-21 22:56

Field	Original Value	New Value
Link		This issue duplicates ~~MDEV-20071~~ [ ~~MDEV-20071~~ ]

Elena Stepanova added a comment - 2019-07-21 23:04

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.

Elena Stepanova added a comment - 2019-07-21 23:04 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.

Elena Stepanova made changes - 2019-07-21 23:04

Component/s		Locking [ 10900 ]
Component/s	Backup [ 13902 ]
Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.4 [ 22408 ]
Affects Version/s		10.3 [ 22126 ]
Assignee		Sergey Vojtovich [ svoj ]

Daniel Black made changes - 2020-08-20 22:24

Link

This issue relates to ~~MDEV-21786~~ [ ~~MDEV-21786~~ ]

Anel Husakovic added a comment - 2020-08-27 03:04

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.

Anel Husakovic added a comment - 2020-08-27 03:04 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.

Daniel Black made changes - 2020-10-09 00:01

Assignee

Sergey Vojtovich [ svoj ]

Anel Husakovic [ anel ]

Anel Husakovic made changes - 2020-10-10 01:50

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

{code:sql}
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
{code}
Looks like {{mysqldump}} not working correct on sequences.

Best regards,

Susanne

Sergei Golubchik made changes - 2021-12-06 21:33

Workflow

MariaDB v3 [ 98246 ]

MariaDB v4 [ 141404 ]

Daniel Black added a comment - 2022-09-16 09:04

> 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

Daniel Black added a comment - 2022-09-16 09:04 > 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

Daniel Black made changes - 2022-09-20 09:38

Summary

mysqldump won't work correct on sequences

cannot insert into write locked tables with sequences

Anel Husakovic made changes - 2022-12-05 08:30

Link

This issue relates to MDEV-30155 [ MDEV-30155 ]

Julien Fritsch made changes - 2023-04-27 14:27

Fix Version/s

10.3 [ 22126 ]

Jira Automation (IT) made changes - 2024-07-04 03:50

Zendesk Related Tickets

132208 152920

Alice Sherepa made changes - 2024-09-13 14:42

Fix Version/s		10.5 [ 23123 ]
Fix Version/s		10.6 [ 24028 ]
Fix Version/s		10.11 [ 27614 ]
Fix Version/s		11.2 [ 28603 ]
Fix Version/s		11.4 [ 29301 ]
Fix Version/s		11.6 [ 29515 ]
Fix Version/s	10.4(EOL) [ 22408 ]

Alice Sherepa made changes - 2024-09-13 14:43

Status

Open [ 1 ]

Confirmed [ 10101 ]

Sergei Golubchik made changes - 2024-09-13 14:43

Assignee

Anel Husakovic [ anel ]

Alice Sherepa made changes - 2024-09-13 14:44

Fix Version/s

11.5 [ 29506 ]

Sergei Golubchik made changes - 2024-09-13 14:50

Assignee

Dmitry Shulga [ JIRAUSER47315 ]

Alice Sherepa made changes - 2024-09-13 14:58

Fix Version/s

11.5 [ 29506 ]

Edward Stoever added a comment - 2024-10-10 17:31

As a workaround, it is possible to use sed or perl to search/replace lines beginning with "LOCK TABLES", commenting them out. Example:

# The following command searches the file dump.sql for lines beginning with "LOCK TABLES" and comments them out:

perl -p -i -e "s/^LOCK TABLES /-- (commented out intentionally) LOCK TABLES /g" dump.sql

Edward Stoever added a comment - 2024-10-10 17:31 As a workaround, it is possible to use sed or perl to search/replace lines beginning with "LOCK TABLES", commenting them out. Example: # The following command searches the file dump.sql for lines beginning with "LOCK TABLES" and comments them out: perl -p -i -e "s/^LOCK TABLES /-- (commented out intentionally) LOCK TABLES /g" dump.sql

Julien Fritsch made changes - 2024-11-18 08:56

Priority

Major [ 3 ]

Critical [ 2 ]

Julien Fritsch made changes - 2024-11-22 14:56

Fix Version/s

11.6(EOL) [ 29515 ]

Julien Fritsch made changes - 2024-11-22 17:06

Fix Version/s

11.2(EOL) [ 28603 ]

People

Assignee:: Dmitry Shulga

Reporter:: miracee

Votes:: 3 Vote for this issue

Watchers:: 12 Start watching this issue

Dates

Created:: 2019-07-16 09:48

Updated:: 2024-12-27 14:17

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration