search

alibaba / otter

阿里巴巴分布式数据库同步系统(解决中美异地机房)
Apache License 2.0
8.05k stars 2.49k forks source link

双向同步时出现No database selected错误 #194

Open vlyl opened 8 years ago

vlyl commented 8 years ago

配置好了双写的同步，向其中一个节点插入一条数据之后，channel状态变成了挂起，对应的pipeline产生了标题为EXCEPTION的日志，内容 有##load phase one failed! 和 No database selected; nested exception is java.sql.BatchUpdateException: No database selected 等信息

2016-09-01 16:03:40.631 [pipelineId = 97,taskName = SelectTask] WARN  c.a.o.shared.arbitrate.impl.setl.monitor.MainstemMonitor - mainstem is not run any in node
2016-09-01 16:03:41.182 [destination = canal-cn-dba , address = /120.76.153.94:1036 , EventParser] WARN  c.a.otter.canal.parse.inbound.mysql.MysqlEventParser - prepare to find start position mysql-bin.011353:17870858:
2016-09-01 16:05:26.892 [Otter-Seda-Executor-77] WARN  com.alibaba.otter.node.etl.load.loader.db.DbLoadAction - ##load phase one failed!
java.util.concurrent.ExecutionException: com.alibaba.otter.node.etl.load.exception.LoadException: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [insert into aa-test.tb_aa_test(`username` , `phone` , `mail` , `id`) values (? , ? , ? , ?) on duplicate key update `username`=values(`username`) , `phone`=values(`phone`) , `mail`=values(`mail`) , `id`=values(`id`)]; SQL state [3D000]; error code [1046]; No database selected; nested exception is java.sql.BatchUpdateException: No database selected
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:603)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:615)
    at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:884)
    at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker$1.doInTransaction(DbLoadAction.java:587)
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
    at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.doCall(DbLoadAction.java:578)
    at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.call(DbLoadAction.java:541)
    at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.call(DbLoadAction.java:513)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.BatchUpdateException: No database selected
    at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1607)
    at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1272)
    at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
    at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
    at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:899)
    at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:1)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587)
    ... 11 more
Caused by: java.sql.SQLException: No database selected
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3835)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3771)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2535)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1911)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2145)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2081)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2066)
    at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1568)
    ... 17 more
:-----------------
- PairId: 105 , TableId: 57 , EventType : I , Time : 1472717121000 
- Consistency :  , Mode :  
-----------------
---Pks
    EventColumn[index=0,columnType=4,columnName=id,columnValue=49,isNull=false,isKey=true,isUpdate=true]
---oldPks

---Columns
    EventColumn[index=1,columnType=12,columnName=username,columnValue=n,isNull=false,isKey=false,isUpdate=true]
    EventColumn[index=2,columnType=12,columnName=phone,columnValue=p,isNull=false,isKey=false,isUpdate=true]
    EventColumn[index=3,columnType=12,columnName=mail,columnValue=m,isNull=false,isKey=false,isUpdate=true]
---Sql
    insert into aa-test.tb_aa_test(`username` , `phone` , `mail` , `id`) values (? , ? , ? , ?) on duplicate key update `username`=values(`username`) , `phone`=values(`phone`) , `mail`=values(`mail`) , `id`=values(`id`)

    at java.util.concurrent.FutureTask.report(FutureTask.java:122) [na:1.8.0_74]
    at java.util.concurrent.FutureTask.get(FutureTask.java:192) [na:1.8.0_74]
    at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.doTwoPhase(DbLoadAction.java:406) [node.etl-4.2.12.jar:na]
    at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.doLoad(DbLoadAction.java:275) [node.etl-4.2.12.jar:na]
    at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.load(DbLoadAction.java:161) [node.etl-4.2.12.jar:na]
    at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$$FastClassByCGLIB$$d932a4cb.invoke(<generated>) [cglib-nodep-2.2.jar:na]
    at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191) [cglib-nodep-2.2.jar:na]
    at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:618) [spring-aop-3.1.2.RELEASE.jar:3.1.2.RELEASE]
    at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$$EnhancerByCGLIB$$80fd23c2.load(<generated>) [cglib-nodep-2.2.jar:na]
    at com.alibaba.otter.node.etl.load.loader.db.DataBatchLoader$2.call(DataBatchLoader.java:198) [node.etl-4.2.12.jar:na]
    at com.alibaba.otter.node.etl.load.loader.db.DataBatchLoader$2.call(DataBatchLoader.java:189) [node.etl-4.2.12.jar:na]
    at java.util.concurrent.FutureTask.run(FutureTask.java:266) [na:1.8.0_74]
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [na:1.8.0_74]
    at java.util.concurrent.FutureTask.run(FutureTask.java:266) [na:1.8.0_74]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_74]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_74]
    at java.lang.Thread.run(Thread.java:745) [na:1.8.0_74]
Caused by: com.alibaba.otter.node.etl.load.exception.LoadException: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [insert into aa-test.tb_aa_test(`username` , `phone` , `mail` , `id`) values (? , ? , ? , ?) on duplicate key update `username`=values(`username`) , `phone`=values(`phone`) , `mail`=values(`mail`) , `id`=values(`id`)]; SQL state [3D000]; error code [1046]; No database selected; nested exception is java.sql.BatchUpdateException: No database selected
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:603)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:615)
    at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:884)
    at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker$1.doInTransaction(DbLoadAction.java:587)
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
    at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.doCall(DbLoadAction.java:578)
    at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.call(DbLoadAction.java:541)
    at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.call(DbLoadAction.java:513)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.BatchUpdateException: No database selected
    at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1607)
    at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1272)
    at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
    at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
    at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:899)
    at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:1)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587)
    ... 11 more
Caused by: java.sql.SQLException: No database selected
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3835)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3771)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2535)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1911)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2145)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2081)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2066)
    at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1568)
    ... 17 more
:-----------------
- PairId: 105 , TableId: 57 , EventType : I , Time : 1472717121000 
- Consistency :  , Mode :  
-----------------
---Pks
    EventColumn[index=0,columnType=4,columnName=id,columnValue=49,isNull=false,isKey=true,isUpdate=true]
---oldPks

---Columns
    EventColumn[index=1,columnType=12,columnName=username,columnValue=n,isNull=false,isKey=false,isUpdate=true]
    EventColumn[index=2,columnType=12,columnName=phone,columnValue=p,isNull=false,isKey=false,isUpdate=true]
    EventColumn[index=3,columnType=12,columnName=mail,columnValue=m,isNull=false,isKey=false,isUpdate=true]
---Sql
    insert into aa-test.tb_aa_test(`username` , `phone` , `mail` , `id`) values (? , ? , ? , ?) on duplicate key update `username`=values(`username`) , `phone`=values(`phone`) , `mail`=values(`mail`) , `id`=values(`id`)

其中数据表结构是，其中id是自增键

+----+----------+-------+------+
| id | username | phone | mail |
+----+----------+-------+------+
|  1 | a        | a     | a    |
|  9 | b        | b     | b    |
+----+----------+-------+------+

sql语句是

use aa-test;insert into tb_aa_test (username, phone, mail) values ('n', 'p', 'm');
agapple commented 7 years ago

---Sql insert into aa-test.tb_aa_test(username , phone , mail , id) values (? , ? , ? , ?) on duplicate key update username=values(username) , phone=values(phone) , mail=values(mail) , id=values(id)

你看生成的sql已经带上了对应的schema

davidhefan commented 6 years ago

我也遇到了同样的问题，请问是如何解决的啊？

agapple commented 6 years ago

aa-test库名需要转义