Autoincrement field reset unintentionally

[ChrisBattersby]

I have setup a table with an autoincrement field solely to supply an application with consecutive numbers for new accounts. The application calls a GetNewAccountNo function, which basically deletes all records from the table then inserts a new record thus supplying the latest incremented number.
This has been working flawlessly for 6 months, but suddenly the autoincrement reset to 0 and started again from there. Is there anything wrong with my approach? I am new to MySQL, and work for a small company where nobody else has access to the database, or the nous to deliberately sabotage the data.
The platform I am working on is MySQL version 5.0.45-community-nt, InnoDB tables, XP pro, Delphi 5, CoreLab MyDac data access components.

 

[Kozusnik]

I would recommend not emptying the file. Leave the data there and reference the new ID immediately after the insert with SELECT LAST_INSERT_ID(). Also, what is the size of that field? I've never seen one reset itself to 0 before unless you explicitly tell it to in the insert.

Mark

 

[ChrisBattersby]

The field is int(11)

 

[ChrisBattersby]

After further research I found some interesting info at

http://bugs/mysql.com/11946

which discusses Truncate and Delete of tables/records with autoincrement fields, and points out some documentation conflicts for various versions of MySQL. It still doesn't resolve the problem of why my application worked for 6 months or so then went awol.
I have decided to reverse the order in which I approach the problem : first insert a new record to get the next autoincremented number and then delete the first record in the table. This should always leave one record in the table and circumvent any empty table problems.

 

[ChrisBattersby]

Of course that should have been

http://bugs.mysql.com/11946

 

[Kozusnik]

It sounds like it should be doing what you don't want. It should reset the autoincrement field when truncated. Is there a reason for InnoDB, could you switch to MyISAM? I've been using ISAM tables for years without any problems.

The only advantage I know of for InnoDB tables is row level locking. Is that necessary?

At this point, I'd suggest, either don't truncate the table and use the suggested method above to get the newly written ID, or switch to MyISAM and try it again. Test first of course.

Good luck,
Mark

 

[DonQuichote]

As Kozusnik also suggested, why do you empty the table? THAT is where your problem really comes from.

 

[ChrisBattersby]

Thanks Mark and DonQuichote for your comments. As outlined in my last post I am always leaving one record in the table now. I have an aversion to storing thousands of useless records - historically due to minimum storage space 34 years ago when I started programming on old ICL mainframes, and that space was at a premium. Old habits die hard!
As to InnoDB vs MyISAM, i'm not clued up enough, yet, in MySQL to really make the decision to change. I'll do some reading up on all this asap. Thanks again for your ideas chaps.

 

[r937]

The only advantage I know of for InnoDB tables is row level locking.

there is also guaranteed data integrity


now you've heard of another





r937.com | rudy.ca