Upgrade 2024.10 relation mshop_attribute_list_type has default value

[columbo]

Hi,

I'm upgrading from Aimeos 2023.10.8 to 2024.10 using PostgreSQL;
Error after executing:

Code: Select all

php artisan aimeos:setup

An exception occurred while executing a query: SQLSTATE[55000]: 
Object not in prerequisite state: 7 ERROR:  column "id" of relation "mshop_attribute_list_type" already has a default value

I am not sure why this error occurs - What do you suggest to solve this problem?

Detail errorlog:

Code: Select all

ERROR: An exception occurred while executing a query: SQLSTATE[55000]: Object not in prerequisite state: 7 ERROR:  column "id" of relation "mshop_attribute_property_type" already has a default value {"exception":"[object] (Doctrine\\DBAL\\Exception\\DriverException(code: 7): An exception occurred while executing a query: SQLSTATE[55000]: Object not in prerequisite state: 7 ERROR:  column \"id\" of relation \"mshop_attribute_property_type\" already has a default value at /vendor/doctrine/dbal/src/Driver/API/PostgreSQL/ExceptionConverter.php:80)
[stacktrace]
#0 /vendor/doctrine/dbal/src/Connection.php(1373): Doctrine\\DBAL\\Driver\\API\\PostgreSQL\\ExceptionConverter->convert()
#1 /vendor/doctrine/dbal/src/Connection.php(1315): Doctrine\\DBAL\\Connection->handleDriverException()
#2 /vendor/doctrine/dbal/src/Connection.php(880): Doctrine\\DBAL\\Connection->convertExceptionDuringQuery()
#3 /vendor/aimeos/upscheme/src/Schema/DB.php(763): Doctrine\\DBAL\\Connection->executeStatement()
#4 /vendor/aimeos/upscheme/src/Up.php(344): Aimeos\\Upscheme\\Schema\\DB->up()
#5 /vendor/aimeos/upscheme/src/Up.php(172): Aimeos\\Upscheme\\Up->runTasks()
#6 /vendor/aimeos/aimeos-core/Setup.php(84): Aimeos\\Upscheme\\Up->up()
#7 /vendor/aimeos/aimeos-laravel/src/Command/SetupCommand.php(65): Aimeos\\Setup->up()
#8 /vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(36): Aimeos\\Shop\\Command\\SetupCommand->handle()
#9 /vendor/laravel/framework/src/Illuminate/Container/Util.php(43): Illuminate\\Container\\BoundMethod::Illuminate\\Container\\{closure}()
#10 /vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(95): Illuminate\\Container\\Util::unwrapIfClosure()
#11 /vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(35): Illuminate\\Container\\BoundMethod::callBoundMethod()
#12 /vendor/laravel/framework/src/Illuminate/Container/Container.php(690): Illuminate\\Container\\BoundMethod::call()
#13 /vendor/laravel/framework/src/Illuminate/Console/Command.php(213): Illuminate\\Container\\Container->call()
#14 /vendor/symfony/console/Command/Command.php(279): Illuminate\\Console\\Command->execute()
#15 /vendor/laravel/framework/src/Illuminate/Console/Command.php(182): Symfony\\Component\\Console\\Command\\Command->run()
#16 /vendor/symfony/console/Application.php(1047): Illuminate\\Console\\Command->run()
#17 /vendor/symfony/console/Application.php(316): Symfony\\Component\\Console\\Application->doRunCommand()
#18 /vendor/symfony/console/Application.php(167): Symfony\\Component\\Console\\Application->doRun()
#19 /vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php(197): Symfony\\Component\\Console\\Application->run()
#20 /artisan(35): Illuminate\\Foundation\\Console\\Kernel->handle()
#21 {main}

Thank you!

[aimeos]

Doctrine DBAL 4.x uses IDENTITY instead of SERIAL for "id" columns:
https://github.com/doctrine/dbal/blob/1 ... #L398-L404

Unfortunately, it doesn't upgrade the columns itself and only links to a file which describes how to do it manually. To simplify the upgrade, we've added code in Upscheme as a workaround to do the migration automatically. Can you please execute this command and test if it works?

Code: Select all

composer req aimeos/upscheme:1.x-dev

[columbo]

FYI: changed in composer.json to:

Code: Select all

"aimeos/upscheme": "1.x-dev as 0.9"

as aimeos-core/Composer.json requires "aimeos/upscheme": "~0.9"


now i get with php artisan aimeos:setup:

Code: Select all

Migrating: vendor/aimeos/aimeos-core/setup/AttributeMigrateKey.php

Doctrine\DBAL\Exception\SyntaxErrorException

An exception occurred while executing a query: SQLSTATE[42601]: Syntax error: 7 ERROR:  column "id" of relation "mshop_attribute_type" is an identity column
HINT:  Use ALTER TABLE ... ALTER COLUMN ... DROP IDENTITY instead.
CONTEXT:  SQL statement "ALTER TABLE mshop_attribute_type ALTER COLUMN id DROP DEFAULT"
PL/pgSQL function upscheme_serial_to_identity(text,text) line 19 at EXECUTE

  at vendor/doctrine/dbal/src/Driver/API/PostgreSQL/ExceptionConverter.php:62
     58▕             case '3F000':
     59▕                 return new SchemaDoesNotExist($exception, $query);
     60▕
     61▕             case '42601':
  ➜  62▕                 return new SyntaxErrorException($exception, $query);
     63▕
     64▕             case '42702':
     65▕                 return new NonUniqueFieldNameException($exception, $query);
     66▕

      +31 vendor frames

  32  artisan:35
      Illuminate\Foundation\Console\Kernel::handle()

[aimeos]

Can you restart with a clean 2023.10 DB dump? It looks like there's a column wrongly migrated by DBAL.

[columbo]

restart with 2023.10 DB; now facing issues with mshop_order_service_attr

Code: Select all

...
Migrating: vendor/aimeos/aimeos-core/setup/Order.php

   Doctrine\DBAL\Exception\DriverException

  An exception occurred while executing a query: SQLSTATE[55000]: Object not in prerequisite state: 7 ERROR:  column "id" of relation "mshop_order_service" already has a default value

  at vendor/doctrine/dbal/src/Driver/API/PostgreSQL/ExceptionConverter.php:80
     76▕             case '08006':
     77▕                 return new ConnectionException($exception, $query);
     78▕         }
     79▕
  ➜  80▕         return new DriverException($exception, $query);
     81▕     }
     82▕ }
     83▕

      +33 vendor frames

  34  artisan:35
      Illuminate\Foundation\Console\Kernel::handle()

[aimeos]

Can you please post the list of sequences from your database? Use "\ds" in psql client

[columbo]

Code: Select all

                           List of relations
 Schema |                 Name                 |   Type   |   Owner
--------+--------------------------------------+----------+------------
 public | activity_log_id_seq                  | sequence | db_webshop
 public | app_settings_id_seq                  | sequence | db_webshop
 public | blocks_id_seq                        | sequence | db_webshop
 public | employee_slugs_id_seq                | sequence | db_webshop
 public | employees_id_seq                     | sequence | db_webshop
 public | failed_jobs_id_seq                   | sequence | db_webshop
 public | features_id_seq                      | sequence | db_webshop
 public | fileables_id_seq                     | sequence | db_webshop
 public | files_id_seq                         | sequence | db_webshop
 public | madmin_job_id_seq                    | sequence | db_webshop
 public | madmin_log_id_seq                    | sequence | db_webshop
 public | madmin_queue_id_seq                  | sequence | db_webshop
 public | mediables_id_seq                     | sequence | db_webshop
 public | medias_id_seq                        | sequence | db_webshop
 public | migrations_id_seq                    | sequence | db_webshop
 public | mshop_attribute_id_seq               | sequence | db_webshop
 public | mshop_attribute_list_id_seq          | sequence | db_webshop
 public | mshop_attribute_list_type_id_seq     | sequence | db_webshop
 public | mshop_attribute_property_id_seq      | sequence | db_webshop
 public | mshop_attribute_property_type_id_seq | sequence | db_webshop
 public | mshop_attribute_type_id_seq          | sequence | db_webshop
 public | mshop_catalog_id_seq                 | sequence | db_webshop
 public | mshop_catalog_list_id_seq            | sequence | db_webshop
 public | mshop_catalog_list_type_id_seq       | sequence | db_webshop
 public | mshop_cms_id_seq                     | sequence | db_webshop
 public | mshop_cms_list_id_seq                | sequence | db_webshop
 public | mshop_cms_list_type_id_seq           | sequence | db_webshop
 public | mshop_coupon_code_id_seq             | sequence | db_webshop
 public | mshop_coupon_id_seq                  | sequence | db_webshop
 public | mshop_customer_address_id_seq        | sequence | db_webshop
 public | mshop_customer_group_id_seq          | sequence | db_webshop
 public | mshop_customer_id_seq                | sequence | db_webshop
 public | mshop_customer_list_id_seq           | sequence | db_webshop
 public | mshop_customer_list_type_id_seq      | sequence | db_webshop
 public | mshop_customer_property_id_seq       | sequence | db_webshop
 public | mshop_customer_property_type_id_seq  | sequence | db_webshop
 public | mshop_index_text_id_seq              | sequence | db_webshop
 public | mshop_locale_id_seq                  | sequence | db_webshop
 public | mshop_locale_site_id_seq             | sequence | db_webshop
 public | mshop_media_id_seq                   | sequence | db_webshop
 public | mshop_media_list_id_seq              | sequence | db_webshop
 public | mshop_media_list_type_id_seq         | sequence | db_webshop
 public | mshop_media_property_id_seq          | sequence | db_webshop
 public | mshop_media_property_type_id_seq     | sequence | db_webshop
 public | mshop_media_type_id_seq              | sequence | db_webshop
 public | mshop_order_base_address_id_seq      | sequence | db_webshop
 public | mshop_order_base_coupon_id_seq       | sequence | db_webshop
 public | mshop_order_base_product_attr_id_seq | sequence | db_webshop
 public | mshop_order_base_product_id_seq      | sequence | db_webshop
 public | mshop_order_base_service_attr_id_seq | sequence | db_webshop
 public | mshop_order_base_service_id_seq      | sequence | db_webshop
 public | mshop_order_id_seq                   | sequence | db_webshop
 public | mshop_order_service_tx_id_seq        | sequence | db_webshop
 public | mshop_order_status_id_seq            | sequence | db_webshop
 public | mshop_plugin_id_seq                  | sequence | db_webshop
 public | mshop_plugin_type_id_seq             | sequence | db_webshop
 public | mshop_price_id_seq                   | sequence | db_webshop
 public | mshop_price_list_id_seq              | sequence | db_webshop
 public | mshop_price_list_type_id_seq         | sequence | db_webshop
 public | mshop_price_property_id_seq          | sequence | db_webshop
 public | mshop_price_property_type_id_seq     | sequence | db_webshop
 public | mshop_price_type_id_seq              | sequence | db_webshop
 public | mshop_product_id_seq                 | sequence | db_webshop
 public | mshop_product_list_id_seq            | sequence | db_webshop
 public | mshop_product_list_type_id_seq       | sequence | db_webshop
 public | mshop_product_property_id_seq        | sequence | db_webshop
 public | mshop_product_property_type_id_seq   | sequence | db_webshop
 public | mshop_product_type_id_seq            | sequence | db_webshop
 public | mshop_review_id_seq                  | sequence | db_webshop
 public | mshop_rule_id_seq                    | sequence | db_webshop
 public | mshop_rule_type_id_seq               | sequence | db_webshop
 public | mshop_service_id_seq                 | sequence | db_webshop
 public | mshop_service_list_id_seq            | sequence | db_webshop
 public | mshop_service_list_type_id_seq       | sequence | db_webshop
 public | mshop_service_type_id_seq            | sequence | db_webshop
 public | mshop_stock_id_seq                   | sequence | db_webshop
 public | mshop_stock_type_id_seq              | sequence | db_webshop
 public | mshop_subscription_id_seq            | sequence | db_webshop
 public | mshop_supplier_address_id_seq        | sequence | db_webshop
 public | mshop_supplier_id_seq                | sequence | db_webshop
 public | mshop_supplier_list_id_seq           | sequence | db_webshop
 public | mshop_supplier_list_type_id_seq      | sequence | db_webshop
 public | mshop_tag_id_seq                     | sequence | db_webshop
 public | mshop_tag_type_id_seq                | sequence | db_webshop
 public | mshop_text_id_seq                    | sequence | db_webshop
 public | mshop_text_list_id_seq               | sequence | db_webshop
 public | mshop_text_list_type_id_seq          | sequence | db_webshop
 public | mshop_text_type_id_seq               | sequence | db_webshop
 public | offer_revisions_id_seq               | sequence | db_webshop
 public | offer_slugs_id_seq                   | sequence | db_webshop
 public | offers_id_seq                        | sequence | db_webshop
 public | page_revisions_id_seq                | sequence | db_webshop
 public | page_slugs_id_seq                    | sequence | db_webshop
 public | page_translations_id_seq             | sequence | db_webshop
 public | pages_id_seq                         | sequence | db_webshop
 public | personal_access_tokens_id_seq        | sequence | db_webshop
 public | setting_translations_id_seq          | sequence | db_webshop
 public | settings_id_seq                      | sequence | db_webshop
 public | tagged_id_seq                        | sequence | db_webshop
 public | tags_id_seq                          | sequence | db_webshop
 public | twill_blocks_id_seq                  | sequence | db_webshop
 public | twill_fileables_id_seq               | sequence | db_webshop
 public | twill_files_id_seq                   | sequence | db_webshop
 public | twill_mediables_id_seq               | sequence | db_webshop
 public | twill_medias_id_seq                  | sequence | db_webshop
 public | twill_related_id_seq                 | sequence | db_webshop
 public | twill_tagged_id_seq                  | sequence | db_webshop
 public | twill_tags_id_seq                    | sequence | db_webshop
 public | twill_users_id_seq                   | sequence | db_webshop
 public | users_address_id_seq                 | sequence | db_webshop
 public | users_id_seq                         | sequence | db_webshop
 public | users_list_id_seq                    | sequence | db_webshop
 public | users_list_type_id_seq               | sequence | db_webshop
 public | users_property_id_seq                | sequence | db_webshop
 public | users_property_type_id_seq           | sequence | db_webshop
(115 rows)

...we also use twillcms - so please ignore twill_* tables

[aimeos]

OK, you've upgraded from 2022.10 to 2023.10 to 2024.10 and PostgreSQL don't rename the sequences when the tables are renamed so we have a table "mshop_order_service" with a sequence "mshop_order_base_service_id_seq". We've changed that in Upscheme to upgrade all sequences even if the sequence name doesn't follow the "<table>_<column>_seq" rule. Please execute "composer up" to get the latest version of Upscheme 1.x-dev and then run the setup task again.

[columbo]

now it works - thank you!