CODEWITHSUNDEEP
mysqlsql-insertauto-increment
Chiwda
Chiwda

Reputation: 1364

MySQL error #167 - Out of range value for column even when it is autoincremented

I have a field (called ID) that is defined as: 

smallint(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=32768

I have a query as follows (simplified):

INSERT INTO delivery (`ConsigneeName`, `ConsigneePhone`) VALUES ('John Doe', '9999999999')

And have also tried 

INSERT INTO delivery (`ID`, `ConsigneeName`, `ConsigneePhone`) VALUES (NULL, 'John Doe', '9999999999')

However, I keep getting the error:

#167 - Out of range value for column 'ID' at row 1

So what could be the problem? I am at my wits end - have tried with ' and ` on the field names and on the values just to see if that was the problem, but I get the same result. I even tried the phpmyadmin interface to insert values (and left ID blank) but I get the same error.

Thanx in advance for the help.

Upvotes: 10

Views: 14087

Answers (2)

Eugene Yarmash
Eugene Yarmash

Reputation: 150178

When the AUTO_INCREMENT value for a column reaches its limit (e.g. 4294967295 for INT UNSIGNED), no new rows can be inserted into the table automatically. This can happen for several reasons:

  • Explicit AUTO_INCREMENT column inserts with large values (e.g. because of a bug in the program). If a value larger than the current max value is explicitly inserted, then AUTO_INCREMENT will be set to this value + 1.

  • Using lots of INSERT-like statements (e.g. INSERT IGNORE, INSERT ... ON DUPLICATE KEY UPDATE) that don't insert any new rows, but increment the AUTO_INCREMENT value.

  • The table has too much rows so the AUTO_INCREMENTcolumn reaches the upper limit of the data type. Consider using a type with a greater range (e.g. BIGINT instead of INT) for the AUTO_INCREMENT column.

Upvotes: 0

Jens
Jens

Reputation: 69505

Your autoincrement starts outside of the range of smallint datetype. So you can not add any entry. Change the datatype of this column to int.

See the mysql documentation about datatype ranges

SMALLINT -32768 32767

Upvotes: 13

Related Questions