Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Open
1 of 2 tasks
NetherGranite opened this issue May 14, 2024 · 1 comment
Open
1 of 2 tasks

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

NetherGranite opened this issue May 14, 2024 · 1 comment
Labels
DBMSSQLServer triaged TypeBug

Comments

@NetherGranite
Copy link

NetherGranite commented May 14, 2024 •
edited

Search first

  • 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

  1. Create a SQL Server database with around 100 tables under a default schema dbo, one of which is named something like [Table_Name']
  2. Run generate-changelog on the database
  3. Get the exception Unexpected error running Liquibase: liquibase.exception.DatabaseException: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'dbo'.
  4. Run generate-changelog on the database but exclude the table via exclude-objects
  5. 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!)
@tati-qalified
Copy link
Contributor

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
DBMSSQLServer triaged TypeBug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@NetherGranite @kevin-atx @tati-qalified