15

I am trying to alter the column datatype using Laravel migration. But I am facing following error. Please help me out.

Schema::table('files', function(Blueprint $table) {
    $table->integer('app_id')->change();
    $table->index(['app_id', 'filename']);
});

SQLSTATE[42000]: Syntax error or access violation: 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 'CHARACTER SET utf8 DEFAULT 0 NOT NULL COLLATE utf8_unicode_ci' at line 1 (SQL: ALTER TABLE files CHANGE app_id app_id INT CHARACTER SET utf8 DEFAULT 0 NOT NULL COLLATE utf8_unicode_ci)

julianstark999
  • 3,450
  • 1
  • 27
  • 41
Nagarajan Malaichamy
  • 183
  • 1
  • 2
  • 6
  • Does this answer your question? [Laravel migrations change a column type from varchar to longText](https://stackoverflow.com/questions/37724150/laravel-migrations-change-a-column-type-from-varchar-to-longtext) – nice_dev Nov 21 '19 at 14:07
  • Not working for the following $table->integer('app_id')->change(); When I convert string to integer CHARACTER SET utf8 added automatically. How to remove this? – Nagarajan Malaichamy Nov 21 '19 at 14:12
  • Do you have `doctrine/dbal` package in your laravel project? – nice_dev Nov 21 '19 at 14:14
  • Yes I have doctrine/dbal. – Nagarajan Malaichamy Nov 21 '19 at 14:15
  • I don't think encoding is the problem here. Also, why 2 times `app_id` in `ALTER TABLE files CHANGE app_id app_id INT CHARACTER SET utf8 DEFAULT 0 NOT NULL COLLATE utf8_unicode_ci`? – nice_dev Nov 21 '19 at 14:17
  • That's not a problem. The script has generated like that only. I just copy pasted the error. If I run as ALTER TABLE files CHANGE app_id app_id INT DEFAULT 0 NOT NULL COLLATE `utf8_unicode_ci` it is working in command line terminal. Note: If I removed the CHARACTER SET utf8 then it was worked. – Nagarajan Malaichamy Nov 21 '19 at 14:21
  • Ok. Does this thread help you? https://stackoverflow.com/questions/14585182/laravel-3-schema-table-column-collation – nice_dev Nov 21 '19 at 14:53
  • Or this https://laracasts.com/discuss/channels/general-discussion/defining-column-collation-in-migrations?page=1 – nice_dev Nov 21 '19 at 14:54
  • Finally I used DB::statement('ALTER TABLE files CHANGE app_id app_id INT DEFAULT 0 COLLATE `utf8_unicode_ci`); But no luck with $table->integer('app_id')->change(); – Nagarajan Malaichamy Nov 22 '19 at 12:43
  • I will see if I can reproduce this issue at my home. Also, is this with a fresh laravel installation or did you change anything in `config/database.php`? – nice_dev Nov 22 '19 at 12:54
  • 1
    I didn't change anything. It is a fresh installation – Nagarajan Malaichamy Nov 25 '19 at 07:56

2 Answers2

13

As a alternative solution to https://github.com/doctrine/dbal/issues/3714 that is downgrading the doctrine/dbal package u can do: 

Schema::table('member_section', function (Blueprint $table) {
    $table->bigInteger('type')->charset(null)->collation(null)->change();
});
Digital87
  • 163
  • 1
  • 5
11

Your issue is most likely caused by a bug in the most recent version of the doctrine/dbal package. The issue was introduced with v2.10.0.

You can always downgrade the package in your composer.json to v2.9.3 it should work just fine.

See the offical issue here: https://github.com/doctrine/dbal/issues/3714

Nicolas Buch
  • 421
  • 1
  • 4
  • 7
  • Wow, I wasted hours on this before I finally found your post. Laravel and Doctrine each have been blaming each other. It's still not resolved. I'll just ignore for now. https://github.com/laravel/framework/issues/30539#issuecomment-559605145 – Ryan Dec 12 '19 at 20:50