|
Joined: Dec 2003
Posts: 1,796
Pooh-Bah
|
Pooh-Bah
Joined: Dec 2003
Posts: 1,796 |
My VPS was recreated today (moved to FC5 with php5 and mysql5) and now I'm having issues with the forum - each post is written with id of '0' and then the forum can't find it. I can go in and manually change the post id to the correct number and it seems to be fine, but new topics and replies are writing post id's as '0' - any idea what gives?
I'd think a corrupted forum file would corrupt the posts table, not just write the post id to '0'. I'd also think a corrupted posts table wouldn't work if I edited the topic (works fine).
Is there a setting somewhere that says what the last post id is? Maybe that's the setting that's corrupt...
|
|
|
|
Joined: Dec 2003
Posts: 1,796
Pooh-Bah
|
Pooh-Bah
Joined: Dec 2003
Posts: 1,796 |
I'm getting this as the error with addpost.inc.php
- Duplicate entry '0' for key 1
|
|
|
|
Joined: Dec 2003
Posts: 1,796
Pooh-Bah
|
Pooh-Bah
Joined: Dec 2003
Posts: 1,796 |
Looks like it affects pm's as well.
Are my indexes screwed? I've repaired all the tables more than once from phpmyadmin.
|
|
|
|
Joined: Jun 2006
Posts: 9,242 Likes: 1
Former Developer
|
Former Developer
Joined: Jun 2006
Posts: 9,242 Likes: 1 |
It sounds like the auto_increment field is messed up. That field is supposed to automatically be populated with the next available number, always getting bigger, since it keeps trying to put it in as zero it sounds like that's the problem. I'm not sure how that can be fixed, but I'll see if I can find any info.
|
|
|
|
Joined: Dec 2003
Posts: 1,796
Pooh-Bah
|
Pooh-Bah
Joined: Dec 2003
Posts: 1,796 |
thank you Rick - I can give you access if you need to check it out.
Where would I check the auto_increment field setting? I don't see it in relation to the POST_ID column.
|
|
|
|
Joined: Dec 2003
Posts: 1,796
Pooh-Bah
|
Pooh-Bah
Joined: Dec 2003
Posts: 1,796 |
ugh, every table looks like it should be auto_increment in the id column's extra field (naturally), except that it's now gone. I have to go back and fix every table now. Any shortcut command?
|
|
|
|
Joined: Dec 2003
Posts: 1,796
Pooh-Bah
|
Pooh-Bah
Joined: Dec 2003
Posts: 1,796 |
well, maybe not every table, but 22 of 55 :smash:
|
|
|
|
Joined: Jun 2006
Posts: 16,301 Likes: 116
|
Joined: Jun 2006
Posts: 16,301 Likes: 116 |
Wow, thats quite a dandy issue allen lol
|
|
|
|
Joined: Dec 2003
Posts: 1,796
Pooh-Bah
|
Pooh-Bah
Joined: Dec 2003
Posts: 1,796 |
yes, it is for the threads forum, now I have to figure out the photopost tables :smash:
|
|
|
|
Joined: Jun 2006
Posts: 16,301 Likes: 116
|
Joined: Jun 2006
Posts: 16,301 Likes: 116 |
I'm rather curious what the hell happened lol... I don't see how the FC upgrade would have affected the MySQL schema...
|
|
|
|
Joined: Dec 2003
Posts: 1,796
Pooh-Bah
|
Pooh-Bah
Joined: Dec 2003
Posts: 1,796 |
Tell me about it - there's 21 databases on that vps I have to go through and see what's broke. Most of them I'm going to drop the tables and re-create.
|
|
|
|
Joined: Jun 2006
Posts: 9,242 Likes: 1
Former Developer
|
Former Developer
Joined: Jun 2006
Posts: 9,242 Likes: 1 |
The first thing you can try is to reset the auto_increment field to what it should be. First, you'd need to find the max id for the table. For example, the posts table.
select MAX(POST_ID) from ubbt_POSTS
That will come back with some number, say 12345. Add one to that number and use it in the following query:
ALTER TABLE ubbt_POSTS AUTO_INCREMENT=123456;
If that doesn't fix it then it might require altering the row to drop the auto_increment field and then readding it.
ALTER TABLE ubbt_POSTS CHANGE POST_ID POST_ID int(11) unsigned primary key
then:
ALTER TABLE ubbt_POSTS CHANGE POST_ID POST_ID int(11) unsigned auto_increment primary key
Even though the tables are already messed up, I'd recommend doing a backup before doing anything, just in case something happens and it makes it worse.
|
|
|
|
Joined: Dec 2003
Posts: 1,796
Pooh-Bah
|
Pooh-Bah
Joined: Dec 2003
Posts: 1,796 |
Thank you, that's what I did 44 times last night - actually with the first one I edited the column manually with phpmyadmin then just copied the resulting mysql command into ultraedit ( ) to save for the second forum I started to write an altertable script for practice, but at 1am I decided I don't need to practice that much I have to sort out photopost on those 2 sites then basically drop the other databases on the other sites and re-install. I don't think we'll be re-creating the vps any time soon again
|
|
|
|
Joined: Jun 2006
Posts: 16,301 Likes: 116
|
Joined: Jun 2006
Posts: 16,301 Likes: 116 |
"backup before upgrading" comes to mind all too clearly lol...
|
|
|
|
Bots
by Outdoorking - 04/13/2024 5:08 PM
|
|
|
1 members (Ruben),
814
guests, and
236
robots. |
Key:
Admin,
Global Mod,
Mod
|
|
|
|