Deleted rows throw error Jchem base/MySQL

 

JChem Version: 5.5.1

Database: MySQL 5.4.1

 

Hi

I have a process that loads data into a database using jcman on the commandline. A lot of the compounds I try to load will already be present in the database. When I try to load these I get errors such as:-

Error in molecule 1
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'EM24807' for key 'PRIMARY'
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:532)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
        at com.mysql.jdbc.Util.getInstance(Util.java:381)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2077)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2362)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2280)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2265)
        at chemaxon.jchem.db.UpdateHandler.execute(UpdateHandler.java:2354)
        at chemaxon.jchem.db.ParallelStructTableUpdater.importFile(ParallelStructTableUpdater.java:400)
        at chemaxon.jchem.db.FileToSQLHandler.importFile(FileToSQLHandler.java:129)
        at chemaxon.jchem.db.Importer.importMols(Importer.java:516)
        at chemaxon.jchem.Command.importFromFile(Command.java:1246)
        at chemaxon.jchem.Command.run(Command.java:700)
        at chemaxon.jchem.Command.main(Command.java:237)

So I delete the row for id EM24807. I can do a select from the table where id = 'EM24807' and it returns 0 rows (good the data is gone).

Now I try the import again and I get exactly the same error message (despite that I deleted the row).

 

Why does this happen? & how can I configure to stop these error messages?

 

Many thanks

 

Ceara

 

 

 

 

Hi Ceara,

 

If I understand correctly, there is a unique constraint on the id field, which was added as a user defined field to the JChem table?

 

Could you tell us a little more information about the environment how jcman is called:

Was the delete and select statement executed in a different MySQL session (than jcman)?

Was this session committed?

 

In the input file for jcman - is it possible to have multiple records with this id?

 

Thanks,

 

Szabolcs

Hi,

For JChem tables the primary key should be the cd_id column, which is a numeric value and usually auto-incremented.

Did you modify the SQL at table creation regarding this ?

So I delete the row for id EM24807. I can do a select from the table where id = 'EM24807' and it returns 0 rows (good the data is gone).


Now I try the import again and I get exactly the same error message (despite that I deleted the row).

Did you delete it via JChemManager ?

Please note that deleting rows (and all DML operations except modifying custom data fields) should only be performed by our API or applications.

Some possible explanations:

- The import data contains the value more than once, so conflict occurs even after deleting it before

- You have deleted the row, but did not commit, so other connections do not see the change.

It could be useful to paste the output of

jcman t table_name

Or even better to send the table creation statement from MySQL Workbench: right-click on table name -> send to SQL editor ->Create Statement

Please also let us know into which column you are directing the imported ID value.

 

Best regards,

 

Szilard 

 

 

Answers to questions:-

Did you modify the SQL at table creation regarding this ?

I added an index (unique I think) to the sobax_id column (as I will need to search on this column), but when I got this error I dropped the index and tried again (but still got the violation).

 

Did you delete it via JChemManager ?

First time no, I just ran a delete statement (I do all my work commandline, I'm not sure the version I'm using allows you to delete via jcman). When that didn't work of course I then went and did the delete via JChemManager desktop app, the behaviour was the same, I still got the same error message.

 

- The import data contains the value more than once, so conflict occurs even after deleting it before

This is quite possible when I run the full process with all the data, but for this testing I'm just using a small file with 10 rows, all of which are unique. If I run the import through twice I don't get errors, I get the 'Structure already exists' message which is fine.

- You have deleted the row, but did not commit, so other connections do not see the change.

The database has autocommit set to on.

 

Here are the table details (sobax_id is the id column used):

 

/*Column Information For - sobax.test_table*/
---------------------------------------------

Field                Type          Collation          Null    Key     Default  Extra           Privileges                       Comment
-------------------  ------------  -----------------  ------  ------  -------  --------------  -------------------------------  -------
cd_id                int(11)       (NULL)             NO      PRI     (NULL)   auto_increment  select,insert,update,references         
cd_structure         mediumblob    (NULL)             NO              (NULL)                   select,insert,update,references         
cd_smiles            text          latin1_swedish_ci  YES             (NULL)                   select,insert,update,references         
cd_formula           varchar(100)  latin1_swedish_ci  YES             (NULL)                   select,insert,update,references         
cd_sortable_formula  varchar(255)  latin1_swedish_ci  YES     MUL     (NULL)                   select,insert,update,references         
cd_molweight         double        (NULL)             YES             (NULL)                   select,insert,update,references         
cd_hash              int(11)       (NULL)             NO      MUL     (NULL)                   select,insert,update,references         
cd_flags             varchar(20)   latin1_swedish_ci  YES             (NULL)                   select,insert,update,references         
cd_timestamp         datetime      (NULL)             NO              (NULL)                   select,insert,update,references         
cd_pre_calculated    tinyint(1)    (NULL)             NO      MUL     0                        select,insert,update,references         
cd_fp1               int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp2               int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp3               int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp4               int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp5               int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp6               int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp7               int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp8               int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp9               int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp10              int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp11              int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp12              int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp13              int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp14              int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp15              int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp16              int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp17              int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp18              int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp19              int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp20              int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp21              int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp22              int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp23              int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp24              int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp25              int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp26              int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp27              int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp28              int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp29              int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp30              int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp31              int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
cd_fp32              int(11)       (NULL)             NO              (NULL)                   select,insert,update,references         
sobax_id             varchar(50)   latin1_swedish_ci  NO              (NULL)                   select,insert,update,references         
psmi                 mediumtext    latin1_swedish_ci  YES             (NULL)                   select,insert,update,references         
pism                 mediumtext    latin1_swedish_ci  YES             (NULL)                   select,insert,update,references         
pmw                  float         (NULL)             YES             (NULL)                   select,insert,update,references         
pmf                  mediumtext    latin1_swedish_ci  YES             (NULL)                   select,insert,update,references         
donor                bigint(20)    (NULL)             YES             (NULL)                   select,insert,update,references         
acceptor             bigint(20)    (NULL)             YES             (NULL)                   select,insert,update,references         
clogp                float         (NULL)             YES             (NULL)                   select,insert,update,references         
rot_bond             bigint(20)    (NULL)             YES             (NULL)                   select,insert,update,references         
heavy_atoms          bigint(20)    (NULL)             YES             (NULL)                   select,insert,update,references         
prefilter            mediumtext    latin1_swedish_ci  YES             (NULL)                   select,insert,update,references         
inchi_key            mediumtext    latin1_swedish_ci  YES             (NULL)                   select,insert,update,references         

/*Index Information For - sobax.test_table*/
--------------------------------------------

Table       Non_unique  Key_name       Seq_in_index  Column_name          Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment
----------  ----------  -------------  ------------  -------------------  ---------  -----------  --------  ------  ------  ----------  -------
test_table           0  PRIMARY                   1  cd_id                A             21172864    (NULL)  (NULL)          BTREE              
test_table           1  test_table_hx             1  cd_hash              A               (NULL)    (NULL)  (NULL)          BTREE              
test_table           1  test_table_fx             1  cd_sortable_formula  A               (NULL)    (NULL)  (NULL)  YES     BTREE              
test_table           1  test_table_px             1  cd_pre_calculated    A               (NULL)    (NULL)  (NULL)          BTREE              

/*DDL Information For - sobax.test_table*/
------------------------------------------

Table       Create Table                                                    
----------  ----------------------------------------------------------------
test_table  CREATE TABLE `test_table` (                                     
              `cd_id` int(11) NOT NULL AUTO_INCREMENT,                      
              `cd_structure` mediumblob NOT NULL,                           
              `cd_smiles` text,                                             
              `cd_formula` varchar(100) DEFAULT NULL,                       
              `cd_sortable_formula` varchar(255) DEFAULT NULL,              
              `cd_molweight` double DEFAULT NULL,                           
              `cd_hash` int(11) NOT NULL,                                   
              `cd_flags` varchar(20) DEFAULT NULL,                          
              `cd_timestamp` datetime NOT NULL,                             
              `cd_pre_calculated` tinyint(1) NOT NULL DEFAULT '0',          
              `cd_fp1` int(11) NOT NULL,                                    
              `cd_fp2` int(11) NOT NULL,                                    
              `cd_fp3` int(11) NOT NULL,                                    
              `cd_fp4` int(11) NOT NULL,                                    
              `cd_fp5` int(11) NOT NULL,                                    
              `cd_fp6` int(11) NOT NULL,                                    
              `cd_fp7` int(11) NOT NULL,                                    
              `cd_fp8` int(11) NOT NULL,                                    
              `cd_fp9` int(11) NOT NULL,                                    
              `cd_fp10` int(11) NOT NULL,                                   
              `cd_fp11` int(11) NOT NULL,                                   
              `cd_fp12` int(11) NOT NULL,                                   
              `cd_fp13` int(11) NOT NULL,                                   
              `cd_fp14` int(11) NOT NULL,                                   
              `cd_fp15` int(11) NOT NULL,                                   
              `cd_fp16` int(11) NOT NULL,                                   
              `cd_fp17` int(11) NOT NULL,                                   
              `cd_fp18` int(11) NOT NULL,                                   
              `cd_fp19` int(11) NOT NULL,                                   
              `cd_fp20` int(11) NOT NULL,                                   
              `cd_fp21` int(11) NOT NULL,                                   
              `cd_fp22` int(11) NOT NULL,                                   
              `cd_fp23` int(11) NOT NULL,                                   
              `cd_fp24` int(11) NOT NULL,                                   
              `cd_fp25` int(11) NOT NULL,                                   
              `cd_fp26` int(11) NOT NULL,                                   
              `cd_fp27` int(11) NOT NULL,                                   
              `cd_fp28` int(11) NOT NULL,                                   
              `cd_fp29` int(11) NOT NULL,                                   
              `cd_fp30` int(11) NOT NULL,                                   
              `cd_fp31` int(11) NOT NULL,                                   
              `cd_fp32` int(11) NOT NULL,                                   
              `sobax_id` varchar(50) NOT NULL,                              
              `psmi` mediumtext,                                            
              `pism` mediumtext,                                            
              `pmw` float DEFAULT NULL,                                     
              `pmf` mediumtext,                                             
              `donor` bigint(20) DEFAULT NULL,                              
              `acceptor` bigint(20) DEFAULT NULL,                           
              `clogp` float DEFAULT NULL,                                   
              `rot_bond` bigint(20) DEFAULT NULL,                           
              `heavy_atoms` bigint(20) DEFAULT NULL,                        
              `prefilter` mediumtext,                                       
              `inchi_key` mediumtext,                                       
              PRIMARY KEY (`cd_id`),                                        
              KEY `test_table_hx` (`cd_hash`),                              
              KEY `test_table_fx` (`cd_sortable_formula`),                  
              KEY `test_table_px` (`cd_pre_calculated`)                     
            ) ENGINE=MyISAM AUTO_INCREMENT=21172892 DEFAULT CHARSET=latin1  

 

Hi,

Thank you for the detailed information.

This is quite strange, the error seems to be complaining about the PRIMARY key, with a value that is meant to be used for the custom ID.

Since there seems to be no obvious answer at this point, it would be best to try to reproduce the exact same scenario.

- Could you paste here the exact command ?

- Could you possibly provide us the sample file as well ? If confidential, you can send it to our support e-mail address.

- Could you confirm if you are using the JDBC driver shipped with JChem ? (no other JDBC driver was added to the CLASSPATH) 

Best regards,

Szilard