[PHPBB3-14923] SQL PostgreSQL blocking errors during DB update installation

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Blocker
Resolution: Fixed
Affects Version/s: 3.1.10
Fix Version/s: 3.1.11-RC1, 3.2.0
Component/s: Database Abstraction Layer (DBAL)
Labels:
- postgresql
- update-script
Environment:
PHP 5.6.19, PostgreSQL 9.2, Firefox 50.1.0

Description

I have updated postgresql database from 3.0.14 to 3.1.10 using /install/database_update.php

but I encountered some SQL errors.

1)

_SQL ERROR [ postgres ]

ERREUR: erreur de syntaxe à la fin de l'entrée LINE 4: LIMIT 250 OFFSET ^ []

SQL

SELECT user_id, user_notify_type, user_notify_pm FROM phpbb_users ORDER BY user_id LIMIT 250 OFFSET

BACKTRACE

FILE: (not given by php)
LINE: (not given by php)
CALL: msg_handler()

FILE: [ROOT]/phpbb/db/driver/driver.php
LINE: 855
CALL: trigger_error()

FILE: [ROOT]/phpbb/db/driver/postgres.php
LINE: 191
CALL: phpbb\db\driver\driver->sql_error()

FILE: [ROOT]/phpbb/db/driver/postgres.php
LINE: 250
CALL: phpbb\db\driver\postgres->sql_query()

FILE: [ROOT]/phpbb/db/driver/driver.php
LINE: 261
CALL: phpbb\db\driver\postgres->_sql_query_limit()

FILE: [ROOT]/phpbb/db/driver/factory.php
LINE: 321
CALL: phpbb\db\driver\driver->sql_query_limit()

FILE: [ROOT]/phpbb/db/migration/data/v310/notification_options_reconvert.php
LINE: 59
CALL: phpbb\db\driver\factory->sql_query_limit()

FILE: [ROOT]/phpbb/db/migration/data/v310/notification_options_reconvert.php
LINE: 41
CALL: phpbb\db\migration\data\v310\notification_options_reconvert->perform_conversion()

FILE: (not given by php)
LINE: (not given by php)
CALL: phpbb\db\migration\data\v310\notification_options_reconvert->convert_notifications()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 591
CALL: call_user_func_array()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 538
CALL: phpbb\db\migrator->run_step()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 361
CALL: phpbb\db\migrator->process_data_step()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 276
CALL: phpbb\db\migrator->try_apply()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 216
CALL: phpbb\db\migrator->try_apply()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 199
CALL: phpbb\db\migrator->update_do()

FILE: [ROOT]/install/database_update.php
LINE: 205
CALL: phpbb\db\migrator->update()_

2) Root cause seems similar to ~~PHPBB3-12007~~ e.g. PostgreSQL dislikes NULL instead of zero

Not sure if there are some regressions because fixes from ~~PHPBB3-12007~~ seems to be here.

To unblock, I updated

/phpbb/db/migration/profilefield_base_migration.php

Around line 189

public function convert_user_field_to_custom_field($start)
{
$insert_buffer = new \phpbb\db\sql_insert_buffer($this->db, $this->table_prefix . 'profile_fields_data');
$limit = 250;
$converted_users = 0;
// BEGIN Unblock
if (empty($start))

{ $start = 0; }
// END Unblock

3) Migration ran a little further but I encounter same kind of errors

SQL ERROR [ postgres ]

ERREUR: erreur de syntaxe à la fin de l'entrée LINE 5: LIMIT 250 OFFSET ^ []

SQL

SELECT user_id, user_interests FROM phpbb_users WHERE user_interests <> '' ORDER BY user_id LIMIT 250 OFFSET

BACKTRACE

FILE: (not given by php)
LINE: (not given by php)
CALL: msg_handler()

FILE: [ROOT]/phpbb/db/driver/driver.php
LINE: 855
CALL: trigger_error()

FILE: [ROOT]/phpbb/db/driver/postgres.php
LINE: 191
CALL: phpbb\db\driver\driver->sql_error()

FILE: [ROOT]/phpbb/db/driver/postgres.php
LINE: 250
CALL: phpbb\db\driver\postgres->sql_query()

FILE: [ROOT]/phpbb/db/driver/driver.php
LINE: 261
CALL: phpbb\db\driver\postgres->_sql_query_limit()

FILE: [ROOT]/phpbb/db/driver/factory.php
LINE: 321
CALL: phpbb\db\driver\driver->sql_query_limit()

FILE: [ROOT]/phpbb/db/migration/profilefield_base_migration.php
LINE: 205
CALL: phpbb\db\driver\factory->sql_query_limit()

FILE: (not given by php)
LINE: (not given by php)
CALL: phpbb\db\migration\profilefield_base_migration->convert_user_field_to_custom_field()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 591
CALL: call_user_func_array()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 538
CALL: phpbb\db\migrator->run_step()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 361
CALL: phpbb\db\migrator->process_data_step()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 276
CALL: phpbb\db\migrator->try_apply()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 276
CALL: phpbb\db\migrator->try_apply()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 276
CALL: phpbb\db\migrator->try_apply()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 276
CALL: phpbb\db\migrator->try_apply()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 276
CALL: phpbb\db\migrator->try_apply()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 276
CALL: phpbb\db\migrator->try_apply()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 276
CALL: phpbb\db\migrator->try_apply()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 276
CALL: phpbb\db\migrator->try_apply()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 276
CALL: phpbb\db\migrator->try_apply()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 276
CALL: phpbb\db\migrator->try_apply()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 276
CALL: phpbb\db\migrator->try_apply()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 276
CALL: phpbb\db\migrator->try_apply()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 276
CALL: phpbb\db\migrator->try_apply()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 216
CALL: phpbb\db\migrator->try_apply()

FILE: [ROOT]/phpbb/db/migrator.php
LINE: 199
CALL: phpbb\db\migrator->update_do()

FILE: [ROOT]/install/database_update.php
LINE: 205
CALL: phpbb\db\migrator->update()

4) To unblock, I updated

phpbb/db/migration/profilefield_base_migration.php

Around line 189

public function convert_user_field_to_custom_field($start)
{
$insert_buffer = new \phpbb\db\sql_insert_buffer($this->db, $this->table_prefix . 'profile_fields_data');
$limit = 250;
$converted_users = 0;
// BEGIN Unblock
if (empty($start))
{ $start = 0; }

// END

5) Like that, it went until the end of database update process with no other error

SQL PostgreSQL blocking errors during DB update installation

Details

Description

Attachments

Activity

People

Dates