Major database mess...I think?

mbrown

So, over the weekend I got reports that my disciples tools instances were, well, unusable. The sites did not render correctly. I did some digging and found that there were TONS of errors in the php error_log.

Looking at the logs it appears that some kind of a database schema migration is getting kicked off - probably part of some kind of theme update. It's definitely not wordpress core - I dont think wordpress core would involve things like this:

[21-Aug-2024 14:57:54 UTC]  2024-08-21 14:57:54 GMT Starting migrating Network Dashboard to number 0
[21-Aug-2024 14:57:54 UTC]  2024-08-21 14:57:54 GMT Done migrating Network Dashboard to number 0
[21-Aug-2024 14:57:54 UTC]  2024-08-21 14:57:54 GMT Starting migrating Network Dashboard to number 1
[21-Aug-2024 14:57:54 UTC]  2024-08-21 14:57:54 GMT Done migrating Network Dashboard to number 1
[21-Aug-2024 14:57:54 UTC]  2024-08-21 14:57:54 GMT Starting migrating Network Dashboard to number 2
[21-Aug-2024 14:57:54 UTC]  2024-08-21 14:57:54 GMT Done migrating Network Dashboard to number 2
[21-Aug-2024 14:57:54 UTC]  2024-08-21 14:57:54 GMT Starting migrating Network Dashboard to number 3
[21-Aug-2024 14:57:54 UTC]  2024-08-21 14:57:54 GMT Done migrating Network Dashboard to number 3
[21-Aug-2024 14:57:54 UTC]  2024-08-21 14:57:54 GMT Starting migrating Network Dashboard to number 4

I'm also seeing a bunch of lines like this:

[21-Aug-2024 03:16:36 UTC] PHP Warning:  Undefined array key "group" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 190
[21-Aug-2024 03:16:36 UTC] PHP Warning:  Undefined array key "group" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 187

Most disturbingly:

[21-Aug-2024 14:57:55 UTC]  2024-08-21 14:57:55 GMT Starting migrating Network Dashboard to number 6
[21-Aug-2024 14:57:55 UTC] WordPress database error Unknown column 'al.field_type' in 'where clause' for query
           SELECT
                DISTINCT( al.object_id ) as site_object_id,
                'new_coaching' as action,
                al.hist_time as timestamp
            FROM TYE_8_dt_activity_log as al
            LEFT JOIN TYE_8_dt_movement_log as m
                ON al.object_id=m.site_object_id
                AND m.site_id = 'e88a1bbb3b80442deee7df00915de7247f16637d296f4204b5f0af7760477542'
                AND m.action = 'new_coaching'
            WHERE al.object_type = 'contacts'
                AND al.meta_key = 'contacts_to_contacts'
                AND al.field_type = 'connection from'
                AND m.id IS NULL
            ORDER BY al.object_id; made by require('wp-blog-header.php'), require_once('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), do_action('after_setup_theme'), WP_Hook->do_action, WP_Hook->apply_filters, {closure}, DT_Network_Dashboard_Migration_Engine::migrate, DT_Network_Dashboard_Migration_0006->up, DT_Network_Activity_Log::query_new_coaching
[21-Aug-2024 14:57:55 UTC]  2024-08-21 14:57:55 GMT Done migrating Network Dashboard to number 6

Now, I maintain a GFS rotation of backups for three months. So I went back and checked - every one of my backups has this stuff in the error logs. I previously had auto-updates enabled for both WordPress Core and plugins, and so I deduce that somehow something went very wrong in some of these updates, and I didn't know about it until things went even more wrong over this past weekend.

I restored back one by one until I found the most recent backup that still appeared to work.

Has anyone seen anything like this? This is a multisite instance too, and I'm dreading trying to clean it up.
Can anyone give me some guidance, should I decide to reinstall WP/DT and migrate the data to a new clean database, how I would do that?

Thanks in advance.

mbrown

@mbrown

I don't know if there was something stale in the cron triggers or what, but I did discover that Bluehost (my provider) had added a plugin that was forcing auto-updates and a few other things that I think were conflicting.

Well, I went back and restored backups until I found a working one again, made a bunch of tweaks to make sure the stupid bluehost plugin is disabled for good, and blocked all auto updating with filters in a mu-plugin.

So far so good. Started up with a bunch of errors related to a dt snapshot job of some sort but they haven't repeated.

Time will tell. Again, if anyone has seen anything like this, I'd appreciate the info.

Thanks!

Corsac

Hey @mbrown!

Thanks for messaging and including the logs, they are very helpful.
Based one the information you gave we were able to replicate one of the error messages and there is a fix is progress. After testing, the migration should work again. We'll comment here when that is ready.

Corsac

@mbrown Would you be able to try the network dashboard plugin v2.10.3 and let us know if you see any more issues?

Also it will which version of php and D.T do are you using?

mbrown

@Corsac

Thanks! First:

Wordpress 6.8
PHP 8.3.6 64-bit

Disciple Tools 1.69.2 with:
Genmapper 1.6.0
Import 1.3.1
Multisite 1.14.1
Multisite Dropdown 1.7
Network Dashboard 2.10.2

I will upgrade the plugin to 2.10.3 later today.

It seems that the wp cron event dt_network_dashboard_push_snapshot is what is triggering the errors. Once I update the plugin will that start immediately or will I need to wait until it triggers normally via wp cron?

Corsac

@mbrown Awesome. Let us know.
The snapshots happen via cron and are schedule to happen at night time. Those should trigger nicely if system cron is setup.

I'm noticing now that the logs you shared are from 2024, let us know if there are recent logs with the new update.

mbrown

@Corsac said in Major database mess...I think?:

en via cron and are schedule to happen at night time. Those should trigger nicely if system cron is setup.

I'm noticing now that the logs you shared are from 2024, let us know if there are recent logs with the new update.

Yeah, they are all the same, sorry about that. Here's last nights.

[30-Apr-2025 14:14:01 UTC] PHP Warning:  Undefined array key "church" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 187
[30-Apr-2025 14:14:01 UTC] PHP Warning:  Undefined array key "church" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 190
[30-Apr-2025 14:14:01 UTC] PHP Warning:  Undefined array key "group" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 187
[30-Apr-2025 14:14:01 UTC] PHP Warning:  Undefined array key "group" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 190
[30-Apr-2025 14:14:03 UTC] PHP Warning:  Undefined array key "church" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 187
[30-Apr-2025 14:14:03 UTC] PHP Warning:  Undefined array key "church" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 190
[30-Apr-2025 14:14:03 UTC] PHP Warning:  Undefined array key "group" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 187
[30-Apr-2025 14:14:03 UTC] PHP Warning:  Undefined array key "group" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 190
[30-Apr-2025 14:14:06 UTC] PHP Warning:  Undefined array key "church" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 187
[30-Apr-2025 14:14:06 UTC] PHP Warning:  Undefined array key "church" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 190
[30-Apr-2025 14:14:06 UTC] PHP Warning:  Undefined array key "group" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 187
[30-Apr-2025 14:14:06 UTC] PHP Warning:  Undefined array key "group" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 190
[30-Apr-2025 14:14:08 UTC] PHP Warning:  Undefined array key "church" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 187
[30-Apr-2025 14:14:08 UTC] PHP Warning:  Undefined array key "church" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 190
[30-Apr-2025 14:14:08 UTC] PHP Warning:  Undefined array key "group" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 187
[30-Apr-2025 14:14:08 UTC] PHP Warning:  Undefined array key "group" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 190
[30-Apr-2025 14:14:11 UTC] PHP Warning:  Undefined array key "church" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 187
[30-Apr-2025 14:14:11 UTC] PHP Warning:  Undefined array key "church" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 190
[30-Apr-2025 14:14:11 UTC] PHP Warning:  Undefined array key "group" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 187
[30-Apr-2025 14:14:11 UTC] PHP Warning:  Undefined array key "group" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 190
[30-Apr-2025 14:14:14 UTC] PHP Warning:  Undefined array key "church" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 187
[30-Apr-2025 14:14:14 UTC] PHP Warning:  Undefined array key "church" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 190
[30-Apr-2025 14:14:14 UTC] PHP Warning:  Undefined array key "group" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 187
[30-Apr-2025 14:14:14 UTC] PHP Warning:  Undefined array key "group" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 190
[30-Apr-2025 14:14:15 UTC] PHP Warning:  Undefined array key "church" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 187
[30-Apr-2025 14:14:15 UTC] PHP Warning:  Undefined array key "church" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 190
[30-Apr-2025 14:14:15 UTC] PHP Warning:  Undefined array key "group" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 187
[30-Apr-2025 14:14:15 UTC] PHP Warning:  Undefined array key "group" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 190
[30-Apr-2025 14:14:18 UTC] PHP Warning:  Undefined array key "church" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 187
[30-Apr-2025 14:14:18 UTC] PHP Warning:  Undefined array key "church" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 190
[30-Apr-2025 14:14:18 UTC] PHP Warning:  Undefined array key "group" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 187
[30-Apr-2025 14:14:18 UTC] PHP Warning:  Undefined array key "group" in /home1/mjbhostc/public_html/equipleaders/dt/wp-content/plugins/disciple-tools-network-dashboard/snapshots/snapshot-queries.php on line 190

After I went back to a backup where the DB seemed reasonably stable, these are all I'm getting now. I don't know if there's a pending update or what.

I have learned since that much of the core of what triggered all this for us is that there were very bad performance issues. Seems several people were complaining about it and the first level techs would "kill all the background jobs" to fix performance. Of course that didn't last. My thinking is that they did this at some point where the DB was in the middle of a migration and caused some issues. Fortunately my DB restore seems to have resolved that - and also fortunately we didn't lose a lot of data.

Anyway, since I'm in IT professionally, I knew that their L1 tech answer was not an answer and I kept pushing. Got a whole bunch of crazy stupid "your app is overloaded with 1000 queries per day" responses but I finally got to an L3 person who had a clue. Seems our server was under a DDOS attack for months but nobody realized it (inspires confidence, eh?). At any rate they got that mitigated and performance has been stable since.

If we can get this issue sorted out so I'm not generating nightly errors I'll feel a LOT better about things.

mbrown

@Corsac

Ok, I've update the plugin to 2.10.3. I'll check the log in the morning to see how it looks and let you know.