According to the last comment on the upstream bug, the bugfix is not quite about the same. It only fixes the situation when the table (temporary table in this case) does not exist. In your case, it does.

As I remember, temporary tables are a painful subject in regard to binary logging format, and it's not as simple at all as "just don't write it in the RBR". The binary logging format might change dynamically (and even automatically), so it's entirely possible that at the time when the table was created, it was SBR and thus the statement was written.

I'll leave it to Elkin to handle it further.

elenst It creates errant transaction regardless temporary table exists or not.

slave1 [localhost] {msandbox} ((none)) > SHOW GLOBAL VARIABLES LIKE 'binlog_format';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| binlog_format | ROW   |

+---------------+-------+

1 row in set (0.00 sec)

slave1 [localhost] {msandbox} ((none)) > SHOW GLOBAL VARIABLES LIKE 'read_only';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| read_only     | ON    |

+---------------+-------+

1 row in set (0.00 sec)

slave1 [localhost] {msandbox} ((none)) > use test;                                  

Database changed

slave1 [localhost] {msandbox} (test) > DROP TEMPORARY TABLE IF EXISTS t1;

Query OK, 0 rows affected, 1 warning (0.00 sec)

slave1 [localhost] {msandbox} (test) > show warnings;

+-------+------+-------------------------+

| Level | Code | Message                 |

+-------+------+-------------------------+

| Note  | 1051 | Unknown table 'test.t1' |

+-------+------+-------------------------+

1 row in set (0.00 sec)

slave1 [localhost] {msandbox} (test) > SHOW BINLOG EVENTS IN 'mysql-bin.000009';

+------------------+-----+-------------------+-----------+-------------+----------------------------------------------------------------------+

| Log_name         | Pos | Event_type        | Server_id | End_log_pos | Info                                                                 |

+------------------+-----+-------------------+-----------+-------------+----------------------------------------------------------------------+

| mysql-bin.000009 |   4 | Format_desc       |         2 |         256 | Server ver: 10.2.14-MariaDB-log, Binlog ver: 4                       |

| mysql-bin.000009 | 256 | Gtid_list         |         2 |         315 | [0-2-171,0-1-176]                                                    |

| mysql-bin.000009 | 315 | Binlog_checkpoint |         2 |         358 | mysql-bin.000009                                                     |

| mysql-bin.000009 | 358 | Gtid              |         1 |         400 | GTID 0-1-177                                                         |

| mysql-bin.000009 | 400 | Query             |         1 |         535 | DROP TEMPORARY TABLE IF EXISTS `test`.`t1` /* generated by server */ |

| mysql-bin.000009 | 535 | Gtid              |         2 |         577 | GTID 0-2-178                                                         |

| mysql-bin.000009 | 577 | Query             |         2 |         712 | DROP TEMPORARY TABLE IF EXISTS `test`.`t1` /* generated by server */ |

+------------------+-----+-------------------+-----------+-------------+----------------------------------------------------------------------+

7 rows in set (0.00 sec)

It creates errant transaction regardless temporary table exists or not.

Yes, I know. My point is, even if we use the upstream fix, it won't solve the problem you are describing. And I don't think with the current implementation it is possible just not to write DROP for temporary tables as you suggested. Maybe Elkin will think of a different solution.

> valerii: it should be as simple as "no new GTID written to the binary log on slave, ever"

Right.

But at the same time we need to pay attention to
a use case by elenst and serg which is about to maintain
consistent binlogging of tmp table operation despite
dynamic changes to --read-only.
It seems that's why --read-only allows binlogging by design.

The two goals may actually clash as #23911 witnesses.

A.
There exists workarounds to call it as least. If a read-only slave specified
its local gtid domain, different from the master domain both requirements satisfy.
This particular slave promotion to master is replication safe. However while this method is feasible it's somewhat complicated in case we would like to prevent
the local domain gtids spread (I left 1-5 steps to automate in MXS in another thread, for the record).

B.

If this one does not suffice (to the customer / MXS ) we can go with
a new server flag to disable the "sticky" tmp table binlogging. The flag can be used
safely on leaf node slaves (no more slaves in chain behind - this fact can't established in real time, of course, so we can't automate a sane initial flag value).

C.
There is also an idea to soften my initial drastic proposal of not to
log completely to *no binlogging to the binlog file while holding on
temp table repl events in THD binlog cache for possible flushing when
--read-only turns to 1*. This won't create either own local or master
domain gtids when --read-only remains 0.

Now I need serg to assess especially B, C one of the two I think we should pick out. The support alerts our action.

There's also D. mark temporary tables created or dropped during read-only. And if later, read-only was turned off, they're needed for replication, switch to row-based (in mixed) or error out (in statement).

It'll avoid logging a lot of (almost always useless) events in THD binlog cache (as in C) and won't require any user actions (as in B).

serg, I like D. idea.

Indeed we can refine logging rules for table drop/create at read-only.

A question comes anyway to pre (set ro=1) existing temp tables.
Similarly to the new tables they could remain updateable as long as @@session.binlog_format = ROW. (I am not sure if we can extend that to MIXED
esp if a table was created under MIXED so must have been bin-logged.)
A use case for that could be of hold on changes eventually aimed at regular tables.

Sergei, could you please follow my latest refinement for D. Thanks, Andrei.

Elkin, yes to handle tables created before but dropped during read-only, I would suggest not to drop them That is, when a user drops such a temporary table, the server doesn't, and only marks the table as "dropped". The server will pretend a table doesn't exist anymore, but will still keep it around. It'll be dropped later, on disconnect, or when a user creates a new temporary table with the same name.

A much simpler fix would be to remember per THD whether it has created or dropped temporary tables during read-only, and refuse to change binlog format away from ROW.

This will practically work for most of the users. And it will help to avoid implementing a fairly complex solution for a use case that nobody has.

The requested change in read-only mode binlogging is motivated by a user running into

 DROP TEMPORARY TABLE IF EXISTS `ipein`.`rxn_report_data_tmp` /* generated by server */

being logged into the binary log of the slave apparently by the
connection close. This created a gtid event as well which later is
found out at guilt for failure of the slave promotion to master.
There are no complains on having CREATE TEMPORARY TABLE also logged
and considering the global binlog-format is ROW the create events were
never logged indeed. That is the user's immediate issue might be well resolved by
tracking the binlog format change starting from the time of a temporary table creation.

Executive summary:
When the format has not changed from (effective) ROW the table drop should not be logged.

However along discussion we've came across a related issue of how to
handle temp tables within the read-only periods which the following
description attempts to specify.

At time of @@global.read_only = ON
and both @@global.log_bin @@session.sql_log_bin are ON

   CREATE TEMPORARY TABLE

is allowed only when @@session.binlog_format= ROW or MIXED. Otherwise
the CREATE is errored out.
MIXED is treated as unsafe for replication in this case so any future
updates on the table are done in the effective ROW mode, that is not
bin-logged.

   DROP TEMPORARY TABLE

is allowed always and it's not binlogged regardless whether it was
created within the current read_only term or earlier.
Implicit DROP through connection close behaves similarly wrt binlogging,
the DROP event binlogging gets deferred until the read-only term ceases.

OTHER binloggable DML and DDL operation on the temp table
are handled similarly.
When a temporary table existed until the current read-only has started and
its @@session.binlog_format is not ROW its attempted modification will be
errored out. Otherwise The ROW format session will proceed seamlessly.

Summary:

Effectively no temporary table replication event gets created in
the read-only term, while such table still can be
created/dropped/modified within the term. Implicit dropping of the
pre-existing temporary table via connection close is handled with
deferring the DROP event creation until @@global.read_only turns to
OFF. The DROP replication event may be completely missed therefore should the
read-only server be shut down. In such case a warning will be
generated also to suggest to execute compensating query manually
which makes sense when there are more slaves
down replication chain that saw the CREATE:s.

serg, could you please run through a summary of proposed changes. Also notice the most probable immediate issue that the user faced which stands somewhat separately from the read-only policy we have been discussing.

Thank you for your time!

Andrei

I suggested a simpler fix above:

A much simpler fix would be to remember per THD whether it has created or dropped temporary tables during read-only, and refuse to change binlog format away from ROW.

Practically, it's done by remembering just one flag per THD: temp_tables_existed_when_read_only_was_enabled

When read_only is disabled in a connection and this flag was set or a temporary table exists, then forever (until disconnect) log everything done in this connection using RBR.

This solves all practical cases with minimal run-time overhead and without tracking per table when it was created. As a drawback, it will force RBR more than strictly necessary in some corner cases.

serg, When read_only is disabled in a connection and this flag was set or a temporary table exists, then forever (until disconnect) log everything done in this connection using RBR this also stays in my sketch. temp_tables_existed_when_read_only_was_enabled is to govern post-read_only term binlogging behavour. And as I am fine to associate it with the connection rather than with a table.

Let me make myself clear on two points.

Firstly, due to post read-only RBR-forever principle there's a use case of a DROP not to be logged which I suggested we would tackle with a warning.

Secondly, the immediately reported case is (may be) irrelevant for the read-only discussion at all as the unexpected DROP TEMP table event is just wrong to turn out into binlog when the connection runs binlog_format ROW which must've been the case in the reporter's case (no CREATE was spotted plus @@global.binlog_format=ROW hints to that).
I wanted to fix the latter case instead or at least first.

okay, an addition:

• if a THD is in the RBR-forever mode, log all drops of temporary tables (using DROP TEMPORARY TABLE IF EXISTS syntax).

and, sure, in that approach that we both seem to agree on there will be no temp-table related writes into binlog in read_only mode. At all. So it'll naturally solve the latter case.

serg, when I agreed on the above proposals from you, I still was not certain of a solution to the support case which I initially believed was caused by connection close. If fact the customer case must be rooted by a new MDEV-20091 that I reported. The latter complains about MDEV-5589 implementation of not logging DROP TEMPORARY TABLE for tables where the CREATE TABLE was not logged feature.
Notice that the MDEV-5589 idea is very close to marking per a table which I thought along
our discussion. So there's is knowledge in every temp table def about its logged status that we can possibly exploit.

Yet I have to prioritize now MDEV-20091 fixing 'cos actually represents the customer case and apparently does not require lots of design consideration.

serg, to earlier comment of yours, incl
to handle tables created before but dropped during read-only, I would suggest not to drop them That is, when a user drops such a temporary table, the server doesn't, and only marks the table as "dropped". The server will pretend a table doesn't exist anymore, but will still keep it around. It'll be dropped later, on disconnect.., and
if a THD is in the RBR-forever mode, log all drops of temporary tables (using DROP TEMPORARY TABLE IF EXISTS syntax)

My question has been (was in removed/edited comments) how/if logging should be implemented for a case like

--connection user

set @@binlog_format=STATEMENT;

CREATE TEMPORARY table tt;             # CREATE Logged

--connection admin

set @@global.read_only=1;

--connection user

--disconnect

We are not going to log such otherwise must-do-log DROP into binlog until (if ever)

--connection admin

set @@global.read_only=0;

But at this point the connection user has gone. So practically we would have to create a list of deferred-to-log-DROP temp tables which the connection admin would attend as a pre-hook to its main read-write opening action.

It's worth to mention DROP /regular/ TABLE IF EXISTS as a second object to handle if we are going to pursue
no binlogging at read-only principle.

Footnotes:
A similar question when CREATE is issued at having ROW format is answered by MDEV-5589/ MDEV-20091

Work in bb-10.3-monty, f49f37eb2dff02dba19f8b3a4e5b55d65e13c131

Will add the MDEV's this fixed in the final commit before pushing

Coding, documentation and testing

Temporary tables will not be logged in read-only mode. Temporary tables created while the server is in read-only mode will not be logged even after the server has disabled the read-only mode.