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

Check constraint with query crashes server and renders DB unusable

Details

    Description

      I'm evaluating the possibility of migrating and existing SQL Anywhere database to MariaDB. While trying to port the existing tables, I've come across a scenario which crashes the mysqld server and renders the database useless. I can restart the server but when I attempt to do anything with the database, it crashes again (error log attached).

      Here is SQL that that will reproduce this problem:

      CREATE TABLE column_code
      		 
      ( column_name varchar(50)
      		 
      , code        char(2)
      		 
      , description varchar(255)
      		 
      , primary key (column_name, code asc)
      		 
      );
      		 
      CREATE TABLE sample_table
      		 
      ( id          integer  NOT NULL AUTO_INCREMENT PRIMARY KEY
      		 
      , value1      integer
      		 
      , value2      char(1)
      		 
      , some_code   char(2)  NOT NULL CHECK(@column in (select code from column_code where column_name = 'sample_table.some_code'))
      		 
      );

      I'm not defending the way this was implemented, but it is what it is. I haven't found any documentation stating this shouldn't work. Please let me know if that's the case.

      Thanks!

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          jessiepotts Jessie Potts added a comment -

          Here's the results of --print-defaults, as recommend by the bug reporting guidelines.

          c:\Program Files\MariaDB 10.2\bin>mysqld --print-defaults
          		 
          mysqld would have been started with the following arguments:
          		 
          --datadir=C:/Program Files/MariaDB 10.2/data --port=3333 --sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION --default_storage_engine=innodb --innodb_buffer_pool_size=3070M --innodb_log_file_size=50M --feedback=ON

          jessiepotts Jessie Potts added a comment - Here's the results of --print-defaults, as recommend by the bug reporting guidelines. c:\Program Files\MariaDB 10.2\bin>mysqld --print-defaults mysqld would have been started with the following arguments: --datadir=C:/Program Files/MariaDB 10.2/data --port=3333 --sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION --default_storage_engine=innodb --innodb_buffer_pool_size=3070M --innodb_log_file_size=50M --feedback=ON
          serg Sergei Golubchik added a comment -

          It shouldn't work. There was a test in the code to disallow user variables and subqueries in CHECK, but it was misplaced. I fixed that.

          serg Sergei Golubchik added a comment - It shouldn't work. There was a test in the code to disallow user variables and subqueries in CHECK, but it was misplaced. I fixed that.
          jessiepotts Jessie Potts added a comment -

          Thanks Sergei! I'd prefer being able to do this but having the engine tell me I can't is much better than letting me do it and then crashing.

          jessiepotts Jessie Potts added a comment - Thanks Sergei! I'd prefer being able to do this but having the engine tell me I can't is much better than letting me do it and then crashing.

          People

            serg Sergei Golubchik
            jessiepotts Jessie Potts
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.