Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

mysql datatype alias SERIAL aborts maxwell producer #371

Closed
jsalmeron opened this issue Jun 29, 2016 · 2 comments
Closed

mysql datatype alias SERIAL aborts maxwell producer #371

jsalmeron opened this issue Jun 29, 2016 · 2 comments

Comments

@jsalmeron
Copy link

A create table statement using the SERIAL datatype, which mysql (5.5) implicitly converts to BIGINT UNSIGNED breaks the maxwell producer:

---- customers (TABLE)
COLUMN_NAME | DATA_TYPE       | PK | NULLABLE | DEFAULT | AUTOINCREMENT | COMPUTED | REMARKS | POSITION
------------+-----------------+----+----------+---------+---------------+----------+---------+---------
id          | BIGINT UNSIGNED | NO | NO       |         | YES           | NO       |         |        1
first_name  | VARCHAR(255)    | NO | YES      |         | NO            | NO       |         |        2
last_name   | VARCHAR(255)    | NO | YES      |         | NO            | NO       |         |        3


13:50:13,355 INFO  Maxwell - Maxwell is booting (MaxwellKafkaProducer), starting at BinlogPosition[binlog.000003:30343]
13:50:13,356 INFO  ProducerConfig - ProducerConfig values: 
        compression.type = gzip
        metric.reporters = []
        metadata.max.age.ms = 300000
        metadata.fetch.timeout.ms = 5000
        acks = 1
        batch.size = 16384
        reconnect.backoff.ms = 10
        bootstrap.servers = [localhost:9092]
        receive.buffer.bytes = 32768
        retry.backoff.ms = 100
        buffer.memory = 33554432
        timeout.ms = 30000
        key.serializer = class org.apache.kafka.common.serialization.StringSerializer
        retries = 1
        max.request.size = 1048576
        block.on.buffer.full = true
        value.serializer = class org.apache.kafka.common.serialization.StringSerializer
        metrics.sample.window.ms = 30000
        send.buffer.bytes = 131072
        max.in.flight.requests.per.connection = 5
        metrics.num.samples = 2
        linger.ms = 0
        client.id = 

13:50:13,360 WARN  ProducerConfig - The configuration value.serializer = class org.apache.kafka.common.serialization.StringSerializer was supplied but isn't a known config.
13:50:13,360 WARN  ProducerConfig - The configuration key.serializer = class org.apache.kafka.common.serialization.StringSerializer was supplied but isn't a known config.
13:50:13,480 INFO  TransportImpl - connecting to host: 172.16.215.19, port: 3306
13:50:13,512 INFO  TransportImpl - connected to host: 172.16.215.19, port: 3306, context: AbstractTransport.Context[threadId=49347,scramble=x0"Z~/iHiX\D"zc`9<`O,protocolVersion=10,serverHost=172.16.215.19,serverPort=3306,serverStatus=2,serverCollation=8,serverVersion=5.5.41-37.0-55-log,serverCapabilities=63487]
13:50:13,513 INFO  AuthenticatorImpl - start to login, user: maxwell, host: 172.16.215.19, port: 3306
13:50:13,520 INFO  AuthenticatorImpl - login successfully, user: maxwell, detail: OKPacket[packetMarker=0,affectedRows=0,insertId=0,serverStatus=2,warningCount=0,message=<null>]
13:50:13,704 INFO  MysqlSavedSchema - Restoring schema id 9387 (last modified at BinlogPosition[binlog.000003:5031])
13:50:13,937 INFO  MysqlSavedSchema - Restoring schema id 2 (last modified at BinlogPosition[binlog.000001:2285119])
13:50:14,592 INFO  MysqlSavedSchema - beginning to play deltas...
13:50:14,820 INFO  MysqlSavedSchema - played 3129 deltas in 227ms
13:50:15,020 ERROR MysqlParserListener - (data_type SERIAL)
13:50:15,020 ERROR SchemaChange - Error parsing SQL: 'CREATE TABLE customers(id SERIAL, first_name VARCHAR(255), last_name VARCHAR(255))'
com.zendesk.maxwell.schema.ddl.MaxwellSQLSyntaxError: SERIAL
        at com.zendesk.maxwell.schema.ddl.MysqlParserListener.visitErrorNode(MysqlParserListener.java:84)
        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 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 org.antlr.v4.runtime.tree.ParseTreeWalker.walk(ParseTreeWalker.java:52)
        at com.zendesk.maxwell.schema.ddl.SchemaChange.parseSQL(SchemaChange.java:79)
        at com.zendesk.maxwell.schema.ddl.SchemaChange.parse(SchemaChange.java:91)
        at com.zendesk.maxwell.schema.AbstractSchemaStore.resolveSQL(AbstractSchemaStore.java:33)
        at com.zendesk.maxwell.schema.MysqlSchemaStore.processSQL(MysqlSchemaStore.java:47)
        at com.zendesk.maxwell.MaxwellReplicator.processQueryEvent(MaxwellReplicator.java:324)
        at com.zendesk.maxwell.MaxwellReplicator.getRow(MaxwellReplicator.java:303)
        at com.zendesk.maxwell.MaxwellReplicator.work(MaxwellReplicator.java:114)
        at com.zendesk.maxwell.RunLoopProcess.runLoop(RunLoopProcess.java:31)
        at com.zendesk.maxwell.Maxwell.run(Maxwell.java:76)
        at com.zendesk.maxwell.Maxwell.main(Maxwell.java:82)

This is quite a problem, because we had to force the advancement by quite a few binlog sequences, as advancing only one (using --init_position binlog.000003:30344), caused an EOFException:

13:50:05,027 INFO  Maxwell - Maxwell is booting (MaxwellKafkaProducer), starting at BinlogPosition[binlog.000003:30344]
13:50:05,028 INFO  ProducerConfig - ProducerConfig values: 
        compression.type = gzip
        metric.reporters = []
        metadata.max.age.ms = 300000
        metadata.fetch.timeout.ms = 5000
        acks = 1
        batch.size = 16384
        reconnect.backoff.ms = 10
        bootstrap.servers = [localhost:9092]
        receive.buffer.bytes = 32768
        retry.backoff.ms = 100
        buffer.memory = 33554432
        timeout.ms = 30000
        key.serializer = class org.apache.kafka.common.serialization.StringSerializer
        retries = 1
        max.request.size = 1048576
        block.on.buffer.full = true
        value.serializer = class org.apache.kafka.common.serialization.StringSerializer
        metrics.sample.window.ms = 30000
        send.buffer.bytes = 131072
        max.in.flight.requests.per.connection = 5
        metrics.num.samples = 2
        linger.ms = 0
        client.id = 

13:50:05,032 WARN  ProducerConfig - The configuration value.serializer = class org.apache.kafka.common.serialization.StringSerializer was supplied but isn't a known config.
13:50:05,032 WARN  ProducerConfig - The configuration key.serializer = class org.apache.kafka.common.serialization.StringSerializer was supplied but isn't a known config.
13:50:05,152 INFO  TransportImpl - connecting to host: 172.16.215.19, port: 3306
13:50:05,195 INFO  TransportImpl - connected to host: 172.16.215.19, port: 3306, context: AbstractTransport.Context[threadId=49317,scramble=MLu+r|;MT>xdlqqf-IiV,protocolVersion=10,serverHost=172.16.215.19,serverPort=3306,serverStatus=2,serverCollation=8,serverVersion=5.5.41-37.0-55-log,serverCapabilities=63487]
13:50:05,195 INFO  AuthenticatorImpl - start to login, user: maxwell, host: 172.16.215.19, port: 3306
13:50:05,201 INFO  AuthenticatorImpl - login successfully, user: maxwell, detail: OKPacket[packetMarker=0,affectedRows=0,insertId=0,serverStatus=2,warningCount=0,message=<null>]
java.io.EOFException
        at com.google.code.or.io.util.ActiveBufferedInputStream.read(ActiveBufferedInputStream.java:177)
        at com.google.code.or.io.impl.XInputStreamImpl.doFill(XInputStreamImpl.java:237)
        at com.google.code.or.io.impl.XInputStreamImpl.read(XInputStreamImpl.java:214)
        at com.google.code.or.net.impl.TransportInputStreamImpl.read(TransportInputStreamImpl.java:88)
        at com.google.code.or.io.impl.XInputStreamImpl.readInt(XInputStreamImpl.java:142)
        at com.google.code.or.io.impl.XInputStreamImpl.readInt(XInputStreamImpl.java:62)
        at com.google.code.or.net.impl.TransportInputStreamImpl.readPacketHeader(TransportInputStreamImpl.java:72)
        at com.google.code.or.net.impl.TransportInputStreamImpl.readPacket(TransportInputStreamImpl.java:57)
        at com.google.code.or.OpenReplicator.dumpBinlog(OpenReplicator.java:319)
        at com.google.code.or.OpenReplicator.start(OpenReplicator.java:111)
        at com.zendesk.maxwell.MaxwellReplicator.beforeStart(MaxwellReplicator.java:100)
        at com.zendesk.maxwell.RunLoopProcess.runLoop(RunLoopProcess.java:25)
        at com.zendesk.maxwell.Maxwell.run(Maxwell.java:76)
        at com.zendesk.maxwell.Maxwell.main(Maxwell.java:82)

We finally could get maxwell started by advancing quite a few binlog sequences, which is of course not desired.
@jsalmeron
Copy link
Author

The contents of the mysql binlog (using the mysqlbinlog utility):

# at 30604
#160629 13:11:27 server id 0  end_log_pos 30749     Query   thread_id=43967 exec_time=0 error_code=0
SET TIMESTAMP=1467198687/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
CREATE TABLE customers(id SERIAL, first_name VARCHAR(255), last_name VARCHAR(255))
/*!*/;

osheroff pushed a commit that referenced this issue Jul 10, 2016
Issue #371 -- SERIAL datatype
e16e390 
SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

because brevity is important, I guess.
osheroff pushed a commit that referenced this issue Jul 12, 2016
Merge pull request #379 from zendesk/gh_371
100428b 
#371 - handle SERIAL datatype properly
osheroff pushed a commit that referenced this issue Jul 14, 2016
v1.1.3, "the button I push to not have to go out"
c19df87 
This is a bugfix release, which fixes:
- #376, a problem parsing
  RENAME INDEX
- #371, a problem with the
  SERIAL datatype
- #362, we now preserve the
  original casing of columns
- #373, we were incorrectly
  expecting heartbeats to work under 5.1
osheroff pushed a commit that referenced this issue Jul 14, 2016
v1.1.3, "the button I push to not have to go out"
4ede35a 
This is a bugfix release, which fixes:
- #376, a problem parsing
  RENAME INDEX
- #371, a problem with the
  SERIAL datatype
- #362, we now preserve the
  original casing of columns
- #373, we were incorrectly
  expecting heartbeats to work under 5.1
@osheroff
Copy link
Collaborator

fixed in https://github.com/zendesk/maxwell/releases/tag/v1.1.3

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@osheroff @jsalmeron