Topic on Project:Support desk

Jump to navigation Jump to search
Pepsisformosa (talkcontribs)

I've been using a raspberry pi to host a private wiki since 2014 (MediaWiki 1.19.20+dfsg-2.3), and recently the sqlite database file outgrew the veracrypt container on which it was stored. Fortunately, I made regular backups of the database file, but while the wiki works for a short period after swapping out the database file for a backup version, within a day or so it will start returning HTTP 500 errors. The database checker claims that is not corrupted, yet the problem persists until I swap out the database for a fresh copy (which then succumbs to the same fate).

I then tried a fresh install (MediaWiki 1.27.5) on a separate pi, but the newer version does not seem to recognize the old database file. I've tried updating the database file as well as the entire media wiki installation on the first pi, and these failed.

What would be the easiest way to get it running again?

Ciencia Al Poder (talkcontribs)

Take a look at Manual:SQLite for requirements and installation. Try a new installation first to see if the installer detects support for sqlite database.

Pepsisformosa (talkcontribs)

Thanks for taking the time to help.

I did a fresh install, and it detected support for sqlite database and works fine with the brand new database. The problem comes when I try to replace the new database with the old database (1.19.20+dfsg-2.3) that actually has all my data in it. When I do that and try to visit the wiki, I get this message: "A database query error has occurred. This may indicate a bug in the software."

When I try to update the table by visiting mediawiki/mw-config/ and running through the update process, it fails with this message:


Turning off Content Handler DB fields for this part of upgrade.

...have ss_active_users field in site_stats table.

...ss_active_users user count set...

...have ipb_allow_usertalk field in ipblocks table.

Adding initial indexes ...

An error occurred:

A database query error has occurred.

Query: CREATE TABLE archive_tmp (

ar_id NOT NULL PRIMARY KEY clustered IDENTITY,

ar_namespace INTEGER NOT NULL DEFAULT 0,

ar_title NVARCHAR(255) NOT NULL DEFAULT '',

ar_text NVARCHAR(MAX) NOT NULL,

ar_comment NVARCHAR(255) NOT NULL,

ar_user INTEGER NULL REFERENCES [user](user_id) ON DELETE SET NULL,

ar_user_text NVARCHAR(255) NOT NULL,

ar_timestamp TEXT NOT NULL DEFAULT GETDATE(),

ar_minor_edit BIT NOT NULL DEFAULT 0,

ar_flags NVARCHAR(255) NOT NULL,

ar_rev_id INTEGER,

ar_text_id INTEGER,

ar_deleted BIT NOT NULL DEFAULT 0,

ar_len INTEGER DEFAULT NULL,

ar_page_id INTEGER NULL,

ar_parent_id INTEGER NULL

)

Function: DatabaseBase::sourceFile( /usr/share/mediawiki/maintenance/sqlite/archives/initial-indexes.sql )

Error: 1 near "clustered": syntax error

Backtrace:

#0 /usr/share/mediawiki/includes/db/Database.php(901): DatabaseBase->reportQueryError('near "clustered...', 1, 'CREATE TABLE ar...', 'DatabaseBase::s...', false)

#1 /usr/share/mediawiki/includes/db/Database.php(3057): DatabaseBase->query('CREATE TABLE ar...', 'DatabaseBase::s...')

#2 /usr/share/mediawiki/includes/db/Database.php(2978): DatabaseBase->sourceStream(Resource id #16, false, false, 'DatabaseBase::s...', false)

#3 /usr/share/mediawiki/includes/installer/DatabaseUpdater.php(669): DatabaseBase->sourceFile('/usr/share/medi...')

#4 /usr/share/mediawiki/includes/installer/SqliteUpdater.php(168): DatabaseUpdater->applyPatch('/usr/share/medi...', false, 'Adding initial ...')

#5 /usr/share/mediawiki/includes/installer/DatabaseUpdater.php(455): SqliteUpdater->sqliteInitialIndexes()

#6 /usr/share/mediawiki/includes/installer/DatabaseUpdater.php(416): DatabaseUpdater->runUpdates(Array, false)

#7 /usr/share/mediawiki/includes/installer/DatabaseInstaller.php(307): DatabaseUpdater->doUpdates()

#8 /usr/share/mediawiki/includes/installer/WebInstallerUpgrade.php(65): DatabaseInstaller->doUpgrade()

#9 /usr/share/mediawiki/includes/installer/WebInstaller.php(279): WebInstallerUpgrade->execute()

#10 /usr/share/mediawiki/mw-config/index.php(81): WebInstaller->execute(Array)

#11 /usr/share/mediawiki/mw-config/index.php(39): wfInstallerMain()

#12 {main}

Purging caches...done.

Ciencia Al Poder (talkcontribs)
Pepsisformosa (talkcontribs)

I tried again using backports and installed sqlite version 3.27.2. It works when I allow it to make a new (empty) database, but when I point it to my old database and tell it to upgrade, it still fails.

The failure is different, at least. This time it never finishes upgrading and was still giving me the "busy" animation 6 hours later.


Turning off Content Handler DB fields for this part of upgrade.

...have ss_active_users field in site_stats table.

...ss_active_users user count set...

...have ipb_allow_usertalk field in ipblocks table.

Adding initial indexes ...

An error occurred:

A database query error has occurred.

Query: CREATE TABLE user_tmp (

user_id INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,

user_name TEXT  NOT NULL default '',

user_real_name TEXT  NOT NULL default '',

user_password BLOB NOT NULL,

user_newpassword BLOB NOT NULL,

user_newpass_time BLOB,

user_email TEXT NOT NULL,

user_options BLOB NOT NULL,

user_touched BLOB NOT NULL default '',

user_token BLOB NOT NULL default '',

user_email_authenticated BLOB,

user_email_token BLOB,

user_email_token_expires BLOB,

user_registration BLOB,

user_editcount INTEGER

)

Function: DatabaseBase::sourceFile( /usr/share/mediawiki/maintenance/sqlite/archives/initial-indexes.sql )

Error: 8 attempt to write a readonly database


Backtrace:

#0 /usr/share/mediawiki/includes/db/Database.php(901): DatabaseBase->reportQueryError('attempt to writ...', 8, 'CREATE TABLE us...', 'DatabaseBase::s...', false)

#1 /usr/share/mediawiki/includes/db/Database.php(3057): DatabaseBase->query('CREATE TABLE us...', 'DatabaseBase::s...')

#2 /usr/share/mediawiki/includes/db/Database.php(2978): DatabaseBase->sourceStream(Resource id #14, false, false, 'DatabaseBase::s...', false)

#3 /usr/share/mediawiki/includes/installer/DatabaseUpdater.php(669): DatabaseBase->sourceFile('/usr/share/medi...')

#4 /usr/share/mediawiki/includes/installer/SqliteUpdater.php(168): DatabaseUpdater->applyPatch('/usr/share/medi...', false, 'Adding initial ...')

#5 /usr/share/mediawiki/includes/installer/DatabaseUpdater.php(455): SqliteUpdater->sqliteInitialIndexes()

#6 /usr/share/mediawiki/includes/installer/DatabaseUpdater.php(416): DatabaseUpdater->runUpdates(Array, false)

#7 /usr/share/mediawiki/includes/installer/DatabaseInstaller.php(307): DatabaseUpdater->doUpdates()

#8 /usr/share/mediawiki/includes/installer/WebInstallerUpgrade.php(65): DatabaseInstaller->doUpgrade()

#9 /usr/share/mediawiki/includes/installer/WebInstaller.php(279): WebInstallerUpgrade->execute()

#10 /usr/share/mediawiki/mw-config/index.php(81): WebInstaller->execute(Array)

#11 /usr/share/mediawiki/mw-config/index.php(39): wfInstallerMain()

#12 {main}

Purging caches...<!DOCTYPE html>

<html><head><title>Database error - MediaWiki</title><style>body { font-family: sans-serif; margin: 0; padding: 0.5em 2em; }</style></head><body>

<p>A database query error has occurred.

This may indicate a bug in the software.</p><ul><li>Query: <div class="mw-code" dir="ltr">DELETE FROM objectcache</div></li><li>Function: <code dir="ltr">DatabaseUpdater::purgeCache</code></li><li>Error: <samp dir="ltr">8 attempt to write a readonly database</samp></li></ul><p>Backtrace:</p><pre>#0 /usr/share/mediawiki/includes/db/Database.php(901): DatabaseBase-&gt;reportQueryError('attempt to writ...', 8, 'DELETE FROM obj...', 'DatabaseUpdater...', false)

#1 /usr/share/mediawiki/includes/db/Database.php(2259): DatabaseBase-&gt;query('DELETE FROM obj...', 'DatabaseUpdater...')

#2 /usr/share/mediawiki/includes/installer/DatabaseUpdater.php(944): DatabaseBase-&gt;delete('objectcache', '*', 'DatabaseUpdater...')

#3 /usr/share/mediawiki/includes/installer/DatabaseInstaller.php(313): DatabaseUpdater-&gt;purgeCache()

#4 /usr/share/mediawiki/includes/installer/WebInstallerUpgrade.php(65): DatabaseInstaller-&gt;doUpgrade()

#5 /usr/share/mediawiki/includes/installer/WebInstaller.php(279): WebInstallerUpgrade-&gt;execute()

#6 /usr/share/mediawiki/mw-config/index.php(81): WebInstaller-&gt;execute(Array)

#7 /usr/share/mediawiki/mw-config/index.php(39): wfInstallerMain()

#8 {main}</pre></body></html>

Ciencia Al Poder (talkcontribs)
Pepsisformosa (talkcontribs)

Well that's embarrassing. I fixed that one, but still no success.


Turning off Content Handler DB fields for this part of upgrade.

...have ss_active_users field in site_stats table.

...ss_active_users user count set...

...have ipb_allow_usertalk field in ipblocks table.

Adding initial indexes ...

An error occurred:

A database query error has occurred.

Query: CREATE TABLE archive_tmp (

ar_id NOT NULL PRIMARY KEY clustered IDENTITY,

ar_namespace INTEGER NOT NULL DEFAULT 0,

ar_title NVARCHAR(255) NOT NULL DEFAULT '',

ar_text NVARCHAR(MAX) NOT NULL,

ar_comment NVARCHAR(255) NOT NULL,

ar_user INTEGER NULL REFERENCES [user](user_id) ON DELETE SET NULL,

ar_user_text NVARCHAR(255) NOT NULL,

ar_timestamp TEXT NOT NULL DEFAULT GETDATE(),

ar_minor_edit BIT NOT NULL DEFAULT 0,

ar_flags NVARCHAR(255) NOT NULL,

ar_rev_id INTEGER,

ar_text_id INTEGER,

ar_deleted BIT NOT NULL DEFAULT 0,

ar_len INTEGER DEFAULT NULL,

ar_page_id INTEGER NULL,

ar_parent_id INTEGER NULL

)

Function: DatabaseBase::sourceFile( /usr/share/mediawiki/maintenance/sqlite/archives/initial-indexes.sql )

Error: 1 near "clustered": syntax error

Backtrace:

#0 /usr/share/mediawiki/includes/db/Database.php(901): DatabaseBase->reportQueryError('near "clustered...', 1, 'CREATE TABLE ar...', 'DatabaseBase::s...', false)

#1 /usr/share/mediawiki/includes/db/Database.php(3057): DatabaseBase->query('CREATE TABLE ar...', 'DatabaseBase::s...')

#2 /usr/share/mediawiki/includes/db/Database.php(2978): DatabaseBase->sourceStream(Resource id #14, false, false, 'DatabaseBase::s...', false)

#3 /usr/share/mediawiki/includes/installer/DatabaseUpdater.php(669): DatabaseBase->sourceFile('/usr/share/medi...')

#4 /usr/share/mediawiki/includes/installer/SqliteUpdater.php(168): DatabaseUpdater->applyPatch('/usr/share/medi...', false, 'Adding initial ...')

#5 /usr/share/mediawiki/includes/installer/DatabaseUpdater.php(455): SqliteUpdater->sqliteInitialIndexes()

#6 /usr/share/mediawiki/includes/installer/DatabaseUpdater.php(416): DatabaseUpdater->runUpdates(Array, false)

#7 /usr/share/mediawiki/includes/installer/DatabaseInstaller.php(307): DatabaseUpdater->doUpdates()

#8 /usr/share/mediawiki/includes/installer/WebInstallerUpgrade.php(65): DatabaseInstaller->doUpgrade()

#9 /usr/share/mediawiki/includes/installer/WebInstaller.php(279): WebInstallerUpgrade->execute()

#10 /usr/share/mediawiki/mw-config/index.php(81): WebInstaller->execute(Array)

#11 /usr/share/mediawiki/mw-config/index.php(39): wfInstallerMain()

#12 {main}

Purging caches...done.

Ciencia Al Poder (talkcontribs)
AssetDenmark (talkcontribs)

I managed to port a wiki from 1.18 to 1.32 - i simply set up the new wiki as-is - and tried to set up everything up the same, installing the same extensions/namespaces etc.. After that I ran a export/import... and that seems to work nicely. (I did not see anything "lost"... yet. ) - you should do one or more backups of the "native" 1.32 Wiki along the way.


From what I read you try to "copy" the SQL files, but there could be all sorts of differences.... doing the export/import also allows you to "mess" a bit with the table names/prefix etc. You can - carefully - edit the export file to match a new prefix ( $wgDBprefix ).

198.72.159.149 (talkcontribs)

Ciencia,

I had to redo the installation and messed up the permissions in doing so. I'd consider that going "back a step" and fixing it to be bringing me back "forward" back to where we were before. :)


I did manage to upgrade my SQLite with backports, and now the "Version" page lists my SQLite version as "3.27.2 with full-text search support". So ignoring my permissions mistake, it looks like there's no change despite the upgraded SQLite.


AssetDenmark,

It looks like you're referring to MySQL databases as opposed to SQLite? I'm not aware of any MySQL "files" I could copy, and also not aware of any "export/import" function for SQLite databases. When I look up how to do the export/import I find only MySQL stuff, and the reason I went with SQLite in the first place was because I thought I could simply back up the database file (which had worked well until recently).


I did look into the installation settings and prefix things, and I'm not sure whether I understand correctly. The original installation was years ago and I can't remember the exact settings, but it seems that this information is all in the LocalSettings.php file, no? I did try using the original LocalSettings.php file with the appropriate changes, but the upgrade process doesn't seem to change based on this? I think the prefix should be the same in both cases, as I did not do anything differently there.

Ciencia Al Poder (talkcontribs)

Feel free to post your current error message, since now I'm lost as of what's the problem now

Pepsisformosa (talkcontribs)

Yeah, it got a little confusing with the extra permissions mistake thrown in there. Basically, I think the backports upgrade didn't change anything.

Here's the current error message:


Turning off Content Handler DB fields for this part of upgrade.

...have ss_active_users field in site_stats table.

...ss_active_users user count set...

...have ipb_allow_usertalk field in ipblocks table.

Adding initial indexes ...

An error occurred:

A database query error has occurred.

Query: CREATE TABLE archive_tmp (

ar_id NOT NULL PRIMARY KEY clustered IDENTITY,

ar_namespace INTEGER NOT NULL DEFAULT 0,

ar_title NVARCHAR(255) NOT NULL DEFAULT '',

ar_text NVARCHAR(MAX) NOT NULL,

ar_comment NVARCHAR(255) NOT NULL,

ar_user INTEGER NULL REFERENCES [user](user_id) ON DELETE SET NULL,

ar_user_text NVARCHAR(255) NOT NULL,

ar_timestamp TEXT NOT NULL DEFAULT GETDATE(),

ar_minor_edit BIT NOT NULL DEFAULT 0,

ar_flags NVARCHAR(255) NOT NULL,

ar_rev_id INTEGER,

ar_text_id INTEGER,

ar_deleted BIT NOT NULL DEFAULT 0,

ar_len INTEGER DEFAULT NULL,

ar_page_id INTEGER NULL,

ar_parent_id INTEGER NULL

)

Function: DatabaseBase::sourceFile( /usr/share/mediawiki/maintenance/sqlite/archives/initial-indexes.sql )

Error: 1 near "clustered": syntax error

Backtrace:

#0 /usr/share/mediawiki/includes/db/Database.php(901): DatabaseBase->reportQueryError('near "clustered...', 1, 'CREATE TABLE ar...', 'DatabaseBase::s...', false)

#1 /usr/share/mediawiki/includes/db/Database.php(3057): DatabaseBase->query('CREATE TABLE ar...', 'DatabaseBase::s...')

#2 /usr/share/mediawiki/includes/db/Database.php(2978): DatabaseBase->sourceStream(Resource id #14, false, false, 'DatabaseBase::s...', false)

#3 /usr/share/mediawiki/includes/installer/DatabaseUpdater.php(669): DatabaseBase->sourceFile('/usr/share/medi...')

#4 /usr/share/mediawiki/includes/installer/SqliteUpdater.php(168): DatabaseUpdater->applyPatch('/usr/share/medi...', false, 'Adding initial ...')

#5 /usr/share/mediawiki/includes/installer/DatabaseUpdater.php(455): SqliteUpdater->sqliteInitialIndexes()

#6 /usr/share/mediawiki/includes/installer/DatabaseUpdater.php(416): DatabaseUpdater->runUpdates(Array, false)

#7 /usr/share/mediawiki/includes/installer/DatabaseInstaller.php(307): DatabaseUpdater->doUpdates()

#8 /usr/share/mediawiki/includes/installer/WebInstallerUpgrade.php(65): DatabaseInstaller->doUpgrade()

#9 /usr/share/mediawiki/includes/installer/WebInstaller.php(279): WebInstallerUpgrade->execute()

#10 /usr/share/mediawiki/mw-config/index.php(81): WebInstaller->execute(Array)

#11 /usr/share/mediawiki/mw-config/index.php(39): wfInstallerMain()

#12 {main}

Purging caches...done.

Reply to "wiki recovery"