|
Joël LEGER
|
Thursday 27 August 2009 7:22:41 am
Hi,
version:4.1.3
Cluster mode
I have a regular problem (recently)
example :
Error: _endCacheGeneration( var/client/cache/template-block/1/2/4/1242146676.cache )
Failed to execute SQL for function:
1062: Duplicate entry 'a1e80d95fa90890ea8b43319ab9fc150-0' for key 1
result it's very slow
|
|
Bertrand Dunogier
|
Thursday 27 August 2009 11:30:42 pm
Could you provide me with:
1) your settings/override/file.ini.append.php file
2) the definition for the cluster tables, ezdbfile and ezdbfile_data (SHOW CREATE TABLE ezdbfile & SHOW CREATE TABLE ezdbfile_data). I have a feeling you're missing the ON DELETE CASCADE trigger from ezdbfile => ezdbfile_data.
Bertrand Dunogier
eZ Systems Engineering, Lyon
http://twitter.com/bdunogier
http://gplus.to/BertrandDunogier
|
|
Joël LEGER
|
Friday 28 August 2009 12:23:15 am
yes sure settings/override/file.ini.append.php :
[ClusteringSettings]
FileHandler=eZDBFileHandler
DBBackend=eZDBFileHandlerMysqlBackend
DBHost=localhost
DBPort=3306
DBSocket=
DBName=client
DBUser=client
DBPassword=pasword
DBChunkSize=65535
DBConnectRetries=3
DBExecuteRetries=20
Tables definition :
CREATE TABLE `ezdbfile` (
`datatype` varchar(60) NOT NULL default 'application/octet-stream',
`name` text NOT NULL,
`name_trunk` text NOT NULL,
`name_hash` varchar(34) NOT NULL default '',
`scope` varchar(20) NOT NULL default '',
`size` bigint(20) unsigned NOT NULL,
`mtime` int(11) NOT NULL default '0',
`expired` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`name_hash`),
KEY `ezdbfile_name` (`name`(250)),
KEY `ezdbfile_name_trunk` (`name_trunk`(250)),
KEY `ezdbfile_mtime` (`mtime`),
KEY `ezdbfile_expired_name` (`expired`,`name`(250))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `ezdbfile_data` (
`name_hash` varchar(34) NOT NULL default '',
`offset` int(11) unsigned NOT NULL,
`filedata` blob NOT NULL,
PRIMARY KEY (`name_hash`,`offset`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
|
|
Bertrand Dunogier
|
Friday 28 August 2009 3:29:44 am
Yes, that's what I thought. There should be a trigger on ezdbfile_data: this trigger automatically deletes records in ezdbfile_data (blobs that are the actual content) when an entry is removed in ezdbfile. This is what you should have get:
CREATE TABLE ezdbfile_data (
name_hash VARCHAR(34) NOT NULL DEFAULT '',
offset INT(11) UNSIGNED NOT NULL,
filedata BLOB NOT NULL,
PRIMARY KEY (name_hash, offset),
CONSTRAINT ezdbfile_fk1 FOREIGN KEY (name_hash) REFERENCES ezdbfile (name_hash) ON DELETE CASCADE
) ENGINE=InnoDB;
To fix it, just drop the table and recreate it, or run this:
ALTER TABLE ezdbfile_data ADD CONSTRAINT ezdbfile_fk1 FOREIGN KEY (name_hash) REFERENCES ezdbfile (name_hash) ON DELETE CASCADE;
Bertrand Dunogier
eZ Systems Engineering, Lyon
http://twitter.com/bdunogier
http://gplus.to/BertrandDunogier
|
|
Joël LEGER
|
Monday 31 August 2009 7:00:26 am
thanks Bertrand for you feedback.
There is a sensitive problem :
There is data in the ezdbfile_data that i can't remove , do you think if i export data , remove the table, create the table with the constraint and import data again will be ok ?
|
|
Bertrand Dunogier
|
Monday 31 August 2009 7:27:34 am
Well, in any case, backup both tables somewhere. You never know. I hope you have enough disk space somewhere ;) Then first try to add the constraint using the ALTER TABLE query. It should work, imho.
Then if this doesn't work, the cleanest way:
- unclusterize your cluster using php bin/php/clusterize.php -u
- drop & recreate the tables with the correct definition
- clusterize again (php bin/php/clusterize.php).
Bertrand Dunogier
eZ Systems Engineering, Lyon
http://twitter.com/bdunogier
http://gplus.to/BertrandDunogier
|