Manual talk:Revision table

Relationship between tables

Trying to understand how some of the tables relate (Page,Archive,Revision,Text)... Is a current page's content also stored in the text table ? even though the field prefix is old_ ? — Preceding unsigned comment added by [[User:{{{1}}}|{{{1}}}]] ([[User talk:{{{1}}}|talk]] • [[Special:Contributions/{{{1}}}|contribs]])

Yes, the content (current and past) is stored in text table. "Field names are a holdover from the 'old' revisions table in MediaWiki 1.4 and earlier" (tables.sql). — Preceding unsigned comment added by [[User:{{{1}}}|{{{1}}}]] ([[User talk:{{{1}}}|talk]] • [[Special:Contributions/{{{1}}}|contribs]])

The pages (their names, their namespace, number of page hits and so on) are stored in the page table and the texts are in the text table. The revision table now links the entries in the pages table to certain texts in the text table. The archive table btw. does the same (the same as the revision table), but for deleted revisions. --88.130.83.1 02:24, 28 February 2014 (UTC)

Problem with import wiki to MySQL

I have a dump of Simple Wikipedia of 9.9.2007 and MediaWiki version 1.11. Table revision in Simple WP has 9 fields, in MediaWiki - 11 fields.

So, when I have tried upload, I have got an error:

• mysql> source /simple20070909/revision.sql
• ERROR 1136 (21S01): Column count doesn't match value count at row 1

Remove two fields, source..., then add them back:

• mysql> ALTER TABLE revision DROP COLUMN rev_len, DROP COLUMN rev_parent_id;
• mysql> source /simple20070909/revision.sql
• mysql> ALTER TABLE revision ADD COLUMN rev_len INT(10) UNSIGNED DEFAULT NULL AFTER rev_deleted, ADD COLUMN rev_parent_id INT(10) UNSIGNED DEFAULT NULL AFTER rev_len;

If two fields were not added, then MediaWiki generates an error:

• from within function "Revision::fetchRow". MySQL returned error "1054: Unknown column 'rev_len' in 'field list' (localhost)".

--AKA MBG 11:48, 11 September 2007 (UTC)

Hostnames

There's an old discussion here: m:Talk:Privacy_policy/Archives/2003#Lookups. Apparently they were still shown on some Wiipedias in 2003. --Nemo 10:58, 18 August 2010 (UTC)

rev_sha1

Hi! I updated my wiki, but it doesn't show the actually pagecontent. As I've seen in the database, I recognized that the sha1-row isn't filled in in the newer revisions. How can I fill the rows? Sorry for my english. Arc96 (talk) 15:26, 7 July 2012 (UTC)

Hello, there's a maintenance script for it. It's not listed in the page yet but the name is quite descriptive, populate<something>.php. Nemo 21:50, 10 July 2012 (UTC)

The option "--force" might be helpful, if the script says, the values had already been calculated. Should that not help, you can set the column to empty string ("") or to NULL (in case of the rev_len column. After that the according maintenance script will update the column nicely. --88.130.83.1 02:20, 28 February 2014 (UTC)

Indices

Is this style okay, or do we need to use a wikitable? E.g.:

Indices for revision table

Index	Fields
PRIMARY	rev_id
rev_page_id	rev_page, rev_id
rev_timestamp	rev_timestamp
page_timestamp	rev_page, rev_timestamp
user_timestamp	rev_user, rev_timestamp
usertext_timestamp	rev_user_text, rev_timestamp
page_user_timestamp	rev_page, rev_user, rev_timestamp

Indices for revision table

Index	Field 1	Field 2	Field 3
PRIMARY	rev_id
rev_page_id	rev_page	rev_id
rev_timestamp	rev_timestamp
page_timestamp	rev_page	rev_timestamp
user_timestamp	rev_user	rev_timestamp
usertext_timestamp	rev_user_text	rev_timestamp
page_user_timestamp	rev_page	rev_user	rev_timestamp

The "show index in revision" format is easier to maintain, but doesn't tell you on one row what fields pertain to an index. Leucosticte (talk) 20:53, 4 November 2012 (UTC)

Manual:Revision_table#rev_deleted

Would someone care to provide more info about the possible values of rev_deleted? Leucosticte (talk) 10:03, 13 December 2013 (UTC)

"0" is set for basically every row and obviously stands for "not deleted". So my first guess would have been that possible values would be this "0" and "1" for "deleted". However, in that case it would have been enough to define the column as BIT or as TINYINT(1), but it in fact is defined as TINYINT(3), which makes me unsure, whether my thoughts are correct and whether "deleted"/"not deleted" is all to be set there... --88.130.92.144 04:46, 22 February 2014 (UTC)

I should state that I'm no expert in the MediaWiki code, but I believe these few lines from Revision.php give us our answer:

const DELETED_TEXT = 1;
const DELETED_COMMENT = 2;
const DELETED_USER = 4;
const DELETED_RESTRICTED = 8;

– RobinHood70 ^talk 05:31, 23 February 2014 (UTC)

I experimented a bit and found this is indeed how it works. E.g., I revisiondeleted the comment, and it changed from 0 to 2; then I revisiondeleted the user as well, and it changed to 6. Leucosticte (talk) 07:38, 23 February 2014 (UTC)

Page creation

Is there any way of checking if a rev was a page creation? πr² (t • c) 16:09, 24 February 2014 (UTC)

rev_parent_id=0 ? πr² (t • c) 16:15, 24 February 2014 (UTC)

What's the use case? Depending on the situation, one might be able to get that data from recentchanges.rc_new. Leucosticte (talk) 17:08, 24 February 2014 (UTC)

Yes, rev_parent_id always contains the number of the previous revision. In case of new pages, its value is "0". The recentchanges table might only be of limited help as it only contains the newest x days of data (by default the newest 90 days). Older information will not be there. --88.130.83.1 02:29, 28 February 2014 (UTC)

What about pre-v1.10 revisions; I guess those would be NULL, huh, or else would have been set to their proper values (i.e. "0" for page creations, or the correct parent_id in other cases)? So, those wouldn't be a problem.

Also, it's an unindexed field; depending on the use case, that might be a problem. From the way he worded his question, though, it sounds like he's not putting it in the WHERE clause, so I guess it wouldn't be a problem. Yeah, I guess there's no problem with using that field as he suggests. Leucosticte (talk) 04:12, 28 February 2014 (UTC)

I have just checked that in one of my installations and there the parent_rev_id field is filled also for very old revisions from way before 1.10. There is a maintenance script, populateParentId.php, which can be used to populate the column. Whether this script was executed automatically during an upgrade or if it had to be used manually afterwards, I don't know. However, often maintenance scripts are executed automatically during update, so I assume that most likely was the case here as well. --88.130.103.75 15:37, 2 March 2014 (UTC)

Yes, there's a MySqlUpdater::doPopulateParentId() that runs that script as a child. Leucosticte (talk) 04:42, 4 March 2014 (UTC)

rev_user_text

It says, "In anonymous revisions imported from UseModWiki or early incarnations of the Phase II software, this field may contain an IP address with the final octet obscured (i.e. \d{1,3}\.\d{1,3}\.\d{1,3}\.xxx such as 24.150.61.xxx; see bug 3631). Some edits imported from UseModWiki may contain a Reverse DNS lookup hostname like ppfree165-153-bz.aknet.it or office.bomis.com." Does this belong in a page that pertains to the revision table on MediaWiki installations in general (not just Wikipedia)? Leucosticte (talk) 10:34, 6 August 2014 (UTC)

rev_parent_id

With regard to revision.rev_parent_id, "This field is used to add support for a tree structure (The Adjacency List Model)", what purpose would a tree structure serve, besides calculating the size difference of a certain revision with the previous revision in the page history view? Leucosticte (talk) 14:11, 1 October 2014 (UTC)

Not sure if this is default behavior or caused by an extension: rev_parent_id is the same for two different revision records when a version is imported from another wiki. Can someone validate this? Thanks! Magalabastro (talk) 19:44, 12 July 2018 (UTC)

rev_bot

Why is there no rev_bot? Leucosticte (talk) 18:48, 1 October 2014 (UTC)

rev_len and rc_old_len

Why don't we store old_len in the revision table as well? It's pretty useful in historical digging and might be useful also for action=history. Can someone point to past discussions about it? Should a feature request be filed? --Nemo 15:05, 24 May 2015 (UTC)

rev_actor field missing?

Based on the description at Manual:Revision_table#rev_user it seems like there should be a rev_actor field listed for this table. I would add it, but IHaveNoIdeaWhatImDoing.jpg Colin M (talk) 14:39, 15 July 2019 (UTC)