Confluent Kafka Connect - JdbcSourceTask: java.sql.SQLException: Java heap space

Question

I am trying to use mode timestamp with mysql, but it does not create any topic in my kafka queue when I do so and there is also no error log.

Here are the connector properties that I am using,

{
        "name": "jdbc_source_mysql_reqistrations_local",
        "config": {
                 "connector.class": "io.confluent.connect.jdbc.JdbcSourceConnector",
                 "key.converter": "io.confluent.connect.avro.AvroConverter",
                 "key.converter.schema.registry.url": "http://localhost:8081",
                 "value.converter": "io.confluent.connect.avro.AvroConverter",
                 "value.converter.schema.registry.url": "http://localhost:8081",
                 "tasks.max": "5",
                 "connection.url": "jdbc:mysql://localhost:3306/prokafka?zeroDateTimeBehavior=ROUND&user=kotesh&password=kotesh",
                 "poll.interval.ms":"100000000",
                 "query": "SELECT Language, matriid, DateUpdated from usersdata.user",
                 "mode": "timestamp",
                 "timestamp.column.name": "DateUpdated",
                 "validate.non.null": "false",
                 "batch.max.rows":"10",
                 "topic.prefix": "mysql-local-"
        }
}

Launch:

./bin/confluent load jdbc_source_mysql_registration_local -d /home/prokafka/config-json/kafka-connect-jdbc-local-mysql.json



{
  "name": "jdbc_source_mysql_reqistrations_local",
  "config": {
    "connector.class": "io.confluent.connect.jdbc.JdbcSourceConnector",
    "key.converter": "io.confluent.connect.avro.AvroConverter",
    "key.converter.schema.registry.url": "http://localhost:8081",
    "value.converter": "io.confluent.connect.avro.AvroConverter",
    "value.converter.schema.registry.url": "http://localhost:8081",
    "tasks.max": "5",
    "connection.url": "jdbc:mysql://localhost:3306/prokafka?zeroDateTimeBehavior=ROUND&user=kotesh&password=kotesh",
    "poll.interval.ms": "100000000",
    "query": "SELECT Language, matriid, DateUpdated from usersdata.users",
    "mode": "timestamp",
    "timestamp.column.name": "DateUpdated",
    "validate.non.null": "false",
    "batch.max.rows": "10",
    "topic.prefix": "mysql-local-",
    "name": "jdbc_source_mysql_reqistrations_local"
  },
  "tasks": [
    {
      "connector": "jdbc_source_mysql_reqistrations_local",
      "task": 0
    }
  ],
  "type": null
}

@kotesh does it work if you just specify `table.whitelist` instead of `query`? If that does, then you can isolate the problem to `query`. If not, it's elsewhere :) — Robin Moffatt, Nov 26 '18 at 14:47
Do you have auto topic creation disabled in Kafka? If so, then connect won't create the topics for data to be sent to it. It would also help if you could show a [mcve] with example database data so we can reproduce the issue — OneCricketeer, Nov 26 '18 at 18:12
@GiorgosMyrianthous both should be same and it is data base name(usersdata) — kotesh, Nov 27 '18 at 10:06
@RobinMoffatt While using "table.whitelist": "users". Iam getting ERROR as [2018-11-27 15:21:04,423] ERROR Failed to run query for table TimestampIncrementingTableQuerier{table="usersdata"."users", query='null', topicPrefix='mysql-registrations', incrementingColumn='', timestampColumns=[DateUpdated]}: {} (io.confluent.connect.jdbc.source.JdbcSourceTask:328) java.sql.SQLException: Java heap space at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129). Table size 2.6 GB,No of rows 2994498,No of columns 124,max_allowed_packet 1073741824 — kotesh, Nov 27 '18 at 10:20
@cricket_007 yes,Please find table details. Table size 2.6 GB,No of rows 2994498,No of columns 124,max_allowed_packet 1073741824 — kotesh, Nov 27 '18 at 10:24
Can you please [edit] your question to include these details, and format them, rather than as comments? And that is not what I meant by "example data", but rather actual database column values, for example, `SELECT * LIMIT 10` and a `SHOW CREATE TABLE`... — OneCricketeer, Nov 27 '18 at 15:51
@cricket_007 ERROR Failed to run query for table TimestampIncrementingTableQuerier{table=null,query='SELECT matriid from usersdata.users limit 10',topicPrefix='mysql-prod-',incrementingColumn='', timestampColumns=[DateUpdated]}:{} (io.confluent.connect.jdbc.source.JdbcSourceTask:328) java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `DateUpdated` > '1970-01-01 00:00:00.0' at line 1 at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) — kotesh, Nov 29 '18 at 09:01
No... Don't put the LIMIT actually in Connect. I meant to literally run that against the database yourself and [edit] your question to include the results — OneCricketeer, Nov 29 '18 at 16:22

score 1 · Answer 1 · answered Nov 27 '18 at 15:58

SQLException: Java heap space

Seems like you're loading too much data for Connect to handle, and must increase your heap size

For example, increase it to 6GB (or more)

I've not tried using Confluent CLI to do this, but according to the code, this might work

confluent stop connect 
export CONNECT_KAFKA_HEAP_OPTS="-Xmx6g"
confluent start connect

If you're limited in memory on this machine, then run Connect separately from your Mysql database, Kafka broker(s), Zookeeper, Schema Registry, etc.

Confluent Kafka Connect - JdbcSourceTask: java.sql.SQLException: Java heap space

1 Answers1