Previous post

http://consoleninja.net/code/dpm/rel/dpm-r2.tar.gz - tarball of r2
git clone http://consoleninja.net/code/dpm/dpm.git - to get the latest code, always
http://consoleninja.net/code/dpm/dpm-export.tar.gz - a tarball of the latest code, for those unwilling to git it.

I'll keep this short. Also, just noticed my tags aren't getting uploaded to the remote git. Sorry.

Bunch of commits worthy of a new release:
- new included pass-through demo.
- cleaned the included connection pooling demo a little.
- 'proxy_until' demo. Disables packet processing temporarily. …

In MySQL Proxy learns R/W splitting we did a first implementation on idea of sending non-transactional reads to the slaves and writes and transactions to the master.

While that was mostly a proof of concept it already worked pretty well. In this second round on R/W splitting several issues are taken care of:

• SELECT SQL_CALC_FOUND_ROWS ... + SELECT FOUND_ROWS()
• INSERT ... + SELECT LAST_INSERT_ID()
• default_db not in sync between client and backend

These changes should make Read/Write splitting more robust and make it a drop-in for more users.

Up to now we used the tutorial-keepalive.lua to implement the Read/Write-splitting. As the name says, it is a tutorial for connection keepalive, …

MySQL Camp II is complete. A small group of about 18 had post dinner at Tiny Thai in New York City. Some elected to drive from Brooklyn, they arrived at least 30 minutes after those of us that the subway.

I have a lot of notes to write, if ever the time permits. For now, the following few that joined for drinks are below. I know other people took photos of the camp, for a change I actually took none. If you want to add a link in comments of photos from the camp that would be great.

Other sizes here

So last night I had to resized one of my Xen partitions. So what was the magic to make this happen?


dd if=/dev/zero count= >> /var/lib/xen/images/shiitake.dsk


Then? I need to increase the partition map for my disk:


fdisk /dev/xvda


What did I do in fdisk? I deleted the partition, and then recreated it with the new available blocks. Of course the machine was running at the time. What is life without a few risks? But I have to ask myself, why doesn't fdisk have a resize command? I swear that tool hasn't changed in well over a decade.

I am running LVM so then I had to:


pvresize /dev/xvda2
lvresize -L +5G /dev/VolGroup00/LogVol00


And finally since the filesystem is ext3:

e2fsck -f /dev/mapper/VolGroup00-LogVol00
resize2fs /dev/mapper/VolGroup00-LogVol00
…

Well, after sitting for two hours on the runway at JFK airport, I finally got home to Columbus late last night. Having such an abundance of free time (while waiting on the runway), I thought about the great three days I had had meeting with Adam and Sandro from the SKOLL Cluster on Wednesday and with the MySQL Campers on Thursday and Friday.

Camp has renewed my faith in the feeling of connection that being a MySQL community member gives to me. From newbies to old hands, MySQl Camp II brought together a great mix of inidividuals and allowed me to meet with a great group of talented and interesting folks. I'm very much looking forward to MySQL Camp III in the Washington D.C. area. A date hasn't been set yet, but likely we'll pick one in the near future. A Google Group has been set up for MySQL Campers, so if you're interested in getting notified about upcoming camps and …

You have inadvertently deleted some rows from your table and want them back. This is semi-doable with fixed row format of MyISAM. I put together a few steps, which I'll compliment with a program that does it for you, later.

• save output of SHOW TABLE STATUS LIKE 't1' and SHOW CREATE TABLE `t1`.
  
• shutdown mysql server asap!
• backup t1.frm, t1.MYI, t1.MYD immediately
• create a new table t1_recover, which has no auto-inc and no unique/pk.
• remove the files t1_recover.MYD and t1_recover.MYI
• write a C program that scans through t1.MYD reading blocks of length Avg_row_length and checking if first byte indicates a row is marked as deleted.
• if it's deleted, dump it as is into the file t1_recover.MYD
• goto mysql prompt. issue REPAIR TABLE `t1_recover` USE_FRM

Some notes.

You cannot recover entire record. You'll …

I caught most of the second day of MySQL Camp 2007. It was fun and educational as before. The format was a little different than the last Camp; everything was in one room. Google and Proven Scaling provided food.

Sessions were loosely organized, to say the least, but that’s what an un-conference is [...]

Making my Quad G5 Mac work a little harder... Now that it has 4G of RAM, it doesn't seem to swap as much and I can happily build up to 2 MySQL source repositories and a couple of concurrent mysql-test-run.
I am currently experimenting with a RAM drive for handling the test run data, using something like the following for creating a 512M RAM disk.

    
diskutil eraseVolume UFS RAMDisk `hdid -nomount ram://1048576`
sudo chmod g+rwt /Volumes/RAMDisk

export MTR_BUILD_THREAD=auto
export MTR_MEM=/Volumes/RAMDisk

So far, looks pretty good.

Quick quiz: when can this happen? When can revoking a privilege from a user “grant” them another privilege?

I try to select from a table, and am denied.

(bentest@db3) [test]> select * from gbidsuggestion_0616 limit 1;
ERROR 1142 (42000): SELECT command denied to user ‘bentest’@'db4.adapt.com’ for table ‘gbidsuggestion_0616′

As super, I connect and revoke a different permission for user ‘bentest@db4.adapt.com’

(root@localhost) [test]> revoke insert on test.* from bentest@’db4.adapt.com’;
Query OK, 0 rows affected (0.00 sec)

Now I log back in as the original user, and I can do the select.

(bentest@db3) [test]> select * from gbidsuggestion_0616 limit 1;
+————————–+…+———+
| gbidsuggestionid |… | active |
+————————–+…+———+
| 1 |… | 0 |
…

In continuing with my personal exploration in BLOBs, I thought I would change gears a bit. Previously I had focused on benchmarking the MyBS engine being designed by Paul McCullagh (the results of which you can find here, here, and here). But this time around I thought I would try something a bit more direct.

I have been playing around storing photos as BLOBs in the database for my photo album application (in preparation for testing how the MyBS engine scales as opposed to storing images on the filesystem). So since I had all that data there already, I thought it might be fun to try and grab the BLOBs directly from the database using a a SELECT...INTO DUMPFILE... I whipped up a quick bash script which first runs an ALTER TABLE ... ENGINE=XYZ and …