1320918 - Differences in the django_{admin_log,migrations,session} schema between environments

Status: RESOLVED FIXED

(Tree Management :: Treeherder, defect, P3)

Description

[Ed Morley [:emorley]]

--
 -- Table structure for table `django_admin_log`
 --
 
 DROP TABLE IF EXISTS `django_admin_log`;
 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `django_admin_log` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
-  `action_time` datetime(6) NOT NULL,
-  `object_id` longtext COLLATE utf8_bin,
-  `object_repr` varchar(200) COLLATE utf8_bin NOT NULL,
+  `action_time` datetime NOT NULL,
+  `object_id` longtext,
+  `object_repr` varchar(200) NOT NULL,
   `action_flag` smallint(5) unsigned NOT NULL,
-  `change_message` longtext COLLATE utf8_bin NOT NULL,
+  `change_message` longtext NOT NULL,
   `content_type_id` int(11) DEFAULT NULL,
   `user_id` int(11) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `<INDEX_NAME>` (`content_type_id`),
   KEY `<INDEX_NAME>` (`user_id`),
   CONSTRAINT `<INDEX_NAME>` FOREIGN KEY (`content_type_id`) REFERENCES `django_content_type` (`id`),
   CONSTRAINT `<INDEX_NAME>` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The collation differences will be handled by bug 1303767.

That just leaves:
-  `action_time` datetime(6) NOT NULL,
+  `action_time` datetime NOT NULL,

Comment 1

[Ed Morley [:emorley]]

A few other core Django tables have the datetime field precision difference too:

--
 -- Table structure for table `django_migrations`
 --
 
 DROP TABLE IF EXISTS `django_migrations`;
 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `django_migrations` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
-  `app` varchar(255) COLLATE utf8_bin NOT NULL,
-  `name` varchar(255) COLLATE utf8_bin NOT NULL,
-  `applied` datetime(6) NOT NULL,
+  `app` varchar(255) NOT NULL,
+  `name` varchar(255) NOT NULL,
+  `applied` datetime NOT NULL,
   PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
 -- Table structure for table `django_session`
 --
 
 DROP TABLE IF EXISTS `django_session`;
 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `django_session` (
-  `session_key` varchar(40) COLLATE utf8_bin NOT NULL,
-  `session_data` longtext COLLATE utf8_bin NOT NULL,
-  `expire_date` datetime(6) NOT NULL,
+  `session_key` varchar(40) NOT NULL,
+  `session_data` longtext NOT NULL,
+  `expire_date` datetime NOT NULL,
   PRIMARY KEY (`session_key`),
   KEY `<INDEX_NAME>` (`expire_date`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 /*!40101 SET character_set_client = @saved_cs_client */;

Comment 2

[Ed Morley [:emorley]]

Similar to bug 1320913 comment 3, I've fixed the datetime differences on dev/stage/prod using:

ALTER TABLE `treeherder`.`django_admin_log` MODIFY `action_time` DATETIME(6) NOT NULL;
ALTER TABLE `treeherder`.`django_migrations` MODIFY `applied` DATETIME(6) NOT NULL;
ALTER TABLE `treeherder`.`django_session` MODIFY `expire_date` DATETIME(6) NOT NULL;

The collation differences will be handled by bug 1303767.