Tag Archives: MySQL

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

How to delete WordPress post revisions

Having revisions on my posts is nice I suppose. But I was starting to get a bit annoyed with the increasingly long list of revisions in each post. And they really did get long. Mainly because I am in a bit of a testing and experimenting phase which means that I have done a lot of adjustments to almost every single post so far because I don’t have 100% control on how I want things or how things will end up looking and so on 😛 There’s also of course all the obligatory spelling errors I only discover after I have clicked Publish 😛

Anyways, I found a nice little MySQL snippet to clear out all of them in a French comment to a blog post. Thought I could share it here. That way I won’t lose it either 🙂

Continue reading How to delete WordPress post revisions