[debian-mysql] Bug#577403: Serious BUG - MySQL Server, Debian Etch 4.0
Radu Ovidiu Ilies
iradu at unix-world.org
Sun Apr 11 10:40:07 UTC 2010
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 <ch at debian.org>
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
More information about the pkg-mysql-maint
mailing list