[MDEV-15106] Unexpected ER_WRONG_INSERT_INTO_SEQUENCE upon INSERT with multiple locks on sequences Created: 2018-01-28  Updated: 2018-05-18  Resolved: 2018-05-03

Status: Closed
Project: MariaDB Server
Component/s: Sequences
Affects Version/s: 10.3
Fix Version/s: 10.3.7

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Michael Widenius
Resolution: Fixed Votes: 0
Labels: None
Environment:

b8c92d752c34e51dbe8ed551542415e2481581d7

Issue Links:
Duplicate
is duplicated by MDEV-15782 Assertion `0' or unknown error on sla... Closed

 Description    

CREATE SEQUENCE seq1;
 
CREATE SEQUENCE seq2;
 
LOCK TABLE seq1 WRITE, seq2 WRITE;
 
INSERT INTO seq1 VALUES (1, 1, 100000, 1, 1, 100, 1, 1);
 
 
 
DROP SEQUENCE seq1, seq2;


MariaDB [test]> INSERT INTO seq1 VALUES (1, 1, 100000, 1, 1, 100, 1, 1);
 
ERROR 4093 (HY000): Wrong INSERT into a SEQUENCE. One can only do single table INSERT into a sequence object (like with mysqldump).  If you want to change the SEQUENCE, use ALTER SEQUENCE instead.



 Comments   
Comment by Oleksandr Byelkin [ 2018-04-27 ] 

CREATE SEQUENCE seq1;
 
CREATE SEQUENCE seq2;
 
LOCK TABLE seq1 WRITE;
 
INSERT INTO seq1 VALUES (1, 1, 100000, 1, 1, 100, 1, 1);
 
UNLOCK TABLE;
 
LOCK TABLE seq1 WRITE, seq2 WRITE;
 
INSERT INTO seq1 VALUES (1, 1, 100000, 1, 1, 100, 1, 1);
 
UNLOCK TABLE;
 
DROP SEQUENCE seq1, seq2;

Comment by Oleksandr Byelkin [ 2018-04-27 ]

Accoring to code and comments in it it is expected behavior and should be documented probably:

   /*
 
      User tries to write a full row directly to the sequence table with
 
      INSERT or LOAD DATA.
 
    
      - Get an exclusive lock for the table. This is needed to ensure that
 
        we excute all full inserts (same as ALTER SEQUENCE) in same order
 
        on master and slaves
 
      - Check that we are only using one table.
 
        This is to avoid deadlock problems when upgrading lock to exlusive.
 
      - Check that the new row is an accurate SEQUENCE object
 
    */
 
    
    THD *thd= table->in_use;
 
    if (thd->lock->table_count != 1)
 
      DBUG_RETURN(ER_WRONG_INSERT_INTO_SEQUENCE);

Comment by Oleksandr Byelkin [ 2018-04-27 ]

monty is above correct?

Comment by Elena Stepanova [ 2018-04-27 ]

It cannot be expected behavior (or if it is, it's a wrong expectation and must be fixed).
Failing INSERT is acceptable, that's not a big deal if it helps safety. But the error code/message it currently produces is obviously wrong and needs to be changed to something sensible.

Comment by Oleksandr Byelkin [ 2018-04-27 ]

OK, better wording is it is exactly programmed by the author behaviour, and so it is completely expected from this code.
It is not up to me fix some ideological decisions. For me the comment looks quite logical and so behaviour is expeting (should be only documented)

Comment by Elena Stepanova [ 2018-04-27 ]

You do single INSERT.
You get an error message "Wrong, you can only do single INSERT"

How can it possibly be logical?

Comment by Michael Widenius [ 2018-05-03 ]

Fixed by removing the check of single lock and let MDL code handle deadlock detection

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