Tag Archives: MySQL

Enabling query logging on MariaDB/MySQL

When developing it can sometimes be very useful to see exactly what queries are sent to the database.

Found out there’s something called a General Query Log, and enabling it was really easy. Just add the following to your my.{ini,cnf} file:


Restart the server and you should now find the log in your data directory (unless no queries done yet).

If you don’t know where your data directory is, just run this query:

SHOW VARIABLES WHERE variable_name = 'datadir'

Missing time zones in WAMP MySQL

If you try to run for example SET time_zone = 'Europe/Oslo' on the default MySQL database that comes with WAMP you might get an error saying the time zone doesn’t exist.

Apparently this is because the time_zone tables for some reason are empty.

How to fix

  1. Go to dev.mysql.com/downloads/timezones.html
  2. Download the POSIX standard Time zone description tables, version 2011n zip archive
  3. Extract the files and overwrite the ones in C:\wamp\bin\mysql\mysql5.6.17\data\mysql, or whatever your equivalent path would be
  4. Restart MySQL

Setting the time zone should now work 🙂

Promoting a user to sysop when all sysops are gone on a MediaWiki installation

Working for a client who has documentation on a MediaWiki installation which was set up before we got there. Was able to create users for ourselves through the regular site, but certain things like deleting pages can only be done by sysops. None of our newly registered users are of course sysops and the only existing sysop user is from whoever installed the wiki to begin with.

Here’s how to promote a user to sysop the hackish way, directly in the database.

Find database details

Note: This can of course be skipped if you already know how to access your mediawiki database, but I didn’t 🙂
  1. SSH into the system where MediaWiki is installed and find its location.
    Was /var/www/html/foowiki in our case.

  2. Open LocalSettings.php and everything should be under the heading ## Database settings

Promote user

  1. Connect with a mysql client.
    mysql -h localhost -u wikiuser -p wikidb
  2. Find the user id.
    SELECT user_id, CONVERT(user_name USING utf8) FROM `user`;
  3. Add user id to sysop (and bureaucrat) group.
    INSERT INTO `user_groups` VALUES (uid, 'sysop'),(uid, 'bureaucrat');

The user should now be sysop, which can be double checked on the Special:ListUsers wiki page.

MySQL tables for continent names, country names and their ISO-3166 codes

Map of the world Here is a MySQL table containing continent names, country names and their ISO-3166 codes.

Needed one a while ago, but the ones I found were either kind of lacking or kind of old. So I made one myself by converting a datafile on Wikipedia into the format I wanted. Used some regular expressions and manual corrections. Later I also went through newsletters with Updates on ISO 3166. Hopefully I got it all right, and hopefully it can save you and others some time as well.

I have also subscribed to their updates and try to follow up when they change anything.

If you find any mistakes or updates I’ve missed, please let me know 🙂

Continue reading MySQL tables for continent names, country names and their ISO-3166 codes

MySQL: Filtering with Regular Expressions

Today I wanted to list all all users in a database who had been too lazy to uppercase the first letter in their name. But how can you do that in MySQL? With regular expressions such a check would be easy to write, but this was in MySQL, not in for example PHP… but wait a minute… MySQL actually supports Regular Expressions? Yes, it does! I honestly had no clue.

Continue reading MySQL: Filtering with Regular Expressions