Archive for December, 2006

Unable to call a stored procedure in MySQL

Thursday, December 28th, 2006

You've created a procedure in MySQL 5. You've granted EXECUTE privileges to your application's user account. But when your application prepares a statement to call that procedure, an exception is thrown. Perhaps it's a NullPointerException deep inside of ConnectorJ. Perhaps the exception claims that the procedure does not exist. Or perhaps you've gotten lucky enough to get the exception that leads you down the right path (Driver requires declaration of procedure to either contain a ''\nbegin'' or ''\n'' to follow argument declaration, or SELECT privilege on mysql.proc to parse column types). Here are the troubleshooting steps that I've gleaned from hours of searching the MySQL forums.

First, open two console windows. In one, log into MySQL as the application user. In the other, log in as root. In your app window, list the databases.

SHOW DATABASES;

If your application's database does not appear, then you should go to your root window and grant database privileges.

USE mydatabase;
GRANT ALL ON mydatabase TO appuser;

Now go back to the app window. List the databases again to ensure that it appears. Now that it does, go into it.

USE mydatabase;

Once you're in, try to view the procedure.

SHOW CREATE PROCEDURE myproc /G

One of three things will happen. If you get a message saying "PROCEDURE myproc does not exist" then the app user does not have privileges for it. Go back to the root window and grant them.

GRANT EXECUTE ON PROCEDURE myproc TO appuser;

The second thing that might happen is that the procedure will be found, but the body will not be listed. Instead, it will say "Create Procedure: NULL". If so, go back to the root window and take care of that.

GRANT SELECT ON mysql.proc TO appuser;

The third thing that could happen is what you want to happen; the body of the procedure will be listed. Now you should be able to call the procedure from your application.

Warnings
You may be tempted to take a couple of short-cuts to resolve problems like these. Please don't. Shortcuts may cause bigger problems down the road.

The first thing you might try is to use the root account from within your application. This is extremely dangerous. Security comes in layers. While you should use stored procedures and prepared statements to avoid SQL injection attacks, you should also apply the principle of least privilege just in case they occur anyway. Allowing your application to connect as root gives an attacker access to your most valuable resource: your data. But giving each part of the application a separate account with only the privilege that it requires quarantines the bad guy.

The second thing that you might try after hours of research is to set the "noAccessToProcedureBodies" flag in the ConnectorJ connection string. Please avoid this flag, as it circumvents the parameter type checking that the JDBC driver provides for you. This flag causes ConnectorJ to convert all of the parameters to strings, which MySQL will then convert back to the required type.

But by walking through the problem step-by-step, these short-cuts should not be necessary.

Episode 6: Datacenter Separation

Tuesday, December 26th, 2006

Listen Now

Advice on keeping development and production servers separate.

Episode 5: Web Services

Tuesday, December 19th, 2006

Listen Now

Lessons Learned about Web Services:

Lesson #1 - Don't put example code from books into production code.
Lesson #2 - Don't reinvent the wheel.
Lesson #3 - Don't jump on the early technology that is not fully proven.
Lesson #4 - Don't expect interoperability between programming platforms or libraries right out of the box (or in version 1.0).

Unhelpful Error Message in Eclipse

Monday, December 18th, 2006

I'm a big fan of the Eclipse Java IDE. I think it is at least on generation ahead of Microsoft's Visual Studio IDE. However, even Eclipse has some poorly designed features.

Take, for example, the error message infrastructure. Instead of handling errors in useful ways, or better yet just doing the right thing in the first place, they wrap all exceptions and present the same useless UI for everything. Here's an example:

The message box tells me that an exception was caught (not helpful), and that the reason was that it encountered a problem (not helpful). It gives me the options of either undoing or aborting the refactoring, which sound a lot like the same thing. Then when I ask for more details, it tells me again that it encountered a problem.

To reproduce this error, you must install the Subclipse plugin to manage a project in Subversion. Delete a package. The package will not actually be deleted, since the change has not been committed to SVN. Then try to move a class into the deleted package. You can see how the separate concerns of Java packages, file systems, source control, and refactoring have all combined to produce a mess of a situation.

Lazy Logging

Friday, December 15th, 2006

I sometimes see coders trying to optimize their logging. Since it takes time to format log messages, they will first test whether logging is enabled:

Logger log = Logger.getLogger(MyClass.class);
if (log.isInfoEnabled())
    log.info( "Processing customer " + customer.getName() +"." );

The info() method already checks whether logging is enabled, but this additional check avoids the string concatenation. String formatting and manipulation is surprisingly expensive, so it is undesirable to take this hit only to have no effect.

However, this type of checking is just a distraction. Fortunately, Log4J gives you an option. The parameter to info() is not a String, as you might think by looking at this example code. It is actually an Object. Log4J will call toString() on the object you pass in. Furthermore, it will only call toString() if it really does log the message. We can take advantage of this to clean up our code.

Here's my solution
I've created a class that performs string concatenation and formatting inside of the toString() method. It caches the result so that multiple calls to toString() don't repeat the work. However, if toString() is never called, the expensive operations are never performed. Using this class, the logging code looks like this:

Logger log = Logger.getLogger(MyClass.class);
log.info( StringFormatter.format("Processing customer {name}.").
    set("name", customer.getName()) );

Downlaod the source code here: stringformatter.zip.

Thanks, Steve

Wednesday, December 13th, 2006

Just a quick note to Steve Jobs and all the talented people at Apple. Thank you for fixing my iPod.

I have a fifth generation, and I just recently upgraded the firmware. Yes, I'm slow to adopt new versions of software. And iTunes 7 has not had a spotless reputation, so I was particularly reluctant to upgrade. But now that I have, I couldn't be happier.

iTunes 7 adds a few features that really amount to little more than iCandy. But even so, that sugar tastes pretty good. In particular, I love cover flow. It acknowledges my conceptual model of music by displaying the artwork of all my albums. Sure, we all know that digital music is stored in files, not on circular disks in square covers, but we still think in the album-oriented metaphor. It's good of Apple to give a nod to a music-lover's way of thinking.

But it's the firmware upgrade for my iPod that really has me singing Steve's praise. With absolutely no work on my part, Apple has restored the continuous experience of listening to my Pink Floyd and Frank Zappa albums. The tiny quarter-second gap that it used to put between consecutive tracks is finally gone. I had previously edited together all 21 tracks of They Might Be Giants' "Fingertips" because the fractional pause every twenty seconds was driving me crazy. This is a fix long overdue.

The iTunes 7 experience is not completely without flaws, however. As I mentioned on Episode 4 of the Adventures in Software talk show, cover flow highlights an identity problem with the file-oriented implementation model. Since albums don't have identity, iTunes sometimes gets confused and displays multiple copies of the same album art. Perhaps a fix in iTunes 7.1?

Episode 4: Identity-Based Patterns

Tuesday, December 12th, 2006

Listen Now

Java Enums:
http://www.javaworld.com/javaworld/jw-07-1997/jw-07-enumerated.html
http://www.javaworld.com/javaworld/javatips/jw-javatip122.html
http://java.sun.com/j2se/1.5.0/docs/guide/language/enums.html

Command:
http://en.wikipedia.org/wiki/Command_pattern

Easy Mock (Java only)
http://www.easymock.org/

NMock
http://nmock.org/

.Net port of Easy Mock
http://www.easymock.net/

Action command object:
http://java.sun.com/j2se/1.5.0/docs/api/javax/swing/Action.html

Strategy:
http://en.wikipedia.org/wiki/Strategy_pattern

State:
http://en.wikipedia.org/wiki/State_pattern

Lex
http://en.wikipedia.org/wiki/Lex_programming_tool

Microsoft Windows Workflow Foundation
http://msdn.microsoft.com/workflow

MySQL LAST_INSERT_ID() is Broken, Too

Monday, December 11th, 2006

Stored procedures are finally supported in MySQL 5. This makes for better encapsulation of the database, and gives us hooks for logging and optimization. However, it also means that we have to find our way through a new set of gotchas.

We've defined a stored procedure that inserts data into three separate tables. The ID of the first table is used as a foriegn key in the second, and the ID of the second table is used in the third. This revealed a problem with the LAST_INSERT_ID() function. Here's the code:

    INSERT queue_user (queue_user_name) VALUES (publisher);
    SET publisher_id = LAST_INSERT_ID();
    INSERT queue_entry (publisher_queue_user_id)
    VALUES (publisher_id);
    SET entry_id = LAST_INSERT_ID();
    INSERT queue_data (queue_entry_id, request)
        VALUES (entry_id, data);

The queue_user row was inserted correctly, and returned an ID of 7. The queue_entry row was correctly associated to publisher ID 7 with the new entry ID 5. However, the entry_id variable was set by LAST_INSERT_ID() to 7, not 5. This caused the queue_data row to be associated with the wrong entry ID. Apparently, within a stored procedure LAST_INSERT_ID() returns the ID generated by the first INSERT statement, not the last one.

Here's my solution
If you wrap the first insert in a transaction, then the problem is solved. This, of course, violates the atomic intent of the transaction, but in our case that wasn't an issue. Here's the "fixed" code:

    START TRANSACTION;
INSERT queue_user (queue_user_name) VALUES (publisher);
    SET publisher_id = LAST_INSERT_ID();
    COMMIT;
INSERT queue_entry (publisher_queue_user_id)
        VALUES (publisher_id);
    SET entry_id = LAST_INSERT_ID();
  INSERT queue_data (queue_entry_id, request)
        VALUES (entry_id, data);

I only hope they fix this soon.

MySQL row_count() is Broken

Wednesday, December 6th, 2006

Like most other SQL-based relational database engines, MySQL provides a mechanism to get the number of rows affected by the last operation. In MySQL, it is the row_count() function. This function is useful for implementing a number of patterns, including the "update/insert" pattern (or "upsert" as it is sometimes known).

It is often necessary to have one procedure that inserts a row if it doesn't yet exist, or update it if it does. There are several ways to accomplish this. You could SELECT first to determine whether it exists, then UPDATE or INSERT based on the result. However, this is not the most efficient mechanism, and may even result in consistency problems or deadlocking depending upon the agressiveness of the RDBMS locking mechanism. A faster and safer approach is the update/insert pattern.

In an update/insert procedure, you attempt an UPDATE statement to modify the desired records. Then you check row_count() to see if any rows were affected. If the records did not previously exist, row_count() would return 0. In that case, you would INSERT.

Unfortunately, the MySQL implementation of row_count() does not support this pattern. Whereas most RDBMS engines count the number of rows matched, MySQL does not count records that haven't actually changed during an UPDATE statement.

Try this experiment. Create a table:

mysql> create table rcbroke
(rcbroke_id int auto_increment not null primary key,
val int not null default 0,
create_date datetime not null,
modify_date timestamp not null);
Query OK, 0 rows affected (0.08 sec)

Insert a couple of rows.

mysql> insert into rcbroke (create_date) values (NOW());
Query OK, 1 row affected (0.03 sec)

mysql> insert into rcbroke (create_date) values (NOW());
Query OK, 1 row affected (0.03 sec)

mysql> select * from rcbroke;
+------------+-----+---------------------+---------------------+
| rcbroke_id | val | create_date         | modify_date         |
+------------+-----+---------------------+---------------------+
|          1 |   0 | 2006-12-06 10:27:27 | 2006-12-06 10:27:27 |
|          2 |   0 | 2006-12-06 10:27:30 | 2006-12-06 10:27:30 |
+------------+-----+---------------------+---------------------+
2 rows in set (0.00 sec)

Now update one of those rows, setting the value that it already contains. Select row_count() to see if the matching row is counted.

mysql> update rcbroke set val=0 where rcbroke_id=1; select row_count();
Query OK, 0 rows affected (0.00 sec) Rows matched: 1  Changed: 0  Warnings: 0

+-------------+
| row_count() |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

Notice that MySQL reports one row matched, but no rows changed. For the update/insert pattern (and most other useful SQL patterns), we are most interested in the rows matched, but row_count() returns the number of rows changed.

But our test table contains a Timestamp column, which is automatically updated when a row changes. Surely, MySQL has set the timestamp for us. Sorry, but no...

mysql> select * from rcbroke;
+------------+-----+---------------------+---------------------+
| rcbroke_id | val | create_date         | modify_date         |
+------------+-----+---------------------+---------------------+
|          1 |   0 | 2006-12-06 10:27:27 | 2006-12-06 10:27:27 |
|          2 |   0 | 2006-12-06 10:27:30 | 2006-12-06 10:27:30 |
+------------+-----+---------------------+---------------------+
2 rows in set (0.00 sec)

If we change the value of a column, then row_count() works correctly.

mysql> update rcbroke set val=1 where rcbroke_id=1; select row_count();
Query OK, 1 row affected (0.02 sec) Rows matched: 1  Changed: 1  Warnings: 0

+-------------+
| row_count() |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

And the modify_date is automatically changed for us.

mysql> select * from rcbroke;
+------------+-----+---------------------+---------------------+
| rcbroke_id | val | create_date         | modify_date         |
+------------+-----+---------------------+---------------------+
|          1 |   1 | 2006-12-06 10:27:27 | 2006-12-06 10:28:35 |
|          2 |   0 | 2006-12-06 10:27:30 | 2006-12-06 10:27:30 |
+------------+-----+---------------------+---------------------+
2 rows in set (0.00 sec)

Here's my solution
The Timestamp column is our saving grace, although it may as well be a regular Datetime column for all it's worth. If we explicitly update modify_date to the current time, MySQL will count that row as changed. There is still a small chance that we might call the UPDATE twice within the same Timestamp granularity, but that chance is small enough to live with in most scenarios.

mysql> update rcbroke set val=1, modify_date=NOW() where rcbroke_id=1; select row_count();
Query OK, 1 row affected (0.02 sec) Rows matched: 1  Changed: 1  Warnings: 0

+-------------+
| row_count() |
+-------------+
|           1 |
+-------------+

1 row in set (0.00 sec)mysql> select * from rcbroke;
+------------+-----+---------------------+---------------------+
| rcbroke_id | val | create_date         | modify_date         |
+------------+-----+---------------------+---------------------+
|          1 |   1 | 2006-12-06 10:27:27 | 2006-12-06 10:29:12 |
|          2 |   0 | 2006-12-06 10:27:30 | 2006-12-06 10:27:30 |
+------------+-----+---------------------+---------------------+
2 rows in set (0.00 sec)

This is the closest we can get to correct behavior of MySQL's row_count(). Even though it may return the correct "number of rows affected" by one interpretation of the SQL spec, I see no valid use for the function as defined.

Episode 3: Identity

Tuesday, December 5th, 2006

Listen Now

Michael, Raymond, and Charles discuss the fundamental law of object -oriented programming: identity.

James Rumbaugh, Michael Blaha, William Premerlani, Frederick Eddy, William Lorensen: Object-Oriented Modeling and Design, Prentice Hall, ISBN 0-13-629841-9

http://en.wikipedia.org/wiki/Identity_%28object-oriented_programming%29