Bug #32139 Computing crc32(group_concat(...)) on a very loaded table doesn't give result ok
Submitted: 6 Nov 2007 9:11 Modified: 12 Dec 2007 9:39
Reporter: Tom Pouce Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.45 OS:Linux (ubuntu 7.10)
Assigned to: CPU Architecture:Any

[6 Nov 2007 9:11] Tom Pouce
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.
[12 Nov 2007 9:39] Valeriy Kravchuk
Thank you for a problem report. Please, send the results of:

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'\G

after getting normal and after getting incorrect results from the query.
[13 Dec 2007 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".