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

Question

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.

Answer 1

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