auto_increment_offset (and increment) limited to 32bits.. or 16 bits, it's unclear

[Stewart Smith]

(when trying to fix up strict_autoinc_1myisam.test)

+SHOW VARIABLES LIKE 'auto_increment%';
+Variable_name Value
+auto_increment_increment 1
+auto_increment_offset 1
 set auto_increment_increment=1000;
-set auto_increment_offset=700;
+set auto_increment_offset=5000000000;
+Warnings:
+Warning 1292 Truncated incorrect auto_increment_offset value: '5000000000'
+SHOW WARNINGS;
+Level Code Message
+Warning 1292 Truncated incorrect auto_increment_offset value: '5000000000'
+SHOW VARIABLES LIKE 'auto_increment%';
+Variable_name Value
+auto_increment_increment 1000
+auto_increment_offset 65535

[Padraig O'Sullivan (posulliv)]

I was able to reproduce this:

drizzle> use test
Database changed
drizzle> show variables like 'auto_increment%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)

drizzle> set auto_increment_increment=1000;
Query OK, 0 rows affected (0.00 sec)

drizzle> set auto_increment_offset=700;
Query OK, 0 rows affected (0.00 sec)

drizzle> set auto_increment_offset=5000000000;
Query OK, 0 rows affected, 1 warning (0.01 sec)

drizzle> show warnings;
+---------+------+---------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect auto_increment_offset value: '5000000000' |
+---------+------+---------------------------------------------------------------+
1 row in set (0.00 sec)

drizzle> show variables like 'auto_increment%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1000 |
| auto_increment_offset | 65535 |
+--------------------------+-------+
2 rows in set (0.00 sec)

drizzle>

Thanks, Padraig.

[Toru Maesaka (tmaesaka)]

I've just pushed a fix for this based on the discussion with Monty and Stewart. Here's a summary:

Firstly, this is a correct behavior in MySQL:

"Attempting to set the value of either of these two variables to an integer greater than 65,535 or less than 0 causes its value to be set to 65,535 instead. "

However, there is no reason to have this restriction since there is no benefit in having it in the first place. So I've raised the width of this value to 64 bits. We also talked about making auto_increment_increment a signed 64bit integer so that we can use negative values but I haven't done this yet since I wasn't sure if adding a new 'sys_var_session_int64_t' type was appropriate.

If it turns out to be appropriate then I'll write a patch and push it on a different occasion :)

Current Behavior
-----------------------------

drizzle> create table incr_tbl (auto_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.02 sec)

drizzle> set auto_increment_offset=5000000000;
Query OK, 0 rows affected (0.00 sec)

drizzle> set auto_increment_increment=5000000000;
Query OK, 0 rows affected (0.00 sec)

drizzle> show variables like "auto_increment_%";
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| auto_increment_increment | 5000000000 |
| auto_increment_offset | 5000000000 |
+--------------------------+------------+
2 rows in set (0.00 sec)

drizzle> insert into incr_tbl values (null);
Query OK, 1 row affected (0.01 sec)

drizzle> select auto_id from incr_tbl;
+------------+
| auto_id |
+------------+
| 5000000000 |
+------------+
1 row in set (0.01 sec)

drizzle> insert into incr_tbl values (null);
Query OK, 1 row affected (0.01 sec)

drizzle> select auto_id from incr_tbl;
+-------------+
| auto_id |
+-------------+
| 5000000000 |
| 10000000000 |
+-------------+
2 rows in set (0.00 sec)

Cheers,
Toru