Opinions, Expertise, Passion.

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().

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!

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.

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.

1. Must be able to handle input and display of dates in local format
2. Must operate in the Timezone of the business (for off-site managed options)
3. Must provide correct currency support
4. Must provide english (if the product is developed in a foreign langauge)
5. Must provide correct Financial Year management
6. Must provide correct local taxation requirements
7. 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.

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

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.

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.

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.

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”

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

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.

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

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)

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