9

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.

Jens
  • 67,715
  • 15
  • 98
  • 113
Chiwda
  • 1,233
  • 7
  • 30
  • 52

1 Answers1

11

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

Jens
  • 67,715
  • 15
  • 98
  • 113
  • Why does the autoincrement start outside the range? I am not giving it any value at all. So it should create a valid value. – Chiwda May 30 '17 at 13:01
  • @Chiwda you give: `AUTO_INCREMENT=32768` – Jens May 30 '17 at 13:02
  • That's in the definition of the field. I'm NOT GIVING ANY VALUE. Also, the table is empty, right now at least, so it should start with 1. – Chiwda May 30 '17 at 13:04
  • 1
    @Chiwda THAT IS THE START VALUE and Autoincrement tries to add one to it – Jens May 30 '17 at 13:09
  • 4
    I the table already inserted full and somebody delete data by DELETE query. if you want reset auto increment value use TRUNCATE TABLE query or ALTER TABLE tablename AUTO_INCREMENT = 1 – huhushow May 30 '17 at 13:17
  • `I'm NOT GIVING ANY VALUE` - and he doesn't even read what his create table statement says. +1 from me, this is the correct answer. @Chiwda you should pay attention to what you do before you start caps lock-ing on people. – N.B. May 30 '17 at 13:43
  • That's not from my create statement. I had created the table a while ago and I did an export to get those values. I have not ever set the values of AutoIncrement. Sorry, about the CAPS LOCK if it appears offensive, but I keep coming back to the point that I have not provided any value to the AutoIncrement or to the field itself. So that will maybe remain a mystery. I did add 10 records using import from CSV (and they numbered 1 to 10) and then deleted them, but that's about it. I just now TRUNCATED the table as suggested and it now works. – Chiwda May 30 '17 at 14:38
  • The only thing I can think of is that one of the failed INSERT statements while I was experimenting set that value. – Chiwda May 30 '17 at 14:39
  • 1
    @Chiwda So someone has entered the max number of entries, thats why the Auto_increment Counter is at the end – Jens May 30 '17 at 14:39
  • 1
    @Chiwda BTW: If you delete a record, the AUTO_INCREMENT Counter will not set back – Jens May 30 '17 at 14:41