IP HELP Please !!!

[dbeckerton]

Hello i am currently going through with a total remodel of my game and i have noticed large amounts of inactive members that i deleted, now i have large ip gaps is there anyway of fixing this

[Spudinski]

Step 1:

Run this in phpMyAdmin.

TRUNCATE `users`

Click yes, wada wada.

Step 2:

And then,

ALTER TABLE `users`  AUTO_INCREMENT = 0;
[left]

[/left]

 

Please note: Step 2 is NOT advised.

[bluegman991]

Step 1:

Run this in phpMyAdmin.

TRUNCATE `users`

Click yes, wada wada.

Step 2:

And then,

ALTER TABLE `users`  AUTO_INCREMENT = 0;

 

Please note: Step 2 is NOT advised.

Truncate should automatically set auto increment to 0. So 2 is not needed.

[Spudinski]

Truncate should automatically set auto increment to 0. So 2 is not needed.

It doesn't.

 

Truncate ...

I do hope this isn't a working system.

I pick fun at people who are literate enough to ask for help, but fail to research their question.

[bluegman991]

@spudinski

I just created a table and deleted some rows from it then truncated it and surely enough it reset auto increment to 0

 

@op

If you do not want to reset everyones id. Then each time a user registers you should scan through the users table and check for gaps and a sign new users, id #'s that are in the gap.

If you do not mind resetting everyones id. Then back up the table, then copy / paste the sql into the query textarea of phpmyadmin (or whatever mysql manager you may be using).

Before running query. Remove any create table query's, remove query were auto increment is set, and remove part of query where id is set.

[Spudinski]

@spudinski

I just created a table and deleted some rows from it then truncated it and surely enough it reset auto increment to 0

 

@op

If you do not want to reset everyones id. Then each time a user registers you should scan through the users table and check for gaps and a sign new users, id #'s that are in the gap.

If you do not mind resetting everyones id. Then back up the table, then copy / paste the sql into the query textarea of phpmyadmin (or whatever mysql manager you may be using).

Before running query. Remove any create table query's, remove query were auto increment is set, and remove part of query where id is set.

MySQL trigger on INSERT, that should do.

[sniko]

@spudinski

I just created a table and deleted some rows from it then truncated it and surely enough it reset auto increment to 0

You didn't delete anything from the table [which OP did], which would make the AUTO INCREMENT not 0

[Spudinski]

You didn't delete anything from the table [which OP did], which would make the AUTO INCREMENT not 0

Bah, MySQL Ref. says it should revert AUTO_INCREMENT to 1 with TRUNCATE syntax.

Never did for me, but ah well. I actually plan to keep my data integrity.

[bluegman991]

You didn't delete anything from the table [which OP did], which would make the AUTO INCREMENT not 0

I did delete something from the table didn't you see the part where it said "and deleted some rows".

Besides I tried it both ways (with and without deleting rows) and it still reset auto increment to 0. Try it out for yourself.

Here's proof that it resets it to 0.

Quoted from http://dev.mysql.com/doc/refman/5.0/en/truncate-table.html

(When fast truncation is used, it resets any AUTO_INCREMENT counter to zero. From MySQL 5.0.13 on, the AUTO_INCREMENTcounter is reset to zero by TRUNCATE TABLE, regardless of whether there is a foreign key constraint.)

^^The above quote is to show since which version mysql has been resetting auto increment to 0.

Here is the same thing quoted from the latest version http://dev.mysql.com/doc/refman/5.6/en/truncate-table.html

Any AUTO_INCREMENT value is reset to its start value. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.

Edited February 12, 2012 by bluegman991

[Spudinski]

I did delete something from the table didn't you see the part where it said "and deleted some rows".

Besides I tried it both ways (with and without deleting rows) and it still reset auto increment to 0. Try it out for yourself.

Here's proof that it resets it to 0.

Quoted from http://dev.mysql.com/doc/refman/5.0/en/truncate-table.html

I'm glad you dug that up, basically the same as I said.

But please, try not to quote previous versions of MySQL. Things changed, a lot.

[bluegman991]

Updated the post to quote the latest version of mysql.

[Danny696]

It doesn't.

I pick fun at people who are literate enough to ask for help, but fail to research their question.

Were they literate enough to ask the for the right help? IP instead of ID? Not like P and D are even next to each other.

P.S. In all uses I have done it has reset the auto_increment back to 0

[sniko]

@Bluegman991 - Ah, my bad. My memory proved me wrong, as I remember having to go into Operations and set it back to 0 manually