MySQL Stored Procedure Crashing Server?

I'm in the process of converting the IERI student assessment data into a relational format. Currently the data is spread betwee nsixteen different tables (one for each year/subject combination). The method I was attempting to use to transform the data was as follows:

1. Create a temporary table for each form of each test (Algebra Form A, Algebra Form B, etc.) containing a single row for each student/answer combination.
2. Use a stored procedure to take the data from the temporary table and insert it into the tables the will contain the final data.
   

Creating the temporary table is tedious in that a self-join has to be done on the original table for each answer. However, this is well worth it as the data is pretty much in a usable format for insertion into the final tables.

Unfortunately, I ran into a major problem when attempting to get the stored procedure to work. The basic and programmatic flow I designed for the procedure seems to be fine. I was able to test with some dummy data and had no problems. When I added the SQL code to do the necessary selects I encountered a serious error … the MySQL daemon appeared to crash. The server is quick to restart after a crash so it's hard to say without doing some investigation, but it does appear to be a crash.

At first I thought maybe I was having a memory limit problem since the amount of data (10700 rows) is fairly large compared to what we normally work with. (Our server isn't top-notch either.) However, after watching resource utilization (using top) I was unable to see any significant usage during temporary table creation or stored procedure operation. Actually, the server seemed to crash immediately after the stored procedure was called.

The stored procedure consisted of a few variables, a few SQL statement executions (one to populate variables, another to insert data into tables), and a cursor for accessing data from the temporary table. Overall, the structure of the stored procedure seems to be fine and is in line with many samples I've seen on dev.mysql.com. The single-use SQL statements are run using PREPARE and EXECUTE since they utilize variables that are populated by the cursor.

Through much trial and error I was able to determine that the single-use SELECT statement I was using to populate a variable is the source of the crash. Upon execution of that statement the server crashes. The specific problem seems to be when I access a table, as I can use a more basic SELECT (something like SELECT 10 INTO @recordref) and everything seems fine.

Since I don't see any way to get this working as things now stand I'm going to do everything through SQL queries. In the meantime, though, I decided to try doing the transformation as a couple of queries that use subqueries to set up the relational-friendly data tables. Though this is really a one-use setting, there's some thought out there that currently in MySQL it can be faster to do things in this manner (see Roland Bouman: Refactoring MySQL Cursors).

As a reminder, when using the MySQL Query Browser a script is better for something like this rather than a query. The main difference between the two access methods is that all the queries in a script are run in one connection/session whereas each query is run in its own connection. Using a script will allow the creation of temporary tables which, in this particular instance, should speed things up noticeably. First I'll develop all the code I need as queries then create one large script to process all the data.

Sometime in the next few weeks I think I'll upgrade to a newer version of MySQL and see if that solves the problem. If that doesn't work I'll post a question on the forums and see if anyone has any thoughts. After that I'll think about posting a bug report. I'm a bit leery about doing so after my mozilla bug reporting experience, but as long as I'm thorough I shouldn't be given the smack down.