Difference between revisions of "Migrating mediawiki from mysql to sqlite"

From Linuxintro
imported>ThorstenStaerk
imported>ThorstenStaerk
Line 51: Line 51:
 
  $old_id = $dbw->nextSequenceValue( 'text_old_id_seq' );
 
  $old_id = $dbw->nextSequenceValue( 'text_old_id_seq' );
 
$dbw->insert calls insert() in include/db/DatabaseSqlite.php. This calls insert() in include/db/Database.php. This calls query() in include/db/Database.php.
 
$dbw->insert calls insert() in include/db/DatabaseSqlite.php. This calls insert() in include/db/Database.php. This calls query() in include/db/Database.php.
 +
 +
Reading the documentation, when you have an autoincrement your old_id may be NULL. Now here is my converted db:
 +
CREATE TABLE "phase3text" (
 +
  "old_id" int(10)  NOT NULL ,
 +
and here is when I create a new one:
 +
CREATE TABLE text (
 +
  old_id INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
  
 
= TroubleShooting =
 
= TroubleShooting =

Revision as of 04:58, 11 September 2013

Overview

To migrate a mediawiki from MySQL to SQLite there are two basic approaches:

  • start with a new database and worry about
    • dumping all pages but the Main Page with the dumpBackup command and restoring them with the importDump command
    • keeping the articles' revisions
    • copying the Main Page from the old wiki to the new
    • copying the users and privileges from the old wiki to the new
    • copying the wiki statistics (like page visits) from the old wiki to the new
    • copying images and other files from the old wiki to the new
    • re-doing things like mediawiki extensions on the new wiki
    • re-doing your settings e.g. from LocalSettings.php on the new wiki
Matt gives a good overview about this
  • just migrate the database below your wiki

migrate database below your wiki

I wanted to migrate the database below my wiki so

  • I created a dump (aka backup aka export) of the database
mysqldump wikidb -u wikiuser -p > dump.sql
  • I got a MySQL -> SQLite converter
wget https://gist.github.com/esperlu/943776/raw/dd87f4088f6d5ec7563478f7a28a37ba02cf26e2/mysql2sqlite.sh
  • I started this converter to create a file /srv/www/htdocs/wikidb.sqlite:
sh mysql2sqlite.sh -u wikiuser -p wikidb | sqlite3 /srv/www/htdocs/wikidb.sqlite
  • change LocalSettings.php to reflect
$wgDBtype = "sqlite";
$wgDBserver = "";
$wgDBname = "wikidb";
$wgDBuser = "";
$wgDBpassword = "";
$wgSQLiteDataDir = "/srv/www/htdocs";
  • allow your webserver to write to the file:
chown wwwrun:www /srv/www/htdocs/wikidb.sqlite

I will admit that I still have some more things to explore here.

Error

When editing an article article I get the error message 

A database query syntax error has occurred. This may indicate a bug in the software. The last attempted database query was:
   INSERT INTO phase3text (old_id,old_text,old_flags) VALUES (NULL,'= main.cpp = 
[...]
from within function "Revision::insertOn/single-row". Database returned error "19: phase3text.old_id may not be NULL".

By line-by-line-debugging I could find the error happens in this code from includes/Revision.php: 

$dbw->insert( 'text',
  array(
  'old_id'    => $old_id,
  'old_text'  => $data,
  'old_flags' => $flags,
  ), __METHOD__
);

$old_id is set by the code: 

$old_id = $dbw->nextSequenceValue( 'text_old_id_seq' );

$dbw->insert calls insert() in include/db/DatabaseSqlite.php. This calls insert() in include/db/Database.php. This calls query() in include/db/Database.php.

Reading the documentation, when you have an autoincrement your old_id may be NULL. Now here is my converted db: 

CREATE TABLE "phase3text" (
 "old_id" int(10)  NOT NULL ,

and here is when I create a new one: 

CREATE TABLE text (
 old_id INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,

TroubleShooting

I got quite a lot of error messages that were easy to fix - provided you know how.

PDO exception

Symptom: When surfing to the wiki you get a page displaying this error message: 

Unexpected non-MediaWiki exception encountered, of type "PDOException"
exception 'PDOException' with message 'There is no active transaction' in /srv/www/htdocs/mediawiki/includes/db/DatabaseSqlite.php:664
Stack trace:

Solution: In LocalSettings.php set all occurrences of $wgDBuser to "". 

$wgDBuser = "";

Is not unique

Symptom: When calling mysql2sqlite.sh you get a lot of error messages like 

Error: near line 565: column cat_id is not unique
Error: near line 566: column cat_id is not unique

Solution: You cannot call mysql2sqlite.sh twice to the same target file. Delete the target file before you start the next conversion, like this: 

rm test.sqlite
sh /root/mysql2sqlite.sh -u wikiuser -p wikidb | sqlite3 test.sqlite

file is encrypted or not a database

Symptom: When trying to open the sqlite database on the command line you get the error message 

Unable to open database "../../data/my_wiki.sqlite": file is encrypted or is not a database

Solution: In my case I used the command sqlite to open the database. sqlite pointed to version 2 of sqlite. When I replaced the command sqlite by sqlite3 it worked: 

tweedleburg:/srv/www/htdocs/mediawiki # sqlite ../../data/my_wiki.sqlite 
Unable to open database "../../data/my_wiki.sqlite": file is encrypted or is not a database
tweedleburg:/srv/www/htdocs/mediawiki # sqlite3 ../../data/my_wiki.sqlite 
SQLite version 3.7.12.1 2012-05-22 02:45:53
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

See also

Retrieved from "http://linuxintro.org/index.php?title=Migrating_mediawiki_from_mysql_to_sqlite&oldid=18775"