/etc/cron.daily/logrotate:
error: error running shared postrotate script for /var/log/mysql.log /var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log
run-parts: /etc/cron.daily/logrotate exited with return code 1

What’s happening is that after all the mysql backing up and everything is done, ubuntu is trying to use the debian-sys-maint user to flush the logs, this is actually called in /etc/logrotate.d/mysql-server. On my system, we seem to have lost this mysql user.

The solution is to look for the password used in the /etc/mysql/debian.cnf file, mine looks like this:

# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = xxxxxxxxxxxxxxxx
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
user     = debian-sys-maint
password = xxxxxxxxxxxxxxxx
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr

Using the password, and some inspiration from this post on the Ubuntu Forums I recreated the user with the necessary permissions and password with:

GRANT RELOAD, SHUTDOWN, PROCESS, SHOW DATABASES, SUPER, LOCK TABLES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY PASSWORD 'xxxxxxxxxxxxxxxx'

This did the trick, as the nightly errors have now disappeared from my script. Hope this helps someone in a similar situation – if you can expand these notes then please add a comment and I will update this as needed – thanks :)

SELECT * 
FROM table1 t1
LEFT JOIN table2 t2 ON (t1.col_a = t2.col_a)

So far, so good, right? In this particular instance though I wanted to filter my results on a particular criteria so I added something like:

SELECT * 
FROM table1 t1
LEFT JOIN table2 t2 ON (t1.col_a = t2.col_a)
WHERE t2.col_b <> 42

This is where it all started to go a bit weird. I wanted all the rows from the first table, plus anything from the second table that matched but didn’t have col_b equal to 42 (this is a rather contrived example I know but my real-world example was so complicated I’d still be explaining it if I started). The syntax above was causing a problem in the case that there was a single matching row in the second table which matched the criteria in the where clause. In this instance, the entry from the first table would appear once, and then immediately get filtered out by the constraint in the where clause.

What I actually needed to do was filter the second table before the join happens. So here’s the syntax:

SELECT *
FROM table1 t1
LEFT JOIN table2 t2 ON (t1.col_a = t2.col_a AND t2.col_b <> 42)

It turns out that you can use the ON clause when you join to include anything that you would usually put in a where clause to restrict the rows on the table that is being joined. Hope this makes sense and is useful to someone, add your comments if you have any!

My talk was “Source Control Saves Lives”, a primer on what source control and why you should use it, with a definite subversion twist because that’s what I know the most about and I’m not a fan of talking about stuff I don’t know about! For posterity, here are the slides – I haven’t done any presentations for years and I couldn’t remember how to work powerpoint svn_saves_lives.ppt

I borrowed Kevin’s mac and downloaded the demo of iWork. Its a 30 day trial and after its expired I can still use it as a player. Which is excellent as using the mac remote in the talk worked really well and got around the awkward layout of the room I was allocated.

The event as a whole was fabulous – I met some amazing new people and have a stack of business cards of people to email and catch up with. I don’t have my own cards which is proving really awkward at this kind of event, perhaps I will get some in the future when I’m attending something like this since they’re not expensive. The new contacts I made could turn out to be really interesting in a whole load of ways – and the free beer and chatter with like-minded people was very welcome as well. Talking over experiences and projects with others has left me refreshed and inspired, in a way I didn’t expect. What more could a girl want?

Bar Camp Leeds is this weekend, Saturday 17th November 2007, and its wildly oversubscribed. I know a few people who were hoping to go and haven’t been able to get places. At the time of writing, I’m still planning to speak but there’s less than 48 hours to go and I haven’t got further than deciding I may speak about source control, and subversion specifically. The nerves are starting to kick in …

show processlist;

If you’re a phpMyAdmin user, then there’s a link for “processes” on the front page of your installation. Give it a try when things seem to be taking longer than they should!

Choosing a Topic

This actually wasn’t hard because I wanted to pick something that I have particular knowledge of. As a part-trained Oracle DBA with experience of Oracle and PHP, these two topics seemed like an obvious choice.

Preparing the ‘Cast

I was quite determined not to read aloud from a script, so I put together some bullet points and practised doing the talk from those quite a lot. Playing back these attempts helped me to see what worked and what didn’t – those points got added, removed and re-ordered quite a few times. Even after a lot of practise, I was still pausing too much between points. I ended up transcribing my unscripted version and then re-recording it, which worked well as I had my own natural language but no pauses!

Sound and Software

I was concerned that I would need additional equipment, such as a better microphone. However my partner has a macbook and the built-in microphone on that proved to be up to the job. I used Garageband from Apple’s ilife to record the track (and also to edit out the clicking I got from pressing the mouse to start/stop recording!). This saves files in .m4a format, but this can be converted to .mp3 using itunes.