Mysql replication with UPDATE JOIN on an ignored table

by

Options AFAIK are mainly based around getting the replication to be be ROW based rather then STATEMENT based.

  1. Set the default to ROW (which is a brute force method, and has its drawbacks).
  2. You can set the SESSION binlog_format to ROW, but it requires the SUPER privilege which the user probably hasn’t and will not be granted either for good reasons.
  3. If the logging happens in MIXED format you can look around here to force a ROW based entry in the binlog, trying to force a useless FOUND_ROWS() or UUID() call in the update could very well trigger it.

An example for the MIXED solution:
The queries:

INSERT INTO sometable VALUES ('a','aa');
UPDATE sometable SET aa="bb";
UPDATE sometable SET aa="cc" WHERE UUID(); -- slight overhead, but always true

The log (use mysqlbinlog to inspect it), clearly STATEMENT based for the first 2, but ROW based for the 3rd:

# at 175
#130918 21:18:25 server id 1  end_log_pos 277   Query   thread_id=142   exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1379531905/*!*/;
INSERT INTO sometable VALUES ('a','aa')
/*!*/;
# at 277
#130918 21:18:25 server id 1  end_log_pos 304   Xid = 488
COMMIT/*!*/;
# at 304
#130918 21:18:52 server id 1  end_log_pos 372   Query   thread_id=142   exec_time=0     eror_code=0
SET TIMESTAMP=1379531932/*!*/;
BEGIN
/*!*/;
# at 372
#130918 21:18:52 server id 1  end_log_pos 463   Query   thread_id=142   exec_time=0     error_code=0
SET TIMESTAMP=1379531932/*!*/;
UPDATE sometable SET aa="bb"
/*!*/;
# at 463
#130918 21:18:52 server id 1  end_log_pos 490   Xid = 497
COMMIT/*!*/;
# at 490
#130918 21:21:06 server id 1  end_log_pos 558   Query   thread_id=144   exec_time=0     error_code=0
SET TIMESTAMP=1379532066/*!*/;
BEGIN
/*!*/;
# at 558
# at 610
#130918 21:21:06 server id 1  end_log_pos 610   Table_map: `test`.`sometable` mapped to number 180
#130918 21:21:06 server id 1  end_log_pos 664   Update_rows: table id 180 flags: STMT_END_F
BINLOG '
Iv05UhMBAAAANAAAAGICAAAAALQAAAAAAAEABHRlc3QACXNvbWV0YWJsZQAC/A8DAwYAAQ==
Iv05UhgBAAAANgAAAJgCAAAAALQAAAAAAAEAAv///QJiYv0CY2P8AQAAYQJiYvwBAABhAmNj
'/*!*/;
# at 664
#130918 21:21:06 server id 1  end_log_pos 691   Xid = 578
COMMIT/*!*/;
DELIMITER ;
# End of log file

More Related Contents:

  1. Scaling solutions for MySQL (Replication, Clustering)
  2. INSERT … ON DUPLICATE KEY UPDATE with WHERE?
  3. How can “set timestamp” be a slow query?
  4. MySQL: @variable vs. variable. What’s the difference?
  5. How to enable MySQL Query Log?
  6. Join between tables in two different databases?
  7. Are you allowed to use numbers as table names in MySQL?
  8. MySQL error code: 1175 during UPDATE in MySQL Workbench
  9. How do you select every n-th row from mysql
  10. Add Foreign Key to existing table
  11. Auto Generate Database Diagram MySQL [closed]
  12. What’s the difference between using INDEX vs KEY in MySQL?
  13. mysql select top n max values
  14. MySQL Trigger: Delete From Table AFTER DELETE
  15. How to find out the MySQL root password
  16. Django – installing mysqlclient error: mysqlclient 1.3.13 or newer is required; you have 0.9.3
  17. Error #2002 Can’t connect to local MySQL server through socket ‘/Applications/MAMP/tmp/mysql/mysql.sock’ (2)
  18. Using index, using temporary, using filesort – how to fix this?
  19. How to restrict a column value in SQLite / MySQL
  20. How to get the max of two values in MySQL?
  21. How to connect locally hosted MySQL database with the docker container
  22. MySQL – SELECT * INTO OUTFILE LOCAL ?
  23. how to partition a table by datetime column?
  24. WooCommerce: Finding the products in database
  25. How to auto-load MySQL on startup on OS X Yosemite / El Capitan
  26. Mysql performance on 6 million row table
  27. How can I “select *” from a table in MySQL but omit certain columns?
  28. For homebrew mysql installs, where’s my.cnf?
  29. MySQL Select Multiple VALUES
  30. Convert SQL Server query to MySQL [duplicate]

Leave a Comment