Solved

how to set a auto_increment to a desired value?

Posted on 2002-03-07
3
712 Views
Last Modified: 2008-03-10
I have a table where one colum is uid, its a int not null auto_increment column , and it´s the primary key for that table, currently the highest value is 5284, but when I try to insert the next value, I get a huge number (2147483647) , not 5285, even phpMyAdmin in the propoerties of the table says "Next Autoindex 2,147,483,647" I´ve tried recreating the table and inserting the values again, and I still get that huge value when I try to insert a new one.

I´ve loooked in mySQL documentation on how to set a specific value in the next auto_increment value, but so far I´ve found nothing. Any clues?
0
Comment
Question by:aguilera007
3 Comments
 
LVL 5

Accepted Solution

by:
kelfink earned 200 total points
ID: 6848693
Generally, it looks like this works fine...

mysql> create table testinc ( i int not null primary key);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into testinc values ( 5284 );
Query OK, 1 row affected (0.00 sec)

mysql> alter table testinc change i i int auto_increment not null;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into testinc values ( );
Query OK, 1 row affected (0.00 sec)

mysql> select * from boff;
+------+
| i    |
+------+
| 5284 |
| 5285 |
+------+
2 rows in set (0.00 sec)

-- BUT --
if, for some reason you need to change the number (as appears necessary in your case) you can set the next-number using:

alter table testinc auto_increment = 5285 ;

Incidentally, mysql seems to ignore my tested request to set the next number to a number which has already been used.

0
 
LVL 5

Expert Comment

by:andriv
ID: 6849472
What are the properties you set for that field.
0
 

Author Comment

by:aguilera007
ID: 6849508
Great help :D
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now