2

I'm investigating an issue on a D7 site where a handful of node revisions have apparently gone missing. The issue was discovered on 3/5, and I just noticed in my node_revision table that several records were created on 3/4 with the same uid and the same timestamp (1362422613). How is this possible? Might it indicate a MySQL problem?

0

DISCLAIMER : Not a Drupal Developer, just a pure MySQL DBA

Here is a sample node_revision table:

mysql> show create table node_revisions\G
*************************** 1. row ***************************
       Table: node_revisions
Create Table: CREATE TABLE `node_revisions` (
  `nid` int(10) unsigned NOT NULL DEFAULT '0',
  `vid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL DEFAULT '0',
  `title` varchar(255) NOT NULL DEFAULT '',
  `body` longtext NOT NULL,
  `teaser` longtext NOT NULL,
  `log` longtext NOT NULL,
  `timestamp` int(11) NOT NULL DEFAULT '0',
  `format` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`vid`),
  KEY `nid` (`nid`),
  KEY `uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=227825 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

Strictly in terms of MySQL, there is nothing in place to prevent the same UID and timestamp from entering this table multiple times. The index uid is a non-unique index on the uid column. What you need is a UNIQUE INDEX to prevent this at the database level.

Run this query:

SELECT uid,timestamp,COUNT(1) ut_dups FROM node_revisions
GROUP BY uid,timestamp HAVING COUNT(1) > 1;

and this will uncover other UIDs with the same timestamp issue.

If you want to block rows from entering this table with duplicate (uid,timestamp), change the indexes as follows (making a backup first):

#
# Backup node_revision
#
CREATE TABLE node_revision_backup LIKE node_revision;
INSERT INTO node_revision_backup SELECT * FROM node_revision;
#
# Change Indexes
#
CREATE TABLE node_revision_new LIKE node_revision;
ALTER TABLE node_revision_new DROP INDEX uid;
ALTER TABLE node_revision_new ADD UNIQUE INDEX uniq_uid_timestamp (uid,timestamp);
INSERT IGNORE INTO node_revision_new SELECT * FROM node_revision;
DROP TABLE node_revision;
ALTER TABLE node_revision_new RENAME node_revision;

The UNIQUE INDEX uniq_uid_timestamp physically prevents (uid,timestamp) from appearing more than once. Check the contents of node_revision and make sure it is proper.

If it looks good, you could run:

DROP TABLE node_revision_backup;

or keep the backup.

If it does not look good, run this:

ALTER TABLE node_revision RENAME node_revision_old;
ALTER TABLE node_revision_backup RENAME node_revision;
DROP TABLE node_revision_old;

Give it a Try !!!

CAVEAT

You will need to check all modules involved that writes to the node_revision table.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.