MySQL out og range value

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.

MySQL out og range value

MySQL ERROR 1264 out of range value

Join The Ride

Subscribe to our fortnightly newsletter with stories from our latest adventures and the best travel tips

Table of Contents