BDB tables crashing mysql 3.23.33
Hi folks, I'm having a hard time using bdb tables. Here are the
details...
I've create a few BDB tables, which are causing me great amounts
of grief. The tables worked fine for a while, then suddenly
started crashing mysqld. I decided to drop the offending
database, and re-create it. No problem so far. Right after
I run the offending queries (see below), mysqld dies. Any subsequent
connection attempt to mysqld hangs, and the server is basically
dead (requiring a killall -9 mysqld). There is a file called
log.01 that appeared, as well as an error message (see below).
I can replicate this error quite easily, as it happens each time the
query
is run.
CREATE TABLE tblCharge (
ChargeID int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
ServiceID int(10) unsigned DEFAULT '0' NOT NULL,
ChargeDate date DEFAULT '-00-00' NOT NULL,
ChargeAmount decimal(20,2) DEFAULT '0.00' NOT NULL,
FedTaxes decimal(20,2) DEFAULT '0.00' NOT NULL,
ProvTaxes decimal(20,2) DEFAULT '0.00' NOT NULL,
ChargeStatus enum('New','Auth','Unauth','Sale','Denied','Refund')
DEFAULT 'New' NOT NULL,
ChargeAuthorizationMessage text,
ChargeComment text,
ChargeTimeStamp varchar(20),
PRIMARY KEY (ChargeID),
KEY ServiceID (ServiceID),
KEY ChargeDate (ChargeDate)
) type=BDB;
Query #1:
BEGIN;
INSERT INTO tblCharge
VALUES(NULL,1,CURRENT_DATE(),1,1,1,'New',NULL,NULL,UNIX_TIMESTAMP(NOW()));
COMMIT;
Query #2
BEGIN;
UPDATE tblCharge SET ChargeAuthorizationMessage = 'blablabla' WHERE
ChargeID = 1;
COMMIT;
Query #3
BEGIN;
INSERT INTO tblCharge
VALUES(NULL,1,CURRENT_DATE(),1,1,1,'New',NULL,NULL,UNIX_TIMESTAMP(NOW()));
ERROR 2013: Lost connection to MySQL server during query
mysql> COMMIT;
Number of processes running now: 0
010228 13:59:40 mysqld restarted
ERROR 2006: MySQL server has gone away
No connection. Trying to reconnect...
Connection id:4
Current database: Funio
./mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var \
--user=root --pid-file=/usr/local/mysql/var/suave.pid --skip-locking
./mysqld: ready for connections
mysqld got signal 11;
The manual section 'Debugging a MySQL server' tells you how to use a
stack trace and/or the core file to produce a readable backtrace that
may
help in finding out why mysqld died
Attemping backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong
I've replicated this problem on 3 different machines, and on 3.23.33 and
3.23.32.
I had been using BDB tables for a couple weeks now in testing, without a
hitch
until the above query starting being used. I also tried to recompile
mysql
statically, with the same results.
I really hope there is an easy patch/fix to this problem, as my
development
team has just finished writing 15000 lines of code and use DBD tables
extensively throughout the application. My company has purchased a
basic license for mysql, and quite frankly this error really concerns
me.
If it was this easy to crash BDB tables and there is no quick fix, I
will
have to stop using mysql altogether throughout my company. We rely
heavily
on mysql right now, and this kind of error makes me wonder if this is
not a
mistake. Until a solution can be found, I will instruct my team to
start porting the application to postgres, I simply can't take the
chance of using these versions of mysql in production.
Description:
BDB tables crash very easily
How-To-Repeat:
See above
Fix:
no idea
Originator: Mark Steele
Organization:
Mark Steele
Vice president research and development
Inet Technologies Inc.
[EMAIL PROTECTED]
MySQL support: [email support]
Synopsis: BDB tables crash
Severity:
Priority:
Category: mysql
Class:
Release: mysql-3.23.32 (Source distribution)
Server: /usr/local/mysql/bin/mysqladmin Ver 8.14 Distrib 3.23.32, for
pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 3.23.32
Protocol version10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 1 min 12 sec
Threads: 1 Questions: 61 Slow queries: 0 Opens: 48 Flush tables: 1
Open tables: 42 Queries per second avg: 0.847
Environment:
System: Linux suave 2.4.1 #11 Sat Feb 17 00:30:04 EST 2001 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from
/usr/lib/gcc-lib/i386-slackware-linux/2.95.2/specs
gcc version 2.95.2 19991024 (release)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS=''
LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Feb 16 20:43 /lib/libc.so.6 ->
libc-2.2.1.so
-rwxr-xr-x 1 root root 10088
Re: BDB tables crashing mysql 3.23.33
Hi Sasha,
The patch you sent me seems to have created other problems,
or simply has allowed to find new ones :)
I'm working on isolating exactly which query makes the bdb
tables crash. Full bug report on the way.
So far, it looks like left joins on bdb tables does something weird.
I was using the following query:
SELECT ProductName, tblProduct.ProductID
FROM tblService LEFT JOIN tblPackage ON (tblService.PackageID =
tblPackage.PackageID)
LEFT JOIN tblConfig ON (tblConfig.ServiceID = tblService.ServiceID)
LEFT JOIN tblProduct ON (tblConfig.ProductID = tblProduct.ProductID)
LEFT JOIN tblProductConfig ON (tblProduct.ProductID =
tblProductConfig.ProductID)
WHERE ProductGroupID = '4' AND tblService.ServiceID ='2'
AND ModifyAllow = 'Y' AND DefaultValue IS NULL AND UserConfigFlag = 'Y'
GROUP BY tblProduct.ProductID;
which returned no results (it should have). the query did work before
some of the tables were switched to BDB.
I rewrote it look like:
select p.ProductName,p.ProductID FROM tblProduct p, tblService s,
tblConfig c,tblProductConfig pc,
tblPackage pkg WHERE s.PackageID = pkg.PackageID AND c.ServiceID =
s.ServiceID
AND c.ProductID = p.ProductID AND p.ProductID = pc.ProductID AND
p.ProductGroupID = 4
AND s.ServiceID = 2 AND pc.ModifyAllow = 'Y' AND pc.DefaultValue IS NULL
AND pc.UserConfigFlag = 'Y' GROUP BY p.ProductID;
which returns the expected results.
The error log is reporting the following error, but I haven't pinpointed
exactly what triggers it yet.
010301 21:09:51 bdb: transaction has active cursors
010301 21:09:51 bdb: PANIC: Invalid argument
010301 21:09:51 Aborted connection 16 to db: 'Funio' user: 'funio'
host: `localhost' (Got an error reading communication packets)
The error seems to be showing up after doing a transaction which has
executed successfully (I'll confirm shortly).
Here's the table structure:
CREATE TABLE tblProduct (
ProductID int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
ProductName varchar(255) DEFAULT '' NOT NULL,
Status enum('Active','Disabled') DEFAULT 'Active' NOT NULL,
ProductTable varchar(255) DEFAULT '' NOT NULL,
AddCommand text,
RemoveCommand text,
EnableCommand text,
DisableCommand text,
ModifyCommand text,
ProductGroupID int(10) unsigned DEFAULT '0' NOT NULL,
PRIMARY KEY (ProductID),
KEY ProductGroupID (ProductGroupID),
KEY Status (Status)
);
CREATE TABLE tblService (
ServiceID int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
UserID int(10) unsigned DEFAULT '0' NOT NULL,
PackageID int(10) unsigned DEFAULT '0' NOT NULL,
StartDate date DEFAULT '-00-00' NOT NULL,
EndDate date DEFAULT '-00-00' NOT NULL,
RebillDate date DEFAULT '-00-00' NOT NULL,
RebillRetryDate date DEFAULT '-00-00' NOT NULL,
Status enum('Active','Disabled','Removed') DEFAULT 'Active' NOT NULL,
ScheduleToBeRemoved enum('Yes','No') DEFAULT 'No' NOT NULL,
ServiceActivationIP char(20),
ServiceActivationTimeStamp char(20),
PRIMARY KEY (ServiceID),
KEY UserID (UserID),
KEY PackageID (PackageID),
KEY StartDate (StartDate),
KEY EndDate (EndDate),
KEY RebillDate (RebillDate),
KEY RebillRetryDate (RebillRetryDate),
KEY Status (Status)
) type=BDB;
CREATE TABLE tblConfig (
ConfigID int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
ServiceID int(10) unsigned DEFAULT '0' NOT NULL,
ProductID int(10) unsigned DEFAULT '0' NOT NULL,
Configured enum('Y','N') DEFAULT 'N' NOT NULL,
PRIMARY KEY (ConfigID),
KEY Configured (Configured),
KEY ServiceID (ServiceID),
KEY ProductID (ProductID)
) type=BDB;
CREATE TABLE tblProductConfig (
ProductFieldID int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
ProductID int(10) unsigned DEFAULT '0' NOT NULL,
UserConfigFlag enum('Y','N') DEFAULT 'Y' NOT NULL,
RequiredConfigFlag enum('Y','N') DEFAULT 'Y' NOT NULL,
ModifyAllow enum('Y','N') DEFAULT 'Y' NOT NULL,
ProductField text NOT NULL,
DefaultValue text,
ProductFieldCheckRegExp varchar(255),
ProductFieldError varchar(100),
PRIMARY KEY (ProductFieldID),
KEY ProductID (ProductID),
KEY UserConfigFlag (UserConfigFlag),
KEY RequiredConfigFlag (RequiredConfigFlag)
);
CREATE TABLE tblPackage (
PackageID int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
PackageName varchar(255),
PackageGroupID int(10) unsigned DEFAULT '0' NOT NULL,
PackageTypeID int(10) unsigned DEFAULT '0' NOT NULL,
PackagePeriod tinyint(3) unsigned,
PackagePrice decimal(10,2),
PackageInstallationFee decimal(10,2),
PackageProfit decimal(10,2),
PackageAnswer text,
Rebill enum('Y','N') DEFAULT 'Y' NOT NULL,
Status enum('Active','Disabled'),
PRIMARY KEY (PackageID),
KEY PackageGroupID (PackageGroupID),
KEY Rebill (Rebill),
KEY PackageTypeID (PackageTypeID)
);
Sasha Pachev wrote:
>
> On Wednesday 28 February 2001 22:04, [EMAIL PROTECTED] wrote:
> >Hi folks, I'm having a hard time using bdb tables. Here are the
> >details...
> >
> >I've create a few BDB tables
