Skip "SET SQL_AUTO_IS_NULL = 0" on versions of MySQL that don't need it

[ssjunior]

Every connection mada to MySQL is issuing an unnecessary query setting SQL_AUTO_IS_NULL to 0. Since MySQL this is not needed anymore and this can be set in the server variables too. Avoiding this query will result in better performance. This shoud be a settings options that allow user to enable or disable this unnecessary query.

​http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_sql_auto_is_null

System Variable Name sql_auto_is_null
Variable Scope Global, Session
Dynamic Variable Yes
Permitted Values Type boolean
Default 0

[ssjunior]

Pull request submitted.

​https://github.com/django/django/pull/4538

[Tim Graham]

Can we avoid the setting and make it a function of the MySQL version? I'm not clear if it's always safe to skip this query on MySQL 5.6+, or only when also setting a MySQL server variable?

[ssjunior]

I think that I have a better solution. Check in the features for the status on the server and issue the command only if it is True on the server. Tell me what do you think and if it is ok I will submitt a new PR.

​https://github.com/ssjunior/django/commit/52bf6c7630ab6b17a3a3ff64213e1ad36d6fe270

    @cached_property
    def sql_auto_is_null(self):
        "Return if server global variable sql_auto_is_null is set to True"
        with self.connection.cursor() as cursor:
            cursor.execute("SHOW GLOBAL VARIABLES LIKE 'sql_auto_is_null'")
            return cursor.fetchone()[1] == 'ON'


    def init_connection_state(self):
        # SQL_AUTO_IS_NULL in MySQL controls whether an AUTO_INCREMENT column
        # on a recently-inserted row will return when the field is tested for
        # NULL. Disabling this value brings this aspect of MySQL in line with
        # SQL standards.
        # Check the sql_auto_is_null status on the MySQL server and if necessary
        # set it to False.
        if self.features.sql_auto_is_null:
            with self.cursor() as cursor:
                cursor.execute('SET SQL_AUTO_IS_NULL = 0')

[Claude Paroz]

Looks good, please make it a pull request. It will need some tests, though.

[Tim Graham]

Claude, how did you imagine a test would look like for this?

[Claude Paroz]

Something like: take a connection object, close it, reopen it and check that no SQL_AUTO_IS_NULL query is done.

[Stewart Park]

​https://github.com/django/django/pull/5794

I made a new pull request for this issue. Added testing as well.

Can you check if it looks good?

[Shai Berger]

We only mark tickets fixed when the PR has been merged.

[Stewart Park]

I see! Sorry about that. Any plan on reviewing this pull request?

[Tim Graham]

Left some minor comments. Please uncheck "Patch needs improvement" when you update it, thanks!

[Stewart Park]

I fixed the code the way you suggested. :) Also passed all the test cases. Thanks.

[Tim Graham <timograham@…>]

In b7fdd60:

Fixed #24675 -- Skipped SQL_AUTO_IS_NULL query on MySQL if not needed.

[Brian Wawok]

In development I still see 2 queries per page load for this. I assume because connections do not persist in dev.

Seems this needs dealt with in a different way in dev?

[Simon Charette]

This patch has only be merged in the master branch so far and is not part of any official release. It should be part of the upcoming 1.10 release.