Schema validation fails with same name sequences in different schemas
Description
Using multiple schemas with same name sequences but different parameters. I use a default schema name, but the sequence information extractor retrieves all sequences and database information is initialized with sequences devoid of schema name in the order received from the extractor. So if the result is a sequence from default schema followed by the same sequence from another schema, it will be overwritten with invalid information from another schema.
Then, DatabaseInformationImpl.java populates sequenceInformationMap in order received from executing the above sql, removing schema qualifier and overwriting without any check. source
Activity
Show:
Sela Lerer April 30, 2023 at 12:46 PM
Verified in 5.6.9 too
Alex CD May 29, 2021 at 10:42 PM
Workaround usingcurrentSchema connection url parameter, with custom dialect:
Danas Mikelinskas November 12, 2020 at 9:48 AM
Edited
To avoid breaking something, the list can still be of unqualified names, just prefer default schema over others.
Along the lines of:
BTW. Issue is reproducible on SQLServer2012Dialect (actual DB is SQL Server 2019)
Alex CD June 26, 2020 at 7:24 PM
Edited
Seems that only validation is impacted by this bug.
Should database metadata contain all sequences from all schemas?
ExtractedDatabaseMetaData.getSequenceInformationList() contains all sequences and has one usage where it filters the required schema (valid).
This may produce bugs in the futures if it is used in other locations without filtering the required schema.
Using multiple schemas with same name sequences but different parameters.
I use a default schema name, but the sequence information extractor retrieves all sequences and database information is initialized with sequences devoid of schema name in the order received from the extractor.
So if the result is a sequence from default schema followed by the same sequence from another schema, it will be overwritten with invalid information from another schema.
Extractor uses select * from information_schema.sequences to get all sequences disregarding any default schema configuration.
Then, DatabaseInformationImpl.java populates sequenceInformationMap in order received from executing the above sql, removing schema qualifier and overwriting without any check. source