What to do if you lose your AUTO_INCREMENT properties on your Joomla Database Tables.

It has been reported to us twice over Christmas that a certain backup and restore component for Joomla is “cloning” Joomla databases without reinstating all the properties of the databases’ primary keys correctly.

You will know when you have this problem as you will not be able to add any NEW content to your Joomla site and you may get error messages about duplicate primary keys.

If you take a look at your jos_content table, check the ID field and see if auto_increment is a property of the field - if not then you have lost all your auto_increments.

I have compiled a short list of SQL commands to reinstate the correct primary keys and properties based on joomla.sql, the default joomla installation SQL. This will NOT fix 3rd party components tables which must be done manually.

The following SQL Commands are applicable to Joomla 1.0.x only.

ALTER TABLE `#__banner`
CHANGE `bid` `bid` INT( 11 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE `#__bannerclient`
CHANGE `cid` `cid` INT( 11 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE `#__bannerfinish`
CHANGE `bid` `bid` INT( 11 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE `#__categories`
CHANGE `id` `id` INT( 11 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE `#__components`
CHANGE `id` `id` INT( 11 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE `#__contact_details`
CHANGE `id` `id` INT( 11 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE `#__content`
CHANGE `id` `id` INT( 11 ) unsigned NOT NULL AUTO_INCREMENT;
ALTER TABLE `#__mambots`
CHANGE `id` `id` INT( 11 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE `#__messages`
CHANGE `message_id` `message_id` INT( 10 ) unsigned NOT NULL AUTO_INCREMENT;
ALTER TABLE `#__modules`
CHANGE `id` `id` INT( 11 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE `#__newsfeeds`
CHANGE `id` `id` INT( 11 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE `#__poll_data`
CHANGE `id` `id` INT( 11 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE `#__poll_date`
CHANGE `id` `id` bigint(20) NOT NULL AUTO_INCREMENT;
ALTER TABLE `#__polls`
CHANGE `id` `id` int(11) unsigned NOT NULL AUTO_INCREMENT;
ALTER TABLE `#__sections`
CHANGE `id` `id` INT( 11 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE `#__template_positions` CHANGE `id` `id` INT( 11 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE `#__users`
CHANGE `id` `id` INT( 11 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE `#__menu`
CHANGE `id` `id` INT( 11 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE `#__weblinks`
CHANGE `id` `id` INT( 11 ) unsigned NOT NULL AUTO_INCREMENT;
ALTER TABLE `#__core_acl_aro`
CHANGE `aro_id` `aro_id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `#__core_acl_aro_groups`
CHANGE `group_id` `group_id` INT( 11 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE `#__core_acl_aro_sections`
CHANGE `section_id` `section_id` INT( 11 ) NOT NULL AUTO_INCREMENT;