Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
10.0, 10.1, 10.1.36, 10.2, 10.3, 10.4
-
None
-
Debian GNU/Linux 9.7 (stretch) using Mariadb packages from Debian and from Mariadb repository
Description
After updating our Debian servers from 10.1.26 to 10.1.37 from the Debian Stable repos I noticed that some simple UPDATE statements suddenly caused issues by either triggering a warning when using binlog_format=statement or by being logged in row format when using binlog_format=mixed or row.
I investigated a bit and it showed that this behaviour started in 10.1.36 and is still prevalent in 10.3 and I found nothing in the changelogs so I decided to report it. I have attached a simple testcase that when run with binlogs enabled and everything else default on a 10.1 instance < 10.1.36 will run without issues but from 10.1.36 on will trigger the warning:
| Note | 1592 | Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly. |
It seems to boil down to the triggers/actions in the foreign key constraints referencing the updated table. When those are not there no warning is triggered and I can't see why this query might actually be unsafe to be logged in statement format as the actual foreign key column is not touched.
I know this behaviour usually won't break anything which is why I left it at minor priority, for me this caused quite a problem though as I have a table matching the testcase that gets updated frequently and with using binlog_format=mixed we suddenly faced 1GB of binlogs every 10 minutes instead of every 2h with statement format which filled up disks and caused a lot more io to happen.