Issues with OntSynHandler

[giovannirco]

We are running two different versions of OntSynHandler and Explorer in three environments and all of them failed around the block 863790.
Dev is running this commit which is tagged as v2.1.1
Stage and prod are running this commit
I couldn't pinpoint the root cause, all envs have different resources and yet it got the same error

I see some errors on stdout of OntSynHandler mentioning a duplicated entry:

[ ERROR] [2020-06-30 19:09:11] com.github.ontio.thread.BlockHandlerThread [119] - Exception occured，Synchronization thread can't work,error ...
org.springframework.dao.DuplicateKeyException:
### Error updating database.  Cause: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '2032fbaeb983066cd85fad90e74627cbef56e95f5dd142aae259053a52375d03' for key 'idx_tx_hash_index'
### The error may exist in class path resource [mapper/TxDetailMapper.xml]
### The error may involve com.github.ontio.mapper.TxDetailMapper.batchInsert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO tbl_tx_detail (  `tx_hash`,  `tx_type`,  `tx_time`,  `block_height`,  `amount`,  `fee`,  `asset_name`,  `from_address`,  `to_address`,  `desc
ription`,  `block_index`,  `tx_index`,  `confirm_flag`,  `event_type`,  `contract_hash`,  `payer`,  `called_contract_hash`     )     VALUES            (       ?,
      ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?     )      ,      (
       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,       ?,...........

This is a very long SQL query

### Cause: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '2032fbaeb983066cd85fad90e74627cbef56e95f5dd142aae259053a52375d03' for key 'idx_tx_hash_index'
; Duplicate entry '2032fbaeb983066cd85fad90e74627cbef56e95f5dd142aae259053a52375d03' for key 'idx_tx_hash_index'; nested exception is java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '2032fbaeb983066cd85fad90e74627cbef56e95f5dd142aae259053a52375d03' for key 'idx_tx_hash_index'
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:242)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
        at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
        at com.sun.proxy.$Proxy92.insert(Unknown Source)
        at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278)
        at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
        at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:58)
        at com.sun.proxy.$Proxy98.batchInsert(Unknown Source)
        at com.github.ontio.service.CommonService.batchInsertDb(CommonService.java:104)
        at com.github.ontio.service.CommonService$$FastClassBySpringCGLIB$$b8fbd360.invoke(<generated>)
        at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
        at com.github.ontio.service.CommonService$$EnhancerBySpringCGLIB$$e2058e33.batchInsertDb(<generated>)
        at com.github.ontio.thread.BlockHandlerThread.batchHandleBlockAndInsertDb(BlockHandlerThread.java:155)
        at com.github.ontio.thread.BlockHandlerThread.run(BlockHandlerThread.java:110)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '2032fbaeb983066cd85fad90e74627cbef56e95f5dd142aae259053a52375d03' for key 'idx_tx_hash_index'
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:970)
        at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:387)
        at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3409)
        at com.alibaba.druid.wall.WallFilter.preparedStatement_execute(WallFilter.java:619)
        at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
        at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
        at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
        at com.alibaba.druid.filter.FilterAdapter.preparedStatement_execute(FilterAdapter.java:1080)
        at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
        at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
        at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498)
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
        at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
        at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
        at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
        ... 22 common frames omitted

Can you please help?

[giovannirco]

can anyone help? We currently have all 3 environments down and we are relying on public infra to show transactions

[leej1012]

Could you please confirm whether record of tx_hash='2032fbaeb983066cd85fad90e74627cbef56e95f5dd142aae259053a52375d03'
already exists in tbl_tx_detail?

[vbonini]

Not it is not.

mysql> select * from tbl_tx_detail where tx_hash="2032fbaeb983066cd85fad90e74627cbef56e95f5dd142aae259053a52375d03" limit 10;
Empty set (0.05 sec)

During our troubleshooting we restored one of the DBs from midnight snapshot and resynced several hours of blocks. It failed on a different tx. This tx does not appear in the table either.

mysql> select * from tbl_tx_detail where tx_hash="8876be5e72a46af92c9daa3ae82f662634ebce3ba97233a9cb0f271cb6a2a835" limit 10;
Empty set (0.06 sec)

[giovannirco]

I have restored the database from a snapshot taken on June 17 and started ontsynhandler again. It synced until block 8638200 where it failed with the same error as the attempt to restore from a snapshot taken in the day before which is what @vbonini mentioned above.

[ INFO ] [2020-07-02 07:24:42] com.github.ontio.service.BlockHandleService [66] - handleOneBlock run-------blockHeight:8638199,txCount:1
[ INFO ] [2020-07-02 07:24:42] com.github.ontio.thread.TxHandlerThread [96] - TxHandlerThread--203 run--------blockHeight:8638199,txHash:cf5b87b84b922cfe544f8d19bea56e258227ffc859f969e0a8954f518f8e1e13
[ INFO ] [2020-07-02 07:24:42] com.github.ontio.thread.TxHandlerThread [124] - ####txType:210, txHash:cf5b87b84b922cfe544f8d19bea56e258227ffc859f969e0a8954f518f8e1e13, calledContractHash:
[ INFO ] [2020-07-02 07:24:42] com.github.ontio.thread.TxHandlerThread [128] - eventLog:{"GasConsumed":20000000,"Notify":[{"States":["Register","did:ont:THWgMDSVmZRpkuFpZ4ev7LbGsjJ6rJUmkq"],"ContractAddress":"0300000000000000000000000000000000000000"},{"States":["Attribute","add","did:ont:THWgMDSVmZRpkuFpZ4ev7LbGsjJ6rJUmkq",["646174614d65746148617368"]],"ContractAddress":"0300000000000000000000000000000000000000"},{"States":["transfer","Aejfo7ZX5PVpenRj23yChnyH64nf8T1zbu","AFmseVrdL9f9oyCzZefL9tG6UbviEH9ugK",20000000],"ContractAddress":"0200000000000000000000000000000000000000"}],"TxHash":"cf5b87b84b922cfe544f8d19bea56e258227ffc859f969e0a8954f518f8e1e13","State":1}
[ INFO ] [2020-07-02 07:24:42] com.github.ontio.thread.TxHandlerThread [539] - ####Register OntId:did:ont:THWgMDSVmZRpkuFpZ4ev7LbGsjJ6rJUmkq
[ INFO ] [2020-07-02 07:24:42] com.github.ontio.thread.TxHandlerThread [560] - ####Attribute op:add####
[ INFO ] [2020-07-02 07:24:42] com.github.ontio.thread.TxHandlerThread [569] - ####attrName:646174614d65746148617368####
[ INFO ] [2020-07-02 07:24:42] com.github.ontio.thread.TxHandlerThread [445] - ####fromAddress:Aejfo7ZX5PVpenRj23yChnyH64nf8T1zbu,toAddress:AFmseVrdL9f9oyCzZefL9tG6UbviEH9ugK,amount:20000000####
[ INFO ] [2020-07-02 07:24:42] com.github.ontio.thread.TxHandlerThread [98] - TxHandlerThread--203 end-------blockHeight:8638199,txHash:cf5b87b84b922cfe544f8d19bea56e258227ffc859f969e0a8954f518f8e1e13
[ INFO ] [2020-07-02 07:24:42] com.github.ontio.service.BlockHandleService [85] - handleOneBlock end-------height:8638199,txCount:1
[ INFO ] [2020-07-02 07:24:42] com.github.ontio.service.BlockHandleService [66] - handleOneBlock run-------blockHeight:8638200,txCount:1
[ INFO ] [2020-07-02 07:24:42] com.github.ontio.thread.TxHandlerThread [96] - TxHandlerThread--202 run--------blockHeight:8638200,txHash:b8cb06d8e74207a9e77bebbac0ff13353604bcf21c4a4638696cfbce206aea88
[ INFO ] [2020-07-02 07:24:42] com.github.ontio.thread.TxHandlerThread [124] - ####txType:210, txHash:b8cb06d8e74207a9e77bebbac0ff13353604bcf21c4a4638696cfbce206aea88, calledContractHash:
[ INFO ] [2020-07-02 07:24:42] com.github.ontio.thread.TxHandlerThread [128] - eventLog:{"GasConsumed":20000000,"Notify":[{"States":["Register","did:ont:TJSu2MiFGgb2cqUp42WzzZPde57yCYsYpC"],"ContractAddress":"0300000000000000000000000000000000000000"},{"States":["Attribute","add","did:ont:TJSu2MiFGgb2cqUp42WzzZPde57yCYsYpC",["646174614d65746148617368"]],"ContractAddress":"0300000000000000000000000000000000000000"},{"States":["transfer","Aejfo7ZX5PVpenRj23yChnyH64nf8T1zbu","AFmseVrdL9f9oyCzZefL9tG6UbviEH9ugK",20000000],"ContractAddress":"0200000000000000000000000000000000000000"}],"TxHash":"b8cb06d8e74207a9e77bebbac0ff13353604bcf21c4a4638696cfbce206aea88","State":1}
[ INFO ] [2020-07-02 07:24:42] com.github.ontio.thread.TxHandlerThread [539] - ####Register OntId:did:ont:TJSu2MiFGgb2cqUp42WzzZPde57yCYsYpC
[ INFO ] [2020-07-02 07:24:42] com.github.ontio.thread.TxHandlerThread [560] - ####Attribute op:add####
[ INFO ] [2020-07-02 07:24:42] com.github.ontio.thread.TxHandlerThread [569] - ####attrName:646174614d65746148617368####
[ INFO ] [2020-07-02 07:24:42] com.github.ontio.thread.TxHandlerThread [445] - ####fromAddress:Aejfo7ZX5PVpenRj23yChnyH64nf8T1zbu,toAddress:AFmseVrdL9f9oyCzZefL9tG6UbviEH9ugK,amount:20000000####
[ INFO ] [2020-07-02 07:24:42] com.github.ontio.thread.TxHandlerThread [98] - TxHandlerThread--202 end-------blockHeight:8638200,txHash:b8cb06d8e74207a9e77bebbac0ff13353604bcf21c4a4638696cfbce206aea88
[ INFO ] [2020-07-02 07:24:42] com.github.ontio.service.BlockHandleService [85] - handleOneBlock end-------height:8638200,txCount:1
[ INFO ] [2020-07-02 07:24:42] com.github.ontio.thread.BlockHandlerThread [154] - batch handle 500 block from 8637701 use time:84651,txCount:670
[ ERROR] [2020-07-02 07:25:04] com.github.ontio.thread.BlockHandlerThread [119] - Exception occured，Synchronization thread can't work,error ...
org.springframework.dao.DuplicateKeyException:
### Error updating database.  Cause: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '8876be5e72a46af92c9daa3ae82f662634ebce3ba97233a9cb0f271cb6a2a835' for key 'PRIMARY'
### The error may exist in class path resource [mapper/OntidTxDetailMapper.xml]
### The error may involve com.github.ontio.mapper.OntidTxDetailMapper.batchInsert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO tbl_ontid_tx_detail (       `tx_hash`,       `tx_type`,       `ontid`,       `tx_time`,       `block_height`,       `description`,       `fee`     )     VALUES              (       ?,       ?,       ?,       ?,       ?,       ?,       ?       )      ,        (       ?,       ?,       ?,       ?,       ?,       ?,       ?       )

We are running this database on AWS using RDS MySQL Engine version 8.0.11 on a db.m5.large instance which have 2 vCPU and 8GB of ram

[leej1012]

1.About DuplicateKeyException of tbl_ontid_tx_detail , please modify unique key idx_tx_hash_ontid:
ALTER TABLE tbl_ontid_tx_detail DROP KEY idx_tx_hash_ontid;
ALTER TABLE tbl_ontid_tx_detail ADD UNIQUE idx_tx_hash_ontid (tx_hash,ontid,description);

Because it includes muilty ont d event of the same ontid in one transaction now.

2.About DuplicateKeyException of tbl_tx_detail , could you please confirm whether UNIQUE KEY idx_tx_hash_index (tx_hash,tx_index) is correct?

[giovannirco]

1.About DuplicateKeyException of tbl_ontid_tx_detail , please modify unique key idx_tx_hash_ontid:
ALTER TABLE tbl_ontid_tx_detail DROP KEY idx_tx_hash_ontid;
ALTER TABLE tbl_ontid_tx_detail ADD UNIQUE idx_tx_hash_ontid (tx_hash,ontid,description);

Because it includes muilty ont d event of the same ontid in one transaction now.

This error regarding tbl_ontid_tx_detail only appeared after restoring the DB from previous snapshot and I have only did this on one of the environments.
When dropping key idx_tx_hash_ontid I got an error, checking the table and there is no idx_tx_hash_ontid.
Second query "worked":

mysql> ALTER TABLE tbl_ontid_tx_detail DROP KEY idx_tx_hash_ontid;
ERROR 1091 (42000): Can't DROP 'idx_tx_hash_ontid'; check that column/key exists
mysql> ALTER TABLE tbl_ontid_tx_detail ADD UNIQUE idx_tx_hash_ontid (tx_hash,ontid,description);
Query OK, 0 rows affected (8.65 sec)
Records: 0  Duplicates: 0  Warnings: 0

After starting ontsynhandler I get a different error now:

[ ERROR] [2020-07-06 13:59:23] com.github.ontio.thread.BlockHandlerThread [119] - Exception occured，Synchronization thread can't work,error ...
org.springframework.dao.DuplicateKeyException:
### Error updating database.  Cause: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '8876be5e72a46af92c9daa3ae82f662634ebce3ba97233a9cb0f271cb6a2a835' for key 'PRIMARY'
### The error may exist in class path resource [mapper/OntidTxDetailMapper.xml]
### The error may involve com.github.ontio.mapper.OntidTxDetailMapper.batchInsert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO tbl_ontid_tx_detail (       `tx_hash`,       `tx_type`,       `ontid`,       `tx_time`,       `block_height`,       `description`,       `fee`     )     VALUES              (       ?,       ?,       ?,       ?,       ?,       ?,       ?       )...
(LONG SQL RESPONSE)
### Cause: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '8876be5e72a46af92c9daa3ae82f662634ebce3ba97233a9cb0f271cb6a2a835' for key 'PRIMARY'
; Duplicate entry '8876be5e72a46af92c9daa3ae82f662634ebce3ba97233a9cb0f271cb6a2a835' for key 'PRIMARY'; nested exception is java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '8876be5e72a46af92c9daa3ae82f662634ebce3ba97233a9cb0f271cb6a2a835' for key 'PRIMARY'
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:242)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
	at com.sun.proxy.$Proxy92.insert(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:58)
	at com.sun.proxy.$Proxy100.batchInsert(Unknown Source)
	at com.github.ontio.service.CommonService.batchInsertDb(CommonService.java:125)
	at com.github.ontio.service.CommonService$$FastClassBySpringCGLIB$$b8fbd360.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
	at com.github.ontio.service.CommonService$$EnhancerBySpringCGLIB$$31438f.batchInsertDb(<generated>)
	at com.github.ontio.thread.BlockHandlerThread.batchHandleBlockAndInsertDb(BlockHandlerThread.java:155)
	at com.github.ontio.thread.BlockHandlerThread.run(BlockHandlerThread.java:110)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '8876be5e72a46af92c9daa3ae82f662634ebce3ba97233a9cb0f271cb6a2a835' for key 'PRIMARY'
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:970)
	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:387)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3409)
	at com.alibaba.druid.wall.WallFilter.preparedStatement_execute(WallFilter.java:619)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
	at com.alibaba.druid.filter.FilterAdapter.preparedStatement_execute(FilterAdapter.java:1080)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
	... 22 common frames omitted
[ INFO ] [2020-07-06 14:04:04] com.github.ontio.schedule.InitOepInfoSchedule [49] - ####InitOepInfoSchedule.initOepInfo begin....                                         

2.About DuplicateKeyException of tbl_tx_detail , could you please confirm whether UNIQUE KEY idx_tx_hash_index (tx_hash,tx_index) is correct?

I'm gonna check other envs and report back here

[vbonini]

2.About DuplicateKeyException of tbl_tx_detail , could you please confirm whether UNIQUE KEY idx_tx_hash_index (tx_hash,tx_index) is correct?

These are the keys/indexes for that table.

mysql> show indexes from tbl_tx_detail;
+---------------+------------+--------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table         | Non_unique | Key_name                 | Seq_in_index | Column_name          | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+---------------+------------+--------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| tbl_tx_detail |          0 | PRIMARY                  |            1 | tx_hash              | A         |    11776732 |     NULL |   NULL |      | BTREE      |         |               | YES     |
| tbl_tx_detail |          0 | PRIMARY                  |            2 | tx_index             | A         |    29358112 |     NULL |   NULL |      | BTREE      |         |               | YES     |
| tbl_tx_detail |          1 | idx_from_address         |            1 | from_address         | A         |      863158 |     NULL |   NULL |      | BTREE      |         |               | YES     |
| tbl_tx_detail |          1 | idx_to_address           |            1 | to_address           | A         |     1500214 |     NULL |   NULL |      | BTREE      |         |               | YES     |
| tbl_tx_detail |          1 | idx_block_height         |            1 | block_height         | A         |     5438268 |     NULL |   NULL |      | BTREE      |         |               | YES     |
| tbl_tx_detail |          1 | idx_called_contract_hash |            1 | called_contract_hash | A         |      345148 |     NULL |   NULL |      | BTREE      |         |               | YES     |
| tbl_tx_detail |          1 | idx_tx_time              |            1 | tx_time              | A         |     7600908 |     NULL |   NULL |      | BTREE      |         |               | YES     |
+---------------+------------+--------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
7 rows in set (0.05 sec)

[giovannirco]

Maybe this is related to character set and collation?? How can I verify if my database is configured correctly? Which tag or commit is 100% working on this repository? @ksemaev asked this here

[matyushkins]

1.About DuplicateKeyException of tbl_ontid_tx_detail , please modify unique key idx_tx_hash_ontid:
ALTER TABLE tbl_ontid_tx_detail DROP KEY idx_tx_hash_ontid;
ALTER TABLE tbl_ontid_tx_detail ADD UNIQUE idx_tx_hash_ontid (tx_hash,ontid,description);

It helped me. Continued sync.
Added as a migration of the database version 1.57

[vbonini]

Do you have any other follow-up troubleshooting steps, after verifying the DB indexes? Do we need to add an index to our DB, or perform another check that our database is configured correctly?

cc @ksemaev @giovannirco

[giovannirco]

1.About DuplicateKeyException of tbl_ontid_tx_detail , please modify unique key idx_tx_hash_ontid:
ALTER TABLE tbl_ontid_tx_detail DROP KEY idx_tx_hash_ontid;
ALTER TABLE tbl_ontid_tx_detail ADD UNIQUE idx_tx_hash_ontid (tx_hash,ontid,description);

Since I am running an old commit on stage and prod I had to do this with the PRIMARY KEY

ALTER TABLE tbl_ontid_tx_detail DROP PRIMARY KEY;
ALTER TABLE tbl_ontid_tx_detail ADD PRIMARY KEY (tx_hash,ontid,description);

After changing this I started ontsynhandler and it continued the sync.

Now that I have gone through all of this I decided to get dev upgraded to latest version but I am not sure which one is the latest. I see the tag version2.1.1 but there are active commits to the branch version2.1.0 so I am confused.

I tried to build from branch version2.1.0 but got some errors that seems to be related to missing properties, the error below is an example but I realized what I was doing wrong and copied the properties from here

[ ERROR] [2020-07-10 03:40:12] org.springframework.boot.SpringApplication [858] - Application run failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'pushConfig': Injection of autowired dependencies failed; nested exception is java.lang.IllegalArgumentException: Could not resolve placeholder 'ontoservice.host' in value "${ontoservice.host}"

I am not entirely sure what to add here so I added without values

ontoservice.host=
ontoservice.appId=
ontoservice.appSecret=

and now I am getting this:

[ WARN ] [2020-07-10 03:51:09] org.mybatis.spring.mapper.ClassPathMapperScanner [44] - No MyBatis mapper was found in '[com.github.ontio]' package. Please check your configuration.
[ WARN ] [2020-07-10 03:51:13] org.springframework.boot.web.servlet.context.AnnotationConfigServletWebServerApplicationContext [557] - Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'nodeScheduledTask': Injection of autowired dependencies failed; nested exception is java.lang.IllegalArgumentException: Could not resolve placeholder 'task.syncGovernanceInfo.filePath' in value "${task.syncGovernanceInfo.filePath}"
[ ERROR] [2020-07-10 03:51:13] com.github.ontio.thread.BlockHandlerThread [123] - Exception occured，Synchronization thread can't work,error ...
java.lang.InterruptedException: sleep interrupted
	at java.lang.Thread.sleep(Native Method)
	at java.lang.Thread.sleep(Thread.java:340)
	at java.util.concurrent.TimeUnit.sleep(TimeUnit.java:386)
	at com.github.ontio.thread.BlockHandlerThread.run(BlockHandlerThread.java:69)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)
[ ERROR] [2020-07-10 03:51:13] org.springframework.boot.SpringApplication [858] - Application run failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'nodeScheduledTask': Injection of autowired dependencies failed; nested exception is java.lang.IllegalArgumentException: Could not resolve placeholder 'task.syncGovernanceInfo.filePath' in value "${task.syncGovernanceInfo.filePath}"
	at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessProperties(AutowiredAnnotationBeanPostProcessor.java:380)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1411)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:592)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:515)
	at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:320)
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:318)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199)
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:849)
	at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:877)
	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:549)
	at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:142)
	at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:775)
	at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:397)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:316)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1260)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1248)
	at com.github.ontio.OntsynhandlerApplication.main(OntsynhandlerApplication.java:54)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:48)
	at org.springframework.boot.loader.Launcher.launch(Launcher.java:87)
	at org.springframework.boot.loader.Launcher.launch(Launcher.java:50)
	at org.springframework.boot.loader.JarLauncher.main(JarLauncher.java:51)
Caused by: java.lang.IllegalArgumentException: Could not resolve placeholder 'task.syncGovernanceInfo.filePath' in value "${task.syncGovernanceInfo.filePath}"
	at org.springframework.util.PropertyPlaceholderHelper.parseStringValue(PropertyPlaceholderHelper.java:172)
	at org.springframework.util.PropertyPlaceholderHelper.replacePlaceholders(PropertyPlaceholderHelper.java:124)
	at org.springframework.core.env.AbstractPropertyResolver.doResolvePlaceholders(AbstractPropertyResolver.java:237)
	at org.springframework.core.env.AbstractPropertyResolver.resolveRequiredPlaceholders(AbstractPropertyResolver.java:211)
	at org.springframework.context.support.PropertySourcesPlaceholderConfigurer.lambda$processProperties$0(PropertySourcesPlaceholderConfigurer.java:175)
	at org.springframework.beans.factory.support.AbstractBeanFactory.resolveEmbeddedValue(AbstractBeanFactory.java:851)
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1188)
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1167)
	at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:593)
	at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:90)
	at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessProperties(AutowiredAnnotationBeanPostProcessor.java:374)
	... 25 common frames omitted                                                                                                                                                                                                                     ```

[leej1012]

Version2.1.0 is latest stable. Please note latest flyway db/migration in Explorer.

I added new config params:
task.syncGovernanceInfo.interval=600000
task.syncGovernanceInfo.filePath=config/authorizePeers.json

And 'authorizePeers.json' is in config folder.