Hello,
I tired to create a "sandbox" installation with the current joomla (3.4) and current virtuemart (3.0.6). The virtuemart produced an error at installing:
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '251' for key 'PRIMARY'
The cause:
We use a multi master replication mysql server currently with two servers, the "auto_increment_increment" is now 10, so the new automatically generated id-s are: 1, 11, 21, 31... This setting divides the possible value range by 10 and so the tinyint data type is to small in some cases. This caused the duplicate entry error. I made some changes in the sql script (tinyint -> int, smallint -> int, mediumint -> int), and with this "hack" the installation finished without an error.
I can decrease the auto_increment_increment setting in mysql, but I am not sure, that it will be enough for every table.
My question is: Are the small integer data types (tinyint, smallint..) really necessary?
With best regards
Laszlo
p.s.: Sorry for my poor English
tinyints are used for booleans and should stay tinyints. The datatypes keep the db small and are good for the performance / ram.
The following tinyints are used as id in the install.sql (I didn't checked the whole script):
CREATE TABLE IF NOT EXISTS `#__virtuemart_adminmenuentries` (
`id` tinyint(1) unsigned NOT NULL AUTO_INCREMENT,
`module_id` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'The ID of the VM Module, this Item is assigned to',
`parent_id` tinyint(1) unsigned NOT NULL DEFAULT '0',
...
PRIMARY KEY (`id`),
...
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Administration Menu Items' AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `#__virtuemart_orderstates` (
`virtuemart_orderstate_id` tinyint(1) UNSIGNED NOT NULL AUTO_INCREMENT,
...
PRIMARY KEY (`virtuemart_orderstate_id`),
...
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='All available order statuses' AUTO_INCREMENT=1 ;