SOLVED Prestashop update 8.1.2 to 8.1.5 fails on updating database schema

[Martin C]

Update prestashop from 8.1.2 to 8.1.5 with 1-Click Upgrade  module version 5.0.1.
Updating database schema fails:
Mariadb version 10.6.17.

How can i manually execute the queries to update the database schema? Where can i find the queries?

Updating database schema...
Executing: ALTER TABLE ps_feature_flag CHANGE state state TINYINT(1) DEFAULT '0'
Executing: AtTER TABLE ps_tab DROP hide_host_mode
Executing: ALTER TABLE ps_translation CHANGE `key` `key` TEXT NOT NULL COLLATE `utf8_bin`
Executing: ALTER TABLE ps_bk_payment_methods CHANGE created_at created_at DATETIME NOT NULL DEFAULT 'current_timestamp()' , CHANGE updated_at updated_at DATETIME NOT NULL
19:32:17 CRITICAL [console] Error thrown while running command "prestashop:schema:update-without-foreign --env=prod". Message: "An exception occurred while executing 'ALTER TABLE ps_C CHANGE created_at created_at DATETIME NOT NULL DEFAULT 'current_timestamp()' , CHANGE updated_at updated_at DATETIME NOT NULL':
SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'created_at'" ["exception" => Doctrine\DBAL\Exception\DriverException { …},"command" => "prestashop:schema:update-without-foreign --env=prod","message" => """ An exception occurred while executing 'ALTER TABLE ps_bk_payment_methods CHANGE created_at created_at DATETIME NOT NULL DEFAULT 'current_timestamp()' , CHANGE updated_at updated_at DATETIME NOT NULL':\n \n SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'created_at' """]
In AbstractMySQLDriver.php line 128:
An exception occurred while executing 'ALTER TABLE ps_bk_payment_methods CH
ANGE created_at created_at DATETIME NOT NULL DEFAULT 'current_timestamp()'
, CHANGE updated_at updated_at DATETIME NOT NULL':
SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default val
ue for 'created_at'
In Exception.php line 18:
SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default val
ue for 'created_at'
In PDOConnection.php line 141:
SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default val
ue for 'created_at'
prestashop:schema:update-without-foreign [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--id_shop [ID_SHOP]] [--id_shop_group [ID_SHOP_GROUP]] [--]

Edited April 21 by Martin C
solved the issue (see edit history)

[Martin C]

Why does the update fail?

[Nickz]

14 minutes ago, Martin C said:

How can i manually execute the queries to update the database schema? Where can i find the queries?

Make a back up first, you can edit the database over BO and over PhPmyadmin.

[Martin C]

Yes i know that but i want to know which changes you have to make to fully update the database schema. I found out that the queries are made on the fly by the UpdateSchemaCommand.php file.

Is it only the table ALTER TABLE ps_bk_payment_methods which fails or is the update script stepping out of the function because of the error and there are more tables which must be altered?

Martin

 

[Nickz]

This might fit: https://stackoverflow.com/questions/61262556/symfony-base-table-or-view-already-exists-1050-table-migration-versions-alre

[Martin C]

Thanks but not not the same issue.

[Martin C]

Probably the query is wrong:

ALTER TABLE ps_bk_payment_methods CHANGE created_at created_at DATETIME NOT NULL DEFAULT 'current_timestamp()'

This must be without quotes to be accepted by MariaDB:

ALTER TABLE ps_bk_payment_methods CHANGE created_at created_at DATETIME NOT NULL DEFAULT current_timestamp()

IS MySQL configured wrong or is it a prestashop upgrade bug?

[Martin C]

Solved removed the buckaroo tables update Prestashop and added the Buckaroo tables again.