framework: Migrating a varchar to a bigint causes invalid charset instructions to appear

Laravel Version: 6.5
PHP Version: 7.3.11
Database Driver & Version: MySQL - 5.7.27-0ubuntu0.18.04.1

Description:

While writing a migration to fix a bad database that had stored a foreign key as a varchar, I wrote the following migration:

Schema::table('jobseeker_qualifications', function (Blueprint $table) {
        $table->bigIncrements('id')->change();
        $table->unsignedBigInteger('user_id')->change();
        $table->unsignedBigInteger('jobseekers_education_id')->change();
        $table->foreign('user_id')->references('id')->on('users');
        $table->foreign('jobseekers_education_id')->references('id')->on('jobseeker_educations');
});

However, this above migration generated the following SQL:

ALTER TABLE jobseeker_qualifications
CHANGE id id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
CHANGE jobseekers_education_id jobseekers_education_id BIGINT UNSIGNED CHARACTER SET utf8 NOT NULL COLLATE `utf8_unicode_ci`,
CHANGE user_id user_id BIGINT UNSIGNED NOT NULL

Work Around: Removing the instructions related to the character set and running this query manually was successful.

Steps To Reproduce:

Create a table containing a VARCHAR field that contains valid id values (only numbers).
Run a migration similar to the above, requesting a change the VARCHAR to unsigned BIGINT.

About this issue

Original URL
State: closed
Created 5 years ago
Reactions: 4
Comments: 22 (10 by maintainers)

Links to this issue

php - How to convert string column type to integer using Laravel migration? - Stack Overflow

Commits related to this issue

Fix issue caused by changes to DBAL https://github.com/laravel/framework/issues/30539#issuecomment-563486759 — committed to Biologer/Biologer by nikazooz 5 years ago
Fixing migration breaking due to bug in dbal/laravel. https://github.com/laravel/framework/issues/30539 — committed to railroadmedia/usora by deleted user 4 years ago
Fix migration to work around https://github.com/laravel/framework/issues/30539 — committed to SlovakNationalGallery/webumenia.sk by eronisko 4 years ago

Most upvoted comments

Hello everyone, I’m one of the core members of the Doctrine team 👋

As I mentioned on the issue in DBAL, I completely understand your frustration and am sorry for this inconvenience.

We really believe that the issue is related to how Laravel configures the DBAL objects to perform the comparisons and think that it was working previously due to a bug that got fixed.

I don’t know Laravel so much to solve this but feel free to ping me in a PR or on Doctrine’s slack channel and I’ll do my best to support the resolution of this issue.

@JohnyProkie thanks for pointing us here and @driesvints and other Laravel maintainers for their hard work on building bridges between the two projects 🤟

+21

lcobucci on Nov 28, 2019

Hi all, if anyone on Laravel 6.x & doctrine/dbal 2.10 experiencing this issue, the quick fix is to set charset and collation to empty string on column change statement. For example:

$table->integer('resource_type')->default(0)->charset('')->collation('')->change();

It does work on my case where previous column type is string (varchar). Looks like someone has already sent PR to fix this, but I hope solution above still help before the PR merged.

+20

yahya09 on Dec 9, 2019

Fixed by https://github.com/laravel/framework/commit/fccdf7c42d5ceb50985b3e8243d7ba650de996d6

+14

taylorotwell on Apr 30, 2020

Can please folks from Laravel and folks from Doctrine make an agreement and push this issue a bit further? https://github.com/doctrine/dbal/issues/3714#issuecomment-559585932

Laravel says it’s issue on Doctrine’s side. Doctrine says it’s issue on Laravel side. Meanwhile we are locked on older version of Doctrine when we want to keep our projects up-to-date. Thank you in advance.

Please note this happened to us when changing varchar column to integer.

+11

JohnyProkie on Nov 28, 2019

Hi all, if anyone on Laravel 6.x & doctrine/dbal 2.10 experiencing this issue, the quick fix is to set charset and collation to empty string on column change statement. For example:

$table->integer('resource_type')->default(0)->charset('')->collation('')->change();

It does work on my case where previous column type is string (varchar). Looks like someone has already sent PR to fix this, but I hope solution above still help before the PR merged.

This unfortunately isn’t working for me for some reason. I’ve tried null and '' for charset and collation and I still get the same error. I’m going from string to bigInteger:

$table->bigInteger('number')
      ->charset('')
      ->collation('')
      ->change();

I finally ended up just running the raw query:

\DB::statement('alter table orders modify number bigint not null');

squatto on Feb 11, 2020

@lcobucci it does seem that only applies to json and binary columns while the issue here is changing a column type from varchar to bigint so I’m not sure if that’s the actual culprit?

https://github.com/laravel/framework/blob/1bbe5528568555d597582fdbec73e31f8a818dbc/src/Illuminate/Database/Schema/Grammars/ChangeColumn.php#L124-L128

driesvints on Dec 2, 2019

@driesvints I haven’t debugged anything but https://github.com/laravel/framework/blob/1bbe5528568555d597582fdbec73e31f8a818dbc/src/Illuminate/Database/Schema/Grammars/ChangeColumn.php#L125-L127 might give us a clue.

Laravel should make sure to not set the charset/collation info on the objects while performing the changes.

lcobucci on Nov 29, 2019

DBAL just closed the issue: see https://github.com/doctrine/dbal/issues/3714#issuecomment-558573089

Justin991q on Nov 26, 2019

Can you try downgrading to 2.9.3?

It looks like this is the same bug: https://github.com/doctrine/dbal/issues/3714

staudenmeir on Nov 11, 2019

Let’s figure this out. If anyone could help pinpoint where we can implement a fix that’d be great.

@lcobucci thanks for your hard work on Doctrine as well 😃

driesvints on Nov 29, 2019