What constitutes a good error message to the user?

Today, will go down in my professional history as quite possibly the lowest I would ever think of a software developer. I’ve carefully avoided the term “fellow coder”, speaking of a IT industry sticking by fellow IT people, but not today.

I presently support an existing production system, some 1000+ users that’s been in place around 3 years in which I’ve had no prior involvement until recently. Integration with other external parties within the system have provided an esclation in errors being reported in this external communication, and lack of adequate feedback to the user is another topic. Email is the means of reporting administratively of user errors, again another topic of issue. Within these emails, which are almost impossible to manage due to the limited internal GUI only toolset and lack of access to actual email account files to automate scripting (yet another topic? Do you see a trend here), is some relevent information regarding the transaction, and then most importantly the error message. The thing I need to investigate the problem.

The line reads (removing some stuff):


Error Code: 234567892

Ok, well a little cryptic, but surely you can work out from the external system what this means. Investigation of some more errors, in the mail GUI product, yet another series of open windows (you can’t view messages like a regular mail client with a summary list and a detail panel), provides for a trend in the error messages:


Error Code: 1235492567
Error Code: -434783134
Error Code: 34345199

The trend being there is none. Of course today by mid morning the email error count is into the hundreds, and I’m none the wiser. Well time to closely investigate the code management (as I’ve already contacted the external party, and asked if I can provide some error codes to receive greater information).

The following constitutes the two lines of code taken in the determination of the error messages I’ve shown so far. Note, this code takes the external system response, and then “attempts to determine usefull error content for presentation back to the user”.


errorNo = new Random().nextInt();
error = “Error Code: ” + errorNo;

Now while everybody laughed out loud, including fellow developers, DBA, IT manager, the Business Owners and Users (which can’t read code but could understand the first of these two lines I highlighted), and yes it really was funny in context with a bigger picture, but really it wasn’t funny at all. Some things make my blood boil, and this was one of them. With all the time lost between multitudes of people, users, call centre etc, I’d never felt a stronger conviction to hunt down the developer that wrote this.

The end of the story is after even trolling old CVS repository entries I was unable to piece sufficient information to determine the author. Most likely done pre version control, and then that trail leads to only a few names I’ve heard mentioned before.

I’d like to see somebody top that one!

MySQL Ideas

Seems I have over time, thought of many ideas, jotted some notes on some, and even done some work, but everybody knows that “home projects” can take a long time.

Here are a few that have resurfaced over the past month, and I doubt I’ll ever get to them, or perhaps some other enterprising person has already done a similar thing. Of course most are for my own personal/professional gratification, but input from others of “great idea, when do we see it” could sway my interests.

INFORMATION_SCHEMA for MySQL Version 4

Why?

Well, quering the INFORMATION_SCHEMA is very cool, and long overdue for information gathering including statistics, schema definitions, schema version comparision tools etc. Of course there are concerns regarding the performance of using the INFORMATION_SCHEMA, and any design should significantly consider this limitation. The INFORMATION_SCHEMA is not physically represented as a normal MySQL database with tables and real data files, and then can be optimised appropiately, queries reference various sources of information and return the results at query time.

How would I achieve this? Here is an approach. The biggest issue would be what programming technology and non-Linux support.

  • Generate a physical INFORMATION_SCHEMA create SQL script from the most current database version. This would allow you to create a real database with real tables.
  • Population. Well at least some tables will be empty, but some work would be required to the population of other information. A first pass with some easy shell scripting and SHOW commands would easily populate information for SCHEMATA (SHOW DATABASES), TABLES (SHOW TABLES), COLUMNS (DESC [table]). Some are covered by the mysql schema. You get the picture. Of course some information will be more more difficult to populate easily.
  • Syncing. Populating is one thing, but if the information is not current then it becomes useless. Depending on your organisations management, in a controlled environment, simply re-running the population process when schema information is changed, or run nightly may work. A more brut force approach may be to look at filesystem timestamps of the database table definitions and trigger when changes occur, but I suspect this would a poor approach.

Storage Engines in use

I’ve often wondered, do people use X storage engine.

I would love to see plastered on the MySQL Home Page stats of 99,999,999 Downloads, 99,999,999 Installations reporting in, and then some breakdowns of database versions, storage engine usage, even demographics. This type of statistical information can be great, but can also be dangerous to competitors, or can be too detailed to become useful. Consider the objective it a general census.

I recently was introduced to Fotolog, and in the top left corner is Members, Photos, and Photos today. Of course now they have volume the numbers are impressive. You blink, do a refresh and they have changed.

Having a more general information goal approach, rather then detailed would be my guidelines. Here are some points.

  • Each MySQL installation is registered providing it a unique id. This is used for all collection and communication, and there is never any real reference back to organisation/name etc. How this works correctly is a little difficult, but would need to involve some checksuming of information like hostname,os,mysql version,maybe ip.
  • A script runs to generate basic statisical information, including unique installation id, present GMT date/time, MySQL version, master/slave, installation date, number of databases, breakdown of storage engines used per database/installation, total number of tables per database/installation.
  • The Information needs to be recorded, so it can be transmitted, and also used for historical comparision, best option would a flatfile on the filesystem.
  • Having an automated approach to then say email this information to a known server. There would need to be some means to have some authentication, and feedback to confirm success. Using email is not an ideal approach, but is a more readily available medium. Another easy means would be a webservices approach, but would require a more direct internet connection, while mail could overcome this means.
  • More advanced ideas would be to record information in and XML format, have a XML storage engine, and enable reports to be run against this collated information, so the System Adminstrator has at there finger tips historical details that are gathered. The benefit of an XML approach is this can be more easily collated with clever approaches with XSLT for example. The fallback would could be a CSV file, but the information being sent, may well need to be more structured for various reasons.
  • Of course if it was limited to Version 5 up, the benefits of stored procedures, events and UDF’s a lot of this could be implemented in MySQL specific technologies, but what about the bulk of installations pre Version 5, you would definitely what to include these.
  • My concerns would definitely be SPAM abuse, or false figures, so these points alone may nullify the process even being worth it.

This is just a broad stroke overview, I’ve got more detailed analysis of pro’s, con’s, example file layouts, and a means of collating the information and providing dynamic reporting on the server.

Of course there are a few ideas I have implemented, and I’m still passionate about and working on in that “spare time” we all never have.

ShowCase MySQL

A showcase of MySQL (via existing popular Open Source apps). I’ve done this via a VMWare Appliance called UltimateLAMP.

I did this for two reasons, first of course I wanted to easily show applications that could be used without any technical requirements (indeed the hardest part is installing the free VMware player), and second, if there was more penetration of possible MySQL options in my present home city of Brisbane, it may provide some MySQL work opportunities. Of course, this an application approach to demonstrating MySQL, rather then a MySQL features approach, and is dependent then on the community and these applications may be less then ideal in a large scale deployment or may not be of a quality to be placed with MySQL, but it’s one way to get leverage.

I would like some way to poll usage of MySQL within organsiations my home town, but it’s a very difficult approach, and I’m sure some organisations would not even know they are using MySQL. The process may also provide a means of providing a service, say quarterly updates of software (MySQL and open source products), customisations, but I think it would be a difficult sell. Having an army of people applying these ideals across a number of cities may provide a more marketable approach.

ACID Performance Benchmark

Leveraging the Apache project JMeter, which has all the infrastructure for handling theads, reporting, graphing, and volume load testing, I’ve written to enhancements for JMeter JDBC Sampler, the first was CALL support, enabling calls to stored procedures. The second is TRANSACTION support enabling a number of SQL statements to be execute in a true transaction, all pass or all fail.

These allow me to provide an application specific performance approach. Now MySQL has various means of testing, the mysql-test that comes with installations (I’ve got more on this for a later time), as well as mysqlslap (again, some more points on this at a later time). Both of these tools have benefits, but I wanted a more application specific approach. Using JMeter provides an overhead, but it provides a more realistic application approach, my goal is not to get the maximum thoughtput possible, being as close to the screws, but to provide a more realistic approach.

Of course, my objective is to use application specific SQL, the SQL you run, this would need to be gathered via various means. Information gathering including what types of transactions, the interactions, the volumes of use would be needed to create realistic approaches. The benefits of JMeter for example, is I can simulate various transactions, enable them to interact, enable them to have implied delays to simulate user use. These are some of the advantages over the current MySQL performance approaches.

Providing this level of simulation however is only half the requirement. This provides for a benchmark for performance, but what about validation. Sure the system can be used with 100 threads with X Insert Orders, and Y Select Orders, and Z Invoice Prints etc per thread, but what if the there were issues and errors.

Data Quality is a key interest and this level of benchmarking also has to have the extent of validating the results, and taking appropiate actions for failures. I’m planning also that this can be used by enabling Referential Integrity (via an appropiate storage engine) on an existing application and then confirming transactional state of queries within applications that are not presently transaction safe.

Presently, I using this approach to assist in the testing of the MySQL Community Storage Engine PBXT. I’m hoping in conjunction with the Sakila Sample Database and various data sets, that I can also publish comparision results with other Storage Engines.

Schema Designer Analyser

My background is database modelling and large systems design, and I’m writing a A MySQL Guide to Database Modelling, Design and Support (still under development). This is an online and very simple approach for open source development to improve it’s image in database modelling. Keeping things simple will help all those non-professionals out there.

To back up this article, which has a checklist for organisations to follow and then incoporate into standards, is a tool to do the analysis that the checklists requires.

I had hoped to initial develop this using the GRT portion of the MySQL Workbench, but I’ve decided to make that a secondary project for now, focusing on a tehnology of greater understanding, possibly Java. Of course the biggest issues is accurate gathering of schema information, so you can see why a INFORMATION_SCHEMA for MySQL version 4 would come in handy.

Conclusion

I’ve got a lot more ideas, and discussions with other MySQL peers rekind ideas I’ve thought of previously. I really should get to putting them all out there to see how many people think I’m crazy, or to find other crazy people to contribute, or even fund! One can dream.

How I work

My work life is really fragmented at present, so I’ve decided a split approach in answer to Dave Rosenberg’s How I Work–what I have learned so far .

What is your role?
Support Developer providing sole support an internal client web based system (Java, Oracle) with 1000+ users producing >$1m daily revenue. Independent Database Consultant. Specialising in Database Modelling, Large Systems Design and Web Development Technologies. Strong proponent of Agile Development Methodologies (specifically Extreme Programming – XP).
What is your computer setup?
Dell Optiplex GX280 (P4, 1GB, 17″ CRT) running Redhat FC4.
(When I started only 2 months ago I got lumped with a similar speced machine running Windows XP, and upteen windows apps (like 4 different versions of TOAD for example) . This OS was contry to my understanding provided in the interview process.
Primary – Dell Inspiron 5150 (P4/3.2GHz, 1GB, 120GB, 15″ + 21″ CRT)
Secondary – Generic (P3/600MHz, 1GB, lots of HDD ~500GB, 17″ CRT)
Both with very impressive Saitek backlit keyboards (one red, one blue) Great at night. See image at bottom.
What desktop software applications do you use daily?
RedHat Fedora Core 4
Eclipse 3.1, FireFox 1.5, Thunderbird 1.5, WebSphere Application Server 5.1, J2DK 1.4.2, Oracle SQL Developer, Open Office 2, XMMS and SSH client (which I use most)
Due to legacy internal systems and support I also must run under Wine (Internet Exploder), and First Class (email client).
Not to stop there, I also must run under Citrix ICA Client apps (FocalPoint, Heat Call Logging, and Microsoft Word for internal forms that won’t work under OO2.) And before somebody suggests why not try VMWare or other clients, I have tried, but software like Focalpoint can’t install?
CentOS 4.3
FireFox 1.5, ThunderBird 1.5, Gaim, SSH, Skype, Open Office 2.
Maybe not all of these every day, but some combination of each day –> MySQL 4.1, MySQL 5.0, MySQL 5.1, MySQL Workbench, Eclipse 3.1, J2DK 1.4.2, J2DK 5.0, Apache Tomcat 5.0.28, Apache Httpd 2.0.53, JMeter.

Presently also configuring a new laptop drive running Ubuntu Dapper 6.06 RC. For the Record, Beta 2, Flight 6 and Flight 7 all failed.
What websites do you visit every day?
Internal Wiki (all day)
At Lunch Google Sci-Tech News
PlanetMySQL
Google Sci-Tech News
PlanetMySQL (if I haven’t already got to them).
What mobile device or cell phone do you use?
N/A NEC e606 3G phone (which I’ve had for probably 3 years now) operating on a 3G network with video calls, again for 3 years.
Do you use IM?
No. All access is blocked other then an internal Jabber server, that I use rarely, never for communication, just a cut/paste of command or syntax. Speaking of blocked, SSH access to my production servers is blocked, and even reading news like Skypes Call Out is blocked by WebSense. Extensively, however due to current employement policy, I’m very hamstrung unless before/after hours.
I use Gaim as I have AIM, MSN and Google Talk accounts, and Skype. My preference was always AIM, but as clients come and go, I’ve had to accumulate more accounts.
Do you use a VoIP phone?
No. Not at present, however for many years I worked with US clients and used Packet 8. Still have the hard phone somewhere.
I’ve also used Skype talk for one or one or conference calls. Of late in Australia to New Zealand and Singapore. Indeed, the quality to Singapore has been excellent, when living in the US, calls to Singapore on Skype were clearer then my Packet8 phone to US numbers.
Do you have a personal organization/time management theory?
Current contract employees a number of disjointed methods, which in observation just shows so much inefficency, it’s worth documenting just to highlight what not to do. We have daily team meetings (10 mins), each listing your top 2 daily tasks. Weekly we have to also submit weekly goals. Weekly combined meetings with another team where we again give weekly top 2 tasks. We use two seperate systems (with manual double entry) for work identification, one for call centre logging issues, and Bugzilla for software bugs, and enhancements. We also use XPlanner (again duplicating a lot of tasks) for time management.
With all this rigid structure, I am daily given either other work to do, or investigate, and in over 3 months, I would rarely end a week anywhere near where it was so described at the start of the week.
With all the technology possible, I actually do not have any electronic management gadget, never had. I use a combination of notebook, plain paper (usually for daily notes etc, which I discard regularly), a diary, and normally a lot of emails which I normally send to/from home.
Given that email is used so much, I basically use Draft Emails for any electronic notes.

Anything else?
Perhaps there is merit in How I work now, and How I’d like to work now.

Saitek Keyboard

A better VNC

I’ve been using VNCViewer from RealVNC under Linux to remote connect to an older machine running windows. Two reasons, I don’t need yet another screen on my desk, and I need windows to adequately test and use the MySQL GUI products, in particular MySQL Workbench.

I never realised there was a better way, particularly over a local network, a command called rdesktop which is capable of natively speaking Remote Desktop Protocol (RDP).

$ su -
$ yum install rdesktop

This did only give me version 1.3.1. The current version 1.4.1 available from the rdesktop Website does provide greater commands including the syntax I use.

su -
cd /src
wget http://optusnet.dl.sourceforge.net/sourceforge/rdesktop/rdesktop-1.4.1.tar.gz
tar xvfz rdesktop-1.4.1.tar.gz
cd rdesktop-1.4.1
./configure
make
make install

Example Usage

$ rdesktop -u <username> -p <password> -K -N -z -a 16 -x b -P 

Some additional common options include -d <domain> and -f for fullscreen.

Comand Syntax

$ rdesktop
rdesktop: A Remote Desktop Protocol client.
Version 1.4.1. Copyright (C) 1999-2005 Matt Chapman.
See http://www.rdesktop.org/ for more information.

Usage: rdesktop [options] server[:port]
   -u: user name
   -d: domain
   -s: shell
   -c: working directory
   -p: password (- to prompt)
   -n: client hostname
   -k: keyboard layout on server (en-us, de, sv, etc.)
   -g: desktop geometry (WxH)
   -f: full-screen mode
   -b: force bitmap updates
   -L: local codepage
   -B: use BackingStore of X-server (if available)
   -e: disable encryption (French TS)
   -E: disable encryption from client to server
   -m: do not send motion events
   -C: use private colour map
   -D: hide window manager decorations
   -K: keep window manager key bindings
   -S: caption button size (single application mode)
   -T: window title
   -N: enable numlock syncronization
   -X: embed into another window with a given id.
   -a: connection colour depth
   -z: enable rdp compression
   -x: RDP5 experience (m[odem 28.8], b[roadband], l[an] or hex nr.)
   -P: use persistent bitmap caching
   -r: enable specified device redirection (this flag can be repeated)
         '-r comport:COM1=/dev/ttyS0': enable serial redirection of /dev/ttyS0 to COM1
             or      COM1=/dev/ttyS0,COM2=/dev/ttyS1
         '-r disk:floppy=/mnt/floppy': enable redirection of /mnt/floppy to 'floppy' share
             or   'floppy=/mnt/floppy,cdrom=/mnt/cdrom'
         '-r clientname=': Set the client name displayed
             for redirected disks
         '-r lptport:LPT1=/dev/lp0': enable parallel redirection of /dev/lp0 to LPT1
             or      LPT1=/dev/lp0,LPT2=/dev/lp1
         '-r printer:mydeskjet': enable printer redirection
             or      mydeskjet="HP LaserJet IIIP" to enter server driver as well
         '-r sound:[local|off|remote]': enable sound redirection
                     remote would leave sound on server
   -0: attach to console
   -4: use RDP version 4
   -5: use RDP version 5 (default)

I haven’t reviewed the security implications but considering I’m only running in my own internal network, it’s not a major priority.

References

Improving Open Source Databases – WordPress

As part of both my UltimateLAMP side project, and my greater involvement with MySQL Workbench, I’ve been wanting to review and document the database schemas of Open Source products.

Indeed, as part of discussions with Mike Zinner of MySQL AB at the recent MySQL Users Conference, I suggested an idea used in the Java World, for example by Clover and Agitar, where to promote the usefullness of their respective tools (in this case code coverage), they provide results against Open Source Products. For an example check out Clover Examples.

With MySQL Workbench, to get some greater exposure of the use of graphical Data Modelling tool, I mentioned the idea of providing a respository of schemas, may help in exposure, usage and feedback of MySQL Workbench. Indeed my work is towards this being added to MySQL Forge, however I’ve found that at my first indepth work, using WordPress 2.0.2 has been a less then successful experience.

The schema would rate a 2 out of 5 on my scale of optimal database schemas. Here are some of my initial findings.

  • Inconsistent naming standards
    • ID uppercase for primary keys in some tables, but not in others.
    • different standards for primary keys, some id, some [table]_id
    • Inconsistent identification of foreign keys (e.g. post_parent). All keys should have _id.
    • Inconsistent datatypes for optional foreign keys (e.g. default of 0 rather then NULL)
    • Inconsistent column naming standards, some tables prefix columns with table name or table like name, (e.g. post_), but not all columns. Some tables has multiple prefix values (e.g. cat_, category_)
  • Poor usage of datatypes. Examples include BIGINT(20) for primary keys, TEXT for post title, INT(10) for columns which as values a TINYINT can support.
  • Inconsistent use of enums (e.g. Y/N for some values, and yes/no for others)
  • No Referential Integrity support (ie. Foreign Keys), limiting the benefits of a modelling tool showing table relationships
  • Different rules for primary keys (most tables have a suggorate key, but not all)

I am actually working on A MySQL Guide to Database Modelling, Design and Support, which covers a lot of these points and more. I’m some way from completing this document, but this initial research of the WordPress product highlights it’s importance to the open source community.

Of course we can just make noise about what we don’t like, or we can do something about it. I’m submitting my ideas, suggestions and code changes to the project. What is done about it is outside of my control, but I’ve at least given my input to this Open Source Product.

WordPress 2.0.2 Schema SQL (taken from a mysqldump after installation)

Here is a graphical representation of the Data Model from MySQL Workbench.
NOTE: My MySQL Workbench will not enable me to do a Export Image, so I’ll need to add this at a later time

Introducing Referential Integrity

Unfortunately this is no automated means of creating appropiate Referential Integrity for a schema. With more strict naming standards, it would be possible to reconstuct this information generally.

The approach to create these new rules were performed manually, but a tool could be developed to provide approximations for a number of these.

# Implement Foreign Keys. Changes include:
#  - Appropiate Storage Engine
#  - ensure column is of same definition as matching primary key (e.g. datatype + unsigned)
#  - create index for foreign key
#  - ensure optional keys use NULL

ALTER TABLE wp_categories ENGINE=InnoDB;
ALTER TABLE wp_comments ENGINE=InnoDB;
ALTER TABLE wp_linkcategories ENGINE=InnoDB;
ALTER TABLE wp_links ENGINE=InnoDB;
ALTER TABLE wp_options ENGINE=InnoDB;
ALTER TABLE wp_post2cat ENGINE=InnoDB;
ALTER TABLE wp_postmeta ENGINE=InnoDB;
ALTER TABLE wp_posts ENGINE=InnoDB;
ALTER TABLE wp_usermeta ENGINE=InnoDB;
ALTER TABLE wp_users ENGINE=InnoDB;

ALTER TABLE wp_usermeta MODIFY user_id  bigint(20) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE wp_usermeta ADD FOREIGN KEY usermeta_users_fk (user_id) REFERENCES wp_users(ID) ON DELETE CASCADE;

ALTER TABLE wp_posts MODIFY post_author BIGINT(20) UNSIGNED NOT NULL;
CREATE INDEX post_author ON wp_posts(post_author);
ALTER TABLE wp_posts ADD FOREIGN KEY posts_users_fk (post_author) REFERENCES wp_users(ID) ON DELETE CASCADE;

ALTER TABLE wp_posts MODIFY post_parent BIGINT(20) UNSIGNED NULL DEFAULT NULL;
UPDATE wp_posts SET post_parent = NULL WHERE post_parent = 0;
CREATE INDEX post_parent ON wp_posts(post_parent);
ALTER TABLE wp_posts ADD FOREIGN KEY posts_posts_fk (post_parent) REFERENCES wp_posts(ID);

ALTER TABLE wp_postmeta MODIFY post_id  bigint(20) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE wp_postmeta ADD FOREIGN KEY postmeta_posts_fk (post_id) REFERENCES wp_posts(ID) ON DELETE CASCADE;

ALTER TABLE wp_categories MODIFY cat_ID BIGINT(20) UNSIGNED NULL DEFAULT NULL AUTO_INCREMENT;
ALTER TABLE wp_categories MODIFY category_parent BIGINT(20) UNSIGNED NULL DEFAULT NULL;
UPDATE wp_categories SET category_parent = NULL WHERE category_parent = 0;
ALTER TABLE wp_categories ADD FOREIGN KEY categories_categories_fk (category_parent) REFERENCES wp_categories(cat_ID);

ALTER TABLE wp_post2cat MODIFY rel_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_post2cat MODIFY post_id BIGINT(20) UNSIGNED NOT NULL;
ALTER TABLE wp_post2cat MODIFY category_id BIGINT(20) UNSIGNED NOT NULL;
ALTER TABLE wp_post2cat ADD FOREIGN KEY post2cat_posts_fk (post_id) REFERENCES wp_posts(ID) ON DELETE CASCADE;
ALTER TABLE wp_post2cat ADD FOREIGN KEY post2cat_categories_fk (category_id) REFERENCES wp_categories(cat_ID) ON DELETE CASCADE;
ALTER TABLE wp_comments MODIFY user_id BIGINT(20) UNSIGNED NULL DEFAULT NULL;
UPDATE wp_comments SET user_id = NULL WHERE user_id = 0;
CREATE INDEX user_id ON wp_comments(user_id);
ALTER TABLE wp_comments ADD FOREIGN KEY comments_users_fk (user_id) REFERENCES wp_users(id) ON DELETE CASCADE;
ALTER TABLE wp_comments MODIFY comment_post_ID BIGINT(20) UNSIGNED NULL DEFAULT NULL;
ALTER TABLE wp_comments ADD FOREIGN KEY comments_posts_fk (comment_post_ID) REFERENCES wp_posts(ID) ON DELETE CASCADE;

ALTER TABLE wp_comments MODIFY comment_parent BIGINT(20) UNSIGNED NULL DEFAULT NULL;
UPDATE wp_comments SET comment_parent = NULL WHERE comment_parent = 0;
CREATE INDEX comment_parent ON wp_comments(comment_parent);
ALTER TABLE wp_comments ADD FOREIGN KEY comments_comments_fk (comment_parent) REFERENCES wp_comments(comment_id);

ALTER TABLE wp_linkcategories MODIFY cat_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_links MODIFY link_category BIGINT(20) UNSIGNED NOT NULL;
ALTER TABLE wp_links ADD FOREIGN KEY links_category_fk (link_category) REFERENCES wp_linkcategories(cat_id);

While there are no column name changes, by default the WordPress code should operate with this revised schema. Issues would include a poor delete strategy that still voilates the liberal constraints now enforced. Special handling of 0 for optional columns when the value is now NULL may also be a problem.

Revised Data Model in MySQL Workbench.
NOTE: My MySQL Workbench will not enable me to do a Export Image, so I’ll need to add this at a later time

Introducing improved Standards

Of course, I’d like to see the schema improve, here are my first draft ideas for improvements of the schema.

# Naming Improvements ===================================================================================
# Rename to lowercase id
ALTER TABLE wp_users CHANGE ID id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_posts CHANGE ID id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_categories CHANGE cat_ID cat_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_comments CHANGE comment_ID comment_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_comments CHANGE comment_post_ID comment_post_id BIGINT(20) UNSIGNED NOT NULL;

# Include _id for all Foreign Keys
ALTER TABLE wp_posts CHANGE post_author post_author_id BIGINT(20) UNSIGNED NOT NULL;
ALTER TABLE wp_posts CHANGE post_parent post_parent_id BIGINT(20) UNSIGNED NOT NULL;
ALTER TABLE wp_categories CHANGE category_parent category_parent_id BIGINT(20) UNSIGNED NOT NULL;
ALTER TABLE wp_comments CHANGE comment_parent comment_parent_id BIGINT(20) UNSIGNED NULL DEFAULT NULL;
ALTER TABLE wp_links CHANGE link_category link_category_id BIGINT(20) UNSIGNED NULL DEFAULT NULL;

# Primary Key Name Standardisation
ALTER TABLE wp_posts CHANGE id post_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_users CHANGE id user_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;

# Foreign Key Standardisation
ALTER TABLE wp_categories MODIFY category_parent_id parent_id BIGINT(20) UNSIGNED NOT NULL;
ALTER TABLE wp_comments CHANGE comment_parent_id parent_id BIGINT(20) UNSIGNED NULL DEFAULT NULL;

# Other Column Standaisations
ALTER TABLE wp_categories MODIFY cat_name category_name VARCHAR(55) NOT NULL;

# Column width improvements
ALTER TABLE wp_categories MODIFY category_count MEDIUMINT(5) NOT NULL DEFAULT '0';
ALTER TABLE wp_posts MODIFY post_title VARCHAR(255) NOT NULL;
ALTER TABLE wp_linkcategories MODIFY cat_name VARCHAR(255) NOT NULL;
ALTER TABLE wp_linkcategories MODIFY cat_id TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_options MODIFY autoload ENUM('Y','N') NOT NULL DEFAULT 'Y';

# Obvious Index Improvements
# Make login a unique index
DROP INDEX user_login_key ON wp_users;
CREATE UNIQUE INDEX  user_login_key  ON wp_users(user_login);

These will require code changes and adequate testing, something I’m not hopeful would happen unless I drove it, but you never know, the next time I review the WordPress Schema I might be impressed.

Conclusion

This is just a broadstroke first review of the schema. As part of all good design, testing of the code, further review and refinement based on better understanding of the product, and also peer review are all important steps not yet undertaken.

Migrating an MyISAM schema to use Referential Integrity

Here are some steps involved. Using the current MySQL defacto engine InnoDB. Of course, Falcon, PBXT and others will enable alternative engines to be used.

Convert Table Storage Engine Types

$ mysql -u[user] -p[password] [database] -e "SHOW TABLES" | grep -v "Tables_in" | sed -e "s/^/ALTER TABLE /" | sed -e "s/$/ ENGINE=InnoDB;/" > upgrade.sql
$ mysql -u[user] -p[password] [database] < upgrade.sql

NOTE: This may not work for all tables, for example those with FULLTEXT indexes will fail.

For the introduction of Referential Integrity we need to ensure the following.

  • Each Foreign Key column should have an index. Prior to 4.1 I think this was a requirement, however it's a good general practice regardless for SQL performance.
  • The datatype must match between Primary Key and Foreign Keys. The most obvious oversight is normally UNSIGNED, however you also for example have INT and INT, and not INT and BIGINT as datatypes.
  • Optional Foreign Keys must contain NULL values and not the normal practice of having a default of 0.

A Sample Foreign Key Syntax.

mysql> ALTER TABLE wp_usermeta ADD FOREIGN KEY usermeta_user_fk (user_id) REFERENCES wp_user(id);
ERROR 1005 (HY000): Can't create table './wordpress/#sql-cd9_10.frm' (errno: 150)

A closer investigation of what this error really is:

$ perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed

A confirmation of table definitions.

mysql> desc wp_usermeta;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| umeta_id   | bigint(20)          |      | PRI | NULL    | auto_increment |
| user_id    | bigint(20) unsigned |      | MUL | 0       |                |
| meta_key   | varchar(255)        | YES  | MUL | NULL    |                |
| meta_value | longtext            | YES  |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

 mysql> desc wp_users;
+---------------------+---------------------+------+-----+---------------------+----------------+
| Field               | Type                | Null | Key | Default             | Extra          |
+---------------------+---------------------+------+-----+---------------------+----------------+
| id                  | bigint(20) unsigned |      | PRI | NULL                | auto_increment |
| user_login          | varchar(60)         |      | UNI |                     |                |
| user_pass           | varchar(64)         |      |     |                     |                |
| user_nicename       | varchar(50)         |      |     |                     |                |
| user_email          | varchar(100)        |      |     |                     |                |
| user_url            | varchar(100)        |      |     |                     |                |
| user_registered     | datetime            |      |     | 0000-00-00 00:00:00 |                |
| user_activation_key | varchar(60)         |      |     |                     |                |
| user_status         | int(11)             |      |     | 0                   |                |
| display_name        | varchar(250)        |      |     |                     |                |
+---------------------+---------------------+------+-----+---------------------+----------------+
10 rows in set (0.00 sec)

A second pair of eyes (thanks Jon), showed that I actually spelt a table name wrong. Did you spot it. Of course it would have been nice if the error message actually told me this. This rather bland message Can’t create table could actually mean.

  • missing index (pre 4.1)
  • incompatible columns data types
  • Invalid Table.

I’m sure if I tried to break it I’d find more examples, but just a trap for unsuspecting people.

Now migrating an existing schema to using Referential Integrity provides some initial benefits (row level locking, misplaced key updates/deletes) but it does not provide true integrity unless your application has been written to support transactions. Chances are it hasn’t, but this is at least the first step.

Restyling a Mediwiki Installation – Lesson 1

Following my implementation of UltimateLAMP, read heaps more at this thread, I undertook to provide customizations of a MediaWiki Installation. Here is the first lesson that you can undertake if you wish to beautify the default MediaWiki Installation.

For the purposes of this demonstration, I am going to help out Jay & Colin and propose a restyle the MySQL forge to fit in with the default Home Page. Hopefully you will see it there soon!

Lesson 1 – Updating the default Monobook.css

There are several different ways to make style changes, the simplest is to customize the system wide Monobook.css, and this Lesson will focus on this.

By accessing the link [http://my.wiki.site/]index.php/MediaWiki:Monobook.css you will be able to make the following changes.

The best way to approach this, like any good programming style, make small changes, testing and confirmation and continue.

Note: For all screen prints, click for a larger image

1. Cleanup Backgrounds

body        { background-image: none; background-color: #FFFFFF;}
.portlet    { background-color: #FFFFFF; }

The MediaWiki Page is made up of three sections, these are the represented by styles .portlet, #content, and #footer. For the purposes of our first example, the content section and the footer section are already white.

==>

At this point I should recommend that you use FireFox for future work. You should then install the Web Developer Add-on. What results from this plugin is invaluable information about the internals of a web page. The two options most useful for this exercise is:

  • Information | Display Id and Class Details.
  • Information | Display Element Information (move the cursor around to get information)
==>

2. Cleanup Borders

I don’t feel that borders around things are warranted. I’m more a clean look kinda guy. Remove all borders, say one to separate the footer from the page.

#content    { border-width: 0px; }
.portlet .pBody
            { border-width: 0px; }
#footer     { border-top: 1px solid #888888; border-bottom-width: 0px; }
==>

Maybe, that’s a little too clean. Add some separators on left side options.

#p-navigation,
#p-search,
#p-tb       { border-top: 1px dotted #888888; }
==>

3. Links

Using the Forge Styles http://forge.mysql.com/css/shared.css we can adjust the links accordingly.

a:link      { color: #10688E; text-decoration: none; }
a:hover     { color: #003366; text-decoration: underline; }
a:visited   { color: #106887; text-decoration: none; }

a.new:link  { color: #AA0000; text-decoration: none; }
a.new:hover { color: #AA0000; text-decoration: underline; }

==>

4. Content

Ok, we have played around a little, now to move into some more serious changes. Looking at the general page look and feel that you see in most page content.


html,body,p,td,a,li
            { font: 12px/19px Verdana, "Lucida Grande", "Lucida Sans Unicode", Tahoma, Arial, sans-serif; }

h1          { font: bold 24px Helvetica, Arial, sans-serif; color: #EB694A; letter-spacing: -1px;
              margin: 1.5em 0 0.25em 0;
              border-bottom: 1px dotted #888888; line-height: 1.1em; padding-bottom: 0.2em; }

h2          { font: bold 18px Helvetica, Arial, sans-serif;  color: #EB694A; letter-spacing: -1px;
              margin: 2em 0 0 0;
              border-bottom: 1px dotted  #888888; line-height: 1.1em; padding-bottom: 0.2em; }

h3          { font-size: 12px; color: #6F90B5; }

h4          { font-size: 12px; color: #6F90B5; }

At this time, I’ve created an Example Page to better demonstration of the look and feel, as the default MediaWiki Main page has limited content.

Some Text Content at the start of the page.

= A First Level Heading =
The first paragraph text.
[[Main Page | Internal Link to existing Page]],
[[Nonexistent Page | Internal Link to nonexistent Page]],
[http://forge.mysql.com  External Link],
http://www.mysql.com  (plain text link)

== A Second Level Heading ==
The first paragraph text.

== A Second Level Heading ==
The first paragraph text.
* List Item Line 1
* List Item Line 2
* List Item Line 3
=== A Third Level Heading ===
The first paragraph text.
* List Item Line 1
** Sub Item 1
** Sub Item 1
** Sub Item 1
*** Sub Item 1
*** Sub Item 2
*** Sub Item 3
* List Item Line 2
* List Item Line 3
=== A Third Level Heading ===
The first paragraph text.
# List Item Line 1
## Sub Item 1
## Sub Item 1
## Sub Item 1
### Sub Item 1
### Sub Item 2
### Sub Item 3
# List Item Line 2
# List Item Line 3
== A Second Level Heading ==
The first paragraph text.

== A Second Level Heading ==
The first paragraph text.

==>

5. Table of Contents

By default, the Table of Contents shows at the top of a page when a given amount of sections or content is present (not sure what the trigger is). The issue is, for larger pages, it means you need to scroll down before you can see any page content. You can disable this with the __NOTOC__ option, but a better solution is to position the Table of Contents so as to not interfere with initial content.

#toc        { float: right; margin: 0 0 1em 1em; border: solid 1px #888888; text-color: #EFEFEF; color: #333333; }
#toc td     { padding: 0.5em; }
#toc .tocindent
            { margin-left: 1em; }
#toc .tocline
            { margin-bottom: 0px; }
#toc p      { margin: 0; }
#toc .editsection
            { margin-top: 0.7em;}
==>

Ok, we are about half way there.

6. Menu Options

In order to get a look and feel like the Forge Home Page., we now have to work on the rest of the navigation options at the top of the page above the content. Let’s start with Second Line of Menu Options (I’ll explain more later why).

#p-cactions { padding-right: 0px; margin-right: 0px; background-color: #006486; width: 100%; top: 77px; }
#p-cactions ul
            { margin: 0; padding: 0; list-style: none;  font-size: 85%; margin-left: 10px; }
#p-cactions li
            { float:left; margin:0; padding:0; text-indent:0; border-width: 0px; }
#p-cactions li a
            { display:block;  color:#F7F7F7;  font-weight: bold;
              background-color: #666666;  border:solid 1px #DDDDDD;
              border-width: 0px;  border-left-width:1px;  text-decoration:none; white-space:nowrap;}
#p-cactions li a:hover
            { background-color: #FFBC2F; color: #666666; }
#p-cactions li.selected a
            { background: #EA7F07;  border:none;}
#p-cactions li.selected  a:hover
            { color: #000000; }
==>

7. Top Menu Options

#p-personal .pBody
            { background: #FFFFFF url(http://forge.mysql.com/img/bggradient.jpg) no-repeat top right; }

#p-personal li a,
#p-personal li a.new
            { color: #FFFFFF; text-decoration: none; font-weight: bold; }
#p-personal li a:hover
            { color: #E97B00; background-color: transparent; text-decoration: underline; }
==>

The down side is it should be the same height at the Forge Page. This required a little more work, and other sections had to be adjusted accordingly, hence why I left this to last. (The size is based on the later mention logo height + margins)

#p-personal { height: 62px; }
#p-personal .pBody
            { height: 62px; }
#p-cactions { top: 62px; }
#content    { margin-top: 84px; }

==>

8. Some Miscellaneous Things

  • Deemphasis the text in the footer a little
  • Remove the annoying arrow that occurs after external links
  • Remove the logo from the login link
  • Hide the My Talk link
#footer     { color: #888888; }
#bodyContent a[href ^="http://"]
            { background:inherit !important; padding-right:inherit !important}
li#pt-userpage
            { background: none; }
li#pt-mytalk
            { display: none; }

==>

The Badging

Let us not forget the final step, the logo badging.

This requires a change to a MediaWiki filesystem file

LocalSettings.php.

$wgLogo             = "http://forge.mysql.com/img/mysqllogo.gif";

And the following Style changes.

#p-logo, #p-logo a, #p-logo a:hover
            { width:100px; height: 52px; }
#p-logo     { margin-left: 10px; margin-top: 5px; margin-bottom: 5px; }

#p-cactions { left: 0px; }
#p-cactions ul
            { margin-left: 180px; }

In order to overcome the top options bleeding to white text on white background, I’ve increased the right side fill of the default bggradient image, replacing the appropriate ULR with the following.

#p-personal .pBody
            { background: #FFFFFF url(/images/bggradient.png) no-repeat top right; }
==>

Conclusion

It’s not quite perfect yet, but this shows how it can be done. Some minor things are left, but I’ve run out of time for the few hours I allocated to this.
The end result of monobook.css for this lesson is:

/* edit this file to customize the monobook skin for the entire site */

/* Background Display */

body        { background-image: none; background-color: #FFFFFF;}
.portlet    { background-color: #FFFFFF; }

/* Borders */

#content    { border-width: 0px; }
.portlet .pBody
            { border-width: 0px; }
#footer     { border-top: 1px solid #888888; border-bottom-width: 0px; }

#p-navigation,
#p-search,
#p-tb       { border-top: 1px dotted #888888; }

/* Links */
a:link      { color: #10688E; text-decoration: none; }
a:hover     { color: #003366; text-decoration: underline; }
a:visited   { color: #106887; text-decoration: none; }

a.new:link  { color: #AA0000; text-decoration: none; }
a.new:hover { color: #AA0000; text-decoration: underline; }

a.external:link {color: #000000; text-decoration: none; }
a.external:hover { color: #AA0000; text-decoration: underline; }


/* Page Look & Feel */
html,body,p,td,a,li
            { font: 12px/19px Verdana, "Lucida Grande", "Lucida Sans Unicode", Tahoma, Arial, sans-serif; }

h1          { font: bold 24px Helvetica, Arial, sans-serif; color: #EB694A; letter-spacing: -1px;
              margin: 1.5em 0 0.25em 0;
              border-bottom: 1px dotted #888888; line-height: 1.1em; padding-bottom: 0.2em; }

h2          { font: bold 18px Helvetica, Arial, sans-serif;  color: #EB694A; letter-spacing: -1px;
              margin: 2em 0 0 0;
              border-bottom: 1px dotted  #888888; line-height: 1.1em; padding-bottom: 0.2em; }

h3          { font-size: 12px; color: #6F90B5; }

h4          { font-size: 12px; color: #6F90B5; }

/* Table of Contents */
#toc        { float: right; margin: 0 0 1em 1em; border: solid 1px #888888; #EFEFEF; color: #333333; }
#toc td     { padding: 0.5em; }
#toc .tocindent
            { margin-left: 1em; }
#toc .tocline
            { margin-bottom: 0px; }
#toc p      { margin: 0; }
#toc .editsection
            { margin-top: 0.7em;}


/* Second Line Top Menu Options */

#p-cactions { padding-right: 0px; margin-right: 0px; background-color: #006486; width: 100%; }

#p-cactions ul
            { margin: 0; padding: 0; list-style: none;  font-size: 85%; margin-left: 10px; }

#p-cactions li
            { float:left; margin:0; padding:0; text-indent:0; border-width: 0px; }

#p-cactions li a
            { display:block;  color:#F7F7F7;  font-weight: bold;
              background-color: #666666;  border:solid 1px #DDDDDD;
              border-width: 0px;  border-left-width:1px;  text-decoration:none; white-space:nowrap;}

#p-cactions li a:hover
            { background-color: #FFBC2F; color: #66666; }

#p-cactions li.selected a
            { background: #EA7F07;  border:none;}

#p-cactions li.selected  a:hover
            { color: #000000; }

/* Top Menu Options */
#p-personal .pBody
            { background: #FFFFFF url(/images/bggradient.png) no-repeat top right; }

#p-personal li a,
#p-personal li a.new
            { color: #FFFFFF; text-decoration: none; font-weight: bold; }
#p-personal li a:hover
            { color: #E97B00; background-color: transparent; text-decoration: underline; }

/* Top Menu Height Adjustments */
#p-personal { height: 62px; }
#p-personal .pBody
            { height: 62px; }
#p-cactions { top: 62px; }
#content    { margin-top: 84px; }

/* Minor Things */
#footer     { color: #888888; }
#bodyContent a[href ^="http://"]
            { background:inherit !important; padding-right:inherit !important}
li#pt-userpage
            { background: none; }
li#pt-mytalk
            { display: none; }

/* Badging */
#p-logo, #p-logo a, #p-logo a:hover
            { width:100px; height: 52px; }
#p-logo     { margin-left: 10px; margin-top: 5px; margin-bottom: 5px; }

#p-cactions { left: 0px; }
#p-cactions ul
            { margin-left: 180px; }

References

User Styles Alternative
Gallery of user styles
Skins
LocalSettings.php Style Information
Navigation Bar
User Rights
Wikipedia Monobook
MediaWiki Skin HowTo

UltimateLAMP

This product is no longer available. This was created over 6 years ago and software is too out of date.


As I discussed earlier in A picture can tell a thousand words, I outlined briefly what the intention of UltimateLAMP was for. Let me spill the beans so to speak.

What is UltimateLAMP?

UltimateLAMP is a fully functional environment allowing you to easily try and evaluate a number of LAMP stack software products without requiring any specific setup or configuration of these products. UltimateLAMP runs as a Virtual Machine with VMware Player (FREE). This demonstration package also enables the recording of all user entered information for later reference, indeed you will find a wealth of information already available within a number of the Product Recommendations starting with the supplied Documentation.

My executive punch line with the “right” buzz words is:


You have heard of all the hype in Open Source with lowering Total Cost of
Ownership (TCO) or Total Economic Impact (TEI)? Evaluate Open Source now
in your organistion at no cost or risk with this software package.

What are the uses for UltimateLAMP?







Well, in a nutshell UltimateLAMP allows for instant exposure of Open Source products that use MySQL. You have to remember my goal here is to promote MySQL. Unfortunately, as with any database the features alone will not get the new sale, it’s the applicability to an organisation, and with Open Source the wealth of existing and developing applications that exist can. I don’t necessarily like this approach. Indeed many open source products are poorly designed and can be poor choices in a large scale enterprise solution, but the flip side is, where else can you start.

The benefits of selling a MediaWiki for example due to the success and scalability with Wikipedia is great. So it’s important that the product list includes proven products and currently developing products (rather then stale ones). This is something that the community can definitely provide valuable feedback on to help in this selection.

Other then becoming a CD used as a drink coaster, I feel the potential is here already to provide a copy to people, even install it on a managers computer. You can’t break the software, so why not install it for your non-computer user friends/family. The goal is to move up to executive management however I feel the exposure to the general community first will greatly help.

How it came about

There is some history to this idea. Here are some of the highlights.

  • Late 2005, several planning sessions with Jon and Morgan about a more practical Open Source Contribution user group lead to obviously LAMP stack products for simplicity and exposure. This lead to exposure of LAMP stack products to more of the general person and split from the original intended goal, but was a great idea.
  • Early 2006, futher discussions of how MySQL could get exposure and traction into organisations. For myself professional, how could I promote in industry sectors that I work in.
  • In April 2006, the MySQL Users Conference with discussions of this idea with others and the positive feedback
  • In May 2006, the VMware Appliance Challenge was the possible exposure and deadline needed for me to “Just Do It”. Originally the idea was intended as a Live CD, but in some ways a virtual machine is just as good.

Where to from now!

Well, supply and demand. I don’t know if anybody else has a particular use, or will even download to use it, or market it.

I could see the potential for MySQL User Groups to get behind my idea, and enable members to filter this into known organisations. I could see for targeted opportunities/events, CD’s or information could be distributed. With the support and backing of MySQL AB, I could see the opportunity for even a breakfast CEO/CTI/CIO introduction or other format of meeting the ultimate intended audience.

I could see the potential that an organisation or entity could provide free hosting (30 days) to an organisation that pointed a 3rd level domain to the provider (e.g. wiki.acme.com). It’s like the honeypot, if it takes off after 30 days, the company will either want to pay for hosting, or what to move it. And that’s also fine, organisation provides a MySQL dump, and links to documented installation instructions, or perhaps a sale of services for initial installation/customisation/training can be made.

What can I do?

There is always a list of things that can be done. For now the greatest thing I can ask for is feedback. The good, the bad and the ugly. It’s find to get the comments to say, “That’s great”, or “Good job” or “I can use this”, it’s just as important to get the comments that are proactive in what’s not good. I would value any feedback. Please feel free to Download UltimateLAMP

On my immediate ToDo List or even partially complete is:

  • Document VMPlayer installation instructions for Windows/Linux (partial).
  • Add more product sample content.
  • Add more mediawiki content about the product, like customisation options, references to specific documentation, or other online working examples.
  • Documenting the installation/creation instructions for individual products.
  • Figure out a better way for users to contribute content that get’s rolled back into the Virtual Machines. For the mediawiki, I could see a public online copy, but for other products it could become harder
  • Optimise VM image (removing unnecessary OS stuff), removing product language support (not ideal), but my goal is to provide a 2 CD pack. The first CD has VMplayer in Win/Linux/RPM formats and the default VMware BrowserAppliance (All software from VMWare). The second CD is UltimateLAMP. Combined in a DVD 2x case along with perhaps a small booklet of a few pages, would enable this product to potential move to a commercial state.
  • Investigating other products

Should anybody wishing to help, leave a comment, that way I can see somebody is reading this and so can others.

Related Post:

UltimateLAMP Passwords

A picture can tell a thousand words

I’m a keen advocate of MySQL. However, while I use it and promote it within my limited IT circles, I often wonder how MySQL can get better traction and exposure, especially within both the industry sectors and physical locations where I am presently.

This presents a dilemma, it’s almost like the term that has been used in Venture Capital, and in the well named book, Crossing the Chasm. I see and believe that MySQL already has good penetration within certain industry sectors, specifically Internet Based, Startup Based, or Small Based segments. However, I’m sure within other commercial sectors, MySQL has either a token exposure or little to no exposure at all, at least in the circles I mix with.

So how can MySQL the product and MySQL AB the company get both better exposure and penetration? Ultimately it’s great for the community, both in dollars driving product features, product support and from my interest, more jobs. I figure there are many different approaches, and they all target organisations, and the individuals making decisions within the organisations differently. You have the 24×7 support with MySQL Network and certified installations that can satisfy management, you have the speed, flexibility, performance and capability that can appeal to a DBA. But this I doubt is the ultimate hurdle.

How do you do the active sell in 60 seconds to a potential cold contact, or how do you as an MySQL Open Source advocate within an organisation do the active or passive sell to a CEO/CTO/CIO/IT Manager etc.

Well I have an idea, it’s called UltimateLAMP. It’s not the ideal solution, it’s not the great sell, but I figure it’s one approach that I can contribute to and promote.

Stay tuned, more to come very soon.

MySQL GUI Products

I’ve started now to actively use more of the MySQL Workbench, MySQL Administrator and MySQL Query Browser and MySQL Migration Toolkit. I am traditionally a very command line person, and it’s important that these skills are never put on the back burner. For all those budding Developers and DBA’s you need these skills, expecially for any MySQL Certification.

To indicate my indent, I’ve even created a new blog category specifically for MySQL GUI Products.
As I’ve mentioned previously, Mike Zinner from MySQL AB really impressed me with where the products were going in development. I’m still rather disappointed about the stability and compatibility when attempting to run under Linux (which is my desktop), but I’ve put that aside and configured a suitable environment to best use and test these products under Windoze! I’ll also be able to trial products under both environments and provide valuable feedback to Workbench Bugs.

To overcome this, as I use Windows reluctantly in a VMware environment, I’ve decided to install all MySQL GUI Products on an older laptop (a Dell Inspiron 5000, PIII 600MHz), which I use for media playing (DivX .avi’s) on my TV. Yes, I hate to resort back to windows, but I had difficulties getting the TV Out working properly under Linux, and I only have so much free time. I would have liked to nut it out, but I find Windows superior in only 2 things, driver compatibility and running Photoshop, so I’ll leave it at that.

So being about to VNC to this machine gives me a spread of processing usage which is acceptable for now, and I’m working from a single desktop. (Time to dream some more about that Dell 24″ UltarSharp Widescreen LCD Flat Panel I’d like).

Well again, with just starting MySQL Workbench, and for the first ever time launching MySQL Administrator I came across Windows functionality I’d not seen before. Within MySQL Administrator under the Windows menu option I was able to switch to MySQL Workbench, a completely different running Windows Program. Now, I’d not seen this before in any multiple running products in Windows. Obviously the power of the GRE enables this, what a nice feature. Well back to now looking at this product some more.

WordPress Blog Upgrade Time

Time to Upgrade my WordPress Blog software from Version 1.5.2 to Version 2.0.2 after my latest spam attacks and Combating Blog Spam attempt.

Here is what I did.

cd /home/arabx/www
tar cvfz blog.backup.20060520.tar.gz wordpress-1.5.2
mysqldump -uroot -p arabx_blog > blog.db.20060520.sql
mv blog.*20060520* /u01/backup   # Not in my WWW
scp blog.*20060520* to@someplacesave:/backup
wget http://wordpress.org/latest.tar.gz
mv latest.tar.gz    wordpress-2.0.2.tar.gz # I really hate unversioned files

Disable Plugins. You had to do this manually from the Admin interface (I’m sure it’s just a SQL statement).
Goto http://blog.arabx.com.au/wp-admin/plugins.php

Now some more work.

tar xvfz wordpress-2.0.2.tar.gz  # creates a wordpress directory
mv wordpress wordpress-2.0.2
ln -s wordpress-2.0.2 blog
cp wordpress-1.5.2/wp-config.php wordpress-2.0.2

Back to the Web page again. So much for unattended upgrades?

http://blog.arabx.com.au/wp-admin/upgrade.php

Follow the Steps (step actually). That all worked, now to restore all the additions?

cp -r wordpress-1.5.2/wp-content/themes/arabx wordpress-2.0.2/wp-content/themes/
cp -r wordpress-1.5.2/wp-content/plugins/eventcalendar3* wordpress-2.0.2/wp-content/plugins/

Reactivate my plugins at http://blog.arabx.com.au/wp-admin/plugins.php
Change to my Theme at http://blog.arabx.com.au/wp-admin/themes.php

So my blog page http://blog.arabx.com.au/ looks much like it did previously. Time now to review some of the new features and plugins, expecially steps to combat spam.
References
WordPress Official Upgrade Page
Upgrade to WordPress 2.0
What’s New in WordPress 2.0?

MySQL :: Developer Zone Quick Polls

I don’t get to the MySQL Developer Zone main page often enough. In thinking about what pages I view everyday or regularly, it doesn’t rate as high as Planet MySQL, MySQL Forums or even the MySQL Forge.

I was most dissappointed in the results of a recent poll What did you think of the 2006 Users Conference?. The top response was I had no idea there was a Users Conference. That’s not good to see this.

An interesting poll What are you most looking forward to at the MySQL Users Conference (April 24-27)?, the clear winner was Drinking beer with MySQL gurus. What does this say about the attendees. Either they are all alcho’s or the just want to be around guru’s in a less technical way.

I see this page also has a live feed of Planet MySQL. Perhaps we should get some more stuff down the right side of PlanetMySQL like the current Quick Poll itself and a feed of the current developer articles at the Developer Zone.

A Post MySQL Conference review. The 4 F's

Finally back home after some R&R at Yosemite before leaving the US. In conclusion, to sum up my experience of the 4th Annual MySQL Users Conference “Excellent”.
Here’s my take. Friends, Functionality & New Features, the Future.

Friends

I’ve used MySQL now for over 6 years, and full time for a number of years, yet I’ve only become active in the MySQL community, particularly Planet MySQL in the past 6 months. Over that time, I’ve read a lot from members, and heard from many people. It was great at the conference to meet many of these people for the first time. The list includes: Community MembersFrank Mash,Mike Kruckenberg, Markus Popp, Roland Bouman, Giuseppe Maxia and Paul McCullagh. MySQL EmployeesMike Hillyer, Colin Charles, Jay Pipes, Mike Zinner, and New ContactsKristian Köhntopp, Jeremy Cole, Sheeri Kritzer, Taneli Otala, Laura Thompson just to start the list.

Functionality

Not only was there plenty of discussion on Server Functionality, there was plenty of MySQL Client functionality including the MySQL Workbench, MySQL Migration Toolkit and the other MySQL GUI products.
There were a number of discussions on uses and implementations of MySQL in large web deployments. It would be great to see some more white papers here.

New Features

A few months ago I wrote an article A call to arms!. In some part, I was just giving my opinion and hoping to gee up some support and feedback from the community. Well, the MySQL 5 Pluggable Storage Architecture got a great boost with announcements of transactional storage engines Falcon by Jim Starkey, Solid and PBXT. Add details of InnoDB New Features, MyISAM additions, and indications of other wonderful if not entirely practical options. I’m sure there is much more in stall to come this year that wasn’t discussed.

A number of talks featured Cluster including Monday’s tutorial, and with 5.1 and beyond I can see next year there will be more discussion on successful Cluster implementations. There was a lot of talks about Scaling out. I’d like to see more practical examples, perhaps a detailed tutorial.

The Future

What does the future hold for MySQL? The MySQL Server and Storage Engine Roadmap provided an insight of the upcoming planned features and releases over the next 2 years. Of course, the marketplace can change quickly, and MySQL is in a great position to react to the needs of the community quickly.

And before your know it, the 5th Annual MySQL Conference will be in play.

Conference Feedback

One thing I had a chance to discuss with Jay Pipes after the conference, I wasn’t the first to mention, and plans are already in motion, was a number of talks just needed more time. Moving the schedule to 55 minute talks gives that extra time, even if it is open question time from the floor, but it also makes knowing when sessions are on much easier, if they always start at the top of the hour.

In Conclusion

Frank (a.k.a Farhan Mashraqi) asked me what session I liked the most? Hard to say. Agile Database Techniques: Data Doesn’t Have to be a Four-Letter Word Anymore rated very highly, as the content was close to heart and my expertise. HackFest B: Creating New SHOW Commands by Brian Aker, showed just how easy it was to get into the MySQL source. Of course the internals are much more complex then this, but it was a good introduction. My favourite keynote was The Ubuntu Project: Improving Collaboration in the Free Software World. There were a number of talks I was disappointed in, as well as a number I didn’t get to due to 8 sessions in parallel.

I would have to say, that what impressed me most was no one single talk, but the functionality of the GRT Shell that Mike Zinner and his team have built into the GUI product line. I was very impressed, and I could see this providing extensive functionality and not just MySQL specific centric tools. This will be area I’ll be focussing on my contributions in the near future.

MySQL Stored Procedures Performance

Another one of the sessions at the MySQL Users Conference I attended was Tuning MySQL5 SQL and Stored Procedures by Guy Harrison from Quest Software. A global company with 6000+ customers.

Guy has written a number of Oracle Performance Books in the past. His work now is on the “Spotlight” product family – Database diagnositic tools converting data to graphical representations. For these products, MySQL 5 and InnoDB only is necessary, simply due to accessing the right internal information for presentation. There are Freeware MySQL product downloads.

In this presentation he stated, nothing he was talking about specifically was relatively new. He did make quite a funny comment, “He is now seeking refugee status in the MySQL Community”.

Guy is author of O’Reilly “MySQL Stored Procedure Programming” Book. I managed to get for free at the conference from the MySQL Quiz night, in addition to a shirt and cap for stumping a Guru.

His talk were on tools and techniques for tuning MySQL.

  • Explain Command – reveals what the optimizer intends to do
  • Explain Extended
mysql> explain extended select ...;
mysql> show warnings G
Shows what the optimizer actually did. In this example, An IN was converted to EXISTS

There were 4 ways to provide optimizer hints.

  1. STRAIGHT_JOIN
  2. USE INDEX(…)
  3. FORCE INDEX(…)
  4. IGNORE INDEX(…)

In addition to the Show Query Log, there are Innodb specific commands, two in particular.

show status like 'innodb%'
* innodb_buffer_pool_read_requests
* innodb_data_read

Indexing and the optimizer

  • In MySQL Index is the best tool to improve performance, however sometimes it’s better to access the entire table.
  • Indexes generally effective when between 5% and 20% of rows are accessed.
  • Subqueries need to be satisified by an index or performance will be quite inefficent.
  • Overloading indexes with additional columns when key queries only use a few columns can enable improved performance.

Not all indexes are created equal. In the following examples, each advancement improved performance.

  • No indexes ()
  • Single Index (customer)
  • multiple indexes (customer, product)
  • concatenated indexes (customer + product)
  • covering index (including required columns, customer+product+qty)

Examples of SQL that can’t benefit from Indexes.

  • Derived tables – SELECT table in a from clause, creates a temporary table and will never get an index.
  • Views with UNIONS/GROUP BY

A comment from the audience was that derived tables can be of a benefit to a correlated sub-query in specific examples.

Stored Procedures provided a mixed blessing for performance.

  • Can improved perfomrance when high network overhead.
  • Some improvement on parsing.
  • Breaking up complex queries may provide benefits.
  • SQL is highly optimized for SET operations.
  • SP is not optimized for number crunching. Computionally not a fast language.

A written routine to calculate prime numbers provided the following performance (from most expensive to least) MySQL SP, Oracle SP,PHP,Perl,Java ,VB.NET ,C (gcc). This showed an example that was excessively inefficient. On the other hand, if the program is network dependent (e.g. access a million rows, perform some statisical aggretation). Comparatively the same between Java and SP locally, but much better in a remote host mode.

Performance of SQL in a SP will dominate overall performance. Where SQL is tuned, goto tried and proven traditional optimisation techniques.

  • Optimize iterations
  • Optimize Logic/Testing
  • Avoid recursion

Loop Management

  • Only perform necessary code within iterations
  • LEAVE or CONTINUE when possible in loops
  • Test the most likely IF/THEN statements first
  • extract if comparisions duplicated to produce nested if’s (within reason)

Some guidelines for Triggers.

  • Triggers will have a non-trival overhead for even the simplest trigger.
  • Due to FOR EACH ROW only, don’t have expensive SQL in any trigger.
  • Very carefully tune SQL in triggers.
  • Empty trigger produced 12% overhead.

For more information check out www.quest.com/mysql

Mark Shuttleworth

Thursday’s Keynote speaker at the MySQL Conference was Mark Shuttleworth talking on The Ubuntu Project: Improving Collaboration in the Free Software World.

Ubuntu from Canonical Ltd is fastly becoming one of the most popular Linux Distributions, based on Debian. Here were some notes from his presentation. Mark had some really powerful one or two line slides that typified both his presentation intention and the goal of Ubuntu.

Delivering on the Promise of Free Software

  • MySQL levelled the playing field, individuals could use MySQL to build applications that could now compete in the industry.
  • Apache, Python and Linux made it all possible.
  • This has provided the Opportunity and passion to build software that can serve a purpose.
  • Ubuntu is the continuation of levelling the playing field. Making always available at no cost.
  • Whatever your vision, you have the opportunity without financial limitation.
  • To be a pioneer in new fields, open source freedom enables the opportunity to anybody.

The New Deal of Free software

* Different Economics

  • Ubuntu is world class and free of charge.
  • Important to recognise the governence.
  • We don’t build 99%, it’s built by communities. We want to maintain good healthy and open relationships with the communities of software developers and contributors.

* Different culture

  • Transparency. You can participate, you get to see how the software is built. Difficult for co-ordination of people worldwide, the advantage is you get to be people in the public space everywhere.
  • To be able to try, experience to express their own inspiration.

Our vision is to build a complete community-driven distribution that can grow and substain itself through support and services.

Self substaining at a professional level. Community Driven.

  • The Ubuntu Proposition
  • Genuine Freedom and Flexibility
  • Zero Licensing Fees
  • Certification: Hardware, Softare, Skills
  • Superb Commercial Technical Support
  • Standards Compliance
  • Internatiolzation and customization
  • Collobaration to the Core

We do the best work when we focus on extreme challenges.

Mark made a great comment about the Chinese Version, What was in English on any pages, for Mark was the saving grace, but for the Chinese user, that was the wall, so a committed emphasis on Internationalisation was important.

Ubuntu as you know it today is widely rated #1 desktop Linux with millons of users globally, with complete office and home desktop distributions. We started on the desktop, we are continuing to expand into different variants including the enterprise. The next release in June 2006 – Dappy – 6.06 will includ LTS (Long Term Support) 3 years for Desktop, 5 years for server.

We maintain relationships with the community ecosystem.

  • Looking the the User Relationships.
  • Prime focus is simplicity. Also Comprehensive and Community, Current.
  • For the business relationships, wanting to offering Tiered Partnership Program and Extensive Global Solutions Provider Network.

Free Software Relationships

  • Close to upstream (e.g. MySQL, Apache, Firefox etc)
  • Close also to other distributions (both debian based and not). We want the best of their work to also be part of what’s available to all.
  • Need to ramp up the collaboration of projects especially between projects. Check out launchpad.net
    Site – Much easy to colloborate across projects
  • With MySQL and Ubuntu you have freedom to deploy without asking permission, and support when and where you need it
  • A key is make packaging ubuntu and mysql the best getting contributions from the community. Expresses most powerfully what MySQL can do.

Beating history into submission

  • We judge ourselves by history. We should look at our strengths. we should ask “What futures are enabled.”

Free software enables a different economic paradigm.
Embrace it.

A question from the audience was where does the name Ubuntu come from. The Answer was.
Ubuntu is common to many African languages. It means “human-esse”. The Ubuntu root is common to a lot of words. Some means included “The way we look after other people defines who we are. ”

Kubuntu – Has a meaning “For Humanity”.



My MySQL Conference Presentation


My talk at the MySQL Users Conference went well. MySQL For Oracle Developers was part of two talks, the second was by Mark Leith on MySQL For Oracle DBA’s.

I had a number of positive comments from attendees, including Ken Jacobs of Oracle who also contributed some valuable information in comparision of UPDATE/DELETE ORDER BY/LIMIT statements I was unaware of.

You can download my paper here.

My slides, like many talks just scratched the surface in the alloted. I’ve been working on additional reference material, a work in progress is available at MySQL For Oracle Developers. My continuatation on this will be dependent on feedback from the community of it’s intended value.

Hacking MySQL Source improvements

Further to my earlier post Hacking MySQL Source (in a good way) in which I was having a compilation problem when re-compiling MySQL source (i.e. tt worked the first build, but failed on subsequent re-compiles, even with no changes).
I’ve been able to solve my re-compiling issue, with special thanks to Jan Kenschke of MySQL AB, who was near during the MySQL Quiz Show.

As suspected, there is no need to re-run the ./BUILD/compile-pentium-debug command as this cleans, a simple make command is sufficient (I needed to confirm no other command args where needed). By doing this, it also better highlighted the actual error, where previously is wasn’t. I didn’t keep a copy of the error, but in effect I needed to do the following in order to successfully recompile.

su - mysqldev
cd mysql-5.1.10-beta-nightly-20060413
touch mysql-test/std_data/client-key.pem
touch mysql-test/std_data/client-cert.pem
touch mysql-test/std_data/cacert.pem
touch mysql-test/std_data/server-cert.pem
touch mysql-test/std_data/server-key.pem
make
# Success

I can’t answer why I had to do this manually (create these cert permissions), and why some level of make doesn’t (or does but I don’t know the command), but it works, and right now that’s important. Hopefully a MySQL guru can enlightment me.

From here, I was able to test as I’d written previously. I just need to confirm my changes work before reporting back, as well as providing some guidelines for debugging with gdb.

Contributing to the MySQL Community

Everybody can contribute to the MySQL Open Source Community. Here are some ideas:

  • You can start with completely a MySQL Conference Survey form (those forms that we are all avoiding).
  • You can download, evaluate and test newer versions of MySQL Products.
  • You can contribute to the MySQL Forums to ask questions and even give answers.
  • Log Bugs & Enhancements on MySQL Products.
  • Write about your experiences with MySQL in a Blog and add your Blog to PlanetMySQL. With now over 100 feeds, if everybody wrote something about MySQL in the next year, we would have over 1000 feeds next conference.
  • Contribute your thoughts (via wiki), MySQL products, code snippets and examples to MySQL Forge (thanks Mike)
  • You can even contribute to the MySQL Source Code.

If you are passionate about MySQL and you are attending the MySQL User Conference, then jump into the Open Source MySQL Community.

Extracting User SQL Queries from a MySQL Server

Joe Kottke from FeedBurner in his MySQL Conference presentation today FeedBurner: Scalable Web Applications Using MySQL and Java mentioned a novel way of extracting SQL Select Statements from a MySQL Server.

The obvious MySQL ways include SHOW [FULL] PROCESSLIST, the Slow Query Log, and the General Query Log. In 5.1 you also use the INFORMATION_SCHEMA.PROCESSLIST.

Anyway, Joe mentioned he does a strings on tcpdump to extract the queries, and there was no need to restart the server (this point interested me). So, not letting a challenge go, having never used tcpdump.

Well, here’s how.


tcpdump -w mysqldump.txt -i lo

NOTE: You must log output to file with -w to get the full packets and not just headers.

In a seperate session.


$ mysql -uroot -p mysql -h `hostname`
mysql> select * from user;
mysql> exit
# I did a ctrl-C in the other terminal (not sure if necessary)
$ strings tcpdump.txt
select * from user
mysql
user
user

NOTE: localhost wasn’t valid, it had to be the hostname of the server. Of course you could access the server from a different client.

Of course there may be other or better ways, but it worked for me and was rather cool.

Perhaps this is a good reason to use SSL?

MySQL Migration Toolkit Extensions

Just to add some more features of the MySQL Migration Toolkit from the MySQL Conference presentation MySQL Migration Toolkit by Mike Zinner from MySQL AB.

You can extend and modify the funcionality of the Migration toolkit with the supplied Eclipse Java project. I’ll need to download the code to write some more detailed notes, but here were a few points from Mike.

Classes are in the com.mysql.grt.modules Package Space
The main configurable classes are nameed Migration????..java and ReverseEngineer???.java with ???? being for the different Database Products

Within the Eclipse project, you can incoporate other languages. For example, ReveserEngineerMySQL Jdbc.java for example has a callGrtFunction to code written in C. This I’m assuming will allow you to incoporate all the other languages of the GRT such as Python, Lua. (I’m still impressed you can actually do this, a mini project for another day to pull this apart.

A question from the audience was about Oracle User Defined data types. MigrationOracle.java for example can easily be extended to cater for the migration of user defined data types to a suitable valid MySQL alternative.

References
MySQL Migration Toolkit – Provides details, screenshots and Flash Tutorials for SQL Server, Oracle and Access Migrations.

The MySQL Migration Toolkit

A Wednesday afternoon presentation at the MySQL Conference was MySQL Migration Toolkit by Mike Zinner from MySQL AB.

In summary, the Migration Toolkit currently provides the following feature set.

  • Wizard like interface
  • Plug-in architecture
  • Migration of Oracle/MS SQL Server/MS Access/Generic JDBC Data Sources. 2006 support for Sybase and DB2.
  • Provides user support to expand to other databases
  • Customisable the produced wizard scripts at any step
  • Completely Scriptable (the wizard interface can produce command line replay scripts) -allows re-running, re-scheduling
  • Binaries include an Eclipse Java Project to enable easy extension of the Migration Toolkit.

The product is built on the Generic Runtime Environment (GRT) which is used in other MySQL GUI Products. The GRT is a thin C Layer that allows you to easily add modules written in other languages (e.g. C/C++, Java, Lua, Python etc).

The following are the steps of the Migration process.

  • Source/Target
  • Object selection
  • Object Mapping
  • Manual Editing
  • Schema Creation
  • Data Mapping
  • Bulk Transfer
  • Summary

Some more relevent points on some of the individual steps.

Source/Target

  • Screen caters for source specific requirements (e.g. SID for Oracle)

Object selection

  • Choose the different Schema Objects, including tables, views. Can limit by type, or individual objects
  • For Oracle additional objects include Schemas, Routines and Sequences

Object Mapping

  • Define Methods for mapping of schema objects
  • Includes Language types (e.g. latin1), multilanguage (utf8), or a user defined
  • For Tables you can choose the purpose which includes data consistency, statistical data. These rules will include determining best storage engine, intepreting auto increment columns.

Manual Editing

  • You Can drill down in great detail to define the object mapping, down to a column level
  • Provides a migrated objects review, including warnings (for example views need to be manually checked, or sequence not migrated)

Schema Creation

  • Can create objects in real-time or create a SQL script for later executation
  • You can review the generate SQL code that will be used in real-time and perform further manual refinement (e.g. converting to specific SMALLINT,INT,MEDIUMINT refinement)
  • Will show any warnings, and allow for manual correction

Data Mapping

  • Can create data in a real-time to a MySQL database
  • Create Insert Statements (normal data and blob data) (optimized multi-row insert with byte limits)
  • No facility at present for flat file creation/loading via LOAD INFILE

Bulk Transfer
Summary

  • A migration report which can be saved to disk.
  • Create a Migration script for the complete process. (which is a generated lua script)

I can see the possibility for the following enhancements.

  • Consider ENUM fields by analysing data (columns char < 20) and offer as data type
  • Consider determining AUTO_INCREMENT columns

References
MySQL Migration Toolkit – Provides details, screenshots and Flash Tutorials for SQL Server, Oracle and Access Migrations.

Hacking MySQL Source (in a good way)

HackFest B: Creating a New SHOW Command by Brian Aker at the MySQL Users Conference

Brian stepped through the steps for those attending to modify and deploy new functionality in the mysql server. Cool. The end result I would consider for an experienced developer as relatively easy (after avoiding the pitfalls).

NOTE: I wasn’t able to complete this successfully during the session, but I’ve posted this, so hopefully the input and review of others can help in overcoming the current issues. See Outstanding Issues throughout my notes.

Our goal: Produce a new command SHOW CONFERENCE;

The following commands were performed on CentOS 4.2. There may be some differences with different Linux Distros.

Prepare a current Source Code Tree


su -
useradd mysqldev
su - mysqldev
wget http://downloads.mysql.com/snapshots/mysql-5.1/mysql-5.1.10-beta-nightly-20060426.tar.gz
# NOTE: You should check the snapshots page for latest versions http://downloads.mysql.com/snapshots.php
tar xvfz mysql-5.1.10-beta-nightly-20060413.tar.gz
cd mysql-5.1.10-beta-nightly-20060413
# compile script will depend on H/W and requirements
./BUILD/compile-pentium-debug --prefix=/home/mysqldev
make install
scripts/mysql_install_db --datadir=/home/mysqldev/data
sql/mysqld --basedir=/home/mysqldev --datadir=/home/mysqldev/data &

NOTE: This can take a while, so it’s a good opportunity to leave this and come back at a later time. The –prefix allows you to install into the new users directory ensuring that you can test without affecting any currently installed mysql installations on your machine.

The BUILD directory contains many different compilations for platforms and variants. This version contains 41 specific scripts. For the purpose of our new SHOW command, we are going to run in debug mode for any necessary debugging.

Outstanding Issues

  • I was surprised a make install didn’t configure the bin/mysqld and scripts/mysql_install_db as you would get in a binary distribution. There is probably additional commands necessary for this, but for the purpose of development it works.
  • I run multiple versions of MySQL on my machine, and I had some conflict with my /etc/my.cnf file, so I just removed it for the purpose of this test. There needs to be a cleaner way to ensure this isn’t used, and also to compartmentalise/isolate different source trees.

Confirm Installation

I ran this in a seperate terminal window.

su - mysqldev
bin/mysql -e "SELECT VERSION()"
+------------------------------------+
| VERSION()                          |
+------------------------------------+
| 5.1.10-beta-nightly-20060413-debug |
+------------------------------------+

This gives us a suitable source baseline.

Implementing a new SHOW Command

The easiest means of developing a new SHOW command is to base this on an existing command. We are going to base this new SHOW command on the SHOW AUTHORS command.

We are going to be looking at the following files.

  1. lex.h
  2. sql_yacc.yy
  3. sql_lex.h
  4. sql_parse.cc
  5. mysql_priv.h
  6. sql_show.cc

All these files are found under the sql directory in the source tree.

lex.h

131:     { "CONFERENCE",       SYM(CONFERENCE_SYM)},

sql_yacc.yy

 202:  %token  CONFERENCE_SYM
...
8267:       | CONFERENCE_SYM
8268:         {
8269:           LEX *lex=Lex;
8270:           lex->sql_command= SQLCOM_SHOW_CONFERENCE;
8271:         }
...
9366:       | CONFERENCE_SYM        {}

sql_lex.h

112:   SQLCOM_SHOW_CONFERENCE,

sql_parse.cc

3515:   case SQLCOM_SHOW_CONFERENCE:
3516:     res= mysqld_show_conference(thd);
3517:     break;

mysql_priv.h

915: bool mysqld_show_conference(THD *thd);

sql_show.cc

229: bool mysqld_show_conference(THD *thd)
230: {
231:   List<item> field_list;
232:   Protocol *protocol= thd->protocol;
233:   DBUG_ENTER("mysqld_show_conference");
234:
235:   field_list.push_back(new Item_empty_string("Name",100));
236:
237:   if (protocol->send_fields(&field_list,
238:                             Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF))
239:     DBUG_RETURN(TRUE);
240:
241:   protocol->prepare_for_resend();
242:   protocol->store("Welcome to the MySQL User Conference 2006", system_charset_info);
243:   if (protocol->write())
244:     DBUG_RETURN(TRUE);
245:   send_eof(thd);
246:   DBUG_RETURN(FALSE);
247: }


cd ..
./BUILD/compile-pentium-debug --prefix=/home/mysqldev
...
gmake[3]: *** Waiting for unfinished jobs....
/bin/chmod +x mysql-test-run-t
/bin/mv mysql-test-run-t mysql-test-run
gmake[3]: Leaving directory `/home/mysqldev/mysql-5.1.10-beta-nightly-20060413/mysql-test'
gmake[2]: *** [all-recursive] Error 1
gmake[2]: Leaving directory `/home/mysqldev/mysql-5.1.10-beta-nightly-20060413/mysql-test'
gmake[1]: *** [all-recursive] Error 1
gmake[1]: Leaving directory `/home/mysqldev/mysql-5.1.10-beta-nightly-20060413'
gmake: *** [all] Error 2

Outstanding Issues

  • I’m sure there must be a more effecient means of compiling changes, this process effectively cleaned and started again.
  • I had a build failure which didn’t seem to make any sense.

At this point I wasn’t able to continue, but here are notes I took of next steps.

make install
gdb mysqld
run --gdb --debug

In another terminal session.

su - mysqldev
bin/mysql
mysql> SHOW CONFERENCE;

I’m keen to see it work in my own environment and documented for others to try.

Brian moved on to creating a INFORMATION_SCHEMA query but we ran out of time to complete this. I’ve got some notes to document as a later date.

PBXT – The MySQL Community Transactional Storage Engine

In having a discussion with Paul McCullagh (the creator of PBXT transactional storage engine) and Taneli Otala MySQL AB CTO after the keynote presentation at the MySQL User Conference, Taneli made the following comment (paraphrased and reproduced with permission).

“I talk about PBXT in discussions shamelessly. The development of PBXT was excellent timing in the MySQL community landscape”.

It was an excellent commendation that MySQL AB management considered so highly the contributions from the community. As mentioned in the opening keynote, MySQL with the Storage Engine API with MySQL 5.1 has great potential to expand what options are available to user of MySQL.

Good work Paul. I like many others wish you the best in your continued development and contribution.

Improvements in SHOW STATUS

It just dawned on me as this topic was mentioned in a MySQL Conference presentation yesterday in a manual process. So my thought is, why can’t the following functionality be added to the MySQL server.

My request for two new extensions to the SHOW STATUS command.

SAVE STATUS – This enables the current SHOW STATUS to be saved (or more specifically cached), you can only keep one copy per server instance.

DIFF STATUS – This shows the difference between the current SHOW STATUS and the last saved show status from the SAVE STATUS command.

This would quickly, easily and interactively via a mysql prompt enable a DBA to see the state of change in a more condensed form. The syntax may need to cater for Session/Global scope, and of course would need to be appropiately named, my examples is just an idea.

Of course you can write an easy script that does the same, and provide a diff, but why not include it directly?

Could not have said Agile better myself

I’ve just attended Scott Ambler’s presentation on Agile Database Techniques: Data Doesn’t Have to be a Four-Letter Word Anymore at the MySQL Users Conference.

There is so much content on the topic, it’s impossible to present so much information in a short 45 minute session. I can speak with authority in regards to the same problem of condensing so much content given this issue with my own presentation MySQL for Oracle Developers.

I ask this question. Why is common sense considered such a radical approach? I state this because Agile Methodology approaches in so many ways are common sense, but “traditionalists” (and I use this term for several groups of existing IT dinosours), see change and continual improvement approaches as potentially evil, while they are constantly just trying to stay afloat daily with bloated, inefficient and overly complex legacy systems (I had to throw in several daggers at the same time, couldn’t resist).

This presentation echoed both a lot of my experiences and part of my writings and current projects. I have been a database modeller for over 16 years, and I have worked with Extreme Programming Agile Methodology for now over 6 years. Here are some abstract and unstructured bullet points from the presentation and some of my own comments intermingled. (Unfortunately due to other discussions I missed the initial 15 mins, however given the content and my own professional standings I can only fully understand what the initial content was).

  • Agile Modeling Driven Development AMDD
  • Agile Data Modelling
  • Iteration o (zero) should consist of an Initial Domain Model to provide scope and visibility of the bigger picture, but only a higher level view. Any extended time spent is wasted time.
  • Why use this approach to data modelling? To handle change efficiently.
  • There is a clear lack of tools and techniques in Automated Testing and Code Coverage concepts specifically for Data. See my thoughts on this at the links at the end of this entry.
  • Incremental software development enables a production rollout after every iteration
  • There is movement to a Data Modelling Standard based on the UML Notation
  • Transitioning approach to schema changes. Interesting concept, not necessary in a new project with Test Code Coverage, but essential for Legacy systems for a low risk approach using small steps.
  • Stop talking about data quality and actually doing something about it. Bad data, or more specifically must have migrated data, that breaks all the integrity in a new system has been the bain of my experiences in large data migration projects.
  • Generalising Specialists

Scott threw in a lot of Agile terms, and for most people in the audience I observed that quite a few of these terms were indeed foreign. I could easily see the potential for a Talk on Introduction to Agile – Applying to MySQL Projects.

One term I didn’t hear was YAGNI – You Ain’t Gonna Need It. This is an essential XP principle which in some ways sums up the common sense approach to software development. Don’t even thing about it until it’s made it’s way to the top of the customer requirements for the current interation.

I’ll also be getting the book “Refactoring Databases – Evolutionary Database Design” that was mentioned, I’m keen to read in depth more of the principles I so much promote myself. Sometimes I feel quite isolated in this area of Data Modelling colliding with Agile Methods.

I’ve written previously content that both re-inforces a number of points of this presentation and also complements Scott’s presentation in a number of ways. These include Unit Testing A Database and Database Modelling within an XP Methodology.

I recall this quote from a tee-shirt once owned, and I think is valid for certain IT professionals that continue to cling to traditional approaches in data modelling. Perhaps I should put it on an Agile slogan shirt for myself. Evolve or Die

In closing, I’ve been wanting to write a paper for quite some time titled “Better Productivity and Quality. An Agile Approach” to share my experiences. I’ve haven’t been able to put my thoughts down, having two other major writings in progress at present, but this presentation has only renewed my vigor.

Opening Conference Keynote

Mike writes a good summary of the MySQL Conference opening keynote State of the Dolphin: Interview with Kaj, Monty and David of MySQL.

I’d like to add just two comments.

Firstly, it was great to see community awards to Giuseppe, Roland, Marcus and Rasmus. Well done!

Second, it was a great thing to see on the slides a reference to Paul’s PBXT Transactional Storage Engine. A MySQL storage engine from the community. I’ve had a chance to meet Paul and have a number of great discussions with him. I wish him all the best, and I’m happy to contribute what I can to see his engine make it into the MySQL product in the future.

Bootstraping

Tuesday’s Keynote speaking from the MySQL Users Conference including a presentation from Greg Gianforte of RightNow Technologies on “Bootstrapping: Starting an Open Source Business With Almost No Money!Read More.

Taking directly from Greg’s presentation, here are some key points for bootstraping that meant something to me.

  • Sales is Job #1
  • Don’t spend Beyond Your Means
  • There Is Always Another Way
  • What is Your Noble Purpose?

Some great points. In summary, not having funds should not be an excuse, find another way . You just need to think creatively. I agree completely, thinking creatively is something I do well. Immerse yourself – talk to lots of people, develop a one page description, get it out to as many people as possible, talk to people, ask for an order. Of course sales is #1, but for me personally, and also within the essense of the presentation – Honesty. His passion about helping people to get started has also lead to publishing a book for bootstrappers (in paperback to save money).

One of the key goals is to “Help our clients to better serve our customers”. Greg was also correct in the need to correct the loss of ethics in the IT and software industry, something I fight for back at home.

A number of these points provided great relevence given I’ve worked for two failed Internet startups.

The closing quiz raised some great questions. Here they are. (Hopefully now reproducible in the Open Source community, oops too late now!)

  • #1 What should you do first?
  • #2 You have an idea. What next?
  • #3 You have a good product, What next?
  • #4 You find a prospect. What next?
  • #5 Your product does not do everything a prospect wants. Should you?
  • #6. A major publication calls saying they are writing a big article about your products and wants you to buy an ad in the same issue of their magzine. You should?
  • #7 You are still gettting started and Dun & Bradstreet is asking for company info and detailed financial data. Should you:
  • #8 A major prospect says they want to fly to visit you, but you are still working out of your house. Should you:
  • #9 You are the only person working full time in the business and you are aksed how many employees you have. Tell them:
  • #10 You’ve just made your first sale and generated your first montly profit. You should.

I’ll save the expense by not giving any answers, but my favourite answer was for #9.

Greg started the presentation with the right words for me. Love Mountains, Love Skiing, check out our Careers. Anybody that knows me, knows that my plan for this year, it to be in the Northen Hemisphere for Winter, working for an organisation that is close to the snow, enabling me to go skiing. I just may have check it out.