Some questions about upgrade of MySQL engine to version 5.0

We recently upgraded our MySQL version to 5.0 to take advantage of some of its new features relative to 4.0.x. Mostly, it works fine and as expected with JChem (3.0.9). However, in one MySQL database where we were using sql statements that pick data from multiple tables within the database (not just the structure table), we have found very extended search times. Here is a sample sql statement looking for compound with cd_id = 3000 submitted via a somewhat modified version of the distributed "query.jsp" script:





select distinct Lab_info.cd_id from compound, Lab_info where compound.cd_id=Lab_info.cd_id and compound.cd_id=3000;





where compound is the structure table. Structure searches complete in a normal time frame. It appears that JChem, doesn't get a correct reponse from the mysql engine about the completion of the search? (i.e., at this test comdition: "if(searcher.isRunning()) {")... as we only get search results after a timeout ....Can someone provide some insight?





Thanks, Manton

Hi,





Please note that MySQL 5.0 is in the development phase currently, so it may contain numerous bugs (not recommended for live systems).


We usually only only test JChem against stable versions.





The general SQL query you have described is not JChem dependent, unless you specify this select for the JChem API at some point ... do you ?


You should also make sure that cd_id field in Lab_info table is indexed.


(The cd_id in the JChem structure table is always indexed, you do not have to worry about that.)





I do not really understand the rest of your question:

Quote:

Structure searches complete in a normal time frame.

seem to contradict this:

Quote:

we only get search results after a timeout

Could you describe the problem in more detail, possibly with code snippets ?





Best regards,





Szilard

Thanks, Szilard. By saying "structure seaches proceed in a normal time frame", I just mean that a structure search, which bypasses or doesn't select from the other tables proceeds normally. We seem to be held up by the checks on whether the mysql search is finished. This is from this point in "query.jsp":





if(searcher.isRunning()) {


visit = ((Integer)session.getAttribute(


sessionVarPrefix+


"search.visit")).intValue()+1;


session.setAttribute(sessionVarPrefix+


"search.visit", new Integer(visit));


%>


....


....





For data field searches from "query.jsp" that select from multiple tables, the visits increment up to about 26 times and then the (correct) search result is returned. I think the 26 figure is not significant but just reflects the timeout and the frequency of checking ... the example sql command submitted through the mysql command line client proceeds in fractions of seconds - so the actual mysql search is not the problem ...it seems that the above checking condition is apparently not satisfied when we select from multiple tables? Any additional ideas or comments? Thanks again, Manton

Manton,





Please tell me where do you specify the mentioned select statement ?


Do you specify it for JChemSearch.setFilterQuery() ?





Szilard

Yes, Szilard. The sql statement format is set early in the script, we replaced the original "stringBuffer sql" statement with our own:





StringBuffer sql = new StringBuffer("select distinct Lab_info.cd_id from "


+ structureTableName + ", Lab_info where compound.cd_id=Lab_info.cd_id and ");





and also:





sql.append("compound."+field+relation+value);





for the structure table items needed and





sql.append("Lab_info."+field+relation+value);





for the queried field items .... Manton

Manton,





Now I have a better picture of what part of the code might be affected.





Please provide me with the following information:





- the exact version of MySQL


- the exact version of the JDBC driver used (the name of the .jar file)


- the type of the operating system


- do you use structure cache or not (you can check it in the "About" dialog of our JSP example)





Best regards,





Szilard

Thanks, Szilard. Yes, here is the information:








MySQL: mysql Ver 14.7 Distrib 5.0.2-alpha


JDBC: mysql-connector-java-3.0.14-production-bin.jar


OS: Linux 2.4.21-215-athlon


Structure caching: useStructureCache is set to "false"





- Manton

Sorry, Szilzard, I have a correction. The MySQL server version is:





5.0.7-beta-standard





Thanks, Manton

Dear Manton,





Thank you for the information, we will investigate the problem soon.





Meanwhile could you test your configuration with the structure cache turned on (recommended), and let me know of the results ?


(I have a hunch that this will help)





Best regards,





Szilard

Thanks, Szilzard. I will try with caching turned off, but we were told by the support staff there at ChemAxon that if we wanted to use:





JChemSearch.setStringToAppend()





we had to turn off caching ... is that not true anymore?





- Manton

Manton,





setStringToAppend() has no effect indeed in cached mode, and this method will be deprecated from JChem 3.1.





You can already use setFilterQuery() to specify a WHERE clause.


From version 3.1 if setFilterQuery() contains an ORDER BY clause this order will also be preserved.





What kind of clause do you currently specify for setStringToAppend() ?





Szilard

Yes, Szilzard, activating caching does solve the one problem ... but then I can't use searcher.setStringToAppend() ... I use it to set both an ORDER BY clause and a WHERE clause which needs to be set dynamically (i.e., it is used or not based on a conditional result) ... maybe I can rewrite that section so that I don't need the searcher.setStringToAppend() and put it in setFilterQuery(), but am I understanding you correctly to say that for an ORDER BY clause, I would have to wait for JChem 3.1 ... ? Thanks - Manton

Hi,





Yes , you have to wait for JChem 3.1 to use the "ORDER BY" clause in setFilterQuery().





You may also download a test version where this already works.


Test versions can be downloaded from here:





http://www.chemaxon.com/download.php?d=/data/download/jchem/test





Please note that these were built from code under development, thus may contain several bugs and only suitable for testing / development.





Best regards,





Szilard