After update, auth migration fails.

[Andrew Buchan]

Hi everyone,

Vital stats: MySQL, python 3.5, Ubuntu, not using South.

I'm upgrading from 1.9.5 to 1.11.8, and in amongst that there is a migration to change to username field length in auth:

auth

 [X] 0001_initial

 [X] 0002_alter_permission_name_max_length

 [X] 0003_alter_user_email_max_length

 [X] 0004_alter_user_username_opts

 [X] 0005_alter_user_last_login_null

 [X] 0006_require_contenttypes_0002

 [X] 0007_alter_validators_add_error_messages

 [ ] 0008_alter_user_username_max_length

That migration fails with the following message:

django.db.utils.IntegrityError: (1452, 'Cannot add or update a child row: a foreign key constraint fails (`tenant_fibrhealth`.`#sql-492_450`, CONSTRAINT `biomarker_range_user_id_ad579e08_fk` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`))')

Here is the model:

class Range(models.Model):
    user = models.ForeignKey(User, blank=True, null=True, related_name='ranges')

From my understanding this is happening because you can't change the length of a field used as a foreign key in MySQL (see https://stackoverflow.com/questions/36386231/how-to-change-the-size-of-a-column-with-foreign-key-constraint)

That post suggests the foreign keys need to be deletes and added back in. I'm not sure I like the idea of deleting all my foreign keys against user_id (there are quite a few tables with this) and I'm also not sure where I would do this, given that the migration file is not mine, but belongs to Django's auth app.

Now surely I'm not the only person using MySQL who's come across this?

Any ideas?

Thanks,

Andrew.

[Andrew Buchan]

Can anyone help with this one at all? It is:

a) pretty critical for my project to get this update because it has a fix for django's DecimalValidator bug which is causing a bug in the app.

b) something I expect that tons of people would have encountered

Or am I wrong about that last bit?

Thanks,

Andrew

[Dylan Reinhold]

Andrew,

  Not real sure on the fix, but the error seems more to be data related. Is it just saying it's trying to add a record that exits?

For the MySQL issue, the foreign key is on the 'id' field in user, which is not changing size, just the username field. So I don't think that is the issue you are seeing.

Dylan

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/dc336bdf-b468-4c7d-a33f-59d996bbeec6%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Andrew Buchan]

That is actually pretty helpful...

I think it means one of the users records which my table references was deleted, but the deletion does not cascade, nor set the user_id to null in the child record, so I have an orphan record. This is allowed because it has null=True, even though the orphan record still has a dud user_is in it. But a modification to an unrelated field in the parent table somehow picks up on this integrity violation.

I'm gonna create a migration to delete orphan records, and see where I get.

Thanks!

To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.

To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.

[Andrew Buchan]

OK, I solved it. 

Migrations attempted to modify auth.user, but it seems MYSQL checks the referential integrity of any child table with a FK to the table being modified. My child table had bad foreign keys, hence the error during migration.

Side note: whe way I got these foreign key errors was not because of a failure to cascade, or a glitch in the app, but rather because of how I initially populated my tests database using an SQL dump of various tables from the live database (but not the user table). It just so happens this particular table is used for quite generic records (user=null) as well as user-specific records, and I'd imported the whole lot. 

It makes sense that MYSQL allows you to import whatever you like in this fashion, but it means you need to be careful what you import. If you need to do an ad-hoc referential integrity check, here's a sproc: https://forums.mysql.com/read.php?20,137634

So this glitch doesn't/won't happen in the live database.

Anyway, back to resolving the myriad other things that are breaking due to the migration, yay!

Thanks for chipping in Dylan, it did actually help.

Andrew