I can't figure out what's the problem in the sql query. I'm using the dbforge library in codeigniter 3.
Mysql Version: 8.0

<?php

class Migration_merchant_withdraw_request extends CI_Migration
{
    public function up()
    {
        $this->dbforge->add_field('id');
        $this->dbforge->add_field([
            '`mitra_id` VARCHAR NOT NULL',
            '`amount` INT NOT NULL',
            '`password` VARCHAR NOT NULL',
            '`status` INT DEFAULT 0',
            '`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP',
            '`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
        ]);
        $this->dbforge->create_table('merchant_withdraw_request');
    }

    public function down()
    {
        $this->dbforge->drop_table('merchant_withdraw_request');
    }
}

Which generates the sql query.

CREATE TABLE `merchant_withdraw_request` (
  `id` INT(9) NOT NULL AUTO_INCREMENT,
  `mitra_id` VARCHAR NOT NULL,
  `amount` INT NOT NULL,
  `password` VARCHAR NOT NULL,
  `status` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `pk_merchant_withdraw_request` PRIMARY KEY(`id`)
) DEFAULT CHARACTER SET = utf8 COLLATE = utf8_general_ci

Yet, I get an error message.

Error Number: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL, amount INT NOT NULL, password VARCHAR NOT NULL, status INT DE' at line 3

I tried this style of adding fields in dbforge.

<?php

class Migration_merchant_withdraw_request extends CI_Migration
{
    public function up()
    {
        $this->dbforge->add_field('id');
        $this->dbforge->add_field([
            'mitra_id' => [
                'type' => 'VARCHAR',
            ],
            'amount' => [
                'type' => 'INT',
            ],
            'password' => [
                'type' => 'VARCHAR',
            ],
            'status' => [
                'type' => 'INT',
                'default' => 0,
            ],
            'created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP',
            'updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
        ]);
        $this->dbforge->create_table('merchant_withdraw_request');
    }

    public function down()
    {
        $this->dbforge->drop_table('merchant_withdraw_request');
    }
}

Yet it produces the same error, what's the problem?

Problem Solved
It turns out that, I have not set the length of the VARCHAR data type. To do this in dbforge, this would be the syntax.

'column_name' => [
    'type' => 'VARCHAR',
    'constraint' => 64 // Set your wanted length.
]
0

There are 0 answers