Archive and drop education program (ep_*) tables on all wikis
Open, NormalPublic

Description

Placeholder task until I find more wikis this applies to. The list so far:

  • legalteamwiki (disabled, tables already empty)

Cf: T125618: Deprecate and remove the EducationProgram extension from Wikimedia servers after June 30, 2018

From: T200316
Can we table SQL dumps of the ep_ tables (only course_token seems to be private data we don't expose on labs replicas) on all the wikis that have education program, publish them to somewhere like https://dumps.wikimedia.org/other/wep/ before we clean up and remove the tables from wikis (and then labs replicas)?

Deletion should probably wait a little while, but filing as a cleanup task as part of removing the extension

See also: T200316#4449887

demon created this task.Sep 1 2017, 3:30 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptSep 1 2017, 3:30 PM
demon removed a project: Epic.Sep 1 2017, 3:30 PM
Marostegui moved this task from Triage to Backlog on the DBA board.Sep 1 2017, 3:32 PM
Marostegui updated the task description. (Show Details)
jcrespo added subscribers: ArielGlenn, jcrespo.

@ArielGlenn What is the process for one time dumps, if there is one?

Does security team need to be involved?

We can dump easily, but what to do next (and before deletion)?

We've never had requests for specific tables like these; the ep tables aren't dumped as part of the regular dumps either.

If you want to produce them and put them somewhere, I can grab them, put them in the right directory (other/something) and set up a web page in puppet that describes them, as a one-off; we do this from time to time for various datasets.

Actually now that these datasets live on labstore1006/7, I should probably loop @Bstorm into this, as the point person for these hosts. I imagine the procedure will be similar.

Does security team need to be involved?

I don't think so.

As long as we NULL out the course token in the same way that it is on the labs repslicas... Though, considering the extension is going to be undeployed, and therefore the tokens are unuseable... It's not a big deal.

I don't see any other problematic data in the tables

As long as we NULL out the course token in the same way that it is on the labs replicas

Then this is not as trivial- it seems the best way it to export them from cloud replicas and then pass them to Data-Services so they can store them in the appropriate place in labstore1006/7.

Reedy renamed this task from Drop education program (ep_*) tables on wikis where it is not enabled to Drop education program (ep_*) tables on all wikis.Jul 26 2018, 12:40 AM
Reedy renamed this task from Drop education program (ep_*) tables on all wikis to Archive and drop education program (ep_*) tables on all wikis.Jul 26 2018, 1:08 AM

See also T200391: Cleaning up after EducationProgram

If we're going to make some archive... Should we be dumping stuff in NS 446 and 447 too?

Reedy added a comment.Jul 29 2018, 1:59 AM

As long as we NULL out the course token in the same way that it is on the labs replicas

Then this is not as trivial- it seems the best way it to export them from cloud replicas and then pass them to Data-Services so they can store them in the appropriate place in labstore1006/7.

Is there a nice way to get mysqldump to play with views and their data?

mysqldump --defaults-file=/home/reedy/replica.my.cnf --lock-tables=false -h enwiki.analytics.db.svc.eqiad.wmflabs enwiki_p ep_orgs ep_courses ep_articles ep_users_per_course ep_students ep_instructors ep_cas ep_oas ep_events ep_revisions > ep-enwiki.sql

ends up with...

-- MySQL dump 10.14  Distrib 5.5.59-MariaDB, for debian-linux-gnu (x86_64)
--
-- Host: enwiki.analytics.db.svc.eqiad.wmflabs    Database: enwiki_p
-- ------------------------------------------------------
-- Server version	10.1.33-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Temporary table structure for view `ep_orgs`
--

DROP TABLE IF EXISTS `ep_orgs`;
/*!50001 DROP VIEW IF EXISTS `ep_orgs`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `ep_orgs` (
  `org_id` tinyint NOT NULL,
  `org_name` tinyint NOT NULL,
  `org_city` tinyint NOT NULL,
  `org_country` tinyint NOT NULL,
  `org_active` tinyint NOT NULL,
  `org_course_count` tinyint NOT NULL,
  `org_instructor_count` tinyint NOT NULL,
  `org_oa_count` tinyint NOT NULL,
  `org_ca_count` tinyint NOT NULL,
  `org_student_count` tinyint NOT NULL,
  `org_courses` tinyint NOT NULL,
  `org_touched` tinyint NOT NULL,
  `org_last_active_date` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Temporary table structure for view `ep_courses`
--

DROP TABLE IF EXISTS `ep_courses`;
/*!50001 DROP VIEW IF EXISTS `ep_courses`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `ep_courses` (
  `course_id` tinyint NOT NULL,
  `course_org_id` tinyint NOT NULL,
  `course_title` tinyint NOT NULL,
  `course_name` tinyint NOT NULL,
  `course_start` tinyint NOT NULL,
  `course_end` tinyint NOT NULL,
  `course_description` tinyint NOT NULL,
  `course_students` tinyint NOT NULL,
  `course_online_ambs` tinyint NOT NULL,
  `course_campus_ambs` tinyint NOT NULL,
  `course_instructors` tinyint NOT NULL,
  `course_token` tinyint NOT NULL,
  `course_field` tinyint NOT NULL,
  `course_level` tinyint NOT NULL,
  `course_term` tinyint NOT NULL,
  `course_lang` tinyint NOT NULL,
  `course_instructor_count` tinyint NOT NULL,
  `course_oa_count` tinyint NOT NULL,
  `course_ca_count` tinyint NOT NULL,
  `course_student_count` tinyint NOT NULL,
  `course_touched` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Temporary table structure for view `ep_articles`
--

DROP TABLE IF EXISTS `ep_articles`;
/*!50001 DROP VIEW IF EXISTS `ep_articles`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `ep_articles` (
  `article_id` tinyint NOT NULL,
  `article_user_id` tinyint NOT NULL,
  `article_course_id` tinyint NOT NULL,
  `article_page_id` tinyint NOT NULL,
  `article_page_title` tinyint NOT NULL,
  `article_reviewers` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Temporary table structure for view `ep_users_per_course`
--

DROP TABLE IF EXISTS `ep_users_per_course`;
/*!50001 DROP VIEW IF EXISTS `ep_users_per_course`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `ep_users_per_course` (
  `upc_user_id` tinyint NOT NULL,
  `upc_course_id` tinyint NOT NULL,
  `upc_role` tinyint NOT NULL,
  `upc_time` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Temporary table structure for view `ep_students`
--

DROP TABLE IF EXISTS `ep_students`;
/*!50001 DROP VIEW IF EXISTS `ep_students`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `ep_students` (
  `student_id` tinyint NOT NULL,
  `student_user_id` tinyint NOT NULL,
  `student_first_enroll` tinyint NOT NULL,
  `student_first_course` tinyint NOT NULL,
  `student_last_enroll` tinyint NOT NULL,
  `student_last_course` tinyint NOT NULL,
  `student_last_active` tinyint NOT NULL,
  `student_active_enroll` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Temporary table structure for view `ep_instructors`
--

DROP TABLE IF EXISTS `ep_instructors`;
/*!50001 DROP VIEW IF EXISTS `ep_instructors`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `ep_instructors` (
  `instructor_id` tinyint NOT NULL,
  `instructor_user_id` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Temporary table structure for view `ep_cas`
--

DROP TABLE IF EXISTS `ep_cas`;
/*!50001 DROP VIEW IF EXISTS `ep_cas`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `ep_cas` (
  `ca_id` tinyint NOT NULL,
  `ca_user_id` tinyint NOT NULL,
  `ca_visible` tinyint NOT NULL,
  `ca_bio` tinyint NOT NULL,
  `ca_photo` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Temporary table structure for view `ep_oas`
--

DROP TABLE IF EXISTS `ep_oas`;
/*!50001 DROP VIEW IF EXISTS `ep_oas`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `ep_oas` (
  `oa_id` tinyint NOT NULL,
  `oa_user_id` tinyint NOT NULL,
  `oa_visible` tinyint NOT NULL,
  `oa_bio` tinyint NOT NULL,
  `oa_photo` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Temporary table structure for view `ep_events`
--

DROP TABLE IF EXISTS `ep_events`;
/*!50001 DROP VIEW IF EXISTS `ep_events`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `ep_events` (
  `event_id` tinyint NOT NULL,
  `event_course_id` tinyint NOT NULL,
  `event_user_id` tinyint NOT NULL,
  `event_time` tinyint NOT NULL,
  `event_type` tinyint NOT NULL,
  `event_info` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Temporary table structure for view `ep_revisions`
--

DROP TABLE IF EXISTS `ep_revisions`;
/*!50001 DROP VIEW IF EXISTS `ep_revisions`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `ep_revisions` (
  `rev_id` tinyint NOT NULL,
  `rev_object_id` tinyint NOT NULL,
  `rev_object_identifier` tinyint NOT NULL,
  `rev_type` tinyint NOT NULL,
  `rev_comment` tinyint NOT NULL,
  `rev_user_id` tinyint NOT NULL,
  `rev_user_text` tinyint NOT NULL,
  `rev_time` tinyint NOT NULL,
  `rev_minor_edit` tinyint NOT NULL,
  `rev_deleted` tinyint NOT NULL,
  `rev_data` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Final view structure for view `ep_orgs`
--

/*!50001 DROP TABLE IF EXISTS `ep_orgs`*/;
/*!50001 DROP VIEW IF EXISTS `ep_orgs`*/;
/*!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      = utf8 */;
/*!50001 SET character_set_results     = utf8 */;
/*!50001 SET collation_connection      = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`viewmaster`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `ep_orgs` AS select `enwiki`.`ep_orgs`.`org_id` AS `org_id`,`enwiki`.`ep_orgs`.`org_name` AS `org_name`,`enwiki`.`ep_orgs`.`org_city` AS `org_city`,`enwiki`.`ep_orgs`.`org_country` AS `org_country`,`enwiki`.`ep_orgs`.`org_active` AS `org_active`,`enwiki`.`ep_orgs`.`org_course_count` AS `org_course_count`,`enwiki`.`ep_orgs`.`org_instructor_count` AS `org_instructor_count`,`enwiki`.`ep_orgs`.`org_oa_count` AS `org_oa_count`,`enwiki`.`ep_orgs`.`org_ca_count` AS `org_ca_count`,`enwiki`.`ep_orgs`.`org_student_count` AS `org_student_count`,`enwiki`.`ep_orgs`.`org_courses` AS `org_courses`,`enwiki`.`ep_orgs`.`org_touched` AS `org_touched`,`enwiki`.`ep_orgs`.`org_last_active_date` AS `org_last_active_date` from `enwiki`.`ep_orgs` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

--
-- Final view structure for view `ep_courses`
--

/*!50001 DROP TABLE IF EXISTS `ep_courses`*/;
/*!50001 DROP VIEW IF EXISTS `ep_courses`*/;
/*!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      = utf8 */;
/*!50001 SET character_set_results     = utf8 */;
/*!50001 SET collation_connection      = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`viewmaster`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `ep_courses` AS select `enwiki`.`ep_courses`.`course_id` AS `course_id`,`enwiki`.`ep_courses`.`course_org_id` AS `course_org_id`,`enwiki`.`ep_courses`.`course_title` AS `course_title`,`enwiki`.`ep_courses`.`course_name` AS `course_name`,`enwiki`.`ep_courses`.`course_start` AS `course_start`,`enwiki`.`ep_courses`.`course_end` AS `course_end`,`enwiki`.`ep_courses`.`course_description` AS `course_description`,`enwiki`.`ep_courses`.`course_students` AS `course_students`,`enwiki`.`ep_courses`.`course_online_ambs` AS `course_online_ambs`,`enwiki`.`ep_courses`.`course_campus_ambs` AS `course_campus_ambs`,`enwiki`.`ep_courses`.`course_instructors` AS `course_instructors`,NULL AS `course_token`,`enwiki`.`ep_courses`.`course_field` AS `course_field`,`enwiki`.`ep_courses`.`course_level` AS `course_level`,`enwiki`.`ep_courses`.`course_term` AS `course_term`,`enwiki`.`ep_courses`.`course_lang` AS `course_lang`,`enwiki`.`ep_courses`.`course_instructor_count` AS `course_instructor_count`,`enwiki`.`ep_courses`.`course_oa_count` AS `course_oa_count`,`enwiki`.`ep_courses`.`course_ca_count` AS `course_ca_count`,`enwiki`.`ep_courses`.`course_student_count` AS `course_student_count`,`enwiki`.`ep_courses`.`course_touched` AS `course_touched` from `enwiki`.`ep_courses` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

--
-- Final view structure for view `ep_articles`
--

/*!50001 DROP TABLE IF EXISTS `ep_articles`*/;
/*!50001 DROP VIEW IF EXISTS `ep_articles`*/;
/*!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      = utf8 */;
/*!50001 SET character_set_results     = utf8 */;
/*!50001 SET collation_connection      = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`viewmaster`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `ep_articles` AS select `enwiki`.`ep_articles`.`article_id` AS `article_id`,`enwiki`.`ep_articles`.`article_user_id` AS `article_user_id`,`enwiki`.`ep_articles`.`article_course_id` AS `article_course_id`,`enwiki`.`ep_articles`.`article_page_id` AS `article_page_id`,`enwiki`.`ep_articles`.`article_page_title` AS `article_page_title`,`enwiki`.`ep_articles`.`article_reviewers` AS `article_reviewers` from `enwiki`.`ep_articles` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

--
-- Final view structure for view `ep_users_per_course`
--

/*!50001 DROP TABLE IF EXISTS `ep_users_per_course`*/;
/*!50001 DROP VIEW IF EXISTS `ep_users_per_course`*/;
/*!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      = utf8 */;
/*!50001 SET character_set_results     = utf8 */;
/*!50001 SET collation_connection      = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`viewmaster`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `ep_users_per_course` AS select `enwiki`.`ep_users_per_course`.`upc_user_id` AS `upc_user_id`,`enwiki`.`ep_users_per_course`.`upc_course_id` AS `upc_course_id`,`enwiki`.`ep_users_per_course`.`upc_role` AS `upc_role`,`enwiki`.`ep_users_per_course`.`upc_time` AS `upc_time` from `enwiki`.`ep_users_per_course` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

--
-- Final view structure for view `ep_students`
--

/*!50001 DROP TABLE IF EXISTS `ep_students`*/;
/*!50001 DROP VIEW IF EXISTS `ep_students`*/;
/*!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      = utf8 */;
/*!50001 SET character_set_results     = utf8 */;
/*!50001 SET collation_connection      = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`viewmaster`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `ep_students` AS select `enwiki`.`ep_students`.`student_id` AS `student_id`,`enwiki`.`ep_students`.`student_user_id` AS `student_user_id`,`enwiki`.`ep_students`.`student_first_enroll` AS `student_first_enroll`,`enwiki`.`ep_students`.`student_first_course` AS `student_first_course`,`enwiki`.`ep_students`.`student_last_enroll` AS `student_last_enroll`,`enwiki`.`ep_students`.`student_last_course` AS `student_last_course`,`enwiki`.`ep_students`.`student_last_active` AS `student_last_active`,`enwiki`.`ep_students`.`student_active_enroll` AS `student_active_enroll` from `enwiki`.`ep_students` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

--
-- Final view structure for view `ep_instructors`
--

/*!50001 DROP TABLE IF EXISTS `ep_instructors`*/;
/*!50001 DROP VIEW IF EXISTS `ep_instructors`*/;
/*!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      = utf8 */;
/*!50001 SET character_set_results     = utf8 */;
/*!50001 SET collation_connection      = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`viewmaster`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `ep_instructors` AS select `enwiki`.`ep_instructors`.`instructor_id` AS `instructor_id`,`enwiki`.`ep_instructors`.`instructor_user_id` AS `instructor_user_id` from `enwiki`.`ep_instructors` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

--
-- Final view structure for view `ep_cas`
--

/*!50001 DROP TABLE IF EXISTS `ep_cas`*/;
/*!50001 DROP VIEW IF EXISTS `ep_cas`*/;
/*!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      = utf8 */;
/*!50001 SET character_set_results     = utf8 */;
/*!50001 SET collation_connection      = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`viewmaster`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `ep_cas` AS select `enwiki`.`ep_cas`.`ca_id` AS `ca_id`,`enwiki`.`ep_cas`.`ca_user_id` AS `ca_user_id`,`enwiki`.`ep_cas`.`ca_visible` AS `ca_visible`,`enwiki`.`ep_cas`.`ca_bio` AS `ca_bio`,`enwiki`.`ep_cas`.`ca_photo` AS `ca_photo` from `enwiki`.`ep_cas` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

--
-- Final view structure for view `ep_oas`
--

/*!50001 DROP TABLE IF EXISTS `ep_oas`*/;
/*!50001 DROP VIEW IF EXISTS `ep_oas`*/;
/*!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      = utf8 */;
/*!50001 SET character_set_results     = utf8 */;
/*!50001 SET collation_connection      = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`viewmaster`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `ep_oas` AS select `enwiki`.`ep_oas`.`oa_id` AS `oa_id`,`enwiki`.`ep_oas`.`oa_user_id` AS `oa_user_id`,`enwiki`.`ep_oas`.`oa_visible` AS `oa_visible`,`enwiki`.`ep_oas`.`oa_bio` AS `oa_bio`,`enwiki`.`ep_oas`.`oa_photo` AS `oa_photo` from `enwiki`.`ep_oas` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

--
-- Final view structure for view `ep_events`
--

/*!50001 DROP TABLE IF EXISTS `ep_events`*/;
/*!50001 DROP VIEW IF EXISTS `ep_events`*/;
/*!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      = utf8 */;
/*!50001 SET character_set_results     = utf8 */;
/*!50001 SET collation_connection      = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`viewmaster`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `ep_events` AS select `enwiki`.`ep_events`.`event_id` AS `event_id`,`enwiki`.`ep_events`.`event_course_id` AS `event_course_id`,`enwiki`.`ep_events`.`event_user_id` AS `event_user_id`,`enwiki`.`ep_events`.`event_time` AS `event_time`,`enwiki`.`ep_events`.`event_type` AS `event_type`,`enwiki`.`ep_events`.`event_info` AS `event_info` from `enwiki`.`ep_events` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

--
-- Final view structure for view `ep_revisions`
--

/*!50001 DROP TABLE IF EXISTS `ep_revisions`*/;
/*!50001 DROP VIEW IF EXISTS `ep_revisions`*/;
/*!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      = utf8 */;
/*!50001 SET character_set_results     = utf8 */;
/*!50001 SET collation_connection      = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`viewmaster`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `ep_revisions` AS select `enwiki`.`ep_revisions`.`rev_id` AS `rev_id`,`enwiki`.`ep_revisions`.`rev_object_id` AS `rev_object_id`,`enwiki`.`ep_revisions`.`rev_object_identifier` AS `rev_object_identifier`,`enwiki`.`ep_revisions`.`rev_type` AS `rev_type`,`enwiki`.`ep_revisions`.`rev_comment` AS `rev_comment`,`enwiki`.`ep_revisions`.`rev_user_id` AS `rev_user_id`,`enwiki`.`ep_revisions`.`rev_user_text` AS `rev_user_text`,`enwiki`.`ep_revisions`.`rev_time` AS `rev_time`,`enwiki`.`ep_revisions`.`rev_minor_edit` AS `rev_minor_edit`,`enwiki`.`ep_revisions`.`rev_deleted` AS `rev_deleted`,`enwiki`.`ep_revisions`.`rev_data` AS `rev_data` from `enwiki`.`ep_revisions` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2018-07-29  1:55:49

With course_token you mean that column on the ep_courses table?
If that is the case, there is no need to play around with the views for mysqldump, as that table has course_token already filtered on sanitariums with the triggers:

| ep_courses_insert       | INSERT | ep_courses       | SET NEW.course_token = ''
| ep_courses_update       | UPDATE | ep_courses       | SET NEW.course_token = ''

And they are correctly sanitized on labs:

mysql:root@localhost [enwiki]> select * from ep_courses where course_token != '';
Empty set (0.02 sec)

So the mysqldump can be done directly on the database not on the views if that helps.

Easy done then :)

jcrespo moved this task from Backlog to Next on the DBA board.Sep 17 2018, 4:21 PM

I may have time to work on this these week (the dumping) not sure if they will be available shortly after, as not sure what that entails for custom dumps.

Reedy added a comment.Sep 17 2018, 4:35 PM

I may have time to work on this these week (the dumping) not sure if they will be available shortly after, as not sure what that entails for custom dumps.

Should just be a case of making sure they're named well, gzip (or similar) and then should be fairly easy to get them into the right place for people to access

Should just be a case of making sure they're named well, gzip (or similar)

I can do that.

should be fairly easy to get them into the right place for people to access

I will put it available to other global roots so the right people can do that, will notify ticket here.

Reedy added a comment.Sep 17 2018, 4:39 PM

Thanks! :)

If you want to produce them and put them somewhere, I can grab them, put them in the right directory (other/something) and set up a web page in puppet that describes them, as a one-off; we do this from time to time for various datasets.

jcrespo moved this task from Next to In progress on the DBA board.
jcrespo added a comment.EditedSep 18 2018, 11:20 AM

List of tables to archive:

root@labsdb1010:~$ mysql -BN --skip-ssl -e "select table_schema, table_name FROM information_schema.tables WHERE table_schema like '%wik%' and table_name like 'ep\_%' and table_schema not like '%\_p'"
arwiki  ep_articles
arwiki  ep_cas
arwiki  ep_courses
arwiki  ep_events
arwiki  ep_instructors
arwiki  ep_oas
arwiki  ep_orgs
arwiki  ep_revisions
arwiki  ep_students
arwiki  ep_users_per_course
cawiki  ep_articles
cawiki  ep_cas
cawiki  ep_courses
cawiki  ep_events
cawiki  ep_instructors
cawiki  ep_oas
cawiki  ep_orgs
cawiki  ep_revisions
cawiki  ep_students
cawiki  ep_users_per_course
cswiki  ep_articles
cswiki  ep_cas
cswiki  ep_courses
cswiki  ep_events
cswiki  ep_instructors
cswiki  ep_oas
cswiki  ep_orgs
cswiki  ep_revisions
cswiki  ep_students
cswiki  ep_users_per_course
dewikiversity   ep_articles
dewikiversity   ep_cas
dewikiversity   ep_courses
dewikiversity   ep_events
dewikiversity   ep_instructors
dewikiversity   ep_oas
dewikiversity   ep_orgs
dewikiversity   ep_revisions
dewikiversity   ep_students
dewikiversity   ep_users_per_course
elwiki  ep_articles
elwiki  ep_cas
elwiki  ep_courses
elwiki  ep_events
elwiki  ep_instructors
elwiki  ep_oas
elwiki  ep_orgs
elwiki  ep_revisions
elwiki  ep_students
elwiki  ep_users_per_course
enwiki  ep_articles
enwiki  ep_cas
enwiki  ep_courses
enwiki  ep_events
enwiki  ep_instructors
enwiki  ep_oas
enwiki  ep_orgs
enwiki  ep_revisions
enwiki  ep_students
enwiki  ep_users_per_course
enwikinews      ep_articles
enwikinews      ep_cas
enwikinews      ep_courses
enwikinews      ep_events
enwikinews      ep_instructors
enwikinews      ep_oas
enwikinews      ep_orgs
enwikinews      ep_revisions
enwikinews      ep_students
enwikinews      ep_users_per_course
eswiki  ep_articles
eswiki  ep_cas
eswiki  ep_courses
eswiki  ep_events
eswiki  ep_instructors
eswiki  ep_oas
eswiki  ep_orgs
eswiki  ep_revisions
eswiki  ep_students
eswiki  ep_users_per_course
fawiki  ep_articles
fawiki  ep_cas
fawiki  ep_courses
fawiki  ep_events
fawiki  ep_instructors
fawiki  ep_oas
fawiki  ep_orgs
fawiki  ep_revisions
fawiki  ep_students
fawiki  ep_users_per_course
frwikisource    ep_articles
frwikisource    ep_cas
frwikisource    ep_courses
frwikisource    ep_events
frwikisource    ep_instructors
frwikisource    ep_oas
frwikisource    ep_orgs
frwikisource    ep_revisions
frwikisource    ep_students
frwikisource    ep_users_per_course
hewiki  ep_articles
hewiki  ep_cas
hewiki  ep_courses
hewiki  ep_events
hewiki  ep_instructors
hewiki  ep_oas
hewiki  ep_orgs
hewiki  ep_revisions
hewiki  ep_students
hewiki  ep_users_per_course
hewiktionary    ep_articles
hewiktionary    ep_cas
hewiktionary    ep_courses
hewiktionary    ep_events
hewiktionary    ep_instructors
hewiktionary    ep_oas
hewiktionary    ep_orgs
hewiktionary    ep_revisions
hewiktionary    ep_students
hewiktionary    ep_users_per_course
itwikiversity   ep_articles
itwikiversity   ep_cas
itwikiversity   ep_courses
itwikiversity   ep_events
itwikiversity   ep_instructors
itwikiversity   ep_oas
itwikiversity   ep_orgs
itwikiversity   ep_revisions
itwikiversity   ep_students
itwikiversity   ep_users_per_course
lvwiki  ep_articles
lvwiki  ep_cas
lvwiki  ep_courses
lvwiki  ep_events
lvwiki  ep_instructors
lvwiki  ep_oas
lvwiki  ep_orgs
lvwiki  ep_revisions
lvwiki  ep_students
lvwiki  ep_users_per_course
mkwiki  ep_articles
mkwiki  ep_cas
mkwiki  ep_courses
mkwiki  ep_events
mkwiki  ep_instructors
mkwiki  ep_oas
mkwiki  ep_orgs
mkwiki  ep_revisions
mkwiki  ep_students
mkwiki  ep_users_per_course
nlwiki  ep_articles
nlwiki  ep_cas
nlwiki  ep_courses
nlwiki  ep_events
nlwiki  ep_instructors
nlwiki  ep_oas
nlwiki  ep_orgs
nlwiki  ep_revisions
nlwiki  ep_students
nlwiki  ep_users_per_course
ptwiki  ep_articles
ptwiki  ep_cas
ptwiki  ep_courses
ptwiki  ep_events
ptwiki  ep_instructors
ptwiki  ep_oas
ptwiki  ep_orgs
ptwiki  ep_revisions
ptwiki  ep_students
ptwiki  ep_users_per_course
ruwiki  ep_articles
ruwiki  ep_cas
ruwiki  ep_courses
ruwiki  ep_events
ruwiki  ep_instructors
ruwiki  ep_oas
ruwiki  ep_orgs
ruwiki  ep_revisions
ruwiki  ep_students
ruwiki  ep_users_per_course
sewiki  ep_articles
sewiki  ep_cas
sewiki  ep_courses
sewiki  ep_events
sewiki  ep_instructors
sewiki  ep_oas
sewiki  ep_orgs
sewiki  ep_revisions
sewiki  ep_students
sewiki  ep_users_per_course
srwiki  ep_articles
srwiki  ep_cas
srwiki  ep_courses
srwiki  ep_events
srwiki  ep_instructors
srwiki  ep_oas
srwiki  ep_orgs
srwiki  ep_revisions
srwiki  ep_students
srwiki  ep_users_per_course
svwiki  ep_articles
svwiki  ep_cas
svwiki  ep_courses
svwiki  ep_events
svwiki  ep_instructors
svwiki  ep_oas
svwiki  ep_orgs
svwiki  ep_revisions
svwiki  ep_students
svwiki  ep_users_per_course
ukwiki  ep_articles
ukwiki  ep_cas
ukwiki  ep_courses
ukwiki  ep_events
ukwiki  ep_instructors
ukwiki  ep_oas
ukwiki  ep_orgs
ukwiki  ep_revisions
ukwiki  ep_students
ukwiki  ep_users_per_course

Because it is almost a random list, this is not trivial- not difficult either, but needs to make sure I don't miss anything.

@Reedy legalteamwiki that you mention on your original task will not be on this list, as these are only public wikis, those should be directly dropped?

Reedy added a comment.Sep 18 2018, 2:16 PM

@Reedy legalteamwiki that you mention on your original task will not be on this list, as these are only public wikis, those should be directly dropped?

Yeah, that was seemingly installed for some testing purposes. But never used. Those can just be dropped.

The tables cannot be dropped from other wikis just yet, as the extension is still enabled (but mostly disabled) on other wikis.

So around the time we archive (sometime this week? we didn't want to disable without giving people the data to access), we should look at turning it off properly on all wikis, then look at undeploying it.

The tables cannot be dropped from other wikis just yet, as the extension is still enabled (but mostly disabled) on other wikis.

Thank you for the clarification, I thought the "deletion will take a while" was outdated, I don't delete anything without proper confirmation, but that makes it more clear.

jcrespo moved this task from In progress to Done on the DBA board.EditedSep 19 2018, 3:07 PM
jcrespo removed jcrespo as the assignee of this task.

So public dumps are ready:

root@labsdb1010:/srv/tmp/ep_dumps$ rm *; mysql -BN --skip-ssl -e "select table_schema, table_name FROM information_schema.tables WHERE table_schema like '%wik%' and table_name like 'ep\_%' and table_schema not like '%\_p'" | while read db table; do echo "Dumping $db.$table..."; mysqldump --skip-ssl --single-transaction $db $table | pv | pigz -c > $db.$table.$(date +%Y%m%d).gz; done
root@labsdb1010:/srv/tmp$ tar -cvf ep_dumps.tar ep_dumps

To examine those, tar xf ep_dumps.tar && zless ep_dumps/arwiki.ep_articles.20180919.gz
To load those into a database, tar xf ep_dumps.tar && zcat ep_dumps/* | mysql database

I (or any other root) can put those anywhere within our infrastructure by running:

root@neodymium:~$ /home/jynus/wmfmariadbpy/wmfmariadbpy/transfer.py labsdb1010.eqiad.wmnet:/srv/tmp/ep_dumps.tar <target_host>:<target_dir>

It would be nice if someone could check one of the databases to make sure nothing was private beyond the token- which shouldn't be an issue, as all these were already public on wikireplicas, but just in case (I checked one of them and saw nothing weird).

(I will come back to this when actual drops can be done.)

CC @ArielGlenn @Reedy

Thanks!

Can someone put them somewhere I can actually get them? As I don't believe I actually have access to labsdb1010 (via ssh anyway) :)

Not overly fussed where, whether it's deploy1001, a bastion, or similar. That way I can get them sanity checked, and then we can get them into place and then move on with getting EP undeployed

The file is at: deploy1001:/home/reedy/ep_dumps.tar

Reedy updated the task description. (Show Details)Sep 25 2018, 12:26 PM

Dumps LGTM

My only question would be about how we package them...

One big tar solves the problem, but chances are they won't want every wikis EP. Individual table files are annoying for downloading

So, I guess a tar.gz for each project/wiki is enough, which I've now done on deploy1001

Change 462691 had a related patch set uploaded (by Reedy; owner: Reedy):
[operations/puppet@production] Add a place to link Education Program extension sql dumps

https://gerrit.wikimedia.org/r/462691

Hey @Bstorm the dumps that @jcrespo did LGTM, but I have repackaged them slightly to make them easier for end users to download

I've added a puppet patch above (462691) to add the link to https://dumps.wikimedia.org/other/ so I just need someone to create an "educationprogram" folder on the labstore hosts in the appropriate place, and then copy deploy1001:/home/reedy/ep_dumps_repackaged.tar over to the labstore host, and extract the contents into the educationprogram folder...

I guess just then some rsyncing (do 1006 and 1007 replicate between themselves?) and then this chapter should be just about done :)

@ArielGlenn, there's a lot going on with the rsyncing on dumps servers. I imagine that there's some kind of notion of what gets synced and to-where somewhere in that mess. I'll take a look around, but if you know off the top of your head how something like this normally would get added, I'm all ears :)

The files are in place now on both servers.

Change 462691 merged by Bstorm:
[operations/puppet@production] Add a place to link Education Program extension sql dumps

https://gerrit.wikimedia.org/r/462691

Change 462726 had a related patch set uploaded (by Bstorm; owner: Bstorm):
[operations/puppet@production] dumps: add initial slash to url path

https://gerrit.wikimedia.org/r/462726

Change 462726 merged by Bstorm:
[operations/puppet@production] dumps: add initial slash to url path

https://gerrit.wikimedia.org/r/462726

Reedy added a comment.Sep 25 2018, 2:54 PM

Looks right to me! Thanks! :)

What is pending here?

  1. Disable the extension everywhere?
  2. Get a list of wikis where it is/was disabled?
  3. Do the proper table DROP?
Reedy added a subscriber: Urbanecm.Sep 28 2018, 5:58 PM

What is pending here?

  1. Disable the extension everywhere?

That's done

  1. Get a list of wikis where it is/was disabled?
arwiki cawiki cswiki dewikiversity elwiki enwiki enwikinews eswiki fawiki frwikisource hewiki hewiktionary itwikiversity lvwiki mkwiki nlwiki ptwiki ruwiki srwiki svwiki ukwiki legalteamwiki
  1. Do the proper table DROP?

Eventually. I'm not sure whether we should hold off on doing this for a little while, as in T188407#4620016 @Urbanecm is using them to do some "exports" to wikitext type pages.

Granted, it wouldn't be too much effort to ask him to import the SQL dumps and do it that way, but is a little more work for him

Urbanecm added a comment.EditedSep 29 2018, 6:01 PM

I think we should wait for some time before dropping the data. I exported the data from EP to wikitext pages on cswiki (as I know the expected page naming there). Maybe other wikis would like to use the same (or similar) solution as well - I don't know. If some wiki wants me to, I'm willing to export EP data to wikitext pages for them as well - but I'm currently not going to ask them about it.

Thanks guys!
Just to clarify, I didn't mean to delete anything just yet, just wanted to know what was pending (specially for the DBA side) :-)
Will not start any drops until you guys are comfortable with it!
Thanks!

bd808 moved this task from Backlog to Wiki replicas on the Data-Services board.Nov 2 2018, 11:38 PM
greg added a subscriber: greg.Nov 8 2018, 8:24 PM

I think we should wait for some time before dropping the data. I exported the data from EP to wikitext pages on cswiki (as I know the expected page naming there). Maybe other wikis would like to use the same (or similar) solution as well - I don't know. If some wiki wants me to, I'm willing to export EP data to wikitext pages for them as well - but I'm currently not going to ask them about it.

What would the expected timeline be for us to wait on that? eg: what is "some time"? :)

Personally, I have no problem with dropping the data - I have transfered the data for cs.wiki and I don't care much about other wikis. What about sending a notification via User-notice and drop the data after a month since the notice was posted?

What about sending a notification via #user-notice and drop the data after a month since the notice was posted?

Agreed

Suggested wording for the notice: "Tables used by nowadays archived extension Education Program will be archived and dropped after a month of this notice. If you want the data to be easily accessible, we recommend dumping them to wikitext pages. ".

greg added a comment.Fri, Nov 16, 10:07 PM

So, basically the end of the year now. Thanks @Johan !

Izno added a subscriber: Izno.Tue, Nov 20, 1:49 AM
Restricted Application added a subscriber: alanajjar. · View Herald TranscriptTue, Nov 20, 8:12 AM
Jony added a subscriber: Jony.Tue, Nov 20, 5:23 PM
NickK added a subscriber: NickK.Wed, Nov 21, 12:22 AM

Is it there a guide on how to make a wikitext export? I would need it for ukwiki.

Please also note that wikis that used the extension this year might need this data longer, e.g. for annual affiliate reporting. Deletion in December might not be a good idea from this point of view.