#15 null value passed to MATCH_SUBSTRUCT crashes the database

[sigsm]

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?

[Jerome Pansanel]

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

[sigsm]

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) FROM 3D_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;

[Jerome Pansanel]

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

[Jerome Pansanel]

The fix has been released in Mychem v0.9.2