I have recently transferred one of my websites to a new server box. This is with the same hosting company though. My previous server was using an NT system and the upgraded one is using Windows 2003 Server. My web site is using Joomla as a CMS and SMF for forums. I have also set up the Joomla-SMF bridge. The transfer was with obvious hiccups. In this blog I like to point out the one issue that I had with the databases.
I was having this error “Database Error: Duplicate entry ‘0′ for key 1″ whenever I tried to create new user or new topics in my forum. Two of my databases, one for Joomla and the other for SMF, were based on MySQL.
After a little googling and browsing through php codes for the page that were generating the error I came up with the solution. The problem was with the export and import of the MySQL database. Understandably, the database import didn’t set the “AUTO_INCREMENT” property of key fields of many tables of the databases. I manually had to set this property using phpMyAdmin. You can set this property in the “Extra” attribute of the field once you display the table structure.
So i did a comparison with my old server installation for both Joomla and SMF database and came up with the following list of table and associated key fields that are using auto_increment attribute.
Joomla Database (version 1.12)
Following are the Joomla tables and corresponding key fields that are using auto_increment attribute.
| Joomla Table | Key Field |
| jos_banner | bid |
| jos_bannerclient | cid |
| jos_bannerfinish | bid |
| jos_categories | id |
| jos_components | id |
| jos_contact_details | id |
| jos_content | id |
| jos_core_acl_aro | aro_id |
| jos_core_acl_aro_groups | group_id |
| jos_core_acl_aro_sections | section_id |
| jos_downloads_blob | id |
| jos_downloads_containers | id |
| jos_downloads_files | id |
| jos_downloads_log | id |
| jos_downloads_reviews | id |
| jos_downloads_structure | id |
| jos_downloads_temp | id |
| jos_downloads_text | id |
| jos_mambots | id |
| jos_mbt_groups | group_id |
| jos_menu | id |
| jos_message | message_id |
| jos_modules | id |
| jos_newsfeeds | id (not catid) |
| jos_polls | id |
| jos_poll_data | id |
| jos_poll_date | id |
| jos_sections | id |
| jos_template_positions | id |
| jos_users | id |
| jos_weblinks | id |
SMF Database (version 1.1.4)
Following are the SMF tables and corresponding key fields that are using autoincrement attribute.
| SMF Table | Key Field |
| smf_ads | ADS_ID |
| smf_attachments | ID_ATTACH |
| smf_ban_groups | ID_BAN_GROUP |
| smf_ban_items | ID_BAN |
| smf_boards | ID_BOARD |
| smf_calendar | ID_EVENT |
| smf_calendar_holidays | ID_HOLIDAY |
| smf_categories | ID_CAT |
| smf_log_actions | ID_ACTION |
| smf_log_banned | ID_BAN_LOG |
| smf_log_errors | ID_ERROR |
| smf_membergroups | ID_GROUP |
| smf_members | ID_MEMBER |
| smf_messages | ID_MSG |
| smf_message_icons | ID_ICON |
| smf_package_servers | ID_SERVER |
| smf_personal_messages | ID_PM |
| smf_polls | ID_POLL |
| smf_smileys | ID_SMILEY |
| smf_topics | ID_TOPIC |
| smf_tpv_chat | ID |
So, make sure that you manually restore the “auto increment” property after an import of the Joomla, SMF and as such where similar situation may occur. For example, you will face similar problem while transferring WordPress, Drupal, PhpBB, and many other web content managers from one machine to other.
Tags: export import, Joomla, MySQL, SMF
December 24, 2007 at 1:47 am |
Thanks for this post. I just migrated a SMF forums over and encountered this exact issue. I tried your fixes listed and voila! All is well. Thanks again for a great post and info (found it off Google).
December 24, 2007 at 8:11 am |
Thanks for informing that it helped you. Please also note that the list of tables I mentioned may differ with your list of tables depending on the Mods installed in SMF or Joomla. The list here only refers to the standard set of tables that mostly comes with vanila SMF 1.1.4 (with 1 Mod AChat installed). If you have numerous Mods installed please check for additional Mod specific tables that may require fixing of the auto_increment field.
March 17, 2008 at 11:31 pm |
Thanks for the post. I had the same issue, but I can’t seem to figure out how restore the “AUTO_INCREMENT” in phpmyadmin. I am trying find the extra attribute in the joomla database but i can’t see to find it. Sorry i’m pretty new at this.
April 4, 2008 at 4:13 am |
in myphpadmin first select the table you want to change then click on the “Structure ” Tab at the top of the myphpadmin window.
Then find the field which you want to change to auto_increment and click on the pen icon under the Actions columns. This should take to the column properties window and click on the EXTRA drops down menu and you will find auto _increment then save.
July 16, 2008 at 2:44 am |
I face similar issue with my wordpress.
I want to upgrade to the 2.6 version. But always got that error message.
I also already take a look at the auto increment field. Its already there.
Now im so blur how to fix this issue
August 11, 2008 at 9:45 pm |
Thanks so much this really helped me! I have had so many problems with this transfer and just when i think they’re all gone another one comes up. It’s like Jaws but with code… I’m also getting an error when i try to add points to peoples reward number. All it says is
process.php: BadAction = AddPurchase
I don’t know if you can help me but I can’t seem to figure out what I’m doing wrong here…
January 3, 2009 at 8:53 am |
oh my goodness you just fixed what I spent hours trying to fix. I was ready to destroy my computer. I would have never figured it out without you. Thank you!
January 5, 2009 at 3:54 pm |
Shiningphoton, thanks for informing that my one cent worth of tips did help you. I am glad and encouraged to publish more tips that I came to know after spending hours and definitely do not want that others waste their time on it.
March 23, 2009 at 7:18 pm |
Hi! Thank You very much for Your post. This post helped me to solve a problem with WordPress 2.7.1 in hostgator, which was intalled by Fantastico, and after I imported the posts from the old sever, I cant add new posts. Bests…
March 31, 2009 at 8:43 am |
Thanks for sharing your experience. I am glad it helped you.