Restoring WP Database - 5million rows?!

[cb3inco]

I'm migrating my site that is using Disciple Tools to a new server using the Updraft plugin. In restoring the database I noticed that the restore process is taking a fair bit of time.

0001.252 () Processing table (InnoDB): wplagenes_dt_activity_log - will restore as: 33_dt_activity_log
0021.743 () Database queries processed: 100 in 21.61 seconds
0049.688 () Database queries processed: 150 in 49.56 seconds
0078.481 () Database queries processed: 200 in 78.35 seconds
0103.645 () Database queries processed: 250 in 103.52 seconds
0231.320 () Database queries processed: 500 in 231.19 seconds
0363.444 () Database queries processed: 750 in 363.31 seconds
0490.032 () Database queries processed: 1000 in 489.90 seconds
0617.940 () Database queries processed: 1250 in 617.81 seconds
0752.824 () Database queries processed: 1500 in 752.69 seconds
0765.011 () Skipped execution of SQL statement (unwanted or internally handled type=18): /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
0765.012 () Skipped execution of SQL statement (unwanted or internally handled type=18): /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
0765.036 () Atomic restore: dropping original table (wplagenes_dt_activity_log)
0765.050 () Atomic restore: renaming new table (33_dt_activity_log) to final table name (wplagenes_dt_activity_log)
0805.152 () Search and replacing table: wplagenes_dt_activity_log: rows: 5119913

At the time of this writing it's on record 2220000 and it's been a good 20 minutes so far. This is pretty minimal install. Why is there over 5 million rows in this database?

[corsacca]

Hey @cb3inco, thanks for messaging

On a D.T site we have running for over 7 years there is about ~500,000 records in the activity log.
So 5,000,000 is surprising.

Field updates are stored there, as well as logs.
There is a automatic process than runs everyday (cron job) that cleans up the log. I'm wondering if that process hasn't been running. On a multisite those often have to be configured manually.

If you have access to phpmyadmin and can run SQL commands:

select action, count(*) as count from 33_dt_activity_log
GROUP by action
ORDER BY count DESC

Will show you the count of each type of activity log. You might need to replace 33_dt_activity_log if your table name is different.

Our example

To delete all the error_logs

DELETE from 33_dt_activity_log
WHERE action = 'error_log'

Let us know how that goes!

[cb3inco]

@corsacca - FYI

This is a single wordpress site btw.

select action, count(*) as count from wp_dt_activity_log GROUP by action ORDER BY count DESC;
+---------------------+---------+
| action | count |
+---------------------+---------+
| error_log | 4883184 |
| field_update | 1154 |
| connected to | 222 |
| logged_in | 130 |
| created | 79 |
| disconnected from | 18 |
| share | 15 |
| assignment_accepted | 14 |
| mail_sent | 3 |
| deleted | 2 |
| record_deleted | 2 |
+---------------------+---------+
11 rows in set (5.341 sec)

Ran the delete command

select action, count(*) as count from wp_dt_activity_log GROUP by action ORDER BY count DESC;
+---------------------+-------+
| action | count |
+---------------------+-------+
| field_update | 1154 |
| connected to | 222 |
| logged_in | 130 |
| created | 79 |
| error_log | 26 |
| disconnected from | 18 |
| share | 15 |
| assignment_accepted | 14 |
| mail_sent | 3 |
| deleted | 2 |
| record_deleted | 2 |
+---------------------+-------+

I have an external service triggering WP cron, so I'm not sure exactly what's going on here.

[corsacca]

Thanks for reporting back!
It looks like there was 4,883,184 error logs.
Those should be cleared out by the cron, unless they all happened in the last month.
Some diagnosing steps:
If you go to WP Admin > D.T Utilities > Error Logs you can see the recent logs. That could let us know what is going on.
If you go to WP Admin > D.T Utilities > Background Job. This list should be empty if cron is running.
Check WP Admin > D.T Utilities to see if any migrations are stuck.