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 …

On Monday August 20th, 2007, the Google Summer of Code officially ended. I have had a great time this summer, although it has not always been sunshine and flowers! Because of the nature of the Summer of Code, setbacks due to lack of knowledge were not a problem. It’s expected that the students don’t know everything!

So mostly the setbacks were organizational. I had 2 students working on MySQL Auditing Software, which I have tentatively (and very geekily) called OughtToAudit. One student was working on the administrative interface, where access to the auditing program and the auditing rules themselves are defined. As well, reporting on suspicious activity as well as the rule-breaking activity could be seen. The other student was working on a pcap (libpcap, winpcap) engine to store all database traffic. Why pcap? One of the main tenets of auditing is that the auditing system is independent of the system to be audited. Part of this is for …

I've just released changes to all tools in MySQL Toolkit. The biggest changes are in MySQL Table Sync, which I'm beginning to give sane defaults and options to. Some of the changes are incompatible (but that's what you get with MySQL Table Sync, which is still very rough). I also found and fixed some bugs with MySQL Visual Explain. Thanks to everyone who submitted bug reports.

Note, the formatting overflow in MySQL Query Profiler was not a security vulnerability. It was simply an issue with a Perl formatting code that displayed numbers as hash marks when they got big enough.

There’s an interesting dynamic going on right now in the DBA world. MySQL’s growth and installed base, as a function of its size three or five years ago, is perhaps five if not ten times larger than it was. In 2002 when Pythian’s MySQL services launched, we took on the platform at the explicit request [...]

In one of the last commits I added a SQL Tokenizer which understands the basic tokens of (My)SQL:

• Strings
• Literals
• Numbers
• Comments
• Operators

With this basic understanding we can normalize Queries and build statistics over similar queries.

The idea is simple and already implemented in mysqldumpslow:

  /* login.php:37 */SELECT * FROM tbl WHERE id = 1243 AND name = "jan"

is turned into

  SELECT * FROM `tbl` WHERE `id` = ? AND `name` = ?

The queries look like prepared statements now and can be used the characterize queries of the same kind.

• comments are removed
• whitespaces are stripped
• literals are quoted
• constants are replaced with ?

Taking the famous world-db and executing some simple queries like: