Shulou(Shulou.com)05/31 Report--

What this article shares with you is about the limitations and solutions of GTID in MySQL. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Now when I look at a technology, I always look at it from another perspective. I prefer to see what he can't do and why he can't do it on the basis of what he can achieve.

For example, MySQL GTID tested the waters at 5.6,5.7 has been developed, but there are still some scenarios that are limited. Like the next two.

One is the create table xxx as select schema, and the other is temporary table-related.

Today we will briefly talk about these two scenes.

The solution of the restriction of create sentence in GTID

The create table xxx as select statement will actually be split into two parts, the create statement and the insert statement, but if you want to do it all at once, MySQL will throw the following error.

Mysql > create table test_new as select * from test

ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE. SELECT.

In fact, the goal of this statement is clear, copy table structure, copy data, the part of insert is easy to solve, the difficulty lies in the part of create table, if a table has 100 columns, then spelling out such a statement is a project.

We also learn to use it skillfully to see if MySQL has any special ways to deal with it.

In addition to properly spelling out table statements, another way is to use like, which is unique to MySQL.

The create table xxx as select approach is split into two parts.

Create table xxxx like data_mgr

Insert into xxxx select * from data_mgr

Limitations and considerations of temporary tables

The other one seems to be a little strange, looking at the document is nothing to say, just remember, but it is not.

If there is a temporary table change in the transaction, it is likely to lead to data inconsistency. There is a corresponding bug in version 5.5 of MySQL, see https://bugs.mysql.com/bug.php?id=76940

If you need to reproduce, you can find a set of 5.5 environment to simulate, minute by minute effect.

We create two tables T1 and T2, and then establish a foreign key association between the two tables for subsequent testing.

Create table T1 (C1 int primary key) engine=innodb

Insert into T1 values (1), (2), (3), (4), (5)

Create table T2 (C1 int, c2 int, foreign key (c2) references T1 (C1)) engine=innodb

Insert into T2 values (1), (2), (5)

Create a temporary table

> create temporary table tmp as select * from T1

Query OK, 5 rows affected (0.01sec)

Records: 5 Duplicates: 0 Warnings: 0

Simulate the bug and start the transaction.

> begin

> drop temporary table if exists tmp

Query OK, 0 rows affected (0.00 sec)

> delete from T1 where C1 > 2

ERROR 1451 (23000): Cannot delete or update a parent row: a fore

Query OK, 0 rows affected (0.00 sec)

Then use mysqlbinlog to check the information inside. You can see that in addition to the temporary table operation above, the following delete will also be written to binlog

Use `test` / *! * /

SET timestamp 1499784283

DROP TEMPORARY TABLE IF EXISTS `tmp` / * generated by server * /

/ *! * /

# at 300

# at 341

# 170711 22:44:46 server id 13386 end_log_pos 341Table_map: `test`.`t1` mapped to number

# 170711 22:44:46 server id 13386 end_log_pos 380 Delete_rows: table id 207 flags: STMT_END_F

BINLOG'

XuRkWRNKNAAAKQAAAFUBAAAAAM8AAAAAAAEABHRlc3QAAnQxAAEDAAA=

XuRkWRlKNAAAJwAAAHwBAAAAAM8AAAAAAAEAAf/+AwAAAP4EAAAA

'/ *! * /

# DELETE FROM test.t1

# WHERE

# @ 1room3 / * INT meta=0 nullable=0 is_null=0 * /

# DELETE FROM test.t1

# WHERE

# @ 1room4 / * INT meta=0 nullable=0 is_null=0 * /

# at 380

# 170711 22:44:49 server id 13386 end_log_pos 449 Query thread_id=176 exec_time=0 error_code=0

SET timestamp 1499784289

COMMIT

From this, you can clearly see that although a transaction rollback has been done, binlog will still record the rollback changes, which can trigger master-slave data inconsistencies in some scenarios.

In GTID, this check has been done, and in the final analysis, it is still the mechanism in cache. Generally speaking, binlog has two cache to cache the transaction binlog:

Binlog_cache_data stmt_cache; / / stores non-transactional tables and temporary tables binlog

Binlog_cache_data trx_cache; / / store transaction table binlog

These are some of the limitations and solutions of GTID in MySQL. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.