search

zendesk / maxwell

Maxwell's daemon, a mysql-to-json kafka producer
https://maxwells-daemon.io/
Other
4.05k stars 1.01k forks source link

com.zendesk.maxwell.schema.ddl.MaxwellSQLSyntaxError: SELECT #186

Closed jpeyper closed 8 years ago

jpeyper commented 8 years ago

I had this error occur today.

line 4:0 no viable alternative at input 'SELECT'
line 5:3 token recognition error at: '*\r'
10:58:19,973 ERROR MysqlParserListener - (create_table (create_table_preamble CREATE TEMPORARY TABLE (table_name (name (id 172898_16841_transmem)))) SELECT t . FROM map . transmem AS t WHERE t . deleted = 'N' AND IFNULL ( t . trnnumrev , 0 ) = 0 AND t . locked = 'Y' AND t . lockedby = 'AUTOSTL' AND t . allocated = 'N' AND t . special = 'N' AND t . invnum_d = 172898 AND t . imnum = 16841)
10:58:19,974 ERROR SchemaChange - Error parsing SQL: 'CREATE
TEMPORARY
TABLE 172898_16841_transmem
SELECT
 t.*
FROM map.transmem AS t
WHERE t.deleted = 'N'
AND IFNULL(t.trnnumrev, 0) = 0
AND t.locked = 'Y'
AND t.lockedby = 'AUTOSTL'
AND t.allocated = 'N'
AND t.special = 'N'
AND t.invnum_d = 172898
AND t.imnum = 16841'
com.zendesk.maxwell.schema.ddl.MaxwellSQLSyntaxError: SELECT
    at com.zendesk.maxwell.schema.ddl.MysqlParserListener.visitErrorNode(MysqlParserListener.java:85)
    at org.antlr.v4.runtime.tree.ParseTreeWalker.walk(ParseTreeWalker.java:41)
    at org.antlr.v4.runtime.tree.ParseTreeWalker.walk(ParseTreeWalker.java:52)
    at org.antlr.v4.runtime.tree.ParseTreeWalker.walk(ParseTreeWalker.java:52)
    at org.antlr.v4.runtime.tree.ParseTreeWalker.walk(ParseTreeWalker.java:52)
    at com.zendesk.maxwell.schema.ddl.SchemaChange.parse(SchemaChange.java:67)
    at com.zendesk.maxwell.MaxwellReplicator.processQueryEvent(MaxwellReplicator.java:274)
    at com.zendesk.maxwell.MaxwellReplicator.getRow(MaxwellReplicator.java:254)
    at com.zendesk.maxwell.MaxwellReplicator.work(MaxwellReplicator.java:87)
    at com.zendesk.maxwell.RunLoopProcess.runLoop(RunLoopProcess.java:31)
    at com.zendesk.maxwell.Maxwell.run(Maxwell.java:89)
    at com.zendesk.maxwell.Maxwell.main(Maxwell.java:95)
10:58:19,975 INFO  SchemaPosition - Storing final position: BinlogPosition[awmstvx00441-bin.000001:79187451]
osheroff commented 8 years ago

huh. what version of mysql? I can certainly add this to the blacklist as Maxwell doesn't care about temporary tables, but according to https://dev.mysql.com/doc/refman/5.6/en/replication-features-temptables.html this isn't supposed to happen with row-based replication.

jpeyper commented 8 years ago

SELECT VERSION() tells me 5.6.25-enterprise-commercial-advanced-log

xmlking commented 8 years ago

I also got this error today. removed private data from query

12:45:12,219 ERROR SchemaChange - Error parsing SQL: 'CREATE OR REPLACE xyz=UNDEFINED DEFINER=`xyz`@`%` SQL zyz VIEW `zyz` AS SELECT a.ztzAS xyz, a.zyzAS zyx, a.xyzAS xyz, a.xyzAS xyz, b.xyzAS xyz, b.xyzAS xyz, b.xyzAS xyz, b.xyzAS blab ,   FROM xyz AS a, xyx AS b WHERE a.xyz= b.xyz'
com.zendesk.maxwell.schema.ddl.MaxwellSQLSyntaxError: CREATE
        at com.zendesk.maxwell.schema.ddl.MysqlParserListener.visitErrorNode(MysqlParserListener.java:85)
        at org.antlr.v4.runtime.tree.ParseTreeWalker.walk(ParseTreeWalker.java:41)
        at org.antlr.v4.runtime.tree.ParseTreeWalker.walk(ParseTreeWalker.java:52)
        at org.antlr.v4.runtime.tree.ParseTreeWalker.walk(ParseTreeWalker.java:52)
        at com.zendesk.maxwell.schema.ddl.SchemaChange.parse(SchemaChange.java:67)
        at com.zendesk.maxwell.MaxwellReplicator.processQueryEvent(MaxwellReplicator.java:274)
        at com.zendesk.maxwell.MaxwellReplicator.getRow(MaxwellReplicator.java:254)
        at com.zendesk.maxwell.MaxwellReplicator.work(MaxwellReplicator.java:87)
        at com.zendesk.maxwell.RunLoopProcess.runLoop(RunLoopProcess.java:31)
        at com.zendesk.maxwell.Maxwell.run(Maxwell.java:89)
        at com.zendesk.maxwell.Maxwell.main(Maxwell.java:95)
12:45:12,220 INFO  SchemaPosition - Storing final position: BinlogPosition[binarylog.023492:6773]
osheroff commented 8 years ago

@xmlking, I opened https://github.com/zendesk/maxwell/pull/185/files the other day for view creation. @jpeyper has got himself a very odd config in which he's getting some row-based and some statement-based stuff.

Also, I'm doing what I've needed to do for awhile which is run maxwell against the mysql test suite and fix a bunch of the lingering parse problems.

xmlking commented 8 years ago

Thanks @osheroff . Appreciate your work and support.

osheroff commented 8 years ago

both issues fixed in v0.16.1