»MYSQL errors...what now?«
|
|||
 I have two errors showing up on my MYSQL and I don't know what to do as I am not to familiar with MYSQL. (using mymysql) I did a CHECK of my database and got the following at the bottom: Problems with indexes of table `phpbb_attachments` More than one INDEX key was created for column `attach_id` Problems with indexes of table `phpbb_sessions` PRIMARY and INDEX keys should not both be set for column `session_id` I checked off these tables and clicked REPAIR. The result was status OK. However, If I CHECK the database again, the same errors show up? Any suggestions on what I should do now? I don't exactly know what these errors mean... posted by Sippenhaft |
|||
|
|||
| in-my-opinion.orgTechnology, Computers, Science, InternetComputers and InternetMYSQL errors...what now? |
|
|||
 Sippenhaft: Any suggestions on what I should do now? I don't exactly know what these errors mean... Login to phpMyAdmin and fix it manually. It shouldn't be to serious if you keep your DB unrepaired, but it may slow down things or make backups/restores impossible. posted by knn |
|||
|
|||
|
|||
|
Quote: Login to phpMyAdmin and fix it manually. The $1,000,000 question...HOW? 
posted by Sippenhaft |
|||
|
|||
|
|||
 Sippenhaft: Quote: Login to phpMyAdmin and fix it manually. The $1,000,000 question...HOW? Can you log into phpmyadmin? Once in there, go to the first table `phpbb_attachments`. On that page, you should see a section near the top middle there listing indexes/keys. Sounds like there is a duplicate index on the same field there that is causing the problem. You should be able to click on the delete button for that index. for the 2nd table, you just need to delete the index for that field. It already has a primary key and that in-and-of-itself is an index. Let us know if that helps at all... posted by volonteshiva |
|||
|
|||
|
|||
|
See here: home.comcast.net… [PNG] home.comcast.net… [PNG] Could these duplicates be cause by more than one area of the forums want similar info and it is not a bad error? posted by Sippenhaft |
|||
|
|||
|
|||
|
Sippenhaft: Could these duplicates be cause by more than one area of the forums want similar info and it is not a bad error? did you set up these keys/indexes or were they set up by the system/developers ahead of time? On the first table, phpbb_attachments... There is no primary key set up for this table. Potential first issue. 2nd, fields post_id and privmsgs_id also appear 2x in the indexes table. I think that even if you fixed the attach_id duplication, it would still give you errors on the other 2 being duplicated. If attach_id is the unique indentifying field for this table, then it alone should be a primary key. Then an index should be created for post_id and then an index created for privmsgs_id. Thus you would have 3 records in this indexes section and no duplications. On the 2nd table, phpbb_sessions... You should just deleted the 3rd index there, 'session_id_ip_user_id' and add an index for the session_ip field. All pages that want to hit these tables would make use of the indexes if you are querying the table based on those fields. Thus the indexes are for any and all interfaces that query them. Different interfaces wouldn't need different index setups. I mean if you had an interface that did a search on session_admin, you would just add an index on just that field. I don't see how these couldn't be "bad errors" since the indexes are set up wrong and it prevents your tables from being queried efficiently and speedily. posted by volonteshiva |
|||
|
|||
|
|||
|
These where set up automatically when I added the mods and such and set up the board. I am very MYSQL illiterate. I think I am following your post above. Is there a way to make backup copies of the two tables in question and rename them or something, then edit the original tables to see if the problem gets resolved and doesnt cause any issues with the fourms?...and if so - HOW? 
I know Its kinda a pain, but could you be very specific how to edit those tables (using phpmyadmin) THANK YOU VERY MUCH for your help...I am learning 
posted by Sippenhaft |
|||
|
|||
|
|||
|
On a side note: can you explain what is going on in those tables? You said the tables are not effiecient, what is happening in those tables...the way I think I am understanding..LOL..is that there is some redundancy happening when it doesnt need to be? Thanks again. Steve posted by Sippenhaft |
|||
|
|||
|
|||
|
Sippenhaft: I am very MYSQL illiterate. I think I am following your post above.
Is there a way to make backup copies of the two tables in question and rename them or something, then edit the original tables to see if the problem gets resolved and doesnt cause any issues with the fourms?...and if so - HOW? I learned something new, I guess To copy a table, go to the table. Then click on the "Operations" tab. From there you should see a "Copy table to". Just put in your table w/ _bkup on the end or something and there you go. Sippenhaft: I know Its kinda a pain, but could you be very specific how to edit those tables (using phpmyadmin) To delete one of the indexes, just click on the "X" next to it. To add an index... In your list of fields... On the right side under Actions... There is a lightning bolt icon next to a key. Hover over it and it says index. Just click on that icon for the field you want and it will create an index on it. posted by volonteshiva |
|||
|
|||
|
|||
|
Sippenhaft: On a side note: can you explain what is going on in those tables? You said the tables are not effiecient, what is happening in those tables...the way I think I am understanding..LOL..is that there is some redundancy happening when it doesnt need to be? Well basically an index is a b-tree which allows for fast retrieval of information from that field's list of values. W/o the index it has to do a linear search. Thus if there are 2 million records and your value was at the end... Knn, please correct me if you see something wrong w/ these statements. So, if there are duplicate indexes going on, it's hard to tell what the system is doing. Likely it is still using the first index/key that was created and the 2nd isn't active. Regardless of that it's just sloppy looking when tables aren't cleanly set up. 
Simple enough fix here though. Just make sure there is one index for each field you want indexed. Since they tables are likely small, you can add and delete indexes all day long. posted by volonteshiva |
|||
|
|||
|
|||
|
Thank you for the info. I am still weary on weather or not Im going to fix it or leave it as of now since it doenst seem to be adversly effecting the board. I probably will fix it in the near future (or at least do what you said above to clean it up) when I have some extra time to do it. I will let you know how it turns out...and thank you again! posted by Sippenhaft |
|||
|
|||
|
|||
|
Sippenhaft: These where set up automatically when I added the mods and such and set up the board. I might add, that I just checked my other forums and they have the same oddity (= errors). phpBB installs itself that way. posted by knn |
|||
|
|||
|
|||
|
volonteshiva: Well basically an index is a b-tree which allows for fast retrieval of information from that field's list of values. W/o the index it has to do a linear search. Thus if there are 2 million records and your value was at the end... Knn, please correct me if you see something wrong w/ these statements. See  posted by knn |
|||
|
|
|||
|
|||
|
Let me also add that indexes are merely to speed up database queries, thus you can safely delete and create indexes as you wish posted by knn |
|||
|
|||
|
|||
|
Quote: I might add, that I just checked my other forums and they have the same oddity (= errors).
phpBB installs itself that way. Well at least its not just me 
Thanks gents! posted by Sippenhaft |
|||
|
|||
 Register Log in |
The time now is 21 November 2008, 04:34 php B.B. |