Laravel-medialibrary: SQL Error when getMedia()

[rluders]

Hello. I'm having this SQL error when I try to get the media from my model.

>>> $user = App\Models\User::find(301);
=> App\Models\User {#834
     id: "301",
     username: "username",
     email: "[email protected]",
     first_name: "Jon",
     last_name: "Snow",
     birthday: null,
     phone: null,
     phone_mobile: null,
     country: null,
     state: null,
     city: null,
     gender: "MA",
     biography: null,
     nationality: null,
     occupation: null,
     relationship: null,
     education: null,
     created_at: "2017-09-22 22:58:58",
     updated_at: "2017-09-22 22:58:58",
     verified: "0",
     verification_token: null,
   }`

`
>>> $user->getMedia()
Illuminate\Database\QueryException with message 'SQLSTATE[HY000]: General error: 2036  (SQL: select * from `media` where `media`.`model_id` = 301 and `media`.`model_id` is not null and `media`.`model_type` = App\Models\User)'
>>> 

[freekmurze]

This package needs JSON column support introduced in MySQL 5.7. Perhaps your version of MySQL is too low?

[rluders]

@freekmurze my mySQL version is:

mysql Ver 14.14 Distrib 5.7.19, for Linux (x86_64) using EditLine wrapper

Anyway, I'll try to update it to the last version or maybe migrate to MariaDB and see what happens. Any news I'll post here again.

Thank you for you help.

[AlexVanderbist]

@rluders Just a heads-up: last time I checked MariaDB didn't fully support the JSON type yet... You might want to look into that first.

[rluders]

@AlexVanderbist Thanks.

OK. I'm sure that what its running is the last version of mySQL 5.7.
Also I recreate all the migrations (migration:fresh), just to make sure.

mysql> describe media;
+-------------------+------------------+------+-----+---------+----------------+
| Field             | Type             | Null | Key | Default | Extra          |
+-------------------+------------------+------+-----+---------+----------------+
| id                | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| model_id          | int(10) unsigned | NO   | MUL | NULL    |                |
| model_type        | varchar(255)     | NO   |     | NULL    |                |
| collection_name   | varchar(255)     | NO   |     | NULL    |                |
| name              | varchar(255)     | NO   |     | NULL    |                |
| file_name         | varchar(255)     | NO   |     | NULL    |                |
| mime_type         | varchar(255)     | YES  |     | NULL    |                |
| disk              | varchar(255)     | NO   |     | NULL    |                |
| size              | int(10) unsigned | NO   |     | NULL    |                |
| manipulations     | json             | NO   |     | NULL    |                |
| custom_properties | json             | NO   |     | NULL    |                |
| order_column      | int(10) unsigned | YES  |     | NULL    |                |
| created_at        | timestamp        | YES  |     | NULL    |                |
| updated_at        | timestamp        | YES  |     | NULL    |                |
| user_id           | int(10) unsigned | YES  | MUL | NULL    |                |
+-------------------+------------------+------+-----+---------+----------------+
15 rows in set (0.00 sec)

Yeah, I just add an extra column at the table, but if I remove it I still getting the same error.
And then I just try to run the same SQL code that the error throws.

mysql> select * from `media` where `media`.`model_id` = 301 and `media`.`model_id` is not null and `media`.`model_type` = App\Models\User;
ERROR: 
Unknown command '\M'.
ERROR: 
Unknown command '\U'.
ERROR 1064 (42000): 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 '\Models\User' at line 1

But, If I fix the SQL Query:

mysql> select * from `media` where `media`.`model_id` = 301 and `media`.`model_id` is not null and `media`.`model_type` = "App\\Models\\User";
+----+----------+-----------------+-----------------+--------------------+--------------------+------------+-------+-------+---------------+-------------------+--------------+---------------------+---------------------+---------+
| id | model_id | model_type      | collection_name | name               | file_name          | mime_type  | disk  | size  | manipulations | custom_properties | order_column | created_at          | updated_at          | user_id |
+----+----------+-----------------+-----------------+--------------------+--------------------+------------+-------+-------+---------------+-------------------+--------------+---------------------+---------------------+---------+
|  1 |      301 | App\Models\User | avatars         | medialibraryEDODph | medialibraryEDODph | image/jpeg | media | 34565 | []            | []                |            1 | 2017-09-26 00:55:51 | 2017-09-26 00:55:51 |    NULL |
+----+----------+-----------------+-----------------+--------------------+--------------------+------------+-------+-------+---------------+-------------------+--------------+---------------------+---------------------+---------+
1 row in set (0.00 sec)

[AlexVanderbist]

That's really strange. I have a feeling this is not necessarily related to medialibrary... Escaping strings in relationship queries is really up to Laravel itself.

Just to be sure, can you post the value for media_model in your medialibrary.php config please?

[rluders]

Here is my media_model config.

    /*
     * The class name of the media model to be used.
     */
    'media_model' => App\Models\Media::class,

Just to be clear. If I use the default config, the error persists, and of course that the SQL query changes.

@AlexVanderbist so I was thinking the same. The problem is something with the Laravel Eloquent, so last night I debug all down to the eloquent classes, but was unable to fix the problem.

Just to be clear about my environment at this moment.

• Laravel 5.5.13
• Medialibrary 6.1.2
• PHP 7.1.9
• mySQL 5.7.19

[rluders]

Hey!
Forget about everything. I just found out why it's happening. Oh, and it is so stupid. ~shame on me~

For some reason, the MySQL docker image came with the charset configured by default as latin1. I just change the charset to the correct one and everything works fine.

Dude, I lost at least 3 hours with this problem. Just 'cause this dammit mysql image came with latin1 instead utf8...

Thank you guys, for your support. Oh, and I'll make a pull request later to give an extra support to the library.

[guicara]

Hello,

Could you update the requirements in the README.md file to specify that MySQL >= 5.7 or MariaDB >= 10.2 is required? Not everyone has these versions.

It is possible to change $table->json('...'); for something else?

[AlexVanderbist]

@guicara This is actually documented in the docs at https://docs.spatie.be/laravel-medialibrary/v6/requirements

It's also not possible to change the migrations to use anything else but the JSON columns as the packages relies on the column->"$.jsonProperty" SQL syntax.

[guicara]

@AlexVanderbist Thank you for your fast reply.

So if I change the schema for $table->longText() the MediaLibrary will not work anymore? (just to be 100% sure because if yes, I will need to ask my host provider to upgrade the version of MySQL).

[AlexVanderbist]

Hey, you might actually be in luck. I had a quick look through the package and I couldn't find anything that might end up being a problem. Only array casting
(https://laravel.com/docs/5.5/eloquent-mutators#array-and-json-casting) seems to be used on the json columns and that'll work just fine with a TEXT column.

Can you let me know if this ends up working? Thanks

[guicara]

@AlexVanderbist I will let you know this week if this ends up working. Thanks ;)

[alederzz]

@guicara Hi, did you try if it works by making the change in the migration?

[guicara]

@alederzz Yes it works well.

[mgkimsal]

@rluders - having this same problem, with utf8 database charset already. Why would the charset make a difference on whether a column value was quoted or not?

[rluders]

@mgkimsal I have no idea, but after I change it for ut8 the problem was solved. :joy_cat:

[nenads]

are we shure about this ?

select * from media where media.model_id = '1' and media.model_id is not null and media.model_type = 'App\Models\Base\Manufacturer'

shoudn't App\Models\Base\Manufacturer be escaped ?

[nenads]

$manufacturer->getMedia();
no result
$manufacturer->media()->get();
result
mysql column setings
utf8mb4
utf8mb4_unicode_ci

[z-h-z-h]

same issue on mysql 5.7.19
try to execute getMedia() and have no result with query

select * from media where media.model_id = '18' and media.model_id is not null and media.model_type = 'App\ModelsSet'

but everything ok if i added \ character and execute query by myself

select * from media where media.model_id = '18' and media.model_id is not null and media.model_type = 'App\\Models\Set'

why laravel don't auto escape \ ?
how to fix this?

[tmikeska]

same issue on mysql 5.7.19
try to execute getMedia() and have no result with query

select * from media where media.model_id = '18' and media.model_id is not null and media.model_type = 'App\ModelsSet'

but everything ok if i added \ character and execute query by myself

select * from media where media.model_id = '18' and media.model_id is not null and media.model_type = 'App\Models\Set'

why laravel don't auto escape \ ?
how to fix this?

Try to use real collection name (media.collection_name) in getMedia('collection_name')