Ben Lobaugh Online

I think, therefore I am. I am, therefore I sail

Category: MySQL (Page 1 of 3)

Find and update WordPress posts with comments older than NNN days

I have a use case where I need to disable comments on posts in WordPress where the last comment was more than one year, or 365 days, ago.

Posts can be found with this query:

SELECT p.ID, p.post_title, p.post_date, p.comment_status, p.`ping_status`
FROM wp_posts AS p
     SELECT c.comment_post_ID
     FROM wp_comments AS c
     WHERE DATEDIFF( curdate(), c.comment_date ) <= 365

The outer query takes the inner query as the search parameter. The inner query is finding the latest comment for each post based on the number in the WHERE condition. 365 represents 365 days, or 1 year.

Therefore, this query is getting the latest comment for post where the comment is less than 1 year old.

Now let’s say you need to update the posts to disable comments. The goal is to disable commenting for all posts that have not had a comment within the last year. It can be accomplished with the following:

UPDATE wp_posts
SET comment_status='closed', ping_status='closed'
		SELECT c.comment_post_ID
		FROM wp_comments AS c
		WHERE DATEDIFF( curdate(), c.comment_date ) >= 365

Here again the number 365 represents the number of days.

Check the posts and you should see the comments have been closed.

MySQL: Count number of tables in database

If you need to count the number of tables that exist in a MySQL database you can do so with the following query. Just remember to swap out the database name!

SELECT count(*) 
FROM information_schema.tables 
WHERE table_schema = 'YOUR_DB_NAME'

MySQL: Find the size of a database

MySQL has the ability to show you size of the information contained in your database. You can find this information per database or per table in a database pretty easily.

Size of databases

To list the size of all the databases on the server run the following:

SELECT table_schema "Database", sum( data_length + index_length) / 1024 / 1024 
"Size in MB" FROM information_schema.TABLES GROUP BY table_schema;

Size of tables in database

To list the size of all the tables in an individual database run:

SELECT table_name, table_rows, data_length, index_length, 
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES where table_schema = "{{NAME OF YOUR DATABASE}}";

MySQL who: See who is connected to your MySQL server with this script

Need to determine who is connected to your MySQL server and how many connections they have open? I tossed together this little script to display the information.


use DBI;
use 5.010;
use Getopt::Long;

# Set defaults
my $host = 'localhost';
my $db = 'information_schema';
my $user = ''; 
my $pass = ''; 

# Get commandline options
GetOptions (
    "host=s"    => \$host,
    "db=s"      => \$db,
    "user=s"    => \$user,
    "pass=s"    => \$pass,
) or die ( "Valid options\n--host\n--user\n--password\n--db database" );

# Create database connection
my $dbh = DBI->connect( "DBI:mysql:$db:$host", $user, $pass, {'PrintError'=>0} )
        or die "** Connection error!\nTry different options:\n--host\n--user\n--password\n--db database\n\n $DBI::         +++errstr\n";

# Get the current processes to check user
my $sql = "show processlist";
my $sth = $dbh->prepare($sql);
$sth->execute() or die "SQL error: $DBI::errstr\n";

# Create an array of all the users
my %user;
while (my @row = $sth->fetchrow_array()) {

# Display User details
print "MySQL - Logged in users\n";
print "=" x 15, "\n";

for my $x (keys %user) {
    print "$x - $user{$x} connections\n";

print "\n";

Script to convert MySQL collation from utf8mb4 to utf8

I ran into a strange situation with WordPress where the MySQL server did not support utf8mb4. I needed to quickly convert the tables back to utf8 to get the site running again.

Though this script does work I highly recommend if you are running WordPress that you upgrade your MySQL server to support utf8mb4 for security. WordPress made this change to fix a security vulnerability introduced by The Trojan Emoji.

    echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE utf8_general_ci;'
    mysql -p$PASS -u $USER "$DB" -e "SHOW TABLES" --batch --skip-column-names \
    | xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;'
) \
| mysql -p$PASS -u $USER "$DB"

Page 1 of 3

Powered by WordPress & Drip City Coffee