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.
