Instan JChem install with MySQL 5.5 keeps asking to Upgrade

We have installed MySQL 5.5 and imported our data in to the database

Single usermode works as it should but every time we try to gointo Normal mode it says our DB needs updating. 

Log in single user ran update says it's fineshed still revice the error in the attached file

installed IJC on the server

tried to run it from the server as a normal DB user and as root from local host and the server name made no diffrence.

made sure there were no users that have the flie opend.

 

Jchem Version Info:

Instant JChem Version: 5.6.0.2 (build: 201110041526-149372)
JChem Version: 5.6.0.2
JChem Table Version: 5060000
Marvin Version: 5.6.0.2 (build date: 2011-09-30)
Java: 1.6.0_26; Java HotSpot(TM) Client VM 20.1-b02
System: Windows Server 2008 version 6.0 running on x86; Cp1252; en_US (instantjchem)
NetBeans build: 110214-aec102d89c78
Java Web Start: No

It seems to me that you are using database with different character set(and collation) than latin1(e.g. utf8)? I think it is deffinitely your case if you see the screenshot below when you are logging as admin in. If the schema owner(single-user mode) is logged in the upgrade proceed, but not for a normal user (multi-user mode) which leads to the dialog shown above.

For more information how to setup unicode support, please refer to http://www.chemaxon.com/instantjchem/ijc_latest/docs/admin/supported_dbs.html

Let me know if it helps. I can help you with settings if mentioned document will not be sufficient.

Filip

I have done what the http://www.chemaxon.com/instantjchem/ijc_latest/docs/admin/supported_dbs.html says to do even from scratch, it still shows that i need to update the tables, I have done this and left them going how long does it take to update a 1 MB database?

For such a small database it should be almost instantaneous.

Well this is a bit tricky:
At first your database schema should be set e.g. by this commans:

ALTER SCHEMA `IJC_schema`  DEFAULT CHARACTER SET utf8  DEFAULT COLLATE utf8_bin ;

After that the table can be set as follow:

ALTER TABLE `IJC_schema`.`name_of_data_tree_ul` COLLATE = utf8_bin ;

Where IJC_schema is the name of your database schema usually = name of IJC schema and name_of_data_tree is the name of your data tree. _ul is the suffix of affected table, which requieres update. 

Use the statement useUnicode=true&characterEncoding=utf8 as described on Supported databases page. For the first time update is needed and then it should work correctly. If not it is probably a bug, but collate utf8_bin must be used in DB schema and in DB table as well.

This works for me.

Filip

I have done this, I manually checked all Tabled and Schema settings and all are utf-8.

no matter if i use the unicode string or not, it is not updating. keeps giving me the update screen.

when i log in and see the select normal or single mode, it says the DB is upto date but still wont let me login normally, keep getting the same error from first post.

Everytime i enter single user mode i get the screen attached.

Hi, the upgrade is because JChem thinks that the update log tables are not correct and need to be updated. These tables of the ones with the same names as the JChem tables but with _UL appended to then end.

Its not clear why this is happening. Could you please send us:

1. the contents of the JChemProperties table

2. a description of all the columns present in any *_UL tables.

We might be able to work out what is wrong with this info.

Tim

here is the jchemproperties table:

cache.registration_table, jchemproperties_CR,
cache.validity_timestamp, 2011-10-13 09:40:21.006,
option.commitInterval, 50,
option.structureCompressionDisabled, true,
propertytable.identifier, PT_ID_83de08a8cb674aec83696e5a1feca386,
table.notebooks.JChemVersion, 5.6.0.2,
table.notebooks.absoluteStereo, true,
table.notebooks.creationTime, 2011-10-13 09:36:39.172,
table.notebooks.ctVersion, 5060000,
table.notebooks.duplicateFiltering, false,
table.notebooks.fingerprint.numberOfBits, 512,
table.notebooks.fingerprint.numberOfEdges, 6,
table.notebooks.fingerprint.numberOfOnes, 2,
table.notebooks.fingerprint.numberOfStrucFPCols, 0,
table.notebooks.mdVersion, 5060000,
table.notebooks.switchOffAllProtections, false,
table.notebooks.tableType, 0,
table.notebooks.tautomerDuplicateFiltering, false,
table.notebooks.validityTimestamp, 2011-10-13 09:36:39.215,
table.notebooks.version, 5060000,
table.products.JChemVersion, 5.6.0.2,
table.products.absoluteStereo, true,
table.products.creationTime, 2011-10-13 09:25:00.403,
table.products.ctVersion, 5060000,
table.products.duplicateFiltering, false,
table.products.fingerprint.numberOfBits, 512,
table.products.fingerprint.numberOfEdges, 6,
table.products.fingerprint.numberOfOnes, 2,
table.products.fingerprint.numberOfStrucFPCols, 0,
table.products.mdVersion, 5060000,
table.products.switchOffAllProtections, false,
table.products.tableType, 0,
table.products.tautomerDuplicateFiltering, false,
table.products.validityTimestamp, 2011-10-13 09:25:00.439,
table.products.version, 5060000,

 

2: the only Colums in all _ul tables are:    update_ip     update_info     cache_id      all rows are empty

  

 

 

One more question - when you connect as single user mode and as multi user mode you are connecting as the same database user and the same IJC user? e.g. the only difference is the single/multi user mode option?

That is correct, only one user right now. wont add more untill multi user mode works.

Hi,

Could you please check whether your JCHEMPROPERTIES_CR table contains the following fields: CACHE_ID, REGISTRATION_TIME, IS_PROTECTED ?

Regards,
Tamás

They are there and here are their contents: 

cache_id                                                                 registration_time                      is_protected
'a9e44dfbf0304cfb80eb89d90a987096',      '2011-10-13 09:40:21.006',          '0'

And what is the type of the REGISTRATION_TIME field?

'registration_time',   'varchar(30)',    'NO', '', NULL, ''

The issue was handled offline.

It seems to be a rare MySQL JDBC driver related problem.

Replacing the  5.1.13 JDBC driver (shipped with Instant JChem) with version 5.1.10 (shipped with JChem) solved the problem.

The latest driver (5.1.18) seems to have the same problem.

Probably ResultSetMetaData.getColumnDisplaySize() reports incorrect size, might be related to MySQL bug #6399. This needs further investigation.

We will change the driver or implement some other workaround in the future. 

 

Until then here is the procedure to manually replace the driver if needed:

1. Stop IJC

2. Locate the folder where the JDBC drivers are stored in IJC. Example:

"c:\Program Files\ChemAxon\InstantJChem\instantjchem\modules" 

3. Rename/remove mysql-connector-java-bin.jar

4. Copy mysql-connector-java-5.1.10-bin.jar to this folder (attached).

5. Rename the new jar to mysql-connector-java-bin.jar

6. Restart IJC