uppercase and lowercase - InnoDB and MyISAM?

I have a subsidiary question to managing tables with JChem.


I am using MySQL - 5.0.18-standard, and it looks like I can create manually by sql statements both the jchemproperties and a structure table.


However, when doing so, jcman fails in importing structures.


I noticed that when creating the log table (_UL, which I didn't do manually) from within jcman, its format is MyISAM whereas the others were created as InnoDB (the default in my version of MySQL).


Furthermore, starting from scratch in jcman, if the table is not in uppercase letters at creation, it subsequently fails to do anything with it.


Is there a rule we should follow?


Thank you,


Nicolas

Quote:

I have a subsidiary question to managing tables with JChem. I am using MySQL - 5.0.18-standard, and it looks like I can create manually by sql statements both the jchemproperties and a structure table. However, when doing so, jcman fails in importing structures.

We do not recommend creating a property table or a structure table manually.





With DatabaseProperties you can create a property table :


http://www.chemaxon.com/jchem/doc/api/chemaxon/jchem/db/DatabaseProperties.html#createPropertyTable(chemaxon.util.ConnectionHandler)





You can use UpdateHandler from the API to create a structure table:


http://www.chemaxon.com/jchem/doc/api/chemaxon/jchem/db/UpdateHandler.html#createStructureTable(chemaxon.util.ConnectionHandler,%20java.lang.String,%20int,%20int,%20int,%20java.lang.String,%20java.lang.String,%20boolean)

Quote:

I noticed that when creating the log table (_UL, which I didn't do manually) from within jcman, its format is MyISAM whereas the others were created as InnoDB (the default in my version of MySQL).

We do not specify the format at table creation.


I think all the tables created by jcman should be in the same format.


MySQL may have a seaprate default setting for JDBC connections, but it's unlikely.

Quote:

Furthermore, starting from scratch in jcman, if the table is not in uppercase letters at creation, it subsequently fails to do anything with it.

Were the property table and the structure table created with jcman ?


If they were, please let me know the JChem version you are using, and the exact version of the JDBC driver.





Kind regards,





Szilard

Thank you for the reply,





First point - manual creation


I realize it is better to create the tables from the JChem API, but since I am way more fluent in SQL statements than in java (yet), I tried that first (attached is an example sql script I used - with an extra ref_product column I use from an sdf file). All are in InnoDB format upon creation.


So far, it works quite well and I can import sdf files after this with jcman, either on the command line or the graphic version. It never complains and the query interface works quite well afterwards.





The next step is obviously to use DatabaseProperties and UpdateHandler...





In the meantime, I will look at the way MySQL use the JDBC to create tables


I use MySQL - 5.0.18-standard from a rpm on RedHat Enterprise Linux WorkStation 3.0, and mysql-connector-java-3.1.11





Second point - uppercase and lowercase


It may be related to the connector, since even when I create everything the right way (i.e. within jcman), it never recognizes a table created in lowercase letters: it lets me create it, but cannot use it afterwards for import.


I am using the latest version of JChem, 3.1.5...





Sincerely,


Nicolas

Quote:

Second point - uppercase and lowercase It may be related to the connector, since even when I create everything the right way (i.e. within jcman), it never recognizes a table created in lowercase letters: it lets me create it, but cannot use it afterwards for import. I am using the latest version of JChem, 3.1.5...

So far we could not reproduce the problem, although the MySQL and driver version was slightly different (5.0.19 and 3.1.12 I believe), and the OS was Windows.





Could you describe in detail how do you create the table (command line / GUI) ?


Please also provide an example case when JChem refuses to use this table (with complete stack trace if there is any).





We will try then with a configuration that matches yours more accurately.





Thank you,





Szilard

If necessary, I will write exactly the procedure I did with jcman, starting from scratch, but I did exactly as written in the administration guide...


In the meantime, I installed the newer Connector/J 3.1.12, and the creation works fine with a lowercase letter table, but the table is in MyISAM format.


When I specify TYPE=INNODB at the end of the sql script generated by jcman, it changes to InnoDB OK but the table_UL is in MyISAM... I can alter it in MySQL by "ALTER TABLE `test_UL` ENGINE = innodb", I hope it is OK...


Thank you for the support,


Nicolas


P.S. I need the InnoDB engine since I want to relate the structure table with other data in other tables with foreign keys...

We are currently testing version 3.1.6 of JChem because we have had many users who couldn't make our application (chemicalinventory) work after downloading the newest JChem.





Dann has played around with it and it works for him on windows.





he has then shipped a db script to me that I have used to create my tables and I try to run the application using JChem 3.1.6.





Catalina.out tells me:





java.sql.SQLException: Table 'chemicals2.STRUCTURES' doesn't exist


at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2926)


at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)


at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)


at com.mysql.jdbc.Connection.execSQL(Connection.java:2978)


at com.mysql.jdbc.Connection.execSQL(Connection.java:2902)


at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:933)


at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1027)


at chemaxon.jchem.db.JChemSearch.getScreenedInBatch(JChemSearch.java:3132)


at chemaxon.jchem.db.JChemSearch.getScreened(JChemSearch.java:2913)


at chemaxon.jchem.db.JChemSearch.search1(JChemSearch.java:2084)


at chemaxon.jchem.db.JChemSearch.search(JChemSearch.java:1911)


at chemaxon.jchem.db.JChemSearch.setRunning(JChemSearch.java:1798)


at chemaxon.jchem.db.JChemSearch.run(JChemSearch.java:1818)





My structures table is in lowercase (structures) and we haven't written it in uppercase anywhere in our code so the output is correct - STRUCTURES doesn't exist.





I run Linux (Ubuntu 5.10) and use Mysql-connector version 3.1.11





After download of version 3.1.12 of the mysql-connector the catalina.out says:





java.sql.SQLException: Table 'chemicals2.structures_UL' doesn't exist


at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2928)


at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)


at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)


at com.mysql.jdbc.Connection.execSQL(Connection.java:2988)


at com.mysql.jdbc.Connection.execSQL(Connection.java:2917)


at com.mysql.jdbc.Statement.execute(Statement.java:529)


at chemaxon.jchem.db.UpdateHandler.storeUpdate(UpdateHandler.java:1805)


at chemaxon.jchem.db.UpdateHandler.saveUpdateAndGetUpdateCounter(UpdateHandler.java:1782)


at chemaxon.jchem.db.UpdateHandler.close(UpdateHandler.java:1752)


at chemicalinventory.beans.ChemicalRegBean.checkChemicalRegistration(ChemicalRegBean.java:302)


at chemicalinventory.beans.ChemicalRegBean.regOK(ChemicalRegBean.java:228)





So now it apparently accepts the structures table???? but complains about not being able to find "structures_UL"!!





And I don't have that in my database as the name of the table is "structures_ul" all in lowercase.





This is very strange! And it works on windows - but as windows doesn't understand upper- lowercase that might be the reason.





Have someone forgotten about the real operating systems when they developed?





claus

Hi,





Regarding the first error ("chemicals2.STRUCTURES"):





This a JDBC driver bug of MySQL-connector version 3.1.11.





It seems the driver erroneously tells that all identifiers are stored in uppercase (like for Oracle), this seem to cause the trouble.





This bug is JDBC driver dependent, it should not work better with older JChem versions either.


Please do not use this particular driver under case sensitive operating systems.





About the OS mystery: MySQL stores databases and tables as file in the operating system, so there case sensitivity is strongly connected with the file system. This is the reason you see no problems like this on case insensitive file systems (e.g. under Windows).








Regarding the second error:





This table ("structures_UL") should exist indeed.


Since JChem 3.1.1 we use log tables to follow changes in the structure table (required for efficient cache update).


You have probably forgot to do one of the following:





- run JChemManager GUI, which will automatically offer you to create these log tables


OR


- in a text-only console you can call jcman u to perform the same upgrade tasks. (this command-line version is available for JChem 3.1.4)





In either case if you use multiple property tables you should repeat the process for each of them.





Please let me know if this helps.





Best regards,





Szilard

Hi Szilard





Thank you for the update.





I haven't created the tables - dann has on a windows machine. I have run a script he created as a dump/export.





But renaming the structures_ul to structures_UL fixed it.





thank you





claus