Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3(EOL), 10.4(EOL), 10.5
-
None
Description
According to the documentation (https://mariadb.com/kb/en/read-only-slaves/) changes to TEMPORARY TABLEs are no longer logged in the binary log:
CREATE, DROP, ALTER, INSERT and DELETE of temporary tables are not logged to binary log, even in statement or mixed mode. With earlier MariaDB versions, one can avoid the problem with temporary tables by using binlog_format=ROW in which cases temporary tables are never logged.
I don't think this is correct, imagin this:
CREATE TEMPORARY TABLE encrypt_the_password AS select id, username, MD5(password) FROM user;
|
INSERT INTO user_with_encryped_password SELECT * FROM encrypt_the_password;
|
The `user_with_encryped_password` table is a real table, and it cannot be filled if the temporary table does not exist in that session. Using SBR or MIXED replication, that is.
Is the documentation incorrect or is this a bug in the server?
Attachments
Issue Links
- is caused by
-
-
- Closed
-
Activity
I did not, I do recollect using similar statements to this in systems with SBR.
It might as well that it still works and that the documentation is incorrect.
Documentation certainly doesn't match the current implementation. Possibly it happened because there were several commits related to this, all without a JIRA item, and some were apparently reverting others, intentionally or not.
However, I'm not quite sure that the current implementation itself is correct and should be documented as is.
I suggest that at least Elkin reviews the current situation (once again) before passing it over to documentation, and maybe helps to summarize it for documentation purposes. Also, when time permits, I recommend alice to do some testing with temporary tables / different binlog formats / read_only=0|1.
The combination of temporary tables and binary logging has always been fragile, read_only makes it even more so. The scenario from the description doesn't fail right away, but it doesn't seem very convincing. At the first glance, the behavior in regard to (not-)logging temporary tables is different depending on how the table was created (CREATE vs CREATE .. SELECT). Also, it seems that in some cases actions on temporary tables make binary logging switch to row format even in SBR mode. I hope we only have very few users of true statement mode (as opposed to mixed), but those who do use it probably do it because they don't want any row events in their binary log.
This looks like a bug i am experiencing. Worked fine in 5.5, broke after upgrading to 10.3
https://jira.mariadb.org/browse/MDEV-24667
This is not the same. The claim in this report is related to changes for read-only slaves introduced in 10.3; and while it's difficult dismiss given the variety of use cases and fragility of the area, it remains hypothetical until proven otherwise.
Your case in MDEV-24667 is very specific and very real, and it is unrelated to 10.3 changes, it's caused by much earlier 10.0 changes.
Hi @Elena Stepanova - I'm curious about this issue which you mention:
> At the first glance, the behavior in regard to (not-)logging temporary tables is different depending on how the table was created (CREATE vs CREATE .. SELECT).
Is this intentional behavior? I have verified that in 10.3 --> 10.5 if binlog_format is MIXED or STATEMENT AND if the server is in read_only mode, the following query will NOT result in an entry in the binlog:
CREATE TEMPORARY TABLE t1_temp (id INT);
Whereas the following query WILL create 2 entries (one for the create and one for the drop when the session ends) (table t1 of course has to exist)
CREATE TEMPORARY TABLE t1_temp LIKE t1;
Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.
Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.
Did you verify that this doesn't work in SBR or MIXED but used to work before?