search

thewca / worldcubeassociation.org

All of the code that runs on worldcubeassociation.org
https://www.worldcubeassociation.org/
GNU General Public License v3.0
324 stars 175 forks source link

Production database discrepancies #1847

Open jfly opened 7 years ago

jfly commented 7 years ago

After #1817 and #1836, I decided to try out db:structure:dump on production to see how out of sync our production and development databases are. The results were not thrilling =(

```diff ~/worldcubeassociation.org/WcaOnRails @production> RACK_ENV=production bin/rake db:structure:dump ~/worldcubeassociation.org/WcaOnRails @production> git diff diff --git a/WcaOnRails/db/structure.sql b/WcaOnRails/db/structure.sql index 6e01618..9c6257c 100644 --- a/WcaOnRails/db/structure.sql +++ b/WcaOnRails/db/structure.sql @@ -169,7 +169,7 @@ CREATE TABLE `InboxPersons` ( `wcaId` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `name` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `countryId` char(2) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', - `gender` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT '', + `gender` char(1) COLLATE utf8mb4_unicode_ci DEFAULT '', `dob` date NOT NULL, `competitionId` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL, KEY `InboxPersons_fk_country` (`countryId`), @@ -208,7 +208,7 @@ CREATE TABLE `Persons` ( `subId` tinyint(6) NOT NULL DEFAULT '1', `name` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `countryId` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', - `gender` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT '', + `gender` char(1) COLLATE utf8mb4_unicode_ci DEFAULT '', `year` smallint(6) NOT NULL DEFAULT '0', `month` tinyint(4) NOT NULL DEFAULT '0', `day` tinyint(4) NOT NULL DEFAULT '0', @@ -287,7 +287,7 @@ CREATE TABLE `Results` ( KEY `Results_regionalSingleRecordCheckSpeedup` (`eventId`,`competitionId`,`roundTypeId`,`countryId`,`best`), KEY `Results_fk_competitor` (`personId`), KEY `index_Results_on_competitionId_and_updated_at` (`competitionId`,`updated_at`), - KEY `_tmp_index_Results_on_countryId` (`countryId`) + KEY `index_Results_on_countryId` (`countryId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci PACK_KEYS=1; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `RoundTypes`; @@ -322,12 +322,12 @@ DROP TABLE IF EXISTS `ar_internal_metadata`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `ar_internal_metadata` ( - `key` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, - `value` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `key` varchar(255) COLLATE utf8_unicode_ci NOT NULL, + `value` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL, PRIMARY KEY (`key`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `archive_phpbb3_forums`; /*!40101 SET @saved_cs_client = @@character_set_client */; @@ -385,7 +385,7 @@ CREATE TABLE `archive_phpbb3_forums` ( PRIMARY KEY (`forum_id`), KEY `left_right_id` (`left_id`,`right_id`), KEY `forum_lastpost_id` (`forum_last_post_id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `archive_phpbb3_posts`; /*!40101 SET @saved_cs_client = @@character_set_client */; @@ -430,7 +430,7 @@ CREATE TABLE `archive_phpbb3_posts` ( KEY `post_visibility` (`post_visibility`), FULLTEXT KEY `post_subject` (`post_subject`), FULLTEXT KEY `post_content` (`post_text`,`post_subject`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `archive_phpbb3_topics`; /*!40101 SET @saved_cs_client = @@character_set_client */; @@ -482,7 +482,7 @@ CREATE TABLE `archive_phpbb3_topics` ( KEY `fid_time_moved` (`forum_id`,`topic_last_post_time`,`topic_moved_id`), KEY `topic_visibility` (`topic_visibility`), KEY `forum_vis_last` (`forum_id`,`topic_visibility`,`topic_last_post_id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `archive_phpbb3_users`; /*!40101 SET @saved_cs_client = @@character_set_client */; @@ -559,7 +559,7 @@ CREATE TABLE `archive_phpbb3_users` ( KEY `user_birthday` (`user_birthday`), KEY `user_email_hash` (`user_email_hash`), KEY `user_type` (`user_type`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `archive_registrations`; /*!40101 SET @saved_cs_client = @@character_set_client */; @@ -587,6 +587,17 @@ CREATE TABLE `archive_registrations` ( UNIQUE KEY `index_registrations_on_competitionId_and_user_id` (`competitionId`,`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `championship_list`; +/*!50001 DROP VIEW IF EXISTS `championship_list`*/; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +/*!50001 CREATE TABLE `championship_list` ( + `country` tinyint NOT NULL, + `competition_id` tinyint NOT NULL, + `name` tinyint NOT NULL, + `championship_type` tinyint NOT NULL +) ENGINE=MyISAM */; +SET character_set_client = @saved_cs_client; DROP TABLE IF EXISTS `championships`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; @@ -808,10 +819,10 @@ CREATE TABLE `polls` ( `id` int(11) NOT NULL AUTO_INCREMENT, `question` text COLLATE utf8mb4_unicode_ci, `multiple` tinyint(1) NOT NULL, - `deadline` datetime NOT NULL, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL, `comment` text COLLATE utf8mb4_unicode_ci, + `deadline` datetime DEFAULT NULL, `confirmed_at` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; @@ -865,17 +876,18 @@ DROP TABLE IF EXISTS `rails_persons`; /*!50001 DROP VIEW IF EXISTS `rails_persons`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; -/*!50001 CREATE VIEW `rails_persons` AS SELECT - 1 AS `id`, - 1 AS `wca_id`, - 1 AS `subId`, - 1 AS `name`, - 1 AS `countryId`, - 1 AS `gender`, - 1 AS `year`, - 1 AS `month`, - 1 AS `day`, - 1 AS `comments`*/; +/*!50001 CREATE TABLE `rails_persons` ( + `id` tinyint NOT NULL, + `wca_id` tinyint NOT NULL, + `subId` tinyint NOT NULL, + `name` tinyint NOT NULL, + `countryId` tinyint NOT NULL, + `gender` tinyint NOT NULL, + `year` tinyint NOT NULL, + `month` tinyint NOT NULL, + `day` tinyint NOT NULL, + `comments` tinyint NOT NULL +) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; DROP TABLE IF EXISTS `registration_competition_events`; /*!40101 SET @saved_cs_client = @@character_set_client */; @@ -1077,6 +1089,21 @@ CREATE TABLE `votes` ( KEY `index_votes_on_user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; +/*!50001 DROP TABLE IF EXISTS `championship_list`*/; +/*!50001 DROP VIEW IF EXISTS `championship_list`*/; +/*!50001 SET @saved_cs_client = @@character_set_client */; +/*!50001 SET @saved_cs_results = @@character_set_results */; +/*!50001 SET @saved_col_connection = @@collation_connection */; +/*!50001 SET character_set_client = utf8mb4 */; +/*!50001 SET character_set_results = utf8mb4 */; +/*!50001 SET collation_connection = utf8mb4_unicode_ci */; +/*!50001 CREATE ALGORITHM=UNDEFINED */ +/*!50013 DEFINER=`root`@`%` SQL SECURITY DEFINER */ +/*!50001 VIEW `championship_list` AS select (case when isnull(`Countries`.`id`) then `championships`.`championship_type` else `Countries`.`name` end) AS `country`,`championships`.`competition_id` AS `competition_id`,`Competitions`.`name` AS `name`,`championships`.`championship_type` AS `championship_type` from ((`championships` left join `Countries` on((`Countries`.`iso2` = `championships`.`championship_type`))) left join `Competitions` on((`Competitions`.`id` = `championships`.`competition_id`))) order by (`championships`.`championship_type` = 'world') desc,(`championships`.`championship_type` = '_Asia') desc,(`championships`.`ch +/*!50001 SET character_set_client = @saved_cs_client */; +/*!50001 SET character_set_results = @saved_cs_results */; +/*!50001 SET collation_connection = @saved_col_connection */; +/*!50001 DROP TABLE IF EXISTS `rails_persons`*/; /*!50001 DROP VIEW IF EXISTS `rails_persons`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; @@ -1085,6 +1112,7 @@ CREATE TABLE `votes` ( /*!50001 SET character_set_results = utf8 */; /*!50001 SET collation_connection = utf8_general_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ +/*!50013 DEFINER=`root`@`%` SQL SECURITY DEFINER */ /*!50001 VIEW `rails_persons` AS select `Persons`.`rails_id` AS `id`,`Persons`.`id` AS `wca_id`,`Persons`.`subId` AS `subId`,`Persons`.`name` AS `name`,`Persons`.`countryId` AS `countryId`,`Persons`.`gender` AS `gender`,`Persons`.`year` AS `year`,`Persons`.`month` AS `month`,`Persons`.`day` AS `day`,`Persons`.`comments` AS `comments` from `Persons` */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; ```

There's a lot of stuff above, here's a list of the important differences I see:

jfly commented 7 years ago

Hey Jonatan, do you mind taking some time to look into this? You don't have to actually write any code yet, the first step would be to just figure out what happened in each of these discrepancies and how we should go about fixing them.

An awesome goal would be for db:structure:dump to have no discrepancies whatsoever. If we do ever get to that point, maybe we could add any discrepancies to server-status, to make sure we don't cause future problems.

jonatanklosko commented 7 years ago
  1. Would just guess it may have to do with MySQL version difference. Suggested fix: maybe an implicit migration to the better type (after determining which one is better).
  2. I have a good theory about the index name. So this comment implies that the index introduced a speed bust, I had certainly tested the query with the index before I created the appropriate Rails migration. Most likely I manually created an index named _tmp_index_Results_on_countryId just to test the performance of the query, and once it did well I added a Rails migration. This way my original name stayed within structure.sql, whereas running the migration on production created an index with a normal Rails-generated name. Suggested fix: add a migration renaming the index if its name is _tmp_index_Results_on_countryId.
  3. This is a really tricky one, and I barely have an idea of what might have happened. So Rails 5 introduced a new table called ar_internal_metadata, it is created once db:migrate is run for the first time using Rails 5. I see that it was added to structure.sql as a part of a really unrelated PR #1505. So my guess is that the table was created in production automatically at some point, and after some time you added the table with changed collations (accidentally?) to the repo. The only thing I am confused by is why it took so long for the table to end up in our git structure.sql. Suggested fix: not sure what would be the best, we could drop the production version of the table and let it be recreated on the next migration (hopefully with the collation we have defined in structure.sql). Maybe someone have a better idea for a clean fix?
  4. Hmm, I have really no idea, you added them in #1682. Suggested fix: I don't really know which engine should be used for those phpbb tables, but we could simply change structure.sql and the tables manually, as they are only used on production anyway.
  5. @Baiqiang, @SAuroux bump =) Suggested fix: just remove the view, if it's no longer necessary.
  6. I cannot think of anything reasonable, it may be a side effect of playing with the charset/collation, which redefined both nullability and the default value. Suggested fix: a simple migration changing it to NOT NULL.
SAuroux commented 7 years ago

I was not involved in the execution of populating the championships list. @Baiqiang ?

jfly commented 7 years ago

  1. @jonatanklosko, what do you mean by "implicit migration"? Perhaps you mean "explicit migration"? If so, then yes, I like that. I say let's go with production, which means changing the gender column in InboxPersons and Persons to be a char instead of a varchar. I guess we don't even need a migration for this, just a PR to update structure.sql.

  2. I like @jonatanklosko's suggestion.

... The only thing I am confused by is why it took so long for the table to end up in our git structure.sql.

I think I can answer this: all us developers have gotten in the habit of running git add -p and ignoring most changes to structure.sql except for the ones we know we added. #1505 was so massive that I didn't bother trying to do a git add -p, I just added the brand new structure.sql, which brought in the new ar_internal_metadata table.

Suggested fix: not sure what would be the best, we could drop the production version of the table and let it be recreated on the next migration (hopefully with the collation we have defined in structure.sql). Maybe someone have a better idea for a clean fix?

I tried this out on staging and on my local development environment, and I ended up with the same two different ar_internal_metadata tables. Perhaps this has to do with differences in the two mysql servers.

  1. I like @jonatanklosko's suggestion.

  2. Ping @Baiqiang!

  3. I like @jonatanklosko's suggestion.