Facing issue with mySQL and sql_safe_updates=1

1

Hello Team,

im facing below issue
my db configuration is as below

url: jdbc:mysql://localhost:20307/test?useSSL=false&allowPublicKeyRetrieval=true&requireSSL=true&verifyServerCertificate=true}&trustCertificateKeyStoreType=JKS&connectionTimeZone=SERVER&preserveInstants=true&enabledTLSProtocols=TLSv1.2&sessionVariables=sql_safe_updates=1

i have used dependency as below
implementation(“mysql:mysql-connector-java:8.0.30”)
implementation(“org.liquibase:liquibase-core:4.9.1”)

below is my changelog-master.xml


Issue:
When starting the application, im facing below error
Caused by: liquibase.exception.DatabaseException: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. [Failed SQL: (1175) DELETE FROM DATABASECHANGELOGLOCK]
at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:397)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:83)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:151)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:135)
at liquibase.lockservice.StandardLockService.init(StandardLockService.java:132)
at liquibase.lockservice.StandardLockService.acquireLock(StandardLockService.java:286)
… 32 common frames omitted
Caused by: java.sql.SQLException: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:763)
at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:393)
… 37 common frames omitted

Solutions tried:
a) inside runscript.sql i have added set sql_safe_updates=0 after finishing query i set it as 1, dint work out.
b) if i removed sql_safe_updates=1 from application.yaml then it worked but i dont want that solution.

Help is highly appreciated.

2

At https://github.com/liquibase/liquibase/blob/4edcddc2d735cc08738a9781aeccfefde9627ee9/liquibase-standard/src/main/java/liquibase/sqlgenerator/core/InitializeDatabaseChangeLogLockTableGenerator.java#L29 liquibase runs a delete statement to populate DATABASECHANGELOGLOCK , so there is no workaround for that. I think you would need to open an issue at github requesting for changes to make liquibase work with this specific mysql configuration.