Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 9

Displaying posts with tag: RBR (reset)

SBR vs RBR when using On Duplicate Key Update for High Availability
+1 Vote Up -0Vote Down

The other day, I was discussing high availability (HA) with other people.  The discussion was going in the direction of, as the application was only using INSERT ... ON DUPLICATE KEY UPDATE (ODKU) and traffic could be replayed, promoting a slave in case of the failure of the master was easy: replaying traffic would make all the slaves converge back to a common state.  It looks as this could work but it might not be as simple.  Before going into the details, let's take a step back and look into HA in general.

  [Read more...]
“Duplicate Entry” error for key PRIMARY on UPDATE query (RBR + Triggers)
+0 Vote Up -0Vote Down

Recently, I have faced one interesting issue with Master(Active)-Master(Passive) replication (RBR + Triggers). Passive master was stopped due to “Duplicate Entry” error with update statement. It was something like this.

Last_Error: Error ‘Duplicate entry ‘29014131’ for key ‘PRIMARY” on query. Default database: ‘db’. Query: ‘UPDATE `db`.`tab1` SET `empid`=’103′, `name`=’Nilnandan’,  `address`=’India ‘, `postcode`=’D100′, `phone`=’878 515 7788’;

Interesting thing was , id (primary key column) was not updated in above update statement. Initially I was confused but when I check further, found that both servers has …

  [Read more...]
Delayed row-based replication with large tables lacking a primary key
+0 Vote Up -0Vote Down

I configure all our master databases to use row-based binary logging where I work. In my opinion it is a much safer option than statement-based replication. The advantages and disadvantages of both types of MySQL replication are detailed in the online documentation here. You can't view the events a slave is applying directly with 'show processlist' but by issuing 'show open tables where in use' you can detect what table is receiving the attention of the SQL thread. If you need more information the mysqlbinlog command …

  [Read more...]
Beware of RBR and tables without indexes
Employee +2 Vote Up -0Vote Down

I always knew RBR and unindexed tables didn't play along very well, but never realized just how much you can distress a slave can in some cases.
Consider this statement (yeah yeah, i know :)
mysql> delete from t1 order by rand(); Query OK, 78130 rows affected (2.61 sec)
t1 has no indexes and is an int field with numbers from 1 to 78130. However, this will cause the slave to re-read entire table for each row deleted! Here it's still running, causing 100% cpu usage:
---TRANSACTION 0 1799, ACTIVE 2390 sec, OS thread id 3672 fetching rows mysql tables in use 1, locked 1 153 lock struct(s), heap size 30704, 78281 row lock(s), …

  [Read more...]
Replication with InnoDB and MyISAM Transactions
Employee +0 Vote Up -0Vote Down

There’s a change of behaviour in MySQL 5.1.31 for Row Based Replication, if you have InnoDB transactions that also write to a MyISAM (or other non-transactional engine) table. It’s a side effect of fixing Bug #40116. Take this simple example:

Transaction 1: INSERT INTO myisam_tbl (item, val) VALUES (1, 0);
Transaction 1: INSERT INTO innodb_tbl (item, val) VALUES (1, -1), (2, -1);
Transaction 1: UPDATE myisam_tbl SET val=val+1 WHERE item=1;
Transaction 1: UPDATE innodb_tbl SET val=( SELECT val FROM myisam_tbl WHERE item=1 ) WHERE item=1;
Transaction 2: START …
  [Read more...]
Q&A on MySQL 5.1
+0 Vote Up -0Vote Down

Listening to Sheeri's presentation on MySQL 5.1, I saw that there are a few questions left unanswered. I am listing here some of the questions that I found interesting, plus a few from an early webinar on the same topic.

Q: does Partitioning physically split data?
A: No. Some engines (MyISAM, Archive) do a physical split, but this is not necessary, as you see if you apply partitioning to a InnoDB table. Partitioning is a logical split of data, for easy retrieval. It is completely transparent to the user. …

  [Read more...]
Quality of 5.1 GA release
+0 Vote Up -0Vote Down

With all due respect to Monty (and I mean that — much respect is due), I have some serious issues with his portrayal of the 5.1 release.  I hate to make my first entry on Planet MySQL about a controversy, but he encouraged people to blog about their experience with 5.1, so that’s what I’ll do here.

Overall Quality

As a long time user, I am very confident that the quality of 5.1 GA far exceeds that of the initial 5.0 GA release (5.0.15).  In fact, I would go further and suggest that the MySQL organization has if anything been too …

  [Read more...]
An odd spot in relay slaves
+0 Vote Up -0Vote Down

A relay slave is a slave that writes to its binary log the stream of commands received from the master, thus enabling other slaves to replicate.
This is often used to relieve the master from heavy traffic created by many slaves asking for binlog events.

A typical scenario is when a master has a few dozen slaves, and instead of dealing with all of them directly, uses four relay slaves, each one dealing with 6 …

  [Read more...]
Decoding binlog entries with row-based replication
+0 Vote Up -0Vote Down

If you have tried using row based replication, you may have noticed two things: (1) it fixes many inconsistencies of statement-based replication, but (2) the binlog is unfit for humans. Inspecting it after a problem occurs won't provide any useful information.
The instructions look like line noise, and when you don't see the result you were expecting you wonder if that's the case.

For …

  [Read more...]
Showing entries 1 to 9

Planet MySQL © 1995, 2016, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.