Mike Hillyer.com

My Normalization session seemed to go well enough.

Video is online at: http://www.mikehillyer.com/video/oscon_2008.html.

Zak Greant is running a free event this year parallell to OSCON that should be very valuable to anyone who is trying to get a start in Open Source or looking to start an Open Source project:

The project is called FOSSCoach and it is the successor to OSCAMP (the
Barcamp-like event that OSCON hosted for the last few years.)

FOSSCoach is focused on teaching people the skills that they need to
participate in (or start) distributed online projects - like PHP or
Wikipedia. Participation is free and people don’t need to be signed up
for OSCON to get in.

The event is meant to provide a way for OSCON to better serve the broad
FOSS community in Portland and surrounding regions.

Details on the event live at in a wiki at:

http://fosscoach.wikia.com

Kudos to Zak for organizing yet another valuable offering and I hope to see you there!

Since it took me over an hour to gather this together, I thought I would relay it here. To install the latest version of Darwin Streaming Server (6.0.3 at time of writing), you need to do the following:

1. Download a patched source RPM from Sverker Abrahamsson at http://www.abrahamsson.com/DarwinStreamingServer-6.0.3-2.src.rpm
2. Install the source RPM with rpm -i DarwinStreamingServer-6.0.3-2.src.rpm
3. Switch to the spec file with cd /usr/src/redhat/SPECS/
4. Build the spec file with rpmbuild -bb DarwinStreamingServer.spec
5. Switch to the built RPMs with cd /usr/local/src/redhat/RPMS/x86_64
6. Install the RPMs with rpm -U  DarwinStreamingServer* - at one point in the install things will be hung. Open another session and kill -9 the DarwinStreamingServer process.
7. Point a browser at the server on port 1220 and away you go! The default user is ‘administrator’ and the default password is ‘password’.

Try the admin guide PDF as a starting point: http://developer.apple.com/opensource/server/streaming/qtss_admin_guide.pdf

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