ksql-migrations is not compatible with Cloud Platform Docker image 6.1.1

[AdilHoumadi]

Describe the bug ksql-migrations is not compatible with Cloud Platform Docker image 6.1.1

To Reproduce Steps to reproduce the behavior, include:

1. The version of KSQL. Use the following Docker-compose file: An extra container is added to official stack to have ksql-migrations. (image: confluentinc/ksqldb-cli:0.17.0)

---
version: '2'
services:
  zookeeper:
    image: confluentinc/cp-zookeeper:6.1.1
    hostname: zookeeper
    container_name: zookeeper
    ports:
      - "2181:2181"
    environment:
      ZOOKEEPER_CLIENT_PORT: 2181
      ZOOKEEPER_TICK_TIME: 2000

  broker:
    image: confluentinc/cp-server:6.1.1
    hostname: broker
    container_name: broker
    depends_on:
      - zookeeper
    ports:
      - "9092:9092"
      - "9101:9101"
    environment:
      KAFKA_BROKER_ID: 1
      KAFKA_ZOOKEEPER_CONNECT: 'zookeeper:2181'
      KAFKA_LISTENER_SECURITY_PROTOCOL_MAP: PLAINTEXT:PLAINTEXT,PLAINTEXT_HOST:PLAINTEXT
      KAFKA_ADVERTISED_LISTENERS: PLAINTEXT://broker:29092,PLAINTEXT_HOST://localhost:9092
      KAFKA_METRIC_REPORTERS: io.confluent.metrics.reporter.ConfluentMetricsReporter
      KAFKA_OFFSETS_TOPIC_REPLICATION_FACTOR: 1
      KAFKA_GROUP_INITIAL_REBALANCE_DELAY_MS: 0
      KAFKA_CONFLUENT_LICENSE_TOPIC_REPLICATION_FACTOR: 1
      KAFKA_CONFLUENT_BALANCER_TOPIC_REPLICATION_FACTOR: 1
      KAFKA_TRANSACTION_STATE_LOG_MIN_ISR: 1
      KAFKA_TRANSACTION_STATE_LOG_REPLICATION_FACTOR: 1
      KAFKA_JMX_PORT: 9101
      KAFKA_JMX_HOSTNAME: localhost
      KAFKA_CONFLUENT_SCHEMA_REGISTRY_URL: http://schema-registry:8081
      CONFLUENT_METRICS_REPORTER_BOOTSTRAP_SERVERS: broker:29092
      CONFLUENT_METRICS_REPORTER_TOPIC_REPLICAS: 1
      CONFLUENT_METRICS_ENABLE: 'true'
      CONFLUENT_SUPPORT_CUSTOMER_ID: 'anonymous'

  schema-registry:
    image: confluentinc/cp-schema-registry:6.1.1
    hostname: schema-registry
    container_name: schema-registry
    depends_on:
      - broker
    ports:
      - "8081:8081"
    environment:
      SCHEMA_REGISTRY_HOST_NAME: schema-registry
      SCHEMA_REGISTRY_KAFKASTORE_BOOTSTRAP_SERVERS: 'broker:29092'
      SCHEMA_REGISTRY_LISTENERS: http://0.0.0.0:8081

  connect:
    image: cnfldemos/cp-server-connect-datagen:0.4.0-6.1.0
    hostname: connect
    container_name: connect
    depends_on:
      - broker
      - schema-registry
    ports:
      - "8083:8083"
    environment:
      CONNECT_BOOTSTRAP_SERVERS: 'broker:29092'
      CONNECT_REST_ADVERTISED_HOST_NAME: connect
      CONNECT_REST_PORT: 8083
      CONNECT_GROUP_ID: compose-connect-group
      CONNECT_CONFIG_STORAGE_TOPIC: docker-connect-configs
      CONNECT_CONFIG_STORAGE_REPLICATION_FACTOR: 1
      CONNECT_OFFSET_FLUSH_INTERVAL_MS: 10000
      CONNECT_OFFSET_STORAGE_TOPIC: docker-connect-offsets
      CONNECT_OFFSET_STORAGE_REPLICATION_FACTOR: 1
      CONNECT_STATUS_STORAGE_TOPIC: docker-connect-status
      CONNECT_STATUS_STORAGE_REPLICATION_FACTOR: 1
      CONNECT_KEY_CONVERTER: org.apache.kafka.connect.storage.StringConverter
      CONNECT_VALUE_CONVERTER: io.confluent.connect.avro.AvroConverter
      CONNECT_VALUE_CONVERTER_SCHEMA_REGISTRY_URL: http://schema-registry:8081
      # CLASSPATH required due to CC-2422
      CLASSPATH: /usr/share/java/monitoring-interceptors/monitoring-interceptors-6.1.1.jar
      CONNECT_PRODUCER_INTERCEPTOR_CLASSES: "io.confluent.monitoring.clients.interceptor.MonitoringProducerInterceptor"
      CONNECT_CONSUMER_INTERCEPTOR_CLASSES: "io.confluent.monitoring.clients.interceptor.MonitoringConsumerInterceptor"
      CONNECT_PLUGIN_PATH: "/usr/share/java,/usr/share/confluent-hub-components"
      CONNECT_LOG4J_LOGGERS: org.apache.zookeeper=ERROR,org.I0Itec.zkclient=ERROR,org.reflections=ERROR

  control-center:
    image: confluentinc/cp-enterprise-control-center:6.1.1
    hostname: control-center
    container_name: control-center
    depends_on:
      - broker
      - schema-registry
      - connect
      - ksqldb-server
    ports:
      - "9021:9021"
    environment:
      CONTROL_CENTER_BOOTSTRAP_SERVERS: 'broker:29092'
      CONTROL_CENTER_CONNECT_CLUSTER: 'connect:8083'
      CONTROL_CENTER_KSQL_KSQLDB1_URL: "http://ksqldb-server:8088"
      CONTROL_CENTER_KSQL_KSQLDB1_ADVERTISED_URL: "http://localhost:8088"
      CONTROL_CENTER_SCHEMA_REGISTRY_URL: "http://schema-registry:8081"
      CONTROL_CENTER_REPLICATION_FACTOR: 1
      CONTROL_CENTER_INTERNAL_TOPICS_PARTITIONS: 1
      CONTROL_CENTER_MONITORING_INTERCEPTOR_TOPIC_PARTITIONS: 1
      CONFLUENT_METRICS_TOPIC_REPLICATION: 1
      PORT: 9021

  ksqldb-server:
    image: confluentinc/cp-ksqldb-server:6.1.1
    hostname: ksqldb-server
    container_name: ksqldb-server
    depends_on:
      - broker
      - connect
    ports:
      - "8088:8088"
    environment:
      KSQL_CONFIG_DIR: "/etc/ksql"
      KSQL_BOOTSTRAP_SERVERS: "broker:29092"
      KSQL_HOST_NAME: ksqldb-server
      KSQL_LISTENERS: "http://0.0.0.0:8088"
      KSQL_CACHE_MAX_BYTES_BUFFERING: 0
      KSQL_KSQL_SCHEMA_REGISTRY_URL: "http://schema-registry:8081"
      KSQL_PRODUCER_INTERCEPTOR_CLASSES: "io.confluent.monitoring.clients.interceptor.MonitoringProducerInterceptor"
      KSQL_CONSUMER_INTERCEPTOR_CLASSES: "io.confluent.monitoring.clients.interceptor.MonitoringConsumerInterceptor"
      KSQL_KSQL_CONNECT_URL: "http://connect:8083"
      KSQL_KSQL_LOGGING_PROCESSING_TOPIC_REPLICATION_FACTOR: 1
      KSQL_KSQL_LOGGING_PROCESSING_TOPIC_AUTO_CREATE: 'true'
      KSQL_KSQL_LOGGING_PROCESSING_STREAM_AUTO_CREATE: 'true'

  ksqldb-cli:
    image: confluentinc/cp-ksqldb-cli:6.1.1
    container_name: ksqldb-cli
    depends_on:
      - broker
      - connect
      - ksqldb-server
    entrypoint: /bin/sh
    tty: true

  ksql-datagen:
    image: confluentinc/ksqldb-examples:6.1.1
    hostname: ksql-datagen
    container_name: ksql-datagen
    depends_on:
      - ksqldb-server
      - broker
      - schema-registry
      - connect
    command: "bash -c 'echo Waiting for Kafka to be ready... && \
                       cub kafka-ready -b broker:29092 1 40 && \
                       echo Waiting for Confluent Schema Registry to be ready... && \
                       cub sr-ready schema-registry 8081 40 && \
                       echo Waiting a few seconds for topic creation to finish... && \
                       sleep 11 && \
                       tail -f /dev/null'"
    environment:
      KSQL_CONFIG_DIR: "/etc/ksql"
      STREAMS_BOOTSTRAP_SERVERS: broker:29092
      STREAMS_SCHEMA_REGISTRY_HOST: schema-registry
      STREAMS_SCHEMA_REGISTRY_PORT: 8081

  rest-proxy:
    image: confluentinc/cp-kafka-rest:6.1.1
    depends_on:
      - broker
      - schema-registry
    ports:
      - 8082:8082
    hostname: rest-proxy
    container_name: rest-proxy
    environment:
      KAFKA_REST_HOST_NAME: rest-proxy
      KAFKA_REST_BOOTSTRAP_SERVERS: 'broker:29092'
      KAFKA_REST_LISTENERS: "http://0.0.0.0:8082"
      KAFKA_REST_SCHEMA_REGISTRY_URL: 'http://schema-registry:8081'

  ksqldb-migrations:
    image: confluentinc/ksqldb-cli:0.17.0
    container_name: ksqldb-migrations
    hostname: ksqldb-migrations
    depends_on:
      - broker
      - ksqldb-server
    tty: true
    volumes:
      - "./migrations:/share/migrations"
    command: "sleep infinity"

2. docker-compose up -d
3. docker exec -it ksql-migrations bash
4. ksql-migrations new-project /share/migrations/ http://ksqldb-server:8088

   
   ksql-migrations new-project /share/migrations/ http://ksqldb-server:8088
   OpenJDK 64-Bit Server VM warning: Option UseConcMarkSweepGC was deprecated in version 9.0 and will likely be removed in a future release.
   Creating new migrations project at /share/migrations/
   /share/migrations/ already exists. Skipping directory creation.
   Creating directory: /share/migrations/migrations
   Creating file: /share/migrations/ksql-migrations.properties
   Writing to config file: ksql.server.url=http://ksqldb-server:8088

Migrations metadata configs:

ksql.migrations.stream.name=MIGRATION_EVENTS

ksql.migrations.table.name=MIGRATION_SCHEMA_VERSIONS

ksql.migrations.stream.topic.name=ksql-service-idksql_MIGRATION_EVENTS

ksql.migrations.table.topic.name=ksql-service-idksql_MIGRATION_SCHEMA_VERSIONS

ksql.migrations.topic.replicas=1

TLS configs:

ssl.truststore.location=

ssl.truststore.password=

ssl.keystore.location=

ssl.keystore.password=

ssl.key.password=

ssl.key.alias=

ssl.alpn=false

ssl.verify.host=true

ksqlDB server authentication configs:

ksql.auth.basic.username=

ksql.auth.basic.password=

Migrations project directory created successfully Execution time: 0.0340 seconds

5.  `ksql-migrations --config-file /share/migrations/ksql-migrations.properties initialize-metadata`

ksql-migrations --config-file /share/migrations/ksql-migrations.properties initialize-metadata OpenJDK 64-Bit Server VM warning: Option UseConcMarkSweepGC was deprecated in version 9.0 and will likely be removed in a future release. Initializing migrations metadata Creating stream: MIGRATION_EVENTS Creating table: MIGRATION_SCHEMA_VERSIONS Migrations metadata initialized successfully Execution time: 2.8870 seconds

6.  `ksql-migrations --config-file /share/migrations/ksql-migrations.properties info`

OpenJDK 64-Bit Server VM warning: Option UseConcMarkSweepGC was deprecated in version 9.0 and will likely be removed in a future release. Failed to verify existence of migrations metadata stream 'MIGRATION_EVENTS'. Did you run ksql-migrations initialize-metadata? Error message: java.lang.IllegalStateException: Unexpected server response format. Response: {"@type":"sourceDescription","statementText":"describe MIGRATION_EVENTS;","sourceDescription":{"name":"MIGRATION_EVENTS","windowType":null,"readQueries":[{"queryString":"CREATE TABLE MIGRATION_SCHEMA_VERSIONS WITH (KAFKA_TOPIC='default_ksql_MIGRATION_SCHEMA_VERSIONS', PARTITIONS=1, REPLICAS=1) AS SELECT\n MIGRATION_EVENTS.VERSION_KEY VERSION_KEY,\n LATEST_BY_OFFSET(MIGRATION_EVENTS.VERSION) VERSION,\n LATEST_BY_OFFSET(MIGRATION_EVENTS.NAME) NAME,\n LATEST_BY_OFFSET(MIGRATION_EVENTS.STATE) STATE,\n LATEST_BY_OFFSET(MIGRATION_EVENTS.CHECKSUM) CHECKSUM,\n LATEST_BY_OFFSET(MIGRATION_EVENTS.STARTED_ON) STARTED_ON,\n LATEST_BY_OFFSET(MIGRATION_EVENTS.COMPLETED_ON) COMPLETED_ON,\n LATEST_BY_OFFSET(MIGRATION_EVENTS.PREVIOUS) PREVIOUS,\n LATEST_BY_OFFSET(MIGRATION_EVENTS.ERROR_REASON) ERROR_REASON\nFROM MIGRATION_EVENTS MIGRATION_EVENTS\nGROUP BY MIGRATION_EVENTS.VERSION_KEY\nEMIT CHANGES;","sinks":["MIGRATION_SCHEMA_VERSIONS"],"sinkKafkaTopics":["default_ksql_MIGRATION_SCHEMA_VERSIONS"],"id":"CTAS_MIGRATION_SCHEMA_VERSIONS_3","statusCount":{"RUNNING":1},"queryType":"PERSISTENT","state":"RUNNING"}],"writeQueries":[],"fields":[{"name":"VERSION_KEY","schema":{"type":"STRING","fields":null,"memberSchema":null},"type":"KEY"},{"name":"VERSION","schema":{"type":"STRING","fields":null,"memberSchema":null}},{"name":"NAME","schema":{"type":"STRING","fields":null,"memberSchema":null}},{"name":"STATE","schema":{"type":"STRING","fields":null,"memberSchema":null}},{"name":"CHECKSUM","schema":{"type":"STRING","fields":null,"memberSchema":null}},{"name":"STARTED_ON","schema":{"type":"STRING","fields":null,"memberSchema":null}},{"name":"COMPLETED_ON","schema":{"type":"STRING","fields":null,"memberSchema":null}},{"name":"PREVIOUS","schema":{"type":"STRING","fields":null,"memberSchema":null}},{"name":"ERROR_REASON","schema":{"type":"STRING","fields":null,"memberSchema":null}}],"type":"STREAM","timestamp":"","statistics":"","errorStats":"","extended":false,"keyFormat":"KAFKA","valueFormat":"JSON","topic":"default_ksql_MIGRATION_EVENTS","partitions":0,"replication":0,"statement":"CREATE STREAM MIGRATION_EVENTS (VERSION_KEY STRING KEY, VERSION STRING, NAME STRING, STATE STRING, CHECKSUM STRING, STARTED_ON STRING, COMPLETED_ON STRING, PREVIOUS STRING, ERROR_REASON STRING) WITH (KAFKA_TOPIC='default_ksql_MIGRATION_EVENTS', KEY_FORMAT='KAFKA', PARTITIONS=1, REPLICAS=1, VALUE_FORMAT='JSON');","queryOffsetSummaries":[]},"warnings":[]} Execution time: 0.8370 seconds

**Expected behavior**

We should see the status of the migrations.
Something like the following:

Current migration version: 1

Version | Name | State | Previous Version | Started On | Completed On | Error Reason

1 | Initial setup | MIGRATED | | 2021-03-03 23:47:50.455 PST | 2021-03-03 23:47:50.689 PST | N/A

**Actual behaviour**

According the documentation this tool is compatible with the 6.X.X releases.

The ksql-migrations tool is available with all ksqlDB versions starting from ksqlDB 0.17 or Confluent Platform 6.2. You can use the tool to manage any ksqlDB cluster running version ksqlDB 0.10 (Confluent Platform 6.0) or newer.

The error is provided in the ticket: `Failed to verify existence of migrations metadata stream`

The same error is thrown for 

1. `destroy-metadata`
2. `apply --next`
3. `apply --next --dry-run`
4.  `validate`

I observed a different behaviour using fully ksqlDB 0.17 for the server and the tool (ksql-migrations).
I will do some testing and raise another issue.

Thanks

[jzaralim]

@AdilHoumadi Thank you for catching this, this is a bug! What happened was there was a slight update in one of the API endpoints the 0.15 release (#6545), which we overlooked. This version of the migrations tool is compatible with versions 0.15+, but we will be patching this bug in the 0.20 release.

[AdilHoumadi]

@jzaralim, Thanks for the feedback, looking forward to test it once released!

[OneCricketeer]

Worth mentioning you effectively have two ksqldb-cli images of different versions in the compose file

[AdilHoumadi]

for the testing I took the official docker-compose from this repo: https://github.com/confluentinc/cp-all-in-one/ The confluentinc/cp-ksqldb-cli:6.1.1 does not contain the ksql-migrations bin, this the reason of having this extra confluentinc/ksqldb-cli:0.17.0 (cli)

[OneCricketeer]

Sure, though, they should be interchangeable with cp-ksqldb-cli trailing a few versions behind

[jzaralim]

This is fixed: https://github.com/confluentinc/ksql/pull/7667

[dailytabs]

We rolled out confluentinc/cp-*:6.1.1 before we realized a use case for ksql.

1) Does this mean I have to wait for confluentinc/cp-*:7.x.x images to have ksql-migrations working?

2a) What is the main problem (other than ksql-migration not being on the cp- images)? The `confluentinc/cp-:6.1.1` images are using ksql <0.15, but the non-cp cli's are using >=0.15?

2b) Does that mean I can use confluentinc/ksqldb-cli:0.14.x and the migrations will work with 6.1.1?

Sorry, very confused with all the version renames between ksql and cp-ksql...it's not very friendly.

3) How do I find out which version of ksql a certain cp-* image is using?!? The cp images show 6.1.1 when using ksql command, while the non-cp cli images shows 0.17/0.18/etc.

3a) You say it will be fixed with 0.20, but git tag --contains 8f2d7994 only shows v7.0.0-x. Where does 0.20 come in?

With traditional database migrations, I would run the migrations at boot of my app server. That's not so good with scaling these days.

4) Is spinning up a cli container, running the migration, then shutting it down really the best way to do ksql migrations? (Is there a ksql-migration project? Probably better to ask there, if there is one.)

[jzaralim]

Hi @dailytabs,

1. If you upgrade your server to 6.2, then the currently released version of ksql-migrations should work. Otherwise, yes you will have to wait for 7.0.0 or the independent 0.20.0 ksqlDB release to use the migration tool with a 6.1.1 server.

2a. The ksql-migrations tool was released in 6.2.0 and are only compatible with 6.2.0/0.15.0 and newer servers. 2b. No, the issue is with ksql-migrations and the server versions. ksql-migrations is not included in the 0.14.x images.

3. We have a table here that shows the equivalent cp/standalone versions. 3a. We're in the process of releasing 0.20, so the 0.20 tag hasn't been created yet :)
4. This is repo the right place to ask! We have tutorials here and here as well as an example of an example repo here that demonstrates how using the ksql-migrations tool might look like.

I hope that helps!

[dailytabs]

@jzaralim Yes, that helped tremendously. We settled on upgrading to 6.2 and I was able to get it all running locally, today. Just need to finish debugging an issue with --define and I should have a solid setup.

Thank you!