Uploaded image for project: 'Debezium'
  1. Debezium
  2. DBZ-6698

Incorrect timestamp conversion

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Not a Bug
    • Icon: Major Major
    • None
    • 2.3.0.Final
    • None
    • None
    • False
    • Hide

      None

      Show
      None
    • False

      What Debezium connector do you use and what version?

      io.debezium.connector.mysql.MySqlConnector 2.3.0.Final

      io.debezium.connector.jdbc.JdbcSinkConnector 2.3.0.Final

      What is the connector configuration?

      {
                  "connector.class": "io.debezium.connector.mysql.MySqlConnector",
                  "database.hostname": "10.27.27.2",
                  "database.port": "3306",
                  "database.user": "user",
                  "database.password": "password",
                  "database.server.id": "423",
                  "database.connectionTimeZone": "UTC",
      	    "snapshot.max.threads": "4",
                  "topic.prefix": "prefix",
                  "database.include.list": "database",
                  "database.history.kafka.bootstrap.servers": "broker:9092",
                  "database.history.kafka.topic": "dbhistory.database" ,
                  "schema.history.internal.kafka.bootstrap.servers": "broker:9092",
                  "schema.history.internal.kafka.topic": "schemahistory.database",
                  "decimal.handling.mode": "precise",
                  "include.schema.changes": "true",
                  "delete.handling.mode": "none",
                  "key.converter": "io.confluent.connect.avro.AvroConverter",
                  "key.converter.schema.registry.url": "http://schema-registry:8081",
                  "key.converter.enhanced.avro.schema.support": "true",
                  "value.converter": "io.confluent.connect.avro.AvroConverter",
                  "value.converter.schema.registry.url": "http://schema-registry:8081",
                  "value.converter.enhanced.avro.schema.support": "true",
                  "connect.meta.data": "true",
                  "producer.override.max.request.size": "104857600",
                  "max.message.bytes": "104857600",
                  "max.request.size": "104857600",
      	    "max.batch.size": "30000",
      	    "max.queue.size": "200000"
          }
      

      What is the captured database version and mode of depoyment?

      Captured database version is Percona Xtradb Cluster 8.0.22

      What behaviour do you see?

      TIMESTAMP column with value '0000-00-00 00:00:00' is being converted to '1970-01-01T00:00:00Z' which is incorrect (out of range).

      Debezium JDBC Sink connector fails trying to insert such data with the following logs:

      JDBC Sink Connector logs

      org.apache.kafka.connect.errors.ConnectException: Exiting WorkerSinkTask due to unrecoverable exception.
      	at org.apache.kafka.connect.runtime.WorkerSinkTask.deliverMessages(WorkerSinkTask.java:614)
      	at org.apache.kafka.connect.runtime.WorkerSinkTask.poll(WorkerSinkTask.java:336)
      	at org.apache.kafka.connect.runtime.WorkerSinkTask.iteration(WorkerSinkTask.java:237)
      	at org.apache.kafka.connect.runtime.WorkerSinkTask.execute(WorkerSinkTask.java:206)
      	at org.apache.kafka.connect.runtime.WorkerTask.doRun(WorkerTask.java:202)
      	at org.apache.kafka.connect.runtime.WorkerTask.run(WorkerTask.java:257)
      	at org.apache.kafka.connect.runtime.isolation.Plugins.lambda$withClassLoader$1(Plugins.java:177)
      	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
      	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
      	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
      	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
      	at java.base/java.lang.Thread.run(Thread.java:829)
      Caused by: org.apache.kafka.connect.errors.ConnectException: JDBC sink connector failure
      	at io.debezium.connector.jdbc.JdbcSinkConnectorTask.put(JdbcSinkConnectorTask.java:80)
      	at org.apache.kafka.connect.runtime.WorkerSinkTask.deliverMessages(WorkerSinkTask.java:583)
      	... 11 more
      Caused by: org.apache.kafka.connect.errors.ConnectException: Failed to process a sink record
      	at io.debezium.connector.jdbc.JdbcChangeEventSink.execute(JdbcChangeEventSink.java:72)
      	at io.debezium.connector.jdbc.JdbcSinkConnectorTask.put(JdbcSinkConnectorTask.java:89)
      	... 12 more
      Caused by: jakarta.persistence.PersistenceException: Converting `org.hibernate.exception.DataException` to JPA `PersistenceException` : JDBC exception executing SQL [INSERT INTO `ice_doctor` (`id`, `spb_id`, `fio`, `birthdate`, `previous_works`, `education`, `additionalEducation`, `awards`, `description`, `show`, `active`, `altUrl`, `clinics`, `User__id`, `createTime`, `updateTime`, `priority`, `rating`, `rating_place`, `comment_votes`, `comment_positive`, `comment_count`, `hide_negative_comments`, `City__id`, `payed`, `filled`, `last_comment_time`, `imageUrl`, `hasImage`, `hidePriceCategoryTitle`, `url`, `priceCategoryTitle`, `postTitle`, `metroJson`, `discountJson`, `Clinic_Price_Category__id`, `discountCreator`, `positon`, `relationPosition`, `email`, `emailValidated`, `workExperience`, `workStartedAt`, `degre`, `category`, `priceOfAdmission`, `clinic_id`, `searchWeight`, `site`, `isModerated`, `phone`, `orderToDoctor`, `isPaid`, `homeVisit`, `candidateOfSciences`, `doctorOfSciences`, `mainDoctor`, `whiteRating`, `hasSchedule`, `hasScheduleUpdatedAt`, `paidWithoutClinic`, `orderabilityStatus`, `indexPageWithoutSlug`, `firstCategory`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE `spb_id`=VALUES(`spb_id`),`fio`=VALUES(`fio`),`birthdate`=VALUES(`birthdate`),`previous_works`=VALUES(`previous_works`),`education`=VALUES(`education`),`additionalEducation`=VALUES(`additionalEducation`),`awards`=VALUES(`awards`),`description`=VALUES(`description`),`show`=VALUES(`show`),`active`=VALUES(`active`),`altUrl`=VALUES(`altUrl`),`clinics`=VALUES(`clinics`),`User__id`=VALUES(`User__id`),`createTime`=VALUES(`createTime`),`updateTime`=VALUES(`updateTime`),`priority`=VALUES(`priority`),`rating`=VALUES(`rating`),`rating_place`=VALUES(`rating_place`),`comment_votes`=VALUES(`comment_votes`),`comment_positive`=VALUES(`comment_positive`),`comment_count`=VALUES(`comment_count`),`hide_negative_comments`=VALUES(`hide_negative_comments`),`City__id`=VALUES(`City__id`),`payed`=VALUES(`payed`),`filled`=VALUES(`filled`),`last_comment_time`=VALUES(`last_comment_time`),`imageUrl`=VALUES(`imageUrl`),`hasImage`=VALUES(`hasImage`),`hidePriceCategoryTitle`=VALUES(`hidePriceCategoryTitle`),`url`=VALUES(`url`),`priceCategoryTitle`=VALUES(`priceCategoryTitle`),`postTitle`=VALUES(`postTitle`),`metroJson`=VALUES(`metroJson`),`discountJson`=VALUES(`discountJson`),`Clinic_Price_Category__id`=VALUES(`Clinic_Price_Category__id`),`discountCreator`=VALUES(`discountCreator`),`positon`=VALUES(`positon`),`relationPosition`=VALUES(`relationPosition`),`email`=VALUES(`email`),`emailValidated`=VALUES(`emailValidated`),`workExperience`=VALUES(`workExperience`),`workStartedAt`=VALUES(`workStartedAt`),`degre`=VALUES(`degre`),`category`=VALUES(`category`),`priceOfAdmission`=VALUES(`priceOfAdmission`),`clinic_id`=VALUES(`clinic_id`),`searchWeight`=VALUES(`searchWeight`),`site`=VALUES(`site`),`isModerated`=VALUES(`isModerated`),`phone`=VALUES(`phone`),`orderToDoctor`=VALUES(`orderToDoctor`),`isPaid`=VALUES(`isPaid`),`homeVisit`=VALUES(`homeVisit`),`candidateOfSciences`=VALUES(`candidateOfSciences`),`doctorOfSciences`=VALUES(`doctorOfSciences`),`mainDoctor`=VALUES(`mainDoctor`),`whiteRating`=VALUES(`whiteRating`),`hasSchedule`=VALUES(`hasSchedule`),`hasScheduleUpdatedAt`=VALUES(`hasScheduleUpdatedAt`),`paidWithoutClinic`=VALUES(`paidWithoutClinic`),`orderabilityStatus`=VALUES(`orderabilityStatus`),`indexPageWithoutSlug`=VALUES(`indexPageWithoutSlug`),`firstCategory`=VALUES(`firstCategory`)]
      	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:165)
      	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:175)
      	at org.hibernate.query.spi.AbstractQuery.executeUpdate(AbstractQuery.java:654)
      	at io.debezium.connector.jdbc.JdbcChangeEventSink.writeUpsert(JdbcChangeEventSink.java:258)
      	at io.debezium.connector.jdbc.JdbcChangeEventSink.write(JdbcChangeEventSink.java:217)
      	at io.debezium.connector.jdbc.JdbcChangeEventSink.execute(JdbcChangeEventSink.java:69)
      	... 13 more
      Caused by: org.hibernate.exception.DataException: JDBC exception executing SQL [INSERT INTO `ice_doctor` (`id`, `spb_id`, `fio`, `birthdate`, `previous_works`, `education`, `additionalEducation`, `awards`, `description`, `show`, `active`, `altUrl`, `clinics`, `User__id`, `createTime`, `updateTime`, `priority`, `rating`, `rating_place`, `comment_votes`, `comment_positive`, `comment_count`, `hide_negative_comments`, `City__id`, `payed`, `filled`, `last_comment_time`, `imageUrl`, `hasImage`, `hidePriceCategoryTitle`, `url`, `priceCategoryTitle`, `postTitle`, `metroJson`, `discountJson`, `Clinic_Price_Category__id`, `discountCreator`, `positon`, `relationPosition`, `email`, `emailValidated`, `workExperience`, `workStartedAt`, `degre`, `category`, `priceOfAdmission`, `clinic_id`, `searchWeight`, `site`, `isModerated`, `phone`, `orderToDoctor`, `isPaid`, `homeVisit`, `candidateOfSciences`, `doctorOfSciences`, `mainDoctor`, `whiteRating`, `hasSchedule`, `hasScheduleUpdatedAt`, `paidWithoutClinic`, `orderabilityStatus`, `indexPageWithoutSlug`, `firstCategory`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE `spb_id`=VALUES(`spb_id`),`fio`=VALUES(`fio`),`birthdate`=VALUES(`birthdate`),`previous_works`=VALUES(`previous_works`),`education`=VALUES(`education`),`additionalEducation`=VALUES(`additionalEducation`),`awards`=VALUES(`awards`),`description`=VALUES(`description`),`show`=VALUES(`show`),`active`=VALUES(`active`),`altUrl`=VALUES(`altUrl`),`clinics`=VALUES(`clinics`),`User__id`=VALUES(`User__id`),`createTime`=VALUES(`createTime`),`updateTime`=VALUES(`updateTime`),`priority`=VALUES(`priority`),`rating`=VALUES(`rating`),`rating_place`=VALUES(`rating_place`),`comment_votes`=VALUES(`comment_votes`),`comment_positive`=VALUES(`comment_positive`),`comment_count`=VALUES(`comment_count`),`hide_negative_comments`=VALUES(`hide_negative_comments`),`City__id`=VALUES(`City__id`),`payed`=VALUES(`payed`),`filled`=VALUES(`filled`),`last_comment_time`=VALUES(`last_comment_time`),`imageUrl`=VALUES(`imageUrl`),`hasImage`=VALUES(`hasImage`),`hidePriceCategoryTitle`=VALUES(`hidePriceCategoryTitle`),`url`=VALUES(`url`),`priceCategoryTitle`=VALUES(`priceCategoryTitle`),`postTitle`=VALUES(`postTitle`),`metroJson`=VALUES(`metroJson`),`discountJson`=VALUES(`discountJson`),`Clinic_Price_Category__id`=VALUES(`Clinic_Price_Category__id`),`discountCreator`=VALUES(`discountCreator`),`positon`=VALUES(`positon`),`relationPosition`=VALUES(`relationPosition`),`email`=VALUES(`email`),`emailValidated`=VALUES(`emailValidated`),`workExperience`=VALUES(`workExperience`),`workStartedAt`=VALUES(`workStartedAt`),`degre`=VALUES(`degre`),`category`=VALUES(`category`),`priceOfAdmission`=VALUES(`priceOfAdmission`),`clinic_id`=VALUES(`clinic_id`),`searchWeight`=VALUES(`searchWeight`),`site`=VALUES(`site`),`isModerated`=VALUES(`isModerated`),`phone`=VALUES(`phone`),`orderToDoctor`=VALUES(`orderToDoctor`),`isPaid`=VALUES(`isPaid`),`homeVisit`=VALUES(`homeVisit`),`candidateOfSciences`=VALUES(`candidateOfSciences`),`doctorOfSciences`=VALUES(`doctorOfSciences`),`mainDoctor`=VALUES(`mainDoctor`),`whiteRating`=VALUES(`whiteRating`),`hasSchedule`=VALUES(`hasSchedule`),`hasScheduleUpdatedAt`=VALUES(`hasScheduleUpdatedAt`),`paidWithoutClinic`=VALUES(`paidWithoutClinic`),`orderabilityStatus`=VALUES(`orderabilityStatus`),`indexPageWithoutSlug`=VALUES(`indexPageWithoutSlug`),`firstCategory`=VALUES(`firstCategory`)]
      	at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:53)
      	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:56)
      	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
      	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
      	at org.hibernate.sql.exec.internal.StandardJdbcMutationExecutor.execute(StandardJdbcMutationExecutor.java:97)
      	at org.hibernate.query.sql.internal.NativeNonSelectQueryPlanImpl.executeUpdate(NativeNonSelectQueryPlanImpl.java:78)
      	at org.hibernate.query.sql.internal.NativeQueryImpl.doExecuteUpdate(NativeQueryImpl.java:820)
      	at org.hibernate.query.spi.AbstractQuery.executeUpdate(AbstractQuery.java:643)
      	... 16 more
      Caused by: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime value: '1970-01-01 00:00:00' for column 'hasScheduleUpdatedAt' at row 1
      	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:104)
      	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
      	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1061)
      	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1009)
      	at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1320)
      	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:994)
      	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:1502)
      	at org.hibernate.sql.exec.internal.StandardJdbcMutationExecutor.execute(StandardJdbcMutationExecutor.java:84)
      	... 19 more
      

       

      Is it expected behavior?

              Unassigned Unassigned
              balbaev Zhan Balbaev (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Created:
                Updated:
                Resolved: