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

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

[26 Feb 2008 14: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 20: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.