Description:
I am trying stored procedures in the new 5.0.3 beta on Fedora Core 3. I have come across a query that works from the MySQL client program, but produces an error if I put it within a stored procedure. Both tables are in the same database. Both descr and description columns were declared as varchar(50) default null. Both tables have data and the budget table has records that have budgetyear="2004". I am running mysqld_multi on a different port if it matters.
The query is this:
update budget inner join chart on budget.account = chart.account set budget.description = chart.descr where budget.budgetyear="2004";
and I am creating and calling the stored procedure like this:
drop procedure if exists sp_populatebudget\g
Delimiter $
create procedure sp_populatebudget()
update budget inner join chart on budget.account = chart.account set budget.description = chart.descr where budget.budgetyear="2004";
$
call sp_populatebudget()\g
The following error results:
Error 1099 (HY000): Table 'budget was locked with a READ lock and can't be updated
I can run the update query before and after I try the procedure with no issues. I even rebooted (window's thinking) and tried the procedure first. I have tried rewording the query to remove the inner join with a where clause. I also tried myisamchk to fix the tables. (I am updating the descriptions, so if the account changes from year to year, they know for what it was used in previous years.)
If I run the call sp_populatebudget()\g line a second time, the client hangs until I kill the process using phpMyAdmin.
How to repeat:
The problem occurs whenever I cut and paste the following into MySQL client running in a terminal window:
use gl
drop procedure if exists sp_populatebudget\g
Delimiter $
create procedure sp_populatebudget()
update budget inner join chart on budget.account = chart.account set budget.description = chart.descr where budget.budgetyear="2004";
$
call sp_populatebudget()\g
