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 '0000-00-00' NOT NULL,
EndDate date DEFAULT '0000-00-00' NOT NULL,
RebillDate date DEFAULT '0000-00-00' NOT NULL,
RebillRetryDate date DEFAULT '0000-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, 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.0000000001 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 '0000-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()));
> >
> ><CRASH HERE>
>
> I do have a patch for it now. From what I understood in the code, this is
> actually a more correct way to do things, but I would like to get Monty's
> approval to be sure. After the patch, MySQL still passes our test suite,
> which is a good sign. So if you are in dire straits, go ahead and apply it,
> otherwise, wait until 3.23.34 - if there is something wrong with the current
> fix, it will be corrected by then. Here it is:
>
> --- 1.33/sql/field.cc Wed Feb 7 19:43:54 2001
> +++ 1.34/sql/field.cc Thu Mar 1 11:49:05 2001
> @@ -4075,8 +4075,8 @@
> const char *Field_blob::unpack(char *to, const char *from)
> {
> memcpy(to,from,packlength);
> + ulong length=get_length(from);
> from+=packlength;
> - ulong length=get_length();
> if (length)
> memcpy_fixed(to+packlength, &from, sizeof(from));
> else
>
> --
> MySQL Development Team
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Sasha Pachev <[EMAIL PROTECTED]>
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
> /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA
> <___/
--
Mark Steele
Vice president research and development
Inet Technologies Inc.
[EMAIL PROTECTED]
010110010110111101110101001000000110000101110010011001010010000001100100011101010110110101100010
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
