Magento 2 – Fix Failed Database Rollback and Foreign Key Constraints

backupdatabaseforeign keymagento-2.1.7magento2

I recently updated from magento v. 2.1.7 to 2.1.9 and then tried to update directly after that to 2.2.1. That failed and when i try to do a rollback (backup created before updating from 2.1.7) that also fails so I am stuck between working versions. The error I get when trying to rollback database is this:

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint, query was: CREATE TABLE `customer_group` (
  `customer_group_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Customer Group Id',
  `customer_group_code` varchar(32) NOT NULL COMMENT 'Customer Group Code',
  `tax_class_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Tax Class Id',
  PRIMARY KEY (`customer_group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='Customer Group';

Best Answer

Since the 2.2.0 release the customer_group_id field has type INT(10):

CREATE TABLE `customer_group` (
 `customer_group_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `customer_group_code` varchar(32) NOT NULL COMMENT 'Customer Group Code',
 `tax_class_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Tax Class Id',
 PRIMARY KEY (`customer_group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='Customer Group'

You can update this table manually or check why during update the old type is used.

This is actual for the all tables where the customer_group_id field used as a foreign key: its length should be changed to the 10.

Related Topic