Обсуждение: Mediawiki 1.10 and PG 8.3 upgrade

Hello all,

I'm sure I did something wrong during my upgrade of mediawiki from
Postgresql 8.2 to 8.3.  Any help on how to recover would be appreciated.

Possible useful info:
OS: Fedora 9
Postgresql: PostgreSQL 8.3.1 on x86_64-redhat-linux-gnu
Mediaiwiki: mediawiki-1.10.4-39.fc9.x86_64

My mediawiki pages are loading, but when I try to edit them, I get this
error message:

A database error has occurred Query: INSERT INTO pagecontent
(old_id,old_text,old_flags) VALUES [snip] ERROR: text search
configuration "default" does not exist CONTEXT: PL/pgSQL function
"ts2_page_text" line 3 at assignment

Backtrace:

#0 /usr/share/mediawiki/includes/Database.php(761):
DatabasePostgres->reportQueryError('ERROR: text se...', 1, 'INSERT INTO
pag...', 'Revision::inser...', 0)
#1 /usr/share/mediawiki/includes/DatabasePostgres.php(695):
Database->query('INSERT INTO pag...', 'Revision::inser...', 0)
#2 /usr/share/mediawiki/includes/Revision.php(696):
DatabasePostgres->insert('text', Array, 'Revision::inser...')
#3 /usr/share/mediawiki/includes/Article.php(1336):
Revision->insertOn(Object(DatabasePostgres))
#4 /usr/share/mediawiki/includes/Article.php(1205): Article->doEdit('{|
class="wikit...', '', 98)
#5 /usr/share/mediawiki/includes/EditPage.php(865):
Article->updateArticle('{| class="wikit...', '', false, false, '', '')
#6 /usr/share/mediawiki/includes/EditPage.php(426): EditPage->attemptSave()
#7 /usr/share/mediawiki/includes/EditPage.php(279): EditPage->edit()
#8 /usr/share/mediawiki/includes/Wiki.php(432): EditPage->submit()
#9 /usr/share/mediawiki/includes/Wiki.php(48):
MediaWiki->performAction(Object(OutputPage), Object(Article),
Object(Title), Object(User), Object(WebRequest))
#10 /usr/share/mediawiki/index.php(92):
MediaWiki->initialize(Object(Title), Object(OutputPage), Object(User),
Object(WebRequest))
#11 {main}



I would like to say thanks to the people that put in so much time and
effort in developing tsearch2 (and postgresql too), your work is greatly
appreciated.  However, I have never had a recent postgresql upgrade work
seemlessly, and it is almost always due to issues with tsearch2.  :(

While searching the internet for magic recipes on how to perform these
upgrades that involve tsearch2, I find long complex instruction sets
detailing brain-numbingly complex operations (hand edit the schema!):

http://julien.danjou.info/blog/index.php/post/2008/05/19/Upgrading-mediawiki-from-PostgreSQL-82-to-83

Also, I have found this blog entry from Greg Mullane which makes it all
seem so simple: (Note Tom Lane's comment, which seems to directly relate
to the error message I am seeing).

http://people.planetpostgresql.org/greg/index.php?/archives/123-MediaWiki-is-Postgres-8.3-compatible.html

These are just a 2 examples, there are others.  The one common thread
shared by all the instructions are that they say different things.  How
are we, Joe PostgreSQL users on the street, supposed to know which
instructions to follow?  Have I (very possible) missed some official
PostgreSQL instructions?  How do we go about triaging tsearch2 problems?

--
-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
  Jon Lapham  <lapham@jandr.org>                Rio de Janeiro, Brasil
  Personal: http://www.jandr.org/
***-*--*----*-------*------------*--------------------*---------------

Jon Lapham wrote:
> Hello all,
>
> I'm sure I did something wrong during my upgrade of mediawiki from
> Postgresql 8.2 to 8.3.  Any help on how to recover would be appreciated.

[...]

> I would like to say thanks to the people that put in so much time and
> effort in developing tsearch2 (and postgresql too), your work is greatly
> appreciated.  However, I have never had a recent postgresql upgrade work
> seemlessly, and it is almost always due to issues with tsearch2.  :(

yeah - upgrades with tsearch2 have always been a bit of a problem - this
is one of many reasons why tsearch2 got fully integrated into the
backend starting with 8.3.

>
> While searching the internet for magic recipes on how to perform these
> upgrades that involve tsearch2, I find long complex instruction sets
> detailing brain-numbingly complex operations (hand edit the schema!):
>
> http://julien.danjou.info/blog/index.php/post/2008/05/19/Upgrading-mediawiki-from-PostgreSQL-82-to-83
>
>
> Also, I have found this blog entry from Greg Mullane which makes it all
> seem so simple: (Note Tom Lane's comment, which seems to directly relate
> to the error message I am seeing).
>
> http://people.planetpostgresql.org/greg/index.php?/archives/123-MediaWiki-is-Postgres-8.3-compatible.html
>
>
> These are just a 2 examples, there are others.  The one common thread
> shared by all the instructions are that they say different things.  How
> are we, Joe PostgreSQL users on the street, supposed to know which
> instructions to follow?  Have I (very possible) missed some official
> PostgreSQL instructions?  How do we go about triaging tsearch2 problems?

http://www.postgresql.org/docs/8.3/static/textsearch-migration.html has
a some discussion - and also see the backwards compatibility module
provided in contrib/tsearch2.


Stefan

Jon Lapham <lapham@jandr.org> writes:
> My mediawiki pages are loading, but when I try to edit them, I get this
> error message:

> A database error has occurred Query: INSERT INTO pagecontent
> (old_id,old_text,old_flags) VALUES [snip] ERROR: text search
> configuration "default" does not exist CONTEXT: PL/pgSQL function
> "ts2_page_text" line 3 at assignment

There isn't a "default" configuration in 8.3, unless you make one named
that.

> These are just a 2 examples, there are others.  The one common thread
> shared by all the instructions are that they say different things.  How
> are we, Joe PostgreSQL users on the street, supposed to know which
> instructions to follow?  Have I (very possible) missed some official
> PostgreSQL instructions?

http://www.postgresql.org/docs/8.3/static/textsearch-migration.html
http://www.postgresql.org/docs/8.3/static/tsearch2.html

I haven't personally tried that procedure with MediaWiki, though.

            regards, tom lane

Jon Lapham wrote:
> Hello all,

> My mediawiki pages are loading, but when I try to edit them, I get this
> error message:
>
> A database error has occurred Query: INSERT INTO pagecontent
> (old_id,old_text,old_flags) VALUES [snip] ERROR: text search
> configuration "default" does not exist CONTEXT: PL/pgSQL function
> "ts2_page_text" line 3 at assignment


>
> These are just a 2 examples, there are others.  The one common thread
> shared by all the instructions are that they say different things.  How
> are we, Joe PostgreSQL users on the street, supposed to know which
> instructions to follow?  Have I (very possible) missed some official

That would be a question for mediawiki people not PostgreSQL people.

> PostgreSQL instructions?  How do we go about triaging tsearch2 problems?
>

You could start by giving us PostgreSQL output, not mediawiki output. Up
your logging level in the postgresql.conf, reload, and give us a little
more context.

Here are the docs on Tsearch:

http://www.postgresql.org/docs/8.3/static/textsearch.html

Which is decidedly different from 8.2 to 8.3.

Here are the docs on logging:

http://www.postgresql.org/docs/8.3/static/runtime-config-logging.html

Sincerely,

Joshua D. Drake

Joshua D. Drake wrote:
> Jon Lapham wrote:
>> How are we, Joe PostgreSQL users on the street, supposed to know which
>> instructions to follow?  Have I (very possible) missed some official
>
> That would be a question for mediawiki people not PostgreSQL people.

Okay, makes sense.  It just seemed to me that in previous upgrades I
have done, the mediawiki people complained that these problems were due
to changes made in postgresql's tsearch2 setup.

Also, a lot of the instructions do seem to be very postgres-oriented
(for example: hand editting of the pgdump output, etc), not much to do
on the mediawiki side of things.  Other apps besides mediawiki seem to
have similar problems, or maybe a better word is upgrade complications.

>> PostgreSQL instructions?  How do we go about triaging tsearch2 problems?
>>
>
> You could start by giving us PostgreSQL output, not mediawiki output. Up
> your logging level in the postgresql.conf, reload, and give us a little
> more context.

Okay, I'll work on that.

> Here are the docs on Tsearch:
>
> http://www.postgresql.org/docs/8.3/static/textsearch.html
>
> Which is decidedly different from 8.2 to 8.3.
>
> Here are the docs on logging:
>
> http://www.postgresql.org/docs/8.3/static/runtime-config-logging.html

Thanks.

--
-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
  Jon Lapham  <lapham@jandr.org>                Rio de Janeiro, Brasil
  Personal: http://www.jandr.org/
***-*--*----*-------*------------*--------------------*---------------

Tom Lane wrote:
> Jon Lapham <lapham@jandr.org> writes:
>> Have I (very possible) missed some official
>> PostgreSQL instructions?
>
> http://www.postgresql.org/docs/8.3/static/textsearch-migration.html
> http://www.postgresql.org/docs/8.3/static/tsearch2.html
>
> I haven't personally tried that procedure with MediaWiki, though.

Okay, thanks for the pointer.  I don't know, it just makes my head hurt
to read all the documentation on tsearch2.  I guess I am just not smart
enough to figure this out.

Just a random examples from the textsearch-migration page:

-Text search configuration setup is completely different now...There is
not currently any automated support for converting an existing custom
configuration for 8.3; you're on your own here.  Ouch!

-In thesaurus configuration files, stop words must be marked with ?."  I
don't know what any of that means, and to be honest with you, I don't
want to know.

Okay, I'll go back and re-read all this and try to figure it out.

--
-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
  Jon Lapham  <lapham@jandr.org>                Rio de Janeiro, Brasil
  Personal: http://www.jandr.org/
***-*--*----*-------*------------*--------------------*---------------

You'll need to get a particular revision of MediaWiki that is PG 8.3
compatible:

http://people.planetpostgresql.org/greg/index.php?/archives/123-
MediaWiki-is-Postgres-8.3-compatible.html

Once that's done, it works fine (at least for me).

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> Also, a lot of the instructions do seem to be very postgres-oriented
> (for example: hand editting of the pgdump output, etc), not much to do
> on the mediawiki side of things.  Other apps besides mediawiki seem to
> have similar problems, or maybe a better word is upgrade complications.

Did you run 'php update.php' from the maintenance directory after you
upgraded? That's the canonical way to fix things up, and if it did
not fix your tsearch2 problems for you, please file a bug about it:

https://bugzilla.wikimedia.org/enter_bug.cgi?product=MediaWiki

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200805301532
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkhAVk0ACgkQvJuQZxSWSshAQwCeOKq4OHXrCPbjC6ATtfncA7Wq
v2oAnR9k/MEJTE33lmeVBN8bRoL7tlco
=eWWA
-----END PGP SIGNATURE-----

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> If upgrading MediaWiki to a version that supports postgres 8.3,
> another option could be to export from the old db as XML (using the
> Special:Export page)

You can also use the dumpBackup.php tool in the maintenance directory.
The XML this dumped does not include the mwusers table, so it's
not entirely helpful.

I spoke too soon when I said earlier all one needs is php update.php.
There are some serious tsearch2 issues that prevent you from dumping
the old database and importing into the new version, even with the
tsearch2 compatibility stuff. I'm creating some workarounds, and
will report back. Doing a --data-only dump and creating the schema
anew from maintenance/postgres/tables.sql should work, although
not if you've made any schema changes, obviously.


- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200805311103
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkhBaX4ACgkQvJuQZxSWSsg2lgCfWn5bPrgZFfp+Tf/rZFlqSEIm
AB8AoKfl/ooepXLbjzh3ql4GGuk86MJT
=yQ9w
-----END PGP SIGNATURE-----

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


Rather than try to document my experiences here in an email thread,
I created a wiki page to document the database upgrade process in general
for MediaWiki. Please look it over and edit it as needed:

http://www.mediawiki.org/wiki/Manual:Upgrading_Postgres

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200806041024
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkhGpcAACgkQvJuQZxSWSsgcPACgqDlcqt4dxfgwRfyehMuBy0lZ
b9sAnjBax4Na+gRxYsdOItGciCKvMjjO
=qFJ9
-----END PGP SIGNATURE-----