Tag: mysql

Posted on

Sometimes it takes a bit of a disaster to force change

Sometimes we keep doing things in the way we always have … even though we know there are better ways – sticking with the same-old-approach is simple. Change is hard.

Like sticking with copy+pasting SQL schema changes into the MySQL CLI. As that is what we’ve always done.

Like what could go wrong?

Especially when somehow GitHub decides to put the whole file into your paste buffer, and that includes DROP TABLE x; before each CREATE TABLE. And I really just wanted the final ‘CREATE TABLE’ in the schema.sql file.

Thankfully Azure provides a Point in Time recovery for MySQL (even if it took the best part of an hour to perform that restore).

 

Posted on

hotel booking / wp_mphb_sync_logs

Just a random post about a WordPress plugin (hotel booking).

For the site in question, I have a script running which alerts me to any long running (>600s) MySQL queries (or causes of deadlock) and attempts to kill them. When it does this it emails me….

So, the site/MySQL was trying to run queries like this :

DELETE FROM wp_mphb_sync_logs WHERE queue_id IN (SELECT queue_id FROM wp_mphb_sync_queue WHERE queue_name < '1639573980');

which I did an explain, it showed there were > 5 million rows to examine (none of which are actually deleted by the query, so I assume the 5 million rows are all for now invalid queue_id entries).

Adding an index on the wp_mphb_sync_logs.queue_id field didn’t really help speed up the delete … and googling around and checking the plugin’s source code, led me to think it was safe (enough) to do a ‘TRUNCATE wp_mphb_sync_logs‘.

Now that’s done the table has remained empty 12 hours later; so I think everything’s fine.

This post is mostly because the plugin’s forum requires a paid membership to contribute; and I’m not paying $400 just to post “doing this worked for me”.

Posted on

Bash / MySQL queries…

I “woke up” and realised I often do this wrong …. (too many connections/individual queries on MySQL) when the query is returning simple values (single word like things) for each field.

As a means of example :

So a before query :

MYSQL="mysql -NB my_database"
IDENTIFIER=some_value

# Obviously $VAR1/$VAR2 can contain spaces etc in this example
VAR1=$(echo "SELECT field FROM table WHERE some_key = '${IDENTIFIER}'" | $MYSQL)
VAR2=$(echo "SELECT field FROM table WHERE some_key = '${IDENTIFIER}'" | $MYSQL)
....

And perhaps a nicer way might look more like :

#!/bin/bash
# Chuck output from a query into an array (retrieve many fields at once)
# coalesce( ... ) copes with a field that may be null.

BITS=( $(echo "SELECT coalesce(field1,200) as field1, field2, field3 FROM some_table WHERE some_key = '${IDENTIFIER}'" | $MYSQL ) )

# and then check we had enough values back
if [ ${#BITS[@] -lt 3 ]; then
    echo "handle this error..."
fi
# This will not work if field1/field2/field3 contain spaces.
VAR1=${BITS[0]}
VAR2=${BITS[1]}
VAR3=${BITS[2]}

Posted on

Cleaning up a lot of WordPress _transients

WordPress seems to like hiding a load of ‘transient’ (cacheable) stuff in it’s wp_options table. Unfortunately for one site, it seems it didn’t bother to clean up the transient stuff, leaving behind about 750,000 records… which made a WP version upgrade painful, as MySQL locks the wp_option table which causes all other page loads to get stuck waiting (and the site to stop working).

Fix / hacky solution after the ‘more’…

Continue reading “Cleaning up a lot of WordPress _transients”

Posted on

MySQL update/write query analysis (query profiling)

Do you have a slow MySQL update/insert/delete query?

Obviously, for ‘SELECT’ queries you can prepend the query with “EXPLAIN ” – however that doesn’t work for the other query types (UPDATE/INSERT/DELETE).

So, one solution which may explain why the query is slow is to turn on MySQL’s profiling functionality, like in the following example :
Continue reading “MySQL update/write query analysis (query profiling)”