Upgrade from 8 to 9 failed

[maha-oueghlani]

Hello,

I upgraded my laravel app from 8 to9, now I must to upgrade spatie/laravel-medialibrary also, so in order to add a migration necessary, I run this :

public function up() {
        if ( ! Schema::hasColumn( 'media', 'generated_conversions' ) ) {
            Schema::table( 'media', function ( Blueprint $table ) {
                $table->json('custom_properties')->change();
                $table->json( 'generated_conversions')->nullable()->collation('utf8_unicode_ci');
            } );
        }
        
        Media::query()
            ->where(function ($query) {
                $query->whereNull('generated_conversions')
                    ->orWhere('generated_conversions', '')
                    ->orWhereRaw("JSON_TYPE(generated_conversions) = 'NULL'");
            })
            ->whereRaw("JSON_LENGTH(custom_properties) > 0")
            ->update([
                'generated_conversions' => DB::raw("JSON_EXTRACT(custom_properties, '$.generated_conversions')"),
                // OPTIONAL: Remove the generated conversions from the custom_properties field as well:
                // 'custom_properties'     => DB::raw("JSON_REMOVE(custom_properties, '$.generated_conversions')")
            ]);
    }

But It was failed with this error :
SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8_general_ci,COERCIBLE) and (utf8_unicode_ci,COERCIBLE) for operation '=' (SQL: update `media` set `generated_conversions` = JSON_EXTRACT(custom_properties, '$.generated_conversions'), `media`.`updated_at` = 2023-06-01 14:13:24 where (`generated_conversions` is null or `generated_conversions` = or JSON_TYPE(generated_conversions) = 'NULL') and JSON_LENGTH(custom_properties) > 0)

the collation of my DB is utf8_unicode_ci that's why I tried to change the collation of generated_conversions column and also I changed custom_properties column to json because it was text , but it's not working.

[maha-oueghlani]

can anyone help me please !!!

[gtjamesa]

I had the same problem. It can be fixed by changing line 21 to include the collation:

->orWhereRaw("JSON_TYPE(generated_conversions) = 'NULL' COLLATE utf8_unicode_ci");