Tag Archives: MySQL

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

Working for a client which 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.

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

Continue reading

MySQL: How to clone a table

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: 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

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 :P There’s also of course all the obligatory spelling errors I only discover after I have clicked Publish :P

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

MySQL: How to reset the root account

I just managed to mess up the MySQL root account. Not a smart idea. After some MySQL manual reading and some serious Google-Fu, I figured out how to fix it.

  1. Stop the mysql server
  2. Add the following to the my.cnf file
    [mysqld]
    skip-grant-tables
  3. Start the mysql server
  4. Start a mysql client and run the following query
    REPLACE INTO mysql.USER VALUES ('localhost','root',PASSWORD('blah'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0);
  5. Quit the mysql client
  6. Stop the mysql server
  7. Remove what we added to my.cnf in step 2
  8. Restart the mysql server

Tadaa.