table locking problem

Hi,

I'm having a strange problem with table locking. I have written a stored
procedure that only accesses a single table. Before executing the
procedure, I'm trying to lock the table in question for writing. The
LOCK TABLE command succeeds, but when I execute the stored procedure it
tells me a non existent table was not locked:

mysql> LOCK TABLE PlanTracking WRITE; CALL spConfirmRequest(7); UNLOCK
TABLE;
Query OK, 0 rows affected (0.00 sec)

ERROR 1100 (HY000): Table 'pt' was not locked with LOCK TABLES
Query OK, 0 rows affected (0.00 sec)

mysql>

The table being referred to was once an alias for a table in a select
query being used for a cursor in my stored procedure. The alias has been
removed, the procedure has been dropped and re-created, and the mysql
service has been restarted yet the problem persists.


Thanks,
J.P.

Here's the stored procedure:

mysql> show create procedure spConfirmRequest;
+------------------+----------+----------------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ --------------------
------------------------------------------------------------ ---------------------------------------------------------+
| Procedure | sql_mode | Create Procedure











|
+------------------+----------+----------------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ --------------------
------------------------------------------------------------ ---------------------------------------------------------+
| spConfirmRequest | | CREATE DEFINER=`root`@`localhost`
PROCEDURE `spConfirmRequest`( ptid bigint )
BEGIN
DECLARE vPlanTrackingID, vProjectID BIGINT;
DECLARE vDownload TINYINT;
DECLARE cursItems CURSOR FOR (
SELECT PlanTrackingID, ProjectID, Download
FROM PlanTracking
WHERE SetNumber = 0 AND Deleted = 0 AND
(PlanTrackingID = ptid OR ParentID = ptid)
ORDER BY PlanTrackingID );

OPEN cursItems;

BEGIN
DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;
LOOP
FETCH cursItems INTO vPlanTrackingID,
vProjectID, vDownload;
UPDATE PlanTracking
SET
SetNumber = getNextSetNo(vProjectID,
vDownload),
ModDate = NOW()
WHERE PlanTrackingID = vPlanTrackingID;
END LOOP;
END;

CLOSE cursItems;

SELECT PlanTrackingID, ParentID, SetNumber
FROM PlanTracking
WHERE Deleted = 0 AND SetNumber > 0 AND (PlanTrackingID = ptid
OR ParentID = ptid)
ORDER BY PlanTrackingID;
END |
+------------------+----------+----------------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ --------------------
------------------------------------------------------------ ---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> LOCK TABLE PlanTracking WRITE; CALL spConfirmRequest(7); UNLOCK
TABLE;
Query OK, 0 rows affected (0.00 sec)

ERROR 1100 (HY000): Table 'pt' was not locked with LOCK TABLES
Query OK, 0 rows affected (0.00 sec)

mysql>





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org