I have a online database which I use to produce dynamic web pages, via an online search engine. All was fine pleople could upload their details on to the database, until now! I was using auto increament to give a unique id etc... The databse has 25 fields in it. When I try to insert into my database via the website form using PHP, or via my web sites control panel using phpmyadmin I am unable to do so the auto increament stops at 127 and will not go any higher, if I delete this record then it will accept the next insert but still give it a value for ID of 127!!!. The only thing that I have notice is it says that I have an overhead usually about 144bytes in my space usage, I don't have them many people in my database at 127 and my current space usage is reading a total of 53,896 bytes.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Overhead
- Thread starter theescort
- Start date
It sounds like you're using an signed tinyint for your column type, which has a max capacity of 127 positive values (255 unsigned).
But I'm assuming that you have less than 127 users, and your auto_increment value has been corrupted to max value. You can verify this at the mysql prompt with the following:
mysql> show table status like 'your_table';
To fix it, use the following from the shell:
shell>myisamchk -A /usr/local/mysql/var/your_database/your_table
(note you may have a different path to your db files than '/usr/local/mysql/')
This will set your auto_increment back to the next highest value. If the highest value is 25, it becomes 26.
Depending on how many records you expect to gather in that table, you will probably want to increase that field type to at least an unsigned smallint, which will give you 65535 records max. Using the auto increment to get a unique value means you need a lot of growing room as records are added and deleted.
HTH
Eman Erin Quick-Laughlin
DBA, PHP/MySQL programmer
But I'm assuming that you have less than 127 users, and your auto_increment value has been corrupted to max value. You can verify this at the mysql prompt with the following:
mysql> show table status like 'your_table';
To fix it, use the following from the shell:
shell>myisamchk -A /usr/local/mysql/var/your_database/your_table
(note you may have a different path to your db files than '/usr/local/mysql/')
This will set your auto_increment back to the next highest value. If the highest value is 25, it becomes 26.
Depending on how many records you expect to gather in that table, you will probably want to increase that field type to at least an unsigned smallint, which will give you 65535 records max. Using the auto increment to get a unique value means you need a lot of growing room as records are added and deleted.
HTH
Eman Erin Quick-Laughlin
DBA, PHP/MySQL programmer
- Status