MySQL

Just uploaded a new revision of the ORM that resolves an issue with MySQL. Apparently even though it wasn't throwing any errors, MySQL wasn't creating foreign key constraints when they're put on the column line in a create table statement... I can understand (I guess) not supporting a particular standard syntax -- what I find odd is that the MySQL server chooses to just ignore the unsupported syntax in this case rather than produce an error that says "I don't understand this syntax!"

Here's a tutorial that mentions the syntax that's needed for MySQL. So the MySQL agent now generates create table statements using this syntax and correctly installs the desired foreign key constraints.

I discovered this actually because someone informed me they were having difficulty installing the Members onTap plugin. This is a plugin for the onTap framework that handles member management and security with a bunch of extras like geographic regions, optional OpenID support for those who want it and an event log with undo/redo features. MySQL was throwing an odd error... "Error Executing Database Query. You can't specify target table 'tap_regionTREE' for update in FROM clause". Why not? If there's one thing I love, it's helpful error messages... this isn't one of them.

It turns out that MySQL can't support any kind of recursion in subqueries. So this works:

But this throws the above mentioned error:

I had rather assumed this was pretty basic stuff... After all, it's the well established conventional behavior of all programming languages that I know of to process an inner before an outer item. For example, in ColdFusion (as in any language), you might have:

And the server will process a() then b() and only *after* both of those have been processed will it process doSomething(a,b), where a and b are the results from the respective functions.

Apparently that's not the case with MySQL because it throws this error if it sees the same table twice... Except the error doesn't tell you that's the reason why it's being thrown. It doesn't mention anything at all about the fact that there's a subquery or the fact that the table appears twice or that there's recursion involved, any of which might threaten to tip you off to the reason for the error.

If you read the text of the error, it appears to be saying "you can't update this table". The closest it comes to hinting at the real problem is its use of the phrase "from clause" which is at best confusing.

So anyway, if you ever see this error message, now you'll know what it means. :)

I had to refactor the nested set for regions in the member plugin so that instead of using a subquery it executes each of those queries and uses a valuelist from them. Which means three trips to the database instead of one. I'm not happy about that, but apparently I don't have much say in the matter. The good news is that regardless of the application, regions won't be modified very often and it won't be a big issue if editing them is a tad sluggish. So even though I may not be very happy about needing to change it this way, it's more of a philosophical pain than a pragmatic issue. It's not going to be a deal-breaker for people using the software. :)