Mike Hillyer.com

When I was on the MySQL Documentation team one thing started to become apparent to me: the MySQL Reference Manual was exactly what it claimed to be: a document better suited to referencing than teaching new users. I make very good use of it because I know MySQL, I know what it can do, and all I want is the right syntax for what I am trying to use it for.

I saw a need for a User Guide, something well suited to new users who were not experienced with MySQL and potentially with DBMSes in general. As a side project I started working on a chapter of what could eventually be a MySQL produced user guide, lacking the depth of the reference manual but more suitable for newer users. This first chapter was on Indexing and while I think it showed promise, I left MySQL AB shortly after completing it.

Fast forward to today, where I see a blog post by Colin Charles announcing a wiki-based community-powered User Guide project and lo and behold, the first completed content is a slightly retooled version of my indexing chapter.

I’m glad to see this getting off the ground, especially as a community project. Now hopefully some people will rally together and pick up the torch, helping to create something that is useful to the new user community.

(I do find it funny that the first line in the indexing chapter is “Note: Experienced users may wish to skip this section.” I’d think the whole point is that experienced users will probably want to skip the entire document.)

Wednesday night I attended a reception hosted by Sun and managed to win myself a Sun Fire X2100 M2 server!

Swag-wise, the conference was an improvement over last year, as judged by Laura Thomson’s T-Shirt index, with around a dozen shirts to be had.

Just a quick note that I have posted the video from my session at this year’s MySQL Conference at:

http://www.mikehillyer.com/video/HowToBeNormal.html 

Speaker: Mike Zinner

Developed a design tool called DBDesigner4 as his own schema design tool, had 1.5 million downloads. Tool was built on Delphi, became outdated. Project brought him to the attention of MySQL AB, resulted in a job offer.

After signing on he began work on basic MySQL GUI tools such as MySQL Administrator, MySQL Query Browser, Migration Toolkit, and MySQL Windows installer and configuration wizard.

Project existed from DBDesigner, then in 2005 a preview version of Workbench was released. Workbench was re-architected in 2007-2008 and was released in April 2008.

Workbench is a fully featured schema design tool for MySQL developers. Two editions: one open source and one commercial. Commercial version has additional functionality.

Intended for developers, architects, DBAs.

Provides schema design features, schema documentation features, database sync, schema validation, internal scripting and plugin architecture.

Design Features: Point and click schema design, multiple-diagram support, foreign key drawing and object design tools.

Automatic schema documentation is available.

Forward and reverse engineering from SQL scripts and live servers.

Schema validation will display warnings and suggest optimizations.

Upcoming talk tomorrow where Mike shows how to write scripts and develop plugins.

Mike then begins a live demo. Tabbed interface makes it easy to move from schema to schema. Once a model is loaded we see diagrams and members of the schema including tables, views, stored procedures, stored procedure groups. Also possible to navigate schema privs, SQL scripts and model notes.

Roles can be built and managed within Workbench, it will map them into the database when you export since MySQL itself has no role support.

Design interface looks very sharp, allowing for multiple layers. Hover the mouse over an FK relationship and it highlights the columns that belong to the relationship.

Mike showed how to create a new diagram. Drag and drop can be used to introduce existing tables. When tables are added the foreign key relationships are automatically drawn in. New tables can be added using drawing tools.

You draw a table, name it, choose a storage engine and then start listing columns. You name columns, choose the datatype from a list, choose whether it is nullable or auto-increment, add key information and the schema is automatically updated.

Composite keys can be defined by naming the key and checking the columns that are members and setting their order. Partitioning is configurable through the workbench.

Designer also allows for things like colour of the table in the schema design, useful for logically grouping tables.

Relationship design tools allow for drag and drop relationshop buildings, with automatic addition of columns to the child tables.

You can create user defined data types, allowing you to easily change all columns with your user-defined type to a new type without extensive edits.

Views can be edited, queries can be edited with syntax highlighting. A visual query designer is coming in a later version. Syntax validation is included in Workbench for queries and stored procedures.

Privilege management is included in Workbench, roles are managed in the tool but not available in MySQL so roles are mapped. Privileges can be assigned to roles and users given roles, workbench will then create SQL scripts that implement the user’s privileges.

Import/Export can happen to live servers or to SQL scripts. Export to PNG and PDF is also available. Printing support is available, large format is supported.

Auto-layout will arrange tables automatically so that relationship lines do not overlap.

Auto-documentation looks very nice, producing a useful report automatically from the design, including any notes you added to your objects. Uses Google libraries.

Plugins and scripts can be user created. Workbench includes a scripting IDE.

Community Edition is not crippled, it just does not include certain plugins. GUI team needed to find compelling reasons to purchase the standard edition for $99. You get a one year update subscription for the price. Workbench web site has a feature comparison matrix.

Coming up is cross-platform support, support for Linux, OSX and Solaris. Add a fulle SQL IDE that replaces query browser. Upcoming is other DB Vendor support. Eventually Workbench may replace the Migration Toolkit.

See http://dev.mysql.com/workbench or wb.mysql.com. Bug reports, forum posts and blog entries are appreciated.

Q&A

Yes, currently this is only for Windows.

Linux version is in progress, thanks to architecture 80% of code is re-used. Q4 target for GA.

Good session, product is looking great.

I’ll be blogging and posting photos from the 2008 MySQL Conference.

My photos can be seen in a Flickr set at  http://www.flickr.com/photos/mhillyer/sets/72157604556435611/

Watch this space for session notes.

I’ll be speaking at OSCON 2008 in Portland, delivering my Normalization Session. More details as I get them.

A blog entry at Word To The Wise talks about a new exploit discovered in the IronPort A60 series:

IronPort Open Relay Vulnerability

Systems Affected
IronPort A60 running software version 2.5.4-005. According to IronPort, later devices and software versions using the same filtering mechanisms are vulnerable.

Overview
In recent weeks, one or more rogue spammers have been using misconfigured IronPort A60s as open relays to send unsolicited emails for AOL users via open relay. It is important for IronPort device administrators to review their configuration to shore up any vulnerability to this web server exploit.

Diagnosis
A seemingly minor configuration mistake made years ago internally has been exploited over the last several weeks to send out massive amounts of unsolicited email to AOL users. The spam mail originated from an outside zombie server, apparently infected with remote mailing viruses (such as BackDoor.Servu.76) according to the IT contact at IP 66.139.77.16. <ESP> has a filter specifically designed to deliver email over IP ranges set for AOL only. However, it was listed before a filter designed to log and discard bounced emails coming in through the Internet-facing of the IronPort appliance.

Impact
We have received 6,500 customer complaints so far through the AOL feedback loop. As the IronPort devices are black boxes, we are unable to determine how many unsolicited emails were delivered across them. It is difficult to ascertain whether or not the rogue spammer(s) knew only AOL addresses were delivered using this exploit. It is important to note that only AOL addresses were delivered in our specific case due to the order of the filters.

Solution
The solution was simple: move the filter designed to log and drop bounce messages coming in from the Internet to the top of the filter list so it will run first, as other filters may direct the IronPort device to deliver the emails through this vulnerability.

Authors: Jake Lanza, Baigh Auvigne, Daniel Fox

Word to the Wise rightly points out that this shows a great side-benefit of FBL emails: when watched closely they can indicate that you are operating as an open relay, allowing third parties to send mail through your server. If not corrected quickly this can lead to blocking and blacklisting.

FBL, or FeedBack Loop emails are sent by certain ISPs to registered senders allowing the senders to know when users click the ‘This is Spam’ button, allowing senders to remove those users from their mailing lists and also gain insight into how their subscriber base responds to their mailings.

While FBL emails provide no improvement to deliverability as far as reputation and filtering are concerned, they are invaluable in helping shape future mailings and maintaining list hygiene.

The following ISPs provide feedback loops, any legitimate sender would want to sign up for as many as possible:

• Hotmail
• AOL
• Yahoo
• Roadrunner
• Outblaze
• Excite
• USA.net

There’s a good article on FBLs at http://www.clickz.com/showPage.html?page=3623337

I’ve had one session accepted at the 2008 MySQL Conference & Expo (which is a good thing since I only submitted one session for nomination).

This year’s session is titled How to be Normal, a Guide for Developers. I decided to return to my old Normalization talk, but lean more towards the practical than the theoretical this year and work on common scenarios faced by developers rather than walk a person through the normal forms like I have previously done.

Here’s the abstract I’ll be sending in to replace the ugly one I submitted with:

At some point in every software project involving a database it becomes necessary for the developers who created (or inherited) the project to step back and take a look at their database. Many projects have a database schema that has evolved over time, with columns added here and tables added there, increasing complexity and often adding redundancy. Super-tables grow with more and more columns making ALTERs slow and backups difficult.

In this session MySQL author and speaker Mike Hillyer will guide the audience through the principles of database normalization and review some common normalization scenarios encountered by many application developers.

Topics include:

• What is normalization and what are its benefits?
• What are the normal forms?
  • First Normal Form
  • Second Normal Form
  • Third Normal Form
  • And so on…
• Normalization Scenarios
• Relationships and Joins
• How Much is Too Much?

This session is intended for a beginner to intermediate audience and is applicable to developers and administrators who deal with database design.

The Normalization talks are always well received and cover important principles, hopefully if you’re at the conference you can come in and participate.

I’ll be posting all my new MySQL related content here in an effort to create a single point for all my technical and non-personal content.

The Ferris Research blog has an entry on ISPs and whether spam blocking is at odds with the common carrier status that seems to protect them from certain liabilities:

This debate is happening again. Thanks to the good work done by MAAWG and others, ISPs are being encouraged to set up outbound spam filtering, to prevent virus-infected PCs sending spam from their networks and to encourage users to clean their infected machines by use of a walled garden. Naturally, some are expressing concern that such discrimination would count as another chink in their common carrier armor.

It’s time for the FCC and similar regulators in other countries to step up and make it clear that such genuinely useful — some would say essential — discrimination would not affect an ISP’s common carrier status.

I personally don’t think that ISPs should risk their common carrier status for filtering mail, but not because spam filtering is useful, instead because such filtering does not occur at the network level.

Let’s say a spam message enters the network of my ISP: the ISP does not block the packets containing the spam message at the edge of the network, instead the spam message is delivered without discrimination to the ISP’s mail server. The ISP’s mail server then filters the message and if the message is not spam the ISP will later deliver the message to my mail client across their network, again without discrimination.

It is the ISP’s network that acts as a carrier, not their mail servers. Even on the outbound side, it would be ISPs filtering mail entering their mail servers from inside the network for external delivery. In every case the network itself would just be carrying packets containing the messages to the appropriate mail servers.