the most awesome guy ever.

The Blog of Darryl E. Clarke

  Random musings from a jaded coder who just needs a hug.

Archive for the ‘MySQL’ Category

Adding an Auto Increment Column After The Fact…

Tuesday, September 29th, 2009

Yep, I pulled a ‘whoops’ today. I made a table, filled it with data, then realized I forgot my standard ‘id’.  My primary key, auto incrementing column that makes me feel good about myself.

Once I noticed it was gone, it was easy to add it back. Right? Right? Well, not so fast.  You can add the new column at the beginning of the table.  But as soon as you go to set it to a Primary, Auto increment, MySQL is going to cry because of all the zero values.  The primary key’s can’t be the same so it blows up.

Before setting it to be the primary key, you need to auto-fill it.  You can do it programmatically with any script of your choice, but I find it easy to do it with MySQL…

SET @pants := 0;
UPDATE `ratings` SET id = (SELECT @pants := @pants + 1);

And that’s it.  Time for a beer.

Tags: , , ,
Posted in MySQL

Summer Project: Setup PostgreSQL

Saturday, May 23rd, 2009

Using my shiny new upgraded Ubuntu 9.04, I’ve gotten around to installing PostgreSQL.

Note: this is not a tutorial.

The installation was simple, as are most installations with Ubuntu. The initial “get going” was a bit odd for PostgreSQL though. The one thing you have to remember when initially trying to get things going is this… (more…)

Tags: , ,
Posted in Coding, MySQL, Ubuntu

Ubuntu, AppArmour, and Moving MySQL Data

Wednesday, January 28th, 2009

Note to self, when moving the path where mysql stores it’s files, make sure you edit the App Armour file to allow mysqld to write to the new location.

Time wasted: 30 minutes.

The message:

kernel: [422118.789696] audit(1233167081.635:38): type=1503 operation="inode_create"
requested_mask="w::" denied_mask="w::" name="/opt/mysql/pluto.lower-test"
pid=12412 profile="/usr/sbin/mysqld" namespace="default"

The solution: edit this file;

/etc/apparmor.d/usr.sbin.mysqld

And add the necessary new paths… Good times, right there.

Tags: , ,
Posted in MySQL, Ubuntu