Skip to main content

WordPress – How to perform Search and Replace on whole Database (post, pages, wp_options) using WP plugins or MySQL tools

Those who are frequently working with Worpress often find themselves operating mass-replace tasks throughout the whole WP Database. Such situation most likely arises in the following scenarios:

  • WebSite URL change (from  www.url-a.com to www.url-b.com), meaning we have to replace the old one in all of our posts/pages.
  • Invalid character issues (mostly due to charset)
  • Having to rename one or more keywords, tags, categories or more (for standard URL & links consistency).
  • Presence of a typo within multiple posts, which you would like to fix without performing thousands of edits.

When we need something like that, we can choose between two possible routes: going for a WordPress plugin to handle such issue in a managed way or work at database level with the help of some free and/or open MySQL management tools.

Read More

WordPress – How to retrieve all posts from one or more given categories with a SQL query

Today a client asked me to remove all the posts from a given category to the auto-generated XML Sitemap of his WordPress blog and also redirect these posts to a new website (same slug, different host): here’s a quick report of how I managed to do that, hoping that it would also help other System Administration that will be given these same tasks.

Read More

How to fix “MySQL server has gone away”, “Packets out of order” and similar MySQL connection errors

If you’re a fond MySQL and/or MariaDB user you most certainly experienced at least one of these common errors:

MySQL server has gone away

Error reading result set’s header

Error executing query

MySQL server has gone away for query

2006, MySQL server has gone away

Packets out of order. Expected X received Y. Packet size=Z

… and so on. You can find these warnings basically anywhere, as they are usually written in the log files of the applications, tools and services that try to connect to your database and fail: however, in most scenarios, they are firstly noticed by the zealous System Administrator inspecting the PHP error log file (or the Joomla or WordPress counterpart) while trying to understand why his beloved web site or service isn’t working like it should.

That said, let’s try to understand the underlying cause and see what we can do to overcome them.

Read More

Mantis BugTracker – How to change a bug date and time (with all related notes, history and info) with a bunch MySQL scripts

If you found this post you most certainly know Mantis BugTracker, also known as MantisBT, the award-winner open-source bug tracking platform built with PHP and MySQL. If you use it a lot, you likely already know that the software doesn’t allow you to change the date and/or the time of basically anything it tracks, such as: bug entries, notes, comments, status changes. Even if you’re the platform administrator, there’s no chance you can do that relying to the built-in commands.

This behaviour shouldn’t sound surprising at all: the main purpose of issue-tracking software is to track the user activity, including those performed by the system administrators. Altough this is generally a good thing, there are some edge-case scenarios where altering these dates could be really useful: for example, when we need to restore an old backup and fill it with the “missing” tasks lost due to a DB crash.

When such a scenario occurs, this set of queries might come to the rescue:

Read More

How to get the size of all MySQL Tables in all Databases (and other size-related queries)

Yesterday I was trying to investigate through an odd size increment affecting one of our MySQL databases. Since I didn’t knew about the table(s) affected by the issue, the first thing I needed to do was to take a look at each table size among the various DB that were there.

I managed to perform such analysis in few minutes with these great queries, loosely based upon those present within this StackOverflow answer.

Read More

Close