CREATE TEMPORARY TABLE creates a transaction in binary log on read only server
Bug #1539504 reported by
Kazimieras Aliulis
This bug affects 1 person
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
||||
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.5 |
Won't Fix
|
Medium
|
Unassigned | |||
5.6 |
Fix Released
|
High
|
Laurynas Biveinis | |||
5.7 |
Fix Released
|
High
|
Laurynas Biveinis |
Bug Description
If "CREATE TEMPORARY TABLE..." gets executed in server with GTID enabled and read_only mode enabled, then 'DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `sometablename`' gets inserted in server binary log. This creates errant transaction, that other slaves in cluster might not have and can break replication if server with errant transaction gets promoted to master and this transaction is already deleted from binary logs.
Changed in percona-server: | |
status: | Incomplete → New |
tags: | added: i138835 |
tags: | added: upstream |
tags: | added: i140906 |
To post a comment you must log in.
Hi,
According to Documentation:
http:// dev.mysql. com/doc/ refman/ 5.6/en/ server- system- variables. html#sysvar_ read_only
Even with read_only enabled, the server permits these operations:
Updates performed by slave threads, if the server is a replication slave. In replication setups, it can be useful to enable read_only on slave servers to ensure that slaves accept updates only from the master server and not from clients.
Use of ANALYZE TABLE or OPTIMIZE TABLE statements. The purpose of read-only mode is to prevent changes to table structure or contents. Analysis and optimization do not qualify as such changes. This means, for example, that consistency checks on read-only replication slaves can be performed with mysqlcheck --all-databases --analyze.
Operations on TEMPORARY tables.
So Operations on TEMPORARY tables enabled with read_only.
If it is not related to your report then please provide more information such as, which version you have been used? configuration options both for master and slave server, the error log portions and anything you think will be valuable.