Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15106

Unexpected ER_WRONG_INSERT_INTO_SEQUENCE upon INSERT with multiple locks on sequences

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.3(EOL)
    • 10.3.7
    • Sequences
    • None
    • b8c92d752c34e51dbe8ed551542415e2481581d7

    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.
      

      Attachments

        Issue Links

          Activity

            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;
            

            sanja Oleksandr Byelkin added a comment - 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;

            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);
            

            sanja Oleksandr Byelkin added a comment - 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);

            monty is above correct?

            sanja Oleksandr Byelkin added a comment - monty is above correct?

            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.

            elenst Elena Stepanova added a comment - 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.

            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)

            sanja Oleksandr Byelkin added a comment - 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)

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

            How can it possibly be logical?

            elenst Elena Stepanova added a comment - You do single INSERT. You get an error message "Wrong, you can only do single INSERT" How can it possibly be logical ?

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

            monty Michael Widenius added a comment - Fixed by removing the check of single lock and let MDL code handle deadlock detection

            People

              monty Michael Widenius
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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