How to change column data type in laravel 5.6?

Question

I am trying to change column data type using laravel 5.6.

I have a table in which two columns have a data type of text but I would like to change it to longtext. I have tried following:

executed composer require doctrine/dbal
executed composer dump-autoload

...and then created the migration 2019_12_23_065820_change_response_column_data_type_in_log_requests_table.php for log_requests table.

...and then the following script

public function up()
{
    Schema::table('log_requests', function (Blueprint $table) {
        $table->longText('request')->nullable()->change();
        $table->longText('response')->nullable()->change();
    });
}

But it is not changing the column's data type. Can someone guide me? Where am I wrong so that I can fix it? Thank you.

EDITED

After requesting for migration in comment, I added migration script:

public function up()
{
    Schema::create('log_requests', function (Blueprint $table) {
        $table->increments('id');
        $table->bigInteger('user_id')->nullable()->unsigned();
        $table->string('api_name')->nullable();
        $table->string('url')->nullable();
        $table->string('method')->nullable();
        $table->string('ip_address')->nullable();
        $table->string('status_code')->nullable();
        $table->string('duration')->nullable();
        $table->text('request')->nullable();
        $table->text('response')->nullable();
        $table->timestamps();
    });
}

@YasinPatel no there is no error, but it does not changing the column type — Imran Abbas, Dec 23 '19 at 07:59
@ImranAbbas how do u know it isn't changed, did u describe the table in mysql? — TsaiKoga, Dec 23 '19 at 08:00
@TsaiKoga I checked the through `phpmyadmin` the column data type is still `text` — Imran Abbas, Dec 23 '19 at 08:02
@TsaiKoga off course, yes 100% sure I did executed and test many times as well after roll backing — Imran Abbas, Dec 23 '19 at 08:04
I'm sure this will solve your problem. https://stackoverflow.com/questions/33140860/laravel-5-1-unknown-database-type-enum-requested/42107554#42107554 — Satendra Rawat, Dec 23 '19 at 10:23
no idea. This code should work. You will need some more extensive debugging like dumping the SQL log (described in https://stackoverflow.com/questions/27753868/how-to-get-the-query-executed-in-laravel-5-dbgetquerylog-returning-empty-ar) — apokryfos, Dec 24 '19 at 14:48
@apokryfos Thank you, I am still trying and looking for solution — Imran Abbas, Dec 25 '19 at 12:47
Can you also share the migration that creates `log_requests`? — apokryfos, Dec 25 '19 at 20:21
I've tried making a fresh laravel project with the default migrations plus the "create" table migration. Then I added the altering migration. Everything seems to have worked fine for me. I don't know what might be but it's probably something very case specific. If @N69S answer worked for you I'd go for that one and call it a day — apokryfos, Dec 26 '19 at 08:02

Immeyti · Answer 1 · 2019-12-23T08:24:58.613

4

Just change the column comment, for example:

$table->mediumText('myColumn')->comment(' ')->change(); // up
$table->text('myColumn')->comment('')->change(); // down

edited Dec 23 '19 at 08:24

answered Dec 23 '19 at 08:16

Immeyti

545
3
15

I want to convert it into longText not medium – Imran Abbas Dec 23 '19 at 12:33
Okey, replace your type with mediumText. just add comment – Immeyti Dec 25 '19 at 13:59
I encounter with same issue and test this code and it works! This answer should be accepted. – mhrabiee Dec 28 '19 at 14:11
@ImranAbbas Do you test this solution? – Immeyti Dec 28 '19 at 14:13
@ImranAbbas just change it to longText then, have u try that? The key of this answer is to change the comment section to be a space – Trisno Raynaldy Dec 30 '19 at 02:30

Fahd Yousri · Answer 2 · 2019-12-25T14:28:58.193

2

This is an issue with how doctorine treats texts, it doesn't differentiate long medium or normal text however it's doable by changing the size attribute.

$table->string('request', 4294967295)->change();

as per @andfelzapata in the issue https://github.com/laravel/framework/issues/9636

edited Dec 25 '19 at 14:28

answered Dec 25 '19 at 14:06

Fahd Yousri

401
3
12

score 1 · Answer 3 · answered Dec 23 '19 at 08:23

1

You can avoid using Dbal if you go for the direct query

public function up()
{
    \DB::statement('alter table log_requests modify request longtext null;');
    \DB::statement('alter table log_requests modify response longtext null;');
}

public function down()
{
    \DB::statement('alter table log_requests modify request text null;');
    \DB::statement('alter table log_requests modify response text null;');
}

answered Dec 23 '19 at 08:23

N69S

16,110
3
22
36

What's wrong if I am using `$table->longText('request')`. It is laravel docs – Imran Abbas Dec 23 '19 at 12:33
@ImranAbbas Nothing is wrong with it, when you create the table. but when you want to alter it, it gets more complicated. – N69S Dec 23 '19 at 15:41
I tried, it is returning following error `SQLSTATE[22001]: String data, right tru ncated: 1406 Data too long for column 'value' at row 1 (SQL: ALTER TABLE system_ logs CHANGE value value VARCHAR(190) CHARACTER SET utf8mb4 NOT NULL COLLATE `utf 8mb4_unicode_ci`) ` – Imran Abbas Dec 26 '19 at 11:23
i dont know where you're getting that error or how that query is related to my answer. `ALTER TABLE system_ logs` & `varchar(190)`, my answer was about `log_requests` & `longtext` ... @ImranAbbas . in any case, when altering column type, it is usual to adapt the values to the new format before or simultaneously. – N69S Dec 27 '19 at 10:59
1

Why you are using plain SQL syntax here? I think this should be done via ORM – mhrabiee Dec 28 '19 at 14:13
@mhrabiee to avoid having to load doctrine/dbal to alter tables in migration. – N69S Dec 29 '19 at 02:27

score 0 · Answer 4 · answered Jan 01 '20 at 06:43

you can do this with the help of this function. You can create a new migration and change column type

public function up()
{
Schema::table('log_requests', function (Blueprint $table) {
    $table->longText('request')->change();
    $table->longText('response')->change();
});

You need to install doctrine/dbal to make this work

composer require doctrine/dbal

How to change column data type in laravel 5.6?

4 Answers4

Linked