You are here: Home / Archives for Technologies / Open Source

MySQL Workbench 1.0.1 First Impressions

by

These are my first impressions of MySQL WorkBench 1.0.1. Rant and rave you may say, but a new user, or an experienced modeller would probably observe these points. Also, given that (with a poll?) I suspect a good number of users will be previous DBDesigner users, some level of functionality that may not be considered initially in a new product, should be considered for this migration path of legacy users.

I’ll take the time to review the Forums adding my points, and review Bug System but for now, just blogging is easier.

Conclusion

Note: Generally a conclusion is at the bottom, but I’d suspect most readers will switch off quickly, I wanted to catch the gaze in the first few secs.

In summary, I’ve just scratched the surface, I’d do a more fuller QA review and testing, but I’m not certain yet of the best feedback loops, and whether it’s warranted. A lot of what I’ve said may already be known internally. I don’t want to sound petty, but I would not like to reference this as a 1.0 product, it’s got some way to go to being both stable, consistent, and complete in a basic level of functionality. In addition, the MySQL Website 5.0 page speal lists MySQL Workbench as a 5.0 product, I think this is a little inaccurate.

This product appears to have a sound start, but it’s a long way from me considering moving from DBDesigner, which is a real shame, as I’ve been waiting now probably 8 months to get my hands on this (and loose some annoying bugs in the now unsupported DBDesigner), and I’m desperately wanting to get support for MySQL 5 re-engineering.

Main View

After startup you are presented with the MySQL Gui Window, which consists of a number of elements, the menu line, the toolbars (horizantial and vertical), the Main View, and some tab views (shown on right side).
Main View

  • You can’t double click on a table to get into edit mode. You must choose the pencil (bottom left corner of table). When you have a table with 30+ columns, this involves some more scrolling, particularly depending on your view magnification, if you start by finding the table first in a large 150+ table model.
  • While the display of the different elements within a table is nice (e.g. Columns, Indicies, foreign keys etc), when I’ve got 150+ tables in my diagram I don’t want to see this verbose information. I’d like to see a user option of cut down views, removing this headings, display/hide indicies,foreign keys, triggers.
  • What does the lock icon do, and the other bottom right icon, there seems to be no feedback as to what they do?
  • What does the other icon do (not pencil, not lock), again appears to be no feedback?
  • I don’t see a snap to grid? I also don’t see anyway to adjust the size of the grid.
  • I see in the Schema window, the hopeful ability to reference multiple schemas (unclear of the full benefit here), but for now I see no clear way to create then one. Given this, when you select the Table Tool in the Tool Options you get a drop down box for schema. How do you create multiple schemas? Should you in one diagram? If you only have one defined, the drop box should not be present.
  • Tool Options shows 3 colors, that’s nice, however how do you move to selecting more? The color only highlights the table name in the Main View. I’d like to see a background color for all tables. Again in 150+ tables, color coding tables from modules makes it easier to see them, the heading is not enough.
  • You can color a table when creating, but there is no ability to edit the color at a later time?
  • After creating two tables both with Primary Keys, I tried to create a relationship between both, using all 3 relationship options. While I could select the source table, I couldn’t select the destination table in any way. (click, double click, etc)
  • Creating a view with the View tool, provides the iconic buttons in the top section of layout, different to the table layout. It should be consistent.
  • The View Tool, allows you to enter SQL for the view, that’s nice, but it should be displayed on the Data Model. The Data Model’s responsibility is to show tables, columns and relationships. It should show table names, column names (in the case of ‘*’, I think it should probably show ‘*’, not expanded columns due to the complexity.
  • I created a table with an INT(11), VARCHAR(40) and X (which becomes X(0), se Table Editor points). In the resultant Main View, I get INT,VARCHAR and X((0)) as the column definitions. There is no consistency here.
  • I would like to see the capability of removing data types from the display within tables. In a more structured environment, you would first start with a logical model, then move to a physical model, or in early design, you are just defining the probably attributes of table, before they become columns. The datatype isn’t determined, so just listing names, and icon (key,mandatory) is all that’s required.

Schema Tab

  • With my long list of tables, I would like to be able to quickly go from the table in this view to the table in the Main View. (Previously DBDesigner provided a double click to achieve this).
  • I understand that the ordering of categories within the database is alphabetical, but it should be in priority order (Table,View,Routine…). Two reasons, this is the likely creation process, you create tables first, and second, for any schema management prior to 5, the other 3 aren’t relevent.
  • With the previous point, I don’t see any compatibility management for MySQL versions. While there are two many subtle changes to warrent a great deal of work, a simply compatibility of 4.0, 4.1, 5.0, 5.1 would clearly provide when in a 4.x compatibility, views, routines etc are invalid, and for simplicity should be removed from views (Schema Tab) and even vertical menu.

Editing

Table Editor.

  • Has no access to help. In this case, on first view I thought, what does Flags mean as a column attribute? While they not be any help yet, the infrastructure should be in place.
  • Data Type has no completion text. (a.k.a DBDesigner would autocomplete, say if you started with a D, to get Date)
  • Data Type, if you specify nothing, it defaults to (0) which is invalid. If you specify D it defaults to D(0). Regardless of what is entered, the table structure should be syntaxially valid at all times on exit of the field.
  • Options Tab. There are a lot of MyISAM specific options, yet I’m using the default Engine which was InnoDB. These should probably be hidden from view.
  • I don’t like the fact you have to go Apply, then Close, and it’s so easy just to go Close and lose your changes without warning. I think theres is a clear need just for a ‘Save’ which saves your changes and returns you to the Main View
  • Show Advanced/Hide Advanced, is only relevent for the Columns tab (at this time), so it should not be visible on the other tabs.
  • I see no ability in this version (1.0.1+) to define the set of valid Data Types (in my case I like to remove some, to simply autocompletion (which isn’t present)

Other

User Interface

  • No Tool tips for menu icons
  • Help | About didn’t do anything. Would have expected a popup window.
  • I’d like to see some tool tips on the icons used within the objects placed on the Main View.
  • There is no other placeholders for help. Worse case, defining the screens as to be completed, perhaps with some identifier, and then the ability for users to contribute to the help (a web 2.0 think), it will be better then writing from scratch. Even a wiki to allow users to start on the help would I think be benefical

Infrastructure
The saved files are XML, yet you give an extension of .mwb While that is nobel, and identifying it’s it’s .xml using a .xml shows it’s XML and also it makes it easier for other use. I can think of 2 straight away. First, I may wish to use XPath Explorer to quickly search for something. Second, I may use XSLT to provide a HTML quick view of my schema?

Errors
Go the following error, but was not able to reproduce, created table, added some columns, moved around tabs, added a new column lastUpdateTimestamp, [tab] to goto the data type, and crash.

** CRITICAL **: myx_grt_bridge_dict_item_get_value: assertion `dict != NULL’ failed

A working MySQL Workbench Under Linux

by

I must admit I’d given up trying to get MySQL Workbench working under Linux. I guess I’d spent at least 4 or 5 days full time at it, and it was just out of my league, with GTK and C++ errors. It had seemed like a loosing battle, I’ve had 3 detailed documented goes at 1.0.0. Last Post 19th Jan, and 2 with 1.0.1 Last Post 31st Jan.

Anyway, the good news is I now have MySQL Worbench working under Linux. I’ve been working with MySQL AB on a number of specific problems and you should expect a release from MySQL AB soon to address these. Here is a summary of my experience.

  • 1.0.1 removes the requirements for java dependancies
  • 1.0.1 provides a README.linux with configuration requirements, and a subset list of minimum library requirements (which needs updating)
  • libsigc++2.0.11 (an older version) is necessary, most recent versions up to .17 do not work
  • Solving the library dependancies from true source .tar.gz was difficult to manage, however I finally got to use rpm libraries to provide some consistent reproducible steps. I’ll document later as there we a number of issues with achieving this under CentOS 4.2
  • Solving the GLIBCXX_3.4.4, GLIBCXX_3.4.5, GLIBCXX_3.4.6 error. This is what finally stopped me. Thanks to my new Guru Friend Alfredo who put me on the path to solving this.
  • Underlying glXGetProcAddress() compile error required code changes (which are being incorporated in next release). This finally lead to a binary being built.
  • Executing Binary lasted a split second Segmentation fault $PRG-bin $* Debugging determined the problem to be a specific NVIDIA issue, reverting back to generic Video Drivers only left me with a OpenGL and minimum 32MB 3D accelerated graphics card error.
  • Patched code to this problem (to be incorporated in next release) has lead to a working product, at least enough to play around. Now the real fun begins.

Got heaps more to write about, however that would have slowed down the good news. More to come soon.

My thanks to the team at MySQL AB. I have received excellent care and response from the top down. I’d like to say that all the pain has been worth it in the end, and had this not been a open source company, I would have given up a long time ago.

Federated Syntax

by

I’ve never used Federated. I’m waiting for the JDBC version capabilities so I can connect to a non MySQL Server (specifically Oracle). In reading the docs, I see that the syntax includes a CONNECTION String.


CREATE TABLE federated_table (
id INT(20) NOT NULL AUTO_INCREMENT,
name VARCHAR(32) NOT NULL DEFAULT '',
other INT(20) NOT NULL DEFAULT '0',
PRIMARY KEY (id),
INDEX name (name),
INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://root@remote_host:9306/federated/test_table';

I’m surprised in the state of the syntax for two reasons.

  • First, there are hardcoded variables in the string, this sort of breaks the rules of seperating syntax from authentication specifics
  • Second, if you have multiple FEDERATED tables, you incur a level of duplication of this information, and then multiple necessary changes if the connection string is modified

I can’t ask I’d like to see the following, as I don’t use this syntax, but I’d like to suggest a level of abstraction of this information. Could the CONNECTION point to a referenced object (e.g. CONNECTION=’HOST1′). This would solve my second point. Now, the requirements of the HOST1 object needs to contain a connection string, and where would this be stored. I don’t have an answer for that one. Perhaps somebody else could make a suggestion?

Updated
Thanks Mike for your comment, click on comments below to read. Boy it’s been been a long time since I read any ISO Standards documents. Just to clarify Mike’s point for those investigative types. SQL/MED is Management of External Data, and the official ISO document is I believe:

ISO/IEC 9075-9:2003 Information technology – Database Languages – SQL – Part 9: Management of External Data (SQL/MED) ISO Reference

Abstract
ISO/IEC 9075 defines the SQL language. The scope of the SQL language is the definition of data structure and the operations on data stored in that structure. Parts 1, 2 and 11 encompass the minimum requirements of the language. Others parts define extensions.
ISO/IEC 9075-9:2003 defines extensions to SQL to support management of external data through the use of foreign-data wrappers and datalink types.

This document makes reference to DATALINK, a syntax I remember vaguely from Oracle Days. Will need to vett that. What’s also of note in this document is the reference to using the TABLE TYPE of ‘FOREIGN’.

I wonder why MySQL went with FEDERATED as the TABLE TYPE. I’m sure this is a good reason?

Brisbane MySQL Users Group Meeting with Brian Aker

by

We had the privilege of Brian Aker Director of Architecture for MySQL speaking at the Brisbane MySQL Users Group this week (28 th Jan 2006). After the initial discussions on various topics, Brian got into his discussion on MySQL 5.1. I was surprised that only 2 people (myself being one of them) had 5.1 installed and being used in any way.

Here were some of the highlights of the talk. Should have taken some more notes.

MySQL 5.1 Features

Partitioning Will include Range, Hash, List and Key handling and Subpartitioning. Documentation · Forum.
Partitioning is a table structure syntax. No changes to any SQL statements are required to use partitioning within your application.

Log Tables Log files can be stored within the database. Error log will definitely not be available. Official information is sketchy.
The 5.1.6 Release notes indicate mysqld -both-log-formats and –old-log-format arguments (as of 5.1.6), but nothing yet in the The MySQL Log Files Documentation

Fast Alter Table To speed up Alter Table for example extending a VARCHAR column, only the tables .frm is rewritten, and no adjustment to table rows is required.

Replication Documentation

  • Row Based Replication (RBR) (5.1.5) Documentation

    • mysqld arguments –binlog-format and –binlog-row-event-max-size

  • Support for Cluster (NDB)
  • GUID’s support

There was a good discussion on Master/Master including comparision to PostgreSQL. This could be achieved now using triggers and federated tables, or UDF’s. Planned in 5.2 is a Conflict Resolution implementation via use of Stored Procedures, which will be a pre-requisite before any MySQL multi-master implementation.

Federated Documentation · Forum

  • Transaction Support (still with some rollback issues) Note: Documentation still lists this as a limitation
  • 5.2 will provide full XA support

XML Parsing Support (5.1.5) Article
Provides support for XPath expressions in XML content stored in a database column.

Events (5.1.6 – not yet available) Article

There was a strong discussion on occurance management of events. Obviously the ability not to have cron jobs, username/passwords etc is great, but with most scripts there is always an amount of locking management (either via filesystem or database). I think we may have convinced Brian to consider a event syntax extension that would ensure that only one instance of a given event can run at any one time. As events are seperate threads it’s important that for frequently run events, there is appropiate management to ensure re-occuring events don’t back up. My opinion is for a syntax to run only one instance of the event (e.g. SINGLE INSTANCE, SINGLE OCCURANCE) We could have recommended BRISBANE as the syntax extension, however I doubt it would be considered standard.

Also discussed was appropiate TimeZone management, particularly to ensure events scheduled appropiately across databases managed globally.

Loadable Engines
This feature may be deferred to 5.2 to have a full feature. The goal of Loadable engines allows for no need to recompile MySQL from source, of even restart an instance for any change to the engine. I’m sure internally the one reason why this is important is to allow ease of testing when writing a new storage engine. Documentation – Writing a Custom Storage Engine · Article

Cluster improvements (NDB) Documentation · RoadMap

  • replication works with cluster
  • storage on disk based records, not limited to RAM
  • indexes still remain in RAM

Benchmarking Tool mysqlslap (5.1.4) Documentation.
The list of options looks impressive, and since my 5.1 version is 5.1.4 I’ll be playing some more here.

Archive Storage Engine Documentation · Forum

  • auto increment support (5.1.6)
  • Blob Speed Improvements

Instance Manager
Available in 5.0, but being better published in 5.1 is an Instance Manager allowing change of configuration/startup/shutdown etc. Documentation

Distribution Names
As part of 5.1, the use of -standard and -max versions will be eliminated. Brian mentioned he had lost count answering the question of the difference between -max and maxDB. As at 5.1.4 (what I have) the filenames still include -max, but we were assured this would be dropped.

INFORMATION_SCHEMA
Of course as part of these changes, there will be a number of new tables in the INFORMATION_SCHEMA including ENGINES (5.1.5), PLUGINS (5.1.5), PARTITIONS (5.1.6), EVENTS (5.1.6).
Of note, there is a FILES (5.1.6) for the storage of table information?

Other References
What’s New in MySQL 5.1 MySQL Change History for Version 5.1

Future Directions for MySQL

  • This year will also be a GUI Enterprise Manager for the community edition
  • FULLTEXT for other storage engines
  • More Transaction Engines
  • Federate Tables to non MySQL tables via JDBC
  • FULLTEXT loadable parser written in C
  • Alternative stored procedure languages
  • Replication Conflict Resolution Management by user definable stored procedures
  • myforge – A soureforge/freshmeat like open source system for MySQL projects

Other Discussions

There were a number of post talk discussions. Those of interest to me included running other languages in stored procedures (myPerl/myPhp user contributions available at Brian’s open source site www.tangent.org), increasing exposure to ISP’s to offer hosting with MySQL 5.0, including best practice guidelines, and of most discussion Asterisk The Open Source PBX.

Solving the GLIBCXX_3.4.4, GLIBCXX_3.4.5, GLIBCXX_3.4.6 error

by

Let’s review the problem. I’ve got this on a number of occasions and different libraries. Here are some typical error conditions.

./mysql-workbench-bin: /usr/lib/libstdc++.so.6: version `GLIBCXX_3.4.5' not found (required by ./mysql-workbench-bin)
Error: Missing Dependency: libstdc++.so.6(GLIBCXX_3.4.6) is needed by package glibmm24

Special thanks to my new Guru friend Alfredo who put me on the path.

$ su -
$ cd /src/rpm
$ wget ftp://rpmfind.net/linux/fedora/extras/4/SRPMS/libsigc++20-2.0.11-1.src.rpm
$ rpmbuild --rebuild libsigc++20-2.0.11-1.src.rpm
$ cd /usr/src/redhat/RPMS/i386/
$ rpm -Uvh libsigc++20*.rpm
Preparing...                ########################################### [100%]
   1:libsigc++20            ########################################### [ 50%]
   2:libsigc++20-devel      ########################################### [100%]

Woot! It was that simple, download the rpm source, compile and install.

What does this ultimately mean? Well, something in your OS configuration isn’t the same as the platform used to build the rpm’s. In my case, I’m running CentOS 4.2, which was been upgraded from CentOS 4.0 via the Network Notification Tool. I’m also trying to install a Fedora FC4 rpm, on a RHEL platform. Anyway, it’s a simple solution and it works and that’s all we need for those that get stuck with this problem.

A detailed History of the problem

$ su -
$ yum install libsigc++20
Setting up Install Process
Setting up repositories
addons                    100% |=========================|  951 B    00:00
update                    100% |=========================|  951 B    00:00
nr-production             100% |=========================| 1.0 kB    00:00
base                      100% |=========================| 1.1 kB    00:00
nr-testing                100% |=========================| 1.0 kB    00:00
nr-mono                   100% |=========================| 1.0 kB    00:00
freshrpms                 100% |=========================|  951 B    00:00
extras                    100% |=========================| 1.1 kB    00:00
Reading repository metadata in from local files
primary.xml.gz            100% |=========================|  35 kB    00:01
nr-mono   : ################################################## 143/143
Added 131 new packages, deleted 87 old in 1.31 seconds
Parsing package install arguments
Resolving Dependencies
--> Populating transaction set with selected packages. Please wait.
---> Package libsigc++20.i386 0:2.0.17-1.1.fc4.nr set to be updated
--> Running transaction check
--> Processing Dependency: libstdc++.so.6(GLIBCXX_3.4.4) for package: libsigc++20
--> Processing Dependency: libstdc++.so.6(GLIBCXX_3.4.6) for package: libsigc++20
--> Finished Dependency Resolution
Error: Missing Dependency: libstdc++.so.6(GLIBCXX_3.4.4) is needed by package libsigc++20
Error: Missing Dependency: libstdc++.so.6(GLIBCXX_3.4.6) is needed by package libsigc++20

So doing some investigation.

$ yum info libstdc++
Setting up repositories
addons                    100% |=========================|  951 B    00:00
update                    100% |=========================|  951 B    00:00
nr-production             100% |=========================| 1.0 kB    00:00
base                      100% |=========================| 1.1 kB    00:00
nr-testing                100% |=========================| 1.0 kB    00:00
nr-mono                   100% |=========================| 1.0 kB    00:00
freshrpms                 100% |=========================|  951 B    00:00
extras                    100% |=========================| 1.1 kB    00:00
Reading repository metadata in from local files
Installed Packages
Name   : libstdc++
Arch   : i386
Version: 3.4.4
Release: 2
Size   : 774 k
Repo   : installed
Summary: GNU Standard C++ Library

Description:
 The libstdc++ package contains a rewritten standard compliant GCC Standard
C++ Library.


$ rpm -ql libstdc++
/usr/lib/libstdc++.so.6
/usr/lib/libstdc++.so.6.0.3


$ rpm -qaR libstdc++
/sbin/ldconfig
/sbin/ldconfig
libc.so.6
libc.so.6(GLIBC_2.0)
libc.so.6(GLIBC_2.1)
libc.so.6(GLIBC_2.1.3)
libc.so.6(GLIBC_2.2)
libc.so.6(GLIBC_2.3)
libgcc_s.so.1
libgcc_s.so.1(GCC_3.0)
libgcc_s.so.1(GCC_3.3)
libgcc_s.so.1(GLIBC_2.0)
libm.so.6
rpmlib(CompressedFileNames) < = 3.0.4-1
rpmlib(PayloadFilesHavePrefix) <= 4.0-1
rpmlib(VersionedDependencies) <= 3.0.3-1

Another Enterprise LAMP stack provider

by

ActiveGrid, the Enterprise LAMP company, provides a service-oriented application platform built on the lightweight architecture of the proven LAMP software infrastructure stack. ActiveGrid Enterprise LAMP simplifies and speeds the development of service-oriented applications that weave together existing enterprise systems into new rich web applications and services. ActiveGrid Enterprise LAMP applications can be flexibly deployed on grids of commodity machines or at virtually any ISP.

Please see http://www.activegrid.com for more information.

Building MySQL Workbench 1.0.1 for Linux (Part 2)

by

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

Blog/Wiki Spamming – What makes your blood boil

by

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

MySQL Sakila Sample Application

by

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.

MySQL Workbench 1.0.1 for Linux

by

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.

Downgrading a MySQL schema from 5 to 4 (Part 2)

by

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.

Sequences in MySQL

by

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

Support for Technology Stacks

by

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.

The challenges of compiling non working Open Source (Part 2)?

by

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.

The challenges of compiling non working Open Source?

by

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.

Database Modelling Software for MySQL

by

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.

How many installations, and just what are they doing?

by

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.

Ruby

by

Being a little despondent regarding Spring, a framework I’ve chosen to skill up in Read More, I’ve changed tack to investigate further Ruby. I was in a training demonstration of Ruby late last year, I’ve had other colleagues talk about it, and in a number of readings of late, Ruby has been making an impact, so time to delve in. I’ve got my working notes on my Wiki, and it took all of a few minutes to be operational. There appears a good wealth of reference material available including at least 2 online books. You can check out these in my Ruby References section.

Here is a comment from one of the current books I’m reading now. Beyond Java.

My partner and I decided to implement a small part of the application in Ruby On Rails, a highly productive web-based programming framework. We did this not to satisfy our customer, but to satisfy a little intellectual curiosity. The results astounded us:

  • For the rewrite, we programmed faster. Mush faster. It took Justin, my lead programmer, four nights to build what it had taken four months to build in Java (RB: They were using Hibernate,Spring and Web Work). We estimated that we were between 5 and 10 times more productive.
  • We generated one-fourth the lines of code; one-fifth if you consider configuration files
  • The productivity gains held up after we moved beyond the rewrite.
  • The Ruby on Rails version of the application performed faster. This is probably not true of all possible use cases, but for our application, the ROR active record persistence strategy trumped Hibernates’s Object Relational Mapping (ORM), at least with minimal tuning.
  • The customer cared much more about productivity then being on a safe Java Foundation. (RB: Highlighted)

As you can well imagine, this shook my world view down to the foundation.”

From Beyond Java. by Bruce. A. Tate pg 3-4.

Linux Format Reader Awards 2006

by

The Linux Format magazine is having it’s annual reader awards in a number of categories.

These include (I’ve include my picks after each category):

You can nominate at http://www.linuxformat.co.uk/nominate/. Nomimations close Friday 10 Feb 2006.

Adding to the Library Collection

by

I took the chance today to order some books from Amazon today to add to the library. Of course I’m still reading 2 current books Spring in Action and the MySQL Certification Study Guide in order to site the second MySQL Professional Certification Exam.

As with most things, you start off looking or reading on the web for something and you end up completely somewhere else. In this case, it was looking at Linux Software Labs (Australia) at the price of their Linux Distribution CD’s, which lead me to the book Beyond Java listed on their site. Called my local computer book store, but not being open (Boxing Day Public Holiday), lead me to go, “well I’ve been meaning to order some books from Amazon”, what were they again. So this lead me to coming up with a whole new list, and I figured for the cost of freight to Australia, I may as well order a few. So here is what I got.

Better, Faster, Lighter Java, MySQL (3rd Edition) (Developer’s Library) , High Performance MySQL,and of course Beyond Java.

The hard part now being the waiting 6-10 days.

XP Group in Brisbane

by

Brisbane has another XP Group. Just found out about it. Info can be found at http://groups.google.com/group/Brisbane-XP. I’ve been involved in some part in 2 previous groups in Brisbane.

I’m thinking about some ideas myself, I’ve got all the XP skills, however I’m now skilling up in Spring (a full-stack Java/J2EE application framework) and Hibernate (a powerful, ultra-high performance object/relational persistence and query service for Java). And I’ve got 2 other friends in similar positions.

Wouldn’t it be great if for 6 to 8 weeks, a few hours a week we could work on a project honning traditional XP as well as having some experience people in technologies helping others. Of course in comes back to some giving all to others, but I’m sure it doesn’t have to be that way.

Speaking at MySQL Users Group

by

I’m preparing to speak at the next MySQL Brisbane Users Group in Febraury 2006. My topic will be Know your competitor – A MySQL Developers Guide to Using Oracle Express Edition. You can get a full copy of my presentation slides at my Articles Page.

Having a strong background in Oracle, and having been using MySQL for the past 5 years, the release of Oracle Database 10g Express Edition (XE) as a Free offering (with limitations of 1 CPU, 1GB Ram, and 4GB disk) is an interesting move by Oracle.

I’ve written a number of recent comments on various Oracle/MySQL things including Responses to some Oracle v’s MySQL Questions, How can Oracle 10g Express Edition target MySQL?, Oracle 10g Express Edition Target Audience. Is it MySQL?, Oracle 10g Express, Free v’s Open Source and OFA.

The question could be posed, what relevence does this have to MySQL developers? Well, in some respects very little, but in others, both knowing about your competitor a little more, being able to see their offering, and in particular in comparision to MySQL can help in a level of understanding in Database differences. I am hoping that from the discussions, people will consider some approaches to design and development that is more “database compatible”, regardless of which database.

Will Oracle 10g Express Edition take off, well difficult question, there are many target markets, will it compete with MySQL in Open Source, hopefully my talk will sporn some discussion of peoples experiences in the various organisations and businesses represented in our meetings.

Upcoming Open Source Conference Presentation

by

I’ve been working recently on a paper I’m presenting to a conference in February 2006 titled Implementing Open Source for Optimal Business Performance. I’ve got the final glossy brochure yesterday so I now have something to show everybody. View Here (Be warned it’s a little bright)

The topic I have been asked to speak on is Overcoming the Challenges of Establishing Service and Support Channels. My notes are still in the early stage, but are available at http://wiki.arabx.com.au/index.php/ARABX_Articles:Overcoming_Service_and_Support_Channels

Conference Details on Ark Group Web Site

Myths Open Source Developers Tell Ourselves

by

Some interesting points from this ONLamp article on Myths Open Source Developers Tell Ourselves

Publishing your Code Will Attract Many Skilled and Frequent Contributors
Myth: Publicly releasing open source code will attract flurries of patches and new contributors.
Reality: You’ll be lucky to hear from people merely using your code, much less those interested in modifying it.

Feature Freezes Help Stability
Myth: Stopping new development for weeks or months to fix bugs is the best way to produce stable, polished software.
Reality: Stopping new development for awhile to find and fix unknown bugs is fine. That’s only a part of writing good software.

The Best Way to Learn a Project is to Fix its Bugs and Read its Code
Myth: New developers interested in the project will best learn the project by fixing bugs and reading the source code.
Reality: Reading code is difficult. Fixing bugs is difficult and probably something you don’t want to do anyway. While giving someone unglamorous work is a good way to test his dedication, it relies on unstructured learning by osmosis.

Packaging Doesn’t Matter
Myth: Installation and configuration aren’t as important as making the source available.
Reality: If it takes too much work just to get the software working, many people will silently quit.

It’s Better to Start from Scratch
Myth: Bad or unappealing code or projects should be thrown away completely.
Reality: Solving the same simple problems again and again wastes time that could be applied to solving new, larger problems.

Programs Suck; Frameworks Rule!
Myth: It’s better to provide a framework for lots of people to solve lots of problems than to solve only one problem well.
Reality: It’s really hard to write a good framework unless you’re already using it to solve at least one real problem.

I’ll Do it Right *This* Time
Myth: Even though your previous code was buggy, undocumented, hard to maintain, or slow, your next attempt will be perfect.
Reality: If you weren’t disciplined then, why would you be disciplined now?

Warnings Are OK
Myth: Warnings are just warnings. They’re not errors and no one really cares about them.
Reality: Warnings can hide real problems, especially if you get used to them.

End Users Love Tracking CVS
Myth: Users don’t mind upgrading to the latest version from CVS for a bugfix or a long-awaited feature.
Reality: If it’s difficult for you to provide important bugfixes for previous releases, your CVS tree probably isn’t very stable.

A better approach to using China for software development

by

India and China are the next powerhouses of software development, simply due to the numbers, but I’ve never heard a good report (maybe I have to dig deeper). My recent experiences are with Australian companies placing call centres in these countries, and almost always the language barrier is a clear limit.

As part of an upcoming conference paper I’m giving I have been looking more closer at the software options available, and I came across an interesting concept that has the background funding to get off the ground (a common problem in startups), and addresses a number of issues including the language barrier (which is less prominent with code).

Sinocode (www.sinocode.com) is the new generation in Offshore Development Centres (ODCs), delivering high value developer expertise from China. Our service offers:

  • Strong economic value in robust software solutions;
  • Proven western style management expertise; and,
  • Highly talented staff.

All of these drivers underpin our proven capability to execute. Execution, on time and on budget, is our key attraction.

(This is their sales pitch, not mine)

Some more reading:
Article in the Australian on 4th October 2005 Ernst places faith in China
An ABC radio interview on 30th August 2005 IT entreprenuer Lloyd Ernst

Responses to some Oracle v's MySQL Questions

by

I was asked a few questions by a reporter thru a collegue, here is an extract of the discussion.

1) Based on your initial experience with Oracle Database Express Edition, what are your initial thoughts on the product in terms of meeting developer needs?

Installing Oracle 10g Express Edition was a breeze. (Article). In the past Oracle products have been more difficult to install, however this has gradually improved with the more recent version releases of 8i, 9i and 10g. This easy installation via rpm under Linux, in particular the inclusion of HTMLDB provides an ideal database environment that is functional in just a few minutes. This is an important first step in gaining initial developer support.

With the database installation, the Web Based HTMLDB Interface and a sound amount of developer articles online at the Oracle Technology Network ( http://otn.oracle.com), there is a lot of information to meet a developers need. In particular, HTMLDB which offers Database Administration, general SQL tools, easy data management and a complete web forms development environment. There is a learning curve in understanding this functionality, however it is an extensive and powerful tool for developers, and requires a minimum of syntax specific knowledge. With the release of Raptor in December, Oracle will have a visual tool for development, which will complement this offering in meeting developer needs.

2) As a mySQL developer, do you think it is on par with proprietary database offerings?

MySQL celebrated it’s 10 anniversary this year, and with the Release of MySQL 5.0, gave the open source community a product to match the strengths of others products with new functionality including views, stored procedures, triggers and a data dictionary. Oracle provided these features with Oracle Version 7, released in 1992, long before MySQL was even a company. In a commercial world, MySQL now offers the strengths of an Oracle Database Product, however Oracle has grown extensively and offers not only a commercial database product but an entire suite of products. MySQL is on par in a great number of areas. Granted they are catching up quickly, and the Open Source community offers features that commercial companies cannot. This includes the ability for open source companies to change, adapt and correct more quickly, bringing features and functionality to the marketplace more often, and more open support and knowledge bases enabling users to gain easier and open access. These two points offer downsides that commercial product offerings in turn offer back, including more commercial strength functionality, and more consistent and managed knowledge management, particularly in support.

3) Do you think the efforts of vendors like Microsoft and Oracle to gain mindshare with entry level versions of their products would entice mySQL developers like you to consider their higher-end database products?

I think that Oracle 10g Express Edition caters to different markets. For existing users and developers with Oracle, the opportunity to provide their products for resale at a reduced cost, with a zero cost Oracle license is a definite market and opportunity for these Oracle Partners. I think the education sector, including universities is another potential market, where a genuine free product can be used in teaching, and then practical application, and lead into usage within the professional employment. The third sector, the competitor market, which includes MySQL and Microsoft is more complicated. Oracle will need to go further to bridge the gap in convenience for MySQL developers. To sight a few examples, MySQL is generally available now in online hosting packages, on Linux Distros. It’s quick, easy and convinient to access, and is gaining significant popularity in the open source community, a good barometer in success in a developer driven environment. When Oracle can provide it’s products within a Linux Distro and you can see Online Hosting providers offering packages including the Oracle 10g Express Edition database, gaining market share will at least be more convenient.
One other strength, is MySQL being part of LAMP, and the significant wealth of open source products developed with MySQL, makes it hard for Oracle to ever gain ground in these continued product offerings. Perhaps if Oracle considered a bounty system, much like Ubuntu, and enticed developers to ensure Open Source products were Oracle compatible, would further entice MySQL developers. (Article)
The downside of these two examples, is there is no return in investment to Oracle, so it will depend on which markets are ultimately intended for Oracle 10g Express Edition. (Article)

4) Among the proprietary database vendors, who do you think has the best offering in your opinion, and where would mySQL fit in?

In this marketplace, there is a platitude of Database Offerings (can provide a list if you want), Oracle has always been the market leader in the commercial space, and there still is no other offering to match this. Historically, software development with databases was undertaken by IT professionals and there were a small number of commercial products available. Oracle gained a large market share in the past 20 years and has kept up with technology advancement. In the past 10 years, the Open Source community has changed the shape of the product offerings, and has also change the type of developers. There is a divide between the professional developers with qualifications and specific experience in software development in a structured development environment, and the new age of developers that have easy access to tools and information, and can easily contributed to an open source project that may have hundreds or thousands of developers worldwide, but they do at times lack solid grounding, and this can lead to bad habits. Depending on your requirements, a large commercial organisation, or a small webstore and blog, there are different needs, and you can’t therefore compare database products based solely on features. You need to include the customer requirements, as well as the ability for a database product to grow and adapt to your business in the ever changing environment.

I think in this day and age, there needs to be a tolerance between multiple database offerings within organisations. As the CIO or CTO of a large organisation, I would hope that different needs within the organisation would lead to two database products in use and synergy, both Oracle and MySQL.

How can Oracle 10g Express Edition target MySQL?

by

As I mentioned earlier, is MySQL a target of the new Oracle 10g Express Edition. Maybe not specifically, but let’s assume it’s on the radar screen. What can Oracle do to woe MySQL users and developers?

I see distinct marketing will be required for Oracle 10g Express Edition, marketing for example to existing partners must be different to “Competitor Marketing”. While I’m sure Oracle will now be able to get benefits from Parters trying to sell their products, now being able to reduce costs to customers (at least initially). Other partners that never considered selling developed applications due to the license cost may now reconsider.

But back onto MySQL. Oracle needs to target specific information to MySQL. OTN Technical Articles for Developers and Architects has nothing on MySQL. I can see the need for a Cookbook to attract MySQL users. The Oracle 10g Express Edition page has some information on developer docs, but not on Conversion, Migration and comparison. Some more opportunity here!

While on Migration, I attempted using the Oracle Migration Workbench to migrate my Version 5 database, without success. MySQL would be low on the agenda if a Version 5 migration was not available for a product that’s been in a pre-production release for most of 2005.

So, there is a clear need for some documentation, Quick Start Guides and How To’s for the MySQL community. Obviously being able to do a database migration also very high. But again this is not a practical application, it’s just the icing.
Until some Open Source projects actively engage in Oracle 10g Express Edition, it will always be a novelty to the MySQL community that won’t get off the ground.

I can see one way to jump start the process. Oracle could offer bounties, much like Ubuntu, to get some of the large open source projects Oracle 10g Express Edition compatible.
For example, http://www.phpbb.com, http://www.oscommerce.com, http://www.phpwebsite.appstate.edu, http://www.dotproject.net.

Now offering $200 per project, that would shake some things up!

Oracle 10g Express Edition Target Audience. Is it MySQL?

by

Just where is Oracle planning on targeting the new Oracle 10g Express Edition?
The obvious answer would be to counter the arch nemesis Microsoft, and the low end product offerings, like the MS SQL Server and the low end free engine MSDE. I didn’t realise to recently, that Microsoft have finally released the next version of MS SQL Server, being 2005, and at the same time provide a free cut down offering, strangely enough called “Express Edition”. It’s taken Microsoft 5 years. Makes you wonder if Database Technology is a high priority!

Is Oracle also now threatened in anyway by MySQL? I think not, however the continued growth of MySQL, it’s availability on any Linux server and in many distros, and now with the MySQL 5.0 GA release with features like Views, Triggers, Stored Procedures, Information Schema and ANSI SQL compliance allows MySQL to compare itself in features with Oracle. Certainly from our local MySQL Users Group, the inside MySQL news is it’s targeted against Microsoft.

Of course Oracle has been around for quite some time, and when I started working with Oracle 7 and Oracle Designer 1.2 back in 1996, these features were already available.

Oracle 10g Express Edition is a specialist database product from a database company, and with the wealth of knowledge that has lead the lasted version 10, everybody else has a long way to go to match this strength. From MySQL, the feature set even in Version 3 and 4, was adequate for application development in many areas, and the community is driving additional features to match other database product offerings.

I think it will be interesting to see what additional information Oracle releases targeted against the MySQL community. I’ve got a number of thoughts here, will post more details soon.

Oracle 10g Express, Free v's Open Source and OFA

by

In lunching with an old Oracle Friend, the topic turned to Oracle 10g Express Edition, and we discussed the pros and cons for organisations. The first thing he asked me was, “Have you tried loading the database larger then the 4G limit yet”. Some People?

In general the consensus seemed that it was viable for a small organisation, good for a startup company to get into a no cost entry solution, but appealing to those venture capitalists as you have a definite growth pattern.

I’m warming to more actively pursing Oracle 10g Express Edition, especially with HTMLDB, and at present while preparing to submit a paper for an upcoming Open Source conference which I hope is accepted, I’ve allocated some time on the significant topic of Open Source Verses Free. Stay tuned.

It dawned on me later in the day to more closely investigate the Oracle RPM installation and where exactly everything went in the FileSystem. I was surprised that it was not OFA compliant.

What if an existing Oracle customer with licenses was trialling Oracle 10g Express Edition and using the same staff.
Or more importantly, what if a new Oracle person picked up some bad habits from the Oracle 10g Express Edition, and when placed in a more commercial Oracle DBA environment got caught out?

I’m a Database Modeler, I’ve studied it, and I’ve practiced it, I’ve applied it and I’ve learnt some things from time to time of other “experts”, but nowadays, you put a database in somebody’s hands, like MySQL which is readily available, and all of a sudden you have people claiming to know and understand database design. Food for thought.

I hope from this new Oracle 10g Express product, also comes some good support from the Oracle Community to ensure a high standard in all things databases.