Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Won't Fix
-
Affects Version/s: 5.5.34
-
Fix Version/s: None
-
Component/s: None
-
Labels:
-
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: 0mydb=# 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
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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