UX: CSAS returns control to UI before completing, subsequent statement fails with Unable to verify the AVRO schema is compatible with KSQL. Subject not found.

[rmoff]

I want to be able to run a set of KSQL statements, either cut & paste as part of a demo, or as a script passed to KSQL.

I've hit a problem with this:

ksql> CREATE STREAM CUSTOMERS_SRC_REKEY WITH (PARTITIONS=1) AS SELECT * FROM CUSTOMERS_SRC PARTITION BY ID;

 Message
----------------------------
 Stream created and running
----------------------------
ksql> CREATE TABLE CUSTOMERS WITH (KAFKA_TOPIC='CUSTOMERS_SRC_REKEY', VALUE_FORMAT ='AVRO', KEY='ID');
 Unable to verify the AVRO schema is compatible with KSQL. Subject not found.; error code: 40401
ksql> CREATE TABLE CUSTOMERS WITH (KAFKA_TOPIC='CUSTOMERS_SRC_REKEY', VALUE_FORMAT ='AVRO', KEY='ID');
 Unable to verify the AVRO schema is compatible with KSQL. Subject not found.; error code: 40401
ksql> CREATE TABLE CUSTOMERS WITH (KAFKA_TOPIC='CUSTOMERS_SRC_REKEY', VALUE_FORMAT ='AVRO', KEY='ID');

 Message
---------------
 Table created
---------------
ksql>

What I think is happening is that the CSAS results in a new Avro schema, which is not registered in time before the subsequent statement (which requires it) is executed. The second statement has to be manually retried until it succeeds (a few seconds later).

KSQL needs to either (a) wait until the first statement has fully executed or (b) before throwing the error in the second statement, check if there is some pending change that would allow it to complete without error.

[apurvam]

I think the fundamental issue is going to be fixed by #2159

Once we update the CLI to use those changes, it should address this bug.

cc @rodesai @vcrfxia: please cross check if the above is true!

[vcrfxia]

@apurvam yes, this is exactly the sort of bug #2159 is intended to address -- thanks for connecting the dots! Our plan is to, in a follow-up PR, have the CLI by default wait until all previous statements have executed before validating new ones, which should indeed fix this bug.

EDIT: Turns out I misunderstood the bug called out in this issue, and #2159 will not fix this bug. See new response below.

[rmoff]

@vcrfxia how will this work in the situation where there are no new records arriving in the source (in the above example, CUSTOMERS_SRC_REKEY)? From what I've observed (and could be wrong) the topic won't get created and schema registered until at least one record has flowed through.

[vcrfxia]

Consulted with @rodesai and it turns out #2159 will not fix this bug, for the reason brought up by @rmoff . Currently, KSQL does not register Avro schemas for a topic created via a CSAS statement until the first record flows through. #2159 does not change this behavior, and will not fix the bug as a result. To fix the bug, KSQL should create the Avro schema as part of acknowledging the CSAS statement, rather than waiting for the first record to flow through and having the serializer create the schema.

cc @apurvam

[apurvam]

ok. Thanks for the follow up @vcrfxia . We can leave this open then.

[rmoff]

Thanks @vcrfxia , @apurvam.

For anyone encountering this issue, a workaround I use for my demos is to include a SELECT … LIMIT 1 after the CSAS, and before the CT, which makes KSQL wait until it gets a record before continuing e.g.

SET 'auto.offset.reset' = 'earliest';
CREATE STREAM ACCOUNTS_STREAM WITH (KAFKA_TOPIC='asgard.demo.accounts', VALUE_FORMAT='AVRO');
CREATE STREAM ACCOUNTS_REKEYED WITH (PARTITIONS=1) AS SELECT * FROM ACCOUNTS_STREAM PARTITION BY ACCOUNT_ID;
-- This select statement is simply to make sure that we have time for the ACCOUNTS_REKEYED topic
-- to be created before we define a table against it
SELECT * FROM ACCOUNTS_REKEYED LIMIT 1;
CREATE TABLE ACCOUNTS WITH (KAFKA_TOPIC='ACCOUNTS_REKEYED',VALUE_FORMAT='AVRO',KEY='ACCOUNT_ID');

[rmoff]

To fix the bug, KSQL should create the Avro schema as part of acknowledging the CSAS statement, rather than waiting for the first record to flow through and having the serializer create the schema.

@vcrfxia is this on the roadmap? Is the only workaround, for now, to explicitly specify the Avro schema in the dependent CT statement?

[vcrfxia]

I'm not aware of it being on the immediate roadmap, but @MichaelDrogalis or @apurvam can answer definitively.

Yes, as you mentioned, one workaround is to specify the schema in the CT statement, rather than rely on schema inference. Another workaround (for the CLI) is to follow the CSAS with a SELECT ... LIMIT 1 on the stream as you mentioned in #2880. I'm not aware of any other workarounds at the moment.

[MichaelDrogalis]

We don't have this one on the roadmap right now, unfortunately.

[ivangfr]

I am facing the same problem since I migrated from version 5.4.1 to 5.5.1. Here is the project (https://github.com/ivangfr/springboot-kafka-debezium-ksql/tree/project-update-migration-ksqldb). The migration changes and some adjustments are in the branch project-update-migration-ksqldb, not in master yet.

Besides, the workaround proposed by @rmoff doesn't work for me. Here is the script: https://github.com/ivangfr/springboot-kafka-debezium-ksql/blob/project-update-migration-ksqldb/docker/ksql/researchers-institutes.ksql

For now, the only way it works is by running each CREATE STREAM and CREATE TABLE statement individually in ksqlDB-cli terminal.

---

Update

Btw, I am not facing anymore the problem describe above about running a script inside ksqlDB. Everything looks ok now in my project.

[rmoff]

@vcrfxia should this now be fixed, based on #4219 ?
@krisajenkins hit this error again today (running Confluent Platform 7.0.0), and I knew I'd seen it somewhere before :)

[vcrfxia]

Hey @rmoff @krisajenkins , are you submitting the two statements (where the latter depends on the former) as two separate requests through the UI or as a single request? I would not expect them to work together as a single request for a reason similar to #4800. If you're submitting them separately and it's still not working I'd be curious to know:

• is ksql registering a schema with Schema Registry when the first CREATE STREAM command is issued?
• are there any logs (most likely in Schema Registry, potentially also forwarded to ksql) suggesting that ksql tried to register a schema but encountered errors?
• does the second statement work after waiting a while, i.e., is it an issue of async propagation?
• can you share your exact statements for reproducing the issue for further investigation?

[rmoff]

@vcrfxia Running it as a single request via RUN

[colinhicks]

Adding workflow labels while we assess whether this can be addressed at the same time as #4800