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 1 year 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

Hi @NetherGranite, thank you for reporting this bug. I have been able to replicate it, no matter if the single quote is in the middle of the table name or at the end. So, myTable' and my'table both generate an error.

This issue will be left open for the community to propose a fix for. The developers will be available to provide guidance if needed.

Thank you, Tatiana

May 21 '24 17:05 tati-qalified

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