Search problem after upgrade of MySQL from 3.23 to 4.1

User eda6f877b4

14-11-2004 14:55:44

After having upgraded my MySQL database from version 3.23 to version 4.1, I am experiencing disfunctional structure searches.





On the old system searching after a benzene ring would give me more than 1000 hits, using the upgraded version of the db, the number of hits is 0. (... and the same problem with eg. cyclohexane... etc.). Just a simpel H3C-CH3 as search criteria, gives a lot of hits....





Data was transferred from the old db to the new by using the MySQL dump utility.





My system is webbased running on both linux redhat 9.0 and win xp in test environment. I am using Tomcat 5.0.





Installing my .war file on a identical system, again using the old version (3.23) the structure search is again working properly!!





The marvin viewer can without problem display all my molecules in the db, also the ones containing benzene rings etc...





Can you point me in the right direction for solving the problem, please!





Dann

ChemAxon 9c0afc9aaf

14-11-2004 15:32:11

Hi,





Some quick tips:





- Please look at the error log of Tomcat (catalina.out) for any error messages.





- Please try to upgrade your MySQL JDBC driver to the latest stable version.


(maybe your old driver is not compatible with 4.x)





- Try to create a new table in the new (4.1) database, and import some structures. Can you search the new table properly ?








Szilard

User eda6f877b4

14-11-2004 18:48:47

There is no error messages in the log files in the server.





The mysql driver I am using is mysql-connector-java-3.0.15-ga, which as I can see is the latest stable release, and works perfectly for all the other queries etc. in the application.





I have tried the following:





1. removed the old structure and properties table. Created new ones using JChem manager. (I left out earlier that I am using JChem 2.1!).


-> this did not solve the problem!





2. Did draw a structure in the MSketch containing a cyclopentane, cyclohexane and a benzene ring. Query after only cyclehexane or benzene gave NO hits, searching after a cyclepentane gave some hits including my new compound.

ChemAxon 9c0afc9aaf

14-11-2004 19:35:02

I could reproduce the problem.





Still investigating the problem, but it's probably a 4.1 MySQL bug concerning binary AND operations.





WORKARUND: the bug won't affect searches in cached mode.


Please turn on structure caching for JChemSearch:





http://www.jchem.com/doc/api/chemaxon/jchem/db/JChemSearch.html#setStructureCaching(boolean)





If you are using the (modified) version of our JSP example, you should set this to true in "searching.jsp"





searcher.setStructureCaching(false);





In later versions caching is determined by a variable, you should write the


following line into the ".jchemsite" configuration file:


(also accessible via setup.jsp)





useStructureCache=true





Please note, that since version 2.3 we have significantly reduced the memory need for the cache.





For information about memory and cache please read the following:





This can be http://www.jchem.com/FAQ.html#outofmemory

User eda6f877b4

15-11-2004 06:39:32

Hi Szilard,





Fine that you could reproduce the problem....!!





I have implementet the suggested workaround, and the search is once again working.....!!





You mention a file called ".jchemsite", where can this be found??





I think I will upgrade my version of JChem, and keep the caching!





Dann

ChemAxon 9c0afc9aaf

15-11-2004 08:51:22

Hi,





The file ".jchemsite" is a property file, used by our open-source JSP example application to store settings.





In older JChem versions it's located under directly in the user home of the user, who runs Tomcat.





In newer versions it's located under <user_home>/chemaxon (Windows systems) or <user_home>/.chemaxon (UNIX-type systems).





The settings of the file can be conveniently modified from "setup.jsp"


A typical URL for setup.jsp is:





http://localhost:8080/jchem/examples/jsp1_x/setup.jsp





Please see


http://www.jchem.com/examples/jsp1_x/setup.html


for more details.

ChemAxon 9c0afc9aaf

15-11-2004 16:01:28

MySQL bug reported:





http://bugs.mysql.com/bug.php?id=6638

ChemAxon 9c0afc9aaf

15-11-2004 17:19:25

Actually this is not really a MySQL bug, rather an incompatible change.


(very unpleasant surprise though)





We have implemented a workaround, so the code will work on all MySQL versions.





The next JChem release (3.0) will work fine with MySQL 4.x.

User eda6f877b4

16-11-2004 05:27:18

Hi,





Thanks for your prompt action on this topic, you have been a great help!





Dann