I leaned a new trick today with LOAD DATA INFILE. I’m migrating some data from an external source, and the Date Format is not the MySQL required YYYY-MM-DD, it was DD-MMM-YY. So how do you load this into a Date Field.


$ echo "02-FEB-07" > /tmp/t1.psv
$ mysql -umysql
USE test;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(d1 DATE);
# echo "02-FEB-07" > /tmp/t1.psv
LOAD DATA LOCAL INFILE '/tmp/t1.psv'
INTO TABLE t1 (@var1)
SET d1=STR_TO_DATE(@var1,'%d-%M-%y');
SELECT * FROM t1;
EXIT


The trick is to bind the appropriate column within the file being loaded to a variable, @var1 in my example and use the SET syntax to perform a function on the variable. Rather cool.

A good tip to know.

Those that know me, know that I‘m a little paranoid about backup automation around the house (because I‘m lazy, and that if it wasn‘t automated, it wouldn‘t get done).

I‘m also a little paranoid about offsite backups, so therefore I have a VPN between my house and my parents‘ house, and send my nightly backups (via rsync) to their place as well.

Normally, when I check my e-mail in the morning, I see a little report about what got backed up. I didn‘t notice that it was missing this morning, and instead it arrived at 3:00 in the afternoon.

That seemed a little odd, so I went and looked at my cricket instance, and low-and-behold, the outbound rsync had the 768Kbps side of my DSL connection pegged since 02:00 AM. A little “du”ing around on my backup server, and I found out that a backup had taken place in the middle of a torrent of FC6. That wouldn‘t have been a problem, except that BT stores …

Those that know me, know that I'm a little paranoid about backup automation around the house (because I'm lazy, and that if it wasn't automated, it wouldn't get done).

I'm also a little paranoid about offsite backups, so therefore I have a VPN between my house and my parents' house, and send my nightly backups (via rsync) to their place as well.

Normally, when I check my e-mail in the morning, I see a little report about what got backed up. I didn't notice that it was missing this morning, and instead it arrived at 3:00 in the afternoon.

That seemed a little odd, so I went and looked at my cricket instance, and low-and-behold, the outbound rsync had the 768Kbps side of my DSL connection pegged since 02:00 AM. A little "du"ing around on my backup server, and I found out that a backup had taken place in the middle of a torrent of FC6. That wouldn't have been a problem, except that BT stores incomplete files in your …

Those that know me, know that I‘m a little paranoid about backup automation around the house (because I‘m lazy, and that if it wasn‘t automated, it wouldn‘t get done).

I‘m also a little paranoid about offsite backups, so therefore I have a VPN between my house and my parents‘ house, and send my nightly backups (via rsync) to their place as well.

Normally, when I check my e-mail in the morning, I see a little report about what got backed up. I didn‘t notice that it was missing this morning, and instead it arrived at 3:00 in the afternoon.

That seemed a little odd, so I went and looked at my cricket instance, and low-and-behold, the outbound rsync had the 768Kbps side of my DSL connection pegged since 02:00 AM. A little “du”ing around on my backup server, and I found out that a backup had taken place in the middle of a torrent of FC6. That wouldn‘t have been a problem, except that BT stores …

So far, this year, past and planned (as in currently reserved) travel means I will spend the equivalent of at least 21 work days inside aircraft - just for MySQL related travel.

eep.

Mark Radcliffe joins us this week to give his expert opinion on the latest draft of GPLv3. Mark is a friend and one of the industry's premier IP attorneys, especially with open source licensing questions. He is outside counsel for the OSI and chairs Committee C in the GPLv3 drafting process.

In other words, he knows his stuff.

Dave and I invited Mark to contribute to Open Sources on GPLv3. Here's his response:

The most recent draft of the GPLv3 was released on Wednesday, March 28. This guest blog will summarize the legal issues in the draft and some of the open issues. I have been involved in the process since the beginning because I am the chair of Committee C, the Users Committee, and I serve as outside general counsel on a pro bono basis for the Open Source Initiative. These comments are mine alone and do not represent the views of any of my clients.

The draft is part of a year long process of …

One very interesting thing I noticed with MySQL was that if you delete a database, ibdata file doesn’t shrink by that much space to minimize disk usage. I deleted the database and checked usage of /usr/local/mysql/var folder and noticed that ibdata file is still the same size. So the problem I face now [...]

Two open source funding announcements yesterday (Vyatta and Silicon Navigator) reminded me that I needed to also post the March funding announcements for Avidence and Penguin Computing.

Avidence: March 19th funding announcement (press release) of $5m in Series A by Red Rock Ventures and ArrowPath Venture Partners. Avidence is an early-stage startup in the process of developing an open source platform for digital video.

Penguin Computing: March 29th funding announcement (press release) of $9m in Series B, led by vSpring Capital, with participation from existing investors San Francisco Equity …

I guess many of you know, that using SELECT count(*) FROM table is problematic and slow when using Innodb tables.
This actually only applies to COUNT(*) queries without WHERE a clause as mentioned in the MySQL Performance Blog.

But if you got some slow count query in your application the best way to increase its performance is to replace / remove it.

So if you are going do to "SELECT count(*) FROM products" the best way, is to have a separated table
that stores the number of products. If you're inserting a row increment the counter, if you're deleting a row, decrement it.

Here is some example:
CREATE TABLE counter( number_of_products int(10) DEFAULT '0' NOT NULL);

Increment when you're adding a new product to the products table:

PLAIN TEXT SQL:

1. UPDATE counter SET …

Yesterday we had our sixth MySQL Meetup here in Hamburg - we had a nice crowd of 21 people. Erick Dennis and Michael Otto from epublica gave a presentation about their experiences with MySQL and scaling it to keep up with the growth of the Xing.com web site. The talk was a rehearsal for their presentation at the upcoming MySQL Conference and Expo in Santa Clara - it went very well and we had good discussions and comments during and after the session. I took some pictures during the meeting, which are now available on my picture gallery. The meeting was …