Ronald Bradford | Enterprise Data Architect | MySQL Subject Matter Expert | Author | Speaker

ronald · January 31, 2006

Following my earlier post of MySQL Workbench 1.0.1 for Linux and logging a MySQL Bug, I’ve had the Bug verified, and the a further update of a compiler success. Details of compile from Bug #16880

Compiled with:
- glibmm-2.8.1 - gtk+-2.8.8 - libsigc++-2.0.11

I’ve got:

libsigc++-2.0.17 glib-2.6.6 glibmm-2.6.1 atk-1.9.0 pango-1.8.2 gtk+-2.6.9 gtkmm-2.6.5 lua-5.0.2

So starting with the obvious downgrading of libsigc++

$ su - $ cd /src $ wget http://ftp.gnome.org/pub/GNOME/sources/libsigc++/2.0/libsigc++-2.0.11.tar.gz $ tar xvfz libsigc++-2.0.11.tar.gz $ cd libsigc++-2.0.11 $ ./configure --prefix=/usr make make install

Now to retrace the MySQL compiling.

$ su - $ cd /src $ wget ftp://ftp.mysql.com/pub/mysql/download/mysql-workbench-1.0.1.tar.gz $ tar xvfz mysql-workbench-1.0.1.tar.gz $ cd mysql-workbench-1.0.1 $ cd mysql-gui-common $ ./configure --enable-grt --enable-canvas $ make

No errors, a sound start.

$ cd ../mysql-workbench/ $ ./configure $ make ../../../mysql-gui-common/library_gc/source/libgcanvas.a(myx_gc_gl_helper.o)(.text+0x1b5): In function `getProcAddress(char const*)': /src/mysql-workbench-1.0.1/mysql-gui-common/library_gc/source/myx_gc_gl_helper.cpp:264: undefined reference to `glXGetProcAddress' collect2: ld returned 1 exit status make[3]: *** [mysql-workbench-bin] Error 1 make[3]: Leaving directory `/src/mysql-workbench-1.0.1/mysql-workbench/source/linux' make[2]: *** [all] Error 2 make[2]: Leaving directory `/src/mysql-workbench-1.0.1/mysql-workbench/source/linux' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/src/mysql-workbench-1.0.1/mysql-workbench/source' make: *** [all-recursive] Error 1

Crash and burn. So try to reproduce with the more accurate libraries. Of course it’s a bit of trial and error, here is the end result in order, but it was much more difficult as previously to get this.

$ cd /src $ wget ftp://ftp.gtk.org/pub/gtk/v2.8/pango-1.10.2.tar.gz $ tar xvfz pango-1.10.2.tar.gz $ cd pango-1.10.2 $ ./configure --prefix=/usr $ make $ make install $ wget ftp://ftp.gtk.org/pub/gtk/v2.8/glib-2.8.6.tar.gz $ tar xvfz glib-2.8.6.tar.gz $ cd glib-2.8.6 $ ./configure --prefix=/usr $ make $ make lib $ wget ftp://ftp.gtk.org/pub/gtk/v2.8/atk-1.10.3.tar.gz $ tar xvfz atk-1.10.3.tar.gz $ cd atk-1.10.3 $ ./configure --prefix=/usr $ make $ make install # (sigc++-2.0 >= 2.0.0 glib-2.0 >= 2.8.0 gobject-2.0 >= 2.8.0 gmodule-2.0 >= 2.8.0) # Latest is 2.8.4 $ wget http://ftp.gnome.org/pub/GNOME/sources/glibmm/2.8/glibmm-2.8.1.tar.gz $ tar xvfz glibmm-2.8.1.tar.gz $ cd glibmm-2.8.1 $ ./configure --prefix=/usr $ make $ make install # New Requirements for gtk 2.8.8 $ wget ftp://ftp.gtk.org/pub/gtk/v2.8/dependencies/cairo-1.0.2.tar.gz $ tar xvfz cairo-1.0.2.tar.gz $ cd cairo-1.0.2 $ ./configure --prefix=/usr $ make $ make install # Pango not found. Pango built with Cairo support is required $ cd /src/pango-1.10.2 $ ./configure --prefix=/usr --with-cairo=YES $ make $ make install $wget ftp://ftp.gtk.org/pub/gtk/v2.8/gtk+-2.8.8.tar.gz $tar xvfz gtk+-2.8.8.tar.gz $cd gtk+-2.8.8 # Requirements (glib-2.0 >= 2.7.1 atk >= 1.0.1 pango >= 1.9.0 cairo >= 0.9.2) $ ./configure --prefix=/usr $ make $ make install # gtkmm Latest is 2.8.2 $ wget http://ftp.gnome.org/pub/GNOME/sources/gtkmm/2.8/gtkmm-2.8.2.tar.gz tar xvfz gtkmm-2.8.2.tar.gz cd gtkmm-2.8.2 ./configure --prefix=/usr # Note, this takes by far the longest to compile make make install

SideNote: This was a maze to get the right dependancies for the right products. It took many hours.

And around the merry-go-round we go again.

cd /src/mysql-workbench-1.0.1/mysql-gui-common $ ./configure --enable-grt --enable-canvas $ make $ make install $ cd ../mysql-workbench/ $ ./configure $ make /usr/lib/python2.3/config/libpython2.3.a(posixmodule.o)(.text+0x3c5a): In function `posix_tmpnam': : warning: the use of `tmpnam_r' is dangerous, better use `mkstemp' /usr/lib/python2.3/config/libpython2.3.a(posixmodule.o)(.text+0x3b94): In function `posix_tempnam': : warning: the use of `tempnam' is dangerous, better use `mkstemp' ../../../mysql-gui-common/library_gc/source/libgcanvas.a(myx_gc_gl_helper.o)(.text+0x1b5): In function `getProcAddress(char const*)': /src/mysql-workbench-1.0.1/mysql-gui-common/library_gc/source/myx_gc_gl_helper.cpp:264: undefined reference to `glXGetProcAddress' collect2: ld returned 1 exit status make[3]: *** [mysql-workbench-bin] Error 1 make[3]: Leaving directory `/src/mysql-workbench-1.0.1/mysql-workbench/source/linux' make[2]: *** [all] Error 2 make[2]: Leaving directory `/src/mysql-workbench-1.0.1/mysql-workbench/source/linux' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/src/mysql-workbench-1.0.1/mysql-workbench/source' make: *** [all-recursive] Error 1

Same result, so after another 4 or 5 hours, I still have nothing.

Update
Some more research (I really should devote my time to other pursuits). An official comment from NVidia Corporation on their forums. View Thread

This is not a NVIDIA bug, glXGetProcAddress() is part of GLX 1.4, which the NVIDIA Linux/UNIX graphics driver doesn’t claim to support (nor do I think was GLX 1.4 ever finalized); you should always use glXGetProcAddressARB().

ronald · January 30, 2006

I had a friend go woot in an IM message today, and my response was something like. Yes, haven’t been to woot.com for a while, which lead to statement “I didn’t know there was a site”.

Well yes folks, there is a www.woot.com, and it’s way cool. Of course you can go there, and unless you read more then the first page you might wonder what all the fuss is about. I’ve taken the liberty of stealing some content from they FAQ.

What is Woot?
Woot.com is an online store and community that focuses on selling cool stuff cheap. It started as an employee-store slash market-testing type of place for an electronics distributor, but it’s taken on a life of its own.
I see only one item, do you sell anything else?
No. We sell one item per day until it is sold out or until 11:59pm central time when it is replaced (see next entry for details). However, each item we sell is in stock and typically ships within 2-3 business days.
What is the schedule for new items?
The short answer: we offer a new item every single day. The details: a new product is released every morning at 12am central time, seven days a week.

You can read more at What is Woot?

So what kind of stuff do they sell. Well you can look at the product archives here. Anything from a Creative Labs USB Bluetooth Adaptor to a Western Digital Caviar 250GB Hard Drive to a InFocus Ultra-Thin 61″ DLP HDTV with Blender.

Woot!

ronald · January 30, 2006

I read many years ago now Maverick written by Ricardo Semler. It was a brillant book,that I greatly enjoyed. This book is based on what Ricardo did when he took over his father’s company in Brazil called Semco. His book was on the top sellers list for a very long time.

So what did he do that was so great. “Semco has let his employees set the terms of their employment: hours, wages, even their office technology.” Of course one line can’t describe the book.

I recommended this book to a friend, and then I eventually bought him a copy some months ago. Well, this evening in a meeting I’d found that he actually got around to reading Maverick. He was so impressed he bought 9 copies to give to other business collegues. You can’t get a better response to a good book review then that.

If you have never heard of him, Google has some matches 90,000 matches for Ricardo Semler.

ronald · January 29, 2006

I know of this not-for-profit organisation that is seeking to find the ideal solution for all of their IT needs. As with most organisations, wish lists are long, and in this case, the pockets are very shallow.

Now there is a certain amount of history, and politics (all organisations have this), so I’ll just focus on the facts at hand. A new system is sought, the budget is limited, there is no IT manager for the organisation, there are no detailed user or functional requirements for product comparision. So what do you do.

I’m not going to be involved in the process in any way, so what advice in guidelines could I give that would enable the parties to at least consider. Remembering that the Organisation has no IT manager, or internal experienced IT professional to advise. (This is of course the most dangerous position, the worst business critical IT decisions are made by non-IT people simply because they are putting the business needs first (that’s their job), but if your organisation is totally dependent on IT, or nearly totally dependent on IT (it could survive a short time, but you would need a lot more staff) how can the best views be presented without an IT expert, even totally independent from the organisation.)

Here is the quick guide.

1. There is an existing operational (but not fully functionality, obviously the reason for moving) system. What are the top 10 points of functionality that you business currently uses and gains most benefit from (also considering cost savings in money and staff resources).

2. What are the top 10 points of functionality the current system does not currently fully provide (obviously for moving). Be specific.

3. Given the case of globalisation, overseas products will be considered. The following are the Internationalisation (I18N) points the system must have (like the permenent members of the UN Security Council), if one fails the product fails regardless.

Must be able to handle input and display of dates in local format
Must operate in the Timezone of the business (for off-site managed options)
Must provide correct currency support
Must provide english (if the product is developed in a foreign langauge)
Must provide correct Financial Year management
Must provide correct local taxation requirements
Must provide local integrations, for example to payment gateways, shipping providers etc.

So what you have from here are 7 must have points (one fails, end of evaluation), and then 20 points next on the list which would form a percentage of 100% if all 20 are met. Obviously using the existing system as a guide, the percentage would be 50%, 10 points it has, and 10 it doesn’t have. Simple.

What is an acceptable fit for the organisation, 50%, 75%, 80%. If you were purchasing an off the shelf product, I would think 80% would be a suitable go-live indicator. Not idea, but sufficient for survival. Doing this evaluation up front is a necessary step of comparing apples with apples.
No product will make 100%, the next part of the evaluation after getting these initial percentages is to look at the cost (in dollars and time) that it would take to move each product to the minimum acceptable fit, in my example 80%. Of course you may want to consider the cost to move to 95%.

Lastly, your organisation must consult in this case an external IT party. Somebody/some organisation which will look at the feature requirements (which are very limited), cost/benefit analysis and provide a best recommendation for the business, a decision made by mind, and not a decision made by heart.

So in summary.

Determine your minumum requirements
Determine the minimum acceptable percentage fit
Determine the cost (time and money) for each product to meet minimum acceptable percentage
Consult an independent person/body that can provide professional advice

Only at this point can you make a better informed decision. Will it be the right decision? Well that’s another question.

ronald · January 29, 2006

Well this is low. I’ve just been spammed on my Wiki. And it was cunning, I just found it by accident. An enterprising hacker embedded into my Home Page hidden links that were not visible via normal page view, but ultimately would be via a search bot or some other means.

Even better the pricks even deleted content on one page. Here is an example diff of pages. I even posted a few days ago What makes your blood boil? and that was just an article on a news site with misleading dated information. I could see the opportunity for more blood boiling articles, but it doesn’t solve the problem. The problem is, I don’t know how to solve the problem.

So what can you do other then clean up this hacker mess, and put in checks to find this, and then continually revise these checks. What bodies can you complain to about URL’s listed, how can you get these removed from the web. That’s what I really want to know.

MediaWiki, the blog software I use did provide a number of manual tools to help identify and correct the information, but only in response to my accidental discovery. I was able to review a History of my Home Page, and then I could drill down to all User Contributions. But it’s a manual process to cleanup. Needless to say I’ll be developing something to more easily identify this and provide. You can also use Block user to stop IP addresses. I’ll also be adding them to my firewall rules.

For those that use Wiki’s or Blog’s, please let me know your software used, and I’ll endevour over time to perhaps provide tips for these products.

As per a request by a MySQL user, I opened my Blog to allow unregistered user comments. I did this reluctently as I wanted that ‘confirmed opt-in’ step, of a user registration, however I had faith in the community. Well I’ve since turned that off as well. I’ve had SPAM posted to my blog, and most recently a post in which I wanted to respond to, but as the information supplied for email as clearly invalid I could not.

So the penalty is users wishing to make comments have to register, it’s quite painless, however the downside is as soon as you enter an email on a website what’s going to happen to it. And this could happen unwillingly. While the Blog owner as no intentions of using registered emails, a flaw in software could expose information for unlawful access.

I’ve implemented a solution to this, and I’m trialling another. The first is I have a virtual domain, not just a virtual email. You have all heard about getting a free email account at a HotMail or Yahoo or etc, and then for subscriptions you use this email, therefore protecting your own identity email from obvious spam. Well I go one step further. I use a whole domain (in this case myvirtualemail.biz), so it costs ~USD$10 per year, big deal, but what I do is I create a new alias for everything I subscribe to. I then have total control of where I send the email, and what I want to do with it. I can also track if I get any spam, which email alias it was addressed to, and then I have a greater granularity of source of the problem. I can just simply trash the alias, therefore no more SPAM in my inbox. The problem is, the SPAM still comes, it takes network traffic, bandwidth and CPU, it gets rejected and then takes even more network traffic and bandwidth.

Now you have me started on a completely different topic of Authenticated Mail. Email is long since been poisoned, and it should be left to die, and out of the ashes a new Phoenix . Cut out the source of SPAM by ensuring the sender is Authenticated. I guess a topic for another time, but it’s the impossibility of moving people off email that makes it enviable.

And the other trial, I’m using GMail to do my filtering for me. I’ve converted my default DNS administrator email (which gets a lot of spam as it’s public on domain registrars), to an alias that redirects to a gmail account, and then from this I forward filtered mail to a POP account. With gmails strict policy on attachments, this approach may not be ideal for the end user, but for this purpose it’s ideal. Still testing this idea, but it’s looking good so far. BTW, if you want a free gmail account, let me know and I’ll send an invitation.

If only I have and enterprising billionaire to fund some of my ideas, I’d really like to be one person that makes a difference, and my difference is to rid us of obvious stuff that destroys our time. And don’t get me started on Viruses. Simple solution, If the Windows OS was read-only, and there was some authentication process for software installation, we could probably rid the world of viruses. I must admit, the VMWare player running a virtual machine solves the problem as well.

Update

Some info on Setting user rights in MediaWiki
in WIKI_HOME/LocalSettings.php I added
$wgWhitelistEdit = true; $wgShowIPinHeader = false; # For non-logged in users $wgSysopUserBans = false; # Allow sysops to ban logged-in users $wgSysopRangeBans = false; # Allow sysops to ban IP ranges

Other references: Wikipedia:Vandalism, Counter Vandalism Unit

ronald · January 29, 2006

I’m sure you are all aware by now of Mike Hillyer’s MySQL Sakila Sample Database that will be launched at the MySQL Conference. We now have an official MySQL Forum for this as well.

As part of leveraging this existing database, and using this for the basis of my MySQL Conference presentation on MySQL for Oracle Developers, I’ve released the first version of my MySQL Sakila Sample Application at http://sakila.arabx.com.au which I would very much like some feedback on. Please use the official MySQL Forum for any comments, suggestions or complaints. Please Note I am still very much in the planning and design phase.

We also have an Unofficial Wiki that describes a little more of the concept and purpose the Sample Application, and a call for others to get onboard to design and develop their own versions in varying languages.

So the sample application, what does this showcase? For now, work has been on the presentation of data, as we finalise the schema and data. In addition the application has been designed to be more self documenting describing via the top menu options the functions available, and business logic considerations, specific MySQL features and a schema to show the underlying tables in question. Look at Admin or Film for an initial example.

Here is a quick list of the functions of the MySQL Sakila Sample Application. For now there is not user authentication so it’s open for all to view.

Home
Customers
- Index (NF), Search/List, Add, View (NF)
Rentals
- Index(NF), New, Search (NF), Return(NF), Overdue(NF), Out(NF)
Film
- Index, Movie List, Actor List, Categories, Languages
Reports
- Index (NF), Top Film Rentals, Top Customer Rentals
Admin
- Index, Staff, Stores, Countries, Cities

(NF) – Not Functional – Please note, as the data hasn’t been finalised, some of the data is my own patch just to display functionality.
More information on what’s available is in the Admin|Documentation page.

ronald · January 28, 2006

Just released at the MySQL Forums yesterday an updated source version of MySQL Workbench for Linux available at ftp://ftp.mysql.com/pub/mysql/download/mysql-workbench-1.0.1.tar.gz.

So can Version 1.0.1 compile when I had no success with compiling 1.0.0?

$ su - $ cd /src $ wget ftp://ftp.mysql.com/pub/mysql/download/mysql-workbench-1.0.1.tar.gz $ tar xvfz mysql-workbench-1.0.1.tar.gz $ cd mysql-workbench-1.0.1 $ cd mysql-gui-common $ ./configure --enable-grt --enable-canvas $ make MySQLGRT/MGRTValueTree.cc:255: instantiated from here /usr/include/sigc++-2.0/sigc++/adaptors/bound_argument.h:158: error: 'const class sigc::bound_argument >&>' has no member named 'visit' make[3]: *** [MGRTValueTree.o] Error 1 make[3]: Leaving directory `/src/mysql-workbench-1.0.1/mysql-gui-common/source/linux' make[2]: *** [all-recursive] Error 1 make[2]: Leaving directory `/src/mysql-workbench-1.0.1/mysql-gui-common/source' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/src/mysql-workbench-1.0.1/mysql-gui-common' make: *** [all] Error 2

Arrrrrggggghhhhh!. What do I have to do to get this beast to compile. I am running the most current libsigc++ of 2.0.17, available for download at http://ftp.gnome.org/pub/GNOME/sources/libsigc++/2.0/

The job made easier this time by the src archive containing the all important README that provides both dependancies and required additional configuration arguments. However it only stated libsigc++ 2.0 which is what I have.


more /src/mysql-workbench-1.0.1/mysql-workbench/README.linux

Compiling Workbench from Scratch
================================

Required Software:
------------------

Much of the requirements are common for all GUI tools shipped by MySQL (such as
Administrator and Query Browser). Instead of building everything by hand, you
should try using the packages supplied by your distribution, preferably using
some tool like apt-get, smart or even yum. Do not forget that you need the -devel
(or -dev) version of each pacakge as well as the main one.

- gtkmm-2.4 or newer and it's dependencies, such as:
        - glibmm-2.4
        - libsigc++-2.0
        - gtk-2.4 (or whatever is required by gtkmm)
- libpcre-5
- MySQL client libraries and headers for MySQL >= 5.0
- OpenGL libraries and headers
- lua 5 (www.lua.org)

Building:
---------

- unpack workbench
- cd mysql-gui-common
- ./configure --enable-grt --enable-canvas
- make
- make install
- cd ../mysql-workbench
- ./configure
- make
- make install

I’ve logged as Bug #16880. I understand there are many variables in different Linux Operating Systems, and libraries, however if I meet the minimum requirements, I have no other options. Perhaps some specifics of what exact versions of required libraries were used to compile in an internal environment would be helpful. Are programs compiled on a generic OS distro and not a developers machine, so it can be reproduced and is testable.

Updated
In public response to pablo’s comment (click on comments to see, which I think is unqualified, please give more details), MySQL AB do read this, I have had positive comments from MySQL AB staff, and they have been proactive since I’ve raised this an earlier issues. In the space of some 4 hours I’ve had a confirmation and verification of my listed Bug, so in this case, response is excellent. Previously with 1.0.0 I was frustrated in a number of areas, first I’ve been waiting probably 8 months to get my hands on this product, at present I don’t have an alternative Open Source Linux Modelling tool supporting MySQL 5. Second, there seemed to be slow progress or perhaps insufficient feedback of progress from MySQL AB, but unless we raise our voice how will they know.

Again in defense of MySQL AB (of which I am not associated with), this is an developing Open Source Product and what we as the community ultimately get out of the product it what we collectively put in. If for example we just say it’s buggy, but don’t report it, seek advice, try to use our best abilities to reproduce and assist in providing a better product. Unfortunately the language and GUI requirements are skills I am not confident in. If they were I would be more involved in coding to correct this problem to move forward.

Another reason for presenting my information is to seek advice if others are experiencing these issues, to raise awareness to MySQL AB, who are an organisation with limited resources yet continue to provide what I consider is an excellent product for it’s current marketplace targets. I’m keen to see a working product because then I can provide 15 years of database modelling experience in large systems design in the review and testing of the product, and hopefully contribute in identifying useful and practical functionality to continully improve the product.

Again, speaking on MySQL AB’s behalf, the introduction of GUI products released last year on which the MySQL Workbench are built on, are only new products and new products take time to mature. Of course I’ve love a fully working product, but I’m not providing finances to millions or billions in R&D for software development.

I think it’s important that we all embrace the ideals of Open Source. Sure they are not perfect at this time, but this is not the ideal of one company to change the world of computing and computer software, but to all individuals to have an opportunity to contribute.

Pablo, I would be most happy to take this discussion offline and ask some more questions regarding the concerns you have made in your post, and try to help better understand your concerns, but without a suitable valid email address I’m unable to respond.

ronald · January 27, 2006

As requested by Frank, here are the working parts of my earlier Downgrading a MySQL schema from 5 to 4 article.

The Problem

To recap, I received a MySQL Version 5.0 schema via a sql file, however I was unable to upgrade from MySQL 4.0 to MySQL 5.0 on my old RedHat 7.3 production server. As an interim solution, I still wanted the schema and data to allow for initial development (without the 5 specific features including Views,Triggers and Procedures/Functions). However the MySQL 5.0 SQL file would not run in MySQL 4.0.

Sample

Here is a small subset of the MySQL Sakila Sample Database schema to demonstrate the problem.


DROP SCHEMA IF EXISTS sakila;
CREATE SCHEMA sakila;
USE sakila;
--
-- Table structure for table `actor`
--
CREATE TABLE actor (
  actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(45) NOT NULL,
  last_name VARCHAR(45) NOT NULL,
  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  PRIMARY KEY  (actor_id),
  KEY idx_actor_last_name (last_name)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Conversion

The following commands produced the output valid for a MySQL 4.0 database.

$ dos2unix sakila-schema-0.2.sql $ cat sakila-schema-0.2.sql | sed -f mysql5to4.sed >sakila-schema-0.2.mysql4

NOTE: These commands are Linux Commands. There are probably Windows compatible commands however without Windows around to test this, I’m not going to offer any advice here. Anybody with any experience here please advise.
If you don’t have Linux, then get it. Sound to hard, well start with a Live CD, for example Knoppix or Ubuntu. These will allow you to boot of CD and it will never affect your Windows machine. If you don’t have broadband or a CD Burner, look at buying a computer magazine. You will find quite regularly options or Live CD’s. Of if you can’t afford that, Ubuntu – ShipIt will ship you the CD for free.
Even better then this, you can now use VMWare Player and have a working Linux environment running in parallel with your Windows at the same time, and it will not affect Windows in any way. You just need the VMWare Player, then a suitable Virtual machine. If there is a single person with an excuse why they can’t experiment with Linux, your not embracing Open Source. Start today, start right now, I’ve given you the links.

Results


DROP DATABASE IF EXISTS sakila;
CREATE DATABASE sakila;
USE sakila;
--
-- Table structure for table `actor`
--
CREATE TABLE actor (
  actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(45) NOT NULL,
  last_name VARCHAR(45) NOT NULL,
  last_update TIMESTAMP NOT NULL,
  active TINYINT NOT NULL DEFAULT TRUE,
  PRIMARY KEY  (actor_id),
  KEY idx_actor_last_name (last_name)
)TYPE=InnoDB;

The Code

Ops, almost forgot the important bit. Note, the syntax in this file is specific, characters and spaces are significant. My advice if you have no idea about ex regular expresssions and syntax, don’t touch.

The contents of mysql5to4.sed
s/CREATE SCHEMA/CREATE DATABASE/ s/DROP SCHEMA/DROP DATABASE/ s/ DEFAULT CHARSET=utf8;$/;/ s/ENGINE=/TYPE=/ s/ ON UPDATE CURRENT_TIMESTAMP// s/ DEFAULT CURRENT_TIMESTAMP// s/BOOL /TINYINT / s/BOOLEAN /TINYINT / /^DELIMITER/d

Further Work

These workarounds work on a generated mysqldump file. For example, I bank on implied standards of UPPERCASE in the syntax of commands.
I don’t cater for situations where DEFAULT CURRENT_TIMESTAMP is defined on a TIMESTAMP column which is not the first listed in the database table. This could be catered for with some more shell scripting with awk (More about awk 1, 2, 3)
I don’t cater for syntax situations not currently described in the Sakila Sample Database.
For now, I’ve manually removed the TRIGGERS, VIEWS, PROCEDURES and FUNCTIONS, however there would be good success via shell scripting to also archieve this.

MySQL Alternative

Morgan provided a suitable syntax for mysqldump in his Compatibility between MySQL Versions article, however I found this not to work completely successful. Here are my findings.

Syntax

mysqldump --compatible=name

Example

Using 5.1.4-alpha-max under Linux (not glib23)
$ mysqldump -P3307 -hlocalhost.localdomain -uroot -d --compatible=mysql40 sakila

The resultant file had the following lines, I’ve just cut and paste to show issues.
Using 4.0.13-standard.

mysql> DELIMITER ;; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER' at line 1

Using 4.1.10a-standard
mysql> DELIMITER ; ERROR: DELIMITER must be followed by a 'delimiter' character or string

This invalid handling of DELIMITER has a downstream affect when it gets to triggers.
mysql> /*!50003 CREATE TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW BEGIN -> INSERT INTO film_text (film_id, title, description) -> VALUES (new.film_id, new.title, new.description); Query OK, 0 rows affected (0.00 sec) mysql> END */;; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'END */' at line 1

I Need to get around to checking the Bug Database to see if these need to be logged.

ronald · January 26, 2006

It’s appalling that in this day of technological advancements and communication, the excuse for publishing dated information just doesn’t fly. 50 or 100 years ago you could be excused for writing something that was 6 months out of date, yet this article “Which Database Is Right For You?” Dated (2006-01-05) states:

So what doesn’t MySQL support? Today, MySQL doesn’t support views (‘virtual’ tables made from other tables), stored procedures (small programs that can be stored in the database) or triggers (actions that the database can be told to do automatically when certain things happen). However, many of these features are promised in future versions.

Well as I finished the article, looking forward to the contact details to notify this “writer” of their dated information, I was beaten to the punch.

Editor’s Note – Correction: We were contacted by Jay Pipes,Community Relations Manager, North America MySQL, Inc. who says that “MySQL 5 (the current stable release of MySQL) does indeed support stored procedures, views, triggers, functions, and many more features.”

Why can’t they put that note in the MySQL section of the article, it can easily get missed at the bottom.

It was only recently that I went out to by a magazine on referral to read an in depth article “The Usual Suspects”. I was most disappointed in it’s dated and in someways inaccurate content. You can read my comments at Review of Database Magazine Article – “The Usual Suspects”

ronald · January 26, 2006

One piece of SQL functionality that doesn’t appear to have any consistency or an ANSI SQL Standard is the management of system generated sequential numbers, used for example in suggorate keys.

MySQL uses AUTO_INCREMENT which serves the purposes adequately, however in my documenting of differences with Oracle in my upcoming MySQL Conference presentation “MySQL for Oracle Developers” there a number of key differences with Oracle’s SEQUENCE usage.

MySQL AUTO_INCREMENT to Oracle SEQUENCE Differences

AUTO_INCREMENT is limited to one column per table
AUTO_INCREMENT must be assigned to a specific table.column (not allowing multi table use)
AUTO_INCREMENT is INSERTed as a not specified column, or a value of NULL

The MaxDB Reserved Words list includes SEQUENCE for the CREATE SEQUENCE however I’ve never used MaxDB. Other popular open source products such as PostgreSQL and Ingres use sequences. Refer to the references section for more details.

Usage

The following provides an example sytax usage within MySQL and Oracle.

MySQL


CREATE TABLE Movie(
id           INT NOT NULL AUTO_INCREMENT,
name     VARCHAR(60) NOT NULL,
released YEAR NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO Movie (name,released) VALUES ('Gladiator',2000); INSERT INTO Movie (id,name,released) VALUES (NULL,'The Bourne Identity',1998);

Oracle


CREATE TABLE Movie(
id          INT NOT NULL,
name     VARCHAR2(60) NOT NULL,
released INT NOT NULL,
PRIMARY KEY (id)
);
CREATE SEQUENCE MovieSeq;

INSERT INTO Movie (id,name,released) VALUES (MovieSeq.NEXTVAL,'Gladiator',2000);

You can within Oracle use a Before Insert trigger to simulate handling of the MySQL Insert syntax. Note: Within Oracle you will require a SEQUENCE per table and a TRIGGER per table. Oracle supports multiple triggers of the same type per table (not sure if MySQL supports this).


CREATE OR REPLACE TRIGGER BRI_MOVIE_TRG
BEFORE INSERT ON Movie
FOR EACH ROW
BEGIN
  SELECT MovieSeq.NEXTVAL INTO :new.id FROM DUAL;
END BRI_MOVIE_TRG;
.
RUN;

INSERT INTO Movie (name,released) VALUES ('The Lion King',1994);

Oracle’s syntax uses the sequence name with .NEXTVAL or .CURVAL.

Future Directions

I would like to see a SEQUENCE implementation with MySQL (whether official or unofficial). I’m sure some enterprising person in the community already has one. Database abstraction layer systems would also most likely have implementations. I liked the PostgreSQL Syntax for ease of use with the following commands.

NEXTVAL(‘sequence’);
CURRVAL(‘sequence’);
SETVAL(‘sequence’,value);

Wanting something and doing something about it are two different things, so here is what I wiped together to demonstrate a possible implementation. It needs a lot more work in appropiate error handling. transaction management, testing and performance analysis, however it shows the options of one possible implementation.

currval


DROP TABLE IF EXISTS sequence;
CREATE TABLE sequence (
name              VARCHAR(50) NOT NULL,
current_value INT NOT NULL,
increment       INT NOT NULL DEFAULT 1,
PRIMARY KEY (name)
) ENGINE=InnoDB;
INSERT INTO sequence VALUES ('MovieSeq',3,5);
DROP FUNCTION IF EXISTS currval;
DELIMITER $
CREATE FUNCTION currval (seq_name VARCHAR(50))
RETURNS INTEGER
CONTAINS SQL
BEGIN
  DECLARE value INTEGER;
  SET value = 0;
  SELECT current_value INTO value
  FROM sequence
  WHERE name = seq_name;
  RETURN value;
END$
DELIMITER ;

Some Testing:


mysql> SELECT currval('MovieSeq');
+---------------------+
| currval('MovieSeq') |
+---------------------+
|                   3 |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT currval('x');
+--------------+
| currval('x') |
+--------------+
|            0 |
+--------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+------------------+
| Level   | Code | Message          |
+---------+------+------------------+
| Warning | 1329 | No data to FETCH |
+---------+------+------------------+
1 row in set (0.00 sec)

What was interesting was I originally used a cursor, as below, but the results for passing an invalid argument (basic boundary testing), returned a SQL error while the above implementation returned a more manageable warning.


 DECLARE c CURSOR FOR
    SELECT current_value FROM sequence
    WHERE name = seq_name;
  OPEN c;
  FETCH c INTO value;

mysql> select currval('x'); ERROR 1329 (02000): No data to FETCH

Indeed the Apache Object Relational Bridge Sequence Manager section shows a very cool syntax for MSSQL.
UPDATE TABLE SET @MAX_KEY = MAX_KEY = MAX_KEY + 1
UPDATE table SET var = column = value which effectively allows you to eliminated the need for a seperate UPDATE and SELECT for this type of operation.

nextval


DROP FUNCTION IF EXISTS nextval;
DELIMITER $
CREATE FUNCTION nextval (seq_name VARCHAR(50))
RETURNS INTEGER
CONTAINS SQL
BEGIN
   UPDATE sequence
   SET          current_value = current_value + increment
   WHERE name = seq_name;
   RETURN currval(seq_name);
END$
DELIMITER ;


mysql> select nextval('MovieSeq');
+---------------------+
| nextval('MovieSeq') |
+---------------------+
|                  15 |
+---------------------+
1 row in set (0.09 sec)

mysql> select nextval('MovieSeq');
+---------------------+
| nextval('MovieSeq') |
+---------------------+
|                  20 |
+---------------------+
1 row in set (0.01 sec)

mysql> select nextval('MovieSeq');
+---------------------+
| nextval('MovieSeq') |
+---------------------+
|                  25 |
+---------------------+
1 row in set (0.00 sec)

setval


DROP FUNCTION IF EXISTS setval;
DELIMITER $
CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER)
RETURNS INTEGER
CONTAINS SQL
BEGIN
   UPDATE sequence
   SET          current_value = value
   WHERE name = seq_name;
   RETURN currval(seq_name);
END$
DELIMITER ;


mysql> select setval('MovieSeq',150);
+------------------------+
| setval('MovieSeq',150) |
+------------------------+
|                    150 |
+------------------------+
1 row in set (0.06 sec)

mysql> select curval('MovieSeq');
+---------------------+
| currval('MovieSeq') |
+---------------------+
|                 150 |
+---------------------+
1 row in set (0.00 sec)

mysql> select nextval('MovieSeq');
+---------------------+
| nextval('MovieSeq') |
+---------------------+
|                 155 |
+---------------------+
1 row in set (0.00 sec)

Other References

Ingres CREATE SEQUENCE Page 105.
PostgreSQL CREATE SEQUENCE
Apache Object/Relational Bridge – Sequence Manager – Subproject of The Apache DB Project
MySQL CREATE PROCEDURE

ronald · January 26, 2006

In my continuing effort of finding a suitable complete Windows environment under Linux for those small occasions when you just can’t ditch it, I’ve tried Win4Lin. You can download 2 week evaluation copy from the website.

$ su - $ wget http://www.win4lin.com/administrator/downloads/Win4LinPro-6.2.5-01.i386.rpm #wget http://www.win4lin.com/index.php?option=com_remository&Itemid=76&func=download&filecatid=3 $ rpm -ivh Win4LinPro-6.2.5-01.i386.rpm

Preparing... ########################################### [100%] 1:Win4LinPro ########################################### [100%] 14 day(s) left in evaluation period. Creating images... done. ERROR: Module kqemu does not exist in /proc/modules Host architecture: i686 KQEMU package: /opt/win4linpro/etc/kqemu.tar.gz NOTE: KQEMU is Copyright 2005 by Fabrice Bellard. Win4Lin, Inc. is an authorized distributor of this code. Building KQEMU module in /tmp/.build_kqemu-24886 Please see the file /tmp/build-kqemu.log in case of error Installing KQEMU module in /lib/modules/2.6.9-22.0.1.ELsmp/misc Loading KQEMU module Starting Win4Lin Pro: [ OK ]

Need to load a Windows XP CD. Note: It must have SP1 or SP2. An older XP CD did not work.

$ loadwinproCD 13 day(s) left in evaluation period. You may use Win4Lin Pro without entering a license code until the evaluation period ends. If you already have a full license code, you may enter it now. If you do not have a license code, you may enter it at any time in the future by running (as root): /opt/win4linpro/bin/ask_license.sh Would you like to enter your license code now? (y/n) n Checking CDROM... Media found: Windows XP Professional (Service Pack 1) Loading installation files... please wait... ................................................. Loading CDROM... please wait... .................................................. Windows CDROM load complete. $ exit # must not be be root to continue

$ installwinpro Selected winxppro to install by default installwinpro: installation details: Target directory: /home/rbradfor/winpro Windows version: winxppro Guest image size: 4G mergepro-gowimg: created /home/rbradfor/winpro/GUEST.IMG, size=4G Enabled KQEMU acceleration (mergepro-core) Partitioning guest image... Guest image partitioning complete. Enabled KQEMU acceleration (mergepro-core) Formatting guest image... QuickFormatting (only flushing metadata) Format complete. 4,293,563,392 bytes total disk space 4,293,563,392 bytes available on disk 4,096 bytes in each allocation unit. 1,048,233 allocation units available on disk. Volume Serial Number is 0D28-140E Guest image format complete. installwinpro: launching guest installation... Enabled KQEMU acceleration mergepro-gfx: LANG not found, defaulting to: en-us

This then provided the normal windows installation in a window, the only requirement was a serial number, so they have definitely streamlined the Windows installation. Then to run windows.

$ winpro

I did have an installation problem, where I got a CD error of not being able to access a file. Now given that Win4Lin precopied the entire CD, I’m not sure what to make of it, I had to skip the file, and it did not appear to affect the installation. I’ve got a screenshot to upload sometime.

So far, all I’ve really done is run it, I found that it was very slow. What was great, that on the very clean desktop the only icon was a mapping the Linux user home directory. I tried to install Photoshop CS, about the only thing I really want under Windows until I become better with Gimp. Problem was it promptly told me I needed 7GB of space, which I didn’t have. Not sure if I can easily add additional diskspace. Guess I’ll have to RTFM.

ronald · January 25, 2006

The Brisbane XP Group met yesterday for a presentation by Dr Paul King of Asert on the book Sustainable Software Development : An Agile Perspective.

I found it a good time to get a collective opinion and review of the techniques and methods we are moving towards in Software Development. Indeed one key point better describing Pair Programming has been added to my upcoming conference presentation Overcoming the Challenges of Establishing Service and Support Channels. I’m hoping Paul makes his notes available as a review of this book, that I will also mention in my presentation.

In Review, this is some of the key points I got from this presentation.

Software gradually degrades over time, and will become a maintenance nightmare
Successful software will be changed again and again
The IT industry has a problem historically with credibility

So the goal is to move towards Sustainability. Some of the points mentioned by Paul were:

Continual refinement
A working product at all times (not just working software)
Value Defect Prevention over Defect Detection
Additional investment and emphasis on design

On point I struggle with is Pair Programming. I don’t struggle with the concept, it’s great and really works. The struggle is selling Pair Programming as a core XP Principle. Some good points of discussion lead to a better angle.

Pair Programming – should be de-emphasised as a key point
By selling Defect Prevention and using Continuous Code Reviews as one method of implementing this
Continuous Code Review are achieved with Pair Programming

Much easier. The key point is management understands the term Code Reviews, and if you can show the effect of Defect Prevention on support costs, using Pair Programming, Refactoring and other techniques, your sales pitch will be easier.

Also for reference, the book Software Craftsmanship: The New Imperative was mentioned as a book with similar ideals. A third recommended reading book that was mentioned at the meeting was The Pragmatic Programmer: From Journeyman to Master.

ronald · January 24, 2006

Well, sometimes you just have to have some fun. Not sure of a pratical use yet. Check out these links.

http://c6.org/toogle/index.php?phrase=Linux+penguin
http://c6.org/toogle/index.php?phrase=marvin
http://c6.org/toogle/index.php?phrase=sunset
http://c6.org/toogle/index.php?phrase=daffy+duck
http://c6.org/toogle/index.php?phrase=polar+bear
http://c6.org/toogle/index.php?phrase=flower
http://c6.org/toogle/index.php?phrase=tulip
http://c6.org/toogle/index.php?phrase=arabx.com.au

ronald · January 24, 2006

Shamelessly stolen from REALITY 2.0 by Doc Searls.
(You know the web, page here today, gone tomorrow. If I just have the link, you may not be able to appreciate the image in the future)

ronald · January 23, 2006

In the past week I’ve added some more reading to my list, extended the library and hammered the credit card. Some of this has been in part to keep abreast of things as a result of being quizzed in job interviews.

Analysis Patterns by Martin Fowler and Oracle Database 10g Linux Administration

ronald · January 22, 2006

Well, learnt a new one today. After a recommendation from a collegue about trying Parellels as a VM tool to run on Linux enabling some more virtual OS’s. (a.k.a. Like VMWare), I eagerly downloaded the rpm and installed. Being caught a bit lately I took the liberty of reviewing the Parallels Workstation 2.0 Installation Guide For Linux as part of the process. Pre-reqs this, ok, run this ok, supports RHEL4 that’s good. Then run the following, accept licence and continue. Fine.

Parallels-config

So I flip to the next page of the manual (pg 13), and there in big letters is:

Troubleshooting
If you receive “Can not configure!” or “Can not compile!” message (note that you should have kernel sources installed), you can view log file and try to fix problem yourself. The log file is named /usr/lib/Parallels/comp.log.< log number>.error
If log contains the following sentence: “Your kernel is compiled with PAE (Physical Address Extension)”, it means that currently running kernel supports PAE. Unfortunately the current version of the Parallels Workstation does not support PAE. To fix the problem:

if you have another kernel configuration compiled without PAE support, boot in this kernel.

if not, recompile your current kernel to create another one that does not support PAE. Please note that recompiling kernel may lead to unstable work of your primary system. For instructions on how to do this please refer to your primary OS system administration guide or consult your system administrator.

run the Parallels Workstation configuration script again.

Well it only took 30 secs for the install to fail. Here I am thinking, given the nightmares I’ve had lately compiling and installing software do I even bother looking.

/usr/lib/Parallels/Drivers/drvMain/vmmodule.h:44:3: #error "Your kernel is compiled with PAE (Physical Address Extension)" /usr/lib/Parallels/Drivers/drvMain/vmmodule.h:45:3: #error "The current version of the Parallels Workstation does not support PAE mode." /usr/lib/Parallels/Drivers/drvMain/vmmodule.h:46:3: #error "(Processor type and features -> High Memory Support) - must be not more than 4GB"

Well, made a post on CentOS 4 forums, but I had mixed response with this site.

Anybody know how to recompile a kernel with PAE support? Doesn’t seem a great topic on google.

BTW, in case you missed it PAE is Physical Address Extension.

ronald · January 22, 2006

I’ve never used database columns that embedded defined valid values within the schema definition. Within MySQL there are 2 definitions, ENUM and SET. There are a few reasons why, but first an explanation of these data types.

In summary, using the MySQL Sample Database.
CREATE TABLE film ( film_id INT UNSIGNED NOT NULL AUTO_INCREMENT, ... rating ENUM('G','PG','PG-13','R','NC-17') DEFAULT 'G', special_features SET('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL, PRIMARY KEY (film_id) )ENGINE=InnoDB DEFAULT CHARSET=utf8;

So from this, the following commands allow you to inspect this information via mysql.

DESCRIBE film; SHOW COLUMNS FROM film LIKE 'rating';

With the introduction of the INFORMATION_SCHEMA in MySQL 5, a more traditional method using a valid SELECT statement.

SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='sakila' AND TABLE_NAME='film' AND COLUMN_NAME = 'rating'

This is great, however you have to know a lot of information, the table_name and column_name ok, they are fixed, but now the table_schema information is required to be known by the application.

Why I don’t use these?

Historically, there have been 3 reasons why I’ve never used ENUM.

First, it’s not standard SQL (at least to my knowledge), and historically hasn’t been consistent between Database products
More importantly as data, it’s terrible to maintain easily, as it’s within the table structure
The management of valid values within an application, and the need to manage this data dynamically.

Now, I’m not certain if within MySQL there are any funky ways to manage this type of information. I’d welcome comments on what people do. However from my previous experience, this is my method of implementation.

All information of this type, I refer to as a code. I implement this within a common table, defined as reference_data (based historically on the Oracle cg_ref_codes). Here is a cut down version of my definition for simplicity purposes.

CREATE TABLE reference_data( id INT UNSIGNED NOT NULL AUTO_INCREMENT, type VARCHAR(20) NOT NULL, code VARCHAR(20) NOT NULL, value VARCHAR(100) NOT NULL, PRIMARY KEY (id) )ENGINE=InnoDB DEFAULT CHARSET=utf8;

Of course, I’ve extended this basic structure to include ordering of data per type, default value of a given type, grouping of types (for larger systems) and a status to manage historically codes no longer valid for new data.

Within tables, I define all codes with an extention of _code so as to clearly identify the content of a given column.

So, for this example I would add data such as:

INSERT INTO reference_data(type,code,value) VALUES ('rating','G','General Audience - All Ages Admitted'), ('rating','PG','Parental Guidance Suggested. Some Material May Not Be Suitable For Children'), ('rating','PG-13','Parents Strongly Cautioned. Some Material May Be Inappropriate For Children Under 13'), ('rating','R','Restricted. Under 17 Requires Accompanying Parent or Adult Guardian'), ('rating','NC-17','No One 17 And Under Admitted');

This for me provides the following benefits:

The Data is data, meaning it can be easily modified via normal application and sql functionality
Additional information such as a more general description can be provided and presented to end users
Additional attributes non shown in this example, provides ordering support, and a default value
The same set of data for a given type can be used in multiple tables

Now, the clear downside of this level of functionality is that the data integrity that is managed by ENUM at the database level has now been lost. You need to temper this with the level of access to your database, and you can easily implement via Database Triggers this level of security. However, I think that clear ability to tie the required values for an ENUM column to easy accessibility via an application, and allow for easy management is clearly a strength.

I’m yet to be convinced otherwise.

ronald · January 22, 2006

Got myself some .avi files but didn’t have a player under CenOS 4.2. MPlayer would appear to rate well as a media player for linux.

yum install mplayer yum install lame

# Unfortunately mplayer while registered could not be downloaded, tried the hard way wget http://rpm.greysector.net/yum/4/i386/mplayer-1.0pre7try2-2.i386.rpm rpm -ivh mplayer-1.0pre7try2-2.i386.rpm # lame wasn't needed via the manual process, but just in case #wget http://rpm.greysector.net/yum/4/i386/libmp3lame-3.96.1-4.i386.rpm

Surprisingly, it’s not a GUI interface, but mplayer on the command line is more then adequate.

ronald · January 22, 2006

As part of my next conference presentation Overcoming the Challenges of Establishing Service and Support Channels I’ve been struggling to find with my professional sources, any quality organisations that provide full support for a technology stack, for example a LAMP stack, or a Java Servlet stack.

Restricted to searching via online, I’ve been impressed by what I’ve found at Spike Source www.spikesource.com. An organisation with an experienced CEO, well known in the Java Industry. They certainly have all the buzz words covered in their product information.

Benefits of their SpikeSource Core Stack.

Fully tested and certified
Installs in minutes with integrated installer
Enterprise-class maintenance and support available
Vendor neutral
Horizontally and vertically scalable

SpikeSource offers three prebuilt configurations that can have you up and running in around ten minutes. These configurations comprise the following component choices:

LAMP Stack – for Websites with dynamic database-driven content written using Perl and PHP.
Servlet Stack – for dynamic Websites written using Java-based Web technologies such as servlets.
J2EE Stack – for Web applications that separate Web interface and application logic using Java Servlets and Enterprise JavaBeans.

Supported Platforms. What’s of interest here is RHEL, SuSE as well as Fedora Core 3. In line with for example Oracle software running under Linux.

What’s interesting, is they have MySQL 4.1.14 in their spikesource stack (1.6.2), so they are quite some months behind here. Especially now that MySQL 5 has been available 3 months now. Not only just stack technology, their infrastructure supports a large number of open source products and appears to provide infrastructure via a community to enhance the product offerings within this stack. The Spike Developer Zone Components List provides a long list of products.

Their release notes provide good instructions, in particular what configuration was used in the building of the software. For example, here is the MySQL Release Notes, MySQL Quick Start Guide, MySQL Troubleshooting Guide

They talk about testing, where Core Stack Testing provides more details here.

They also claim to provide VMWare Community Virtual Machine that can be run via the free VM Player on any system without having an effect on an existing system. This is indeed impressive, however it doesn’t seem available. There are many other installations available at the VMWare site.

I’m interested to see what else existing in the marketplace for a fully supported technology stack, rather then support of individual components (e.g. RedHat for Linux, MySQL AB for MySQL, JBoss for a servlet container)

In reading comparisions, there is reference also to Source Labs – www.sourcelabs.com. Anybody that can offer recommendations that I can research would be great.

ronald · January 21, 2006

Well the title got me when I decided to purchase this book “Beyond Java – A glimpse at the Future of Programming Languages”, however perhaps it should have been titled “Why to move from Java to Ruby” as the book for a good portion is an explanation of how Ruby solves the problems that Java has and the direction Java is moving. While the book did describe where Java was, and the future limits and what to look at Beyond Java, the high use of Ruby to describe these overwhelmed the book. In fact, only the last chapter of 20 pages gave an comparison of “Contenders” as the chapter title described other then scant descriptions

Initially I lost count of the number of times information regarding C++ was repeated in the book, and how Java got it’s great penetration from the C++ community. I almost put the book down after the first few chapters, it was highly repetitive.

However, given my increasing interest in Ruby I was able to work though this. I could see a Java developer that has already discard Ruby as a fad to put this book down. In fact, as a Ruby reference it provided some good tips, again strengthening my comment of including Ruby in the title.

Overall an interesting read, however for a small book it could have offered a lot more.

On the same topic, some interesting points in the article The Problems With Java.

ronald · January 20, 2006

Why oh why would you want to do this. Well it my case, I’ve committed to developing a web application using MySQL 5 features, knowing that I had to upgrade my production server from 4.0

Well as part of doing this, I hit a stumbling block. My current production web server runs RedHat 7.3, and even with all the latest rpm updates, it does not have glib 2.3 which is required for MySQL 5. I’m no guru, but trying to upgrade from Redhat 7.3 to 9, to at least get these rpm is not an easy process. I’m not confident to try to compile glib 2.3 and all it’s dependancies on a production server, nor is it possible to recompile MySQL down (I suspect not). All just too many variables. It appears the time has come to scrap it and work with a more current RedHat Enterprise Linux version. Down side, the 25 web sites are not going to be too happy.

Anyway, as an interim to at least move forward as much as possible I dowgraded the provided schema.sql to at least run under 4.0. We all know about new features in 5.0, so this is just really the same list, but it was worthwhile documenting.

Remove views
Remove triggers
Remove DELIMITER syntax
Change ENGINE= to TYPE=
Change DROP SCHEMA to DROP DATABASE
Change CREATE SCHEMA to CREATE DATABASE
Remove DEFAULT CHARSET=utf8 from table definition
Remove DEFAULT TIMESTAMP from TIMESTAMP columns (NOTE: Need to verify the column is the first TIMESTAMP column)
Change BOOLEAN to TINYINT(1)

Update:
Friend and collegue Morgan points out that if I was dumping a schema with 5.1 you could use the syntax mysqldump --compatible=name. Unfortunately in my case the schema was coming from a third party, however now I know this syntax, I can always ask for it.

Update 2:
I know in earlier versions that you could get a MySQL product installation, and also get one compiled with glib23. I just figured that over time the older glib22 were dropped, and were not available with 5.1. It seems domestic blindness has now hit me in computer software. I did review the MySQL 5.1 downloads page, and did not see lower in the page mixed in the 30 to 40 downloads and option to download a glibc22 version. Thanks Arjen for pointing that out.

Domestic Blindness
“The inability to find a common object that is right in front of your face.”

ronald · January 19, 2006

I’m not there yet with compiling MySQL Workbench from the previously released 1.0.0-alpha source for linux. I’d like to think I’m getting closer but not really certain. I’m getting good response to my Bug Report #16604 from MySQL Staff. Seems I work at it during the day, update, and then next morning there is some more info, but still doesn’t cut the cheese, as it just moves me to next error.

So in my last edition, I got an error and installed a lua product (which was the right one), but I didn’t do a configure again, just a compile. I bet that’s a C/C++ 101 lesson. Also as per request, added Java options consistent with my environment. So:

$ cd /src/mysql-workbench-1.0.0/mysql-gui-common/ $ ./configure --enable-grt --enable-canvas --with-java-ldflags="-L/opt/j2sdk1.4.2_10/jre/lib/i386/client/ -ljvm" ./configure: line 5556: ./po/POTFILES.in: No such file or directory

$ make MySQLGRT/MGRTValueTree.cc:255: instantiated from here /usr/include/sigc++-2.0/sigc++/adaptors/bound_argument.h:158: error: 'const class sigc::bound_argument< std ::list< MYX_GRT_VALUE*, std::allocator< MYX_GRT_VALUE*> >&>' has no member named 'visit' make[3]: *** [MGRTValueTree.o] Error 1 make[3]: Leaving directory `/src/mysql-workbench-1.0.0/mysql-gui-common/source/linux' make[2]: *** [all-recursive] Error 1 make[2]: Leaving directory `/src/mysql-workbench-1.0.0/mysql-gui-common/source' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/src/mysql-workbench-1.0.0/mysql-gui-common' make: *** [all] Error 2

There is an error in the configure file at line 5556. Must have missed before, but I doubt, related to the later error.
So still not luck, with another solid C++ error. Some more research.

Google gets me to http://mail.gnome.org/archives/gtkmm-list/2005-November/msg00194.html which repeats the same problem, however no solution. Further reference to confirm the libc++ 2.0 API Docs indicating visit is indeed a member. I have the latest version from http://ftp.gnome.org/pub/GNOME/sources/libsigc++/2.0/ of 2.0.17. No indication of error at Gnome Bug Tracking including using detailed serach. So I’ve exhausted my investigative knowledge here.

The second recommendation from support.

If you want, you can try the latest version in the subversion repository, which should be easier to build. That version doesn’t require Java and have several issues fixed:
svn co http://svn.mysql.com/svnpublic/mysql-gui-common/
svn co http://svn.mysql.com/svnpublic/mysql-workbench/

So, after checking out what’s needed for SubVersion at http://subversion.tigris.org/.

wget http://dag.wieers.com/packages/subversion/subversion-1.2.1-0.1.2.el4.rf.i386.rpm rpm -ivh subversion-1.2.1-0.1.2.el4.rf.i386.rpm svn co http://svn.mysql.com/svnpublic/mysql-gui-common/ svn co http://svn.mysql.com/svnpublic/mysql-workbench/ cd mysql-gui-common ./configure -bash: ./configure: No such file or directory

Oops, seems that file is not in the repository (at least the repository I have access to, so hoping this is built on ).

$ cp /src/mysql-workbench-1.0.0/mysql-gui-common/configure . $ ./configure $ config.status: error: cannot find input file: Makefile.in

$ cp /src/mysql-workbench-1.0.0/mysql-gui-common/Makefile.in . $ ./configure config.status: error: cannot find input file: library/Makefile.in

Ok, there’s a pattern here:

$ find /src/mysql-workbench-1.0.0 -name Makefile.in /src/mysql-workbench-1.0.0/mysql-workbench/res/linux/Makefile.in /src/mysql-workbench-1.0.0/mysql-workbench/res/Makefile.in /src/mysql-workbench-1.0.0/mysql-workbench/images/palette/Makefile.in /src/mysql-workbench-1.0.0/mysql-workbench/images/toolbar/Makefile.in /src/mysql-workbench-1.0.0/mysql-workbench/images/icons/Makefile.in /src/mysql-workbench-1.0.0/mysql-workbench/images/Makefile.in /src/mysql-workbench-1.0.0/mysql-workbench/images/cursors/Makefile.in /src/mysql-workbench-1.0.0/mysql-workbench/source/lua/Makefile.in /src/mysql-workbench-1.0.0/mysql-workbench/source/linux/Makefile.in /src/mysql-workbench-1.0.0/mysql-workbench/source/Makefile.in /src/mysql-workbench-1.0.0/mysql-workbench/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/res/grt/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/res/linux/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/res/po/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/res/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/library_grt/source/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/library_grt/newt/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/library_grt/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/library_gc/source/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/library_gc/ftgl/src/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/library_gc/ftgl/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/library_gc/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/library/source/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/library/tests/test_parse_sql_statemets/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/library/tests/test_mysql_api/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/library/tests/test_xml/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/library/tests/test_detect_charset/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/library/tests/read_international/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/library/tests/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/library/tests/test_translations/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/library/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/images/grt/structs/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/images/grt/icons/db/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/images/grt/icons/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/images/grt/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/images/icons/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/images/icons/png/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/images/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/images/png/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/library_grt_workbench/source/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/library_grt_workbench/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/source/lua/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/source/linux/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/source/grtsh/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/source/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/library_util/source/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/library_util/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/library_sql_parser/source/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/library_sql_parser/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/library_grt_modules/source/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/library_grt_modules/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/library_sql_resultset/source/Makefile.in /src/mysql-workbench-1.0.0/mysql-gui-common/library_sql_resultset/Makefile.in

$ find . -name Makefile.in ./library/Makefile.in ./Makefile.in
Seems there are 43 Makefile.in and they are all not there. So script a copy of all this files over into new structure.

$ ./configure config.status: error: cannot find input file: res/po/Makefile.in.in $ cp /src/mysql-workbench-1.0.0/mysql-gui-common/res/po/Makefile.* res/po/ $ ./configure $ ./configure --enable-grt --enable-canvas $ make In file included from ....mysql-gui-commonlibrary_sql_parsersourcemyx_sql_parser.yy:21: ../include/MyxSQLTreeItem.h:171:28: warning: no newline at end of file ....mysql-gui-commonlibrary_sql_parsersourcemyx_sql_parser.tab.cc: In function `int yyparse()': ....mysql-gui-commonlibrary_sql_parsersourcemyx_sql_parser.tab.cc:28452: error: expected primary-expression before "__attribute__" ....mysql-gui-commonlibrary_sql_parsersourcemyx_sql_parser.tab.cc:28452: error: expected `;' before "__attribute__" make[3]: *** [myx_sql_parser.tab.o] Error 1 make[3]: Leaving directory `/src/wb/mysql-gui-common/library_sql_parser/source' make[2]: *** [all-recursive] Error 1 make[2]: Leaving directory `/src/wb/mysql-gui-common/library_sql_parser' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/src/wb/mysql-gui-common' make: *** [all] Error 2

Well, I’ve now exhausted this as well.

ronald · January 18, 2006

I signed up for Google Talk some time ago, but just could not get the sucker to operate within Gaim successfully.

Seems I was missing an advanced setting as found at http://www.google.com/support/talk/bin/answer.py?answer=24073. Now I have a successful login, I just need some other friends to join Google Talk. Yes that means you.

ronald · January 18, 2006

Did I push to much in my last post? I don’t think so, but I guess it’s a fragile balance sometimes in Open Source between those keen end users, and the developers that do give so much towards their own creations (I understand, I’m in that category myself).

I was very proud of my work yesterday, it took a whole day of my time (I do have better things to do, like finish my own Open Source project HTMLtags, while will allow me to build my sample application, which I can then use for my MySQL Users Conference presentation). I learnt to dig around the net a lot, go on the wild goose chase several times, understand some more under the hoods of compiling, libraries and dependencies in the GTK world I would have otherwise not really cared about. But as I said, I got to a brick wall by the end, and it was dishearting.

It seemed my Bug Report #16604 on MySQL Workbench compiling listing a clear number of bugs was not well received by the development team. Maybe I should have slept on it, but about 1am in the morning I made another plea for assistance.

Well I woke this morning, and as I mentioned in my opening statement was I too passionate about this pursuit. Perhaps not. Overnight, a positive response to my #16604 provided feedback that my hard work was indeed incoporated, and the next clue of compilation (which could just be in a simple INSTALL file hopefully in the future) was presented.

So eagerly ignoring breakfast, and the increasing pile of dirty dishes, I jump in with this new intel. It ws just one line.

you need to configure gui-common with: ./configure –enable-grt –enable-canvas to enable the components needed by Workbench.

$ cd /src/mysql-workbench-1.0.0/mysql-gui-common $ ./configure --enable-grt --enable-canvas $ make make[3]: Entering directory `/src/mysql-workbench-1.0.0/mysql-gui-common/library_grt/source' if gcc -DHAVE_CONFIG_H -I. -I. -I../.. -I../../library_grt/include -I/usr/include/glib-2.0 -I/usr/lib/glib-2.0/include -I/usr/include/libxml2 -I../../library_util/include -I../../library_util/shared_include -I../../library_grt/newt -I/opt/mysql/include -I/usr/include/pcre -I/usr/include/python2.3 -DENABLE_JAVA_MODULES -DENABLE_PYTHON_MODULES -DLUA_TEXT_DIALOGS -g -O2 -MT lua_dialogs.o -MD -MP -MF ".deps/lua_dialogs.Tpo" -c -o lua_dialogs.o lua_dialogs.c; then mv -f ".deps/lua_dialogs.Tpo" ".deps/lua_dialogs.Po"; else rm -f ".deps/lua_dialogs.Tpo"; exit 1; fi lua_dialogs.c:20:17: lua.h: No such file or directory lua_dialogs.c:21:21: lauxlib.h: No such file or directory .... make[3]: *** [lua_dialogs.o] Error 1 make[3]: Leaving directory `/src/mysql-workbench-1.0.0/mysql-gui-common/library_grt/source' make[2]: *** [all-recursive] Error 1 make[2]: Leaving directory `/src/mysql-workbench-1.0.0/mysql-gui-common/library_grt' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/src/mysql-workbench-1.0.0/mysql-gui-common'

More googling, hopefully http://www.lua.org is what this is???

$ cd /src $ wget http://www.lua.org/ftp/lua-5.0.2.tar.gz $ tar xvfz lua-5.0.2.tar.gz $cd lua-5.0.2 $ ./configure $ vi config # (change Line 151: INSTALL_ROOT from /usr/local to /usr) $ ./configure $ make $ make install

$ cd /src/mysql-workbench-1.0.0/mysql-gui-common $ make make[3]: Entering directory `/src/mysql-workbench-1.0.0/mysql-gui-common/source/linux' if g++ -DHAVE_CONFIG_H -I. -I. -I../.. -DXTHREADS -D_REENTRANT -DXUSE_MTSAFE_API -I/usr/include/libglade-2.0 -I/usr/include/gtk-2.0 -I/usr/include/libxml2 -I/usr/lib/gtk-2.0/include -I/usr/X11R6/include -I/usr/include/atk-1.0 -I/usr/include/pango-1.0 -I/usr/include/freetype2 -I/usr/include/freetype2/config -I/usr/include/glib-2.0 -I/usr/lib/glib-2.0/include -I/usr/include/gtkmm-2.4 -I/usr/lib/gtkmm-2.4/include -I/usr/include/glibmm-2.4 -I/usr/lib/glibmm-2.4/include -I/usr/include/gdkmm-2.4 -I/usr/lib/gdkmm-2.4/include -I/usr/include/pangomm-1.4 -I/usr/include/atkmm-1.6 -I/usr/include/sigc++-2.0 -I/usr/lib/sigc++-2.0/include -I/opt/mysql/include -I/usr/include/pcre -DENABLE_JAVA_MODULES -DENABLE_PYTHON_MODULES -I/usr/include/freetype2 -I../../library/include -I../../library_util/include -I../../library_util/shared_include -I../../library_grt/include -I../../library_grt_modules/include -I../../library_grt_workbench/include -I../../library_gc/include -I../../library_gc/ftgl/include -I.. -DDATADIRNAME=""share"" -DCOMMONDIRNAME="""" -g -O2 -MT MGRT.o -MD -MP -MF ".deps/MGRT.Tpo" -c -o MGRT.o `test -f 'MySQLGRT/MGRT.cc' || echo './'`MySQLGRT/MGRT.cc; then mv -f ".deps/MGRT.Tpo" ".deps/MGRT.Po"; else rm -f ".deps/MGRT.Tpo"; exit 1; fi MySQLGRT/MGRT.cc: In member function `void MGRT::init_thread(const std::string&)': MySQLGRT/MGRT.cc:115: error: `myx_grt_shell_print_welcome' undeclared (first use this function) MySQLGRT/MGRT.cc:115: error: (Each undeclared identifier is reported only once for each function it appears in.) MySQLGRT/MGRT.cc:170: error: `myx_lua_init_loader' undeclared (first use this function) MySQLGRT/MGRT.cc:194: error: `myx_grt_init_lua_shell' undeclared (first use this function) MySQLGRT/MGRT.cc: In member function `void MGRT::perform_shell_command(const Glib::ustring&)': MySQLGRT/MGRT.cc:275: error: `myx_grt_lua_shell_execute' undeclared (first use this function) MySQLGRT/MGRT.cc: In member function `Glib::ustring MGRT::shell_prompt()': MySQLGRT/MGRT.cc:283: error: `myx_grt_lua_shell_get_prompt' undeclared (first use this function) make[3]: *** [MGRT.o] Error 1 make[3]: Leaving directory `/src/mysql-workbench-1.0.0/mysql-gui-common/source/linux' make[2]: *** [all-recursive] Error 1 make[2]: Leaving directory `/src/mysql-workbench-1.0.0/mysql-gui-common/source' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/src/mysql-workbench-1.0.0/mysql-gui-common' make: *** [all] Error 2

This seems defined in library_grt/source/myx_grt_lua_shell.c which has compiled to object code. So it’s probably clearly related with the lua dependency in
some way, but I’m not a C++ developer so apart from googling and greping it’s a good 3 levels over my head.

A very strong dead end again, clearly very C++ and MySQL specific. Back to my new buddy within MySQL AB, for the next clues.

ronald · January 18, 2006

One of the great benefits of Open Source, it’s Free, and you can get great support, sometimes even from the developers directly (rather then 5 levels of paid customer support for a commercial product). One of the greatest banes of Open Source, if you have a problem, and nobody has experienced and documented in a forum etc the problem you have with the same OS, libraries etc, you could be totally up the creek without a paddle, boat and for that matter water. (luckily you still have oxgyen)

Well, I’m having this problem with MySQL Workbench. A product promising so much, but if you can’t get the binary working on Linux to even start, where do you go.

You will see via the Forums, I’m not the only person. This is the current Bugs List.

Wanting to make a difference, even just for myself, and those others that also seem lost, I set out to pursue this to the bitter end. Long story short, some 6-7 hrs later I’m so close, and the response in a related Bug at the point I’ve now finally reached is:

at this moment we do not process bugs of mysql workbench, because it’s still in the intensive development, and we provided snapshot in order to give people first implression of WB.
I am changing status to ‘Analyzing’ and return to this bugreport when WB will be issued officially.

Well, my only statement here at 11:30pm at night, is I’m annoyed and frustated. The only reason why I’m compiling source is the snapshot doesn’t work, there is no information of when it will be issued officially, infact as I’ve mentioned previously, there are 3 different versions for 3 different OS’s at present. (More Info in Forums). It’s complicated as it seems to be all one way communication, people reporting problems, but no information feedback in return. Perhaps I should have worked on my own Open Source project?

My simple question in response to this comment is. “Please provide the source that built the binary, so at least we can work with the same apples. The released source and snapshot binary are not consistent.”

My only pursuit now is to publish my findings. I’m made signficant progress that will help others, but only so far. I’ve also uncovered 3 configuration errors, and library inconsistency as per minimum requirements (which isn’t documented, it’s hit and miss, trial and error), and a fatal compilation error stopping you in your tracks. My current logged bug report of this Bug #16604

The road already covered

My environment is CentOS 4.2 (a.k.a RedHat Enterprise Linux RHEL 4.2) recompiled and free.

The first pot hole.

Downloaded Linux Binary 1.0.0-alpha from ftp://ftp.mysql.com/pub/mysql/download/mysql-workbench-1.0.0-alpha-linux-i386.tar.gz (announced on forums MySQL Workbench 1.0.0alpha for Linux (November 24, 2005)

Errors on startup.
./mysql-workbench-bin: /usr/lib/libstdc++.so.6: version `GLIBCXX_3.4.5' not found (required by ./mysql-workbench-bin) ./mysql-workbench-bin: /usr/lib/libstdc++.so.6: version `GLIBCXX_3.4.4' not found (required by ./mysql-workbench-bin)

Refer to my Initial findings published at forums (December 16, 2005) over 1 month ago with details of my environment and installed libraries.

Looking at the hole, now it’s a trench.

Diving right in.
$ su - $ cd /src $ wget ftp://ftp.mysql.com/pub/mysql/download/mysql-workbench-1.0.0-alpha-linux.tar.gz $ tar xvfz mysql-workbench-1.0.0-alpha-linux.tar.gz $ cd /src/mysql-workbench-1.0.0/mysql-gui-common $ ./configure

Errors
No package ‘gtkmm-2.4′ found
configure: error: Library requirements (libglade-2.0 gtkmm-2.4) not met; consider adjusting the PKG_CONFIG_PATH environment variable if your libraries are in a nonstandard prefix so pkg-config can find them.
Found http://www.gtkmm.org – gtkmm is the official C++ interface for the popular GUI library GTK+. First problem, current version is 2.8, previous version (as on home page links is 2.2).

More digging, clicking the home page documentation link, and some docs recommend binary installs (why am I not surprised). gtkmm 2.4 documentation. What the! The home page has 2.8 and 2.2, and the docs are 2.4. I’m a little confused, but I trundle on.

Found http://www.gtkmm.orgNo RHEL binaries, tried for Fedora Core 4. Attempt to Add FC4 extras to yum settings and do yum install gtkmm24-docs failed. (this was the docs recommendation)

So track it down on a mirror. Now that is now gtkmm24 Version 2.6???? I’m sure there’s a reason but I’m a GTK lay person and it’s confusing me.

$ wget http://public.planetmirror.com/pub/fedora/linux/extras/4/i386/gtkmm24-2.6.2-2.i386.rpm $ wget http://public.planetmirror.com/pub/fedora/linux/extras/4/i386/gtkmm24-devel-2.6.2-2.i386.rpm $ wget http://public.planetmirror.com/pub/fedora/linux/extras/4/i386/gtkmm24-docs-2.6.2-2.i386.rpm

$ rpm -ivh gtkmm24-2.6.2-2.i386.rpm warning: gtkmm24-2.6.2-2.i386.rpm: V3 DSA signature: NOKEY, key ID 1ac70ce6 error: Failed dependencies: libglibmm-2.4.so.1 is needed by gtkmm24-2.6.2-2.i386 libsigc-2.0.so.0 is needed by gtkmm24-2.6.2-2.i386 libstdc++.so.6(GLIBCXX_3.4.4) is needed by gtkmm24-2.6.2-2.i386

I figured it wasn’t going to be that easy. However, found an interesting note, (GLIBCXX_3.4.4), hmmm, see that error before. Is that sunlight I see looking up out of the trench.

It doesn’t ran it pours.

Won’t focus here.

libsigc Not found in CentOS RPMS Found at http://libsigc.sourceforge.net
$ cd /src $ wget http://ftp.gnome.org/pub/GNOME/sources/libsigc++/2.0/libsigc++-2.0.17.tar.gz $ tar xvfz libsigc++-2.0.17.tar.gz $ cd /src/libsigc++-2.0.17 $ ./configure $ make $ make install $ rpm -ivh gtkmm24-2.6.2-2.i386.rpm

Still fails with same dependancies including libsigc-2.0.so.0 which is what I just compiled.

Using a trench digger now, shovelling was too much work.

I won’t bore you with the iterative details of my approach, needless to say, basically I had to work backwards from this resultant coding, a number of times. But this worked. (Compiling the dependancies that is)

$ cd /src $ wget http://ftp.gnome.org/pub/GNOME/sources/libsigc++/2.0/libsigc++-2.0.17.tar.gz $ tar xvfz libsigc++-2.0.17.tar.gz $ cd /src/libsigc++-2.0.17 $ ./configure --prefix=/usr $ make $ make install

One Down, note the –prefix=/usr is significant.. There must be some funky way to use LD_LIBRARY_PATH, LIBDIR, just need somebody to explain why simple evironment variable doesn’t work downstream.

# configure: error: Package requirements (sigc++-2.0 >= 2.0.0 glib-2.0 >= 2.4.0 gobject-2.0 >= 2.4.0 gmodule-2.0 >= 2.4.0) were not met. $ cd /src $ wget http://ftp.gnome.org/pub/GNOME/sources/glibmm/2.4/glibmm-2.4.8.tar.gz $ tar xvfz glibmm-2.4.8.tar.gz $ cd /src/glibmm-2.4.8 $ ./configure --prefix=/usr $ make $ make install

Two down.

# Requires checking for glibmm-2.4 >= 2.4.0 atk >= 1.6.0... Package glibmm-2.4 was not found in the pkg-config search path. $ cd /src $ wget http://ftp.gnome.org/pub/GNOME/sources/gtkmm/2.4/gtkmm-2.4.11.tar.gz $ tar xvfz gtkmm-2.4.11.tar.gz $ cd /src/gtkmm-2.4.11 $ ./configure --prefix=/usr $ make $ make install

Three Down.

$ cd /src/mysql-workbench-1.0.0/mysql-gui-common $ ./configure checking for pcre-config... no configure: error: Could not find pcre-config script. Make sure the pcre libraries are installed

What is pcre-config? Not much luck finding that, try pcre, come across http://www.pcre.org/ – Perl Compatible Regular Expressions. Well could be, no info about pcre-config, nothing on the Perl regular expressions man page. Well, no pain no gain, it’s in a yum list so.

$ yum install pcre

Already installed, well that doesn’t help. Some more digging around, more time digging a bigger hole, there’s a pcre-devel, let’s try that.

$ yum install pcre-devel

Voila!, I have a pcre-config script

$ cd /src/mysql-workbench-1.0.0/mysql-gui-common $ ./configure

./configure: line 3488: -f: command not found

Are a problem, lucky already documented in Forums (at least somebody has been this far).

Replace Line 3488
if ! -f po ; then
with
if test ! -f po ; then

Try Again.

$ cd /src/mysql-workbench-1.0.0/mysql-gui-common $ ./configure

./configure: line 7368: syntax error near unexpected token `else'

Haven’t see this documented.

Replace Line 7466 (note 2 lines earlier)
if test "${ac_cv_prog_PCRE_LIBS+set}" = set; theN
with
if test "${ac_cv_prog_PCRE_LIBS+set}" = set; then

Try Again.

$ cd /src/mysql-workbench-1.0.0/mysql-gui-common $ ./configure $ make ../../library_util/include/myx_util_functions.h:30:18: pcre.h: No such file or directory

Patience is running short, a find locates file in /usr/include/prce directory.

# I'm now no longer amused with this, I'm not a C developer, but basic code should compile. $ cp cd /usr/include /pcre/* /usr/include $ ./configure $ make MGTableEditor.cc: In constructor `MGTableEditor::MGTableEditor(bool)': MGTableEditor.cc:229: error: 'class Gtk::ComboBoxEntry' has no member named 'get_entry' MGTableEditor.cc:265: error: 'class Gtk::ComboBoxEntry' has no member named 'get_entry'

Well, why am I not surprised. More reading, now into the bowes of GTK. API Docs at http://www.gtkmm.org/docs/gtkmm-2.4/docs/reference/html/classGtk_1_1ComboBoxEntry.html
shows that get_entry is valid for 2.4.

Hmmm, more though, now is that 2.4, or is that 2.6. Well this trench digger just fell into the hole is was digging.

A bigger hammer, now the full blown Hydraulic Excavator

I won’t linger here, been down this path before, however more plumbing was required so it was an iterative process again.
cd /src wget ftp://ftp.gtk.org/pub/gtk/v2.6/glib-2.6.6.tar.gz ftp://ftp.gtk.org/pub/gtk/v2.6/glib-2.6.6.tar.gz cd /src/glib-2.6.6 ./configure --prefix=/usr

# checking for sigc++-2.0 >= 2.0.0 glib-2.0 >= 2.6.0 gobject-2.0 >= 2.6.0 gmodule-2.0 >= 2.6.0... Requested 'glib-2.0 >= 2.6.0' but version of GLib is 2.4.7 cd /src wget http://ftp.acc.umu.se/pub/GNOME/sources/glibmm/2.6/glibmm-2.6.1.tar.gz tar xvfz glibmm-2.6.1.tar.gz cd /src/glibmm-2.6.1 ./configure --prefix=/usr make make install

cd /src wget ftp://ftp.gtk.org/pub/gtk/v2.6/atk-1.9.0.tar.bz2 bunzip2 atk-1.9.0.tar.bz2 tar xvf atk-1.9.0.tar cd /src/atk-1.9.0 ./configure --prefix=/usr make make install

cd /src wget ftp://ftp.gtk.org/pub/gtk/v2.6/pango-1.8.2.tar.gz tar xvfz pango-1.8.2.tar.gz cd /src/pango-1.8.2 ./configure --prefix=/usr make make install

# Requested 'pango >= 1.8.0' but version of Pango is 1.6.0 cd /src wget ftp://ftp.gtk.org/pub/gtk/v2.6/gtk+-2.6.9.tar.gz tar xvfz gtk+-2.6.9.tar.gz cd /src/gtk+-2.6.9 ./configure --prefix=/usr make make install

#pango-1.8.2.tar.gz # checking for ATKMM... Requested 'atk >= 1.9.0' but version of Atk is 1.8.0 # checking for GDKMM... Requested 'gtk+-2.0 >= 2.6.0' but version of GTK+ is 2.4.13 cd /src wget http://ftp.acc.umu.se/pub/GNOME/sources/gtkmm/2.6/gtkmm-2.6.5.tar.gz tar xvfz gtkmm-2.6.5.tar.gz cd /src/gtkmm-2.6.5 ./configure --prefix=/usr make make install

Now the test.

$ cd /src/mysql-workbench-1.0.0/mysql-gui-common $ make clean $ ./configure $ make $ make install

WOOOOHOOO! no errors here!

One down, one to go.

$ cd /src/mysql-workbench-1.0.0/mysql-workbench-1.0.0 $ make clean $ ./configure $ make

make[3]: Entering directory `/src/mysql-workbench-1.0.0/mysql-workbench/source/linux' make[3]: *** No rule to make target `../../../mysql-gui-common/source/linux/libwbcommongui.a', needed by `mysql-workbench-bin'. Stop.

Investigation
$ cd /src/mysql-workbench-1.0.0/mysql-workbench/source/linux # (current make directory) $ ls -l ../../../mysql-gui-common/source/linux/lib* -rw-r--r-- 1 root root 7410114 Jan 18 22:43 ../../../mysql-gui-common/source/linux/libmacommongui.a -rw-r--r-- 1 root root 8006062 Jan 18 22:44 ../../../mysql-gui-common/source/linux/libqbcommongui.a

Similar library name exists, but not libwbcommongui.a Well that’s it, it’s finally now looks not a environement problem, more a software development problem. I’ve logged my findings at Bug #16604. I guess we will wait for a good response.

Was it all worth it! Well 90 mins of documenting, the longest blog I’ll ever write. If the next runner can move this forward to a getting a binary from compilation, that then starts, then it was worth it. But only in a reasonable time.

ronald · January 17, 2006

I’m stuck between a rock and a hard place. I’ve been using DBDesigner 4 from FabForce, an open source visual design tool, and apart from working around a number of bugs, I’ve found it practical to design from scratch. The big plus, it works under Linux.

With the announcement that this was being incorporated into MySQL, called MySQL Workbench, I was looking forward to getting my hands on it. I guess that was about 8 months ago. Finally about 6 weeks ago, Version 1.0.0-alpha was released for Linux. Unfortunately it didn’t work, would not even start for me. Logged as Bug #15421, which got marked as a duplicate of Bug #15218 (I could have sworn I did a search first). Anyway, this got promptly closed as Unable to reproduce, but I see it’s finally been reopened again.

Windows is at 1.0.2, Mac OS/X is at 1.0.3-alpha, and yet linux is still at 1.0.0-alpha. What gives? There seems to be a clear lack of communication as to what’s going on.

The problem is, I’m preparing presentatations for the Brisbane MySQL Users Group, and the MySQL User Conference, and I want to import an existing MySQL database into a modeling tool to generate diagrams. It seems that DBDesigner4 is not MySQL 5 friendly, and given it’s not supported anymore where do you turn.

In DBDesigner, I get the error. “dbExpress error: Invalid Username/password”. Upteen double checks and then searching on the web finally leads to
http://forums.mysql.com/read.php?113,32121,57081#msg-57081, and the trick SET PASSWORD FOR ‘some_user’@’some_host’ = OLD_PASSWORD(‘newpwd’);

So now I can get a connection to the database, there ain’t no model to import. It seems I’m now forced to go look elsewhere for a open-source and free modelling tool that runs under Linux.

Ultimately I can’t really complain, this is the primary problem with Open Source, on one side, it’s Free, the other side support, documentation and help can be a hit and miss affair, and there are no commitments to a release schedule or feedback as there is no money involved.

Still if after all this time, I’m forced to go out and buy a product, it will be a great shame.

ronald · January 16, 2006

Would it not be great if on the MySQL website there was a page of stats (updated daily) that provided statistics like number of installations, a breakdown of versions registered (not certain I like that exact word) , OS’s, countries etc. More specifically, some useful stats on the engine types in practical use, avg number of tables per database etc. Of course the types of stats could be limitless, but with the success of MySQL as well as other open source projects, more imperial figures on installations other then just downloads I think would definitely benefit given the current momentum. (Availability of information to competitors could be both a good and bad thing.) Perhaps figures can be shown in percentages, not actual numbers.

Anyway, nice idea you say, we can all come up with ideas, but how could you implement something like this.

I made a post yesterday and mentioned the thought of an XML storage engine. It seemed to stir some feedback. Those notes were actually taken from more detailed notes that I’ve never published so I thought it would be good to explain some of the background.

Many months ago at the Brisbane MySQL Users Group our informal discussions following the evening presentation turned to a more detailed analysis of the different engine types available within MySQL. A throw away comment by somebody like ‘We have no idea if BDB is really used?’ prompted me to consider the possibility of why can’t this question be answered in the future.

How would it work? Well for each installation of MySQL on a server (given you can have multiple installations of varying versions per machine) the option to provide a feedback loop is made available post installation, it could even be part of the install processs (probably not in .rpm). The feedback loop configures the following basic settings.

Frequency (Monthly, Weekly, Daily, Once Off)
Granularity (Summary, Verbose)

What would I have this feedback loop submit:

MySQL Version
OS Version
Country/Locale
Number of User Databases (excluding mysql & test)
Number of tables per storage Engine per database

More detailed information, not necessary for stats, but perhaps useful for MySQL internals would be output from the SHOW set of commands. (variables, status, table status,engines, innodb status). This alone is another topic, allowing collection of information that can be used to evaluate usage, possible tunings etc). Additional information such as number of triggers, stored procedures etc, would identify if this level of functionality is actually really being used out there.

By registering with MySQL to provide a feedback loop from your server installation to MySQL, you are provided with a unique Id, which enables clear tracking for MySQL, but provides clear anonymity for the customer to ensure we are not taking any propriety information from them.

We never want to hide the data of an installation that is provided to MySQL, it needs to be in the open. Why not make it an open standard of data exchange (using XML), and why not then enable the installation itself to use these statistics internally for some level of reporting.

XML allows for the files to be reused outside of MySQL directly. Using XSLT you could take these XML files for better presentation of statistical information. This would probably go against the ethos of MySQL with now information stored in XML instead of the database. So why not create an XML Storage Engine.

It would for lack of a better word be grossly inefficient, however it was more a Read/Only type structure, more you just write out a chunk of data in one go, and you always read the entire file. You don’t do partial updates (you could, but you rewrite the entire file), it leads itself to statistically information, written once only. Of course you would have to satisfy the search

What other possible extensions for an XML storage engine, you could store and read RSS feeds, even OpenDoc standards as implemented by Open Office for Documents.

Now, regardless of all the associated complexities like, how do you post add this functionality to older versions, how do you automate the scheduling of the feedback look across different OS’s, blah, blah, blah, the purpose is just to throw the idea around for a minute and see what falls out.

Additional statistics more useful for internal MySQL usage could pinpoint numbers of upgrades, from which version to which versions (more specifically if not current GA version). The biggest downside is as part of Open Source you can’t enforce gathering any of this information, so when the website says 1 million installations (it’s 1 million of people that have taken the time to notify MySQL, and that could be 10% or 50%, you just don’t know).

Could there perhaps be space here for a commercial product? I doubt it as this is very tightly coupled with MySQL, and unless there was seamless integration I doubt very few people would go to any trouble after installing MySQL.

It’s just an idea, but it’s nice to have ideas at least some of the time.

ronald · January 15, 2006

It wasn’t that long ago that MySQL released the GA Release of Version 5.0 with major new features (Oct 24 2005). It still took 5.0 about a year to go from alpha to GA, however I’d suspect a much shorter turnaround this time.

Version 5.1 is already at alpha, and the largest public functionality mentioned has been Partitioning. It is also anticipated that Storage Engines (a very handy MySQL feature in comparison to other RDBS products), will be a hot-pluggable API instead of a source re-compilation. Now I’ve never even looked at the Storage Engine code, but it’s been talked about a few times, particularly the CSV Storage Engine in general discussion.

There are some new features being documented now, but not generally available in an alpha build including Events Management (5.1.6) and XPath support within SQL (5.1.5).

Well I could use the events management immediately with some refactoring of code into a stored procedure. My current CRM allows for a system wide (ie all users) Phone Batch to be created for phone donations. Batches are locked into daily, so a new batch must be manually created each day. The ability to both automatically create a daily open Phone Batch, as well as automatically close the previous day’s while only a small task, removes a currently repetitive task from somebody’s day. Of course at present, this is planned, but via a cron job and wrapper script, now I could do it all in the Database.

XPath expressions, interesting, I use XPath Explorer a great standalone Java app, and Eclipse Plugin, but I guess now I can just do it at a SQL Prompt. What I’d really love to see is an XML Storage Engine.

XML Storage Engine

What about an XML Storage Engine? It would for lack of a better word be grossly inefficient, however it was more a Read/Only type structure, more you just write out a chunk of data in one go, and you always read the entire file. You don’t do partial updates (you could, but you rewrite the entire file), it leads itself to statistically information, written once only. So would it provide any benefit. Really only you have any sources of XML data and you just want to keep this information in an XML format, effectively the MySQL data file is an actual XML file that can be easily copied.

XML would allow for the files to be reused outside of MySQL directly. Using XSLT you could take these XML files for better presentation of statistical information. What other possible extensions for an XML storage engine could be possible. You could store and read RSS feeds, even OpenDoc standards as implemented by Open Office for Documents.

Would it serve any real purpose? Probably not. But with MySQL Storage Engines at least it’s easily possible.

ronald · January 15, 2006

In a recent job interview I was asked the question regarding Unit Testing/Automated Testing of a Database? An interesting question and indeed an interesting problem. I thought it was a good topic to describe what I’ve done in the past, and where I would go for a more complete testing environment given the opportunity of a entire XP project.

This is the approach I have implemented successfully in the past. It’s not a complete solution, however at the time with the client it provided appropriate coverage.

I don’t use a framework such as dbUnit to load data via XML, or specifically test data. XML is ugly to store data, and also with maintenance and comparison. I start with a pre-configured database of representative sample data, refer to my notes later on this, and then I use the tests of the application to perform the necessary data manipulation. This ensures that you are as close as possible to testing actual situations, and ensuring that any issues the application does (such as enter bad data, or RI failures) are caught appropriately.

Within this process an automated build test would first reset the database to a known set of data. I’ve also found that this helps as you can also recreate the schema if necessary. As part of Schema Design in an XP Development, I have two ways to create the database schema. You can create it from scratch (so there is always appropriate SQL to create the current version of the Schema, lets say BUILD_102. Alternatively, you can always upgrade between releases, for example between BUILD_101 and BUILD_102 with the appropriate upgrade scripts. Upgrade or patch scripts only move one version to the next version. It’s not possible in a production environment to simply recreate your schema for each release, however for testing and training you can. It’s also pointless after 50 releases to have to perform 50 patch releases from the original source schema for every automated build test.

This does lead to two paths necessary for creating a schema, but this can also be tested adequately in an automated way.

I also split my application tests suites that use the sample data into two buckets. Destructive and Non-Destructive. The reason is the Non-Destructive tests (i.e., non DML statements) can be re-run as many times as necessary. The Destructive tests (i.e., DML statements) can only be run once, before the database must be restored. Of course you can have the approach of setUp() and tearDown() within JUnit however it’s cleaner if you can extract this somewhat to a higher level, making the Unit Tests easier. By also running tests that don’t continually use the some data, or builds the data though test execution, you get a better coverage of different data sets. To give a few examples, You could create a Test that created a row of data, then edited the same row, then deleted the row. These are indeed valid, but if the first test fails, how do you know if the update and delete tests are also broken, they are by dependant by default and will fail, but did they really. If with your sample data, you created a new row, edited a different pre-configured row, and also deleted a different pre-configured row, you could eliminate the need to dependencies.

Now of course, there are situations where data must be specifically checked at the database level, for example it may never be displayed in your application, it may be intermediate information that is then summarised for display, or internal audit information held against data (for example Create User, Last Updated User), or data created by procedures or triggers. There are also situations when even within an application testing that can verify the data in a User Interface, you want to verify this at the source.

To this end I have a custom written JUnit extension that can perform specific SQL statements and comparison. I’ll need to write about this and provide this at a later time. (when I can dig it up)

Sample Data

Sample Data in the database is pre-configured, not in XML files, but so it can be managed by more primitive means, either by a database GUI interface or via SQL flat files or even text files. Why have pre-configured data this way? A few reasons.

It’s not coupled to your tests in any way, so it can be reused, for example as Training Data.
You can use database specific tools more easily say in loading the data in a relational way.
You can use the same database specific tools to export the data easily, if say you use an application to modify certain information.
You could more easily incorporate legacy data that is also being migrated if you use the same database specific tools.

Granted XML is universal in it’s data representation, it’s more self descriptive, but it’s a really pain to edit manually, and it’s very verbose when there are simply more primitive methods of this type of data management.

So we are creating a pre-configured data set, and an extensive one when possible. As I mentioned, the re-use capabilities for training or demonstrations really works.

Training Data

I have successfully with a number of systems, specifically CRM implementations used a Cartoon Environment for the sample data. There are a few reasons for this. First, most people I’ve ever met can related in some way to some set of the data. If they can’t, then there can read info online, or watch a movie etc, and get an appreciation from the representative data set, effectively I’m leveraging of the time and effort of others here, much better then a non-descript set of data.

You have the cartoon characters (e.g. Mickey Mouse, Donald Duck, Daffy Duck, Marvin the Martian, The Simpsons, The Flintstones, The Jetsons), use all the streets and rides as Disneyland as addresses, the animators as the users (e.g. Walt Disney, Chuck Jones, Stan Lee), you can use the different studios (e.g. Warner Bros, Disney, Pixar) for different states or countries, you can use shows or movies (e.g. Toy Story, Shrek) to group characters in other ways.

With this type of data, common attributes such as birth date, family units, nick names, people deceased etc, are all part of the available data. It’s surprising how much information you can find when using The Simpsons for example, of full names, addresses, interests etc.

It’s impressive when the CEO of a company is showing the application to overseas business partners, when his knowledge of the application (from his management perspective) is sufficient, there is no knowledge of the data really necessary to use or explain as it’s commonly used and generally understood.

At this point I would like to ensure that I correctly acknowledge the registered trademarks of Disney, Warner Bros, Hanna Barbera, Pixax, Dreamworks and that I am not using the names for any profit.

Summary

So this is what I’ve used in the past. What would I do in the future if I was charged with bullet proof testing of a database, even independent of an application, effectively 100% test coverage of the data. Well, this is an unproven approach, but I’d relish the opportunity to give it a full blooded test one day.

How to test the database with an automated test approach.

I’d consider the breaking down of testing into 3 areas. These being:

Schema
Data
Business Logic/Referential Integrity

Each of this is effectively built on the the preceding points.

Schema

This would be quite straight forward it’s a flat comparison between schema’s, which could be managed via the appropriate products data dictionary tables using SQL. You could even simply compare 2 schema’s in a few simple SQL statements. You could also use the approach of export the schema definition, and then compare flat files. You will find some downsides to these approaches, ordering is a big thing, columns within a database table, or the order of the tables that are exported may not be guaranteed. However given appropriate standards are defined used of tools this comparison could easily occur.

Being able to verify patches between releases, and full installed schema’s are also possible. The schema is the easy part.

Data

Data could be tested in varying means. Counts, sums and sample comparisons, but it’s also just data, why not md5sum the entire data. Why not even dump the data to flat files, and use basic difference tools for comparison. One simple approach. Especially if you are loading data, using or manipulating it, then you can export and compare at a file level. This would work very well for data considered Read Only for the life of testing.
This format may allow you to compare data between two different database products, e.g. Oracle use for your transactional online processing, and MySQL use for Web Data or Management Summary Reporting application.

In order to test the data you need the schema, but how can you test the data without the business logic and Referential Integrity. Within MySQL you can easily disable foreign key constraints, or easily adjust the table type to a structure to ignore this syntax. This could allow you to run tests with and without Referential Integrity to determine the strength of your application.

Of course this is a static version of the data. Performing separate testing of DML statements directly against the database could prove a waste of time, unless your application was written in such a way, that your application database layer was a complete API. Still you would be simulating what your application is ultimately doing, so it could be overkill. You could apply the techniques of comparison with know results after a successful running of automated build testing.

Business Logic/Referential Integrity

The hard stuff. Well you could be half way there with adequate schema and data testing. At least you are then confident that core integrity exists.

The problem is also to do with application integrity verses database integrity.

Let’s take a percentage, it goes from 0 to 100. Now using MySQL for example, you would define this as TINYINT(3) UNSIGNED, giving you a valid range of 0-255, and by default a display characteristic of 3 characters (the (3) in this example is just beautification).

You application logic restricts the value into this column to 0 to 100. But do you enforce this at the database? Depends on your needs. If the application is the only way to insert and maintain data, then you could get away with it, if data can be managed from other external systems, you may have APIs that also need to manage it. What if you grant SQL access to DBA’s, could they accidentially mess it up. That song “It’s a fine line between pleasure and pain” comes to mind at this moment. I guess what I’m getting at here, in solely database testing you could easily insert 255 into a percent column and pass a number of data specific tests. I’d assume it would fail some as well otherwise your tests aren’t complete, but when using the application you could never test 255, as the client would never allow it.

There are a lot more issues in RI testing, Cascading UPDATE, DELETE rules for etc. And then when you work all that out, you have to start with Triggers and Stored procedures. I’m not going to spend any time here at this time.

Unforeseen Side Effects

Data is a strange beast. It’s the source of information, so I always like to go back to the data for comparison, however the lack of good data (most notably Legacy Systems Migration) can drive you mad. What good is it to have a new system but not be able to enforce an adequate level of Referential Integrity or Business logic due to incomplete historical data. In essence this has proven in the time I’ve also supported large systems, a good portion of the development cost in support, it’s bad data and/or the need for a simple application to have more complex rules to cater for so called incomplete data.

I’ll give you a trivial example. Gender. In the new system an organisation will always ensure they get the gender of a customer (let’s not wonder how they do it, it’s just an example). So the application is designed to support Male/Female, Reference Data may exist to translate M & F to Male and Female respectively for data storage efficiency. Check constraints, enumeration data types (which I don’t like) may exist. Reports may do side by side comparisons.

Now the company buys a competitor, and then gets their database of 500,000 customers, but they don’t record the Gender. Do you then relax all your great integrity? Do you introduce a gender of Unknown? But that’s only for display, the maintenance screens can’t allow you to select it, so you then need a different level of Reference Data management. Do you make an educated guess and correct the data? Does the customer do an expensive mailout campaign and data collection process to correct this information? So what’s the big deal anyway the customer asks? Well if it’s an organisation that sells hygiene products, you don’t want to sending out material on Shaving Cream to Women on your list and Body Wax to Men. However you can’t use that explanation to describe solely a database driven reason to the customer for the cost of introducing this data. How do you show business value to the customer, when they simply what the data available?

I know this is a trivial example, but if I had a dollar for every trivial problem that customers spent months on,verses the really hard problems, I be writing this from a much more comfortable and relaxing resort haven. (A ski resort rather then a beach resort)

Conclusion

So can you Unit Test a Database solely without an application? Yes. Would you want to? Maybe, to a certain degree. Depending on your type of data. If all your information is highly visible in data entry and data retrieval, you should couple testing more closely with your application. If your data is very generated and collated, rarely user entered but bulk loaded, such as Sensis information or GIS information, then dedicated testing all aspects of the database decoupled from the application could indeed make your application test easier, because it’s easier to identify bad data that the application creates.

ronald · January 14, 2006

Sometimes I wonder how I get to trivial information. Well in the Wikipedia article Google Hoaxes you will find that the Google search engine is valid in a number of unusual languages including Klingon. I’m a Star Trek fan, but that’s going a little to far, however I guess if they create Elvish Language the LOTR people would love it. My favourites are Elmer Fudd and Bork Bork Bork. For those that don’t know this line, it’s famous from the Chef in the Muppets.

It took a while to find it, but you can listen to the famous Chef Song Here.