php artisan migrate with MariaDB 10.5.15

[alexandreIP]

MariaDB Server version: 10.5.15-MariaDB-0+deb11u1 Raspbian 11

PHP 8.2.1 (cli) (built: Jan 7 2023 08:34:34) (NTS) Copyright (c) The PHP Group Zend Engine v4.2.1, Copyright (c) Zend Technologies with Zend OPcache v8.2.1, Copyright (c), by Zend Technologies

fresh install : ip@raspberrypi:/var/www/phpip $ php artisan migrate --seed

INFO Preparing database.

Creating migration table .......................................................................................................... 608ms DONE

INFO Running migrations.

2014_10_12_100000_create_password_resets_table .................................................................................... 234ms DONE 2018_12_07_184310_create_actor_role_table ......................................................................................... 482ms DONE 2018_12_07_184310_create_actor_table ............................................................................................ 1,926ms DONE 2018_12_07_184310_create_classifier_table ....................................................................................... 1,422ms DONE 2018_12_07_184310_create_classifier_type_table .................................................................................... 502ms DONE 2018_12_07_184310_create_classifier_value_table ................................................................................... 839ms DONE 2018_12_07_184310_create_country_table ............................................................................................ 365ms DONE 2018_12_07_184310_create_default_actor_table ...................................................................................... 597ms DONE 2018_12_07_184310_create_event_name_table ......................................................................................... 971ms DONE 2018_12_07_184310_create_event_table .............................................................................................. 746ms DONE 2018_12_07_184310_create_matter_actor_lnk_table ................................................................................. 1,133ms DONE 2018_12_07_184310_create_matter_category_table .................................................................................... 487ms DONE 2018_12_07_184310_create_matter_table ............................................................................................. 333ms FAIL

Illuminate\Database\QueryException

SQLSTATE[HY000]: General error: 1901 Function or expression 'concat_ws('',concat_ws('-',concat_ws('/',country,origin),type_code),idx)' cannot be used in the GENERATED ALWAYS AS clause of suffix (SQL: alter table matter add unique UID(category_code, caseref, suffix))

at vendor/laravel/framework/src/Illuminate/Database/Connection.php:760 756▕ // If an exception occurs when attempting to run a query, we'll format the error 757▕ // message to include the bindings with SQL, which will make this exception a 758▕ // lot more helpful to the developer instead of just the database's errors. 759▕ catch (Exception $e) { ➜ 760▕ throw new QueryException( 761▕ $query, $this->prepareBindings($bindings), $e 762▕ ); 763▕ } 764▕ }

  +12 vendor frames 

13 database/migrations/2018_12_07_184310_create_matter_table.php:15 Illuminate\Support\Facades\Facade::__callStatic()

  +25 vendor frames 

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

[jjdejong]

Please don't create a new issue for an already open one. This was issue #117, which I've now closed. Anyway, as I said, I already saw such issues happening with MariaDB. Can you try a "genuine" MySQL db? I believe MySQL allows creating indexes on non-stored generated columns, whereas MariaDB requires the generated columns to be stored. I can't do anything about this, because the SQL queries are produced by Laravel's migration script processing, which is agnostic about the "stored" status of generated columns.

[alexandreIP]

Sorry, I thought it was closed because of your answer about mariaDB version. Will try to find a "genuine" MySQL db for Raspberry.

[jjdejong]

In the migration script, try replacing $table->string('suffix', 16)->virtualAs by $table->string('suffix', 16)->storedAs

[alexandreIP]

                    $table->string('suffix',

16)->storedAs('concat_ws("",concat_ws("-",concat_ws("/",country,origin),type_code),idx)');

gives

SQLSTATE[HY000]: General error: 1901 Function or expression 'concat_ws('',concat_ws('-',concat_ws('/',country,origin),type_code),idx)' cannot be used in the GENERATED ALWAYS AS clause of suffix (SQL: create table matter (id int unsigned not null auto_increment primary key, category_code char(5) not null, caseref varchar(30) not null comment 'Case reference for the database user. The references for the other actors (client, agent, etc.) are in the actor link table.', country char(2) not null comment 'Country where the matter is filed', origin char(2) null comment 'Code of the regional system the patent originates from (mainly EP or WO)', type_code char(5) null, idx tinyint(1) null comment 'Increment this to differentiate multiple patents filed in the same country in the same family', suffix varchar(16) as (concat_ws("",concat_ws("-",concat_ws("/",country,origin),type_code),idx)) stored, parent_id int unsigned null comment 'Link to parent patent. Used to create a hierarchy', container_id int unsigned null comment 'Identifies the container matter from which this matter gathers its shared data. If null, this matter is a container', responsible char(16) not null comment 'Database user responsible for the patent', dead tinyint(1) not null default '0' comment 'Indicates that the case is no longer supervised. Automatically set by \"killer events\" like \"Abandoned\"', notes text null, expire_date date null, term_adjust smallint not null default '0' comment 'Patent term adjustment in days. Essentially for US patents.', creator char(16) null comment 'User who created the record', updater char(16) null comment 'User who last modified the record', created_at timestamp null, updated_at timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB)

at vendor/laravel/framework/src/Illuminate/Database/Connection.php:760 756▕ // If an exception occurs when attempting to run a query, we'll format the error 757▕ // message to include the bindings with SQL, which will make this exception a 758▕ // lot more helpful to the developer instead of just the database's errors. 759▕ catch (Exception $e) { ➜ 760▕ throw new QueryException( 761▕ $query, $this->prepareBindings($bindings), $e 762▕ ); 763▕ } 764▕ }

  +12 vendor frames

13 database/migrations/2018_12_07_184310_create_matter_table.php:15 Illuminate\Support\Facades\Facade::__callStatic()

  +25 vendor frames

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

[jjdejong]

Well, don't know. I can't reproduce the problem with my MySQL 8.0 installation. Try installing MySQL and let me know.

[alexandreIP]

Until now, I can't find any website about installing MySql on Raspberry, which is a pity.

[alexandreIP]

It works well with ubuntu server

[jjdejong]

Sure, that's our development platform. Otherwise, maybe you can contribute a fix for MariaDB users. @papoteur-mga uses MariaDB, what's his input?

[papoteur-mga]

Hello, Sorry for the delay. I encountered also such a problem. This was with an export from MariaDB 10.3.27 to an import in MariaDB 10.5.8. According to my notes, I got in creation of task_rules table:

  `uid` varchar(32) GENERATED ALWAYS AS
(md5(concat(`task`,`trigger_event`,`clear_task`,`delete_task`,`for_category`,ifnull(`for_country`,'c'),ifnull(`for_origin`,'o'),ifnull(`for_type`,'t'),`days`,`months`,`years`,`recurring`,ifnull(`abort_on`,'a'),ifnull(`condition_event`,'c'),`use_priority`,ifnull(`detail`,'d'))))
VIRTUAL,

ERROR 1901 (HY000) at line 1600: Function or expression
'concat(`task`,`trigger_event`,`clear_task`,`delete_task`,`for_category`,ifnull(`for_country`,'c'),ifnull(`for_origin`,'o'),ifnull(`for_type`,'t'),`days`,`months`,`years`,`recurring`,ifnull(`abort_on`,'a'),ifnull(`condition_event`,'c'),`use_priority`,ifnull(`detail`,'d'))'
cannot be used in the GENERATED ALWAYS AS clause of `uid` 

I replaced uid with a simple field to go further. After the import, I suppressed the column and created a virtual column with said definition, and this passed.