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

Confusing error code when doing auto-increment insert for out-of-range values



    • Bug
    • Status: Closed (View Workflow)
    • Blocker
    • Resolution: Fixed
    • 5.5.28, 5.3.8, 5.2.13, 5.1.62
    • 5.5.28
    • None
    • None
    • All


      When one inserts a row into a table with auto-increment, the error message
      can be confusing:

      create table t1 (a smallint primary key auto_increment);
      insert into t1 values(32767);
      insert into t1 values(NULL);
      ERROR 1062 (23000): Duplicate entry '32767' for key 'PRIMARY

      In some cases on gets:
      ERROR 1264 (22003): Out of range value for column 'id' at row 1
      or there is a warning about the "out of range".
      This depends on SQL Mode, engine used or if one inserts one or multiple row at a time.
      Inserting negative numbers for the auto increment columns also works differently between engines.

      The task is to ensure that all engines works exactly the same way and that one will always get:
      ERROR 1264 (22003): Out of range value for column
      when an auto increment overflows.

      To fix this one need to do changes in sql/handler.cc
      and in innodb/xtradb.

      Because of how the auto_increment checking is done, we can fix all issues
      except for the case of inserting a generated max bigint unsigned value (18446744073709551615).

      In this case all engines will report:
      "Failed to read auto-increment value from storage engine"
      To fix this would require a handler interface change that I am not confident in doing in 5.5
      In additional this would be hard to fix this in InnoDB as stores 'the next number to be used'. This means we can't distinguish between inserting the max possible number or if there is still room for the max number.

      Another effect of this is that for columns of type bigint unsigned, the max generated auto increment value is 18446744073709551614




            monty Michael Widenius
            monty Michael Widenius
            0 Vote for this issue
            3 Start watching this issue



              Git Integration

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