null value passed to MATCH_SUBSTRUCT crashes the database
Status: Beta
Brought to you by:
pansanel
This happened to me, but I don't know if this is repeatable. I had some 40K+ entries in a database and did a substructure search via MATCH_SUBSTRUCT. The search kept crashing the database although it recovered (in effect resetting the connection). Tried a few things but in the end if I excluded (via sql) the entries with Null value in the column used for the reference_obmol blob it stopped crashing. Can someone verify this or is this is already known perhaps?
Hi,
Yes, it may be a bug. I should add a check on the data type before to do any match substruct test. Can you send be the table structures (I don't need any data) of your database and the query you did?
Cheers,
Jerome
Hi,
The table structures and the query were taken verbatim from the examples in the handbook. The 3D_structure table had entries of an empty string that resulted in a NULL entry into the bin_structure for obserialized when populated.
But in case it helps the exported structures from my database and the query that crashed are pasted in below.
best,
Siggi
--
-- Table structure for table
compounds
--
CREATE TABLE IF NOT EXISTS
compounds
(id
int(11) unsigned NOT NULL AUTO_INCREMENT,name
varchar(255) COLLATE utf8_bin NOT NULL,created
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,modified
timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',PRIMARY KEY (
id
),UNIQUE KEY
name
(name
)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Compound Library' AUTO_INCREMENT=40445 ;
--
-- Table structure for table
3D_structures
--
CREATE TABLE IF NOT EXISTS
3D_structures
(compound_id
int(11) unsigned NOT NULL,molfile
text COLLATE utf8_bin NOT NULL,PRIMARY KEY (
compound_id
)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Compound 3D Structures';
--
-- Table structure for table
bin_structures
--
CREATE TABLE IF NOT EXISTS
bin_structures
(compound_id
int(11) unsigned NOT NULL,fp2
blob,obserialized
blob,PRIMARY KEY (
compound_id
),UNIQUE KEY
compound_id
(compound_id
)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Compound Binary Structures';
--
-- Insertion of data into the
bin_structures
table--
--
INSERT INTO
bin_structures
(compound_id
,fp2
,obserialized
)SELECT
compound_id
, FINGERPRINT2(molfile
),MOLECULE_TO_SERIALIZEDOBMOL(
molfile
) FROM3D_structures
;--
-- Query that crashes if there are NULL values in bin_structures
--
SELECT
compounds
.name
FROM
compounds
,bin_structures
WHERE
compounds
.id
=bin_structures
.compound_id
AND MATCH_SUBSTRUCT('[OH]c1ccccc1',
obserialized
);LIMIT 10;
Hi,
Can you replace your current molmatch.c file with this replacement and tell me if it works better (it shoud, it test this fix). If it works are requested, I will release a new version of Mychem within the next week.
Cheers,
Jerome
The fix has been released in Mychem v0.9.2