dbeckerton Posted February 11, 2012 Posted February 11, 2012 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 Quote
Spudinski Posted February 11, 2012 Posted February 11, 2012 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. Quote
bluegman991 Posted February 11, 2012 Posted February 11, 2012 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. Quote
Spudinski Posted February 11, 2012 Posted February 11, 2012 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. Quote
bluegman991 Posted February 11, 2012 Posted February 11, 2012 @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. Quote
Spudinski Posted February 11, 2012 Posted February 11, 2012 @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. Quote
sniko Posted February 11, 2012 Posted February 11, 2012 @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 Quote
Spudinski Posted February 11, 2012 Posted February 11, 2012 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. Quote
bluegman991 Posted February 12, 2012 Posted February 12, 2012 (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 February 12, 2012 by bluegman991 Quote
Spudinski Posted February 12, 2012 Posted February 12, 2012 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. Quote
bluegman991 Posted February 12, 2012 Posted February 12, 2012 Updated the post to quote the latest version of mysql. Quote
Danny696 Posted February 13, 2012 Posted February 13, 2012 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 Quote
sniko Posted February 15, 2012 Posted February 15, 2012 @Bluegman991 - Ah, my bad. My memory proved me wrong, as I remember having to go into Operations and set it back to 0 manually Quote
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.