How to migration change columns type?

Question

I got an error, When I try to column type from string to text, With migration function of Laravel.

File : {data_time}_change_db_structure.php

public function up()
  {
    Schema::table('service_request_type', function (Blueprint $table) {
      $table->dropIndex(['sro_key_group']);

      $table->text('sro_key_group')->change();
      $table->renameColumn('sro_key_group', 'tags');
    });
  }

This is orginal migration create table file.

public function up()
  {
    Schema::create('service_request_type', function (Blueprint $table) {
      $table->engine = 'InnoDB';
      ...
      $table->string('sro_key_group', 100)->nullable()->index();
      ...
    });
  }

Error I got.

[Illuminate\Database\QueryException]
SQLSTATE[42000]: Syntax error or access violation: 1170 BLOB/TEXT column 'sro_key_group' used in key specification without a key length (SQL: ALTER TABLE service_request_type CHANGE sro_key_group sro _key_group TEXT DEFAULT NULL COLLATE utf8_unicode_ci)

                                                                                                                                          [Doctrine\DBAL\Driver\PDOException]                                   

SQLSTATE[42000]: Syntax error or access violation: 1170 BLOB/TEXT column 'sro_key_group' used in key specification without a key length

                                                                                                                                          [PDOException]                                                        

SQLSTATE[42000]: Syntax error or access violation: 1170 BLOB/TEXT column 'sro_key_group' used in key specification without a key length

What wrong? And I already install doctrine/dbal in my composer.json.

Answer 1

You'll need to do this in three steps, either using three separate migrations, or three calls to table() as you've shown in your answer.

The first issue is that, even though you've written your statements in the order you'd like them to execute (and the order they need to execute), the schema builder will actually rearrange the order so that "change" statements are executed first. The schema builder treats new columns and changed columns as "implied" statements, and moves them to the top of the stack of commands to run. However, rename statements are not considered "change" statements.

So, even though you've written the code to:

[
    remove index,
    change column from varchar to text,
    rename column,
]

The schema builder will actually execute:

[
    change column from varchar to text,
    remove index,
    rename column,
]

Now, since the change command is happening before the column is removed from the index, you are getting the 1170 error.

The next issue is with attempting to do the column change and the column rename in the same context. The SQL to implement the requests changes is generated by doing schema diffs, however both schema diffs will be done before any changes are actually made. So, the first change from varchar to text will generate the appropriate SQL to make that change, but then the second change to rename the column will actually generate SQL that changes the column back to a text field while renaming it.

To work around these issues, you can either create three migrations, where the first migration simply drops the index, the second migration changes the type, and then the third migration renames it, or you can keep your one migration and run three table() statements.

public function up()
{
    // make sure the index is dropped first
    Schema::table('service_request_type', function (Blueprint $table) {
        $table->dropIndex(['sro_key_group']);
    });

    // now change the type of the field
    Schema::table('service_request_type', function (Blueprint $table) {
        $table->text('sro_key_group')->nullable()->change();
    });

    // now rename the field
    Schema::table('service_request_type', function (Blueprint $table) {
        $table->renameColumn('sro_key_group', 'tags');
    });
}