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
- Go to dev.mysql.com/downloads/timezones.html
- Download the POSIX standard Time zone description tables, version 2011n zip archive
- Extract the files and overwrite the ones in C:\wamp\bin\mysql\mysql5.6.17\data\mysql, or whatever your equivalent path would be
- Restart MySQL
Setting the time zone should now work
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
This can of course be skipped if you already know how to access your mediawiki database, but I didn’t
- SSH into the system where MediaWiki is installed and find its location.
/var/www/html/foowiki in our case.
LocalSettings.php and everything should be under the heading
## Database settings
- Connect with a mysql client.
mysql -h localhost -u wikiuser -p wikidb
- Find the user id.
SELECT user_id, CONVERT(user_name USING utf8) FROM `user`;
- 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.
Needed to get a LAMP environment up and running for some Kohana development. Starting with a more or less fresh install of Ubuntu, here’s what I did.
Continue reading Quick installation of LAMP for Kohana on Ubuntu
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
Ever wanted to, for some reason, clone a database table? Don’t really want to export anything or figure out what statement was used to create that table? Turns out that’s a lot easier to do than I thought it was.
Continue reading MySQL: How to clone a table
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
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