magento2: setup:upgrade throwing sql errors after updating to Magento 2.3.1

Hi,

We had updated our magento from Magento 2.1.14 => 2.3.1. After following magento official notification on the update process. We had completed the update to 2.3.1 via composer.

Right after we had cleared cache and run the php bin/magento setup:upgrade , what we got when executing this code was

php bin/magento setup:upgrade Cache cleared successfully File system cleanup: /home/evc/public_html/generated/code/Composer /home/evc/public_html/generated/code/MSP /home/evc/public_html/generated/code/Magento /home/evc/public_html/generated/code/Magmodules /home/evc/public_html/generated/code/Symfony /home/evc/public_html/generated/code/TemplateMonster The directory ‘/home/evc/public_html/generated/metadata/’ doesn’t exist - skipping cleanup Updating modules: Schema creation/updates: SQLSTATE[HY000]: General error: 1025 Error on rename of ‘./firesonl_main/#sql-d90_1b45f’ to ‘./firesonl_main/customer_group’ (errno: 150 - Foreign key constraint is incorrectly formed), query was: ALTER TABLE customer_group MODIFY COLUMN customer_group_id int(10) UNSIGNED NOT NULL AUTO_INCREMENT [evc@evc public_html]$

Preconditions (*)

Current Magento version 2.3.1
Used Magento https://raw.githubusercontent.com/magento/magento2/2.3.0/dev/tools/UpgradeScripts/pre_composer_update_2.3.php to fix composer prior to run composer update

Steps to reproduce (*)

Install Magento 2.1.14 and use https://raw.githubusercontent.com/magento/magento2/2.3.0/dev/tools/UpgradeScripts/pre_composer_update_2.3.php to fix the composer and run composer update.
You will end on the error shown above.

Expected result (*)

We accept a smooth run of the setup:upgrade command

Actual result (*)

Running, php bin/magento setup:upgrade will shows the error
php bin/magento setup:upgrade Cache cleared successfully File system cleanup: /home/evc/public_html/generated/code/Composer /home/evc/public_html/generated/code/MSP /home/evc/public_html/generated/code/Magento /home/evc/public_html/generated/code/Magmodules /home/evc/public_html/generated/code/Symfony /home/evc/public_html/generated/code/TemplateMonster The directory ‘/home/evc/public_html/generated/metadata/’ doesn’t exist - skipping cleanup Updating modules: Schema creation/updates: SQLSTATE[HY000]: General error: 1025 Error on rename of ‘./firesonl_main/#sql-d90_1b45f’ to ‘./firesonl_main/customer_group’ (errno: 150 - Foreign key constraint is incorrectly formed), query was: ALTER TABLE customer_group MODIFY COLUMN customer_group_id int(10) UNSIGNED NOT NULL AUTO_INCREMENT [evc@evc public_html]$

About this issue

Original URL
State: closed
Created 5 years ago
Comments: 19 (3 by maintainers)

Most upvoted comments

What worked here:

Try to alter customer_group_id type to int(10) from mysql or your database manager gui. Sql should show the origin of the issue i.e (the foreign key preventing the update). Mine was due to amasty Cannot change column 'customer_group_id': used in a foreign key constraint 'AMASTY_FPC_LOG_CUSTOMER_GROUP_CUSTOMER_GROUP_CUSTOMER_GROUP_ID' of table 'db.amasty_fpc_log' 2x:xx:xx SQL command
Go to the concerned table drop the foreign key
Update the types of customer_group_id column to int(10) in both tables (ie customer_group and the table with the deleted foreign key here amasty_fpc_log)
Recreate the foreign key

The concerned tables should be locked to preserve integrity (additional help https://stackoverflow.com/questions/13606469/cannot-change-column-used-in-a-foreign-key-constraint).

seyuf on Sep 7, 2019