I have written a new book on SQL DML. This is a total beginner book: how to commit and rollback, how to query, how to add data, etc.

Probably not of interest to most of the people who read this blog but if you know of anyone completely new to SQL, this would make a great Christmas present. Only 14.95. It is completely vendor agnostic, although the examples all use Oracle and MySQL.

You can view the Table Of Contents, Preface and Index here. I plan to release some of the chapters for free on the blog and will make the PDF of the book available at a discount. I have several more

How do you teach a thinking pattern? In my previous job I worked with a data analyst, who was really good at thinking about how our data correlates among itself. He was good at the data analysis, good at thinking up relationships, and good at coming up with complex comparisons.

However, he wrote some pretty funky SQL. We had him take a course in basics of MySQL, and from time to time I’d take a gander in the slow query logs and pick a few queries and point out the good and bad things. Most of these are optimization tips, such as “Indexes don’t apply to columns when you’re applying a function to that column”.

But it’s hard to try to think outside of your own box, and even running EXPLAIN on every query won’t necessarily tell you how you can fix a query. The problem is that there’s no way to know if you have optimized the query as much as possible.

http://www.oracle.com/openworld/2007/appreciation.html

Now, Billy Joel is one of my all-time favorite pop musicians. I saw him in concert and nosebleed seats at the Boston Garden cost me USD $100 per ticket, and I bought 4 tickets (my twin brother is a die-hard Billy Joel fan, they were a holiday surprise 2 years ago!)

Billy Joel regularly sells out sports arenas. I can only imagine how much Oracle paid to have a concert with him.

And don’t get me wrong, the rest of the list is also stellar. Which only adds to my disbelief.

MySQL shows customer appreciation by not grossly overcharging.

MySQL: Because you’re smart enough to buy your own damn concert tickets.

So, at midnight I got a call from customer service saying our site was slow. I narrowed it down to one of our auxiliary databases, that seems to have gotten wedged just about midnight. Normal queries that took less than 4 seconds started taking longer and longer, moving up to 5 seconds and past 30 seconds in the span of a minute or so.

In the moment, I thought killing off all the queries would be a good move. My kill script, which consists of:


for i in `/usr/bin/mysql -u user -pPass -e ’show full processlist’ | grep appuser | cut -f1`
do
mysql -u user -pPass -e “kill $i”
done


This will attempt to kill any mysql connection owned by the appuser. I used it a few times, and it didn’t work. So I used a trick I learned when we bring our site down — sometimes there are straggling connections to mysql, so what I do is

aka, “Better late than never”…..

Back in March 2007, the Boston MySQL User Group (http://mysql.meetup.com/137) watched and heard Brian DeLacey give a tutorial of Ruby on Rails, including its interaction with MySQL using ActiveRecord.

I knew absolutely nothing about Ruby on Rails before attending the presentation, other than Ruby was a language and people were saying that Rails made for easy development. After the presentation, I knew enough to start coding!

Brian is an excellent speaker, and this presentation is long overdue. (I’d tried creating the video before, during and after the MySQL Users Conference back in April, and my application kept crashing. I guess it just needed a break, because I fired it up today and it seemed to save the movie OK. Please let me know if you watch the presentation and something seems

A sysadmin friend of mine was describing some DBA work he was doing, and wrote this:

I’m not much of a DBA, really - if it can’t be done through phpMyAdmin I’m not likely to be doing it.

This is in stark contrast to so many so-called DBAs who say “I’m a DBA….If it can’t be done through phpMyAdmin it must be Senior DBA work.”

I’ve used phpMyAdmin for MySQL administration, and there’s just something so nice about working on commandline.

Some have pointed out that the 12 Days of Scaleout (http://mysql.com/why-mysql/scaleout/) campaign is a “cheap marketing tactic.”

Why, yes. It’s inexpensive as far as campaigns go. It’s definitely marketing. The grumbling seemed to be that there was no content on how the scaleout happened and worked for these companies.

We have to remember that not everyone is a geek. While we already know and love MySQL, there are people out there who only vaguely understand what a “database” is, much less have even heard of MySQL. Many laypeople I talk to haven’t heard of Oracle!

MySQL needs this kind of marketing. Perhaps it better belongs as an advertisement in a glossy magazine, but I see no problem with MySQL using what they own — lists, forums, PlanetMySQL, its own web page — to do cheap marketing. In fact,

As the 2007 Community Advocate of the Year, I’m taking the “MySQL 5 Wishes” meme and changing it a bit. I hope y’all don’t mind:

1) Everyone has a different level of familiarity. The community does well with this when writing articles, for instance cross-referencing older articles, linking to documentation, the MySQL Forge, etc. Not much background information other than “MySQL usage” is assumed.

However, where we fall down is when we aggregate some writings and call it documentation. The worst form of this is a tool that grows organically, from “look, here’s a script!” to a full-blown tool/patch/add-on. Sourceforge stinks for trying to make documentation, so most folks just link to their posts tagged “mytool” or whatever the name is.

Using some marketing skills would be wonderful — make a page for folks who have

So, this is me:

Special thanx to Colin Charles for taking the picture and linking to it from his blog.

Notice that in addition to my photogenic qualities as well as the bags under my eyes, that I’m wearing an incredibly geeky necklace.

Yes, it’s true. I bought a white gold dolphin to wear around my neck, because I am THAT much of a MySQL geek.

Ask and you shall receive: http://face.centosprime.com/rdb-w/?p=68 linked to my previous post on the Data Warehousing Tips and Tricks session (http://sheeri.net/archives/204) with the comment, “I need to learn more about MERGE TABLES and INSERT ? ON DUPLICATE KEY UPDATE“.

So here’s a bit more:

The manual pages for the MERGE storage engine:
http://dev.mysql.com/doc/refman/5.0/en/merge-storage-engine.html
and
http://dev.mysql.com/doc/refman/5.0/en/merge-table-problems.html

MySQL Forums for the MERGE talbe are at:

http://www.regdeveloper.co.uk/2006/07/18/multivalued_datatypes_access/

This is an interesting read — it would be awesome if MySQL just used the “SET” or “ENUM” data types to be a placeholder for a join table, that it would create automatically for you. Of course, that’s a new level of functionality — MySQL does not implicitly create permanent tables with any commands. But it would be neat.

Thanx to Rich McIver for passing along this link:

http://www.businessintelligencelowdown.com/2007/02/top_10_largest_.html

I’m amused mostly because the article interchanges “database” with “data storage” — many of the sites have “digital documents” included in their count, and YouTube is in there completely with the amount of space their videos take up. But is all this stuff stored in databases? I do not think so. Anyone know for sure?

I stumbled across this on the fabulous http://www.everythingsysadmin.com :

from:  http://www.socallinuxexpo.org/wios07/

The Southern California Linux Expo (SCALE) will host a Women in Open Source Event as part of their upcoming 2007 conference, SCALE 5x.

The focus of this event is on the women in the open source and free software communities. The goal of this event is to encourage women to use technology, open source and free software, and to explore the obstacles that women face in breaking into the technology industry. The Women in Open Source event will be held on February 9, 2007 at the Los Angeles Airport Westin Hotel.

So, a little gotcha:

The CHAR() and VARCHAR() types are different types. MySQL silently converts any CHAR() fields to VARCHAR() when creating a table with at least 1 VARCHAR() field.

http://dev.mysql.com/doc/refman/5.0/en/silent-column-changes.html

If any column in a table has a variable length, the entire row becomes variable-length as a result. Therefore, if a table contains any variable-length columns (VARCHAR, TEXT, or BLOB), all CHAR columns longer than three characters are changed to VARCHAR columns. This does not affect how you use the columns in any way; in MySQL, VARCHAR is just a different way to store characters. MySQL performs this conversion because it saves space and makes table operations faster.

However, that’s not entirely accurate. Because according to

Download the video at:

http://technocation.org/videos/BostonMySQLJanUserGroupBrianAkerLg.wmv
- 520 kbps, 320 x 240, 354M. Small size, low quality, but you can still see the slides and hear everything.

http://technocation.org/videos/BostonMySQLJanUserGroupBrianAkerSm.wmv - 45 kbps, 320 x 120, 29M. Small size, low quality, but you can still see the slides and hear everything.

Technocation, Inc. received a donation of a Sony Handycam DCR SR80 (http://tinyurl.com/yvyfam ), extra-long battery, microphone (proprietary Sony that goes with the camera).

In a short sentence: I am impressed. The sound quality (on the large version) is