[MDEV-5380] binlog row format and table without PK Created: 2013-12-03  Updated: 2013-12-03  Resolved: 2013-12-03

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.34
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Nicolas Payart Assignee: Unassigned
Resolution: Won't Fix Votes: 0
Labels: upstream
Environment:

Using MariaDB Generic Linux packages on Debian Wheezy 64-bits



 Description   

I set a replication master -> slave.
I create a simple table with NO Primary Key
I populate data (a few thousands rows)

Then, I switch binlog format to ROW
I update rows on the master (it takes a few seconds to execute)

=> On slave, it takes a very long time to apply changes (on my original table with 200'000 rows, I skipped the query (with SQL_SLAVE_SKIP_COUNTER) after 20 minutes still in "Reading event from the relay log" state...).

If I create a primary key, changes are applied instantly on slave.

Here is a test case to reproduce the problem :

MASTER
--------

mydb=# CREATE TABLE `nptest` (
-> `id` int(11) NOT NULL DEFAULT '0',
-> `tmp` tinyint(4) DEFAULT NULL
-> );
Query OK, 0 rows affected (0.07 sec)

mydb=# INSERT INTO nptest (id) SELECT id FROM othertable;
Query OK, 192743 rows affected (1.83 sec)
Records: 192743 Duplicates: 0 Warnings: 0

mydb=# SET binlog_format = ROW;
Query OK, 0 rows affected (0.00 sec)

mydb=# UPDATE nptest SET tmp = 1;
Query OK, 192743 rows affected (3.76 sec)
Rows matched: 192743 Changed: 192743 Warnings: 0

SLAVE
--------

(none)=# SHOW PROCESSLIST\G

*************************** 23. row ***************************
Id: 627527
User: system user
Host:
db: NULL
Command: Connect
Time: 103
State: Reading event from the relay log
Info: NULL
Progress: 0.000



 Comments   
Comment by Elena Stepanova [ 2013-12-03 ]

It's not really surprising though, is it? You run one update on the master, it triggers 200,000 updates on the slave, each of which has to look up a row without any index.

Is there any particular reason why you choose not to use a PK?

MySQL bug report for the reference (there were many before and after, but I think this one is most representative):

http://bugs.mysql.com/bug.php?id=53375

Comment by Nicolas Payart [ 2013-12-03 ]

Thank you Elena. Yes of course, without PK I now realize that slave must scan all rows to apply changes. I thought it had some internal id or whatever to identify each row but it is not... Well, another good reason to always create a PK!

Comment by Elena Stepanova [ 2013-12-03 ]

Closing for now as "won't fix". Hopefully in future versions there will be improvements in the area.

Generated at Thu Feb 08 07:03:49 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.