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?
DISCLAIMER : Not a Drupal Developer, just a pure MySQL DBA
Here is a sample
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 !!!
You will need to check all modules involved that writes to the