Useful SQL Commands

I recently needed to take a database with separate date and time columns, convert that combined date-time data into Unix time format (i.e. seconds since the epoch [1st Jan 1970]), and add a new column to the database with that data. It took me a fair bit of googling to get it all working, so here’s my notes.

Sorry this isn’t really in an article format – but I need this written down and available for the future… Also, I wrote a small Java app to read the separate date and time text files and spit out a timezone-correct unixtime text file, which I then added to a spreadsheet containing just the the primary key rows and exported it as tab-separated values suitable for MySQL (or really, MariaDB in this case) import.

Show the table structure for the ‘accidents’ table

Count unique values in the UNIX_TIME column in the accidents table

Find duplicate values in a column (cannot make column a primary key if there are dupes!)

Make a column be the primary key for a table

Drop the UNIX_TIME column

Add a new column UNIX_TIME to the accidents table after the last column (STAT_DIV_NAME)

Add an empty column with no default value

Create a temporary table in memory with a single column called UNIX_TIME which is of type BIGINT

Import from local CSV file

I created a tab-separated CSV file which had just the ACCIDENT_NO as primary key and the UNIX_TIME field. We can then import that data into the temptable with:

Load all the single-line data values fromthe UNIX_TIME.txt file into the UNIX_TIME column of our temp table

Merge the temptable with just the ACCIDENT_NO and UNIX_TIME into the accidents table using ACCIDENT_NO as the primary key

So in this particular instance where I had a partial update, I was missing 13,375 UNIX_TIME values!

Make sure this worked by setting all the UNIX_TIME values in the accidents table to zero, and then re-importing

Now finally, we can drop the in-memory temptable!

Export the database

To export the database we can use the mysqldump utility (this is NOT within the MySQL command line!):

Then to import this dump, let’s say we don’t have a govhack2015 database

Followed by (in the MySQL command line):

It all works! =D

How to stop a MySQL action which is taking forever… (like when I bodge a merge) – in a different MySQL commandline interface

How To: Connect to a MySQL database using Connector/.NET in C#

Try this:

How To: Disable All WordPress Plugins from the Database

I added a couple of WordPress plugins the other day to tack on a little bit of extended functionality to the site, but things went badly, and some bizarre interactions between plugins meant that I couldn’t log in to the WordPress administration interface – it would just throw me back to the main site without even having the option to log in… As such, I couldn’t disable the plugins (because you need to be in the administration interface to do that!), so what the heck to do?

You might think to ssh in to the box and remove the plugins from the …/wp-content/plugins/ folder to disable them (as I did) – only this won’t work. What we really need to do is disable the plugins directly from the database – and luckily for us it’s a pretty simple process as long as we can ssh into the box!

Note: If you dont’ have ssh access to your server, you could always take the phpMyAdmin route to achieve the same goal through a web interface, but if the problem you have is related to being redirected all the time (like mine was) then the direct DB access way is really the only way to go!

Gaining SSH Access

If you’re running linux you’ve already got a ssh client built in, if you’re on Windows then you’ll probably want to get yourself a copy of PuTTY or such.

Assuming the IP address of your server is 1.2.3.4, then you can ssh to the box with:

In the above command the switch is “minus lower-case-L”, not “minus pipe-symbol” or anything, and if you’ve changed your ssh port away from the default of 22, then you can just add the switch -p , so if my ssh daemon was running on port 2233, and I wanted to log in as the user bob, I’d use:

Connect to MySQL

Once you’ve got a ssh connection to the server, the next thing you’ll want to do is connect to your database, with the following command:

So if I had a MySQL administrator called dbAdmin I’d use:

The -p switch on the end will prompt you to enter the password for your MySQL admin user account – don’t worry if you’ve forgotten the credentials you should use – they’ll be in your WordPress wp-config.php file, which is usually located in your www or htdocs root folder (on Linux this is usually /var/www).

Quick Tip: By placing your wp-config.php file in the web root it’s possible for it to be accessed by malicious types, but you don’t have to place it here! You can, in fact, move the file one level up so that it’s outside of the web-root and WordPress will still be able to find it, but scoundrels won’t! So if you’re running WordPress with all your files in /var/www/ just move the wp-config.php file up to /var/ and it’ll still work and be safer! You can do this from the ssh terminal with:

Manipulate the Database

Once you’ve got administrator access to MySQL you need to connect to your database (again, the database name will be in your wp-config.php file), so if we’d called our database myWebDB then we could connect to it through the MySQL command line interface with:

Once this has connected successfully, all you have to do to disable all plugins is issue the following command:

That’s it! Attempt to access your site! Any plugins which were causing mayhem will now be disabled, and you can then add them back one-by-one until you find the combination that’s stuffing things up!

Cheers!

Props: Many thanks to Jeff Star of PerishablePress for his article Quickly Disable or Enable All WordPress Plugins via the Database which saved my bacon, and prompted me to write this quick guide.
Flattr this