Contributing to JMeter

As part of my using JMeter for the purpose of testing a new Transactional storage engine PBXT for MySQL, I’ve been investigating the best approach for handling transactions. Read more about earlier decisions at my earlier post Testing a new MySQL Transactional Storage Engine.

I found that the JMeter JDBC Sampler only supports SELECT and UPDATE Statements, and not calls to stored procedures. This is just one approach I’m considering taking.

Well, I guess it’s time to contribute code to an Apache Project. I’ve modified code and logged bugs before for Tomcat, but this will be my first attempt of modify code and submit.

A summary of what I did (really for my own short term memory):

Now I just have to wait to see if it’s accepted. Regardless, it works for me. And that’s Open Source. FREEDOM

svn checkout http://svn.apache.org/repos/asf/jakarta/jmeter/trunk/ jmeter

$ svn diff JDBCSampler.java > JDBCSampler.java.patch
$ cat  JDBCSampler.java.patch
Index: JDBCSampler.java
===================================================================
--- JDBCSampler.java    (revision 388876)
+++ JDBCSampler.java    (working copy)
@@ -23,6 +23,7 @@
 import java.sql.ResultSetMetaData;
 import java.sql.SQLException;
 import java.sql.Statement;
+import java.sql.CallableStatement;

 import org.apache.avalon.excalibur.datasource.DataSourceComponent;
 import org.apache.jmeter.samplers.Entry;
@@ -45,6 +46,8 @@

        public static final String QUERY = "query";
        public static final String SELECT = "Select Statement";
+       public static final String UPDATE = "Update Statement";
+       public static final String STATEMENT = "Call Statement";

        public String query = "";

@@ -69,6 +72,7 @@
                log.debug("DataSourceComponent: " + pool);
                Connection conn = null;
                Statement stmt = null;
+               CallableStatement cs = null;

                try {

@@ -88,14 +92,19 @@
                                        Data data = getDataFromResultSet(rs);
                                        res.setResponseData(data.toString().getBytes());
                                } finally {
-                                       if (rs != null) {
-                                               try {
-                                                       rs.close();
-                                               } catch (SQLException exc) {
-                                                       log.warn("Error closing ResultSet", exc);
-                                               }
-                                       }
+                                       close(rs);
                                }
+                       // execute stored procedure
+                       } else if (STATEMENT.equals(getQueryType())) {
+                               try {
+                                       cs = conn.prepareCall(getQuery());
+                                       cs.execute();
+                                       String results = "Executed";
+                                       res.setResponseData(results.getBytes());
+                               } finally {
+                                       close(cs);
+                               }
+                       // Insert/Update/Delete statement
                        } else {
                                stmt.execute(getQuery());
                                int updateCount = stmt.getUpdateCount();
@@ -112,20 +121,8 @@
                        res.setResponseMessage(ex.toString());
                        res.setSuccessful(false);
                } finally {
-                       if (stmt != null) {
-                               try {
-                                       stmt.close();
-                               } catch (SQLException ex) {
-                                       log.warn("Error closing statement", ex);
-                               }
-                       }
-                       if (conn != null) {
-                               try {
-                                       conn.close();
-                               } catch (SQLException ex) {
-                                       log.warn("Error closing connection", ex);
-                               }
-                       }
+                       close(stmt);
+                       close(conn);
                }

                res.sampleEnd();
@@ -164,6 +161,38 @@
                return data;
        }

+       public static void close(Connection c) {
+               try {
+                       if (c != null) c.close();
+               } catch (SQLException e) {
+                       log.warn("Error closing Connection", e);
+               }
+       }
+
+       public static void close(Statement s) {
+               try {
+                       if (s != null) s.close();
+               } catch (SQLException e) {
+                       log.warn("Error closing Statement", e);
+               }
+       }
+
+       public static void close(CallableStatement cs) {
+               try {
+                       if (cs != null) cs.close();
+               } catch (SQLException e) {
+                       log.warn("Error closing CallableStatement", e);
+               }
+       }
+
+       public static void close(ResultSet rs) {
+               try {
+                       if (rs != null) rs.close();
+               } catch (SQLException e) {
+                       log.warn("Error closing ResultSet", e);
+               }
+       }
+
        public String getQuery() {
                return query;
        }

$ svn diff JDBCSamplerBeanInfo.java > JDBCSamplerBeanInfo.java.patch
$ cat JDBCSamplerBeanInfo.java.patch
Index: JDBCSamplerBeanInfo.java
===================================================================
--- JDBCSamplerBeanInfo.java    (revision 388876)
+++ JDBCSamplerBeanInfo.java    (working copy)
@@ -50,7 +50,7 @@
                p.setValue(NOT_UNDEFINED, Boolean.TRUE);
                p.setValue(DEFAULT, JDBCSampler.SELECT);
                p.setValue(NOT_OTHER,Boolean.TRUE);
-               p.setValue(TAGS,new String[]{JDBCSampler.SELECT,"Update Statement"});
+               p.setValue(TAGS,new String[]{JDBCSampler.SELECT,JDBCSampler.UPDATE,JDBCSampler.STATEMENT});

                p = property("query");
                p.setValue(NOT_UNDEFINED, Boolean.TRUE);

Update

Good to know somebody read my post, and responded positively. The quickest way for patches is to log a Bugzilla request. Seemed somebody already had, so it was easy for me to just to contribute to Bug #38682

Emulating Oracle Output Functionality

Updated 28-mar-2006
There really is no way to do a comparision by numbers in features and functionality when it comes to Oracle and MySQL in the area of Stored Procedures and Triggers. Oracle does provide a far greater and extensive features list, however having more only means it has more.

We all know this, MySQL is a growing evolving database product, having just celebrated it’s tenth anniversary, Oracle on the other hand, will next year be 30, and has had significant funding in R&D being up until recently the second largest software company world wide. However, being open source, MySQL has the advantages of being lean and mean, providing only functionality users actually want and use, and has the community and organisation that is focussed clearly on providing new and improved functionality that users need.

I’ll have a lot more to speak about on this topic at my MySQL User Conference presentation MySQL for Oracle Developers next month, however in the preceeding weeks I thought I’d open some discussion and debate on various features, particularly in the area of Stored Procedures.

Here are some initial points, I’d like to talk about today. The purpose of this discussion is to highlight the difference between these two products, and possibilly other RDBMS products. While a RDBMS offers a feature, I am not stating that MySQL should necessarily also have this feature, however as part of comparing products in these articles, I am comparing existing Oracle Functionality to MySQL, so it will generally involve the description of functionality that is not presently comparable.

  1. Package Space
  2. Provider Supplied Procedures

1. Package Space

MySQL provides for Stored Procedures and Functions only the definition of individual procedures and functions. There is no way to group them into logical groupings.

Oracle provides the functionality of Package Space. Groups of procedures and functions can be defined within a package. Consider it like a wrapper of a group of procedures and functions. The package definition consists of 2 parts, the header section, which defines the scope of the procedures and functions, and the body which defines the actual procedures and functions. An added advantage of this that within Oracle you can protect your package body code (making it unavailable for view). I’ll discuss this more at a later time.

I’d like to see MySQL consider a package syntax particularly as businesses start to work more extensively with stored procedures and functions in larger enterprise applications. UPDATE: As stated by Roland in the comments, you can register your support for this functionality at MySQL under Bug #11696.

Within MySQL you can emulate package name, at least in syntax. You can use a dot ‘.’ within a procedure name, unlike this restriction for tables. The down side is the call must be quoted. I’ll discuss this more next.

2. Provider Supplied Procedures

Oracle provides an extensive list of supplied procedures and functions, defined in many packages. One of the earliest you learn in Oracle development is DBMS_OUTPUT.PUT_LINE. As the name indicates, this allows your to output a line of information. This is a common means of debugging output. Andrew Gilfrin post on Debugging Stored Procedures in MySQL raises this point, and an interesting solution using a debugging table. While this functionality works, one of my goals is to provide compability functions so that any migration of code could be simplified. Here is a much simplier solution.

$ more dbms_output.put_line.mysql
DELIMITER //
DROP PROCEDURE `dbms_output.put_line`
//
CREATE PROCEDURE `dbms_output.put_line` (output  VARCHAR(255))
BEGIN
  SELECT output;
END;
//
DELIMITER ;

A MySQL Usage:

mysql> CALL `dbms_output.put_line` ('hello world');

A comparision Oracle Usage:

SQL> SET SERVER OUTPUT ON
SQL> BEGIN
SQL> DBMS_OUTPUT.PUT_LINE ('hello world');
SQL> END;

This is as close the syntax can be. With MySQL you require the CALL and the backqoutes (`). Both things that can be easily added in an automated way.

References:
DBMS_OUTPUT Note: this is a public web version of the Version 8i manual online. Oracle provides online documentation, but you require a login (which is free) to the Oracle Technology Network.

Oracle8i Supplied Packages Reference. Again this is dated information, being an older version, but gives you an indication. For reference Oracle 7 released in 1993 first provided Package/Procedure/Function functionality.

Other

On a side note, I’d really like to see a IF EXISTS syntax for DROP PROCEDURE. IF EXISTS is an excellent MySQL extension. Oracle for stored procedures has the CREATE OR REPLACE syntax.

Just how many articles are at Planet MySQL?

I was trying to find an old article at Planet MySQL. One about a MySQL UDF to write to /var/log/messages. No luck.

Anyway, there is no search option on the site, and the latest addition of 10 entries per page makes it difficult to review pages. The RSS feed doesn’t give me a full option.

Anyway, it led me to look back in time to just how many articles are listed on Planet MySQL, and read some old stuff. I only came across it after I stumbled across the Brisbane MySQL Users Group back in Sep 2005.

Anyway the count was 2146. I’d like to see a stat on the home page of how many articles, and perhaps how many, last day, last week, last month.

MySQL Forge

I was reading Zack Urlocker’s MySQL Workbench Beta article and was keen to look at the Extensible architecture. Not much detail yet in the Figure Stylesheets, Scripts and Plugins, which will be good when it’s there, however it lead me to another secret.

The MySQL Forge. This was mentioned at the Brisbane MySQL Users Group Meeting with Brian Aker in January. There isn’t much content at present, but there is a Call for Content.

I don’t recall anybody blogging about it, or maybe I just missed it. Giuseppe Maxia should get his MySQL General Purpose Stored Routines Library up there. On that thought, I’ve been looking for a post several months ago, about a UDF that wrote to the system log /var/log/messages. Does anybody remember this?

Another dissappointing MySQL article

Another slightly disappointing article regarding MySQL, this one from a printed magazine. Below are my comments to the editor of Linux Format. The Dear Editor is an email link should others wish to make any comments. (Previous article comments What makes your blood boil?, Review of Database Magazine Article – “The Usual Suspects”)

Dear Editor,

I’ve recently subscribed to LXF, and have generally been very happy with the content in past months. I’m disappointed in your recent LXF77 article “Harness a database” Pg 57. Being a strong MySQL supporter, your article includes a number of practices which are less then ideal, and especially for the newly initiated, overly complicated when simplier alternatives exist.

I am happy to see that you had the current version 5.0.18. However in 7 years of using MySQL I’ve never had to compile from source, a binary has always been available, yet in your article you take the compile from source approach. While you may have done this to appease all possible readers of all POSIX variants, the binary for at least Linux could have been included.

In addition, much like your earlier article for Samba where you make reference to “most distros include this”, indeed MySQL is also included in most popular Linux distros.

You could have also made reference to the MySQL downloads area website. Infact, the MySQL 5.0 download area provides some 70+ dedicated distro versions for optimal usage. The site also states “For maximum stability and performance, we recommend that you use the binaries we provide.”

The second point that I do believe is completely unnecessary, and not recommended by MySQL, is the manual creation of user security. MySQL provides the CREATE USER command to achieve this. In your article, you would execute CREATE USER lxf@localhost IDENTIFIED BY ‘orangutan'; rather then an INSERT INTO user and a FLUSH PRIVILEGES.

Throughout your article you make no reference to the MySQL website, perhaps this is a policy, however it’s always ideal to have online references. At worst, a link to say www.linuxformat.co.uk/products/mysql (or appropiate) that provides links to the product information, in this case MySQL. This will allow you to undertake your necessary advertising, as well as track traffic.

I’m also sure, that contacting the MySQL Community Relations Manager for review of any MySQL content in future would be openly accepted, I’m even happy to review MySQL content in your magazine.

Atomicity, Consistency, Isolation, and Durability = ACID

ACID is the key transaction processing feature for a RDBMS. Without this, the integrity of the database cannot be guaranteed.

In Summary.

Atomicity is an all-or-none proposition.
Consistency guarantees that a transaction never leaves your database in a half-finished state.
Isolation keeps transactions separated from each other until they’re finished.
Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.

A clearer definition from the Wikipedia.

  • Atomicity refers to the ability of the DBMS to guarantee that either all of the tasks of a transaction are performed or none of them are. The transfer of funds can be completed or it can fail for a multitude of reasons, but atomicity guarantees that one account won’t be debited if the other is not credited as well.
  • Consistency refers to the database being in a legal state when the transaction begins and when it ends. This means that a transaction can’t break the rules, or integrity constraints, of the database. If an integrity constraint states that all accounts must have a positive balance, then any transaction violating this rule will be aborted.
  • Isolation refers to the ability of the application to make operations in a transaction appear isolated from all other operations. This means that no operation outside the transaction can ever see the data in an intermediate state; a bank manager can see the transferred funds on one account or the other, but never on both—even if she ran her query while the transfer was still being processed. More formally, isolation means the transaction history (or schedule) is serializable. For performance reasons, this ability is the most often relaxed constraint.
  • Durability refers to the guarantee that once the user has been notified of success, the transaction will persist, and not be undone. This means it will survive system failure, and that the database system has checked the integrity constraints and won’t need to abort the transaction. Typically, all transactions are written into a log that can be played back to recreate the system to its state right before the failure. A transaction can only be deemed committed after it is safely in the log.

Testing a new MySQL Transactional Storage Engine

As part of my A call to arms! post about a month ago, I’ve had a number of unofficial comments of support. In addition, I’ve also been approached to assist in the completion of a MySQL Transactional support engine. More information on the PBXT engine will be forthcoming soon by it’s creator.

Anyway, I’ve taken on the responsiblity of assisting in testing this new storage engine. This will also give me the excuse of being able to pursue some other ideas about the performance of differing storage engines for differing tables in business circumstances, such as MyIsam verses InnoDB in a highly OLTP environment. Part of testing will be ensure ACID conformance in varying situations and multi-concurrency use. Of course the ability to also do performance and load testing would be a obvious extension.

Considering how I’m going to benchmark is an interesting approach. I of course want to use Java, my choice of language at present. This presents a problem, in another factor towards performance, however by using Java, I’m simulating a more real world environment of a programming overhead and JDBC Connector rather then just raw performance output.

Laying out a plan would include an ability to have an existing database structure and data, be able to bulk define SQL statements and transactions, and parameterise SQL during transactions. I would need to be able to verify the state of database from the transactions, and clearly identify any invalid data. I would also need the ability for handling threads, and of course adequate reporting of my results.

As of MySQL 5.1.4, there is a supported benchmarking tool called mysqlslap in MySQL. I’ve discounted using this because I figured at this early stage, the documentation and exposure of this is of course limiting, and I’m sure I’d still need to perform other development.

Along comes JMeter. Within Java development I use JUnit quite extensively. This is key in the test-driven agile methodology approach of Extreme Programming. In discussion with this problem with a collegue on a new project, I found that JMeter was used for extensive load testing for web applications, but also performed database testing, and provides the support to integrate JUnit tests.

So yesterday I had a quick look at JMeter. The capabilities for defining, reporting and threading are quite complete. It took litterally minutes to install, configure, run an initial test and view results all in a GUI interface. A little more work gave me scripting handling of my initial tests. I’ve posted my initial investigations of JMeter – Performance Testing Software and JMeter and Ant Integration earlier.

With this behind me, I’ve just got to define the approach for more complete transactional tests, explictly confirming the results (I’m hoping to achieve this in custom JUnit tests). If I can solve this, then I can spend the most of time in the defining of adequate tests. Let’s see what the next few days work provides.

Latest MySQL Versions under VMWare

I made reference previously to Testing/Trialing new MySQL Releases using VMWare.

Well, I’ve just about completed my own Image for the lastest MySQL 5.0 (given I’m now running MySQL 5.1). I’m interested in sharing my experiences, and even providing some images for users if there is any demand out there.

What I’ve decided on is to use the VMWare supplied Browser Appliance which is Ubuntu 5.10. The great thing is the image autoboots into graphical mode, auto logins and loads a browser. My goal now is to get a suitable startup page describing the MySQL environment, links, manual etc.

The only requirements to run would be the Free VMware Player and a latest image.

I’m looking at providing a FTP and RSYNC download. Those savvy people will benefit a lot more from RSYNC when the image is upto 500MB.

I’m interested to know if anybody else would use this with the lastest 5.0, 5.1, 5.2 etc versions. Also, would it be of benefit to include other MySQL products such as Query Browser, Administrator, Workbench.

Check out the Innodb Site?

Have a look at InnoDB. Well, ok your lazy, so here’s a screen print below.
Plastered thoughout the top section is the word MySQL. It’s in the core banner blurb, there’s a logo, and even links to the MySQL Documentation. And right in the middle of all this is “Innobase OY is an Oracle Company”, with the standard Oracle Logo.

So my question would be, is this a good thing or a bad thing from a MySQL advertising perspective?
Does it help or hinder MySQL?
Does it show Oracle as being in partnership with MySQL? Is this good for MySQL to increase it’s exposure into the Oracle world?
Does inclusion of InnoDB now within Oracle traffic and links improve exposure to MySQL within search engines?

Frankly, I’m a little surprised that Oracle Legal hasn’t got onto this, I’m sure somewhere there would be a decree, that no competitor logos should be shown with the official Oracle logo.

I’d suspect that the existing MySQL community know the deal re Oracle, so perhaps this will expose more Oracle specific people to MySQL!

Oracle Comments

Some recent posts regarding Oracle (See Smart moves by MySQL AB and Larry Ellison still doesn’t understand open source) leads me to put in my 2 cents worth.

My background I’m sure like a lot of experienced MySQL people is in Oracle, and indeed in Ingres before that (starting in 1988). I have also worked for a number of years at Oracle Corporation. Ironically I started as their resident Ingres Specialist, in an international research project of DMS (Design & Migration Services) of re-engineering Ingres applications into an Oracle Designer Repository some 10 years ago in 1996. I of course moved into a number of other Oracle roles for clients following that. I still retain some contacts around the place.

It’s obvious Larry’s goal is to become more “Open Source”. This is of course a complex topic, and you can’t just say it like this. I’ll leave that for another day, but I’ve got some opinion on current trends.

It’s clear from recent purchases of JBoss, and SleepCat (Berkley DB) that Oracle has an intention of providing a suitable technology stack in a number of vertical marketspaces that Oracle presently cannot. For example, in the embedded space. Purchasing provides a much quicker solution, and also provides a different way to solve the problem instead of the legacy Oracle Product line. I haven’t seen any recent press, but I know that Xen was also in Oracle’s wallet space.

I would suspect that Oracle will move to providing a Stack solution (akin to SpikeSource for example) in a number of vertical spaces. These stacks will include non-traditional but now owner Oracle products, including Berkeley DB in lower end solutions. This along with the recent Oracle Express Edition (XE) free offering will enable Oracle to target market’s that were previously unavailable.

This is clearly not for a financial intention. There is no way to obtain a return on investment, however it’s all about upselling. By providing these offerings, Oracle now has a large marketplace to promote commercial product offerings.

Assuming, Oracle goes down this path, I’d assume they will also provide clear migration paths between stack offerings, as part of upselling. This will be more complex to achieve. I also suspect that Oracle will move towards a better Web 2.0 model, by providing products are no cost, but provide a service to support and maintain in these new marketspaces.

In closing, why did Oracle purchase InnoBase, the creators of the MySQL InnoDB Storage Engine? This question seems still unanswered. It doesn’t fit easily into the above ideas of differing technology stacks. Ultimately, money buys power, and I suspect it was just that, with no real future intentions or clear plan.

Update
Thanks for the comment about JBoss, yes, it’s not official, nor is Xen, but it took Oracle I think 15 months to get Peoplesoft.

I came across the Oracle Corporation Wikipedia entry in other work, the end of the history page reads a who’s who of acquisitions.

A call to arms!

With Oracle Corporation purchasing InnoBase, the company providing the InnoDB Storage Engine, and now reliable rumors of the acquisition of SleepyCat, the BDB Storage Engine, both key transactional storage engines for MySQL are effectively owned by a competitor.

While the is a strange and probably unchartered territory for both organisations, I’m personally concerned. I use InnoDB extensively, however if there was a comparable alternative within MySQL I’d consider switching out of principle. Is Oracle purchasing these organisations a bad thing? We don’t know. That’s the problem. While MySQL will undoubtly continue to provide these storage engines as part of the MySQL Database I believe a call to arms is needed.

It’s true that Oracle helped more general adoption of Linux when it announced this as it’s primary platform some years back. I’m sure with a middleware suite such as JBoss this will benefit from Oracle’s exposure, but will it benefit in it’s development? Will funding for development skew the product torwards what Oracle wants, not the community? Even things such as BerkleyDB being embedded in Open Office, and Google wanting to spend into the refinement of Open Office for possible web services options just makes you wonder whats happening. I’m on holiday’s trying to relax, and not do any reading, developing or communicating, but the one thing that makes my mind turn is, why is Oracle doing this?

My concerns are: look what happened to the acquired big commercial competitors, the PeopleSoft, Siebel, JD Edwards. Now, it’s a little difficult with the Open Source software, but I’m no licence expert. While it will continue to be available in current licencing options, I’m sure there has to be some concerns. Even something trivial like, all MySQL downloads that include these engines must first be registered on the website, and all these statistics must go to Oracle. Can they do this? I have no idea, but what if they could.

I’m sure internally in MySQL AB there are plans afoot for alternative transactional storage engines. The pluggable nature of these within MySQL makes it easy to move in this direction. I think sometimes, some functionality is kept close to heart, and you only here about it when some actual work is released.

I’m interested to guage reaction to see if a public working group should be setup to specifically tackle the issue of an independent transactional storage engine. Are there others out there that feel the same way I do. Now let me be clear, I’m not anti Oracle, infact rather pro Oracle, but I’m very anti Microsoft. If it was Microsoft buying these companies how would people react?

In my opinion this should be a bold announcement from MySQL now. In stating the development and release of a new Transactional Storage Engine this year, and then not evening mentioning InnoDB and BDB, they are downplaying the Oracle buy in, and emphasising a true Open Source Company option. In no means say they are no longer supporting InnoDB and BDB, but if the media exposure from MySQL continues to mention them, then it’s going to bleed into some reference back to Oracle.

I can’t contribute to the actual development in C++, if it was Java that would be a different matter, but as I move more away from hardcore coding there are plenty of other areas in which I could contribute.

InnoDB, BDB. What is Big Red Doing!

Last year saw a record number of acquisitions by Oracle Corporation. Of note was in October 2005 InnoBase (Read Press Release) which had a direct relationship with MySQL providing the InnoDB Storage Engine. It’s too early to tell what the impact to MySQL will be if any.

I’ve been in Singapore, and have not read any news in the past few days, but all information I’m receiving from those collegues in the know is that Sleepycat Software (the company behind Berkley DB, and the MySQL BDB Storage Engine) is now firmly in the sites of Oracle Acquisition. The rumors of JBoss is also definitely on the go. No official news from any Oracle Press Releases as yet.

So what is Oracle doing, and what impact will this have for MySQL? Both InnoDB and BDB are key transactional storage engines. Given that MySQL has the full capability for ease of plugin for new storage engines is this a prod to MySQL to consider another alternative?

If I wasn’t mistaken, it seems Larry Ellison is looking to be a big name in open source!

Auditing an existing MySQL Installation

Yesterday I ran into an old collegue that now runs quite a successful computer store chain and highly successful web store here in Australia. Long story short he was having some MySQL problems, so I offered to pass my eye over it. Now, given that they had some data corruption in stock levels not being correct (e.g. getting to an inventory count of -1), my first split second thought was inappropiate transaction management.

In thinking last night, what would I do as part of auditing an existing MySQL Installation and application quickly for reference and also to highlight issues etc, assuming I would have no access to any administrators or developers.

So what would you do? I made some preliminary notes, here’s the full account of what I did do.

Audit Steps

OS Specifics

$ mkdir /tmp/arabx
$ cd /tmp/arabx
# keep a quick copy of stuff
$ script
# Linux generals
$ hostname
$ uname -a
$ uptime
$ df
$ which mysql
$ mysql --version

MySQL Specifics

$ mysql -uroot -p mysql
mysql>  show variables;
mysql>  show status;
mysql>  show databases;
mysql>  show processlist;
mysql>  show full processlist;
mysql>  select host,user,passwd from user;
mysql> exit;
$ cat /etc/my.cnf
# error not found?
$ find / -name my.cnf
# Results
$ cat /etc/mysql/my.cnf
$ cp /etc/mysql/my.cnf .
$ cd /usr/local/mysql
$ du
$ cd data
$ ls -al
$ cp *.err /tmp/arabx  # for later review
# What, no database, surely they were not recording in the mysql database
# quick confirm there was another database from earlier list?
$ grep datadir /etc/mysql/my.cnf
# I see off somewhere else
$ cd /var/lib/mysql
$ ls -al
$ du
$ cp *.err /tmp/arabx  # for later review
$ cd /tmp/arabx
$ mysqldump --no-data  -u[user] -p [db] > schema.sql
$ grep TYPE schema.sql
# Observation:  All MyISAM, the first proof of my initial theory
$ mysql -u[user] -p [db]
mysql> show tables;
mysql> show tables status;
mysql>exit;
$ cd /tmp
$ tar cvfz arabx.tar.gz arabx
$ scp arabx.tar.gz ...

Application Specifics
I’m not going to detail the steps here as this is really very implementation dependant. What I did in summary was:

  • Identified website location on filesystem, general du, ls -l commands
  • View working screens showing Stock Count logs, Forfill Order and Product Return
  • Review code of these areas, as well as the data, confirming what was seen on screen via SQL

Recommendations

Immediate

  • Cleanup current directory of html files to remove old files, or old backup copies. This ensures no PC has some old page with bad code bookmarked
  • Cleanup /usr/local/mysql/data as it’s now defined in /var/local/mysql. It threw me with to data directories and error logs. It was only that the database directory was not in the first location, otherwise I may have missed it initially.
  • Stock Adjustment page needs to log an Adjustment history for Audit Trail (Only Sales and Returns were in Audit Trail, so it left a possible gap?)
  • Implementation of Transactions. Given the volume of transactions, and that it would appear that LOCK TABLES had been implemented but removed due to performance, obvious choice is implement InnoDB tables, and transaction management in code. (Quite some work)
  • Change of some columns from DATE to DATETIME to record the time of occurance. (Code was using NOW(), so that part’s already done). Or, implement some TIMESTAMP columns (as none were in use) and leverage MySQL standard functionality.

Medium Term

  • Running MySQL 4.0, which is fine, but the data corruption and lack of clean website code, leads to an easier solution for auditing via triggers.
  • Upgrade to MySQL 5.0 Key reason, triggers. This enables more audit analysis with even less need for or initial understanding of the application
  • Implement a CVS respository. Even for single development, it’s a habit that far outways the impact of a few more commands.
  • Review Backup strategies for HTML code and respository, which was only being mirrored, but not backed up for a disaster recovery situation

I guess if I ever do this again, there is merit in cleaning this all up and providing some level of automation.
Does anybody have any suggestions of obvious other things to consider.

MySQL Workbench 1.0.1 First Impressions

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

Testing/Trialing new MySQL Releases

By now, I’m sure you have all heard about Free VMware Player allowing easy and quick access to see, view and use other OS’s easily. For those Windows users out there, now is your chance to trial MySQL under Linux with no impact to your system, why wait.

See the MySQL Virtual Machine details on the VMware site. On closer inspection this effectively pushes you to the VMware Technology Network (VMTN) page within the MySQL website.

The MySQL guys needs to update their site to reflect new reference to the free player, rather then a trial version of Workstation. Even VMware Server is free (could be mentioned). You can read more about the offerings etc at a previous blog Using VMware Server (free).

Also MySQL is only offering MySQL 4.1.12 (that was released in May last year). Surely this would be a great way to push the newer releases, I’d like to see MySQL offer this for at least the current production GA version 5.0, and it would be a great way to promote new releases, like an alpha release of 5.1. How easy would it be then for people to trial and test the new features without any hint of messing up or changing any existing environments. Perhaps the MySQL marketing department could consider this?

Is is more work? Yes. Will it take resources? Yes. But look at the benefits. Instead of the diehards that are prepared to download bleeding edge releases, you can now reach a newer market of users with an easier, cleaner and throw away installation of newer releases. You would not consider doing it for every dot release, more the current GA 5.0, and alpha and RC’s for newer versions I think.

A working MySQL Workbench Under Linux

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

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

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.

Building MySQL Workbench 1.0.1 for Linux (Part 2)

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

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

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

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)

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.

What makes your blood boil?

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

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

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

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

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

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

Sequences in MySQL

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

To enum or not to enum?

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

In summary, using the MySQL Sample Database.

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

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


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

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


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

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

Why I don’t use these?

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

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

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

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


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

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

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

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


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

This for me provides the following benefits:

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

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

I’m yet to be convinced otherwise.

Support for Technology Stacks

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.

Downgrading a MySQL schema from 5 to 4

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

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

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

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

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

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


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

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

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.