Jump to content
MakeWebGames

Recommended Posts

Posted

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

Posted
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.

Posted
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.
Posted

@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.

Posted
@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.

Posted
@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

Posted
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.

Posted (edited)
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 by bluegman991
Posted
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.

Posted
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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...