boardom

Anavy · Joined: 29 May 2002 Posts: 58

I had to remove the last post from my Database (Ver 3.23.46). I did it from phpMyAdmin (ver 2.2.6) and removed the post from b2posts table.

Now, phpMyAdmin shows that the next Autoindex is 85 when it should be 84 (The ID of the post that was removed). Running OPTIMIZE_TABLE didn't fix the Autoindex value and I can't find a way to fix it manualy. Is there a simple way do do it?

CodeWhacker · Joined: 31 Oct 2002 Posts: 12

Don't lose any sleep over it... just leave it as it is. No harm will come from having a hole in your set of post IDs. Anyone hitting that ID with a 'p=?' query string will just see an empty spot where the post would be.

If you're really all about having things perfectly sequential, export your tables to a file from phpMyAdmin, drop all the tables, massage the Post ID fields in the dump file by hand and re-import. Probably more bother than just leaving a hole, eh?

Anavy · Joined: 29 May 2002 Posts: 58

CodeWhacker, you are probably right about it. But I know that this value is kept somewhere and I wonder how to get there. It maybe a real issue someday.

macshack · Joined: 17 Jul 2002 Posts: 1204 Location: Phoenix, Az

Hi,

auto-increment is a special beast. It is MYSQL's way of guaranteeing that simultaneous inserts do not generate duplicate keys or id. Think about the issue of user A attempting to reset the auto-increment value while user B just did an insert an incremented it. Not good. This special 'feature' of MYSQL is managed by MYSQL only. Now I may be wrong here, but I do not know of any way to reset/set the value of the "next auto-increment" value to some value or to have MYSQL to internally reset it to a value it constructs. The only way I know to address this is the dump/drop/create/load steps talked about above.

I can not think of a condition where this concern of "holes" is an issue. You can always find out the value that was generated for your last insert and then potentially use that value in some other tuple of another table. In fact this would be the precise reason not to re-order things. Reverential keys would be all messed up.

So the bottom line here is don't worry about it. And if you do, make sure you understand the schema and the relationships.

michael e

Anavy · Joined: 29 May 2002 Posts: 58

Thanks Michael for the In-depth look into that matter.

Amir