Problems with MySQL-corrupt table?

msobik

All,

I'm experiencing a problem with a MySQL database table. I have an auto increment field that I use for a primary key for photo ids. The last id in the DB is 113, but when I try to add another photo the id that MySQL chooses is 2147483647 instead of 114. The other tables with auto increment don't seem to be having any trouble and I can access the other fields in the table, no problem. When I try to add another photo after the 2147... the database won't add it and returns an id of 0. Any ideas as to what's going on? Thanks for your help.

Mike

[deleted]

Check the manual for the datatypes, you reached the max value of the INTEGER datatype.

This happens after you do 2 billion inserts, or on insert where you forced the auto_increment column to contain a the max value.

Check the manual for info on how to 'reset' the auto_incrment column.

msobik

Vincent,

Thanks so much for the help. You were right on. I had been playing with myisamchk -r, but that doesn't fix the auto-increment column. Any idea why this happened?

Mike

msobik

Sorry, forgot to post HOW I solved the problem. Run:

myisamchk -A

with or without options to fix the auto-increment problem. Check the manual for exact usage.

Mike

nedved31

msobik;617682 wrote:
All,

I'm experiencing a problem with a MySQL database table. I have an auto increment field that I use for a primary key for photo ids. The last id in the DB is 113, but when I try to add another photo the id that MySQL chooses is 2147483647 instead of 114. The other tables with auto increment don't seem to be having any trouble and I can access the other fields in the table, no problem. When I try to add another photo after the 2147... the database won't add it and returns an id of 0. Any ideas as to what's going on? Thanks for your help.

Mike

For this complicated situation you may use sql recovery. The program works under low system requirements and using modern methods of recovering mdf info. It has intuitive and clear interface for any users.