1320913 - Differences in the Django auth_* tables between environments

Status: RESOLVED FIXED

(Tree Management :: Treeherder, defect, P2)

Description

[Ed Morley [:emorley]]

There are multiple differences between the Vagrant and prod Django contrib.auth tables (auth_{permission,user,user_groups,user_user_permissions}).

See:
attachment 8814461 [details] [diff] [review]

Whilst these tables are Django-generated, I'm guessing there were changes/fixes to the ORM between when they were first created and now. (eg improving the de-duping of indexes that overlap).

Comment 1

[Ed Morley [:emorley]]

Using the updated diff in attachment 8814461 [details] [diff] [review], there are a few differences:

1) Redundant indexes on prod (Django was fixed, so now correctly doesn't create them)
2) Collation differences (will resolve in bug  1303767)
3) `datetime(6)` of vagrant vs `datetime` of prod, for `auth_user.date_joined`

For #1:

> SELECT * FROM sys.schema_redundant_indexes WHERE table_name LIKE 'auth_%'

******************** 1. row *********************
              table_schema: treeherder
                table_name: auth_group_permissions
      redundant_index_name: auth_group_permissions_5f412f9a
   redundant_index_columns: group_id
redundant_index_non_unique: 1
       dominant_index_name: group_id
    dominant_index_columns: group_id,permission_id
 dominant_index_non_unique: 0
            subpart_exists: 0
            sql_drop_index: ALTER TABLE `treeherder`.`auth_group_permissions` DROP INDEX `auth_group_permissions_5f412f9a`
******************** 2. row *********************
              table_schema: treeherder
                table_name: auth_permission
      redundant_index_name: auth_permission_37ef4eb4
   redundant_index_columns: content_type_id
redundant_index_non_unique: 1
       dominant_index_name: content_type_id
    dominant_index_columns: content_type_id,codename
 dominant_index_non_unique: 0
            subpart_exists: 0
            sql_drop_index: ALTER TABLE `treeherder`.`auth_permission` DROP INDEX `auth_permission_37ef4eb4`
******************** 3. row *********************
              table_schema: treeherder
                table_name: auth_user_groups
      redundant_index_name: auth_user_groups_6340c63c
   redundant_index_columns: user_id
redundant_index_non_unique: 1
       dominant_index_name: user_id
    dominant_index_columns: user_id,group_id
 dominant_index_non_unique: 0
            subpart_exists: 0
            sql_drop_index: ALTER TABLE `treeherder`.`auth_user_groups` DROP INDEX `auth_user_groups_6340c63c`
******************** 4. row *********************
              table_schema: treeherder
                table_name: auth_user_user_permissions
      redundant_index_name: auth_user_user_permissions_6340c63c
   redundant_index_columns: user_id
redundant_index_non_unique: 1
       dominant_index_name: user_id
    dominant_index_columns: user_id,permission_id
 dominant_index_non_unique: 0
            subpart_exists: 0
            sql_drop_index: ALTER TABLE `treeherder`.`auth_user_user_permissions` DROP INDEX `auth_user_user_permissions_6340c63c`
4 rows in set

Comment 2

[Ed Morley [:emorley]]

I've run the `sql_drop_index` statements above against dev/stage/prod:

ALTER TABLE `treeherder`.`auth_group_permissions` DROP INDEX `auth_group_permissions_5f412f9a`;
ALTER TABLE `treeherder`.`auth_permission` DROP INDEX `auth_permission_37ef4eb4`;
ALTER TABLE `treeherder`.`auth_user_groups` DROP INDEX `auth_user_groups_6340c63c`;
ALTER TABLE `treeherder`.`auth_user_user_permissions` DROP INDEX `auth_user_user_permissions_6340c63c`;

Comment 3

[Ed Morley [:emorley]]

For the `datetime(6)`, the docs say to just updated the column:
https://docs.djangoproject.com/en/1.10/ref/databases/#fractional-seconds-support-for-time-and-datetime-fields

...using eg:

ALTER TABLE `treeherder`.`auth_user` MODIFY `date_joined` DATETIME(6) NOT NULL;

...which I've done against dev/stage/prod.