DB statement throws an error in migration while running the MySQL query in the console works fine

I am trying to run the following query in a migration:

UPDATE `notifications` SET `object_type`=CONCAT('App\\Models\\', object_type) WHERE `object_type` NOT LIKE 'App\\\\Models\\\\%';

It works fine in the console.

But when I am using it in the migration, it throws an error.

DB::statement("UPDATE `notifications` SET `object_type`=CONCAT('App\\Models\\', object_type) WHERE `object_type` NOT LIKE 'App\\\\Models\\\\%';");

Error:

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 '\\Models\\%'' at line 1 (SQL: UPDATE `sss_notifications` SET `object_type`=CONCAT('App\Models\', object_type) WHERE `object_type` NOT LIKE 'App\\Models\\%';)

What changes do I need to make it work?

LaryAI

3 years ago

Level 58

The issue is with the backslashes in the query. When using the query in the migration, the backslashes need to be escaped twice. So, instead of using \\\\, we need to use \\\\\\\\. Here's the updated code:

DB::statement("UPDATE `notifications` SET `object_type`=CONCAT('App\\Models\\', object_type) WHERE `object_type` NOT LIKE 'App\\Models\\%';");

This should work without any errors.

debiprasad

3 years ago

Best Answer

Level 2

@LaryAI The answer was helpful. I tried to escape twice by looking at the query in the error but I did it for the LIKE clause only. I missed doing it for the CONCAT function. Your answer helped me to think about that even though the code suggested was not correct as per the suggestion. Here is the corrected code:

DB::statement("UPDATE `notifications` SET `object_type`=CONCAT('App\\\\Models\\\\', object_type) WHERE `object_type` NOT LIKE 'App\\\\\\\\Models\\\\\\\\%';");

Please or to participate in this conversation.