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

Cannot insert NULL value into a AUTO_INCREMENT column in a CONNECTed MariaDB table

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 10.3, 10.4, 10.9.3, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
    • 10.4, 10.5, 10.6, 10.11
    • None
    • Windows 10

    Description

      I have a CONNECT to a MariaDB server table which contains an AUTO_INCREMENT column.

      Trying to insert a NULL value into this column always fails, while acording to the specifications I would expect it to use the next auto_incremented value.

      Minimal example using a locally installed MariaDB 10.9.3 server: (Problem came up originally when CONNECTing from a MariaDB 10.6.10 server to a MySQL 5.7 server – same effect.)¶

      use test;
      create table t0 (
        id int primary key AUTO_INCREMENT,
        name varchar(255)
      ) ENGINE=InnoDB;
      create table t0_conn engine=CONNECT table_type=MYSQL
        connection='mysql://username:xyzzy@localhost/test/t0'; 
      insert into t0 values ( null, 'test1' ); -- works OK
      select * from t0_conn; -- works OK: 1 test1
      insert int0 t0_conn values ( 2, 'test2' ); -- works OK
      insert into t0_conn values ( null, 'test3'); -- ERROR 1048 (23000): Column 'id' cannot be null
      

      Since directly inserting into the underlying table (t0) works OK, it seems that the CONNECT engine is over-eager in checking the NOT NULL condition that is implied by the primary key. Unfortunately, this makes the AUTO_INCREMENT feature unusable in the CONNECT engine.

      Attachments

        Activity

          People

            TheLinuxJedi Andrew Hutchings
            gwselke Gisbert W. Selke
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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