beehexa integrationsidebar
beehexa beehexa logo 1

We Build HexaSync Integration Platform for Connecting ERP, POS, CRM, Accounting, and eCommerce Applications to Automate Business Processes

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.

Table of Contents

Ready to integrate and automate at scale ?

Learn how HexaSync lets you build enterprise-grade integrations and automations without having to code.

Receive Exclusive Productivity Tips Directly in Your Inbox

We’ll email you 1-3 times per week—and never share your information.

Get started for free

You can’t add more hours to the day. Beehexa is the next best thing.