Description:
I have two tables :
CREATE TABLE IF NOT EXISTS `jos_vm_product` (
`product_id` bigint(20) unsigned NOT NULL auto_increment,
`vendor_id` int(11) NOT NULL default '0',
`product_parent_id` bigint(20) unsigned default '0',
`product_sku` bigint(20) unsigned default '0',
`product_s_desc` varchar(255) default NULL,
`product_desc` text,
`product_thumb_image` varchar(255) default NULL,
`product_full_image` varchar(255) default NULL,
`product_publish` char(1) default NULL,
`product_weight` decimal(10,4) default NULL,
`product_weight_uom` varchar(32) default 'kg',
`product_length` decimal(10,4) default NULL,
`product_width` decimal(10,4) default NULL,
`product_height` decimal(10,4) default NULL,
`product_lwh_uom` varchar(32) default 'cm',
`product_url` varchar(255) default NULL,
`product_in_stock` int(11) default NULL,
`product_available_date` int(11) default NULL,
`product_availability` varchar(56) NOT NULL default '',
`product_special` char(1) default NULL,
`product_discount_id` int(11) default NULL,
`ship_code_id` int(11) default NULL,
`cdate` int(11) default NULL,
`mdate` int(11) default NULL,
`product_name` varchar(64) default NULL,
`product_sales` int(11) NOT NULL default '0',
`attribute` text,
`custom_attribute` text NOT NULL,
`product_tax_id` tinyint(2) NOT NULL default '0',
`product_unit` varchar(32) default NULL,
`product_packaging` int(11) default NULL,
`is_cadeau` tinyint(2) NOT NULL default '0',
PRIMARY KEY (`product_id`),
UNIQUE KEY `product_sku` (`product_sku`),
KEY `idx_product_vendor_id` (`vendor_id`),
KEY `idx_product_product_parent_id` (`product_parent_id`),
KEY `idx_product_ship_code_id` (`ship_code_id`),
KEY `idx_product_name` (`product_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='All products are stored here.' AUTO_INCREMENT=1;
AND
CREATE TABLE IF NOT EXISTS `jos_vm_product_crcs` (
`product_id` bigint(20) unsigned NOT NULL,
`CRC` int(10) unsigned NOT NULL,
PRIMARY KEY (`product_id`),
KEY `CRC` (`CRC`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Table de calcul des CRCs des produits';
AND these triggers :
CREATE TRIGGER trg_crc_products_delete BEFORE DELETE ON jos_vm_product FOR EACH ROW
DELETE FROM jos_vm_product_crcs WHERE product_id=OLD.product_id;'
'CREATE TRIGGER trg_crc_products_insert AFTER INSERT ON jos_vm_product FOR EACH ROW
INSERT INTO jos_vm_product_crcs(product_id,CRC) VALUES(NEW.product_id, crc32( concat_ws( "|", NEW.`product_id` , NEW.`vendor_id` , NEW.`product_parent_id` , NEW.`product_sku` , NEW.`product_desc` , NEW.`product_thumb_image` , NEW.`product_full_image` , NEW.`product_weight` , NEW.`product_weight_uom` , NEW.`product_length` , NEW.`product_width` , NEW.`product_height` , NEW.`product_lwh_uom` , NEW.`product_name` , NEW.`product_unit` , NEW.`product_packaging` ))) ON DUPLICATE KEY UPDATE CRC=crc32( concat_ws( "|", NEW.`product_id` , NEW.`vendor_id` , NEW.`product_parent_id` , NEW.`product_sku` , NEW.`product_desc` , NEW.`product_thumb_image` , NEW.`product_full_image` , NEW.`product_weight` , NEW.`product_weight_uom` , NEW.`product_length` , NEW.`product_width` , NEW.`product_height` , NEW.`product_lwh_uom` , NEW.`product_name` , NEW.`product_unit` , NEW.`product_packaging` ));'
'CREATE TRIGGER trg_crc_products AFTER UPDATE ON jos_vm_product FOR EACH ROW
INSERT INTO jos_vm_product_crcs(product_id,CRC) VALUES(NEW.product_id,
crc32( concat_ws( "|", NEW.`product_id` , NEW.`vendor_id` , NEW.`product_parent_id` ,
NEW.`product_sku` , NEW.`product_desc` , NEW.`product_thumb_image`, NEW.`product_full_image` ,
NEW.`product_weight` , NEW.`product_weight_uom` , NEW.`product_length` , NEW.`product_width` ,
NEW.`product_height` , NEW.`product_lwh_uom` , NEW.`product_name` , NEW.`product_unit` , NEW.`product_packaging` )))
ON DUPLICATE KEY UPDATE CRC=crc32( concat_ws( "|", NEW.`product_id` , NEW.`vendor_id` , NEW.`product_parent_id` ,
NEW.`product_sku` , NEW.`product_desc` , NEW.`product_thumb_image` , NEW.`product_full_image` , NEW.`product_weight` ,
NEW.`product_weight_uom` , NEW.`product_length` , NEW.`product_width` , NEW.`product_height` , NEW.`product_lwh_uom` ,
NEW.`product_name` , NEW.`product_unit` , NEW.`product_packaging` ));'
Here is the problem :
I insert about 100000 products in jos_vm_product. Then I run immediately :
SELECT crc32(GROUP_CONCAT(`jos_vm_product_crcs`.CRC ORDER BY product_id)) AS agent_crc FROM `jos_vm_product_crcs`
WHERE product_id<'1000000000000000000';
Sometimes, the crc is ok, sometimes not. If I run this last query manualy after the end of the process, no problem, result is ok.
I tried to lock tables, optimize them before crc (in order to rewrite them and to be sure that previous inserts are finished), ...
group_concat_max_len is set at 10240000 and the resulting blob is only about 1,2 Mb.
Any idea ?
How to repeat:
It is possible to create the these tables and triggers and then inserting about 100000 rows in jos_vm_product immediately followed by the last query.