Bug #42400 InnoDB autoinc code can't handle floating-point columns

• Submitted: 28 Jan 2009 5:37
• Modified: 18 Jun 2010 23:05
• Reporter: Sunny Bains
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S1 (Critical)
• Version: 5.1+ and plugin
• OS: Any
• CPU Architecture: Any
• Tags: autoinc

[28 Jan 2009 5:37] Sunny Bains

Description:
This bug was reported by a user of the InnoDB plugin version here:

  http://forums.innodb.com/read.php?3,400,400#msg-400

InnoDB with the latest AUTOINC changes assumes that the AUTOINC column type can only be an integer. This assertion causes a crash.

How to repeat:
CREATE TABLE t1 (
        c1 DOUBLE NOT NULL AUTO_INCREMENT,
        c2 INT,
        PRIMARY KEY (c1)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO t1 VALUES('', 1);
INSERT INTO t1 VALUES('', 2);
SELECT * FROM t1;
ALTER TABLE t1 CHANGE c1 c1 SERIAL; 
SELECT * FROM t1;
INSERT INTO t1 VALUES('', 3);
INSERT INTO t1 VALUES('', 4);
SELECT * FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (
        c1 FLOAT NOT NULL AUTO_INCREMENT,
        c2 INT,
        PRIMARY KEY (c1)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO t1 VALUES('', 1);
INSERT INTO t1 VALUES('', 2);
SELECT * FROM t1;
ALTER TABLE t1 CHANGE c1 c1 SERIAL; 
SELECT * FROM t1;
INSERT INTO t1 VALUES('', 3);
INSERT INTO t1 VALUES('', 4);
SELECT * FROM t1;
DROP TABLE t1;

Suggested fix:
Check for floating-point column types when calculating the max value for a column type.

 /************************************************************************
-Get the upper limit of the MySQL integral type. */
+Get the upper limit of the MySQL integral and floating-point type. */

 ulonglong
 ha_innobase::innobase_get_int_col_max_value(
@@ -3439,6 +3439,7 @@ ha_innobase::innobase_get_int_col_max_va
                max_value = 0xFFFFFFFFULL;
                break;
        case HA_KEYTYPE_LONG_INT:
+       case HA_KEYTYPE_FLOAT:
                max_value = 0x7FFFFFFFULL;
                break;
        /* BIG */
@@ -3446,6 +3447,7 @@ ha_innobase::innobase_get_int_col_max_va
                max_value = 0xFFFFFFFFFFFFFFFFULL;
                break;
        case HA_KEYTYPE_LONGLONG:
+       case HA_KEYTYPE_DOUBLE:
                max_value = 0x7FFFFFFFFFFFFFFFULL;
                break;
        default:

[2 Feb 2009 22:17] Ken Jacobs

Given that the risk is low, and the effort is zero (since there is a fix) and since this affects 5.1, there would appear to be little reason to defer this to 6.0.

Please consider if this patch can be included in 5.1.  It will, of course, be fixed in the InnoDB Plugin so that is another alternative ....

[13 Mar 2009 19:49] Timothy Smith

Pushed to 5.1.33; Docs please return to "Patch approved" waiting for a 6.0 snapshot.

  Applying InnoDB snashot 5.1-ss4350, part 2.  Fixes
  
  Bug #42400    InnoDB autoinc code can't handle floating-point columns
  
  Detailed revision comments:
  
  r4065 | sunny | 2009-01-29 16:01:36 +0200 (Thu, 29 Jan 2009) | 8 lines
  branches/5.1: In the last round of AUTOINC cleanup we assumed that AUTOINC
  is only defined for integer columns. This caused an assertion failure when
  we checked for the maximum value of a column type. We now calculate the
  max value for floating-point autoinc columns too.
  
  Fix Bug#42400 - InnoDB autoinc code can't handle floating-point columns
  rb://84 and Mantis issue://162
  
  r4111 | sunny | 2009-02-03 22:06:52 +0200 (Tue, 03 Feb 2009) | 2 lines
  branches/5.1: Add the ULL suffix otherwise there is an overflow.

[15 Mar 2009 0:17] Paul DuBois

Noted in 5.1.33 changelog.

For InnoDB tables, inserting into floating-point AUTO_INCREMENT
columns failed. 

Setting report to Patch Approved pending push into 6.0.x.

[14 Sep 2009 19:42] Paul DuBois

Noted in 5.4.2 changelog.

[5 May 2010 15:16] Bugs System

Pushed into 5.1.47 (revid:[e-mail]-20100505145753-ivlt4hclbrjy8eye) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[6 May 2010 17:55] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

[28 May 2010 6:02] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100524190136-egaq7e8zgkwb9aqi) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (pib:16)

[28 May 2010 6:31] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100524190941-nuudpx60if25wsvx) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[28 May 2010 6:58] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100524185725-c8k5q7v60i5nix3t) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[29 May 2010 15:19] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

[15 Jun 2010 8:15] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100615080459-smuswd9ooeywcxuc) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)

[15 Jun 2010 8:32] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100615080558-cw01bzdqr1bdmmec) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (pib:16)

[17 Jun 2010 12:06] Bugs System

Pushed into 5.1.47-ndb-7.0.16 (revid:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 12:51] Bugs System

Pushed into 5.1.47-ndb-6.2.19 (revid:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 13:33] Bugs System

Pushed into 5.1.47-ndb-6.3.35 (revid:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)