As you can read from my Innodb Architecture and Performance Optimization presentation Innodb automatically manages undo area in system tablespace so you never need to care about it. I present it as positive feature reducing administration effort needed but it also can cause a troubles as it happened for me today:

InnoDB: 11 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 292735956 row operations to undo
InnoDB: Trx id counter is 0 96267520

So MySQL Server was restarted (it likely was admin mistake in this case) and spend hours to undo almost 300.000.000 of row operations being unavailable during all of this time.

This was MySQL 4.1, MySQL 5.0 would do better by performing roll back in background but affected data still might not be fully available.

Why one would use such large transaction ? Well it was development mistake. Long and complex data load process was performed in …

I'm just back from OpenSource Database Conference and PHP International Conference which took place in Frankfurt.

I've uploaded slides for two talks I've been giving which you might want to check out.

In general Database portion of the conference was a bit boring. May be because it was not widely announced or may be for some other reason. There were number of talks about MySQL by Arjen Lentz, Me and Giuseppe Maxia . There also were talks about Firebird, Apache Derby, Ingres and DB40. There however were no talks about PostgreSQL which is probably second most popular OpenSource Database or any others.

There were number of nice talks in PHP section - I especially enjoyed talks about PHP6 localization and DateTime …

As I had the occasion to take an existing database and make some wholesale changes to it and reload it under a new name, and particularly wanted to do this possibly numerous times while I tweaked the changes I'm making, the thought had crossed my mind to use sed or something similar to take a database dump, modify a few of its particulars, and load it under a new name. With that in mind I created a bash script rather like the following:

#!/bin/bash

mysqldump -h server -u root -ppassword \
      --database olddatabasename \
      --routines \
      --ignore-table=olddatabasename.page_content \
      --add-drop-database \
      --add-drop-table \
\
| sed -e "s/\`//g" \
      -e "s/olddatabasename/newdatabasename/g" \
      -e "s/Song/Work/g" \
      -e "s/song/work/g" \
      -e "s/artists_works/artist2work/g" \
\
| mysql -h server -u root -ppassword

mysql …

MySQL Camp is going to be AWESOME! 163 participants! Insane! Just insane! This was a brilliant idea (my idea btw).

Jay Pipes did a lot of the work here so he deserves a lot of props.

I bumped into Marten Mickos here at Web 2.0 and he's going to be at the conference as well.

MySQL AB invited me to Frankfurt to talk about their PHP native driver project. While I am not much of a C-hacker, I do know database API's and users preferences in them quite well from my work on MDB2. As such my job was to present a wish list for features. Do note that this is a wish list and any features I mention in this blog post are not yet in any definitive plan. Aside from that, I also took it upon me to make sure that whatever we do, we try to keep the API as clear and simple as possible.

Anyways, at first I was wondering if the chance for a speed increase was really worth MySQL AB throwing developers at this. I am also not that concerned about the license of libmysql. With the FLOSS exception everything seems more or less ok. So why go through all the trouble? A lot of the wish lists items could easily be implemented on top of …

reporting about david geffen?s apparent bid for the los angeles times, nikki finke says ?He?ll ratchet up the Web site (even though he hates how prohibitively expensive it is to do that).?

prohibitively expensive? i guess there is still a lot of stupid money flowing into web properties. i?m in the wrong line of work.

a strange little side-note: mysql?s website gets more traffic than latimes.com, according to alexa.

I was over at the O'Reilly Web 2.0 conference and ended up in the "overflow" room.  At most conferences, the overflow room is like a dungeon for late comers.  At Web 2.0, they made this into a premium suite; it was sponsored by Google and there were comfy chairs, padded cubes to sit on, plenty of snack food and fridges stocked with your choice from dozens of caffeinated or healthy beverages.  Frankly, it was more comfortable in the overflow room than in the main conference area.  I happened to try a sugar-free energy drink called XS, from Quixtar (the Amway guys).  I'm not enough of a conoisseur to tell the difference between Red Bull and …

A bit late maybe, but I finally setup a new meeting: 29th of November.

If you're interested you can sign up to attend here: http://mysql.meetup.com/148/. If you don't like to register with Meetup, then just let me know and I'll sign you up for the meeting as guest.

On the agenda:
- Get to know each other
- New stuff in MySQL 5.0/5.1
- MySQL Enterprise: The WTH?-questions answered
- New storage engines
- and more..

Make some noise!

A bit late maybe, but I finally setup a new meeting: 29th of November.

If you're interested you can sign up to attend here: http://mysql.meetup.com/148/. If you don't like to register with Meetup, then just let me know and I'll sign you up for the meeting as guest.

On the agenda:
- Get to know each other
- New stuff in MySQL 5.0/5.1
- MySQL Enterprise: The WTH?-questions answered
- New storage engines
- and more..

Make some noise!

Above is a diagram of the Friendster backend at the time I was there. The entire presentation can be found here at mysqluc.com. Due to all the turmoil at Friendster, even with the coolest and most competent VP of Engineering in the business Jeff Winner we could not push my intended design. This design is now used at Flickr with huge success.

What I wanted to do was introduce a system that allowed Friendster to partition the user data. This was the same system that I started to implement at AuctionWatch in 1999 and was not able to …