Page MenuHomePhabricator
Create Task
Maniphest T108857

Delete obsolete schemas {tick} [5 pts]
Closed, ResolvedPublic
Actions

Description

For those schemas marked as obsolete, EL audit spreadsheet column J:
https://docs.google.com/spreadsheets/d/1mQtbsbGHLbGsHeNaYFCdg6X4K1VSxjUStBpx4GmVbhk/edit#gid=1610723354

  • Drop all schema tables
  • Delete the schema wiki page and talk page

Related Objects
Search...

Event Timeline

mforns created this task.Aug 12 2015, 6:56 PM
mforns raised the priority of this task from to Needs Triage.
mforns updated the task description. (Show Details)
mforns added a project: Analytics-Backlog.
mforns subscribed.
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptAug 12 2015, 6:56 PM
mforns mentioned this in T104877: Enforce policy for each schema: Sanitize {tick} [8 pts].Aug 14 2015, 3:06 PM
mforns set Security to None.Aug 19 2015, 10:19 PM
mforns added subscribers: • Springle, • kevinator, • madhuvishy.
• kevinator triaged this task as High priority.Aug 20 2015, 5:28 PM
• kevinator moved this task from Incoming to Prioritized on the Analytics-Backlog board.
• kevinator moved this task from Prioritized to Blocked on the Analytics-Backlog board.
mforns added a parent task: T104877: Enforce policy for each schema: Sanitize {tick} [8 pts].Aug 25 2015, 4:38 PM
mforns claimed this task.Sep 8 2015, 3:20 PM
mforns edited projects, added Analytics-Kanban; removed Analytics-Backlog.

Here's the list of the schemas that should be permanently deleted:

Campaigns_5485644
Campaigns_5487321
Analytics_5751947
NewEditorMilestone_6538838
PerformanceMetric_6757313
AccountCreation_4933341
• kevinator closed subtask T110247: Archive obsolete schema pages {tick} [5 pts] as Resolved.Sep 8 2015, 10:25 PM
mforns reassigned this task from mforns to jcrespo.Sep 9 2015, 12:44 AM

@jcrespo
Hey, I also assigned this task to you, as we combined.
Thanks!

mforns moved this task from Next Up to Paused on the Analytics-Kanban board.Sep 9 2015, 12:45 AM
jcrespo raised the priority of this task from High to Needs Triage.Sep 9 2015, 10:45 AM
• ggellerman edited projects, added Analytics-Backlog; removed Analytics-Kanban.Sep 9 2015, 3:36 PM
• ggellerman moved this task from Blocked to Radar on the Analytics-Backlog board.
jcrespo triaged this task as Medium priority.Sep 29 2015, 9:46 AM
jcrespo added a project: DBA.
jcrespo moved this task from Triage to In progress on the DBA board.
Krenair subscribed.Sep 29 2015, 10:16 AM
jcrespo added a comment.Sep 29 2015, 10:57 AM

@mforns These are the tables I have found that match the conditions on the spreedsheet/phabricator comment:

db1046:

MariaDB EVENTLOGGING m4 localhost log > use log;
Database changed
MariaDB EVENTLOGGING m4 localhost log > SHOW TABLE STATUS like 'Campaigns\_%'\G
*************************** 1. row ***************************
           Name: Campaigns_5485644
         Engine: InnoDB
        Version: 10
     Row_format: Compressed
           Rows: 0
 Avg_row_length: 0
    Data_length: 8192
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 1
    Create_time: 2015-01-16 10:04:08
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=COMPRESSED
        Comment: 
*************************** 2. row ***************************
           Name: Campaigns_5487321
         Engine: InnoDB
        Version: 10
     Row_format: Compressed
           Rows: 0
 Avg_row_length: 0
    Data_length: 8192
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 1
    Create_time: 2015-01-16 10:04:08
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=COMPRESSED
        Comment: 
2 rows in set (0.00 sec)

MariaDB EVENTLOGGING m4 localhost log > SHOW TABLE STATUS like 'Analytics\_%'\G
*************************** 1. row ***************************
           Name: Analytics_5751947
         Engine: InnoDB
        Version: 10
     Row_format: Compressed
           Rows: 1
 Avg_row_length: 8192
    Data_length: 8192
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 1
    Create_time: 2015-01-16 10:04:08
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=COMPRESSED
        Comment: 
1 row in set (0.00 sec)

MariaDB EVENTLOGGING m4 localhost log > SHOW TABLE STATUS like 'NewEditorMilestone\_%'\G
*************************** 1. row ***************************
           Name: NewEditorMilestone_6538838
         Engine: InnoDB
        Version: 10
     Row_format: Compressed
           Rows: 0
 Avg_row_length: 0
    Data_length: 8192
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 1
    Create_time: 2015-01-16 10:04:08
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=COMPRESSED
        Comment: 
1 row in set (0.00 sec)

MariaDB EVENTLOGGING m4 localhost log > SHOW TABLE STATUS like 'PerformanceMetric\_%'\G
*************************** 1. row ***************************
           Name: PerformanceMetric_6757313
         Engine: InnoDB
        Version: 10
     Row_format: Compressed
           Rows: 0
 Avg_row_length: 0
    Data_length: 8192
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 1
    Create_time: 2015-01-16 10:04:08
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=COMPRESSED
        Comment: 
1 row in set (0.00 sec)

MariaDB EVENTLOGGING m4 localhost log > SHOW TABLE STATUS like 'AccountCreation\_%'\G
*************************** 1. row ***************************
           Name: AccountCreation_4933341
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 292847616
Max_data_length: 0
   Index_length: 84688896
      Data_free: 490733568
 Auto_increment: 1
    Create_time: 2015-02-09 22:33:06
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=COMPACT
        Comment: 
1 row in set (0.00 sec)

db1047:

mysql> use log;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLE STATUS like 'Campaigns\_%'\G
*************************** 1. row ***************************
           Name: Campaigns_5485644
         Engine: TokuDB
        Version: 10
     Row_format: Dynamic
           Rows: 3
 Avg_row_length: 233
    Data_length: 699
Max_data_length: 9223372036854775807
   Index_length: 270
      Data_free: 81920
 Auto_increment: 4
    Create_time: 2014-05-03 09:39:42
    Update_time: 2014-05-28 14:19:02
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: `compression`='tokudb_zlib'
        Comment: 
*************************** 2. row ***************************
           Name: Campaigns_5487321
         Engine: TokuDB
        Version: 10
     Row_format: Dynamic
           Rows: 8
 Avg_row_length: 234
    Data_length: 1876
Max_data_length: 9223372036854775807
   Index_length: 720
      Data_free: 81920
 Auto_increment: 9
    Create_time: 2014-05-03 09:39:43
    Update_time: 2014-05-28 14:19:02
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: `compression`='tokudb_zlib'
        Comment: 
2 rows in set (0.01 sec)

mysql> SHOW TABLE STATUS like 'Analytics\_%'\G
*************************** 1. row ***************************
           Name: Analytics_5751947
         Engine: TokuDB
        Version: 10
     Row_format: Dynamic
           Rows: 1
 Avg_row_length: 190
    Data_length: 190
Max_data_length: 9223372036854775807
   Index_length: 87
      Data_free: 81920
 Auto_increment: 2
    Create_time: 2014-05-03 09:39:40
    Update_time: 2014-05-28 14:19:03
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: `compression`='tokudb_zlib'
        Comment: 
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS like 'NewEditorMilestone\_%'\G
*************************** 1. row ***************************
           Name: NewEditorMilestone_6538838
         Engine: TokuDB
        Version: 10
     Row_format: Dynamic
           Rows: 59
 Avg_row_length: 173
    Data_length: 10209
Max_data_length: 9223372036854775807
   Index_length: 5288
      Data_free: 49152
 Auto_increment: 60
    Create_time: 2014-05-16 10:15:31
    Update_time: 2014-05-28 15:31:34
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: `compression`='tokudb_zlib'
        Comment: 
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS like 'PerformanceMetric\_%'\G
*************************** 1. row ***************************
           Name: PerformanceMetric_6757313
         Engine: TokuDB
        Version: 10
     Row_format: Dynamic
           Rows: 1
 Avg_row_length: 191
    Data_length: 191
Max_data_length: 9223372036854775807
   Index_length: 87
      Data_free: 49152
 Auto_increment: 2
    Create_time: 2014-05-19 14:22:38
    Update_time: 2014-05-28 15:46:06
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: `compression`='tokudb_zlib'
        Comment: 
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS like 'AccountCreation\_%'\G
*************************** 1. row ***************************
           Name: AccountCreation_4933341
         Engine: TokuDB
        Version: 10
     Row_format: Dynamic
           Rows: 1373650
 Avg_row_length: 327
    Data_length: 449875815
Max_data_length: 9223372036854775807
   Index_length: 118136614
      Data_free: 527204352
 Auto_increment: 1673651
    Create_time: 2014-05-03 09:39:02
    Update_time: 2014-05-28 14:18:28
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: `compression`='tokudb_zlib'
        Comment: 
1 row in set (0.01 sec)

dbstore2002:

mysql> use log;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLE STATUS like 'Campaigns\_%'\G
*************************** 1. row ***************************
           Name: Campaigns_5485644
         Engine: TokuDB
        Version: 10
     Row_format: Dynamic
           Rows: 3
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 9223372036854775807
   Index_length: 0
      Data_free: 36864
 Auto_increment: 4
    Create_time: 2015-06-04 13:30:40
    Update_time: 2015-06-29 00:00:46
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=COMPRESSED `compression`='tokudb_zlib'
        Comment: 
*************************** 2. row ***************************
           Name: Campaigns_5487321
         Engine: TokuDB
        Version: 10
     Row_format: Dynamic
           Rows: 8
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 9223372036854775807
   Index_length: 0
      Data_free: 36864
 Auto_increment: 9
    Create_time: 2015-06-04 13:30:40
    Update_time: 2015-06-29 00:00:46
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=COMPRESSED `compression`='tokudb_zlib'
        Comment: 
2 rows in set (0.03 sec)

mysql> SHOW TABLE STATUS like 'Analytics\_%'\G
*************************** 1. row ***************************
           Name: Analytics_5751947
         Engine: TokuDB
        Version: 10
     Row_format: Dynamic
           Rows: 1
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 9223372036854775807
   Index_length: 0
      Data_free: 36864
 Auto_increment: 2
    Create_time: 2015-06-04 13:30:40
    Update_time: 2015-06-29 00:00:46
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=COMPRESSED `compression`='tokudb_zlib'
        Comment: 
1 row in set (0.04 sec)

mysql> SHOW TABLE STATUS like 'NewEditorMilestone\_%'\G
*************************** 1. row ***************************
           Name: NewEditorMilestone_6538838
         Engine: TokuDB
        Version: 10
     Row_format: Dynamic
           Rows: 1
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 9223372036854775807
   Index_length: 0
      Data_free: 36864
 Auto_increment: 60
    Create_time: 2015-06-04 21:21:34
    Update_time: 2015-06-04 21:21:34
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=COMPRESSED `compression`='tokudb_zlib'
        Comment: 
1 row in set (0.03 sec)

mysql> SHOW TABLE STATUS like 'PerformanceMetric\_%'\G
*************************** 1. row ***************************
           Name: PerformanceMetric_6757313
         Engine: TokuDB
        Version: 10
     Row_format: Dynamic
           Rows: 1
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 9223372036854775807
   Index_length: 0
      Data_free: 36864
 Auto_increment: 2
    Create_time: 2015-06-04 21:24:32
    Update_time: 2015-06-04 21:24:32
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=COMPRESSED `compression`='tokudb_zlib'
        Comment: 
1 row in set (0.04 sec)

mysql> SHOW TABLE STATUS like 'AccountCreation\_%'\G
*************************** 1. row ***************************
           Name: AccountCreation_4933341
         Engine: TokuDB
        Version: 10
     Row_format: Dynamic
           Rows: 75894
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 9223372036854775807
   Index_length: 1028806
      Data_free: 99176448
 Auto_increment: 1673651
    Create_time: 2015-06-04 13:30:39
    Update_time: 2015-07-07 18:56:51
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=COMPACT `compression`='tokudb_zlib'
        Comment: 
1 row in set (0.04 sec)

None of them seem to be currently in use, and with no or very little data.

I am going to execute the following statements on these servers (m4/log database):

DROP TABLE log.Campaigns_5485644;
DROP TABLE log.Campaigns_5487321;
DROP TABLE log.Analytics_5751947;
DROP TABLE log.NewEditorMilestone_6538838;
DROP TABLE log.PerformanceMetric_6757313;
DROP TABLE log.AccountCreation_4933341;

Please double check, slowly (take your time), that everything listed here is according to the intended plan, and that you understand that once I execute the above statements, recovering data from those tables will not be possible, ever. Applications using those specific tables will break, and users will not be able to read from them.

jcrespo moved this task from In progress to Blocked external/Not db team on the DBA board.Sep 30 2015, 7:45 AM
mforns added a comment.Oct 6 2015, 6:51 PM

@jcrespo

I've carefully reviewed this and confirmed with the schema owners that the deletion of those obsolete schemas can be started.
So, yes, you can go ahead and delete those :]

Thanks!

jcrespo moved this task from Blocked external/Not db team to In progress on the DBA board.Oct 8 2015, 2:11 PM
jcrespo added a comment.Oct 8 2015, 2:37 PM

Deletion log:

db1047:

mysql> DROP TABLE log.Campaigns_5485644;
Query OK, 0 rows affected (0.16 sec)

mysql> DROP TABLE log.Campaigns_5487321;
Query OK, 0 rows affected (0.07 sec)

mysql> DROP TABLE log.Analytics_5751947;
Query OK, 0 rows affected (0.09 sec)

mysql> DROP TABLE log.NewEditorMilestone_6538838;
Query OK, 0 rows affected (0.09 sec)

mysql> DROP TABLE log.PerformanceMetric_6757313;
Query OK, 0 rows affected (0.08 sec)

mysql> DROP TABLE log.AccountCreation_4933341;
Query OK, 0 rows affected (0.08 sec)
mysql> SHOW TABLE STATUS like 'Campaigns\_%'\G
Empty set (0.02 sec)

mysql> SHOW TABLE STATUS like 'Analytics\_%'\G
Empty set (0.00 sec)

mysql> SHOW TABLE STATUS like 'NewEditorMilestone\_%'\G
*************************** 1. row ***************************
           Name: NewEditorMilestone_6538838
         Engine: Aria
        Version: 10
     Row_format: Page
           Rows: 0
 Avg_row_length: 0
    Data_length: 8192
Max_data_length: 17592186011648
   Index_length: 8192
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-10-08 14:14:12
    Update_time: 2015-10-08 14:14:12
     Check_time: NULL
      Collation: utf8_unicode_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS like 'PerformanceMetric\_%'\G
*************************** 1. row ***************************
           Name: PerformanceMetric_6757313
         Engine: Aria
        Version: 10
     Row_format: Page
           Rows: 0
 Avg_row_length: 0
    Data_length: 8192
Max_data_length: 17592186011648
   Index_length: 8192
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-10-08 14:14:13
    Update_time: 2015-10-08 14:14:13
     Check_time: NULL
      Collation: utf8_unicode_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS like 'AccountCreation\_%'\G
Empty set (0.00 sec)
mysql> DROP TABLE log.NewEditorMilestone_6538838;
Query OK, 0 rows affected (0.13 sec)

mysql> DROP TABLE log.PerformanceMetric_6757313
    -> ;
Query OK, 0 rows affected (0.10 sec)

mysql> SHOW TABLE STATUS like 'Campaigns\_%'\G
Empty set (0.00 sec)

mysql> SHOW TABLE STATUS like 'Analytics\_%'\G
Empty set (0.00 sec)

mysql> SHOW TABLE STATUS like 'NewEditorMilestone\_%'\G
Empty set (0.00 sec)

mysql> SHOW TABLE STATUS like 'PerformanceMetric\_%'\G
Empty set (0.00 sec)

mysql> SHOW TABLE STATUS like 'AccountCreation\_%'\G
Empty set (0.00 sec)

db1046:

mysql> DROP TABLE log.Campaigns_5485644;
Query OK, 0 rows affected (0.29 sec)

mysql> DROP TABLE log.Campaigns_5487321;
Query OK, 0 rows affected (0.03 sec)

mysql> DROP TABLE log.Analytics_5751947;
Query OK, 0 rows affected (0.03 sec)

mysql> DROP TABLE log.NewEditorMilestone_6538838;
Query OK, 0 rows affected (0.08 sec)

mysql> DROP TABLE log.PerformanceMetric_6757313;
Query OK, 0 rows affected (0.08 sec)

mysql> DROP TABLE log.AccountCreation_4933341;
Query OK, 0 rows affected (0.05 sec)

mysql> SHOW TABLE STATUS like 'Campaigns\_%'\G
Empty set (0.00 sec)

mysql> SHOW TABLE STATUS like 'Analytics\_%'\G
Empty set (0.00 sec)

mysql> SHOW TABLE STATUS like 'NewEditorMilestone\_%'\G
Empty set (0.00 sec)

mysql> SHOW TABLE STATUS like 'PerformanceMetric\_%'\G
Empty set (0.00 sec)

mysql> SHOW TABLE STATUS like 'AccountCreation\_%'\G
Empty set (0.00 sec)

dbstore2002:

mysql> SHOW TABLE STATUS like 'Campaigns\_%'\G
Empty set (0.03 sec)

mysql> SHOW TABLE STATUS like 'Analytics\_%'\G
Empty set (0.03 sec)

mysql> SHOW TABLE STATUS like 'NewEditorMilestone\_%'\G
Empty set (0.03 sec)

mysql> SHOW TABLE STATUS like 'PerformanceMetric\_%'\G
Empty set (0.03 sec)

mysql> SHOW TABLE STATUS like 'AccountCreation\_%'\G
Empty set (0.04 sec)

@mforns, the deletion was successful. However, something strange happened at 2015-10-08 14:14, as if NewEditorMilestone_6538838 and PerformanceMetric_6757313 were recreated. Could you confirm somehow that there is no code reacreating those tables?

It could be some glitch that I cannot reproduce, like some delay on the deletion, but could you double check or tell me where to look? I cannot check it on my logs as they are disabled for performance reasons.

jcrespo moved this task from In progress to Done on the DBA board.Oct 8 2015, 2:39 PM
mforns edited projects, added Analytics-Kanban; removed Analytics-Backlog.Oct 9 2015, 2:40 PM
mforns moved this task from Paused to Done on the Analytics-Kanban board.
mforns removed a project: Analytics-Kanban.
jcrespo reassigned this task from jcrespo to mforns.Oct 9 2015, 2:44 PM
jcrespo subscribed.

I've not touched the wiki page for this task.

mforns added a comment.Oct 13 2015, 10:15 AM

@jcrespo
Thanks! I will move the task to done in our Kanban.

mforns renamed this task from Delete obsolete schemas {tick} to Delete obsolete schemas {tick} [5 pts].Oct 13 2015, 10:15 AM
mforns added a project: Analytics-Kanban.
• kevinator closed this task as Resolved.Oct 15 2015, 4:02 PM
jcrespo reopened this task as Open.Oct 26 2015, 7:42 PM

We should do this also on dbstore1002, as there is no replication link between the servers.

jcrespo closed this task as Resolved.Oct 26 2015, 7:43 PM

Done.

jcrespo reopened this task as Open.Oct 26 2015, 8:19 PM

And on db1047 too!

jcrespo closed this task as Resolved.Oct 26 2015, 8:21 PM

I already did it to db1047. I need to sleep more.

mforns added a comment.Oct 27 2015, 3:26 PM

Thanks again, @jcrespo

jcrespo reopened this task as Open.Nov 30 2015, 4:30 PM

This task caused the purging process to fail (T119380). Table purge_schedule has to be updated to reflect the dropped tables.

jcrespo closed this task as Resolved.Dec 1 2015, 4:52 PM

It seems fixed now, last error:

151130 18:18:45 [Note] Event Scheduler: [root@10.%].[log.delete_schedule] event execution failed.
mforns added a comment.Dec 1 2015, 6:03 PM

Thanks @jcrespo :]

Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL · Credits