Error in generated column (MySQL) in Laravel by storedAs()

Question

here is my migration script

Schema::create('orders', function (Blueprint $table) {
            .
            .
            .
            $table->decimal('coupon_discount')->default(0);
            $table->decimal('delivery_charge')->default(0);
            $table->decimal('sub_total')->default(0);
            $table->decimal('total')->storedAs('(sub_total + delivery_charge) - coupon_discount');
            $table->decimal('paid')->default(0);
            $table->decimal('due')->storedAs('total - paid');
            $table->longText('note')->nullable();
            .
            .
            .
            $table->timestamps();
        });

i have to create generated-columns for total and due columns.

but while i tried to run the migration file its showing exception

Exception:

Illuminate\Database\QueryException : SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'stored, paid decimal(8, 2 ) not null default '0', due decimal(8, 2) as (total' at line 1 (SQL:

create table orders (
    id int unsigned not null auto_increment primary key, 
    user_id int unsigned null, 
    area_id int unsigned not null, 
    location_id int unsigned not null, 
    delivery_add ress longtext not null, 
    delivery_address_id int unsigned null, 
    mobile varchar(191) not null, 
    email varchar(191) null, 
    coupon_id int unsigned null, 
    coupon_discount decimal(8, 2) not null default '0', 
    delivery_charge decimal(8, 2) not null default '0 ', 
    sub_total decimal(8, 2) not null default '0', 
    total decimal(8, 
    2) as ((sub_total + delivery_charge) - coupon_discount) stored, 
    paid decimal(8, 2) not null default '0', 
    due decimal(8, 
    2) as (total - paid) stored, 
    note longtext null, 
    order_type tin yint not null, 
    delivery_type tinyint not null, 
    status smallint not null default '0', 
    payment_method tinyint not null, 
    payment_channel smallint null, 
    payment_status tinyint not null default '0', 
    created_at timestamp null, 
    updated_at timestamp null
) default character set utf8mb4 collate 'utf8mb4_unicode_ci'
)

Note: i have mysql Ver 15.1 Distrib 10.1.35-MariaDB, for Win32

what i missed ?

Your error message says you are using MariaDB, not MySQL. What version of MariaDB are you using, as generated columns may not always have been supported? — Tim Biegeleisen, Dec 13 '18 at 05:30
Can you execute the corresponding create table statement directly against MariaDB? If you can't, then at least you'll know that it is not a PHP problem. — Tim Biegeleisen, Dec 13 '18 at 05:31
i have mysql Ver 15.1 Distrib 10.1.35-MariaDB, for Win32 (AMD64) @TimBiegeleisen — Emtiaz Zahid, Dec 13 '18 at 05:40
The `STORED` keyword requires MariaDB 10.2.1+: https://mariadb.com/kb/en/library/generated-columns/ — Jonas Staudenmeir, Dec 13 '18 at 06:27
look like i should upgrade my MariaDB. thanks @JonasStaudenmeir — Emtiaz Zahid, Dec 13 '18 at 06:29

score 0 · Answer 1 · answered Dec 13 '18 at 06:47

0

[Solved]

It was my MariaDB version problem, not PHP

Upgraded MariaDB 10.1 to 10.3. now its working fine.

followed the solution from here

Thanks Jonas, Sachin, Tim

answered Dec 13 '18 at 06:47

Emtiaz Zahid

2,645
2
19
35

1

`STORED` was not available until MariaDB 10.2. – Rick James Dec 16 '18 at 04:48

Error in generated column (MySQL) in Laravel by storedAs()

1 Answers1