liquibase Incorrect syntax exception with generate-changelog if using single quotes in table names in a database with many tables with SQL Server

Incorrect syntax exception with generate-changelog if using single quotes in table names in a database with many tables with SQL Server

Open NetherGranite opened this issue 3 months ago • 1 comments

Search first

[X] I searched and no similar issues were found

Description

This is potentially a revival of #3825. Having a single quote in a table name can cause Liquibase to throw under odd circumstances.

Steps To Reproduce

Create a SQL Server database with around 100 tables under a default schema dbo, one of which is named something like [Table_Name']
Run generate-changelog on the database
Get the exception Unexpected error running Liquibase: liquibase.exception.DatabaseException: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'dbo'.
Run generate-changelog on the database but exclude the table via exclude-objects
Successfully generate a script

Expected/Desired Behavior

generate-changelog should not throw when a table has a single quote in its name and certain circumstances are met.

Liquibase Version

4.27.0

Database Vendor & Version

Microsoft SQL Server 16.00.1050

Liquibase Integration

CLI

Liquibase Extensions

No response

OS and/or Infrastructure Type/Provider

Windows 11

Additional Context

If I omit enough tables, the exception stops occurring even though the table with the single quote in its name remains. Unfortunately I have too many tables in my database to narrow down whether there's a specific conflict.

Stack trace in logs:

[2024-05-14 08:43:13] INFO [liquibase.command] Logging exception.
ERROR: Exception Details
[2024-05-14 08:43:13] INFO [liquibase.ui] ERROR: Exception Details
ERROR: Exception Primary Class:  SQLServerException
[2024-05-14 08:43:13] INFO [liquibase.ui] ERROR: Exception Primary Class:  SQLServerException
ERROR: Exception Primary Reason:  Incorrect syntax near 'dbo'.
[2024-05-14 08:43:13] INFO [liquibase.ui] ERROR: Exception Primary Reason:  Incorrect syntax near 'dbo'.
ERROR: Exception Primary Source:  4.27.0
[2024-05-14 08:43:13] INFO [liquibase.ui] ERROR: Exception Primary Source:  4.27.0
[2024-05-14 08:43:13] INFO [liquibase.command] Command execution complete
[2024-05-14 08:43:13] SEVERE [liquibase.integration] Incorrect syntax near 'dbo'.
liquibase.exception.CommandExecutionException: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'dbo'.
        at liquibase.command.CommandScope.execute(CommandScope.java:257)
        at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:55)
        at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:24)
        at picocli.CommandLine.executeUserObject(CommandLine.java:2041)
        at picocli.CommandLine.access$1500(CommandLine.java:148)
        at picocli.CommandLine$RunLast.executeUserObjectOfLastSubcommandWithSameParent(CommandLine.java:2461)
        at picocli.CommandLine$RunLast.handle(CommandLine.java:2453)
        at picocli.CommandLine$RunLast.handle(CommandLine.java:2415)
        at picocli.CommandLine$AbstractParseResultHandler.execute(CommandLine.java:2273)
        at picocli.CommandLine$RunLast.execute(CommandLine.java:2417)
        at picocli.CommandLine.execute(CommandLine.java:2170)
        at liquibase.integration.commandline.LiquibaseCommandLine.lambda$execute$2(LiquibaseCommandLine.java:395)
        at liquibase.Scope.child(Scope.java:199)
        at liquibase.Scope.child(Scope.java:175)
        at liquibase.integration.commandline.LiquibaseCommandLine.lambda$execute$3(LiquibaseCommandLine.java:370)
        at liquibase.Scope.child(Scope.java:199)
        at liquibase.Scope.child(Scope.java:175)
        at liquibase.integration.commandline.LiquibaseCommandLine.execute(LiquibaseCommandLine.java:367)
        at liquibase.integration.commandline.LiquibaseCommandLine.main(LiquibaseCommandLine.java:104)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:566)
        at liquibase.integration.commandline.LiquibaseLauncher.main(LiquibaseLauncher.java:116)
Caused by: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'dbo'.
        at liquibase.snapshot.jvm.ForeignKeySnapshotGenerator.addTo(ForeignKeySnapshotGenerator.java:75)
        at liquibase.snapshot.jvm.JdbcSnapshotGenerator.snapshot(JdbcSnapshotGenerator.java:78)
        at liquibase.snapshot.SnapshotGeneratorChain.snapshot(SnapshotGeneratorChain.java:49)
        at liquibase.snapshot.jvm.JdbcSnapshotGenerator.snapshot(JdbcSnapshotGenerator.java:68)
        at liquibase.snapshot.SnapshotGeneratorChain.snapshot(SnapshotGeneratorChain.java:49)
        at liquibase.snapshot.jvm.JdbcSnapshotGenerator.snapshot(JdbcSnapshotGenerator.java:68)
        at liquibase.snapshot.SnapshotGeneratorChain.snapshot(SnapshotGeneratorChain.java:49)
        at liquibase.snapshot.DatabaseSnapshot.include(DatabaseSnapshot.java:313)
        at liquibase.snapshot.DatabaseSnapshot.replaceObject(DatabaseSnapshot.java:456)
        at liquibase.snapshot.DatabaseSnapshot.replaceObject(DatabaseSnapshot.java:478)
        at liquibase.snapshot.DatabaseSnapshot.includeNestedObjects(DatabaseSnapshot.java:369)
        at liquibase.snapshot.DatabaseSnapshot.include(DatabaseSnapshot.java:332)
        at liquibase.snapshot.DatabaseSnapshot.init(DatabaseSnapshot.java:106)
        at liquibase.snapshot.DatabaseSnapshot.<init>(DatabaseSnapshot.java:59)
        at liquibase.snapshot.JdbcDatabaseSnapshot.<init>(JdbcDatabaseSnapshot.java:38)
        at liquibase.snapshot.SnapshotGeneratorFactory.createSnapshot(SnapshotGeneratorFactory.java:214)
        at liquibase.snapshot.SnapshotGeneratorFactory.createSnapshot(SnapshotGeneratorFactory.java:187)
        at liquibase.command.core.DiffCommandStep.generateDatabaseShapshot(DiffCommandStep.java:205)
        at liquibase.command.core.DiffCommandStep.createReferenceSnapshot(DiffCommandStep.java:192)
        at liquibase.command.core.DiffCommandStep.createDiffResult(DiffCommandStep.java:113)
        at liquibase.command.core.DiffCommandStep.run(DiffCommandStep.java:89)
        at liquibase.command.CommandScope.execute(CommandScope.java:219)
        ... 23 more
Caused by: liquibase.exception.DatabaseException: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'dbo'.
        at liquibase.snapshot.ResultSetCache.get(ResultSetCache.java:98)
        at liquibase.snapshot.JdbcDatabaseSnapshot$CachingDatabaseMetaData.getForeignKeys(JdbcDatabaseSnapshot.java:77)
        at liquibase.snapshot.jvm.ForeignKeySnapshotGenerator.addTo(ForeignKeySnapshotGenerator.java:64)
        ... 44 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'dbo'.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:261)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1752)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:675)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:594)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7739)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4384)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:293)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:263)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:509)
        at liquibase.snapshot.ResultSetCache$ResultSetExtractor.executeAndExtract(ResultSetCache.java:249)
        at liquibase.snapshot.ResultSetCache$ResultSetExtractor.executeAndExtract(ResultSetCache.java:229)
        at liquibase.snapshot.JdbcDatabaseSnapshot$CachingDatabaseMetaData$ForeignKeysResultSetCache.bulkFetch(JdbcDatabaseSnapshot.java:877)
        at liquibase.snapshot.ResultSetCache.get(ResultSetCache.java:58)
        ... 46 more


Unexpected error running Liquibase: Incorrect syntax near 'dbo'.

liquibase.exception.CommandExecutionException: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'dbo'.
        at liquibase.command.CommandScope.execute(CommandScope.java:257)
        at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:55)
        at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:24)
        at picocli.CommandLine.executeUserObject(CommandLine.java:2041)
        at picocli.CommandLine.access$1500(CommandLine.java:148)
        at picocli.CommandLine$RunLast.executeUserObjectOfLastSubcommandWithSameParent(CommandLine.java:2461)
        at picocli.CommandLine$RunLast.handle(CommandLine.java:2453)
        at picocli.CommandLine$RunLast.handle(CommandLine.java:2415)
        at picocli.CommandLine$AbstractParseResultHandler.execute(CommandLine.java:2273)
        at picocli.CommandLine$RunLast.execute(CommandLine.java:2417)
        at picocli.CommandLine.execute(CommandLine.java:2170)
        at liquibase.integration.commandline.LiquibaseCommandLine.lambda$execute$2(LiquibaseCommandLine.java:395)
        at liquibase.Scope.child(Scope.java:199)
        at liquibase.Scope.child(Scope.java:175)
        at liquibase.integration.commandline.LiquibaseCommandLine.lambda$execute$3(LiquibaseCommandLine.java:370)
        at liquibase.Scope.child(Scope.java:199)
        at liquibase.Scope.child(Scope.java:175)
        at liquibase.integration.commandline.LiquibaseCommandLine.execute(LiquibaseCommandLine.java:367)
        at liquibase.integration.commandline.LiquibaseCommandLine.main(LiquibaseCommandLine.java:104)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:566)
        at liquibase.integration.commandline.LiquibaseLauncher.main(LiquibaseLauncher.java:116)
Caused by: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'dbo'.
        at liquibase.snapshot.jvm.ForeignKeySnapshotGenerator.addTo(ForeignKeySnapshotGenerator.java:75)
        at liquibase.snapshot.jvm.JdbcSnapshotGenerator.snapshot(JdbcSnapshotGenerator.java:78)
        at liquibase.snapshot.SnapshotGeneratorChain.snapshot(SnapshotGeneratorChain.java:49)
        at liquibase.snapshot.jvm.JdbcSnapshotGenerator.snapshot(JdbcSnapshotGenerator.java:68)
        at liquibase.snapshot.SnapshotGeneratorChain.snapshot(SnapshotGeneratorChain.java:49)
        at liquibase.snapshot.jvm.JdbcSnapshotGenerator.snapshot(JdbcSnapshotGenerator.java:68)
        at liquibase.snapshot.SnapshotGeneratorChain.snapshot(SnapshotGeneratorChain.java:49)
        at liquibase.snapshot.DatabaseSnapshot.include(DatabaseSnapshot.java:313)
        at liquibase.snapshot.DatabaseSnapshot.replaceObject(DatabaseSnapshot.java:456)
        at liquibase.snapshot.DatabaseSnapshot.replaceObject(DatabaseSnapshot.java:478)
        at liquibase.snapshot.DatabaseSnapshot.includeNestedObjects(DatabaseSnapshot.java:369)
        at liquibase.snapshot.DatabaseSnapshot.include(DatabaseSnapshot.java:332)
        at liquibase.snapshot.DatabaseSnapshot.init(DatabaseSnapshot.java:106)
        at liquibase.snapshot.DatabaseSnapshot.<init>(DatabaseSnapshot.java:59)
        at liquibase.snapshot.JdbcDatabaseSnapshot.<init>(JdbcDatabaseSnapshot.java:38)
        at liquibase.snapshot.SnapshotGeneratorFactory.createSnapshot(SnapshotGeneratorFactory.java:214)
        at liquibase.snapshot.SnapshotGeneratorFactory.createSnapshot(SnapshotGeneratorFactory.java:187)
        at liquibase.command.core.DiffCommandStep.generateDatabaseShapshot(DiffCommandStep.java:205)
        at liquibase.command.core.DiffCommandStep.createReferenceSnapshot(DiffCommandStep.java:192)
        at liquibase.command.core.DiffCommandStep.createDiffResult(DiffCommandStep.java:113)
        at liquibase.command.core.DiffCommandStep.run(DiffCommandStep.java:89)
        at liquibase.command.CommandScope.execute(CommandScope.java:219)
        ... 23 more
Caused by: liquibase.exception.DatabaseException: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'dbo'.
        at liquibase.snapshot.ResultSetCache.get(ResultSetCache.java:98)
        at liquibase.snapshot.JdbcDatabaseSnapshot$CachingDatabaseMetaData.getForeignKeys(JdbcDatabaseSnapshot.java:77)
        at liquibase.snapshot.jvm.ForeignKeySnapshotGenerator.addTo(ForeignKeySnapshotGenerator.java:64)
        ... 44 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'dbo'.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:261)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1752)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:675)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:594)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7739)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4384)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:293)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:263)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:509)
        at liquibase.snapshot.ResultSetCache$ResultSetExtractor.executeAndExtract(ResultSetCache.java:249)
        at liquibase.snapshot.ResultSetCache$ResultSetExtractor.executeAndExtract(ResultSetCache.java:229)
        at liquibase.snapshot.JdbcDatabaseSnapshot$CachingDatabaseMetaData$ForeignKeysResultSetCache.bulkFetch(JdbcDatabaseSnapshot.java:877)
        at liquibase.snapshot.ResultSetCache.get(ResultSetCache.java:58)
        ... 46 more

Are you willing to submit a PR?

[ ] I'm willing to submit a PR (Thank you!)

May 14 '24 14:05 NetherGranite

liquibase liquibase copied to clipboard

Incorrect syntax exception with generate-changelog if using single quotes in table names in a database with many tables with SQL Server

Search first

Description

Steps To Reproduce

Expected/Desired Behavior

Liquibase Version

Database Vendor & Version

Liquibase Integration

Liquibase Extensions

OS and/or Infrastructure Type/Provider

Additional Context

Are you willing to submit a PR?

liquibase
liquibase copied to clipboard