Menu
▾
▴
[Firebird-devel] [ firebird-Bugs-1017870 ] FB disconnects when stored proc executed
|
From: SourceForge.net <no...@so...> - 2004-09-01 20:56:53
|
Bugs item #1017870, was opened at 2004-08-27 14:37 Message generated for change (Comment added) made by christotwy You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=109028&aid=1017870&group_id=9028 Category: None Group: None Status: Open Resolution: None Priority: 5 Submitted By: TerryC (christotwy) Assigned to: Nobody/Anonymous (nobody) Summary: FB disconnects when stored proc executed Initial Comment: I created a relatively long stored procedure in my database, developing it incrementally. The first section, ending with the first "suspend;", executed fine, as well as the second. When I added the third section, it compiled without error, but executing it causes Firebird to disconnect everyone! This causes IB Expert to crash when I'm using it. IBO Console doesn't crash, but reports that it has been forcibly disconnected. The behavior is the same whether the database is on my local machine or a server on my network. In the attached database, the stored procedure at issue is "COUNTS_BY_REP". ---------------------------------------------------------------------- >Comment By: TerryC (christotwy) Date: 2004-09-01 14:56 Message: Logged In: YES user_id=1112288 Closed? Please see my post RE: BINGO! below. I think that's it. I will attach the DB as soon as I can get it stripped down. ---------------------------------------------------------------------- Comment By: TerryC (christotwy) Date: 2004-09-01 14:55 Message: Logged In: YES user_id=1112288 seanleyne, Many thanks for the rewrite! I would have gotten around to optimizing it eventually -- the disconnects became top priority -- but you have saved me the effort. ...and it is FAST! ---------------------------------------------------------------------- Comment By: Sean Leyne (seanleyne) Date: 2004-09-01 14:42 Message: Logged In: YES user_id=71163 Since Claudio and I weren't able to reproduce this bug, the entry is being closed accordingly. ---------------------------------------------------------------------- Comment By: TerryC (christotwy) Date: 2004-09-01 14:35 Message: Logged In: YES user_id=1112288 BINGO! OK, I seem to have nailed it down to a single statement in the SP. After changing the "select distinct '(string const)'..." stuff to the appropriate "VAR = '(string const)'" syntax, there were no more disconnects. I narrowed it down to the last one in the SP: "select distinct '% / Total' from OFI_TYPE into :LBL;" If this statement remains, it disconnects. If it is changed to "LBL = '% / Total';" then all is well. Does this ring any bells? ---------------------------------------------------------------------- Comment By: TerryC (christotwy) Date: 2004-09-01 13:53 Message: Logged In: YES user_id=1112288 robocop, a) No, the UDFs were added at the start of the project as a "just in case". The DB isn't finished yet, so I'll remove them if they turn out to be unncecessary. b) I usually just put in the current day's date (the default with IBExpert) and it has always crashed. c) OK, I was too tired when I wrote that. The "LBL = 'xxx'" syntax is what I was after. I need to remember I'm writing a SP and not a query. ;-) Thanks! ---------------------------------------------------------------------- Comment By: Sean Leyne (seanleyne) Date: 2004-09-01 08:40 Message: Logged In: YES user_id=71163 Claudio, Ignore my last comment -- I was expecting to see 3 items, I didn't see that you had deleted one file. ---------------------------------------------------------------------- Comment By: Sean Leyne (seanleyne) Date: 2004-09-01 08:38 Message: Logged In: YES user_id=71163 Claudio, The script you uploaded??? I don't see anything. Terry, Like Claudio, I had no problems -- your script ran fine on v1.5.1. Have a look, though, at the script I've uploaded -- should significantly trim the execution time (by a factor of 30). ---------------------------------------------------------------------- Comment By: Claudio Valderrama C. (robocop) Date: 2004-09-01 02:37 Message: Logged In: YES user_id=62823 This is what I did: Trimmed the script and ran it. Created the db. Ran the procedure with current_timestamp as input. I got only zeroes. Couldn't observe any failure, at least in FB2. So I think we need data. Terry, please follow instructions: Observe the new script that I've uploaded. You can see I commented out unneeded things. Make a copy of your database (either backup and restore with another name or shutdown the FB server and copy the fdb). In the copied db, please drop all other unneeded procedures, then you can drop all the UDFs. Finally, try to get rid of any tables that you can. Drop them. You can't drop tables used by your problematic procedure, so you can act by trial and error. This will decrease the size of the db and will get rid of any unneded dependencies to reproduce the problem. Backup this trimmed db, zip the backup and try to upload it. Since it will be smaller, you may be able to submit it here. I'm betting we need data to be able to reproduce the problem, but only the relevant data, tables and procedures. ---------------------------------------------------------------------- Comment By: Claudio Valderrama C. (robocop) Date: 2004-09-01 02:13 Message: Logged In: YES user_id=62823 Terry: a) Do we need the UDFs to test your failure? For what I see, there are no further references to them than their declarations. b) Do we need a special input timestamp or any datetime causes the same failure? Do we need to run the procedure (execute it) or only prepare it to crash the server? c) Your db is defined in dialect 1. What are you trying to do with these four statements? select distinct 'Recurring Root' from OFI_TYPE into :LBL; select distinct 'Recurring Co/Location' from OFI_TYPE into :LBL; select distinct '% / Total' from OFI_TYPE into :LBL; select distinct NULL from OFI_TYPE into :ID; AFAICT, these aren't columns in the OFI_TYPE table. C. ---------------------------------------------------------------------- Comment By: TerryC (christotwy) Date: 2004-08-30 15:58 Message: Logged In: YES user_id=1112288 OK, installed and ran v1.5.1 locally; it crashes (and disconnects) with exactly the same code in firebird.log. ---------------------------------------------------------------------- Comment By: Sean Leyne (seanleyne) Date: 2004-08-30 11:58 Message: Logged In: YES user_id=71163 Terry, Please retry your script with the latest FB release v.1.5.1 ---------------------------------------------------------------------- Comment By: TerryC (christotwy) Date: 2004-08-30 11:51 Message: Logged In: YES user_id=1112288 Sorry RE: typo FB Version is 1.5.0.4306, not .4360 ---------------------------------------------------------------------- Comment By: TerryC (christotwy) Date: 2004-08-30 11:48 Message: Logged In: YES user_id=1112288 ...and here's firebird.log capturing a startup, crash and restart ---------------------------------------------------------------------- Comment By: TerryC (christotwy) Date: 2004-08-30 11:47 Message: Logged In: YES user_id=1112288 ...and here's firebird.log capturing a startup, crash and restart ---------------------------------------------------------------------- Comment By: TerryC (christotwy) Date: 2004-08-30 11:32 Message: Logged In: YES user_id=1112288 The SP is 15-16KB, 506 lines. ZIPped, the DB is only 618KB. If I can't upload the DB, will the script have more luck? The DB isn't that large. I will try to attach the script to this response. FB ver (fbserver.exe) == 1.5.0.4360 on Windows 2000, Server and Pro. ---------------------------------------------------------------------- Comment By: Sean Leyne (seanleyne) Date: 2004-08-27 16:51 Message: Logged In: YES user_id=71163 How big is the DB information? How big (bytes) is the SP? Does the server Firebird.log contains any message? Can you create a simple script, which shows the problem instead of a whole DB? Also, what version of FB are you using - v1.0.x, v1.5.x? What platform? ---------------------------------------------------------------------- Comment By: TerryC (christotwy) Date: 2004-08-27 14:42 Message: Logged In: YES user_id=1112288 Tried to upload DB with submission, but nothing went?? ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=109028&aid=1017870&group_id=9028 |
