'CASCADE' support

[mavenraven]

Please search the existing issues for relevant feature requests, and use the reaction feature to add upvotes to pre-existing requests.

Use Case(s)

This stems out of trying to use Django with Vitess. Specifically, this limitation was found running migrations in the following project: https://github.com/planetscale/django-locallibrary-tutorial.

The error message that is outputted is:

Traceback (most recent call last):                                                                                                                                                                            
  File "manage.py", line 15, in <module>
    execute_from_command_line(sys.argv)
  File "/home/user/.pyenv/versions/3.6.12/lib/python3.6/site-packages/django/core/management/__init__.py", line 381, in execute_from_command_line
    utility.execute()
  File "/home/user/.pyenv/versions/3.6.12/lib/python3.6/site-packages/django/core/management/__init__.py", line 375, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/user/.pyenv/versions/3.6.12/lib/python3.6/site-packages/django/core/management/base.py", line 316, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/user/.pyenv/versions/3.6.12/lib/python3.6/site-packages/django/core/management/base.py", line 353, in execute
    output = self.handle(*args, **options)
  File "/home/user/.pyenv/versions/3.6.12/lib/python3.6/site-packages/django/core/management/base.py", line 83, in wrapped                                                                            [70/292]
    res = handle_func(*args, **kwargs)                                                                                                                                                                        
  File "/home/user/.pyenv/versions/3.6.12/lib/python3.6/site-packages/django/core/management/commands/migrate.py", line 203, in handle                                                                        
    fake_initial=fake_initial,                                                                                                                                                                                
  File "/home/user/.pyenv/versions/3.6.12/lib/python3.6/site-packages/django/db/migrations/executor.py", line 117, in migrate                                                                                 
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)                                                                                                          
  File "/home/user/.pyenv/versions/3.6.12/lib/python3.6/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards                                                                   
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)                                                                                                                      
  File "/home/user/.pyenv/versions/3.6.12/lib/python3.6/site-packages/django/db/migrations/executor.py", line 244, in apply_migration                                                                         
    state = migration.apply(state, schema_editor)                                                                                                                                                             
  File "/home/user/.pyenv/versions/3.6.12/lib/python3.6/site-packages/django/db/migrations/migration.py", line 124, in apply                                                                                  
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)                                                                                                                      
  File "/home/user/.pyenv/versions/3.6.12/lib/python3.6/site-packages/django/db/migrations/operations/fields.py", line 150, in database_forwards                                                              
    schema_editor.remove_field(from_model, from_model._meta.get_field(self.name))                                                                                                                             
  File "/home/user/.pyenv/versions/3.6.12/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 461, in remove_field                                                                           
    return self.delete_model(field.remote_field.through)                                                                                                                                                      
  File "/home/user/.pyenv/versions/3.6.12/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 331, in delete_model                                                                           
    "table": self.quote_name(model._meta.db_table),                                                                                                                                                           
  File "/home/user/.pyenv/versions/3.6.12/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 133, in execute                                                                                
    cursor.execute(sql, params)                                                                                                                                                                               
  File "/home/user/.pyenv/versions/3.6.12/lib/python3.6/site-packages/django/db/backends/utils.py", line 100, in execute                                                                                      
    return super().execute(sql, params)                                                                                                                                                                       
  File "/home/user/.pyenv/versions/3.6.12/lib/python3.6/site-packages/django/db/backends/utils.py", line 68, in execute                                                                                       
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)                                                                                                                       
  File "/home/user/.pyenv/versions/3.6.12/lib/python3.6/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers                                                                        
    return executor(sql, params, many, context)                                                                                                                                                               
  File "/home/user/.pyenv/versions/3.6.12/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute                                                                                      
    return self.cursor.execute(sql, params)                                                                                                                                                                   
  File "/home/user/.pyenv/versions/3.6.12/lib/python3.6/site-packages/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/user/.pyenv/versions/3.6.12/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/home/user/.pyenv/versions/3.6.12/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 71, in execute
    return self.cursor.execute(query, args)
  File "/home/user/.pyenv/versions/3.6.12/lib/python3.6/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/home/user/.pyenv/versions/3.6.12/lib/python3.6/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/home/user/.pyenv/versions/3.6.12/lib/python3.6/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
django.db.utils.OperationalError: (1105, "vtgate: http://-<redacted>/: syntax error at position 42 near 'CASCADE'")

The migration file that is being ran is:

# -*- coding: utf-8 -*-
# Generated by Django 1.10 on 2016-09-27 09:47
from __future__ import unicode_literals

from django.db import migrations, models


class Migration(migrations.Migration):

    dependencies = [
        ('catalog', '0015_auto_20160927_1808'),
    ]

    operations = [
        migrations.CreateModel(
            name='Genre',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                ('name', models.CharField(help_text='Enter a book genre (e.g. Science Fiction, French Poetry etc.)', max_length=200)),
            ],
        ),
        migrations.RemoveField(
            model_name='book',
            name='subject',
        ),
        migrations.AlterField(
            model_name='book',
            name='isbn',
            field=models.CharField(help_text='13 Character <a href="https://www.isbn-international.org/content/what-isbn">ISBN number</a>', max_length=13, verbose_name='ISBN'),
        ),
        migrations.DeleteModel(
            name='Subject',
        ),
        migrations.AddField(
            model_name='book',
            name='genre',
            field=models.ManyToManyField(help_text='Select a genre for this book', to='catalog.Genre'),
        ),
    ]

There may be a workaround for this. I have not investigated it deeply. Also interestingly, if I run python3 manage.py migrate again, I see

django.db.utils.OperationalError: (1050, 'vtgate: http:/<redacted>/: target: django5.-.master, used tablet:<redacted> (10.8.91.60): vttablet: rpc error: code = AlreadyExists desc = Table \'catalog_genre\' already exists (errno 1050) (sqlstate 42S01) (CallerID: <redacted>): Sql: "CREATE TABLE `catalog_genre` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `name` varchar(200) NOT NULL)", BindVars: {}')

which suggests to me that there is some weirdness on how Vitess / MySQL handles the DDL. I know that it's not transactional, but I don't have much context on the details for either as of yet and if this is expected behavior.

cc @mcronce

[mavenraven]

Just as a guess, the ManyToManyField sets something like ON DELETE CASCADE which Vitess doesn't support for logical reasons:
"An existing lookup_unique vindex can be trivially switched to a consistent_lookup_unique by changing the vindex type in the VSchema. This is because the data is compatible. Caveat: If you delete a row from the owner table, Vitess will not perform cascading deletes. This is mainly for efficiency reasons; the application is likely capable of doing this more efficiently."

[mavenraven]

The workaround is likely https://docs.djangoproject.com/en/3.1/ref/models/fields/#django.db.models.ForeignKey.on_delete, using NULL, testing now.

[mavenraven]

I wasn't able to get this working in my timebox. Here's a summary of my thoughts and findings:

• I went up to https://docs.djangoproject.com/en/3.1/intro/tutorial03/. This worked as expected.
• Given that Rails works, I'm almost positive that there is just some workaround / limitations that the user will have to work around, but that nothing is fundamentally broken.
• I'm not familiar enough with Django to know exactly how to fix the issue in the test app without some digging in. I expect that it's something straightforward with changing the cascade rules.
• The test app also unfortunately is doing a lot of stuff in each migration, which means that the migrations table gets into an inconsistent state :/ . I was unable to get python manage.py sqlflush to work against Vitess, but that's probably due to my ignorance around Vitess.