beehexa integrationsidebar
beehexa logo

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

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.

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.