Loading...

Type: Bug
Resolution: Done
Priority: Major
Fix Version/s: 1.2.1.Final
Affects Version/s: 1.1.2.Final
Component/s: mysql-connector
Labels:
None

Steps to Reproduce:
Hide

create a new file named "mysql-decimal-issue.ddl" , under path: {debezium-project-home}/debezium-connector-mysql/src/test/resources/ddl/;

copy above SQL into step 1's file

add below code into MySqlSchemaTest.java

@Test public void testForDecimal() { mysql = build .with(DatabaseHistory.SKIP_UNPARSEABLE_DDL_STATEMENTS, true) .storeDatabaseHistoryInFile(TEST_FILE_PATH) .serverName(SERVER_NAME) .createSchemas(); mysql.start(); source.setBinlogStartPoint("binlog-001", 400); mysql.applyDdl(source, "db1", "SET " + MySqlSystemVariables.CHARSET_NAME_SERVER + "=utf8mb4", this::printStatements); mysql.applyDdl(source, "db1", readFile("ddl/mysql-decimal-issue.ddl"), this::printStatements); assertHistoryRecorded(); }

run unit test as:

mvn test -Dtest=MySqlSchemaTest#testForDecimal

you will get the following exception:

io.debezium.text.ParsingException: no viable alternative at input 'CREATE DEFINER=`root`@`%` PROCEDURE `_Navicat_Temp_Stored_Proc`(st datetime,et datetime,saleman varchar(50))\nBEGIN CREATE TEMPORARY TABLE temp1\nSELECT\n SUM(round(case\n when\n bod.return_product_weight is not null\n then\n bod.actual_price-floor(floor(bod.return_product_weight/50)/10*bod.unit_price*100)/100\n when\n bod.return_product_num is not null\n then\n bod.actual_price-(CAST(bod.return_product_num AS DECIMAL(10)' at io.debezium.antlr.ParsingErrorListener.syntaxError(ParsingErrorListener.java:40) at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41) at org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:544) at org.antlr.v4.runtime.DefaultErrorStrategy.reportNoViableAlternative(DefaultErrorStrategy.java:310) at org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:136) at io.debezium.ddl.parser.mysql.generated.MySqlParser.sqlStatements(MySqlParser.java:1121) at io.debezium.ddl.parser.mysql.generated.MySqlParser.root(MySqlParser.java:849) at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:68) at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:41)
Show
create a new file named "mysql-decimal-issue.ddl" , under path: {debezium-project-home}/debezium-connector-mysql/src/test/resources/ddl/; copy above SQL into step 1's file add below code into MySqlSchemaTest.java @Test public void testForDecimal() { mysql = build .with(DatabaseHistory.SKIP_UNPARSEABLE_DDL_STATEMENTS, true ) .storeDatabaseHistoryInFile(TEST_FILE_PATH) .serverName(SERVER_NAME) .createSchemas(); mysql.start(); source.setBinlogStartPoint( "binlog-001" , 400); mysql.applyDdl(source, "db1" , "SET " + MySqlSystemVariables.CHARSET_NAME_SERVER + "=utf8mb4" , this ::printStatements); mysql.applyDdl(source, "db1" , readFile( "ddl/mysql-decimal-issue.ddl" ), this ::printStatements); assertHistoryRecorded(); } run unit test as: mvn test -Dtest=MySqlSchemaTest#testForDecimal you will get the following exception: io.debezium.text.ParsingException: no viable alternative at input 'CREATE DEFINER=`root`@`%` PROCEDURE `_Navicat_Temp_Stored_Proc`(st datetime,et datetime,saleman varchar(50))\nBEGIN CREATE TEMPORARY TABLE temp1\nSELECT\n SUM(round( case \n when\n bod.return_product_weight is not null \n then\n bod.actual_price-floor(floor(bod.return_product_weight/50)/10*bod.unit_price*100)/100\n when\n bod.return_product_num is not null \n then\n bod.actual_price-(CAST(bod.return_product_num AS DECIMAL(10)' at io.debezium.antlr.ParsingErrorListener.syntaxError(ParsingErrorListener.java:40) at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41) at org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:544) at org.antlr.v4.runtime.DefaultErrorStrategy.reportNoViableAlternative(DefaultErrorStrategy.java:310) at org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:136) at io.debezium.ddl.parser.mysql.generated.MySqlParser.sqlStatements(MySqlParser.java:1121) at io.debezium.ddl.parser.mysql.generated.MySqlParser.root(MySqlParser.java:849) at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:68) at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:41)

SFDC Cases Counter:
SFDC Cases Open:
SFDC Cases Links:

I got following exception :

io.debezium.text.ParsingException: no viable alternative at input 'CREATE DEFINER=`root`@`%` PROCEDURE `_Navicat_Temp_Stored_Proc`(st datetime,et datetime,saleman varchar(50))\nBEGIN CREATE TEMPORARY TABLE temp1\nSELECT\n    SUM(round(case\n                   when\n                        bod.return_product_weight is not null\n                   then\n                        bod.actual_price-floor(floor(bod.return_product_weight/50)/10*bod.unit_price*100)/100\n                   when\n                        bod.return_product_num is not null\n                   then\n                        bod.actual_price-(CAST(bod.return_product_num AS DECIMAL(10)'
	at io.debezium.antlr.ParsingErrorListener.syntaxError(ParsingErrorListener.java:40)
	at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41)
	at org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:544)
	at org.antlr.v4.runtime.DefaultErrorStrategy.reportNoViableAlternative(DefaultErrorStrategy.java:310)
	at org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:136)
	at io.debezium.ddl.parser.mysql.generated.MySqlParser.sqlStatements(MySqlParser.java:1121)
	at io.debezium.ddl.parser.mysql.generated.MySqlParser.root(MySqlParser.java:849)
	at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:68)
	at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:41)

When I dig into the exception , I found the ANTLR parser did not resolve following SQL:

CREATE DEFINER=`root`@`%` PROCEDURE `_Navicat_Temp_Stored_Proc`(st datetime,et datetime,saleman varchar(50))
BEGIN CREATE TEMPORARY TABLE temp1
SELECT
    SUM(round(case
                   when
                        bod.return_product_weight is not null
                   then
                        bod.actual_price-floor(floor(bod.return_product_weight/50)/10*bod.unit_price*100)/100
                   when
                        bod.return_product_num is not null
                   then
                        bod.actual_price-(CAST(bod.return_product_num AS DECIMAL(10))*unit_price)
                   else
                        bod.actual_price END,2)
       ) '实际金额小计(不含退货)'
FROM business_order bo
LEFT JOIN business_order_detail bod on bo.id=bod.order_id

Details

Description

Attachments

Easy Agile Planning Poker

Activity

People

Dates