Bug #20065
closed
MYSQL Replication breaks because of lack of AUTO_INCREMENT PK in cache_pagesection
0%
Description
Hi,
recently I discovered that in a high load environment with multiple replicating MYSQL hosts the replication tends to break beacuase of lack of an auto incrementing primary key in the table cache_pagesection.
Here is my solution:
ALTER TABLE cache_pagesection DROP PRIMARY KEY;
CREATE TABLE cache_pagesection (
uid int(11) NOT NULL auto_increment PRIMARY KEY,
KEY pageid_mphash (page_id,mpvar_hash),
PRIMARY KEY,
);
Tho only problem seems to be a limitaiton of the SQL compare script, which is unable to handle PK Changes.
best regards,
Lukas Taferner
(issue imported from #M10508)
Updated by Peter K over 15 years ago
We ran into the same problem. We also had to alter the realurl-tables (#0010496). Do you have any clue how muchf these changes affects performance?
Updated by Lukas Taferner over 15 years ago
Hi Peter,
not much of an performance issue as far as mysql is concerned . . but lots of advantages with clearing the cache across the cluster.
Seems like they added auto_increment PK's in 4.3 for cache_pages and cache_pagesections but not for cache_treelist (whatever this table is for). I will post another feature request for 4.3
regards,
Lukas
Updated by Christian Kuhn over 15 years ago
Resolved, fixed in 4.3:
CREATE TABLE cache_pagesection (
id int(11) unsigned NOT NULL auto_increment,
identifier varchar(250) DEFAULT '' NOT NULL,
crdate int(11) unsigned DEFAULT '0' NOT NULL,
content mediumtext,
tags mediumtext,
lifetime int(11) unsigned DEFAULT '0' NOT NULL,
PRIMARY KEY (id),
KEY cache_id (identifier)
) ENGINE=InnoDB;
Updated by Oliver Hader over 15 years ago
When was this fixed in 4.3?
The issue was initially reported for 4.2.6 and not for 4.3... hm...
Updated by Christian Kuhn over 15 years ago
This was indirectly fixed by Ingo with #19427 in rev. 4336 for the new 4.3 caching framework.
A fix for 4.2 would probably not make it to the core as a unwanted db update would be required in bugfix releases.
Current 4.2 definition: #- Table structure for table 'cache_pagesection'
#
CREATE TABLE cache_pagesection (
page_id int(11) unsigned DEFAULT '0' NOT NULL,
mpvar_hash int(11) unsigned DEFAULT '0' NOT NULL,
content blob,
tstamp int(11) unsigned DEFAULT '0' NOT NULL,
PRIMARY KEY (page_id,mpvar_hash)
) ENGINE=InnoDB;
Updated by Lukas Taferner over 15 years ago
I'm not sure if this is really fixed:
PRIMARY KEY (page_id,mpvar_hash)
Imagine 2 MySQL hosts with the same HTTP request at the same time. They will write the same record in the table and will try to replicate -> collision and broken replication, just a matter of time. Please insert an auto_increment UID which can be declared with an offset differing for every MySQL Host
Updated by Christian Kuhn over 15 years ago
Thanks for feedback, Lukas!
Ok, maybe I wasn't precise enough:
The current faulty implementation in 4.2 with "PRIMARY KEY (page_id,mpvar_hash)" will probably not be fixed for 4.2.x as this would require a db update for minor releases, which is only feasible for major bugs in stable versions. I don't consider this a critical issue, but only a minor glitch in special environments, that shouldn't force administrators to change db settings for minor release upgrades. Ingo as release manger of 4.2 would also probably not let a corresponding fix get into 4.2 branch (you could try anyway, though). We didn't have any db changes in 4.2.x until now.
But: The implementation for 4.3 (current trunk) fixes this with:
id int(11) unsigned NOT NULL auto_increment,
PRIMARY KEY (id),
so the issue is solved for 4.3, or did I miss something? If your issue is still valid in current trunk (aka 4.3) for some reason we will try to fix it!
Sum up:
No db changes for non-critical bugs in minor versions of stable releases (here: 4.2), but allowed fixing in upcoming next major stable version (aka 4.3).
So (if I didn't miss something) this issue is a "won't fix" for 4.2 and a "already fixed" for 4.3.