一次Navicat执行带注释的DDL导致canal异常的问题分析 以及相关知识扩展总结

问题描述

在Navicat上执行一个修改表结构语句的SQL，数据库运行结果正常，但是canal在解析处理时发生了异常，导致后面binlog无法在解析。

解决措施

因为是在本地开发环境，所以解决的措施也很直接，删除example目录下的h2.mv.db和meta.dat文件，并调整监听binlog监听的position位置和时间戳，服务重启。

问题分析

ALTER TABLE  `my_table` 
ADD COLUMN `last_updated_by` int(11) NULL COMMENT '测试验证' AFTER `status`
com.alibaba.druid.sql.parser.ParserException: syntax error, error in :'-- 注释信息
--
ALTER TABLE  `fxd, pos 10, line 2, column 2, token -
        at com.alibaba.druid.sql.parser.SQLParser.printError(SQLParser.java:576) ~[druid-1.2.6.jar:1.2.6]
        at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:602) ~[druid-1.2.6.jar:1.2.6]
        at com.alibaba.druid.sql.SQLUtils.parseStatements(SQLUtils.java:565) ~[druid-1.2.6.jar:1.2.6]
        at com.alibaba.druid.sql.SQLUtils.parseStatements(SQLUtils.java:587)
1
2
3
4
5
6
7
8
9

主要错误描述在于

com.alibaba.druid.sql.parser.ParserException: syntax error, error in :'-- 注释信息
--
ALTER TABLE  `fxd, pos 10, line 2, column 2, token -
1
2
3

可以猜测原因大概是因为canal解析SQL异常，canal解析SQL语法树使用的是Druid的工具方法，即SQLUtils。

druid解析工具验证

为了验证我所执行的SQL是否能够在其解析工具正常执行，下面使用druid的SQL解析工具进行测试。
目前canal服务器端部署的是1.1.5版本，我到github中看了其依赖的druid描述如下：

  <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.2.6</version>
  </dependency>
1
2
3
4
5

为了验证以及避免后面canal运行不稳定，我在项目中引入了该依赖并且保持同样的版本信息。

以上是我根据错误描述定位到的源码位置，主要就是SQLUtils.parseStatements解析部分

这里创建了一个测试表并测试添加一些字段

        DbType dbType = JdbcConstants.MYSQL;
        String sql = "-- Comments goes here\n" +
                "-- Comments goes \n" +
                "-- \n" +
                "ALTER TABLE  `t_test` ADD COLUMN `name` int(11) NULL COMMENT '测试验证' AFTER `status`; ";
        List<SQLStatement> statementList = SQLUtils.parseStatements(sql, dbType);
        for (SQLStatement sqlStatement : statementList) {
            System.out.println(sqlStatement);
        }
1
2
3
4
5
6
7
8
9

经过多次的测试，发现通过以上的方式去解析SQL都是正常的。

当我尝试去故意写错注释语法时，以下位置不保留空格位置

如下所示，发现出现了类似的错误。但是这个是因为我故意将注释语法写错了导致的，因为时间问题并没有在针对该问题进行详细的测试了，但是可以得出的结论是druid的SQL解析是没啥问题的，出现上面的问题的原因可能是navicat在发送的sql执行语句出现了一些特殊的字符，导致canal这边解析异常。

ORM框架验证

针对上面的问题，继续将canal执行有问题的SQL语句放入MyBatis框架的XML中，放入项目中运行，经过多次的测试，验证得出都是没问题的，canal可以正常解析注释和SQL。

问题复现

经过多次的测试，最终验证出在navicat中执行如下带有注释的SQL语句时会出现异常，其中主要是因为 【1】【4】行没有注释说明的注释符号导致的。其中只写注释符号没有注释不管位置放在哪里都会出现解析异常。

说明：为什么我会写这样的注释，这是直接用的navicat的注释模板，没有想到会出现这个问题，也幸亏是在开发环境可以快速的处理。

扩展

开发中SQL注释注意事项

SQL标准注释

SQL标准注释有两种：
【1】释符“–” 用于单行注释，格式：–注释内容
【2】多行注释，即“/**/”。“/”用于注释文字的开头，“/”用于注释文字的结尾，可在程序中标识多行文字为注释。

针对以上的出现的问题在有canal关联的数据库服务中还是保证SQL注释的整洁，不要有空注释（目前仅在navicat发现该问题）。

代码中注释

之前有看到因为在XML中写单行注释，导致mybatis仍旧会把#{}算成一个带注入的参数问题

此外，在之前工作开发中也发现了一个代码注释嵌入到SQL语句中的问题，但是这个并没有影响SQL运行，影响了IDEA一些SQL格式化工具的使用

这个SQL是可以正常运行的，但是在使用它一些MyBatis的SQL 自动日志格式化工具时就解析有问题了，把这个SQL拿出来是无法执行的，需要手动去除掉这个注释。

对于写在XML中的SQL语句应该遵守的是XML的注释规范，虽然大多数情况下使用其他注释也没问题，但应该尽量去避免出现问题。

XML注释标准：<!-- content -->  
1

Druid组件SQL解析工具

在进行问题验证测试时所使用的Druid的SQL解析工具，在经过多次使用下，感觉该工具对于数据库SQL语句的解析和格式化有着很不错的应用，这里记录下使用实践。
SQL Parser是Druid的一个重要组成部分，Druid内置使用SQL Parser来实现防御SQL注入（WallFilter）、合并统计没有参数化的SQL(StatFilter的mergeSql)、SQL格式化、分库分表。

【详细的介绍，之前有发过一篇文章，参见如下】

SQL解析处理和扩展开发工具 Java

小结

【1】在进行了多次的测试下，也仅仅发现只有当使用navicat并存在空行注释的情况下才会出现问题，因为日常开发中这样的情况不多并且可以避免，也就没有过多的去深究了，以后避免这样写法。
【2】本次的问题值得反思的是在日常开发中，对于注释这些细节问题也需要重视起来，比如上面的空行注释，还有上面参考案例XML中的SQL注释等等，在平常的开发中要尽可能的保持标准写法，删除无用的注释。
【3】本次实践中所使用的Druid的SQL解析工具，以及围绕着该工具介绍的一些SQL操作方法，确实是一个很好辅助工具，如果后面有类似的需求可以参考使用，尤其是在开发一些数据平台的相关系统