Error occured while inserting row: Incorrect string value...

I can not import a tetrazole, indole and an alfa-beta unsaturated acid in a mySQL database.

It works fine in my localdb  but not in the server database.

It seems as the smiles string is not correct.

"Incorrect string value: '[nH]1...'for column ' cd_smiles' at row 1"

This is the example with the tetrazole. I get the same error if I draw the hydrogen or leave it

org.springframework.jdbc.UncategorizedSQLException: Incorrect string value: '[nH]1)...' for column 'cd_smiles' at row 1; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1366]; Incorrect string value: '[nH]1)...' for column 'cd_smiles' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '[nH]1)...' for column 'cd_smiles' at row 1
 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
 at com.im.df.impl.db.dao.jchem.AbstractJChemDao$UpdateHandlerImpl.insert(AbstractJChemDao.java:320)
 at com.im.df.impl.db.dao.DFDaoSupport.insert(DFDaoSupport.java:140)
 at com.im.df.impl.db.dao.DFDaoWrapper.insert(DFDaoWrapper.java:83)
 at com.im.df.impl.db.DBEntityDataProviderImpl.doInsertRow(DBEntityDataProviderImpl.java:401)
 at com.im.df.impl.db.DBEntityDataProviderImpl.access$100(DBEntityDataProviderImpl.java:63)
 at com.im.df.impl.db.DBEntityDataProviderImpl$2.doInTransaction(DBEntityDataProviderImpl.java:330)
 at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
 at com.im.df.impl.db.DBEntityDataProviderImpl.insertOrFindRow(DBEntityDataProviderImpl.java:325)
 at com.im.df.impl.db.InsertionHandler.doInsertData(InsertionHandler.java:301)
 at com.im.df.impl.db.InsertionHandler.doInTransaction(InsertionHandler.java:261)
 at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
 at com.im.df.impl.db.DBSchemaDataProviderImpl.insert(DBSchemaDataProviderImpl.java:218)
 at com.im.ijc.core.api.actions.InsertRowAction$3.phase1InRequestProcessor(InsertRowAction.java:408)
 at com.im.commons.progress.BackgroundRunner.processTheTask(BackgroundRunner.java:128)
 at com.im.commons.progress.BackgroundRunner$1.run(BackgroundRunner.java:91)
 at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:1418)
 at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:1957)
Caused by: java.sql.SQLException: Incorrect string value: '[nH]1)...' for column 'cd_smiles' at row 1
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
 at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
 at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
 at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2113)
 at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2409)
 at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2327)
 at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2312)
 at chemaxon.jchem.db.UpdateHandler.execute(UpdateHandler.java:2378)
 at com.im.df.impl.db.dao.jchem.AbstractJChemDao$UpdateHandlerImpl.insert(AbstractJChemDao.java:310)
 ... 16 more

I was able to import tetrazole with this smiles 'c1nnn[nH]1'. I had it in a text file - 1 row 1 molecule. Are you imported it from a text file or another way? I suppose that smiles are correct if it works on local DB. Other conflict might be in characterset DB settings and DB collation settings. If it is possible try to setup new DB schema with callation and characterset set to latin1. Does it help?

Other information which may help us to solve it are: version of IJC, version of MySQL, environment - Linux/Win/Mac etc... Can you please tell as these and maybe send full IJC log file ( http://www.chemaxon.com/instantjchem/ijc_latest/docs/admin/debug_info.html ).

Thanks,
Filip 

Hi Filip

I had used the utf8.

After the change it works as it should.

Thanks for your help.

Just in case... There are some additional configuration steps to follow when you need to use unicode in MySQL. Please refer to http://www.chemaxon.com/instantjchem/ijc_latest/docs/admin/supported_dbs.html#unicode for a brief info how to enable UTF support to be working correctly.

You are very welcome.