Laravel Migration: Error in Json column on Hosting Server but not on Local Xampp Server

Question

I recently downloaded and installed a package for the wallet in which I got migrations published & these migrations have JSON column in some tables.

Migration:

public function up(): void
{
    Schema::create($this->table(), function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->morphs('payable');
        $table->enum('type', ['deposit', 'withdraw'])->index();
        $table->decimal('amount', 64, 0);
        $table->boolean('confirmed');
        $this->json($table, 'meta')->nullable();
        $table->uuid('uuid')->unique();
        $table->timestamps();

        $table->index(['payable_type', 'payable_id', 'type'], 'payable_type_ind');
        $table->index(['payable_type', 'payable_id', 'confirmed'], 'payable_confirmed_ind');
        $table->index(['payable_type', 'payable_id', 'type', 'confirmed'], 'payable_type_confirmed_ind');
    });
}

public function json(Blueprint $table, string $column): ColumnDefinition
{
    $conn = DB::connection();
    if ($conn instanceof MySqlConnection || $conn instanceof PostgresConnection) {
        $pdo = $conn->getPdo();
        try {
            $sql = 'SELECT JSON_EXTRACT(\'[10, 20, [30, 40]]\', \'$[1]\');';
            $prepare = $pdo->prepare($sql);
            $prepare->fetch();
        } catch (\Throwable $throwable) {
            return $table->text($column);
        }
    }

    return $table->json($column);
}

protected function table(): string
{
    return (new Transaction())->getTable();
}

public function down(): void
{
    Schema::drop($this->table());
}

This migration works fine in the local xampp server but I don't understand why am I getting an error in my live server. Below is the error which I get when I am trying to run the migration.

I tried to use $table->json('meta')->nullable(); this didn't work either though I think it was not a appropriate thing to do because as far as I can see $this->json() calls json function.

Some also suggested using Text instead of Json but that's not a solution I feel. How can we get this resolved?

Have you checked whether your installed MySQL server version supports the JSON data type? — matticustard, May 25 '21 at 08:19
@matticustard oh yes I think that's the issue. I just checked it doesn't support Json data type. Thanks — Hardik Sisodia, May 25 '21 at 09:33

score 2 · Accepted Answer · answered May 25 '21 at 08:36

2

Please check your MySQL version if it is 5.6 or lower than the function json will not work. You should use MySQL version 5.7 or higher to use json function. Or else use "text" as the column in MySQL version 5.6 or lower.

answered May 25 '21 at 08:36

Muzaffar Shaikh

630
4
14

1

Yes, you are right. I think that's the issue. My current SQL version does not support JSON data type. Thanks a lot, it helped. – Hardik Sisodia May 25 '21 at 09:35
Great. Could you please upvote it as might help others as well. – Muzaffar Shaikh May 25 '21 at 12:18

Laravel Migration: Error in Json column on Hosting Server but not on Local Xampp Server

1 Answers1