| Bug #21000 | Stored proc crashes Query Browser & Admin | ||
|---|---|---|---|
| Submitted: | 12 Jul 2006 12:49 | Modified: | 17 Jul 2006 11:07 |
| Reporter: | Bogdan Enache | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Query Browser | Severity: | S1 (Critical) |
| Version: | 1.1.20 | OS: | Windows (Windows XP) |
| Assigned to: | CPU Architecture: | Any | |
[12 Jul 2006 12:52]
Bogdan Enache
Sorry, I made a mistake in the info written above, because I'm in a hurry. I have two views, one which selects something like "select from tb1 join tb2" and the second one "select from tb1 join tb3". The stored proc chooses from what view to select data based on a value in tb1. Everything else stays the same, still crashes.... Thank you.
[12 Jul 2006 12:54]
Bogdan Enache
*edited synopsis (typo)
[12 Jul 2006 13:12]
Bogdan Enache
Event Viewer in Windows reports: Faulting application mysqlquerybrowser.exe, version 1.1.20.0, faulting module mysqlquerybrowser.exe, version 1.1.20.0, fault address 0x00182e10. For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
[12 Jul 2006 13:49]
MySQL Verification Team
Thank you for the bug report. Could you please provide a dump as attached file with the complete test case, I am getting several copying and pasting from the browser. Thanks in advance.
[12 Jul 2006 14:19]
Bogdan Enache
SQL dump of the database
Attachment: app_dump.sql (application/octet-stream, text), 8.55 KiB.
[12 Jul 2006 14:22]
Bogdan Enache
File with SQL dump added.
[12 Jul 2006 15:33]
MySQL Verification Team
Thank you for the feedback. I was able to repeat with Query Browser 1.1.20 but not with 1.2.1 beta (however 1.2.1 version not retrieved a result set when actually exists) so another bug report should be opened. Could you please test the 1.2.1 beta version? Thanks in advance.
[12 Jul 2006 17:40]
Bogdan Enache
I can't find any link for 1.2.1 beta .... Until then, testing some configurations on home machine: * Query Browser 1.1.17 with MySQL 5.0.16: works OK ! I am not able to replicate the bug on this setup, no matter how many runs and how fast. Also, proper results returned by query browser. * Updated to MySQL Server 5.0.22. Can't replicate bug, no matter what. * Updated Query Browser to 1.1.20 (with server 5.0.22). It hang after 2 fast runs of the stored proc. "The instruction at "0x00582e10" referenced memory at "0x02475a18". The memory could not be "read"." Etc... Tested again, but this time with 10 seconds delay between every run. It didn't crashed after 10 delayed runs, but crashed immediately after 2 quick presses of Execute button. Apparently there is a timing problem with Query Browser, not with the server itself.
[12 Jul 2006 17:45]
Bogdan Enache
Downgraded Query Browser to 1.1.18. It crashes in the same manner. I can't find 1.1.17 to re-download it to test again. Thank you.
[12 Jul 2006 18:17]
MySQL Verification Team
Thank you for the feedback. You can get it from here: http://dev.mysql.com/downloads/gui-tools/index.html
[12 Jul 2006 21:17]
Bogdan Enache
Can't replicate this with Query Browser 1.2.1 beta. It doesn't crash, and returns corect results. Nevertheless, I see something that maybe looks like a memory leak. After a few hundred function calls, memory usage for MySQLQueryBrowser increased constantly from 16 MB to 29 MB, and stayed there. The average was around 28k per procedure call. Used memory didn't decrease. Is it supposed to retain the values of past queries ? Might be related to the bug in 1.1.18 - 1.1.20 ?
[13 Jul 2006 12:37]
Bogdan Enache
I have just tried 1.2.1 on the machine at work. Same behaviour as at home. No crashes, correct result sets returned, but used memory increases after each query.
[17 Jul 2006 9:34]
Bogdan Enache
Should I forget about this bug (as it appears it's solved in 1.2.1beta) and post a new one about a possible memory leak in 1.2.1beta ? (Even if they seem to be related?)
[17 Jul 2006 11:07]
MySQL Verification Team
Thank you for the bug report. I am closing this bug since it is fixed in released version. Please feel free for to open a new bug report for the memory leak you found. Thanks in advance.
Description: I have a database with some tables in it, linked by foreign keys. I also have a view, that selects from something like (tb1 join tb2) or (tb1 join tb3), depending on the value of a variable in tb1. I also created a procedure that select * from that view. When running the stored proc, Query Browser crashes after 2 runs, but returns the correct result set. After this, sometimes, Administrator can't access any tables (for example if I try to edit table tb1). This makes me thinking it's a problem with the server itself, but i'm not sure. Here is a piece of the sql dump created by mysqldump, which should contain everything needed: /////////////////////////////////////////////////////////////////////////// -- MySQL dump 10.10 -- -- Host: localhost Database: app -- ------------------------------------------------------ -- Server version 5.0.22-community-nt /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `clienti` -- DROP TABLE IF EXISTS `clienti`; CREATE TABLE `clienti` ( `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Cheia surogat', `tip` tinyint(3) unsigned NOT NULL default '2' COMMENT 'Tip: 0 -> pers fizica, 1 -> pers juridica', `denumire` varchar(240) collate utf8_romanian_ci NOT NULL default '' COMMENT 'Denumire client', `upd_time` timestamp NOT NULL default '0000-00-00 00:00:00' COMMENT 'Timpul ultimului update', `upd_user` int(10) unsigned NOT NULL default '0' COMMENT 'Userul care a facut ultimul update', `obs` text collate utf8_romanian_ci COMMENT 'Observatii', `adresa` text collate utf8_romanian_ci NOT NULL COMMENT 'Adresa principala / sediu', PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_romanian_ci COMMENT='Tabela clienti'; -- -- Dumping data for table `clienti` -- /*!40000 ALTER TABLE `clienti` DISABLE KEYS */; LOCK TABLES `clienti` WRITE; INSERT INTO `clienti` VALUES (2,0,'Bogdan','2006-07-07 12:57:05',1,'eu','str Varful cu Dor'),(3,1,'Exito srl','2006-07-07 12:58:48',1,'eu','str Varful cu Dor'),(5,1,'Blabla srl','2006-07-07 13:14:15',1,'firma de renume','str Mamaia'),(6,0,'Enache Bogdan','2006-07-11 08:19:04',0,'Fibra optica 100 GBPS','1 Decembrie 1918'),(7,0,'Enache Bogdanel','2006-07-11 08:31:58',0,'Fibra optica 100 GBPS','1 Decembrie 1918'),(8,0,'gheorghe gh v','2006-07-11 12:18:31',1,'jdosa\r\nsf\r\nsaf\r\nsa','sfskaj'),(9,0,'Dana d','2006-07-12 06:28:32',1,'assa\r\nasdsa\r\nsa\r\ndsa','Constanţa\r\nbdul 1 Decembrie 1918'),(10,0,'Dana x','2006-07-12 06:37:39',1,'assa\r\nasdsa\r\nsa\r\ndsa','Constanţa\r\nbdul 1 Decembrie 1918'),(11,1,'jsandak SRl','2006-07-12 09:37:49',1,'kdwl\r\nf\r\nf\r\nsadf','wf;la\r\nf\r\nf\r\n\r\nfs'),(12,0,'fklas klsaflak','2006-07-12 11:22:19',1,'jklajflkwa','klwajlfkwql'),(16,0,'fklas klsaflak','2006-07-12 11:25:50',1,'jklajflkwa','klwajlfkwql'),(17,0,'fklas klsaflak','2006-07-12 11:26:53',1,'jklajflkwa','klwajlfkwql'); UNLOCK TABLES; /*!40000 ALTER TABLE `clienti` ENABLE KEYS */; -- -- Table structure for table `persfiz` -- DROP TABLE IF EXISTS `persfiz`; CREATE TABLE `persfiz` ( `id` int(10) unsigned NOT NULL auto_increment, `cl_id` int(10) unsigned NOT NULL default '0', `cnp` varchar(13) collate utf8_romanian_ci NOT NULL default '', `serieci` varchar(8) collate utf8_romanian_ci NOT NULL default '', PRIMARY KEY (`id`), UNIQUE KEY `cl_id` (`cl_id`), UNIQUE KEY `cnp` (`cnp`), UNIQUE KEY `serieci` (`serieci`), CONSTRAINT `persfiz_id` FOREIGN KEY (`cl_id`) REFERENCES `clienti` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_romanian_ci COMMENT='Tabela persoane fizice'; -- -- Dumping data for table `persfiz` -- /*!40000 ALTER TABLE `persfiz` DISABLE KEYS */; LOCK TABLES `persfiz` WRITE; INSERT INTO `persfiz` VALUES (2,2,'1810711134272','gr111198'),(3,6,'1880811134272','GR123456'),(4,7,'1680941134272','GV153457'),(5,8,'1850811134272','GG999999'),(6,9,'2810811134272','GV112233'),(7,10,'2810811135272','GV112633'),(8,12,'4810811134272','TT999911'),(9,16,'4810871134272','TT999971'),(10,17,'4810871134275','TT999975'); UNLOCK TABLES; /*!40000 ALTER TABLE `persfiz` ENABLE KEYS */; -- -- Table structure for table `persjur` -- DROP TABLE IF EXISTS `persjur`; CREATE TABLE `persjur` ( `id` int(10) unsigned NOT NULL auto_increment, `cl_id` int(10) unsigned NOT NULL default '0', `cui` varchar(24) collate utf8_romanian_ci NOT NULL default '', `regcom` varchar(24) collate utf8_romanian_ci NOT NULL default '', `iban` varchar(24) collate utf8_romanian_ci NOT NULL default '', `banca` varchar(200) collate utf8_romanian_ci NOT NULL default '', PRIMARY KEY (`id`), UNIQUE KEY `cl_id` (`cl_id`), UNIQUE KEY `cui` (`cui`), UNIQUE KEY `regcom` (`regcom`), UNIQUE KEY `iban` (`iban`), CONSTRAINT `persjur_id` FOREIGN KEY (`cl_id`) REFERENCES `clienti` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_romanian_ci COMMENT='Tabela persoane juridice'; -- -- Dumping data for table `persjur` -- /*!40000 ALTER TABLE `persjur` DISABLE KEYS */; LOCK TABLES `persjur` WRITE; INSERT INTO `persjur` VALUES (1,3,'235238','j13/2005/224242','RO3212345678901234567890',''),(2,5,'13131','j13/2004/1234','RO1234567890123456789012',''),(3,11,'J13/1242123/2006','238523829','RO2412345612345678901234','Transilvania'); UNLOCK TABLES; /*!40000 ALTER TABLE `persjur` ENABLE KEYS */; -- -- Table structure for table `view_cl_fiz` -- DROP TABLE IF EXISTS `view_cl_fiz`; /*!50001 DROP VIEW IF EXISTS `view_cl_fiz`*/; /*!50001 DROP TABLE IF EXISTS `view_cl_fiz`*/; /*!50001 CREATE TABLE `view_cl_fiz` ( `id` int(10) unsigned, `tip` tinyint(3) unsigned, `denumire` varchar(240), `upd_time` timestamp, `upd_user` int(10) unsigned, `obs` text, `adresa` text, `cnp` varchar(13), `serieci` varchar(8) ) */; -- -- Table structure for table `view_cl_jur` -- DROP TABLE IF EXISTS `view_cl_jur`; /*!50001 DROP VIEW IF EXISTS `view_cl_jur`*/; /*!50001 DROP TABLE IF EXISTS `view_cl_jur`*/; /*!50001 CREATE TABLE `view_cl_jur` ( `id` int(10) unsigned, `tip` tinyint(3) unsigned, `denumire` varchar(240), `upd_time` timestamp, `upd_user` int(10) unsigned, `obs` text, `adresa` text, `cui` varchar(24), `regcom` varchar(24), `iban` varchar(24) ) */; END */;; /*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;; /*!50003 DROP PROCEDURE IF EXISTS `cl_view` */;; /*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER"*/;; /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `cl_view`(IN cl_id INT) BEGIN DECLARE t INT; SELECT tip INTO t FROM clienti WHERE id = cl_id; IF t = 0 THEN /* pers fizica */ SELECT * FROM view_cl_fiz WHERE id = cl_id; ELSE /* pers juridica */ SELECT * FROM view_cl_jur WHERE id = cl_id; END IF; END */;; /////////////////////////////////////////////////////////////////// End of sql dump. How to repeat: Load the dumped schema and try to run the proc a few times, in query browser or PHP.