Error configuring an instance of SqlServerConnectorTask
5,077 views
Skip to first unread message
Max Li
Apr 16, 2019, 1:28:16 PM4/16/19
to debezium
Hi All, please help me on this,
env: confluent5.12
plugins: copy SQLServer debezium 0.94 connector to share/java
run curl -s houbiap5:18083/connectors/mssql-newtest2/status | jq, got the following:
{
"name": "mssql-newtest2",
"connector": {
"state": "RUNNING",
"worker_id": "10.154.54.83:18083"
},
"tasks": [],
"type": "source"
}
from log file,
org.apache.kafka.connect.errors.ConnectException: Error configuring an instance of SqlServerConnectorTask; check the logs for details
at io.debezium.connector.common.BaseSourceTask.start(BaseSourceTask.java:39)
at org.apache.kafka.connect.runtime.WorkerSourceTask.execute(WorkerSourceTask.java:199)
at org.apache.kafka.connect.runtime.WorkerTask.doRun(WorkerTask.java:175)
at org.apache.kafka.connect.runtime.WorkerTask.run(WorkerTask.java:219)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
[2019-04-16 13:14:18,149] ERROR WorkerSourceTask{id=mssql-newtest2-0} Task is being killed and will not recover until manually restarted (org.apache.kafka.connect.runtime.WorkerTask:178)
[2019-04-16 13:14:18,150] INFO Connector has already been stopped (io.debezium.connector.sqlserver.SqlServerConnectorTask:185)
[2019-04-16 13:14:18,150] INFO [Producer clientId=producer-4] Closing the Kafka producer with timeoutMillis = 30000 ms. (org.apache.kafka.clients.producer.KafkaProducer:1139)
the log file is attached
Gunnar Morling
Apr 16, 2019, 2:46:48 PM4/16/19
to debe...@googlegroups.com
Seems like your connector config is incomplete/invalid as per these
errors from your log:
[2019-04-16 13:14:18,139] ERROR The 'database.server.name' value is
invalid: Must not have the same value as database.history.kafka.topic
(io.debezium.connector.common.BaseSourceTask:1841)
[2019-04-16 13:14:18,139] ERROR The 'database.server.name' value is
invalid: A value is required
(io.debezium.connector.common.BaseSourceTask:1841)
[2019-04-16 13:14:18,139] ERROR The 'database.server.name' value is
invalid: A value is required
(io.debezium.connector.common.BaseSourceTask:1841)
[2019-04-16 13:14:18,139] ERROR The 'database.dbname' value is
invalid: A value is required
(io.debezium.connector.common.BaseSourceTask:1841)
--Gunnar
> --
> You received this message because you are subscribed to the Google Groups "debezium" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
> To post to this group, send email to debe...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/cbb1a857-6269-4797-9c9f-477804081085%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
errors from your log:
[2019-04-16 13:14:18,139] ERROR The 'database.server.name' value is
invalid: Must not have the same value as database.history.kafka.topic
(io.debezium.connector.common.BaseSourceTask:1841)
[2019-04-16 13:14:18,139] ERROR The 'database.server.name' value is
invalid: A value is required
(io.debezium.connector.common.BaseSourceTask:1841)
[2019-04-16 13:14:18,139] ERROR The 'database.server.name' value is
invalid: A value is required
(io.debezium.connector.common.BaseSourceTask:1841)
[2019-04-16 13:14:18,139] ERROR The 'database.dbname' value is
invalid: A value is required
(io.debezium.connector.common.BaseSourceTask:1841)
--Gunnar
> You received this message because you are subscribed to the Google Groups "debezium" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
> To post to this group, send email to debe...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/cbb1a857-6269-4797-9c9f-477804081085%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
Max Li
Apr 16, 2019, 4:23:40 PM4/16/19
to debezium
Thank you very much, Gunnar.
I rerun the connector by using the following,
[oracle@houbiap5 confluent5]$ curl -s -X POST -H "Content-Type: application/json" --data '{
> "name": "newtest2",
> "config": {
> "connector.class": "io.debezium.connector.sqlserver.SqlServerConnector",
> "database.hostname": "10.154.54.83",
> "database.port": "1433",
> "database.user": "sql_testdb",
> "database.password": "silver7",
> "database.dbname": "db_encompass",
> "table.whitelist": "newtest2",
> "database.history.kafka.bootstrap.servers": "kafka:9092",
> "database.history.kafka.topic": "mssql-newtest2"
> }
{
"name": "newtest2",
"config": {
"connector.class": "io.debezium.connector.sqlserver.SqlServerConnector",
"database.hostname": "10.154.54.83",
"database.port": "1433",
"database.user": "sql_testdb",
"database.password": "silver7",
"database.dbname": "db_encompass",
"table.whitelist": "newtest2",
"database.history.kafka.bootstrap.servers": "kafka:9092",
"database.history.kafka.topic": "mssql-newtest2",
"name": "newtest2"
},
"tasks": [],
"type": "source"
}
now check the status
"connector": {
"state": "RUNNING",
"worker_id": "10.154.54.83:18083"
},
"tasks": [],
"type": "source"
}
check the log file, got different errors,
[2019-04-16 16:11:01,402] ERROR WorkerSourceTask{id=newtest2-0} Task threw an uncaught and unrecoverable exception (org.apache.kafka.connect.runtime.WorkerTask:177)
java.lang.RuntimeException: Couldn't obtain database name
at io.debezium.connector.sqlserver.SqlServerConnection.retrieveRealDatabaseName(SqlServerConnection.java:364)
at io.debezium.connector.sqlserver.SqlServerConnection.<init>(SqlServerConnection.java:84)
at io.debezium.connector.sqlserver.SqlServerConnectorTask.start(SqlServerConnectorTask.java:86)
at io.debezium.connector.common.BaseSourceTask.start(BaseSourceTask.java:49)
at org.apache.kafka.connect.runtime.WorkerSourceTask.execute(WorkerSourceTask.java:199)
at org.apache.kafka.connect.runtime.WorkerTask.doRun(WorkerTask.java:175)
at org.apache.kafka.connect.runtime.WorkerTask.run(WorkerTask.java:219)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 10.154.54.83, port 1433 has failed. Error: "Connection refused. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:228)
if I use jdbc driver as the following, there is no issue to sync data from sql server db to kafka but cannot do CDC, that's reason I am using debezium
cat source-sqlserver-newtable1.properties
name=source-sqlserver-newtable1
connector.class=io.confluent.connect.jdbc.JdbcSourceConnector
tasks.max=1
topic.prefix=mssql-
connection.url=jdbc:sqlserver://houncadb5.oiiad.com:1433;database=DBname;username=username;password=yourpassword;
mode=timestamp
timestamp.column.name=datemodified
table.whitelist=newtable1
validate.non.null=false
I attached the new log file, please advise, thanks a lot!
Max Li
Apr 16, 2019, 4:50:42 PM4/16/19
to debezium
Hi Gunar,
I corrected the dbname and kafka server name but still got the following,
curl -s houbiap5:18083/connectors/newtest2/status | jq
{
"error_code": 404,
"message": "No status found for connector newtest2"
}
the log file is attached, please help, thanks a lot
Jiri Pechanec
Apr 16, 2019, 11:39:26 PM4/16/19
to debezium
Hi,
from the logs it seems that the connector has to be started. Can you list all connectors form the Connect instance?
Also is the IP in database.hostname correct? Should not be there a DNS name?
database.server.name is a logical name so it should be something like `db1`. `table.whitelist` would be then soemthing like `db1.dbo.my_table_to_monitor`
J.
Max Li
Apr 17, 2019, 1:43:25 PM4/17/19
to debezium
Thank you, J,
here is the latest update
I clean everything then restarted from the scratch,
1. run bin/confluent start
2. bin/connect-distributed -daemon etc/kafka/connect-distributed.properties
3. run the following connector, IP is correct for SQL server(windows server), database.server.name is a logical name , so it can be any unique name, is it correct?
curl -s -X POST -H "Content-Type: application/json" --data '{
"name": "connector-newtest2",
"config": {
"connector.class": "io.debezium.connector.sqlserver.SqlServerConnector",
"database.hostname": "10.154.51.196",
"database.port": "1433",
"database.user": "sql_testdb",
"database.password": "silver7",
"database.dbname": "db_encompass",
"database.server.name": "houncadb5",
"table.whitelist": "dbo.newtest2",
"database.history.kafka.bootstrap.servers": "localhost:9092",
"database.history.kafka.topic": "mssql-newtest2"
}
4. list all connectors
[oracle@houbiap5 kafka-rest]$ curl -s houbiap5.oii.oceaneering.com:8083/connectors/ | jq [
"connector-newtest2"
]
[oracle@houbiap5 kafka-rest]$ curl -s houbiap5.oii.oceaneering.com:18083/connectors/ | jq
[
"connector-newtest2"
]
5. check status
[oracle@houbiap5 kafka-rest]$ curl -s houbiap5.oii.oceaneering.com:18083/connectors/connector-newtest2/status | jq
{
"error_code": 404,
"message": "No status found for connector connector-newtest2"
}
[oracle@houbiap5 kafka-rest]$ curl -s houbiap5.oii.oceaneering.com:8083/connectors/connector-newtest2/status | jq
{
"name": "connector-newtest2",
"connector": {
"state": "RUNNING",
"worker_id": "10.154.54.83:8083"
},
"tasks": [],
"type": "source"
}
6. I saw the topic is created
7. but no data is available on kafka
[oracle@houbiap5 confluent5]$ bin/kafka-console-consumer --bootstrap-server houbiap5.oii.oceaneering.com:9092 --topic mssql-newtest2 --from-beginning
{
"source" : {
"server" : "houncadb5"
},
"position" : {
"commit_lsn" : "0008aeec:00078881:0001",
"change_lsn" : "NULL"
},
"databaseName" : "db_encompass",
"schemaName" : "dbo",
"tableChanges" : [ {
"type" : "CREATE",
"id" : "\"db_encompass\".\"dbo\".\"newtest2\"",
"table" : {
"defaultCharsetName" : null,
"primaryKeyColumnNames" : [ "clientid" ],
"columns" : [ {
"name" : "clientid",
"jdbcType" : 4,
"typeName" : "int identity",
"typeExpression" : "int identity",
"charsetName" : null,
"length" : 10,
"scale" : 0,
"position" : 1,
"optional" : false,
"autoIncremented" : false,
"generated" : false
}, {
"name" : "peoplesoftclientid",
"jdbcType" : -9,
"typeName" : "nvarchar",
"typeExpression" : "nvarchar",
"charsetName" : null,
"length" : 30,
"position" : 2,
"optional" : false,
"autoIncremented" : false,
"generated" : false
}, {
"name" : "address",
"jdbcType" : -9,
"typeName" : "nvarchar",
"typeExpression" : "nvarchar",
"charsetName" : null,
"length" : 255,
"position" : 3,
"optional" : true,
"autoIncremented" : false,
"generated" : false
}, {
"name" : "datecreated",
"jdbcType" : 93,
"typeName" : "datetime",
"typeExpression" : "datetime",
"charsetName" : null,
"length" : 23,
"scale" : 3,
"position" : 4,
"optional" : true,
"autoIncremented" : false,
"generated" : false
}, {
"name" : "datemodified",
"jdbcType" : 93,
"typeName" : "datetime",
"typeExpression" : "datetime",
"charsetName" : null,
"length" : 23,
"scale" : 3,
"position" : 5,
"optional" : true,
"autoIncremented" : false,
"generated" : false
} ]
}
} ]
}
8. check all logs, attached all logs, no ERROR except the following, which can be ignored
[oracle@houbiap5 kafka]$ cat kafka.stdout |grep ERROR
[2019-04-17 13:02:06,904] ERROR Could not submit metrics to Confluent: Connect to support-metrics.confluent.io:443 [support-metrics.confluent.io/35.161.94.40, support-metrics.confluent.io/34.213.115.148] failed: connect timed out (io.confluent.support.metrics.utils.WebClient)
[2019-04-17 13:02:06,904] ERROR Failed to submit metrics via secure endpoint, falling back to insecure endpoint (io.confluent.support.metrics.submitters.ConfluentSubmitter)
[2019-04-17 13:02:10,912] ERROR Could not submit metrics to Confluent: Connect to support-metrics.confluent.io:80 [support-metrics.confluent.io/35.161.94.40, support-metrics.confluent.io/34.213.115.148] failed: connect timed out (io.confluent.support.metrics.utils.WebClient)
9, please help, thanks a lot in advanced
Jiri Pechanec
Apr 18, 2019, 2:24:59 AM4/18/19
to debezium
Hi,
have you also enabled CDC for database and table you want to monitor? - see https://debezium.io/docs/connectors/sqlserver/#setting-up-sqlserver
The table.whitelist is still wrong - it is three component value, in case of your example it should be houncadb5.dbo.newtest2
J.
Jiri Pechanec
Apr 18, 2019, 2:36:19 AM4/18/19
to debezium
Please disregard my note regarding the three component table name - I mixed it with a different connector.
J.
Reply all
Reply to author
Forward
0 new messages