Bug #34837 Errors are not coming on assigning invalid values auto_increment_increment

• Submitted: 26 Feb 2008 13:04
• Modified: 27 Feb 2008 19:51
• Reporter: Salman Rawala
• Status: Can't repeat
• Category: MySQL Server: Compiling
• Severity: S3 (Non-critical)
• Version: 5.1.22
• OS: Any
• CPU Architecture: Any
• Tags: auto_increment_increment, invalid value of auto_increment

[26 Feb 2008 13:04] Salman Rawala

Description:
When we assign any invalid value to variable like negative value, value in decimal or value out of range, error should appear that this value cannot be set to variable or value out of range.

Current behavior is that MySQL DB truncs value that is out of range to boundary value.

How to repeat:
You can reproduce this bug by using following code:

SET @@global.auto_increment_increment = 0;
SELECT @@global.auto_increment_increment;
SET @@global.auto_increment_increment = -1024;
SELECT @@global.auto_increment_increment;
SET @@global.auto_increment_increment = 65536;
SELECT @@global.auto_increment_increment;
--Error ER_PARSE_ERROR
SET @@global.auto_increment_increment = 65530.34.;
SELECT @@global.auto_increment_increment;

Actual Output: 

SET @@global.auto_increment_increment = 0;
SELECT @@global.auto_increment_increment;
@@global.auto_increment_increment
1
SET @@global.auto_increment_increment = -1024;
SELECT @@global.auto_increment_increment;
@@global.auto_increment_increment
65535
SET @@global.auto_increment_increment = 65536;
SELECT @@global.auto_increment_increment;
@@global.auto_increment_increment
65535
SET @@global.auto_increment_increment = 65530.34.;

Suggested fix:
Expected Output:

SET @@global.auto_increment_increment = 0;
SELECT @@global.auto_increment_increment;
@@global.auto_increment_increment
1					 //Here error should occur that the value is out of range							
SET @@global.auto_increment_increment = -1024;
SELECT @@global.auto_increment_increment;
@@global.auto_increment_increment
65535                                    //Error should occur here that negative value cannot be                          		 		        assigned to variable 
SET @@global.auto_increment_increment = 65536;
SELECT @@global.auto_increment_increment;
@@global.auto_increment_increment
65535                                    //Here error should occur that value is out of range

[27 Feb 2008 19:51] Sveta Smirnova

Thank you for the report.

I can not repeat described behavior:

SET @@global.auto_increment_increment = 0;
Warnings:
Warning 1292    Truncated incorrect auto-increment-increment value: '0'
SELECT @@global.auto_increment_increment;
@@global.auto_increment_increment
1
SET @@global.auto_increment_increment = -1024;
Warnings:
Warning 1292    Truncated incorrect auto-increment-increment value: '0'
SELECT @@global.auto_increment_increment;
@@global.auto_increment_increment
1
SET @@global.auto_increment_increment = 65536;
Warnings:
Warning 1292    Truncated incorrect auto_increment_increment value: '65536'
SELECT @@global.auto_increment_increment;
@@global.auto_increment_increment
65535
SET @@global.auto_increment_increment = 65530.34;
ERROR 42000: Incorrect argument type to variable 'auto_increment_increment'
SELECT @@global.auto_increment_increment;
@@global.auto_increment_increment
65535

You can see warnings after each incorrect query.