Package: mysql-server-5.0 Version: 5.0.32-7etch12 Hi,
I need to report a bug for the below package (MySQL-Server 5.0.32-7etch12). debian40:~# dpkg --list mysql-server-5.0 Desired=Unknown/Install/Remove/Purge/Hold | Status=Not/Installed/Config-files/Unpacked/Failed-config/Half-installed |/ Err?=(none)/Hold/Reinst-required/X=both-problems (Status,Err: uppercase=bad) ||/ Name Version Description +++-============================-============================-======================================================================== ii mysql-server-5.0 5.0.32-7etch12 mysql database server binaries debian40:~# dpkg --status mysql-server-5.0 Package: mysql-server-5.0 Status: install ok installed Priority: optional Section: misc Installed-Size: 68928 Maintainer: Christian Hammers <[email protected]> Architecture: amd64 Source: mysql-dfsg-5.0 Version: 5.0.32-7etch12 Replaces: mysql-server (<< 5.0.32-7etch12), mysql-server-4.1 Provides: mysql-server, virtual-mysql-server, mysql-server-4.1 Depends: mysql-client-5.0 (>= 5.0.32-7etch12), libdbi-perl, perl (>= 5.6), libc6 (>= 2.3.5-1), libgcc1 (>= 1:4.1.1-12), libmysqlclient15off (>= 5.0.27-1), libncurses5 (>= 5.4-5), libreadline5 (>= 5.2), libstdc++6 (>= 4.1.1-12), libwrap0, zlib1g (>= 1:1.2.1), debconf (>= 0.5) | debconf-2.0, psmisc, passwd, lsb-base (>= 3.0-10) Pre-Depends: mysql-common (>= 5.0.32-7etch12), adduser (>= 3.40) Recommends: mailx Suggests: tinyca Conflicts: mysql-server (<< 5.0.32-7etch12), mysql-server-4.1 (<< 5.0.26-3) Conffiles: /etc/init.d/mysql-ndb-mgm d8e59f75aa722b2727d1e168cdc54a46 /etc/init.d/mysql-ndb cc36ed07930ea06a216b9c00bbeb56e3 /etc/init.d/mysql 4f0c573e38f141149bd19e4a929305b9 /etc/logrotate.d/mysql-server 3ca2603d73eeebf3e7978f0b6e572699 /etc/mysql/debian-start 49411590e584499b823314e9d1915da8 /etc/logcheck/ignore.d.workstation/mysql-server-5_0 20ccf274886d8f2897a10d9288579410 /etc/logcheck/ignore.d.server/mysql-server-5_0 20ccf274886d8f2897a10d9288579410 /etc/logcheck/ignore.d.paranoid/mysql-server-5_0 e8dbe35695437354553e61fd65b702ba Description: mysql database server binaries MySQL is a fast, stable and true multi-user, multi-threaded SQL database server. SQL (Structured Query Language) is the most popular database query language in the world. The main goals of MySQL are speed, robustness and ease of use. . This package includes the server and ndb-cluster binaries. ===================================== We are currently maintain an ERP which make use of InnoDB MySQL heavily. The ERP is writen in PHP. I discovered a serious bug in MySQL 5.0.32-7 on Debian Etch 4.0. The system was up-to-date, includding security. System uses only official, stable patches. I discovered this bug when archiving some data on ERP which has a TRANSACTIONAL sequence like: INSERT INTO (SELECT ... FROM) This bug occur always when doing a particular query. I tested it on other servers (MySQL 5.0.67, MySQL 5.0.90) and does not occur. I have been forced to compile the MySQL community server 5.0.67 from source with --prefix=/opt/mysql to be able to run the ERP in a stable manner. CONCLUSION: This bug does not occur on other MySQL versions, but only in MySQL 5.0.32-7. After using MySQL 5.0.67 community on the same OS and PHP version the bug didn't occur. Bug Severity: Critical Bug occurence: Always Bug Description: MySQL Crash, when doing a query ... Hints: If you run the query without INSERT INTO (), as SELECT ..., it does not crash the server. The query that does crash the server: INSERT INTO `erp_archive__2009_11_sum_stocklog_val` ( SELECT '', '2009-11-30', `item_code` AS y_code, `item_attrib` AS y_attrib, @y_prev_qty:= CAST(IFNULL((SELECT SUM(`item_qty`) FROM `erp_archive__2009_11_stocklog` WHERE (((`delivered`='1') AND (`date_time` LIKE '2009-11-%')) AND ((`item_code`=y_code) AND (`item_attrib`=y_attrib)) AND (`doc_type`='in'))),0) AS DECIMAL(20,4)) AS y_prev_qty, @y_int_i_qty:= CAST(IFNULL((SELECT SUM(`item_qty`) FROM `erp_archive__2009_11_stocklog` WHERE (((`delivered`='1') AND (`date_time` LIKE '2009-11-%')) AND ((`item_code`=y_code) AND (`item_attrib`=y_attrib)) AND ((`doc_type`='zx') AND (`partner_type`='z') AND (`item_qty`>0)))),0) AS DECIMAL(20,4)) AS y_int_i_qty, @y_int_o_qty:= CAST(IFNULL((SELECT SUM(`item_qty`) FROM `erp_archive__2009_11_stocklog` WHERE (((`delivered`='1') AND (`date_time` LIKE '2009-11-%')) AND ((`item_code`=y_code) AND (`item_attrib`=y_attrib)) AND ((`doc_type`='zx') AND (`partner_type`='z') AND (`item_qty`<0)))),0) AS DECIMAL(20,4)) AS y_int_o_qty, @y_buy_qty:= CAST(IFNULL((SELECT SUM(`item_qty`) FROM `erp_archive__2009_11_stocklog` WHERE (((`delivered`='1') AND (`date_time` LIKE '2009-11-%')) AND ((`item_code`=y_code) AND (`item_attrib`=y_attrib)) AND (`doc_type`='sp'))),0) AS DECIMAL(20,4)) AS y_buy_qty, @y_rbuy_qty:= CAST(IFNULL((SELECT SUM(`item_qty`) FROM `erp_archive__2009_11_stocklog` WHERE (((`delivered`='1') AND (`date_time` LIKE '2009-11-%')) AND ((`item_code`=y_code) AND (`item_attrib`=y_attrib)) AND (`doc_type`='sr'))),0) AS DECIMAL(20,4)) AS y_rbuy_qty, @y_sell_qty:= CAST(IFNULL((SELECT SUM(`item_qty`) FROM `erp_archive__2009_11_stocklog` WHERE (((`delivered`='1') AND (`date_time` LIKE '2009-11-%')) AND ((`item_code`=y_code) AND (`item_attrib`=y_attrib)) AND (`doc_type`='cs'))),0) AS DECIMAL(20,4)) AS y_sell_qty, @y_rsell_qty:= CAST(IFNULL((SELECT SUM(`item_qty`) FROM `erp_archive__2009_11_stocklog` WHERE (((`delivered`='1') AND (`date_time` LIKE '2009-11-%')) AND ((`item_code`=y_code) AND (`item_attrib`=y_attrib)) AND (`doc_type`='cr'))),0) AS DECIMAL(20,4)) AS y_rsell_qty, @y_final_qty:= CAST((@y_prev_qty + (@y_int_i_qty + @y_int_o_qty) + (@y_buy_qty + @y_rbuy_qty) + (@y_sell_qty + @y_rsell_qty)) AS DECIMAL(20,4)) AS y_final_qty, @y_prev_mprice:= CAST(IFNULL((SELECT (SUM(`item_qty` * `item_price`) / SUM(`item_qty`)) FROM `erp_archive__2009_11_stocklog` WHERE (((`delivered`='1') AND (`date_time` LIKE '2009-11-%')) AND ((`item_code`=y_code) AND (`item_attrib`=y_attrib)) AND (`doc_type`='in'))),0) AS DECIMAL(20,4)) AS y_prev_mprice, @y_int_i_mprice:= CAST(IFNULL((SELECT (SUM(`item_qty` * `item_price`) / SUM(`item_qty`)) FROM `erp_archive__2009_11_stocklog` WHERE (((`delivered`='1') AND (`date_time` LIKE '2009-11-%')) AND ((`item_code`=y_code) AND (`item_attrib`=y_attrib)) AND ((`doc_type`='zx') AND (`partner_type`='z') AND (`item_qty`>0)))),0) AS DECIMAL(20,4)) AS y_int_i_mprice, @y_buy_mprice:= CAST(IFNULL((SELECT (SUM(`item_qty` * `item_price`) / SUM(`item_qty`)) FROM `erp_archive__2009_11_stocklog` WHERE (((`delivered`='1') AND (`date_time` LIKE '2009-11-%')) AND ((`item_code`=y_code) AND (`item_attrib`=y_attrib)) AND (`doc_type`='sp'))),0) AS DECIMAL(20,4)) AS y_buy_mprice, @y_rbuy_mprice := CAST(IFNULL((SELECT (SUM(`item_qty` * `item_price`) / SUM(`item_qty`)) FROM `erp_archive__2009_11_stocklog` WHERE (((`delivered`='1') AND (`date_time` LIKE '2009-11-%')) AND ((`item_code`=y_code) AND (`item_attrib`=y_attrib)) AND (`doc_type`='sr'))),0) AS DECIMAL(20,4)) AS y_rbuy_mprice, @y_sell_mprice:= CAST(IFNULL((SELECT (SUM(`item_qty` * (`item_price` * ((100 - `item_discount`) / 100))) / SUM(`item_qty`)) FROM `erp_archive__2009_11_stocklog` WHERE (((`delivered`='1') AND (`date_time` LIKE '2009-11-%')) AND ((`item_code`=y_code) AND (`item_attrib`=y_attrib)) AND (`doc_type`='cs'))),0) AS DECIMAL(20,4)) AS y_sell_mprice, @y_rsell_mprice:= CAST(IFNULL((SELECT (SUM(`item_qty` * (`item_price` * ((100 - `item_discount`) / 100))) / SUM(`item_qty`)) FROM `erp_archive__2009_11_stocklog` WHERE (((`delivered`='1') AND (`date_time` LIKE '2009-11-%')) AND ((`item_code`=y_code) AND (`item_attrib`=y_attrib)) AND (`doc_type`='cr'))),0) AS DECIMAL(20,4)) AS y_rsell_mprice, @y_cmp_mprice:= CAST(IFNULL((SELECT (SUM(`item_qty` * `item_price`) / SUM(`item_qty`)) FROM `erp_archive__2009_11_stocklog` WHERE (((`delivered`='1') AND (`date_time` LIKE '2009-11-%')) AND ((`item_code`=y_code) AND (`item_attrib`=y_attrib)) AND ((`doc_type`='in') OR ((`doc_type`='zx') AND (`partner_type`='z') AND (`item_qty`>0)) OR (`doc_type`='sp') OR (`doc_type`='cr')))),0) AS DECIMAL(20,4)) AS y_cmp_mprice FROM `erp_archive__2009_11_stocklog` WHERE ((`delivered`='1') AND (`date_time` LIKE '2009-11-%')) GROUP BY `item_code`, `item_attrib` ORDER BY `item_code`, `item_attrib` ) =================== The schema of the two tables: CREATE TABLE `erp_archive__2009_11_stocklog` ( `id` varchar(36) NOT NULL, `date_time` varchar(20) NOT NULL COMMENT 'YYYY-MM-DD HH:II:SS', `doc_type` char(2) NOT NULL COMMENT 'document type', `doc_id` varchar(10) NOT NULL COMMENT 'id document', `imp_exp` enum('','i','e') NOT NULL COMMENT '''''=internal ; i=import ; e=export', `partner_type` enum('','c','p','s','z','x') NOT NULL COMMENT '''''=internal ; c=corporate customer ; p=private customer ; s=supplier ; z = int. adj ; x = int transf.', `partner_id` varchar(10) NOT NULL COMMENT 'Partner ID', `warehouse` tinyint(2) unsigned NOT NULL COMMENT 'warehouse ID', `item_code` varchar(20) NOT NULL COMMENT 'item unique code', `item_attrib` varchar(50) NOT NULL COMMENT 'max. 50 chars', `item_qty` decimal(11,4) NOT NULL default '0.0000' COMMENT 'item quantity +in/-out', `item_price` decimal(11,4) unsigned NOT NULL default '0.0000' COMMENT 'item stock in/out price except. transport', `item_transport` decimal(11,4) unsigned NOT NULL default '0.0000' COMMENT 'item transport price on in/out', `item_tax` decimal(4,2) unsigned NOT NULL default '0.00' COMMENT 'item tax % (percent)', `item_discount` decimal(4,2) unsigned NOT NULL default '0.00' COMMENT 'item discount %', `delivered` tinyint(1) unsigned NOT NULL default '0' COMMENT 'IN: 0=on ordered ; 1=in warehouse // OUT: 0=reserved ; 1=delivered', PRIMARY KEY (`id`), KEY `date` (`date_time`(10)), KEY `doc_type` (`doc_type`), KEY `item_group` (`warehouse`,`item_code`,`item_attrib`), KEY `partner` (`partner_type`,`partner_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='ERP - Inventory - Stocks Log'; CREATE TABLE `erp_archive__2009_11_sum_stocklog_val` ( `id` int(10) unsigned NOT NULL auto_increment, `date` varchar(10) NOT NULL COMMENT 'YYYY-MM-DD', `item_code` varchar(20) NOT NULL COMMENT 'Item Unique Code', `item_attrib` varchar(50) NOT NULL COMMENT 'Item Attribute', `q_prev` decimal(20,4) NOT NULL COMMENT 'Total Qty - Prev', `q_in_i` decimal(20,4) NOT NULL COMMENT 'Total Qty Internal - IN', `q_in_o` decimal(20,4) NOT NULL COMMENT 'Total Qty Internal - OUT', `q_buy` decimal(20,4) NOT NULL COMMENT 'Total Qty - Buy', `q_rbuy` decimal(20,4) NOT NULL COMMENT 'Total Qty - RBuy', `q_sell` decimal(20,4) NOT NULL COMMENT 'Total Qty - Sell', `q_rsell` decimal(20,4) NOT NULL COMMENT 'Total Qty - RSell', `q_fin` decimal(20,4) NOT NULL COMMENT 'Total Qty - FINAL', `mp_prev` decimal(20,4) NOT NULL COMMENT 'Medium Price - PREV', `mp_int_i` decimal(20,4) NOT NULL COMMENT 'Medium Price Internals IN (Z Type)', `mp_buy` decimal(20,4) NOT NULL COMMENT 'Medium Price - BUY', `mp_rbuy` decimal(20,4) NOT NULL COMMENT 'Medium Price - RBUY', `mp_sell` decimal(20,4) NOT NULL COMMENT 'Medium Price - SELL', `mp_rsell` decimal(20,4) NOT NULL COMMENT 'Medium Price - RSELL', `mp_cmp` decimal(20,4) NOT NULL COMMENT 'Medium Price - CMP', PRIMARY KEY (`id`), KEY `date` (`date`), KEY `item_group` (`item_code`,`item_attrib`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='ERP - TEMPLATE - v.1.5 - Inventory Log Value Sum / Overall' AUTO_INCREMENT=1 ; =================== Please anounce me when this bug is fixed, and I will move again on the MySQL server from Debian Distro (prefered agains keeping my separate MySQL server compiled from source, to be able to keep the ERP running. Regards, Radu -- To UNSUBSCRIBE, email to [email protected] with a subject of "unsubscribe". Trouble? Contact [email protected]
