SQL – Walt-O-Matic

MySQL Cascade Delete Problem

MySQL 5.1.51 introduced a nasty little bug that has the potential to really cause some production servers some ill — cascading delete and updates can sometimes fail if you’re dealing with “too much” data at once. Where “too much” is a relatively small amount. You may get bit if you have a one-to-many relationship.

A note of warning to MySQL users using 5.1.51, you may want to downgrade to 5.1.50 for a little bit.

There’s a problem with 5.1.51 in which cascading deletes or updates throw an error. Not good if you have constraints and one to many relationships.

ERROR 1030 (HY000): Got error -1 from storage engine

The mysql error log will say something like:

InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 250

This is a confirmed bug in MySQL and is repeatable. The error will cause a transaction rollback. Not good news for people running production systems.

See MySQL Bug #357255.

While the problem was quickly identified and apparently resolved, as the defect report is closed, it does not look like the 06-Oct-2010 change has made it out to the production baseline as of the time of this writing.

Additionally, the MySQL pre-release snapshots on labs.mysql.com show there is a mysql-5.1.52 pending with a September date, and this hasn’t made it to general production yet.

That leaves one to speculate that the fix will appear in the 5.1.53 version, and we won’t be seeing that for a month or two. Yikes.

Hibernate Schema Update Problems

For a while I’ve been having problems trying to get a SchemaUpdate working with Hibernate. Turns out the problem was me, not Hibernate. Newly created tables had a blank ACL, and I just needed to set them.

I’ve been using Hibernate for relational persistence for a while now, and I have to say it’s been working out pretty well.

That was, until I went to do a SchemaUpdate.

The moment I did an alternation of a table, or created a new entity, things went sour, and I was unable to read my old data. Was it me, or was it Hibernate?

could not initialize a collection
PSQLException: ERROR: permission denied for relation newtablejustadded

It was me.

Turns out Hibernate’s update was working just fine. There was no magic versioning or class hashing going on, detecting the change to the database.

The problem was the ACL was blank for the newly created entity table (I was using Postgres).

By issuing this command, all was fine again:

GRANT INSERT, SELECT, UPDATE, DELETE, REFERENCES ON newtable TO GROUP agroupIwasusing;

Since it took a while to figure out what was going on, I thought I’d post this to help others that follow a similar, yet frustrating, path.

SQLite Functions

I was trying to figure out how to display an integer as a date and time in SQLite3. And it’s documented, and documented very, very well. Problem is, so is the code base, so when I looked for time conversion functions for SQLite, even browsing the Wiki, I kept getting the developer pages, not the SQL core functions. But, I found them. And now I’m documenting where so I can get to them at a later date.

Seems silly, but I’ve been using SQLite and was having the darnedest time try to convert an integer into a time. Problem was, I knew the functions for Sqlite3 had to be out there, but all I kept getting from search engines and exploring the developer Wiki were the C/C++ API functions, not the ones needed for SQL. I guess the way the pages are indexed, the source pages score higher than the user manual pages.

Anyhow, located the SQL functions and wanted to document their location for myself so I could locate them again later.

Seems they are called Core Functions.

They hide under the language expression page, which is under the SQL language page.

I was thrilled to find that the SQLite C Interface allowed the creation of your own functions. Brilliant!

Turns out, that the date and time functions were hiding in a different part on the Wiki.

The magic I wanted, given a time stored in an integer, was:
SELECT datetime(timevalue, 'unixepoch', 'localtime');