When append useAffectedRows=true to the jdbc URL, liquibase cannot update the changeloglockdatabasechangeloglock properly

[jobmission]

Search first

• I searched and no similar issues were found

Description

I am using a MySQL database，when append useAffectedRows=true to the jdbc URL, liquibase cannot update the changeloglockdatabasechangeloglock properly.

0 rows were updated instead of the expected 1 row using executor liquibase.executor.jvm.JdbcExecutor there are [{COUNT(*)=1}] rows in the table

Steps To Reproduce

append eAffectedRows=true to the jdbc URL.
spring.datasource.url=jdbc:mysql://ip:3306/your_db_name?allowPublicKeyRetrieval=true&useAffectedRows=true

Expected/Desired Behavior

Liquibase can execute normally, regardless of whether there is a new changeset

Liquibase Version

4.24

Database Vendor & Version

mysql 8.3

Liquibase Integration

Maven springboot3.2.2

Liquibase Extensions

No response

OS and/or Infrastructure Type/Provider

Windows10

Additional Context

No response

Are you willing to submit a PR?

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

[tati-qalified]

Hi @jobmission, I've tested this using Spring Boot and using the CLI, but I haven't been able to reproduce the bug you're reporting.

Could you provide more detailed steps to reproduce it?

Thanks,
Tatiana

[jobmission]

Thanks you reply.
The same code will trigger this issue on the second run。
During the second run, there was no changesets to run. Because "useAffectedRows=true" was set, so the update databasechangeloglock returned 0 instead of 1, causing the problem to occur

[tati-qalified]

I ran the test again and was able to verify the error you've mentioned. However, in my case the lock is eventually released, and the program doesn't fail.

Here's part of the output from the second run:

Database is up to date, no changesets to execute
2024-01-30T13:06:43.651-03:00  INFO 30688 --- [main] liquibase.changelog                      : Reading from lbcat.DATABASECHANGELOG
2024-01-30T13:06:43.657-03:00  INFO 30688 --- [main] liquibase.util                           : UPDATE SUMMARY
2024-01-30T13:06:43.657-03:00  INFO 30688 --- [main] liquibase.util                           : Run:                          0
2024-01-30T13:06:43.657-03:00  INFO 30688 --- [main] liquibase.util                           : Previously run:               1
2024-01-30T13:06:43.657-03:00  INFO 30688 --- [main] liquibase.util                           : Filtered out:                 0
2024-01-30T13:06:43.657-03:00  INFO 30688 --- [main] liquibase.util                           : -------------------------------
2024-01-30T13:06:43.657-03:00  INFO 30688 --- [main] liquibase.util                           : Total change sets:            1
2024-01-30T13:06:43.657-03:00  INFO 30688 --- [main] liquibase.util                           : Update summary generated
2024-01-30T13:06:43.677-03:00  WARN 30688 --- [main] liquibase.lockservice                    : Failed to release change log lock
2024-01-30T13:06:43.678-03:00 ERROR 30688 --- [main] liquibase.command                        : Could not release lock

liquibase.exception.LockException: liquibase.exception.LockException: Did not update change log lock correctly.

0 rows were updated instead of the expected 1 row using executor liquibase.executor.jvm.JdbcExecutor there are [{COUNT(*)=1}] rows in the table
	at liquibase.lockservice.StandardLockService.releaseLock(StandardLockService.java:397) ~[liquibase-core-4.24.0.jar:na]
	... 38 common frames omitted
    
(...)

2024-01-30T13:06:43.681-03:00  INFO 30688 --- [main] liquibase.command                        : Command execution complete

(...)

Process finished with exit code 0

And here's my databasechangeloglock table, also after the second run:

My question is: is this error blocking your work? Are you getting an exception that makes the execution fail?

[jobmission]

just like your test output, there is no blocking of the application. But with error and exception outputs, it will trigger our log analysis system alarm, causing unnecessary trouble.

[tati-qalified]

That makes sense. We currently have a fix to a similar problem but only for MSSQL database instances, it can be found in the Liquibase source code.
For version 4.25.1, the path is liquibase-standard/src/main/java/liquibase/lockservice/StandardLockService#373.

If you want, you could submit a PR to extend that solution to MySQL instances as well. This would ensure that the fix is implemented soon, and ready for you to use. Our development team will be available to provide guidance if needed.

Thank you,
Tatiana