[SOLVED] virtuemart_order_histories - Error 1075/1062 - auto-increment, primary

[antifragile]

Hi, after having upgraded from VM2.6 to 3 I am getting the error "1075 Incorrect table definition; there can be only one auto column and it must be defined as a key SQL=ALTER TABLE `yagdev_virtuemart_order_histories` CHANGE COLUMN `virtuemart_order_history_id` `virtuemart_order_history_id` INT(1) UNSIGNED NOT NULL AUTO_INCREMENT FIRST"

I guess it is a problem with the Auto Increment in the table. When trying to solve this I am then getting a 1062 error with a mention of a duplicate primary key. I have tried to change settings via PHPMyAdmin and via the SQL field.

I have already searched for this problem but I am finding things like "you need to completely rebuild the table" (which I hope I can avoid) to hints to replace part of the table structure in SQL.

The table structure is:


-- Tabellenstruktur für Tabelle `yagdev_virtuemart_order_histories`
--

CREATE TABLE IF NOT EXISTS `yagdev_virtuemart_order_histories` (
`virtuemart_order_history_id` int(1) unsigned DEFAULT NULL,
`virtuemart_order_id` int(1) unsigned NOT NULL DEFAULT '0',
`order_status_code` char(1) NOT NULL DEFAULT '0',
`customer_notified` tinyint(1) NOT NULL DEFAULT '0',
`comments` varchar(21000) DEFAULT NULL,
`published` tinyint(1) NOT NULL DEFAULT '1',
`created_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`created_by` int(1) NOT NULL DEFAULT '0',
`modified_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified_by` int(1) NOT NULL DEFAULT '0',
`locked_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`locked_by` int(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


A Google search yielded that there should be a mention of auto_increment, Unique und Primary Key. Here an example from another table.

`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`memberid` VARCHAR( 30 ) NOT NULL ,
`Time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`firstname` VARCHAR( 50 ) NULL ,
`lastname` VARCHAR( 50 ) NULL ,
UNIQUE (memberid),
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I tried all kinds of combinations but I am always getting a "1062 - Duplicate entry '0' for key 'PRIMARY'" error.

Would someone be please so kind and posts his table structure? Would it be as easy to simply replace that and upload it via SQL?

Many thanks and regards,

Dan



VM 3.0.12
Joomla 3.4.8
PHP 5.6
MySQL 5.5

[Milbo]

Sometimes user have this problem. I am not sure how the problem is created. But I solved the problem itself.

So somehow sometimes tables lose the Primary key and the tableupdater either leaves the table with an autoincrement, but without primary key, or it tries to add a columns as autoinrement, but is missing the primary key, so it cant.

the vm3.0.13, which we upload as testversion today has an enhanced tableupdater, which has a workaround for this problem. Again, I  dont know how the tables get broken, but it shouldnt be a problem anylonger. So just wait a bit and you can download the last version, it is well tested and runs already on our store.

btw:
The table definition in our install.sql is

Code Select Expand


CREATE TABLE IF NOT EXISTS `#__virtuemart_order_histories` (
  `virtuemart_order_history_id` INT(1) UNSIGNED NOT NULL AUTO_INCREMENT,
  `virtuemart_order_id` int(1) UNSIGNED NOT NULL DEFAULT '0',
  `order_status_code` char(1) NOT NULL DEFAULT '0',
  `customer_notified` tinyint(1) NOT NULL DEFAULT '0',
  `comments` varchar(21000),
  `published` tinyint(1) NOT NULL DEFAULT '1',
  `created_on` datetime NOT NULL default '0000-00-00 00:00:00',
  `created_by` int(1) NOT NULL DEFAULT '0',
  `modified_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified_by` int(1) NOT NULL DEFAULT '0',
  `locked_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `locked_by` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`virtuemart_order_history_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='Stores all actions and changes that occur to an order' AUTO_INCREMENT=1 ;


[Milbo]

http://dev.virtuemart.net/attachments/download/985/com_virtuemart.3.0.13_extract_first.zip

[antifragile]

Milbo, many thanks!

Unfortunately, at least in my case, I am now getting the alternative error message "Incorrect table definition; there can be only one auto column and it must be defined as a key SQL=ALTER TABLE `yagdev_virtuemart_calcs` DROP PRIMARY KEY;"

So now a different table "XX_virtuemart_calcs" is now a culprit. This seems to have worked in older installations.

(To remind, before the error was "1075 Incorrect table definition; there can be only one auto column and it must be defined as a key SQL=ALTER TABLE `yagdev_virtuemart_order_histories` CHANGE COLUMN `virtuemart_order_history_id` `virtuemart_order_history_id` INT(1) UNSIGNED NOT NULL AUTO_INCREMENT FIRST"

But perhaps we are now closer to a solution? How would I best drop the primary key in "XX_virtuemart_calcs"?


-- Tabellenstruktur für Tabelle `yagdev_virtuemart_calcs`
--

CREATE TABLE IF NOT EXISTS `yagdev_virtuemart_calcs` (
  `virtuemart_calc_id` smallint(1) unsigned NOT NULL AUTO_INCREMENT,
  `virtuemart_vendor_id` smallint(1) unsigned NOT NULL DEFAULT '1' COMMENT 'Belongs to vendor',
  `calc_jplugin_id` int(1) NOT NULL DEFAULT '0',
  `calc_name` varchar(64) NOT NULL DEFAULT '' COMMENT 'NAME OF THE RULE',
  `calc_descr` varchar(128) NOT NULL DEFAULT '' COMMENT 'DESCRIPTION',
  `calc_kind` varchar(16) NOT NULL DEFAULT '' COMMENT 'DISCOUNT/TAX/MARGIN/COMMISSION',
  `calc_value_mathop` varchar( NOT NULL DEFAULT '' COMMENT 'THE MATHEMATICAL OPERATION LIKE (+,-,+%,-%)',
  `calc_value` decimal(10,4) NOT NULL DEFAULT '0.0000' COMMENT 'The Amount',
  `calc_currency` smallint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Currency of the Rule',
  `calc_shopper_published` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Visible for Shoppers',
  `calc_vendor_published` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Visible for Vendors',
  `publish_up` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Startdate if nothing is set = permanent',
  `publish_down` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Enddate if nothing is set = permanent',
  `for_override` tinyint(1) NOT NULL DEFAULT '0',
  `calc_params` varchar(18000) NOT NULL DEFAULT '',
  `ordering` int(1) NOT NULL DEFAULT '0',
  `shared` tinyint(1) NOT NULL DEFAULT '0',
  `published` tinyint(1) NOT NULL DEFAULT '1',
  `created_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `created_by` int(1) NOT NULL DEFAULT '0',
  `modified_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified_by` int(1) NOT NULL DEFAULT '0',
  `locked_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `locked_by` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`virtuemart_calc_id`),
  KEY `virtuemart_vendor_id` (`virtuemart_vendor_id`),
  KEY `published` (`published`),
  KEY `calc_kind` (`calc_kind`),
  KEY `shared` (`shared`),
  KEY `publish_up` (`publish_up`),
  KEY `publish_down` (`publish_down`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=20 ;


Odd is that it looked almost in my older ad working installation. I can only spot a different order in the KEYS.


-- Tabellenstruktur für Tabelle `hp12_virtuemart_calcs`
--

CREATE TABLE IF NOT EXISTS `hp12_virtuemart_calcs` (
  `virtuemart_calc_id` smallint(1) unsigned NOT NULL AUTO_INCREMENT,
  `virtuemart_vendor_id` smallint(1) unsigned NOT NULL DEFAULT '1' COMMENT 'Belongs to vendor',
  `calc_jplugin_id` int(1) NOT NULL DEFAULT '0',
  `calc_name` varchar(64) NOT NULL DEFAULT '' COMMENT 'NAME OF THE RULE',
  `calc_descr` varchar(128) NOT NULL DEFAULT '' COMMENT 'DESCRIPTION',
  `calc_kind` varchar(16) NOT NULL DEFAULT '' COMMENT 'DISCOUNT/TAX/MARGIN/COMMISSION',
  `calc_value_mathop` varchar( NOT NULL DEFAULT '' COMMENT 'THE MATHEMATICAL OPERATION LIKE (+,-,+%,-%)',
  `calc_value` decimal(10,4) NOT NULL DEFAULT '0.0000' COMMENT 'The Amount',
  `calc_currency` smallint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Currency of the Rule',
  `calc_shopper_published` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Visible for Shoppers',
  `calc_vendor_published` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Visible for Vendors',
  `publish_up` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Startdate if nothing is set = permanent',
  `publish_down` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Enddate if nothing is set = permanent',
  `for_override` tinyint(1) NOT NULL DEFAULT '0',
  `calc_params` varchar(18000) NOT NULL DEFAULT '',
  `ordering` int(1) NOT NULL DEFAULT '0',
  `shared` tinyint(1) NOT NULL DEFAULT '0',
  `published` tinyint(1) NOT NULL DEFAULT '1',
  `created_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `created_by` int(1) NOT NULL DEFAULT '0',
  `modified_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified_by` int(1) NOT NULL DEFAULT '0',
  `locked_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `locked_by` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`virtuemart_calc_id`),
  KEY `publish_up` (`publish_up`),
  KEY `publish_down` (`publish_down`),
  KEY `virtuemart_vendor_id` (`virtuemart_vendor_id`),
  KEY `published` (`published`),
  KEY `calc_kind` (`calc_kind`),
  KEY `shared` (`shared`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=20 ;


Any ideas highly appreciated :-)

Cheers,

Dan

[Milbo]

sry, small error as described at

reupped http://dev.virtuemart.net/attachments/download/987/com_virtuemart.3.0.13_extract_first.zip

http://forum.virtuemart.net/index.php?topic=133113.msg461171#msg461171

[antifragile]

Milbo, worked perfectly! Many thanks! You are a star!

Cheers,

Dan

[Milbo]

Great :-)
Yeh from time to time, a user came up with this problem. I am happy that I fixed this 5 year old problem.

[joerg69]

Hello, I have the same Problem wirth the Update from VM t to VM 3.2.4
What can I do ?

Is ther a solution ?