Details
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
-
-
- Closed
-
-
MDEV-30155 SEQUENCEs/VIEWS/TRIGGERS/PROCEDURES dumped with mariadb-dump cannot be restored in different DB
-
- Confirmed
-
Activity
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.
> 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
|
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
|
I'm not sure it's mysqldump to blame.
The questions are: