We need some maintenance here at this site location. For a few days, you may need to visit https://www.anonymousconservative.com/news for the daily post.
You may want to not comment from here on out as well, as it is not clear if comments from the last few days until the reboot will be salvageable and show on the new site. I have to look at how to do something, and it may not be doable.
To that end, if anybody knows a free wordpress plugin which would look at two databases, and allow me to merge new comments and posts from some date forward into an older backup database, that would be great. Right now everybody acts like if you import the old database it automatically overwrites everything and all new material is lost. I hate to do that, even though it is only four or five days, though it is preferable to losing the old material.
If not, no prob, I will figure it out. But post quality may be down for a few days too as I barely had enough to do this site before, and this promises to be a decent sized pain in the ass which needs to be done.
Thank you for your patience.
I say make it a jubilee week. No comments count, all impersonation is forgiven, no joke is out of bounds, and the only two rules are no fedpoasting, and its over when AC says it is over.
> no joke is out of bounds
Include ribald limericks and you’ve got a deal
I thought those were always the rules?
Thank you for all of the work you do!
Thank you. I appreciate when people say this is helpful!
Merci Beaucoup for all you do and how appreciated you are. You are lifted up in prayer. St Michael please protect AC his loved ones, his ministry, and all technical and other attacks against him.
In Jesus Holy name.
Thank you for that! Much appreciated.
Yes. Thanks very much, AC. This site means a lot. I can’t know how hard you work, but I know it is a real effort. Good luck with the what you are doing.
Thanks!
Plugin: https://wordpress.org/plugins/wp-dbmanager/
CHATGPT:
As of my last knowledge update in September 2021, WordPress itself doesn’t have built-in support for transaction logs or point-in-time recovery like some advanced database systems do. However, you can still accomplish similar goals through other means.
wp-cli, and then restore them when needed. This would be more like a snapshot-based recovery rather than transaction logs.Remember that working directly with databases, especially when modifying or reverting data, can be risky. It’s important to thoroughly test any procedures in a safe environment before applying them to your live site.
For the most up-to-date information on this topic, I recommend checking the WordPress official documentation and community forums or looking into any new developments or plugins that might have emerged after my knowledge cutoff date.
So basically a database is not like ordered lists that you can take an old one, and append the new data?
I though if I had a backup that was from, say 2021, and now the current database is spotty up until then, but after then to now is good, I could clip all this later data and stick it into the old backup, and it would be whole.
The SQL enterprise databases use transaction logs, for point in time restores. A backup of the DB is done. They restore the DB and then run the transaction logs against it. That’s how those DB’s get restored, but that’s for oracle and MS-SQL databases. Based on CHATGPT search, WordPress is using a “less sophisticated” database.
I concur with “General’s Addition” comment below. You will probably have to extract out the comment data to flat files (CSV) then you can perform the “ordered list” edits you want. Then import the file back in somehow. You definitely want some type of test system to validate it on.
This looks hopeful:
https://wordpress.org/plugins/comments-import-export-woocommerce/
Does this plugin export article specific comments?
Yes. You can export comments for specific article.
You might be able to use that to just do a new site with a full restore from last week. Then add in the missing articles and comments.
Thank you!
I thought I’d have a few day break from keeping up with the comments. Then, I went to do an information feed sweep before bedtime, and there were over 50 comments. No rest for the weary…
Best of luck getting this sorted. Your site is my go to daily news feed & commentary on the madness. Thank you for what you do.
Thank you!
“… if anybody knows a free wordpress plugin which would look at two databases, and allow me to merge new comments and posts from some date forward into an older backup database …”
This image shows the database relationships inside WordPress:
The “wp_comments” table has associations with “wp_users” and “wp_commentmeta”.
Looking at “wp_users”, it looks like “wp_comments” references those relationships but doesn’t add to them, so “wp_users” should be safe to skip.
The problem is “wp_commentmeta” which appears to be usable for whatever “metadata” a comment plug-in chooses to add, with the implication that unless there are standard conventions between plug-ins, this data won’t be compatible across them.
And so the “helpful yet useless” advice you’ve been receiving is probably the result of people using an import plug-in or external tool without adjusting the sequence numbers so the existing data doesn’t get hammered. Typically any old site will have many more records than a newly relaunched site, and so the most frequently encountered effect would be that you’d blow away the new data.
I see something called “WordPress Comments Import & Export” by WebToffee that could be useful.
Maybe this workflow helps, and you will want to experiment elsewhere with a backup of the site:
1) Export the “wp_comments” and “wp_commentmeta” data to delimited flat files (such as CSV).
2) Look at the data coming out of both tables to see what the max sequence values are (such as “wp_commentmeta.meta_id” and “wp_comments.comment_ID”).
3) Record those max sequence values.
4) Do whatever DB reset you need to do for comments.
5) Find a way to advance the auto-increment counter past the max sequence values for those tables. (This is specific to MariaDB, MySQL, etc., so go learn how to do that.)
6) Do some quick data inserts via SQL command line on these tables, using some examples from your exported data. (Or see if you can get the import tool to process a single record for each.)
7) Verify that the new data gets written above the old sequence values, then delete your ad hoc examples.
8) New reality: let the site launch with new data going into sequences above the old data.
9) Merge day: backup the new data using the export tool.
10) Reload a small amount of the old data using the import part of the plug-in to see if it’s overwriting new records.
11) If the proof-of-concept works and the data’s getting merged without conflicts, go for it!
Keep in mind is that “wp_commentmeta” is very “meta”, as in it isn’t clear how that data’s structured or how it’s meant to be kept compatible between comment plug-ins, and the WordPress documentation about it is fairly fluffy.
If the idea here is to switch to another comment plug-in rather than just cleaning up the DB, see if you can get a description of how each plug-in uses the comment meta table.
There’s a WordPress full site backup/transfer tool you can also use to create a clone of the site, which would let you debug these problems while leaving things intact on the main site (even if they’re broken somehow). This can be helpful to do locally if you have a compatible local server and enough bandwidth, or perhaps just launch a new copy on your existing hosting provider’s systems.
People here with deeper WordPress experience would also be able to help if you’d name the DB (as in MySQL 5.7, etc.) and the comment plug-in so that some of the “meta” relationships can be sorted out.
So if there isn’t a tool to do a safe merge, what you probably need is a way to advance the current DB table sequence numbers so that the data you’ll eventually merge won’t get bashed or won’t wind up bashing the new data. (Also: DELETE FROM and DROP work differently, and if you are not aware of how or why, you should go learn that now.)
If the goal here is to repair the entire DB, you need an offline backup of it anyway, and so the export tool will help with that. You’d also want to use your hosting provider’s backup systems for this to have a native copy, such as whatever you use for an admin tool, which is probably cPanel or something like it.
Beyond that, there’s the WP_ALLOW_REPAIR option in “wp-config.php” that tells WordPress to repair the DB. It’s also possible in cPanel with myPHPAdmin to do DB table integrity repairs as well.
Nota bene: This isn’t meant to be advice, this is merely a working strategy that may be amended to fit actual conditions, which you will encounter in their full ugliness as you get farther into this.
That’s why there isn’t a canned solution: by the time you reach the complexity level of a repair operation, all of your canned solutions have already failed, and if you cannot roll back, you are left with implementing ad hoc solutions, which is where you are now.
This is not cynicism about the software industry.
This is a fact of engineering and operations research.
Have a plan, then amend it as needed as that plan meets the enemy.
But real engineers don’t test in production, it takes a “Titan” for a job that deep, BTW.
Good luck! Let’s see if you sink the site! 🙂
I was kind of hoping for something point and click.
It might be the best option is to roll back to a week ago and just manually add the posts I’ve added since then?
I know you want a quick solution, but in order to understand some of the limitations and potential traps, it’s worth taking a few moments to understand what’s underneath the hood and some of the risks.
Another helpful link from WordPress (which includes the ERD):
https://codex.wordpress.org/Database_Description
A few specific references:
https://codex.wordpress.org/Database_Description#Table:_wp_commentmeta
https://codex.wordpress.org/Database_Description#Table:_wp_comments
Pay special attention to the “extra” schema description field: these fields auto-increment, which means that they increase according to an increment interval under the control of the database, not the WordPress software, and usually not under the control of anything that runs within the WordPress software, such as plug-ins.
And so the sequence numbering, which can be easy to mess up, has to be squared away with the DB and is why that plan focuses on it. Without the sequence numbering being accurate, it’s very easy to lose data by having it filed away incorrectly.
You could load the data in with some import/export tool, but if the tools don’t move the sequence numbers forward beyond the loaded data, data loss becomes inevitable, with the most obvious variant being that old data overwrites new data. If you are shopping now for an all-in-one solution, these are the kinds of questions you need to ask, including how the tools work with past and future merges.
If you look at the WordPress ERD, you’ll see that “wp_posts” is central to the entire thing, and so changes to sequence numbers (which for “wp_posts” would involve the “wp_posts.ID” field) might destablilise the DB’s integrity.
See if you can limit your operations to the comments within posts and doing things in a way where the sequence numbers within those two tables (“wp_comments” and “wp_commentmeta”) stay consistent and idempotent between DB copies.
Some of these tables, such as “wp_posts”, have multiple keys and are referenced by other tables, and so changes to sequence numbers inevitably lead to data integrity problems.
I’d consider using that “wp-config.php” option on a copy to see if you can get WordPress itself to rebuild any internal indexes, including those maintained by MariaDB or MySQL. There are five indexed fields on “wp_comments” alone as well as two on “wp_commentmeta”.
According to WordPress, setting the WP_ALLOW_REPAIR option in “wp-config.php” forces your blog offline entirely during that operation. This is something you’d turn on once, let it run, and when you can log in again you’d shut the blog down again and reset that option. This is again why a copy works best.
But you could use that copy as well as some slash-and-burn techniques on it to see if you can get the copy DB to behave correctly, after which you’d use that WordPress site-to-site copy tool or just do those things again on the master. The proof-of-concept would of course be getting the copy DB and the copied site to behave exactly like the master, after which it’s safer to just try stuff and see what sticks.
Also, periodic backups of key tables to CSV help if you have to slash-and-burn via direct text editing any DB commits that appear to be harmful to the WordPress software, and of course you can apply plug-ins and filters later to deal with any of those.
Once you get into the territory of lost referential integrity, there really is no way of knowing how a canned solution that might work across the entire DB from a master table perspective (such as “wp_posts”) would behave in capturing everything and being able to sort through the breakage.
That’s why these suggestions seem overly complex, but are instead like using a scalpel instead of a chainsaw.
You have to know where and how to cut first in order to avoid cutting too harshly.
Your problems are very likely fixable in the DB itself with command line tools and a limited set of data import/export tools that would let you edit around the worst of the issues. That’s where CSV import/export comes in handy.
If a full import/export tool allows you to reconstruct the entire DB from exported files?
It’s worth a shot.
Experiment with a copy, of course, that’ll save time and pain as well.
The background about the experience and a write-up on how you got the repair to work would be useful to a lot of other people, especially those who through no fault of their own wound up with slightly messed up WordPress DBs.
Sometimes understanding the process is more useful than just getting the product, because then you can fix the problems if they come up again.
The idea here is that there isn’t one way to get to the solution, and it’s better to beat up something disposable (such as a full WordPress install copy) somewhere else and apply what you’ve learned.
Also, we are clearly within very different time zones, as my comments go into moderation and stay there for hours. 🙂
Ugh …
BTW, it looks like MySQL makes pushing the sequence value forward on a table into something awful.
If the SQL creates the table with AUTO INCREMENT, it increments according to the increment interval, which defaults to 1, and the sequence also starts at 1.
So if you do nothing with a new table copy to push the next sequence value forward, any new data loads in at sequence value 1, regardless of what you’ve already loaded into it.
I don’t see a “sequence nextval” global like thing as you have in some other DBs (especially Oracle), and so this is done by internal values, plus there’s no “ALTER SEQUENCE” operation you can use.
If you decide to go down this path, “ALTER TABLE AUTO_INCREMENT”, setting that value high, loading junk data to push the sequence out, and setting the auto increment interval back to 1 is how to do it, plus you can find some decent examples via search engines.
(Me to MySQL: WHY ARE YOU LIKE THIS? WTF?)
[ahem]
If your reaction is to say, “But General, I don’t feel comfortable with scalpels, not like this”, then your solution really is a WordPress plug-in, one you’ll probably have to pay for to get support for it, and it’s a question about how deep your pockets are for such support.
Before you get to that point, make a copy of your site and play with that first, then ask your new software vendors the kinds of questions that have been brought up here.
That import/export tool I mentioned before looked like it’d be OK, and I didn’t see that it required any money to use.
Maybe exporting your entire WordPress blog to a copy and importing it back with the changes you want would fix the problems?
At least you’d have a copy to experiment on first.
I think WP-DBManager plugin might be useful. Or you could do it through PHPAdmin.
There is also WP Data Merge plugin but I have never used it.
Thank you!
If it’s worth $249, https://www.wpallimport.com/ will do what you need. You can take a slice of content by date range saved to CSV file and import it into another site.
Thank you!
Can I ask, how do I get the SQL file turned into a CSV file so this can work with it?
EDIT : No, never mind I see I can export it that way in MyPHP. This looks workable. Thank you!
but ah needs mah fix!