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
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.