Possible to create duplicate Avro Schemas in the Schema Registry

[#1]

It appears to be possible to create the same avro schema in the registry. This feels like a bug to me.

Both were create using the Schema Registry UI, not through the REST interfaces.

avro1.txtavro2.txt

HDF 3.0.0.0, Registry 0.3.0

[#2]

@David Streever this is very strange. I just tried in my own installation and couldn't reproduce. If you have the registry still running with this data. Can you please run following sql query against registry database and paste the results here.

select * from schema_metadata_info;

[#3]

Hello. I work with Dave Holtzhouser. I am the system admin that has access to the PostgreSQL database. Here is the results of the sql query you requested above:

id | type | schemaGroup | name | compatibility | description
| evolve | timestamp
----+------+---------------------+---------------------------+---------------+-------------------------------------------------------------------------------------------------------------------
------------+--------+---------------
1 | avro | Kafka | Test | BACKWARD | TEst
| t | 1498680663869
2 | avro | Kafka | RoutingSlip | BACKWARD | An implementation of the Routing Slip EIP (http://www.dummyurl.com/patterns/messaging/Routing
Table.html) | t | 1498756496668
3 | avro | Kafka | RoutingSlip | BACKWARD | An implementation of the Routing Slip EIP (http://www.dummyurl.com/patterns/messaging/Routing
Table.html) | t | 1498756511480
4 | avro | Kafka | EmailAddressMsg | BACKWARD | An email address
| t | 1500669984537
5 | avro | Kafka | EmailMessageMsg | BACKWARD | An email message
| t | 1500670028183
6 | avro | truck-sensors-kafka | raw-truck_events_avro | BACKWARD | Raw Geo events from trucks in Kafka Topic
| t | 1501266679367
7 | avro | Kafka | MMS_Sales_email_dev | BACKWARD | Email Test Schema
| t | 1501269422220
8 | avro | Kafka | MMS_Sales_CarCompany_Emails | BACKWARD | CarCompany Email Topic
| t | 1501281176150

[#4]

@Kirk DeMumbrane

Thanks for the details. In HDF-3.0 release we supported mysql as GA and postgres as TechPreview. This is a an issue with our Postgres create table scripts. You can pickup the latest postgres scripts from here

https://github.com/hortonworks/registry/tree/master/bootstrap/sql/postgresql and run the following commands. These commands will delete any existing data and re-create the tables. Make sure you take the backup of your data.

copy the above postgresql files under here

cp *.sql /usr/hdf/current/registry/bootstrap/sql/postgresql

run the following command
/usr/hdf/current/registry/bootstrap/bootstrap-storage.sh drop-create

[#5]

Hello Sriharsha,
I am trying to run the drop-create command and there appears to be a problem with the create_tables.sql script. Error:
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: multiple primary keys for table "schema_metadata_info" are not allowed
Position: 555
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:303)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:289)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:266)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:262)
at com.hortonworks.registries.storage.tool.SQLScriptRunner.runScript(SQLScriptRunner.java:98)
at com.hortonworks.registries.storage.tool.TablesInitializer.doExecute(TablesInitializer.java:198)
at com.hortonworks.registries.storage.tool.TablesInitializer.doExecuteCreate(TablesInitializer.java:175)
at com.hortonworks.registries.storage.tool.TablesInitializer.main(TablesInitializer.java:162)

The create table command below has id as the serial primary key and also there is a primary key statement at the bottom indicating "name" is a primary key. Can you correct the script with the proper primary key and repost it on GitHub?
CREATE TABLE IF NOT EXISTS schema_metadata_info ( "id" SERIAL PRIMARY KEY, "type" VARCHAR(255) NOT NULL, "schemaGroup" VARCHAR(255) NOT NULL, "name" VARCHAR(255) NOT NULL, "compatibility" VARCHAR(255) NOT NULL, "validationLevel" VARCHAR(255) NOT NULL, -- added in 0.3.1, table should be altered to add this column from earlier versions. "description" TEXT, "evolve" BOOLEAN NOT NULL, "timestamp" BIGINT NOT NULL, UNIQUE ("id"), PRIMARY KEY ( "name") ); Thanks,
Kirk

[#6]

@Kirk DeMumbrane Pushed one more fix into those scripts and tested against postgres. Can you please try one more time getting the latest files from master here

https://github.com/hortonworks/registry/

[#7]

Thanks. I gave it a try. The SQL script now runs correctly. However when I try to create add a new schema I see this error in the registry.log file:

ERROR [13:21:06.461] [dw-27 - POST /api/v1/schemaregistry/schemas] c.h.r.s.w.SchemaRegistryResource - Error encountered while adding schema info [SchemaMetadata{type='avro', schemaGroup='sales-nxt-email', name='test', description='test', compatibility=BACKWARD, evolve=true}]
com.hortonworks.registries.storage.exception.StorageException: org.postgresql.util.PSQLException: ERROR: null value in column "validationLevel" violates not-null constraint
Detail: Failing row contains (2, avro, sales-nxt-email, test, BACKWARD, null, test, t, 1502734866442).
at com.hortonworks.registries.storage.impl.jdbc.provider.sql.factory.AbstractQueryExecutor$QueryExecution.executeUpdate(AbstractQueryExecutor.java:225)
at com.hortonworks.registries.storage.impl.jdbc.provider.sql.factory.AbstractQueryExecutor.executeUpdate(AbstractQueryExecutor.java:182)
at com.hortonworks.registries.storage.impl.jdbc.provider.postgresql.factory.PostgresqlExecutor.insertOrUpdateWithUniqueId(PostgresqlExecutor.java:182)
at com.hortonworks.registries.storage.impl.jdbc.provider.postgresql.factory.PostgresqlExecutor.insert(PostgresqlExecutor.java:80)
at com.hortonworks.registries.storage.impl.jdbc.JdbcStorageManager.add(JdbcStorageManager.java:66)
at com.hortonworks.registries.schemaregistry.DefaultSchemaRegistry.addSchemaMetadata(DefaultSchemaRegistry.java:168)
at com.hortonworks.registries.schemaregistry.webservice.SchemaRegistryResource.lambda$addSchemaInfo$1(SchemaRegistryResource.java:380)
at com.hortonworks.registries.schemaregistry.webservice.SchemaRegistryResource.handleLeaderAction(SchemaRegistryResource.java:158)
at com.hortonworks.registries.schemaregistry.webservice.SchemaRegistryResource.addSchemaInfo(SchemaRegistryResource.java:371)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)

Looking at the create_tables.sql script for the schema_metadata_info table it appears there is a new column called "validationLevel" that is not in the original script that was used to install schema registry. Should this column allow nulls?

--New Script

CREATE TABLE IF NOT EXISTS schema_metadata_info (

"id" SERIAL UNIQUE NOT NULL,

"type" VARCHAR(255) NOT NULL,

"schemaGroup" VARCHAR(255) NOT NULL,

"name" VARCHAR(255) NOT NULL,

"compatibility" VARCHAR(255) NOT NULL,

"validationLevel" VARCHAR(255) NOT NULL, -- added in 0.3.1, table should be altered to add this column from earlier versions.

"description" TEXT,

"evolve" BOOLEAN NOT NULL,

"timestamp" BIGINT NOT NULL,

PRIMARY KEY ( "name"),

UNIQUE ("id")

);

--Script which was originally used to install Schema Registry

CREATE TABLE IF NOT EXISTS schema_metadata_info (
"id" SERIAL PRIMARY KEY,
"type" VARCHAR(256) NOT NULL,
"schemaGroup" VARCHAR(256) NOT NULL,
"name" VARCHAR(256) NOT NULL,
"compatibility" VARCHAR(256) NOT NULL,
"description" TEXT,
"evolve" BOOLEAN NOT NULL,
"timestamp" BIGINT NOT NULL,
UNIQUE("id","name")
);

[#8]

Hello Sriharsha. Has any progress been made on the post that I submitted yesterday? Thanks in advance.

[#9]

@Kirk DeMumbrane saw your comments. Will need to send upgrades scripts against 3.0 release.