Not able to setup:upgrade, SQL error

[hanhoe]

Hello,

I copied from the webserver in the cloud the magento 2 to my local new webserver for working.

I install the database and all files.

Then I run composer update from 2.2.2 to get the newest 2.3.2

This was all fine.

But when I send "bin/magento setup:upgrade" i get the error:

Cache cleared successfully
File system cleanup:
/var/www/html/generated/code/Composer
/var/www/html/generated/code/Firebear
/var/www/html/generated/code/Iazel
/var/www/html/generated/code/Magento
/var/www/html/generated/code/Mageplaza
/var/www/html/generated/code/Symfony
The directory '/var/www/html/generated/metadata/' doesn't exist - skipping cleanup
/var/www/html/pub/static/adminhtml
/var/www/html/pub/static/deployed_version.txt
/var/www/html/pub/static/frontend
/var/www/html/var/view_preprocessed/pub
Updating modules:
Schema creation/updates:
SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint, query was: ALTER TABLE `quote_item` MODIFY COLUMN `item_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT "Item Id", MODIFY COLUMN `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT "Updated At", MODIFY COLUMN `discount_amount` decimal(20, 4) NULL DEFAULT 0 COMMENT "Discount Amount", MODIFY COLUMN `base_discount_amount` decimal(20, 4) NULL DEFAULT 0 COMMENT "Base Discount Amount", MODIFY COLUMN `tax_amount` decimal(20, 4) NULL DEFAULT 0 COMMENT "Tax Amount", MODIFY COLUMN `base_tax_amount` decimal(20, 4) NULL DEFAULT 0 COMMENT "Base Tax Amount", MODIFY COLUMN `row_total` decimal(20, 4) NOT NULL DEFAULT 0 COMMENT "Row Total", MODIFY COLUMN `base_row_total` decimal(20, 4) NOT NULL DEFAULT 0 COMMENT "Base Row Total", MODIFY COLUMN `row_total_with_discount` decimal(20, 4) NULL DEFAULT 0 COMMENT "Row Total With Discount", MODIFY COLUMN `base_tax_before_discount` decimal(20, 4) NULL COMMENT "Base Tax Before Discount", MODIFY COLUMN `tax_before_discount` decimal(20, 4) NULL COMMENT "Tax Before Discount", MODIFY COLUMN `price_incl_tax` decimal(20, 4) NULL COMMENT "Price Incl Tax", MODIFY COLUMN `base_price_incl_tax` decimal(20, 4) NULL COMMENT "Base Price Incl Tax", MODIFY COLUMN `row_total_incl_tax` decimal(20, 4) NULL COMMENT "Row Total Incl Tax", MODIFY COLUMN `base_row_total_incl_tax` decimal(20, 4) NULL COMMENT "Base Row 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", ADD CONSTRAINT PRIMARY KEY (`item_id`), ADD CONSTRAINT `QUOTE_ITEM_PARENT_ITEM_ID_QUOTE_ITEM_ITEM_ID` FOREIGN KEY (`parent_item_id`) REFERENCES `quote_item` (`item_id`) ON DELETE CASCADE, ADD CONSTRAINT `QUOTE_ITEM_QUOTE_ID_QUOTE_ENTITY_ID` FOREIGN KEY (`quote_id`) REFERENCES `quote` (`entity_id`) ON DELETE CASCADE, ADD CONSTRAINT `QUOTE_ITEM_STORE_ID_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE SET NULL, ADD INDEX `QUOTE_ITEM_PARENT_ITEM_ID` (`parent_item_id`), ADD INDEX `QUOTE_ITEM_PRODUCT_ID` (`product_id`), ADD INDEX `QUOTE_ITEM_QUOTE_ID` (`quote_id`), ADD INDEX `QUOTE_ITEM_STORE_ID` (`store_id`)

[hanhoe]

When I copy the query in myPhpAdmin, I get the error at:

ADD CONSTRAINT PRIMARY KEY

This option conflicts with Constraint (near Primary key).

How can I fix this?

[Manthan Dave]

Hello @hanhoe 

To resolve this issue i would suggest you to run below commands in sequence :

rm -rf generated
php bin/magento indexer:reindex
php bin/magento setup:upgrade
php bin/magento setup:di:compile
php bin/magento setup:static-content:deploy -f
php bin/magento cache:clean
php bin/magento cache:flush

then check also after running if its still not resolved then i would suggest to enabled developer mode for your setup and then run the same bunch of commands in sequence and then check !

Hope it helps 

[hanhoe]

Hi @Manthan Dave 

at the second command:

php bin/magento indexer:reindex

I get this errors:

Design Config Grid indexer process unknown error:
Table status for `design_config_dummy_cl` is incorrect. Can`t fetch version id.
Customer Grid indexer process unknown error:
Table status for `customer_dummy_cl` is incorrect. Can`t fetch version id.
Category Products indexer process unknown error:
Table status for `catalog_category_product_cl` is incorrect. Can`t fetch version id.
Product Categories indexer process unknown error:
Table status for `catalog_product_category_cl` is incorrect. Can`t fetch version id.
Catalog Rule Product indexer process unknown error:
Table status for `catalogrule_rule_cl` is incorrect. Can`t fetch version id.
Product EAV indexer process unknown error:
Table status for `catalog_product_attribute_cl` is incorrect. Can`t fetch version id.
Stock indexer process unknown error:
Table status for `cataloginventory_stock_cl` is incorrect. Can`t fetch version id.
Product Price indexer process unknown error:
Table status for `catalog_product_price_cl` is incorrect. Can`t fetch version id.
Catalog Product Rule indexer process unknown error:
Table status for `catalogrule_product_cl` is incorrect. Can`t fetch version id.
Catalog Search indexer process unknown error:
Table status for `catalogsearch_fulltext_cl` is incorrect. Can`t fetch version id.

I removed all dummy/sample lines in the composer.json, because I don´t need sample data anymore.

Is this the problem?

[Manthan Dave]

Hi @hanhoe 

Yes here i would suggest you to disable flat catalog from the Magento admin panel or from the database - as few of processes are not able to find the ID that means the store is in flat catalog mode so.

from this link - https://www.mageplaza.com/kb/how-to-enable-flat-catalog-magento-2.html

you will find how to enable/disable flat catalog on the Magento 2.

then again run the above commands in sequence !

[hanhoe]

Hi  @Manthan Dave 

yes, right. The flat catalog is enabled.

I copy all from my online webserver to my local instance, where I use php 7.2 and upgraded to 2.3.2 .. so right now I´m not able to use the admin panel.

Can I disable this direct in the database?

In the web I can´t find any instruction to disable this in the database direct.

Thank you for your support!

[hanhoe]

Hi again,

or is the flat catalog not enabled?

[Manthan Dave]

Hi @hanhoe 

Yes - It means, its already disabled 

So you have some other issue - can you please check flat tables is there or not in your database ?

I would suggest you to refer this link - https://magento.stackexchange.com/questions/164315/magento-2-tables-being-created-ending-in-cl

you will require to remove those cl tables and also at the end you will require to update version ID, something like below query : 

UPDATE `mview_state` SET `version_id` = '0';

Hope it helps !

[hanhoe]

Hi @Manthan Dave 

I deleted all tables with the ending _cl

I run the sql:

UPDATE `mview_state` SET `version_id` = '0';

I reset the indexer:

php bin/magento indexer:reset

and try again to indexing:

php bin/magento indexer:reindex

now I get:

Table design_config_dummy_cl does not exist
Table customer_dummy_cl does not exist
Table catalog_category_product_cl does not exist
Table catalog_product_category_cl does not exist
Table catalogrule_rule_cl does not exist
Table catalog_product_attribute_cl does not exist
Table cataloginventory_stock_cl does not exist
Table catalog_product_price_cl does not exist
Table catalogrule_product_cl does not exist
Table catalogsearch_fulltext_cl does not exist

[Manthan Dave]

Hi @hanhoe 

Can you run rest all the commands and check before running indexer ?