canal 出现异常Cause: org.h2.jdbc.JdbcSQLException: Table “META_HISTORY“ not found

最新推荐文章于 2025-03-03 10:52:22 发布

ak3k

最新推荐文章于 2025-03-03 10:52:22 发布

阅读量2.3k

点赞数

分类专栏： canal

本文链接：https://blog.youkuaiyun.com/asdf1368822590/article/details/118650749

版权

canal 专栏收录该内容

1 篇文章

订阅专栏

canal版本：1.1.5

详细异常如下：

### The error may exist in spring/tsdb/sql-map/sqlmap_history.xml
### The error may involve com.alibaba.otter.canal.parse.inbound.mysql.tsdb.dao.MetaHistoryMapper.findByTimestamp
### The error occurred while executing a query
### SQL: select                       a.id as id,a.gmt_create as gmtCreate,a.gmt_modified as gmtModified,   a.destination as destination,a.binlog_file as binlogFile,
a.binlog_offest as binlogOffest,a.binlog_master_id as binlogMasterId,a.binlog_timestamp as binlogTimestamp,   a.use_schema as useSchema,a.sql_schema as sqlSchema,a.s
ql_table as sqlTable,a.sql_text as sqlText,a.sql_type as sqlType,a.extra as extra                         from meta_history a                   where destination = ?
 and binlog_timestamp >= ? and binlog_timestamp <= ?         order by binlog_timestamp asc,id asc
### Cause: org.h2.jdbc.JdbcSQLException: Table "META_HISTORY" not found; SQL statement:
select


                a.id as id,a.gmt_create as gmtCreate,a.gmt_modified as gmtModified,
                a.destination as destination,a.binlog_file as binlogFile,a.binlog_offest as binlogOffest,a.binlog_master_id as binlogMasterId,a.binlog_timestamp as b
inlogTimestamp,
                a.use_schema as useSchema,a.sql_schema as sqlSchema,a.sql_table as sqlTable,a.sql_text as sqlText,a.sql_type as sqlType,a.extra as extra


        from meta_history a

        where destination = ? and binlog_timestamp >= ? and binlog_timestamp <= ?
        order by binlog_timestamp asc,id asc [42102-196]
; bad SQL grammar []; nested exception is org.h2.jdbc.JdbcSQLException: Table "META_HISTORY" not found; SQL statement:
select


                a.id as id,a.gmt_create as gmtCreate,a.gmt_modified as gmtModified,
                a.destination as destination,a.binlog_file as binlogFile,a.binlog_offest as binlogOffest,a.binlog_master_id as binlogMasterId,a.binlog_timestamp as b
inlogTimestamp,
                a.use_schema as useSchema,a.sql_schema as sqlSchema,a.sql_table as sqlTable,a.sql_text as sqlText,a.sql_type as sqlType,a.extra as extra


        from meta_history a

        where destination = ? and binlog_timestamp >= ? and binlog_timestamp <= ?
        order by binlog_timestamp asc,id asc [42102-196]
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:234)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
        at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
        at com.sun.proxy.$Proxy9.selectList(Unknown Source)
        at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223)
        at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:147)
        at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:80)
        at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:144)
        at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85)
        at com.sun.proxy.$Proxy11.findByTimestamp(Unknown Source)
        at com.alibaba.otter.canal.parse.inbound.mysql.tsdb.dao.MetaHistoryDAO.findByTimestamp(MetaHistoryDAO.java:26)
        at com.alibaba.otter.canal.parse.inbound.mysql.tsdb.DatabaseTableMeta.applyHistoryOnMemory(DatabaseTableMeta.java:426)
        at com.alibaba.otter.canal.parse.inbound.mysql.tsdb.DatabaseTableMeta.rollback(DatabaseTableMeta.java:167)
        at com.alibaba.otter.canal.parse.inbound.mysql.AbstractMysqlEventParser.processTableMeta(AbstractMysqlEventParser.java:142)
        at com.alibaba.otter.canal.parse.inbound.AbstractEventParser$1.run(AbstractEventParser.java:197)
        at java.lang.Thread.run(Thread.java:745)
Caused by: org.h2.jdbc.JdbcSQLException: Table "META_HISTORY" not found; SQL statement:
select


                a.id as id,a.gmt_create as gmtCreate,a.gmt_modified as gmtModified,
                a.destination as destination,a.binlog_file as binlogFile,a.binlog_offest as binlogOffest,a.binlog_master_id as binlogMasterId,a.binlog_timestamp as b
inlogTimestamp,
                a.use_schema as useSchema,a.sql_schema as sqlSchema,a.sql_table as sqlTable,a.sql_text as sqlText,a.sql_type as sqlType,a.extra as extra


        from meta_history a

        where destination = ? and binlog_timestamp >= ? and binlog_timestamp <= ?
        order by binlog_timestamp asc,id asc [42102-196]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
        at org.h2.message.DbException.get(DbException.java:179)
        at org.h2.message.DbException.get(DbException.java:155)
        at org.h2.command.Parser.readTableOrView(Parser.java:5552)
        at org.h2.command.Parser.readTableFilter(Parser.java:1266)
        at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1946)
        at org.h2.command.Parser.parseSelectSimple(Parser.java:2095)
        at org.h2.command.Parser.parseSelectSub(Parser.java:1940)
        at org.h2.command.Parser.parseSelectUnion(Parser.java:1755)
        at org.h2.command.Parser.parseSelect(Parser.java:1743)
        at org.h2.command.Parser.parsePrepared(Parser.java:449)
        at org.h2.command.Parser.parse(Parser.java:321)
        at org.h2.command.Parser.parse(Parser.java:293)
        at org.h2.command.Parser.prepareCommand(Parser.java:258)
        at org.h2.engine.Session.prepareLocal(Session.java:578)
        at org.h2.engine.Session.prepareCommand(Session.java:519)
        at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1204)
        at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:73)
        at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:288)
        at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:366)
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:86)
        at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59)
        at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:87)
        at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62)
        at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
        at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
        at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
        at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
        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:483)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)
        ... 13 more
]
2021-07-11 01:25:14.296 [destination = example , address = host.docker.internal/192.168.65.2:3306 , EventParser] WARN  c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventPars
erProxy - ---> begin to find start position, it will be long time for reset or first position
2021-07-11 01:25:14.297 [destination = example , address = host.docker.internal/192.168.65.2:3306 , EventParser] WARN  c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventPars
erProxy - prepare to find start position just show master status
2021-07-11 01:25:14.353 [destination = example , address = host.docker.internal/192.168.65.2:3306 , EventParser] ERROR c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventPars
erProxy - dump address host.docker.internal/192.168.65.2:3306 has an error, retrying. caused by
com.alibaba.otter.canal.parse.exception.CanalParseException: apply failed
Caused by: org.springframework.jdbc.BadSqlGrammarException:
### Error querying database.  Cause: org.h2.jdbc.JdbcSQLException: Table "META_HISTORY" not found; SQL statement:
select


                a.id as id,a.gmt_create as gmtCreate,a.gmt_modified as gmtModified,
                a.destination as destination,a.binlog_file as binlogFile,a.binlog_offest as binlogOffest,a.binlog_master_id as binlogMasterId,a.binlog_timestamp as b
inlogTimestamp,
                a.use_schema as useSchema,a.sql_schema as sqlSchema,a.sql_table as sqlTable,a.sql_text as sqlText,a.sql_type as sqlType,a.extra as extra


        from meta_history a

        where destination = ? and binlog_timestamp >= ? and binlog_timestamp <= ?
        order by binlog_timestamp asc,id asc [42102-196]
### The error may exist in spring/tsdb/sql-map/sqlmap_history.xml
### The error may involve com.alibaba.otter.canal.parse.inbound.mysql.tsdb.dao.MetaHistoryMapper.findByTimestamp

原因：

因为canal默认是通过h2数据库存储的表结构变动，比如我们当前binlog日志position 10-100 这个时候classes的字段是2个 id,name 但是101之后就加了个字段sex

我们因为某些原因导致我们canalService阻塞读取位置一直10 虽然表结构变动及时刷入到了到h2数据库,但是重启启动canalService 刷入的是最新的表结构 id,name,sex 然后我们继续从10开始去读取表结构DDL对不上报错

解决方式就是将内存数据库改为物理持久化 canal支持数据库配置。