--- Begin Message ---
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
--- End Message ---
