I was working on migrating Magento from 2.1.9 to 2.2.5 and tried to run this query for migrating product price data
REPLACE INTO magento_225.catalog_product_entity_decimal SELECT * FROM magento_219.catalog_product_entity_decimal;
But this issue prevented me to have it done:
ERROR 1264 (22003): Out of range value for column 'value' at row 240
I checked table definition in the source database and it gave me this output
mysql> describe magento_219.catalog_product_entity_decimal; +--------------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------------------+------+-----+---------+----------------+ | value_id | int(11) | NO | PRI | NULL | auto_increment | | attribute_id | smallint(5) unsigned | NO | MUL | 0 | | | store_id | smallint(5) unsigned | NO | MUL | 0 | | | entity_id | int(10) unsigned | NO | MUL | 0 | | | value | decimal(16,4) | YES | | NULL | | +--------------+----------------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
I also checked table definition in the destination database and I have
mysql> describe vmax.catalog_product_entity_decimal; +--------------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------------------+------+-----+---------+----------------+ | value_id | int(11) | NO | PRI | NULL | auto_increment | | attribute_id | smallint(5) unsigned | NO | MUL | 0 | | | store_id | smallint(5) unsigned | NO | MUL | 0 | | | entity_id | int(10) unsigned | NO | MUL | 0 | | | value | decimal(12,4) | YES | | NULL | | +--------------+----------------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
Well, I had to update the value column value to decimal(16,4) to have the same data type in both databases
ALTER TABLE magento_225.catalog_product_entity_decimal CHANGE `value` `value` DECIMAL(16,4) NULL DEFAULT NULL COMMENT 'Value';
and finally, I ran the above query successfully and product price was migrated successfully.