If you do a search of the Xoops forums you'll find pages upon pages of users reporting that they are getting a "Could not insert forum post" error when trying to submit posts to their forums. Most are told that it probably is a firewall issue relating to the blocking of HTTP referers, and others also advise to do a CHECK and REPAIR on their MySQL Database. Seems sound advice, and in a lot of instances these approaches will work. I never thought anything of these reported issues before as I had never experienced them first hand. Last week that changed though as I went to reply to a post on my forum and the dreaded "Could not insert forum post" message appeared. I followed all the advice I could find about firewalls and database maintainence from the Xoops forums, but the problem persisted. The solution I found was totally different to anything that I could find mentioned before. Once I realised that this was not going to be a quick fix I decided to post an article in AMS to notify users that the forums would be down for a while, but to my suprise I was given an error when submitting. The article showed up ok, but when you clicked on the "read more" link I was given an error that the article did not exist. I went to the AMS management console and opened the "manage articles" panel. What I found was that my article had been submitted, but the storyid field had been given a vaule of 0. This was obviously not correct so using PhpMyAdmin I manually updated the storyid field to the next article number in line in both the ams_article and ams_text tables, after which the article displayed without a problem. I also did the same thing with the post_id field in the bb_posts and bb_posts_text tables. However, on subsequent attempts to create a new forum post or AMS article these fields would again present themselves with an invalid value of 0. This didn't make sense to me as this field was an auto_increment field, and MySQL was meant to increase this value by one for each new record added. I turned on MySQL debug in Xoops and tried submitting a forum post, and sure enough there was a MySQL INSERT statement trying to create a new record with a post_id value of 0. This seemed vaguely familiar, and then I remembered that I had seen very similar invalid SQL once or twice before on the Xoops forums. The one thing that all these invalid SQL queries had in common was that any value that was meant to be using the auto_increment function was being submitted to the database as a 0 as can be seen in the example below taken from the Xoops forums.

Finally the penny dropped and I logged back into PhpMyAdmin, but this time instead of looking at the data of in the storyid and post_id fields, I looked at the field structure. Sure enough the the auto_increment value was not set under the Extra column of either field. I set this Extra value back to auto_increment for the storyid and post_id fields in the ams_article, ams_text, bb_posts and bb_posts_text tables and then tried submitting a new article and a new post. This time around everything worked as it should, no problems what so ever. This left me with the question of how did this auto_increment value get cleared, and most importantly how was I going to be able to reset this value to what it should be for every field that needed it in my sites database? After some investigation I found that when I setup my site on my server a few weeks after a migration I had set MySQL Administrator to do schedueled backups, and for some reason I had clicked the compatibility mode option. What I didn't realise then was that this option actually made the backups compatible with MySQL version 3.23 which in turn removes the auto_increment setting from the SQL. Last week I had some trouble with my main server and had to shift my main database off to my test server, and when I imported the database onto the test server it was from a backup with this compatibility mode set. Now I knew why it happened, I was now faced with how to resolve the issue with very limited MySQL syntax knowledge. What I did in the end was I logged into PhpMyAdmin and made a data only backup, i.e I unticked the structure tickbox when exporting. I then dropped all my Xoops tables and restored it using the last backup I had of my site that wasn't done using the compatibility mode which was about 5 weeks old which restored my database structure. I then used PhpMyAdmin again to empty all these tables, and then restored the database I made earlier which contained data only (no structure). I then deleted any records in the ams_article, ams_text, bb_posts and bb_posts_text tables that had the invalid data values of 0. End result was I was able to get my table structure restored and retained all my data, but I was very lucky in that I have not spent much time on my site over the last few weeks creating content. The other lucky thing (if you can call it that) is that as the auto_increment values were always being submitted as 0, they could only be submitted to each table once as any other attempt after that would fail as it was seen as a duplicate record. The only really time consuming part of this process was tracking down why it was happening, and then scouring tables for invalid records after the restore. Ideally what you would do is restore you database structure, then populate that with you most up to date data backup, and then finally write and SQL query to find all fields with the auto_increment value set and delete any data in those fields that was equal to 0. Unfortunately my MySQL knowledge is not up to that standard as of yet. Still, all is well that ends well!