MySQL: manage.py dbshell does not get charset from DATABASES setting

[Tom Vergote]

I noticed that manage.py dbshell doesn't respect the database_options.
I ran into an issue with an application we are creating that needs to support mysql and postgre at least, we execute some sql scripts that get piped to manage.py dbshell (to avoid hardcoding psql -U xxx or mysql -u xxx and creating 2 scripts every time).

When running an utf8 database with utf8 as our charset in database_options, we ran into some weird encoding issues.

The solution for us was to learn mysql/client.py to respect the encoding settings in settings.py

Are you opposed to something like this?

Attaching small patch that fixes our problem. Let me know if it needs extending to support other backends or database_options.

[Tom Vergote]

small patch to illustrate suggestion

[Tom Vergote]

Also please note that the read_default_file setting is respected, so it's somehow inconsistent behaviour

[Carl Meyer]

We should look into whether this is needed on other backends as well, not just MySQL.

[Julie Pichon]

I adapted tvrg's patch to the current codebase - ​https://github.com/jpichon/django/compare/ticket_6517

I also tested dbshell with the sqlite3 and postgresql backends, and it works with UTF-8 characters without patching.

There are other ways to get the MySQL backend to play nicely with UTF-8 that don't require changing the code. You can add "default-character-set = utf8" to /etc/mysql/my.cnf on the server side, or create an option file with the UTF-8 charset config and update the settings to point to it in the "read_default_file" database options (as seen on ​https://docs.djangoproject.com/en/dev/ref/databases/#connecting-to-the-database)

[client]
default-character-set = utf8

If the patch option is preferred, I'm happy to work on the tests if someone could let me know where the dbshell tests live.

[Anssi Kääriäinen]

Seems like a good idea to me.

[Tim Graham]

A tests can be added in tests/dbshell/test_mysql.py.

[Manav Agarwal]

I wrote a test which is as follows:

def test_charset_configured(self):
    self.assertEqual(
        ['mysql', '--user=someuser', '--password=somepassword',
         '--host=somehost', '--port=444', 'somedbname'],
        self.get_command_line_arguments({
            'NAME': 'somedbname',
            'USER': 'someuser',
            'PASSWORD': 'somepassword',
            'HOST': 'somehost',
            'PORT': 444,
            'DEFAULT-CHARACTER-SET': 'utf8',
            'OPTIONS': {},
        }))

So in the above-mentioned tests, it is working fine when I am not using --default-character-set=utf8 in the list while I am including the same in the dictionary. (The above test is working fine) but IMO it should not work fine as when we are explicitly defining default-character-set to utf8 in the settings file, then it is to be mentioned in the list but as soon as I am adding the same to the list it is raising an error which means it needs a patch and after applying the patch as follows:

charset = settings_dict['OPTIONS'].get('charset',settings_dict['DEFAULT-CHARACTER-SET'])
if charset:
    args += ["--default-character-set=%s" % charset]

The above test is getting failed and the below-mentioned test is getting successfully executed.

def test_charset_configured(self):
    self.assertEqual(
        ['mysql', '--user=someuser', '--password=somepassword',
         '--host=somehost', '--port=444', 'somedbname',  '--default-character-set=utf8'],
        self.get_command_line_arguments({
            'NAME': 'somedbname',
            'USER': 'someuser',
            'PASSWORD': 'somepassword',
            'HOST': 'somehost',
            'PORT': 444,
            'OPTIONS': {},
            'DEFAULT-CHARACTER-SET': 'utf8',
        }))

If the patch and the tests seem fine, I may create a PR

[Mariusz Felisiak]

​PR

[Mariusz Felisiak <felisiak.mariusz@…>]

In af87574a:

Fixed #6517 -- Made dbshell use charset option on MySQL.

Co-Authored-By: Mariusz Felisiak <felisiak.mariusz@…>