Skip Menu |
 

This queue is for tickets about the DBD-mysql CPAN distribution.

Report information
The Basics
Id: 7743
Status: resolved
Priority: 0/
Queue: DBD-mysql

People
Owner: Nobody in particular
Requestors: byarnell [...] mechpod.com
Cc:
AdminCc:

Bug Information
Severity: Normal
Broken in: 2.9002
Fixed in: (no value)



Subject: Can't call user defined stored procedure that calls a second user defined stored procedure
Download (untitled) / with headers
text/plain 5.2k
Hello, As far as I can tell this is a possible bug around the user defined stored procedures available in 5.0.0-alpha. I'm pretty sure it is not on MySQL but in the DBD::mysql package. This is my first bug submission so please let me know if you need any more info. Enviroment info: perl v5.6.1 mysql Ver 14.3 Distrib 5.0.0-alpha, for sun-solaris2.7 (sparc) package Bundle::DBD::mysql running on sun-solaris 2.7 and 2.8 client machines I'm also using DBD::Oracle version 1.12 on this same client host I have 2 user defined Stored Procedures (SP hereafter) that can both be executed from the mysql> prompt without any problems. The first or "complex" SP opens a cursor and calls the second or "simple" SP. I can call the "simple" SP without any problem from perl script using: $statement="call sp_complete_order($order_key)"; $sth=$dbh->prepare($statement) || die "..; $sth->execute() || die "...; but when I call the "complex" SP the execution fails with: Error Message: DBD::mysql::st execute failed: PROCEDURE %f does not exist at /usr/local/apache/cgi-bin/test/CompleteVarPrintBatch.cgi line 107 calling syntax: $statement="call sp_complete_batch($batch_key)"; $sth=$dbh->prepare($statement) || die "..; $sth->execute() || die "...; The only possible problem might be that the "complex" SP is opening a cursor on a table that the "simple" SP will delete rows from during it's execution.?. I'm not reporting this problem to MySQL.org because as stated before both SP's execute from the mysql> prompt. Here are the 2 Stored Procedures: The "complex" one which calls the another SP: DELIMITER // create procedure sp_complete_batch( IN p_batch_key INT ) BEGIN DECLARE counter INT; DECLARE cur1 CURSOR for select order_key from orders where batch_key = p_batch_key; DECLARE v_order_key INT; OPEN cur1; SELECT COUNT(*) INTO counter from orders where batch_key = p_batch_key; WHILE counter > 0 DO FETCH cur1 into v_order_key; CALL SP_COMPLETE_ORDER(v_order_key); SET counter = counter - 1; END WHILE; CLOSE cur1; END // DELIMITER ; The "simple" which executes without a problem using DBD::mysql: DELIMITER // create procedure sp_complete_order( IN p_order_key INT ) BEGIN update orders set status_id='COMPLETED', date_done=now() where order_key = p_order_key; insert into inactive_orders select * from orders where order_key = p_order_key; delete from orders where order_key = p_order_key; END // DELIMITER ; Here are the table creation scripts for the 2 tables involved: create table orders ( order_key int unsigned not NULL AUTO_INCREMENT, batch_key int unsigned, base_path varchar(200) not NULL, file_name varchar(150) not NULL, date_received datetime not NULL, date_printed datetime, date_reworked datetime, date_ship_due datetime, date_done datetime, status_id varchar(20) not NULL DEFAULT 'PENDING' references order_status(status_id), print_seq int unsigned, billable_flag ENUM('YES','NO') not NULL, rework_count smallint unsigned not NULL DEFAULT '0', customer_id varchar(20) not NULL references customers(customer_id), order_type_id varchar(20) not NULL references order_types(order_type_id), qty mediumint unsigned not NULL DEFAULT '1', image_count int unsigned not NULL DEFAULT '1', file_type_id varchar(10) not NULL references file_types(file_type_id), media_type_id varchar(10) not NULL DEFAULT 'PLAIN' references media_types(media_type_id), binding_type_id varchar(10) not NULL DEFAULT 'NONE' references binding_types(binding_type_id), sides ENUM('SIMPLEX','DUPLEX') not NULL DEFAULT 'SIMPLEX', printer_id varchar(20) not NULL references printers(printer_id), deliv_location varchar(20), notes tinytext, primary key (order_key), index idx_orders_file_name (file_name), index idx_orders_date_received (date_received), index idx_orders_deliv_location (deliv_location) ); create table inactive_orders ( order_key int unsigned not NULL, batch_key int unsigned, base_path varchar(200) not NULL, file_name varchar(150) not NULL, date_received datetime not NULL, date_printed datetime, date_reworked datetime, date_ship_due datetime, date_done datetime, status_id varchar(20) not NULL DEFAULT 'PENDING' references order_status(status_id), print_seq int unsigned, billable_flag ENUM('YES','NO') not NULL, rework_count smallint unsigned not NULL DEFAULT '0', customer_id varchar(20) not NULL references customers(customer_id), order_type_id varchar(20) not NULL references order_types(order_type_id), qty mediumint unsigned not NULL DEFAULT '1', image_count int unsigned not NULL DEFAULT '1', file_type_id varchar(10) not NULL references file_types(file_type_id), media_type_id varchar(10) not NULL DEFAULT 'PLAIN' references media_types(media_type_id), binding_type_id varchar(10) not NULL DEFAULT 'NONE' references binding_types(binding_type_id), sides ENUM('SIMPLEX','DUPLEX') not NULL DEFAULT 'SIMPLEX', printer_id varchar(20) not NULL references printers printer_id), deliv_location varchar(20), notes tinytext, primary key (order_key), ); NOTE: We are using ISAM tables so the foreign constraints are really not working but we kept them in there just in case we migrate to InnoDB tables in future. Please let me know if you need any more info and if there is anything I can try on my end to help debug this possible problem. Regards, Bill Yarnell
Download (untitled) / with headers
text/plain 1.4k
[guest - Wed Sep 22 14:24:03 2004]: Show quoted text
> Hello, > As far as I can tell this is a possible bug around the user defined > stored procedures available in 5.0.0-alpha. I'm pretty sure it is > not on MySQL but in the DBD::mysql package. This is my first bug > submission so please let me know if you need any more info. >
First off, what version of DBD::mysql are you running? Second, Maybe. But I don't see off hand what could cause this problem from DBD::mysql's side. Show quoted text
> Error Message: > DBD::mysql::st execute failed: PROCEDURE %f does not exist at > /usr/local/apache/cgi-bin/test/CompleteVarPrintBatch.cgi line 107 >
I don't know that much about the procedure support in 5.0, but looking at the error message above, I'd suspect a memory corruption bug where the procedure is being called, unless mysql normally mangels the proc name to things like "^B%f". To see what is going on driver-side, you can run a trace(9), and if anything is out of place (like it is sending the wrong proc name), let me know. You might also want to enable debugging on the server side to try and find out what statement the server is getting & what it is sending. Show quoted text
> file_type_id varchar(10) not NULL references > > NOTE: We are using ISAM tables so the foreign constraints are really > not working but we kept them in there just in case we migrate to > InnoDB tables in future. >
Did they fix that syntax issue 5.0? Last time I tried the references clauses were dropt when put inline with the field definition. Rudy


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.