MySQL ERROR 1264 out of range value

Table of Contents

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.