MySQL ERROR 1264 out of range value

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.  

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.