[Resolved] INT (5) field inserting 10 digits

[[deleted]]

I have an INT (5) field in my MySQL database. My registration page inserts an auto_increment userid when a user registers for the first time. However, instead of a 5 digit integer being created a 10 digit integer is inserted.

I don't understand how this could be happening. I went from 2 digit userid being inserted to 10 without changing the field type.

ANy help anyone can provide would be greatly appreciated.

Thanks.

[Lars_Berg]

An int type in Mysql uses 4 bytes for storage regardless. The 5 is only used for display purposes (by some tools) and does affect which values that can be stored.

With 4 bytes you can store values in the range -2147483648 to 2147483647.

YaeosMd.

[[deleted]]

The number it keeps trying to insert is max signed value for INT 2147483647. I don't understand why it keeps trying to do so when the previous entries are in the double digits.

[ahundiak]

Do you by any chance have two auto_inc columns in the table? That's a no no. But CREATE TABLE would probably have failed anyways.

Please Post:
1. The CREATE TABLE command used. Keep it simple, just a couple of fields including the INT5 column.
2. The INSERT statement.

[[deleted]]

Here it is:

CREATE TABLE users (
  userid int(4) NOT NULL auto_increment,
  firstname varchar(50) NOT NULL default '',
  lastname varchar(50) NOT NULL default '',
  emailaddress varchar(100) NOT NULL default '',
  status_id int(5) NOT NULL default '0',
  password varchar(40) NOT NULL default '',
  PRIMARY KEY  (userid)
) TYPE=MyISAM AUTO_INCREMENT=2147483647 ; // not sure why this says this

and now for the insert

$qInsertNewMemberInfo = "INSERT INTO users SET
              firstname = '$firstname',
              middleinitial = '$middle',
              lastname = '$lastname',
              streetaddress1 = '$streetaddress1',
              streetaddress2 = '$streetaddress2',
              streetaddress3 = '$streetaddress3',
              city = '$city',
              stateid = '$state',
              zipcode = '$zipcode',
              dareacode = '$dareacode',
              dexchange= '$dexchange',
              dphonenumber = '$dphonenumber',
              emergencyareacode = '$emergencyareacode',
              emergencyexchange= '$emergencyexchange',
              emergencyphonenumber = '$emergencyphonenumber',
              emergencycontactname = '$emergencycontactname',
              password = md5('$pwd'),
              membertype_id = '$membertype',
              waiverid = '$readwaiver',
              readrefund= '$readrefund',
              emailaddress = '$newemail',
              gender_id ='$gender',
              agegroup_id ='$agegroup',
              mailinglist_everyone = '$mailinglist_everyone',
              mailinglist_longrun = '$mailinglist_longrun',
              registrationdate = NOW()";

THe only auto_inc column is userid.

THanks.

[ahundiak]

Hmmm.
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#CREATE_TABLE
This line:
TYPE=MyISAM AUTO_INCREMENT=2147483647
Says to use that long number for the first autoinc value. So the insert is working as designed. You need to determine why someone added that line and either delete it or set it to 1. Sometimes the start value is used to allow data replication between various databases.

Secondly, while mysql supports the INSERT INTO SET syntax it's not standard SQL. I'd suggest using INSERT INTO VALUES.

Finally, it's not a bad idea to use the word NULL (no quotes) for the value of the auto_inc column. A value of NULL means to get the next auto_inc value. And it makes your code a bit easier to read.

On the hand, kudos for the way you formatted your sql statement to allow it to be readable. So many people just jam it into one long line.

[[deleted]]

I tried to change the value of the auto_inc column to NULL and setting the next auto_increment value to using phpMyAdmin, but they the values did not change. I exported the table and made the changes to the SQL when re-importing and the values still did not change to NULL and 1.

[ahundiak]

Not sure what to suggest. I suppose there could be something unusual with the database setup. Keep in mind that the only way to reset the auto_inc is by deleting the table and creating it again.

You might try a very simple case.

CREATE TABLE x
id INTEGER AUTO_INCREMENT PRIMARY KEY,
name CHAR(20);
);
INSERT INTO x (id,name) VALUES(NULL,'Hello');

And see what you get. Then recreate using INT(5). You are using mysql?

[[deleted]]

The problem turned out to be that I had a userid of -1. When I removed the -1 and change the next auto_inc the changes stuck,

Thanks for everyone's help.