Occassionaly when I'm working on a site I might append an entirely new set of database tables on an existing website. After a while the database might become so large that it's neccessary to drop older tables.

Tables that share the same prefix can be dropped with the following 2 part method:

In this example, the database table prefix is _elgg

First run the following SQL command:

SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '\_elgg%' 
AND TABLE_SCHEMA = 'your_database_name';

This will return a bunch of commands to drop each individual table. Running each of these SQL queries will infact solve this issue, but we can go one step further to automate things a little more by running the following query:

SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '\_elgg%' 
AND TABLE_SCHEMA = 'your_database_name' 
INTO OUTFILE '/tmp/whatever_filename';

SOURCE /tmp/whatever_filename;

The same can also be achieved directly within the command line by running the following:

mysql -B databasename -uroot -prootpassword --disable-column-names  -e `mysql -B databasename -uroot -prootpassword --disable-column-names  -e "SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '\_elgg%' AND TABLE_SCHEMA = 'your_database_name';"`

Published in Blog
Wednesday, 20 July 2016 01:46

Disable all plugins using mySQL

Sometimes when trying to log into the wordpress back end I get the dreaded whitescreen of death. This is usually caused by a plugin. The solution is usually realtively painless: disable all the plugins. This fixes the admin login whitescreen issue. Plugins can then be enabled individually, checking to see which plugin breaks the login page.

Published in Blog
Wednesday, 18 November 2015 12:17

Changing relationships in SQL

The following changes the relationships between worldpress posts and their category, although it can be modified for other uses.

the example changes posts in category ID #10 and places them in category ID #20

UPDATE wp_term_relationships 
SET term_taxonomy_id = 
   (SELECT term_taxonomy_id FROM wp_term_taxonomy WHERE term_id =10)WHERE term_taxonomy_id =
   (SELECT term_taxonomy_id FROM wp_term_taxonomy WHERE term_id =20)
Published in Blog

I've been trying to migrate a wordpress site onto a very old IIS server, and keep coming up with an error when importing the database into phpMyAdmin:

#1273 - Unknown collation: 'utf8mb4_unicode_ci' 

Fortunately the issue is fairly easy to fix. In phpMyAdmin of the source website:

  1. Click the "Export" tab for the database
  2. Click the "Custom" radio button
  3. Go the section titled "Format-specific options" and change the dropdown for "Database system or older MySQL server to maximize output compatibility with:" from NONE to MYSQL40.
  4. Scroll to the bottom and click "GO".
Published in Blog