Bug #20065

MYSQL Replication breaks because of lack of AUTO_INCREMENT PK in cache_pagesection

Added by Lukas Taferner about 11 years ago. Updated over 9 years ago.

Status:
Closed
Priority:
Should have
Category:
-
Target version:
-
Start date:
2009-02-20
Due date:
% Done:

0%

TYPO3 Version:
4.2
PHP Version:
5.2
Tags:
Complexity:
Is Regression:
Sprint Focus:

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)


Related issues

Related to TYPO3 Core - Bug #20502: MYSQL Replication breaks because of lack of AUTO_INCREMENT PK in cache_treesection Closed 2009-05-25

History

#1 Updated by Peter K about 11 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?

#2 Updated by Lukas Taferner almost 11 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

#3 Updated by Christian Kuhn over 10 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;

#4 Updated by Oliver Hader over 10 years ago

When was this fixed in 4.3?
The issue was initially reported for 4.2.6 and not for 4.3... hm...

#5 Updated by Christian Kuhn over 10 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: #
  1. 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;

#6 Updated by Lukas Taferner over 10 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

#7 Updated by Christian Kuhn over 10 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.

Also available in: Atom PDF