CODEWITHSUNDEEP
mysql
user4166144
user4166144

Reputation: 251

MySQL: Strange AUTO_INCREMENT

Table looks like:

mysql> DESC text;
+-----------------+--------------+------+-----+-------------------+----------------+
| Field           | Type         | Null | Key | Default           | Extra          |
+-----------------+--------------+------+-----+-------------------+----------------+
| id              | int(11)      | NO   | PRI | NULL              | auto_increment |
| text            | varchar(255) | YES  |     | NULL              |                |
+-----------------+--------------+------+-----+-------------------+----------------+
2 rows in set (0.00 sec)

and AUTO_INCREMENT is 1:

mysql> ALTER TABLE text AUTO_INCREMENT = 1;
Query OK, 1 row affected (0.36 sec)
Records: 1  Duplicates: 0  Warnings: 0

but I get strange id like:

mysql> SELECT id FROM text;
+------------+
| id         |
+------------+
| 2147483647 |
+------------+
1 row in set (0.00 sec)

What is the problem?

Upvotes: 0

Views: 74

Answers (1)

exussum
exussum

Reputation: 18550

When you change the auto increment it is set to greatest(your_value,max(column)+ 1)

though I cant find the part in the docs which mention it, it is in the comments

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

ALTER TABLE text AUTO_INCREMENT = 1;

then check the result of 

SELECT `AUTO_INCREMENT`
FROM  INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'DatabaseName'
AND   TABLE_NAME   = 'text';

to confirm that its not actually 1

Upvotes: 2

Related Questions