cancel
Showing results for 
Search instead for 
Did you mean: 

Database Error Magento 2.2.x Upgrade to 2.3.6

Database Error Magento 2.2.x Upgrade to 2.3.6

I am trying to upgrade Magento 2.2.7 to 2.3.6. After composer Update and checking php bin/magento -V it is showing magento CLI 2.3.6

I am using PHP 7.3 MySQL 5.7

When I try to run php bin/magento s:up shows below error

I have removed values from the table quote_address, since values were not available inside the quote table, using the below query. But still issue exist

DELETE FROM quote_address WHERE quote_id not in (select entity_id from quote);
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`db`.`#sql-1cd8_4dc1e`, CONSTRAINT `QUOTE_ADDRESS_QUOTE_ID_QUOTE_ENTITY_ID` FOREIGN KEY (`quote_id`) REFERENCES `quote` (`entity_id`) ON DELETE CASCADE), query was: 
ALTER TABLE `quote_address` MODIFY COLUMN `address_id` int(10) UNSIGNED NOT NULL  AUTO_INCREMENT COMMENT "Address ID", MODIFY COLUMN `quote_id` int(10) UNSIGNED NOT NULL DEFAULT 0  COMMENT "Quote ID", MODIFY COLUMN `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT "Updated At", MODIFY COLUMN `customer_id` int(10) UNSIGNED NULL   COMMENT "Customer ID", MODIFY COLUMN `customer_address_id` int(10) UNSIGNED NULL   COMMENT "Customer Address ID", MODIFY COLUMN `region_id` int(10) UNSIGNED NULL   COMMENT "Region ID", MODIFY COLUMN `country_id` varchar(30) NULL  COMMENT "Country ID", MODIFY COLUMN `subtotal` decimal(20, 4)  NOT NULL DEFAULT 0 COMMENT "Subtotal", MODIFY COLUMN `base_subtotal` decimal(20, 4)  NOT NULL DEFAULT 0 COMMENT "Base Subtotal", MODIFY COLUMN `subtotal_with_discount` decimal(20, 4)  NOT NULL DEFAULT 0 COMMENT "Subtotal With Discount", MODIFY COLUMN `base_subtotal_with_discount` decimal(20, 4)  NOT NULL DEFAULT 0 COMMENT "Base Subtotal With Discount", MODIFY COLUMN `tax_amount` decimal(20, 4)  NOT NULL DEFAULT 0 COMMENT "Tax Amount", MODIFY COLUMN `base_tax_amount` decimal(20, 4)  NOT NULL DEFAULT 0 COMMENT "Base Tax Amount", MODIFY COLUMN `shipping_amount` decimal(20, 4)  NOT NULL DEFAULT 0 COMMENT "Shipping Amount", MODIFY COLUMN `base_shipping_amount` decimal(20, 4)  NOT NULL DEFAULT 0 COMMENT "Base Shipping Amount", MODIFY COLUMN `shipping_tax_amount` decimal(20, 4)  NULL  COMMENT "Shipping Tax Amount", MODIFY COLUMN `base_shipping_tax_amount` decimal(20, 4)  NULL  COMMENT "Base Shipping Tax Amount", MODIFY COLUMN `discount_amount` decimal(20, 4)  NOT NULL DEFAULT 0 COMMENT "Discount Amount", MODIFY COLUMN `base_discount_amount` decimal(20, 4)  NOT NULL DEFAULT 0 COMMENT "Base Discount Amount", MODIFY COLUMN `grand_total` decimal(20, 4)  NOT NULL DEFAULT 0 COMMENT "Grand Total", MODIFY COLUMN `base_grand_total` decimal(20, 4)  NOT NULL DEFAULT 0 COMMENT "Base Grand Total", MODIFY COLUMN `shipping_discount_amount` decimal(20, 4)  NULL  COMMENT "Shipping Discount Amount", MODIFY COLUMN `base_shipping_discount_amount` decimal(20, 4)  NULL  COMMENT "Base Shipping Discount Amount", MODIFY COLUMN `subtotal_incl_tax` decimal(20, 4)  NULL  COMMENT "Subtotal Incl Tax", MODIFY COLUMN `base_subtotal_total_incl_tax` decimal(20, 4)  NULL  COMMENT "Base Subtotal Total Incl Tax", MODIFY COLUMN `discount_tax_compensation_amount` decimal(20, 4)  NULL  COMMENT "Discount Tax Compensation Amount", MODIFY COLUMN `base_discount_tax_compensation_amount` decimal(20, 4)  NULL  COMMENT "Base Discount Tax Compensation Amount", MODIFY COLUMN `shipping_discount_tax_compensation_amount` decimal(20, 4)  NULL  COMMENT "Shipping Discount Tax Compensation Amount", MODIFY COLUMN `base_shipping_discount_tax_compensation_amnt` decimal(20, 4)  NULL  COMMENT "Base Shipping Discount Tax Compensation Amount", MODIFY COLUMN `shipping_incl_tax` decimal(20, 4)  NULL  COMMENT "Shipping Incl Tax", MODIFY COLUMN `base_shipping_incl_tax` decimal(20, 4)  NULL  COMMENT "Base Shipping Incl Tax", MODIFY COLUMN `vat_id` text NULL COMMENT "Vat ID", MODIFY COLUMN `vat_request_id` text NULL COMMENT "Vat Request ID", MODIFY COLUMN `gift_message_id` int(11)  NULL   COMMENT "Gift Message ID", ADD COLUMN `validated_country_code` text NULL COMMENT "Validated Country Code", ADD COLUMN `validated_vat_number` text NULL COMMENT "Validated Vat Number", ADD CONSTRAINT `QUOTE_ADDRESS_QUOTE_ID_QUOTE_ENTITY_ID` FOREIGN KEY (`quote_id`) REFERENCES `quote` (`entity_id`)  ON DELETE CASCADE
1 REPLY 1

Re: Database Error Magento 2.2.x Upgrade to 2.3.6

If your new database is absolutely empty of tables and you're getting this on import, you have duplicate keyed rows in one of the tables you're trying to import which fails the foreign key check.

It is recommended and would not hurt to run Magento's database repair tool on the database to correct this issue.

Download Magento Database Repair Tool Version 1.1 here:

http://www.magentocommerce.com/download

Their instructions on running it:

http://www.magentocommerce.com/wiki/1_-_installation_and_configuration/db-repair-tool

The oft quoted magentocommerce.com forum recommendation by non-db admin types is to shut the foreign key checks off, hope you haven't accumulated bad karma, import the database and keep an eye out for future weirdness when it turns out there really was a duplicate foreign key.

In this case, it sounds like you've already imported the database and are finding after the fact that you have duplicated foreign keys which means the Magento database needs an integrity check.

If issue solved, Click Kudos & Accept as Solution.
LitCommerce - The Most Simple & Affordable Multi-channel Selling Tool