https://wiki.alpinelinux.org/w/index.php?title=Converting_mediawiki_database_from_postgresql_to_mysql/mariadb&feed=atom&action=history
Converting mediawiki database from postgresql to mysql/mariadb - Revision history
2024-03-28T22:33:09Z
Revision history for this page on the wiki
MediaWiki 1.40.0
https://wiki.alpinelinux.org/w/index.php?title=Converting_mediawiki_database_from_postgresql_to_mysql/mariadb&diff=24690&oldid=prev
Sertonix: use https
2023-08-25T21:24:12Z
<p>use https</p>
<table style="background-color: #fff; color: #202122;" data-mw="interface">
<col class="diff-marker" />
<col class="diff-content" />
<col class="diff-marker" />
<col class="diff-content" />
<tr class="diff-title" lang="en">
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← Older revision</td>
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">Revision as of 21:24, 25 August 2023</td>
</tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l17">Line 17:</td>
<td colspan="2" class="diff-lineno">Line 17:</td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>Mediawiki postgresql version is quite different from mysql version. For example: some table names are different, field order, timestamp format (pg: '2015-11-01 08:42:31+00', mysql: '20151101084231'), many fields that are NULL in postgresql are not allowed to be NULL in mysql. That is why there will be issues when dumping data from postgresql and importing it to mysql.<br></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>Mediawiki postgresql version is quite different from mysql version. For example: some table names are different, field order, timestamp format (pg: '2015-11-01 08:42:31+00', mysql: '20151101084231'), many fields that are NULL in postgresql are not allowed to be NULL in mysql. That is why there will be issues when dumping data from postgresql and importing it to mysql.<br></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><br></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><br></div></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>There is a script mentioned in <del style="font-weight: bold; text-decoration: none;">http</del>://www.winterrodeln.org/trac/wiki/MediaWikiPostgresqlToMysql that should be converting postgresql database to mysql. However in my case it did not work because of the issues mentioned above (that script was written in 2009-01-16).</div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>There is a script mentioned in <ins style="font-weight: bold; text-decoration: none;">https</ins>://www.winterrodeln.org/trac/wiki/MediaWikiPostgresqlToMysql that should be converting postgresql database to mysql. However in my case it did not work because of the issues mentioned above (that script was written in 2009-01-16).</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>==Mediawiki location==</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>==Mediawiki location==</div></td></tr>
<tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l459">Line 459:</td>
<td colspan="2" class="diff-lineno">Line 459:</td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>==Setting up new mediawiki==</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>==Setting up new mediawiki==</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>{{Cmd|<nowiki>cd /var/www/wiki.wikiname.org/htdocs</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>{{Cmd|<nowiki>cd /var/www/wiki.wikiname.org/htdocs</div></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>wget <del style="font-weight: bold; text-decoration: none;">http</del>://releases.wikimedia.org/mediawiki/1.25/mediawiki-1.25.3.tar.gz</div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>wget <ins style="font-weight: bold; text-decoration: none;">https</ins>://releases.wikimedia.org/mediawiki/1.25/mediawiki-1.25.3.tar.gz</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>tar zxf mediawiki-1.25.3.tar.gz</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>tar zxf mediawiki-1.25.3.tar.gz</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>mv mediawiki-1.25.3 w</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>mv mediawiki-1.25.3 w</div></td></tr>
<tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l471">Line 471:</td>
<td colspan="2" class="diff-lineno">Line 471:</td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>?></nowiki></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>?></nowiki></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>Open your wiki in browser <del style="font-weight: bold; text-decoration: none;">http</del>://wiki.wikiname.org, go through setup process (DB type:mysql, engin: innodb, charset: binary)<br></div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>Open your wiki in browser <ins style="font-weight: bold; text-decoration: none;"><nowiki>https</ins>://wiki.wikiname.org<ins style="font-weight: bold; text-decoration: none;"></nowiki></ins>, go through setup process (DB type:mysql, engin: innodb, charset: binary)<br></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>Compare LocalSettings.php file generated by setup process with old mediawiki LocalSettings.php file<br></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>Compare LocalSettings.php file generated by setup process with old mediawiki LocalSettings.php file<br></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>Take needed settings from old LocalSettings.php<br></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>Take needed settings from old LocalSettings.php<br></div></td></tr>
</table>
Sertonix
https://wiki.alpinelinux.org/w/index.php?title=Converting_mediawiki_database_from_postgresql_to_mysql/mariadb&diff=24309&oldid=prev
Zcrayfish: Removed RfD: at least one other page links here. No RfD discussion whatsoever on talk page.
2023-08-14T15:50:44Z
<p>Removed RfD: at least one other page links here. No RfD discussion whatsoever on talk page.</p>
<table style="background-color: #fff; color: #202122;" data-mw="interface">
<col class="diff-marker" />
<col class="diff-content" />
<col class="diff-marker" />
<col class="diff-content" />
<tr class="diff-title" lang="en">
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← Older revision</td>
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">Revision as of 15:50, 14 August 2023</td>
</tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l1">Line 1:</td>
<td colspan="2" class="diff-lineno">Line 1:</td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><del style="font-weight: bold; text-decoration: none;">{{delete|The choice of database, as well as the process of conversion, are not specific or unique to Alpine, and are probably best documented by the MediaWiki project itself.}}</del></div></td><td colspan="2" class="diff-side-added"></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>==Why to use mysql/mariadb instead of postgresql?==</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>==Why to use mysql/mariadb instead of postgresql?==</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
</table>
Zcrayfish
https://wiki.alpinelinux.org/w/index.php?title=Converting_mediawiki_database_from_postgresql_to_mysql/mariadb&diff=22775&oldid=prev
Arrogance at 03:09, 14 December 2022
2022-12-14T03:09:08Z
<p></p>
<table style="background-color: #fff; color: #202122;" data-mw="interface">
<col class="diff-marker" />
<col class="diff-content" />
<col class="diff-marker" />
<col class="diff-content" />
<tr class="diff-title" lang="en">
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← Older revision</td>
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">Revision as of 03:09, 14 December 2022</td>
</tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l1">Line 1:</td>
<td colspan="2" class="diff-lineno">Line 1:</td></tr>
<tr><td colspan="2" class="diff-side-deleted"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">{{delete|The choice of database, as well as the process of conversion, are not specific or unique to Alpine, and are probably best documented by the MediaWiki project itself.}}</ins></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>==Why to use mysql/mariadb instead of postgresql?==</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>==Why to use mysql/mariadb instead of postgresql?==</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
</table>
Arrogance
https://wiki.alpinelinux.org/w/index.php?title=Converting_mediawiki_database_from_postgresql_to_mysql/mariadb&diff=13833&oldid=prev
John3-16: Added See also: MariaDB; Categorized:SQL
2017-09-17T06:04:20Z
<p>Added See also: MariaDB; Categorized:SQL</p>
<table style="background-color: #fff; color: #202122;" data-mw="interface">
<col class="diff-marker" />
<col class="diff-content" />
<col class="diff-marker" />
<col class="diff-content" />
<tr class="diff-title" lang="en">
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← Older revision</td>
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">Revision as of 06:04, 17 September 2017</td>
</tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l509">Line 509:</td>
<td colspan="2" class="diff-lineno">Line 509:</td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>php maintenance/rebuildLocalisationCache.php</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>php maintenance/rebuildLocalisationCache.php</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div></nowiki>}}</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div></nowiki>}}</div></td></tr>
<tr><td colspan="2" class="diff-side-deleted"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></ins></div></td></tr>
<tr><td colspan="2" class="diff-side-deleted"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">==See also==</ins></div></td></tr>
<tr><td colspan="2" class="diff-side-deleted"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">* [[MariaDB]]</ins></div></td></tr>
<tr><td colspan="2" class="diff-side-deleted"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></ins></div></td></tr>
<tr><td colspan="2" class="diff-side-deleted"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">[[Category:SQL]]</ins></div></td></tr>
</table>
John3-16
https://wiki.alpinelinux.org/w/index.php?title=Converting_mediawiki_database_from_postgresql_to_mysql/mariadb&diff=13792&oldid=prev
John3-16: Added internal link to MediaWiki
2017-09-16T04:26:53Z
<p>Added internal link to MediaWiki</p>
<table style="background-color: #fff; color: #202122;" data-mw="interface">
<col class="diff-marker" />
<col class="diff-content" />
<col class="diff-marker" />
<col class="diff-content" />
<tr class="diff-title" lang="en">
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← Older revision</td>
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">Revision as of 04:26, 16 September 2017</td>
</tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l1">Line 1:</td>
<td colspan="2" class="diff-lineno">Line 1:</td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>==Why to use mysql/mariadb instead of postgresql?==</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>==Why to use mysql/mariadb instead of postgresql?==</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>Here is what MediaWiki says [[https://www.mediawiki.org/wiki/Manual:PostgreSQL]]:</div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>Here is what <ins style="font-weight: bold; text-decoration: none;">[[</ins>MediaWiki<ins style="font-weight: bold; text-decoration: none;">]] </ins>says [[https://www.mediawiki.org/wiki/Manual:PostgreSQL]]:</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><blockquote>Most of the common maintenance scripts work with PostgreSQL however some of the more obscure ones might have problems.<br></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><blockquote>Most of the common maintenance scripts work with PostgreSQL however some of the more obscure ones might have problems.<br></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>Is PostgreSQL a good choice for MediaWiki?<br></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>Is PostgreSQL a good choice for MediaWiki?<br></div></td></tr>
</table>
John3-16
https://wiki.alpinelinux.org/w/index.php?title=Converting_mediawiki_database_from_postgresql_to_mysql/mariadb&diff=11470&oldid=prev
Slukin: /* Preparing mysql database and wiki user */
2015-11-18T06:11:05Z
<p><span dir="auto"><span class="autocomment">Preparing mysql database and wiki user</span></span></p>
<table style="background-color: #fff; color: #202122;" data-mw="interface">
<col class="diff-marker" />
<col class="diff-content" />
<col class="diff-marker" />
<col class="diff-content" />
<tr class="diff-title" lang="en">
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← Older revision</td>
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">Revision as of 06:11, 18 November 2015</td>
</tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l444">Line 444:</td>
<td colspan="2" class="diff-lineno">Line 444:</td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>{{Cmd|vi ipblocks.sql}}</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>{{Cmd|vi ipblocks.sql}}</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>==<del style="font-weight: bold; text-decoration: none;">Preparing </del>mysql <del style="font-weight: bold; text-decoration: none;">database and wiki </del>user ==</div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>==<ins style="font-weight: bold; text-decoration: none;">Changing </ins>mysql <ins style="font-weight: bold; text-decoration: none;">root password, creating </ins>user <ins style="font-weight: bold; text-decoration: none;">for mediawiki</ins>==</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>{{Cmd|<nowiki>mysql -u root</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>{{Cmd|<nowiki>mysql -u root</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpassword');</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpassword');</div></td></tr>
</table>
Slukin
https://wiki.alpinelinux.org/w/index.php?title=Converting_mediawiki_database_from_postgresql_to_mysql/mariadb&diff=11460&oldid=prev
Slukin: Created page with "==Why to use mysql/mariadb instead of postgresql?== Here is what MediaWiki says https://www.mediawiki.org/wiki/Manual:PostgreSQL: <blockquote>Most of the common maintenan..."
2015-11-17T13:46:09Z
<p>Created page with "==Why to use mysql/mariadb instead of postgresql?== Here is what MediaWiki says <a href="/w/index.php?title=Https://www.mediawiki.org/wiki/Manual:PostgreSQL&action=edit&redlink=1" class="new" title="Https://www.mediawiki.org/wiki/Manual:PostgreSQL (page does not exist)">https://www.mediawiki.org/wiki/Manual:PostgreSQL</a>: <blockquote>Most of the common maintenan..."</p>
<p><b>New page</b></p><div>==Why to use mysql/mariadb instead of postgresql?==<br />
<br />
Here is what MediaWiki says [[https://www.mediawiki.org/wiki/Manual:PostgreSQL]]:<br />
<blockquote>Most of the common maintenance scripts work with PostgreSQL however some of the more obscure ones might have problems.<br><br />
Is PostgreSQL a good choice for MediaWiki?<br><br />
Wikipedia uses MySQL so MediaWiki gets more testing on MySQL than PostgreSQL. While support for PostgreSQL is maintained by volunteers, most core functionality is working.</blockquote><br />
<br />
==Challenge==<br />
====Why not to use dumpBackup.php script in mediawiki? [[https://www.mediawiki.org/wiki/Manual:DumpBackup.php]]====<br />
If you need to save only mediawiki data and pages modify history then the best and easiest way probably would be:<br />
{{Cmd|<nowiki>php maintenance/dumpBackup.php --full --include-files --uploads > /tmp/wiki.xml<br />
php maintenance/importDump.php /tmp/wiki.xml<br />
</nowiki>}}<br />
However user accounts, user contrubutions, logs, etc will not be migrated this way.<br><br />
<br />
====Why not to use tools for converting postgresql databases to mysql?====<br />
Mediawiki postgresql version is quite different from mysql version. For example: some table names are different, field order, timestamp format (pg: '2015-11-01 08:42:31+00', mysql: '20151101084231'), many fields that are NULL in postgresql are not allowed to be NULL in mysql. That is why there will be issues when dumping data from postgresql and importing it to mysql.<br><br />
<br><br />
There is a script mentioned in http://www.winterrodeln.org/trac/wiki/MediaWikiPostgresqlToMysql that should be converting postgresql database to mysql. However in my case it did not work because of the issues mentioned above (that script was written in 2009-01-16).<br />
<br />
==Mediawiki location==<br />
In this example mediawiki files root is located in '''/var/www/wiki.wikiname.org/htdocs/w'''<br><br />
Web server is '''lighttpd'''. /etc/lighttpd/lighttpd.conf contains lines:<br />
{{Cmd|<nowiki>simple-vhost.server-root = "/var/www"<br />
simple-vhost.default-host = "wiki.wikiname.org"<br />
simple-vhost.document-root = "/htdocs/"<br />
url.rewrite-once = (<br />
"^/wiki/([^?]*)(?:\?(.*))?" => "/w/index.php?title=$1&$2",<br />
"^/wiki" => "/w/index.php",<br />
)<br />
</nowiki>}}<br />
<br />
==Disallowing editing mediawiki until database is converted==<br />
{{Cmd|vi /var/www/wiki.wikiname.org/htdocs/w/LocalSettings.php}}<br />
<nowiki>$wgReadOnly = 'This wiki is currently being upgraded to a newer software version.';</nowiki><br />
<br />
==Preparing dump files for importing data to mysql==<br />
====Making postgresql dump====<br />
{{Cmd|<nowiki>su - postgres<br />
pg_dump wikidb > /tmp/wikidb-pg.dump<br />
exit<br />
</nowiki>}}<br />
<br />
====Making temporary postgresql database looking like mysql====<br />
{{Cmd|<nowiki>su - postgres<br />
echo "CREATE DATABASE tmpdb ENCODING 'SQL_ASCII' LC_COLLATE='C' LC_CTYPE='C' TEMPLATE=template0;" | psql<br />
psql tmpdb < /tmp/wikidb-pg.dump<br />
exit<br />
</nowiki>}}<br />
<br />
====SQL script modifying postgresql database====<br />
For avoiding issues when importing data to mysql we need to modify mediawiki postgresql database so that it would look like mediawiki mysql database. Basically the script will make field order in pg tables like in mysql and will set non-NULL values to the fields that are not allowed to be NULL in mysql<br><br />
'''/tmp/pg-prepare-for-mysql.sql'''<br />
{{Cmd|<nowiki>/* archive */<br />
CREATE VIEW tmp_view AS<br />
select ar_id, ar_namespace, ar_title, ar_text, ar_comment, ar_user, ar_user_text, ar_timestamp, ar_minor_edit, ar_flags, ar_rev_id, ar_text_id, ar_deleted, ar_len, ar_page_id, ar_parent_id, ar_sha1, ar_content_model, ar_content_format FROM archive;<br />
SELECT * INTO tmp_table FROM tmp_view;<br />
DROP VIEW tmp_view;<br />
update tmp_table set ar_text = '' where ar_text is NULL;<br />
update tmp_table set ar_comment = '' where ar_comment is NULL;<br />
update tmp_table set ar_user = 0 where ar_user is NULL;<br />
update tmp_table set ar_flags = '' where ar_flags is NULL;<br />
ALTER TABLE archive RENAME TO orig_archive;<br />
ALTER TABLE tmp_table RENAME TO archive;<br />
<br />
/* category */<br />
CREATE VIEW tmp_view AS<br />
select cat_id, cat_title, cat_pages, cat_subcats, cat_files FROM category;<br />
SELECT * INTO tmp_table FROM tmp_view;<br />
DROP VIEW tmp_view;<br />
ALTER TABLE category RENAME TO orig_category;<br />
ALTER TABLE tmp_table RENAME TO category;<br />
<br />
/* categorylinks */<br />
CREATE VIEW tmp_view AS<br />
select cl_from, cl_to, cl_sortkey, cl_sortkey_prefix, cl_timestamp, cl_collation, cl_type FROM categorylinks;<br />
SELECT * INTO tmp_table FROM tmp_view;<br />
DROP VIEW tmp_view;<br />
update tmp_table set cl_sortkey = '' where cl_sortkey is NULL;<br />
ALTER TABLE categorylinks RENAME TO orig_categorylinks;<br />
ALTER TABLE tmp_table RENAME TO categorylinks;<br />
<br />
/* change_tag (OK) */<br />
<br />
/* externallinks */<br />
CREATE VIEW tmp_view AS<br />
select el_id, el_from, el_to, el_index FROM externallinks;<br />
SELECT * INTO tmp_table FROM tmp_view;<br />
DROP VIEW tmp_view;<br />
ALTER TABLE externallinks RENAME TO orig_externallinks;<br />
ALTER TABLE tmp_table RENAME TO externallinks;<br />
<br />
/* filearchive (OK) */<br />
<br />
/* image */<br />
CREATE VIEW tmp_view AS<br />
select img_name, img_size, img_width, img_height, img_metadata, img_bits, img_media_type, img_major_mime, img_minor_mime, img_description, img_user, img_user_text, img_timestamp, img_sha1 FROM image;<br />
SELECT * INTO tmp_table FROM tmp_view;<br />
DROP VIEW tmp_view;<br />
update tmp_table set img_bits = 0 where img_bits is NULL;<br />
update tmp_table set img_major_mime = '' where img_major_mime is NULL;<br />
update tmp_table set img_minor_mime = '' where img_minor_mime is NULL;<br />
ALTER TABLE image RENAME TO orig_image;<br />
ALTER TABLE tmp_table RENAME TO image;<br />
<br />
/* imagelinks */<br />
CREATE VIEW tmp_view AS<br />
select il_from, il_from_namespace, il_to FROM imagelinks;<br />
SELECT * INTO tmp_table FROM tmp_view;<br />
DROP VIEW tmp_view;<br />
ALTER TABLE imagelinks RENAME TO orig_imagelinks;<br />
ALTER TABLE tmp_table RENAME TO imagelinks;<br />
<br />
/* interwiki */<br />
CREATE VIEW tmp_view AS<br />
select iw_prefix, iw_url, iw_api, iw_wikiid, iw_local, iw_trans FROM interwiki;<br />
SELECT * INTO tmp_table FROM tmp_view;<br />
DROP VIEW tmp_view;<br />
ALTER TABLE interwiki RENAME TO orig_interwiki;<br />
ALTER TABLE tmp_table RENAME TO interwiki;<br />
<br />
/* ipblocks */<br />
CREATE VIEW tmp_view AS<br />
select ipb_id, ipb_address, ipb_user, ipb_by, ipb_by_text, ipb_reason, ipb_timestamp, ipb_auto, ipb_anon_only, ipb_create_account, ipb_enable_autoblock, ipb_expiry, ipb_range_start, ipb_range_end, ipb_deleted, ipb_block_email, ipb_allow_usertalk, ipb_parent_block_id FROM ipblocks;<br />
SELECT * INTO tmp_table FROM tmp_view;<br />
DROP VIEW tmp_view;<br />
update tmp_table set ipb_address = '' where ipb_address is NULL;<br />
update tmp_table set ipb_user = 0 where ipb_user is NULL;<br />
update tmp_table set ipb_range_start = '' where ipb_range_start is NULL;<br />
update tmp_table set ipb_range_end = '' where ipb_range_end is NULL;<br />
ALTER TABLE ipblocks RENAME TO orig_ipblocks;<br />
ALTER TABLE tmp_table RENAME TO ipblocks;<br />
<br />
/* iwlinks (OK) */<br />
<br />
/* job */<br />
CREATE VIEW tmp_view AS<br />
select job_id, job_cmd, job_namespace, job_title, job_timestamp, job_params, job_random, job_attempts, job_token, job_token_timestamp, job_sha1 FROM job;<br />
SELECT * INTO tmp_table FROM tmp_view;<br />
DROP VIEW tmp_view;<br />
ALTER TABLE job RENAME TO orig_job;<br />
ALTER TABLE tmp_table RENAME TO job;<br />
<br />
/* l10n_cache (OK) */<br />
<br />
/* langlinks */<br />
CREATE VIEW tmp_view AS<br />
select ll_from, ll_lang, ll_title FROM langlinks;<br />
SELECT * INTO tmp_table FROM tmp_view;<br />
DROP VIEW tmp_view;<br />
update tmp_table set ll_lang = '' where ll_lang is NULL;<br />
update tmp_table set ll_title = '' where ll_title is NULL;<br />
ALTER TABLE langlinks RENAME TO orig_langlinks;<br />
ALTER TABLE tmp_table RENAME TO langlinks;<br />
<br />
/* logging */<br />
CREATE VIEW tmp_view AS<br />
select log_id, log_type, log_action, log_timestamp, log_user, log_user_text, log_namespace, log_title, log_page, log_comment, log_params, log_deleted FROM logging;<br />
SELECT * INTO tmp_table FROM tmp_view;<br />
DROP VIEW tmp_view;<br />
update tmp_table set log_user = 0 where log_user is NULL;<br />
update tmp_table set log_comment = '' where log_comment is NULL;<br />
update tmp_table set log_params = '' where log_params is NULL;<br />
ALTER TABLE logging RENAME TO orig_logging;<br />
ALTER TABLE tmp_table RENAME TO logging;<br />
<br />
/* log_search (OK) */<br />
<br />
/* module_deps (OK) */<br />
<br />
/* msg_resource (OK) */<br />
<br />
/* msg_resource_links (OK) */<br />
<br />
/* objectcache */<br />
CREATE VIEW tmp_view AS<br />
select keyname, value, exptime FROM objectcache;<br />
SELECT * INTO tmp_table FROM tmp_view;<br />
DROP VIEW tmp_view;<br />
update tmp_table set keyname = '' where keyname is NULL;<br />
ALTER TABLE objectcache RENAME TO orig_objectcache;<br />
ALTER TABLE tmp_table RENAME TO objectcache;<br />
<br />
/* oldimage */<br />
CREATE VIEW tmp_view AS<br />
select oi_name, oi_archive_name, oi_size, oi_width, oi_height, oi_bits, oi_description, oi_user, oi_user_text, oi_timestamp, oi_metadata, oi_media_type, oi_major_mime, oi_minor_mime, oi_deleted, oi_sha1 FROM oldimage;<br />
SELECT * INTO tmp_table FROM tmp_view;<br />
DROP VIEW tmp_view;<br />
update tmp_table set oi_bits = 0 where oi_bits is NULL;<br />
update tmp_table set oi_description = '' where oi_description is NULL;<br />
update tmp_table set oi_user = 0 where oi_user is NULL;<br />
update tmp_table set oi_major_mime = '' where oi_major_mime is NULL;<br />
update tmp_table set oi_minor_mime = '' where oi_minor_mime is NULL;<br />
ALTER TABLE oldimage RENAME TO orig_oldimage;<br />
ALTER TABLE tmp_table RENAME TO oldimage;<br />
<br />
/* page */<br />
CREATE VIEW tmp_view AS<br />
select page_id, page_namespace, page_title, page_restrictions, page_is_redirect, page_is_new, page_random, page_touched, page_links_updated, page_latest, page_len, page_content_model, page_lang FROM page;<br />
SELECT * INTO tmp_table FROM tmp_view;<br />
DROP VIEW tmp_view;<br />
update tmp_table set page_restrictions = '' where page_restrictions is NULL;<br />
/*update tmp_table set page_touched = '' where page_touched is NULL;*/<br />
ALTER TABLE page RENAME TO orig_page;<br />
ALTER TABLE tmp_table RENAME TO page;<br />
<br />
/* pagelinks */<br />
CREATE VIEW tmp_view AS<br />
select pl_from, pl_from_namespace, pl_namespace, pl_title FROM pagelinks;<br />
SELECT * INTO tmp_table FROM tmp_view;<br />
DROP VIEW tmp_view;<br />
ALTER TABLE pagelinks RENAME TO orig_pagelinks;<br />
ALTER TABLE tmp_table RENAME TO pagelinks;<br />
<br />
/* page_props (OK) */<br />
<br />
/* page_restrictions */<br />
CREATE VIEW tmp_view AS<br />
select pr_id, pr_page, pr_type, pr_level, pr_cascade, pr_user, pr_expiry FROM page_restrictions;<br />
SELECT * INTO tmp_table FROM tmp_view;<br />
DROP VIEW tmp_view;<br />
ALTER TABLE page_restrictions RENAME TO orig_page_restrictions;<br />
ALTER TABLE tmp_table RENAME TO page_restrictions;<br />
<br />
/* protected_titles */<br />
CREATE VIEW tmp_view AS<br />
select pt_namespace, pt_title, pt_user, pt_reason, pt_timestamp, pt_expiry, pt_create_perm FROM protected_titles;<br />
SELECT * INTO tmp_table FROM tmp_view;<br />
DROP VIEW tmp_view;<br />
update tmp_table set pt_user = 0 where pt_user is NULL;<br />
/*update tmp_table set pt_expiry = '' where pt_expiry is NULL;*/<br />
ALTER TABLE protected_titles RENAME TO orig_protected_titles;<br />
ALTER TABLE tmp_table RENAME TO protected_titles;<br />
<br />
/* querycache (OK) */<br />
<br />
/* querycachetwo (OK) */<br />
<br />
/* querycache_info */<br />
CREATE VIEW tmp_view AS<br />
select qci_type, qci_timestamp FROM querycache_info;<br />
SELECT * INTO tmp_table FROM tmp_view;<br />
DROP VIEW tmp_view;<br />
update tmp_table set qci_type = '' where qci_type is NULL;<br />
/* update tmp_table set qci_timestamp = '' where qci_timestamp is NULL; */<br />
ALTER TABLE querycache_info RENAME TO orig_querycache_info;<br />
ALTER TABLE tmp_table RENAME TO querycache_info;<br />
<br />
/* recentchanges (rc_cur_time was removed in 1.24wmf6) */<br />
CREATE VIEW tmp_view AS<br />
select rc_id, rc_timestamp, rc_user, rc_user_text, rc_namespace, rc_title, rc_comment, rc_minor, rc_bot, rc_new, rc_cur_id, rc_this_oldid, rc_last_oldid, rc_type, rc_source, rc_patrolled, rc_ip, rc_old_len, rc_new_len, rc_deleted, rc_logid, rc_log_type, rc_log_action, rc_params FROM recentchanges;<br />
SELECT * INTO tmp_table FROM tmp_view;<br />
DROP VIEW tmp_view;<br />
update tmp_table set rc_user = 0 where rc_user is NULL;<br />
update tmp_table set rc_comment = '' where rc_comment is NULL;<br />
update tmp_table set rc_cur_id = 0 where rc_cur_id is NULL;<br />
/* update tmp_table set rc_ip = '' where rc_ip is NULL; */<br />
ALTER TABLE recentchanges RENAME TO orig_recentchanges;<br />
ALTER TABLE tmp_table RENAME TO recentchanges;<br />
<br />
/* redirect (OK) */<br />
<br />
/* revision */<br />
CREATE VIEW tmp_view AS<br />
select rev_id, rev_page, rev_text_id, rev_comment, rev_user, rev_user_text, rev_timestamp, rev_minor_edit, rev_deleted, rev_len, rev_parent_id, rev_sha1, rev_content_model, rev_content_format FROM revision;<br />
SELECT * INTO tmp_table FROM tmp_view;<br />
DROP VIEW tmp_view;<br />
update tmp_table set rev_page = 0 where rev_page is NULL;<br />
update tmp_table set rev_text_id = 0 where rev_text_id is NULL;<br />
update tmp_table set rev_comment = '' where rev_comment is NULL;<br />
ALTER TABLE revision RENAME TO orig_revision;<br />
ALTER TABLE tmp_table RENAME TO revision;<br />
<br />
/* searchindex (does not exists in pg) */<br />
<br />
/* sites (OK) */<br />
<br />
/* site_identifiers (OK) */<br />
<br />
/* site_stats */<br />
CREATE VIEW tmp_view AS<br />
select ss_row_id, ss_total_edits, ss_good_articles, ss_total_pages, ss_users, ss_active_users, ss_images FROM site_stats;<br />
SELECT * INTO tmp_table FROM tmp_view;<br />
DROP VIEW tmp_view;<br />
ALTER TABLE site_stats RENAME TO orig_site_stats;<br />
ALTER TABLE tmp_table RENAME TO site_stats;<br />
<br />
/* tag_summary (OK) */<br />
<br />
/* templatelinks */<br />
CREATE VIEW tmp_view AS<br />
select tl_from, tl_from_namespace, tl_namespace, tl_title FROM templatelinks;<br />
SELECT * INTO tmp_table FROM tmp_view;<br />
DROP VIEW tmp_view;<br />
ALTER TABLE templatelinks RENAME TO orig_templatelinks;<br />
ALTER TABLE tmp_table RENAME TO templatelinks;<br />
<br />
CREATE VIEW tmp_view AS<br />
select old_id, old_text, old_flags FROM pagecontent;<br />
SELECT * INTO tmp_table FROM tmp_view;<br />
DROP VIEW tmp_view;<br />
update tmp_table set old_text = '' where old_text is NULL;<br />
update tmp_table set old_flags = '' where old_flags is NULL;<br />
ALTER TABLE pagecontent RENAME TO orig_pagecontent;<br />
ALTER TABLE tmp_table RENAME TO pagecontent;<br />
<br />
/* transcache (OK) */<br />
<br />
/* updatelog (OK) */<br />
<br />
/* uploadstash */<br />
CREATE VIEW tmp_view AS<br />
select us_id, us_user, us_key, us_orig_path, us_path, us_source_type, us_timestamp, us_status, us_chunk_inx, us_props, us_size, us_sha1, us_mime, us_media_type, us_image_width, us_image_height, us_image_bits FROM uploadstash;<br />
SELECT * INTO tmp_table FROM tmp_view;<br />
DROP VIEW tmp_view;<br />
update tmp_table set us_user = 0 where us_user is NULL;<br />
update tmp_table set us_key = '' where us_key is NULL;<br />
update tmp_table set us_orig_path = '' where us_orig_path is NULL;<br />
update tmp_table set us_path = '' where us_path is NULL;<br />
/* update tmp_table set us_timestamp = '' where us_timestamp is NULL; */<br />
update tmp_table set us_status = '' where us_status is NULL;<br />
update tmp_table set us_size = 0 where us_size is NULL;<br />
update tmp_table set us_sha1 = '' where us_sha1 is NULL;<br />
ALTER TABLE uploadstash RENAME TO orig_uploadstash;<br />
ALTER TABLE tmp_table RENAME TO uploadstash;<br />
<br />
/* user (in pg it is named mwuser)*/<br />
CREATE VIEW tmp_view AS<br />
select user_id, user_name, user_real_name, user_password, user_newpassword, user_newpass_time, user_email, user_touched, user_token, user_email_authenticated, user_email_token, user_email_token_expires, user_registration, user_editcount, user_password_expires FROM mwuser;<br />
SELECT * INTO tmp_table FROM tmp_view;<br />
DROP VIEW tmp_view;<br />
update tmp_table set user_real_name = '' where user_real_name is NULL;<br />
update tmp_table set user_password = '' where user_password is NULL;<br />
update tmp_table set user_newpassword = '' where user_newpassword is NULL;<br />
update tmp_table set user_token = '' where user_token is NULL;<br />
update tmp_table set user_email = '' where user_email is NULL;<br />
/* update tmp_table set user_touched = '' where user_touched is NULL; */<br />
delete from tmp_table where user_id = 0;<br />
ALTER TABLE mwuser RENAME TO orig_mwuser;<br />
ALTER TABLE tmp_table RENAME TO mwuser;<br />
/* it cannot be named 'user', like in mysql */<br />
<br />
/* user_former_groups */<br />
CREATE VIEW tmp_view AS<br />
select ufg_user, ufg_group FROM user_former_groups;<br />
SELECT * INTO tmp_table FROM tmp_view;<br />
DROP VIEW tmp_view;<br />
update tmp_table set ufg_user = 0 where ufg_user is NULL;<br />
ALTER TABLE user_former_groups RENAME TO orig_user_former_groups;<br />
ALTER TABLE tmp_table RENAME TO user_former_groups;<br />
<br />
/* user_groups */<br />
CREATE VIEW tmp_view AS<br />
select ug_user, ug_group FROM user_groups;<br />
SELECT * INTO tmp_table FROM tmp_view;<br />
DROP VIEW tmp_view;<br />
update tmp_table set ug_user = 0 where ug_user is NULL;<br />
ALTER TABLE user_groups RENAME TO orig_user_groups;<br />
ALTER TABLE tmp_table RENAME TO user_groups;<br />
<br />
/* user_newtalk */<br />
CREATE VIEW tmp_view AS<br />
select user_id, user_ip, user_last_timestamp FROM user_newtalk;<br />
SELECT * INTO tmp_table FROM tmp_view;<br />
DROP VIEW tmp_view;<br />
update tmp_table set user_ip = '' where user_ip is NULL;<br />
ALTER TABLE user_newtalk RENAME TO orig_user_newtalk;<br />
ALTER TABLE tmp_table RENAME TO user_newtalk;<br />
<br />
/* user_properties */<br />
CREATE VIEW tmp_view AS<br />
select up_user, up_property, up_value FROM user_properties;<br />
SELECT * INTO tmp_table FROM tmp_view;<br />
DROP VIEW tmp_view;<br />
update tmp_table set up_user = 0 where up_user is NULL;<br />
ALTER TABLE user_properties RENAME TO orig_user_properties;<br />
ALTER TABLE tmp_table RENAME TO user_properties;<br />
<br />
/* valid_tag (OK) */<br />
<br />
/* watchlist (OK) */<br />
<br />
/* external_user (not needed) */<br />
ALTER TABLE external_user RENAME TO orig_external_user;<br />
<br />
/* hitcounter (removed in MediaWiki 1.25) */<br />
ALTER TABLE hitcounter RENAME TO orig_hitcounter;<br />
<br />
/* mediawiki_version ('Nobody actually uses it anymore, it just gets silently updated (and only for Postgres at that)') */<br />
ALTER TABLE mediawiki_version RENAME TO orig_mediawiki_version;<br />
<br />
/* profiling ('It's only used in an obscure debugging mode.') */<br />
ALTER TABLE profiling RENAME TO orig_profiling;<br />
<br />
<br />
/* trackbacks (This feature was removed completely in version MediaWiki 1.19.) */<br />
ALTER TABLE trackbacks RENAME TO orig_trackbacks;<br />
</nowiki>}}<br />
<br />
{{Cmd|<nowiki>su - postgres<br />
psql tmpdb < /tmp/pg-prepare-for-mysql.sql<br />
exit<br />
</nowiki>}}<br />
<br />
====Making dump files for mysql import====<br />
{{Cmd|<nowiki>su - postgres<br />
mkdir /tmp/wikidb-pg-dump<br />
psql -d wikidb -c "SELECT table_schema || '.' || table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog', 'information_schema');" > /tmp/t<br />
cat /tmp/t | grep mediawiki | sed 's/ mediawiki.//g' | sort > /tmp/pg-tables.txt<br />
<br />
for t in `cat /tmp/pg-tables.txt`; do echo "dumping $t"; pg_dump -d tmpdb --data-only --inserts -t mediawiki.$t > /tmp/wikidb-pg-dump/$t.sql; done<br />
exit<br />
</nowiki>}}<br />
<br />
====Modifying dump files====<br />
Postgresql specific sql should be removed<br />
{{Cmd|<nowiki>cd /tmp/wikidb-pg-dump<br />
for f in *.sql; do sed -i 's/^SET statement_timeout = 0;$//g' $f; done<br />
for f in *.sql; do sed -i 's/^SET lock_timeout = 0;$//g' $f; done<br />
for f in *.sql; do sed -i "s/^SET client_encoding = 'SQL_ASCII';$//g" $f; done<br />
for f in *.sql; do sed -i 's/^SET standard_conforming_strings = on;$//g' $f; done<br />
for f in *.sql; do sed -i 's/^SET check_function_bodies = false;$//g' $f; done<br />
for f in *.sql; do sed -i 's/^SET client_min_messages = warning;$//g' $f; done<br />
for f in *.sql; do sed -i 's/^SET search_path = mediawiki, pg_catalog;$//g' $f; done<br />
for f in *.sql; do sed -i "s/^SELECT pg_catalog.setval('filearchive_fa_id_seq', 25, true);$//g" $f; done<br />
</nowiki>}}<br />
<br />
Postgresql table '''mwuser''' corresponds to mysql table '''user'''<br><br />
{{Cmd|<nowiki>for f in *.sql; do sed -i "s/^INSERT INTO mwuser VALUES/INSERT INTO user VALUES/g" $f; done</nowiki>}}<br />
<br />
Postgresql table '''pagecontent''' corresponds to mysql table '''text'''<br><br />
{{Cmd|<nowiki>for f in *.sql; do sed -i "s/^INSERT INTO pagecontent VALUES/INSERT INTO text VALUES/g" $f; done</nowiki>}}<br />
<br />
Back slashes should be replaced with double backslashes<br />
{{Cmd|<nowiki>for f in *.sql; do sed -i 's/\\/\\\\/g' $f; done</nowiki>}}<br />
<br />
Timestamp format in postgresql is different from mysql (pg: '2015-10-31 10:10:10+00', mysql: '20151031101010'). All timestamps should be converted to avoid errors like:<br><br />
<code>... Language::sprintfDate: The timestamp should be a number</code><br />
{{Cmd|<nowiki>for f in *.sql; do sed -i "s/'\([0-9][0-9][0-9][0-9]\)-\([0-9][0-9]\)-\([0-9][0-9]\) \([0-9][0-9]\):\([0-9][0-9]\):\([0-9][0-9]\)[+-][0-9][0-9]'/'\1\2\3\4\5\6'/g" $f; done</nowiki>}}<br />
<br />
In postgresql '''ipblocks''' table may contain records with duplicated field '''ipb_address'''. mysql requires that ipb_address field is unique.<br />
Check manually '''ipblocks.sql''' file. Delete records with duplicated field '''ipb_address''' to prevent errors like:<br><br />
<code># ERROR 1062 (23000) at line 152: Duplicate entry 'xxxxxx-0-0-0' for key 'ipb_address'</code><br />
{{Cmd|vi ipblocks.sql}}<br />
<br />
==Preparing mysql database and wiki user ==<br />
{{Cmd|<nowiki>mysql -u root<br />
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpassword');<br />
SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('newpassword');<br />
SET PASSWORD FOR 'root'@'::1' = PASSWORD('newpassword');<br />
FLUSH PRIVILEGES;<br />
CREATE USER 'wikiuser'@'localhost' IDENTIFIED BY 'wikiuserpassword';<br />
GRANT ALL PRIVILEGES ON *.* TO 'wikiuser'@'localhost' WITH GRANT OPTION;<br />
CREATE USER 'wikiuser'@'%' IDENTIFIED BY 'wikiuserpassword';<br />
GRANT ALL PRIVILEGES ON *.* TO 'wikiuser'@'%' WITH GRANT OPTION;<br />
exit<br />
</nowiki>}}<br />
<br />
==Setting up new mediawiki==<br />
{{Cmd|<nowiki>cd /var/www/wiki.wikiname.org/htdocs<br />
wget http://releases.wikimedia.org/mediawiki/1.25/mediawiki-1.25.3.tar.gz<br />
tar zxf mediawiki-1.25.3.tar.gz<br />
mv mediawiki-1.25.3 w<br />
rm mediawiki-1.25.3.tar.gz<br />
</nowiki>}}<br />
<br />
{{Cmd|vi index.php}}<br />
<nowiki><br />
<?php<br />
header('Location: /wiki');<br />
?></nowiki><br />
<br />
Open your wiki in browser http://wiki.wikiname.org, go through setup process (DB type:mysql, engin: innodb, charset: binary)<br><br />
Compare LocalSettings.php file generated by setup process with old mediawiki LocalSettings.php file<br><br />
Take needed settings from old LocalSettings.php<br><br />
Copy new LocalSettings.php into mediawiki directory /var/www/wiki.wikiname.org/htdocs/w<br><br />
Copy needed files from old mediawiki (images/*, extensions/..., resources/...)<br />
<br />
{{Cmd|<nowiki>cd /var/www/wiki.wikiname.org/htdocs/w<br />
find . -type d > /tmp/d<br />
while read d; do chmod 755 "$d"; done < /tmp/d<br />
find . -type f > /tmp/f<br />
while read f; do chmod 644 "$f"; done < /tmp/f<br />
chown -R lighttpd:lighttpd .<br />
cd /var/www/wiki.wikiname.org/htdocs/w<br />
php maintenance/update.php # it will activate extensions and create needed tables in mysql db<br />
</nowiki>}}<br />
<br />
==Clearing mysql tables before import==<br />
{{Cmd|<nowiki>mysql wikidb -uUSERNAME -pPASSWORD --default-character-set=utf8 -Bse 'show tables;' > /tmp/t<br />
for t in `cat /tmp/t`; do echo "TRUNCATE $t;"; done >/tmp/mysql-clear-tables.sql<br />
mysql wikidb -uUSERNAME -pPASSWORD --default-character-set=utf8 < /tmp/mysql-clear-tables.sql<br />
</nowiki>}}<br />
<br />
==Importing data into mysql==<br />
{{Cmd|<nowiki>for t in `cat /tmp/pg-tables.txt`; do echo "importing $t"; mysql wikidb -uUSERNAME -pPASSWORD --default-character-set=utf8 < /path/to/wikidb-pg-dump/$t.sql; done<br />
</nowiki>}}<br />
<br />
==Running maintenance scripts==<br />
{{Cmd|php maintenance/update.php<br />
php maintenance/rebuildrecentchanges.php<br />
php maintenance/rebuildtextindex.php<br />
}}<br />
<br />
==Regenerating cache==<br />
Old cache in '''l10n_cache''' table may couse problems. It can be deleted and regenerated again.<br />
{{Cmd|<nowiki>echo "TRUNCATE l10n_cache;" | mysql wikidb -uUSERNAME -pPASSWORD --default-character-set=utf8<br />
cd /var/www/wiki.wikiname.org/htdocs/w<br />
php maintenance/rebuildLocalisationCache.php<br />
</nowiki>}}</div>
Slukin