Mailing List Archive: Update.php error using postgres. squences and others already exists

Update.php error using postgres. squences and others already exists

May 31, 2020, 9:09 PM

Post #1 of 4 (614 views)

Hello

I am new to the list

I am trying to upgrade my mediawiki installation from 1.31.7 to 1.34.1
and I am getting errors when executing php update.php.
Same thing happened when trying to upgrade to 1.33.3
I have seen bug reports dating back to 2016. Last suggestion being to
move such things to the public schema instead of the database schema.
Is this safe/good idea?
What other solution can I apply?

----------- Example of output --------------------
MediaWiki 1.34.1 Updater

Your composer.lock file is up to date with current dependencies!
Going to run database updates for mypedia-default-
Depending on the size of your database this may take a while!
...skipping: 'mwuser' table doesn't exist yet.
...skipping: 'mwuser' table doesn't exist yet.
...skipping: 'pagecontent' table doesn't exist yet.
Creating sequence logging_log_id_seq
Wikimedia\Rdbms\DBQueryError from line 1603 of
/var/www/html/mypedia/includes/libs/rdbms/database/Database.php: A
database query error has occurred. Did you forget to run your
application's database schema updater after upgrading?
Query: CREATE SEQUENCE logging_log_id_seq
Function: Wikimedia\Rdbms\DBConnRef::query
Error: 42P07 ERROR: relation "logging_log_id_seq" already exists

#0 /var/www/html/mypedia/includes/libs/rdbms/database/Database.php(1574):
Wikimedia\Rdbms\Database->getQueryExceptionAndLog('ERROR:
relatio...', '42P07', 'CREATE SEQUENCE...', 'Wikimedia\\Rdbms...')
#1 /var/www/html/mypedia/includes/libs/rdbms/database/Database.php(1152):
Wikimedia\Rdbms\Database->reportQueryError('ERROR: relatio...',
'42P07', 'CREATE SEQUENCE...', 'Wikimedia\\Rdbms...', false)
#2 /var/www/html/mypedia/includes/libs/rdbms/database/DBConnRef.php(68):
Wikimedia\Rdbms\Database->query('CREATE SEQUENCE...',
'Wikimedia\\Rdbms...', 0)
#3 /var/www/html/mypedia/includes/libs/rdbms/database/DBConnRef.php(292):
Wikimedia\Rdbms\DBConnRef->__call('query', Array)
#4 /var/www/html/mypedia/includes/installer/PostgresUpdater.php(833):
Wikimedia\Rdbms\DBConnRef->query('CREATE SEQUENCE...')
#5 /var/www/html/mypedia/includes/installer/DatabaseUpdater.php(490):
PostgresUpdater->addSequence('logging', false, 'logging_log_id_...')
#6 /var/www/html/mypedia/includes/installer/DatabaseUpdater.php(454):
DatabaseUpdater->runUpdates(Array, false)
#7 /var/www/html/mypedia/maintenance/update.php(205):
DatabaseUpdater->doUpdates(Array)
#8 /var/www/html/mypedia/maintenance/doMaintenance.php(99):
UpdateMediaWiki->execute()
#9 /var/www/html/mypedia/maintenance/update.php(277):
require_once('/var/www/html/s...')
#10 {main}

---------------------------------------------------------

Thankyou

_______________________________________________
MediaWiki-l mailing list
To unsubscribe, go to:
https://lists.wikimedia.org/mailman/listinfo/mediawiki-l

Re: Update.php error using postgres. squences and others already exists [ In reply to ]

tsukebumi at gmail

Jun 1, 2020, 3:02 PM

Post #2 of 4 (613 views)

Permalink

Solved it. Not sure if it is that something changed in postgres, but I
am using postgres 12 here.
After digging the source code and debugging the error messages, I
found a few places where it prepends 'default' to the name of tables
and sequences and other relations.

After I commented out those calls, it worked

On Sun, May 31, 2020 at 11:09 PM dabicho <tsukebumi@gmail.com> wrote:
>
> Hello
>
> I am new to the list
>
> I am trying to upgrade my mediawiki installation from 1.31.7 to 1.34.1
> and I am getting errors when executing php update.php.
> Same thing happened when trying to upgrade to 1.33.3
> I have seen bug reports dating back to 2016. Last suggestion being to
> move such things to the public schema instead of the database schema.
> Is this safe/good idea?
> What other solution can I apply?
>
> ----------- Example of output --------------------
> MediaWiki 1.34.1 Updater
>
> Your composer.lock file is up to date with current dependencies!
> Going to run database updates for mypedia-default-
> Depending on the size of your database this may take a while!
> ...skipping: 'mwuser' table doesn't exist yet.
> ...skipping: 'mwuser' table doesn't exist yet.
> ...skipping: 'pagecontent' table doesn't exist yet.
> Creating sequence logging_log_id_seq
> Wikimedia\Rdbms\DBQueryError from line 1603 of
> /var/www/html/mypedia/includes/libs/rdbms/database/Database.php: A
> database query error has occurred. Did you forget to run your
> application's database schema updater after upgrading?
> Query: CREATE SEQUENCE logging_log_id_seq
> Function: Wikimedia\Rdbms\DBConnRef::query
> Error: 42P07 ERROR: relation "logging_log_id_seq" already exists
>
>
> #0 /var/www/html/mypedia/includes/libs/rdbms/database/Database.php(1574):
> Wikimedia\Rdbms\Database->getQueryExceptionAndLog('ERROR:
> relatio...', '42P07', 'CREATE SEQUENCE...', 'Wikimedia\\Rdbms...')
> #1 /var/www/html/mypedia/includes/libs/rdbms/database/Database.php(1152):
> Wikimedia\Rdbms\Database->reportQueryError('ERROR: relatio...',
> '42P07', 'CREATE SEQUENCE...', 'Wikimedia\\Rdbms...', false)
> #2 /var/www/html/mypedia/includes/libs/rdbms/database/DBConnRef.php(68):
> Wikimedia\Rdbms\Database->query('CREATE SEQUENCE...',
> 'Wikimedia\\Rdbms...', 0)
> #3 /var/www/html/mypedia/includes/libs/rdbms/database/DBConnRef.php(292):
> Wikimedia\Rdbms\DBConnRef->__call('query', Array)
> #4 /var/www/html/mypedia/includes/installer/PostgresUpdater.php(833):
> Wikimedia\Rdbms\DBConnRef->query('CREATE SEQUENCE...')
> #5 /var/www/html/mypedia/includes/installer/DatabaseUpdater.php(490):
> PostgresUpdater->addSequence('logging', false, 'logging_log_id_...')
> #6 /var/www/html/mypedia/includes/installer/DatabaseUpdater.php(454):
> DatabaseUpdater->runUpdates(Array, false)
> #7 /var/www/html/mypedia/maintenance/update.php(205):
> DatabaseUpdater->doUpdates(Array)
> #8 /var/www/html/mypedia/maintenance/doMaintenance.php(99):
> UpdateMediaWiki->execute()
> #9 /var/www/html/mypedia/maintenance/update.php(277):
> require_once('/var/www/html/s...')
> #10 {main}
>
> ---------------------------------------------------------
>
>
>
> Thankyou

_______________________________________________
MediaWiki-l mailing list
To unsubscribe, go to:
https://lists.wikimedia.org/mailman/listinfo/mediawiki-l

Re: Update.php error using postgres. squences and others already exists [ In reply to ]

ladsgroup at gmail

Jun 2, 2020, 4:52 AM

Post #3 of 4 (612 views)

Permalink

Thanks for reporting this issue. We are planning to improve the situation
by first reducing the schema drift between PG and MySQL:
https://phabricator.wikimedia.org/T164898 and in the next step with
abstracting schema and shcema changes
https://phabricator.wikimedia.org/T191231

My request is that what exactly did you do to fix the issue? I want to turn
them to patches so we fix the upgrade path for other PG instances.
Thanks!

On Tue, Jun 2, 2020 at 12:03 AM dabicho <tsukebumi@gmail.com> wrote:

> Solved it. Not sure if it is that something changed in postgres, but I
> am using postgres 12 here.
> After digging the source code and debugging the error messages, I
> found a few places where it prepends 'default' to the name of tables
> and sequences and other relations.
>
> After I commented out those calls, it worked
>
> On Sun, May 31, 2020 at 11:09 PM dabicho <tsukebumi@gmail.com> wrote:
> >
> > Hello
> >
> > I am new to the list
> >
> > I am trying to upgrade my mediawiki installation from 1.31.7 to 1.34.1
> > and I am getting errors when executing php update.php.
> > Same thing happened when trying to upgrade to 1.33.3
> > I have seen bug reports dating back to 2016. Last suggestion being to
> > move such things to the public schema instead of the database schema.
> > Is this safe/good idea?
> > What other solution can I apply?
> >
> > ----------- Example of output --------------------
> > MediaWiki 1.34.1 Updater
> >
> > Your composer.lock file is up to date with current dependencies!
> > Going to run database updates for mypedia-default-
> > Depending on the size of your database this may take a while!
> > ...skipping: 'mwuser' table doesn't exist yet.
> > ...skipping: 'mwuser' table doesn't exist yet.
> > ...skipping: 'pagecontent' table doesn't exist yet.
> > Creating sequence logging_log_id_seq
> > Wikimedia\Rdbms\DBQueryError from line 1603 of
> > /var/www/html/mypedia/includes/libs/rdbms/database/Database.php: A
> > database query error has occurred. Did you forget to run your
> > application's database schema updater after upgrading?
> > Query: CREATE SEQUENCE logging_log_id_seq
> > Function: Wikimedia\Rdbms\DBConnRef::query
> > Error: 42P07 ERROR: relation "logging_log_id_seq" already exists
> >
> >
> > #0 /var/www/html/mypedia/includes/libs/rdbms/database/Database.php(1574):
> > Wikimedia\Rdbms\Database->getQueryExceptionAndLog('ERROR:
> > relatio...', '42P07', 'CREATE SEQUENCE...', 'Wikimedia\\Rdbms...')
> > #1 /var/www/html/mypedia/includes/libs/rdbms/database/Database.php(1152):
> > Wikimedia\Rdbms\Database->reportQueryError('ERROR: relatio...',
> > '42P07', 'CREATE SEQUENCE...', 'Wikimedia\\Rdbms...', false)
> > #2 /var/www/html/mypedia/includes/libs/rdbms/database/DBConnRef.php(68):
> > Wikimedia\Rdbms\Database->query('CREATE SEQUENCE...',
> > 'Wikimedia\\Rdbms...', 0)
> > #3 /var/www/html/mypedia/includes/libs/rdbms/database/DBConnRef.php(292):
> > Wikimedia\Rdbms\DBConnRef->__call('query', Array)
> > #4 /var/www/html/mypedia/includes/installer/PostgresUpdater.php(833):
> > Wikimedia\Rdbms\DBConnRef->query('CREATE SEQUENCE...')
> > #5 /var/www/html/mypedia/includes/installer/DatabaseUpdater.php(490):
> > PostgresUpdater->addSequence('logging', false, 'logging_log_id_...')
> > #6 /var/www/html/mypedia/includes/installer/DatabaseUpdater.php(454):
> > DatabaseUpdater->runUpdates(Array, false)
> > #7 /var/www/html/mypedia/maintenance/update.php(205):
> > DatabaseUpdater->doUpdates(Array)
> > #8 /var/www/html/mypedia/maintenance/doMaintenance.php(99):
> > UpdateMediaWiki->execute()
> > #9 /var/www/html/mypedia/maintenance/update.php(277):
> > require_once('/var/www/html/s...')
> > #10 {main}
> >
> > ---------------------------------------------------------
> >
> >
> >
> > Thankyou
>
> _______________________________________________
> MediaWiki-l mailing list
> To unsubscribe, go to:
> https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
>

--
Amir (he/him)
_______________________________________________
MediaWiki-l mailing list
To unsubscribe, go to:
https://lists.wikimedia.org/mailman/listinfo/mediawiki-l

Re: Update.php error using postgres. squences and others already exists [ In reply to ]

tsukebumi at gmail

Jun 2, 2020, 10:48 AM

Post #4 of 4 (611 views)

Permalink

Database.php has a function named tablename, near the end it calls a
function to prepend database or schema. This file seems not to be
exclusive to postgres, but the function is unnecessary and for some
reason it was prependin 'default.' as the schema. I commented out the
first call to prependDatabaseOrSchema.

DatabasePostgres.php had a similar issue on the method relationExists.
It was preppendin 'default' too. As that error appeared first, I fixed
it first by commenting out the call to realTableName, but on further
inspection it eventually calls prependDatabaseOrSchema

I did a dirty fix, did not dig deeper into the issue, but somewhere
the schema default is being used instead of the actual schema to
generate tablename, and the table pg_class does not include the schema
on the column relname, so queries involving that table were always
returning zero results.

i.e. a query to verify if a sequence exits was like

SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE
c.relnamespace = n.oid AND c.relname =
'default.page_restrictions_pr_id_seq' AND n.nspname = 'wikidb' AND
c.relkind IN ('S');

the correct query should have been

SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE
c.relnamespace = n.oid AND c.relname = 'page_restrictions_pr_id_seq'
AND n.nspname = 'wikidb' AND c.relkind IN ('S');

Hope it helps

On Tue, Jun 2, 2020 at 6:52 AM Amir Sarabadani <ladsgroup@gmail.com> wrote:
>
> Thanks for reporting this issue. We are planning to improve the situation by first reducing the schema drift between PG and MySQL: https://phabricator.wikimedia.org/T164898 and in the next step with abstracting schema and shcema changes https://phabricator.wikimedia.org/T191231
>
> My request is that what exactly did you do to fix the issue? I want to turn them to patches so we fix the upgrade path for other PG instances.
> Thanks!
>
>
> On Tue, Jun 2, 2020 at 12:03 AM dabicho <tsukebumi@gmail.com> wrote:
>>
>> Solved it. Not sure if it is that something changed in postgres, but I
>> am using postgres 12 here.
>> After digging the source code and debugging the error messages, I
>> found a few places where it prepends 'default' to the name of tables
>> and sequences and other relations.
>>
>> After I commented out those calls, it worked
>>
>> On Sun, May 31, 2020 at 11:09 PM dabicho <tsukebumi@gmail.com> wrote:
>> >
>> > Hello
>> >
>> > I am new to the list
>> >
>> > I am trying to upgrade my mediawiki installation from 1.31.7 to 1.34.1
>> > and I am getting errors when executing php update.php.
>> > Same thing happened when trying to upgrade to 1.33.3
>> > I have seen bug reports dating back to 2016. Last suggestion being to
>> > move such things to the public schema instead of the database schema.
>> > Is this safe/good idea?
>> > What other solution can I apply?
>> >
>> > ----------- Example of output --------------------
>> > MediaWiki 1.34.1 Updater
>> >
>> > Your composer.lock file is up to date with current dependencies!
>> > Going to run database updates for mypedia-default-
>> > Depending on the size of your database this may take a while!
>> > ...skipping: 'mwuser' table doesn't exist yet.
>> > ...skipping: 'mwuser' table doesn't exist yet.
>> > ...skipping: 'pagecontent' table doesn't exist yet.
>> > Creating sequence logging_log_id_seq
>> > Wikimedia\Rdbms\DBQueryError from line 1603 of
>> > /var/www/html/mypedia/includes/libs/rdbms/database/Database.php: A
>> > database query error has occurred. Did you forget to run your
>> > application's database schema updater after upgrading?
>> > Query: CREATE SEQUENCE logging_log_id_seq
>> > Function: Wikimedia\Rdbms\DBConnRef::query
>> > Error: 42P07 ERROR: relation "logging_log_id_seq" already exists
>> >
>> >
>> > #0 /var/www/html/mypedia/includes/libs/rdbms/database/Database.php(1574):
>> > Wikimedia\Rdbms\Database->getQueryExceptionAndLog('ERROR:
>> > relatio...', '42P07', 'CREATE SEQUENCE...', 'Wikimedia\\Rdbms...')
>> > #1 /var/www/html/mypedia/includes/libs/rdbms/database/Database.php(1152):
>> > Wikimedia\Rdbms\Database->reportQueryError('ERROR: relatio...',
>> > '42P07', 'CREATE SEQUENCE...', 'Wikimedia\\Rdbms...', false)
>> > #2 /var/www/html/mypedia/includes/libs/rdbms/database/DBConnRef.php(68):
>> > Wikimedia\Rdbms\Database->query('CREATE SEQUENCE...',
>> > 'Wikimedia\\Rdbms...', 0)
>> > #3 /var/www/html/mypedia/includes/libs/rdbms/database/DBConnRef.php(292):
>> > Wikimedia\Rdbms\DBConnRef->__call('query', Array)
>> > #4 /var/www/html/mypedia/includes/installer/PostgresUpdater.php(833):
>> > Wikimedia\Rdbms\DBConnRef->query('CREATE SEQUENCE...')
>> > #5 /var/www/html/mypedia/includes/installer/DatabaseUpdater.php(490):
>> > PostgresUpdater->addSequence('logging', false, 'logging_log_id_...')
>> > #6 /var/www/html/mypedia/includes/installer/DatabaseUpdater.php(454):
>> > DatabaseUpdater->runUpdates(Array, false)
>> > #7 /var/www/html/mypedia/maintenance/update.php(205):
>> > DatabaseUpdater->doUpdates(Array)
>> > #8 /var/www/html/mypedia/maintenance/doMaintenance.php(99):
>> > UpdateMediaWiki->execute()
>> > #9 /var/www/html/mypedia/maintenance/update.php(277):
>> > require_once('/var/www/html/s...')
>> > #10 {main}
>> >
>> > ---------------------------------------------------------
>> >
>> >
>> >
>> > Thankyou
>>
>> _______________________________________________
>> MediaWiki-l mailing list
>> To unsubscribe, go to:
>> https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
>
>
>
> --
> Amir (he/him)
>
_______________________________________________
MediaWiki-l mailing list
To unsubscribe, go to:
https://lists.wikimedia.org/mailman/listinfo/mediawiki-l