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_ ?


 * 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).


 * 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: --AKA MBG 11:48, 11 September 2007 (UTC)
 * from within function "Revision::fetchRow". MySQL returned error "1054: Unknown column 'rev_len' in 'field list' (localhost)".

Hostnames
There's an old discussion here: m:Talk:Privacy_policy/Archives/2003. 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 .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.:

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
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:
 * – 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)
 * 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? πr2 (t • c) 16:09, 24 February 2014 (UTC)
 * rev_parent_id=0 ? πr2 (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 . 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, "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)

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