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.

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email