Uploaded image for project: 'phpBB'
  1. phpBB
  2. PHPBB-14923

SQL PostgreSQL blocking errors during DB update installation

XMLWordPrintable

      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: 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()_

      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

            Marc Marc
            pazufr pazufr [X] (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Created:
              Updated:
              Resolved: