django.db.utils.OperationalError: 3780 Referencing column and referenced column are incompatible

Question

I'm trying to add a foreign key relation between two models in Django, but I'm getting this error.

Notification model:

class Notification(models.Model):
    ...
    lab_extension = models.ForeignKey(Labs, on_delete=models.CASCADE, null=True, to_field='lab_id')
    ...

Lab model

class Lab(models.Model):
    ...
    lab_id = models.AutoField(primary_key=True)
    ...

After assigning this foreign key field to the Notification model, I get this error when doing python manage.py migrate:

django.db.utils.OperationalError: (3780, "Referencing column 'lab_extension_id' and referenced column 'lab_id' in foreign key constraint [constraint name] are incompatible.")

It's likely this error will not persist if I remove AutoField from the Lab model, and use the default id field. But I'm at a point into the project where I can't do that. I also realize a foreign key field is generally not nullable, but for this project, the Notification model may or may not point to a Lab model.

Doing SHOW CREATE TABLE notification gives me this:

notification_notification, CREATE TABLE `notification_notification` (
  `id` int NOT NULL AUTO_INCREMENT,
  `read` tinyint(1) NOT NULL,
  `message` longtext NOT NULL,
  `user_id` int NOT NULL,
  `category` varchar(2) NOT NULL,
  `created` datetime(6) NOT NULL,
  `question_id` int DEFAULT NULL,
  `submission_id` int DEFAULT NULL,
  `update_request_id` int DEFAULT NULL,
  `lab_extension_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `notification_notific_user_id_e9d6f5f4_fk_user_mana` (`user_id`),
  KEY `notification_notification_question_id_af1ad55d_fk_qa_question_id` (`question_id`),
  KEY `notification_notific_submission_id_78f78d05_fk_community` (`submission_id`),
  KEY `notification_notific_update_request_id_39cf03ba_fk_community` (`update_request_id`),
  CONSTRAINT `notification_notific_submission_id_78f78d05_fk_community` FOREIGN KEY (`submission_id`) REFERENCES `community_communitysubmission` (`id`),
  CONSTRAINT `notification_notific_update_request_id_39cf03ba_fk_community` FOREIGN KEY (`update_request_id`) REFERENCES `community_submissionupdaterequest` (`id`),
  CONSTRAINT `notification_notific_user_id_e9d6f5f4_fk_user_mana` FOREIGN KEY (`user_id`) REFERENCES `user_manager_user` (`id`),
  CONSTRAINT `notification_notification_question_id_af1ad55d_fk_qa_question_id` FOREIGN KEY (`question_id`) REFERENCES `qa_question` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3241 DEFAULT CHARSET=latin1

Doing SHOW CREATE TABLE lab gives me this:

lab, CREATE TABLE `lab` (
  `lab_id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `project_dir` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '/opt/PacketAccess/',
  `lab_model` int unsigned NOT NULL DEFAULT '0',
  `project_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `pn_account` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `deployed` int NOT NULL,
  `progress` int NOT NULL,
  `request_time` int NOT NULL,
  `deploy_time` int NOT NULL,
  `destroy_time` int NOT NULL,
  `user_ip` varchar(260) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `status` int NOT NULL DEFAULT '1',
  `labRegion` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'EWR1',
  `pn_progress` int NOT NULL,
  `pn_project_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'n/a',
  `pn_host_name` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'unknown',
  `pn_device_id` varchar(255) COLLATE utf8_bin NOT NULL,
  `pn_device_ip` varchar(40) COLLATE utf8_bin NOT NULL,
  `pn_sdn_ip` varchar(40) COLLATE utf8_bin NOT NULL,
  `pn_nfmp_ip` varchar(40) COLLATE utf8_bin NOT NULL,
  `pn_flow_ip` varchar(40) COLLATE utf8_bin NOT NULL,
  `pn_nfmt_ip` varchar(40) COLLATE utf8_bin NOT NULL,
  `pn_nrcx_ip` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '0.0.0.0',
  `pn_nrcs_ip` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '0.0.0.0',
  `pn_elastic_ip` varchar(40) COLLATE utf8_bin NOT NULL,
  `lab_version` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '17.3',
  `lab_tags` varchar(255) COLLATE utf8_bin NOT NULL,
  `user_email` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'n/a',
  `voucher_id` int NOT NULL,
  `admin_password` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `oss_password` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `deployed_email` text CHARACTER SET utf8 COLLATE utf8_bin,
  `lab_name` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `customer_lab_ip` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '0.0.0.0',
  `deployment_test` tinyint(1) DEFAULT NULL,
  `ssl_enabled` tinyint(1) DEFAULT NULL,
  `associated_lab_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`lab_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11995 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin

NOTE: I altered the names of some tables and fields on this post to protect privacy, so if some names are inconsistent, please ignore that.

Answer 1

I had a similar problem. Your problem stems from having a different default CHARACTER SET and COLLATION.

When you ran SHOW CREATE TABLE notification, the very last line of the result shows

) ENGINE=InnoDB AUTO_INCREMENT=3241 DEFAULT CHARSET=latin1

When you ran SHOW CREATE TABLE lab, the very last line of the result shows

) ENGINE=InnoDB AUTO_INCREMENT=11995 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin

Your two columns are simply using their own table's charset and collation, and they happen to be different.

To my knowledge, django doesn't really deal with the charset and collation (maybe someone can correct me on that); django will just use whatever defaults the database is using; the problem and solution are db related.

All you need to do is make everything match (datatype, charset, collation), and then Django should be able to make your foreign key constraint.

It seems that older versions of MySQL have their default charset and collation on latin1, and now the default is utf8. If you dump and restore from an old db to a new db, you will probably experience this bug (it seems you and i were victim to this).

In my case, I had a dump of the db already and this new db is just experimental, so I dropped my experimental database, recreated it with the correct charset and collation, and the restored it with the dump I had, and then ran my django migrations.

DROP DATABASE yourdbname;

CREATE DATABASE yourdbname CHARACTER SET latin1 COLLATE latin1_swedish_ci;

If you are on a db that shouldn't be deleted, you can try updating the character set and collation on the db level, table level, or column level. It seems that you already messed around with character sets and collation, since your lab table has some columns with different ones (shown in SHOW CREATE TABLE lab)

Answer 2

For me, the problem was in Collation.

I checked the Collation of the first table as:

select column_name, COLLATION_NAME, CHARACTER_SET_NAME from information_schema.`COLUMNS` where table_name = "<table_name>";

Then fixed the default database collation as:

alter database <database_name> CHARACTER SET <utf8mb4> COLLATE <utf8mb4_desired_value>;

Answer 3

Here is the probem,

In Lab table

  `lab_id` int unsigned NOT NULL AUTO_INCREMENT

While in Notification

lab_extension_id` int DEFAULT NULL

These fields are different and can not be set to foreignKeys, move one type to the other.