Page MenuHomePhabricator

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

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

Event Timeline

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 added a subscriber: mforns.
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 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

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

jcrespo raised the priority of this task from High to Needs Triage.Sep 9 2015, 10:45 AM
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.

@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

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!

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.

mforns moved this task from Paused to Done on the Analytics-Kanban board.
mforns removed a project: Analytics-Kanban.
jcrespo added a subscriber: jcrespo.

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

@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.

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

And on db1047 too!

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

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

It seems fixed now, last error:

151130 18:18:45 [Note] Event Scheduler: [root@10.%].[log.delete_schedule] event execution failed.