Why running Laravel / Doctrine raw database queries is not the same as using mysql cli?

Question

I have encountered a situation when doing a database migration that contradicted what I thought I knew about php connections to a database. I wanted to change the collation of a column, so I wrote the following statement:

ALTER TABLE posts MODIFY COLUMN text_html LONGTEXT COLLATE utf8_unicode_ci

An this works correctly using mysql cli. Now, in order to have this change tracked in code, I created a Laravel migration, with the following statement:

DB::unprepared('ALTER TABLE posts MODIFY COLUMN text_html LONGTEXT COLLATE utf8_unicode_ci');

and I got the following error:

SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'published_at' (SQL: ALTER TABLE posts MODIFY COLUMN text_html LONGTEXT COLLATE utf8_unicode_ci)

I thought it may have something to do with Laravel, so I tried doing a more "raw" query:

DB::connection() ->getDoctrineConnection() ->exec('ALTER TABLE posts MODIFY COLUMN text_html LONGTEXT COLLATE utf8_unicode_ci');

Which gave me a similar error:

An exception occurred while executing 'ALTER TABLE posts MODIFY COLUMN text_html LONGTEXT COLLATE utf8_unicode_ci': SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'published_at'

So there are actually two problems here. The first one, obviously, why am I getting an error about the published_at column if I'm trying to modify the text_html column. But the second one, the one I'm more interested in, is why it works on mysql cli and it doesn't with php. If running raw queries in php is not the same as running queries with mysql cli, this tells me php is doing something more than just running the query. What is it doing?

@Indra it was definitely that, I still don't understand why the error is for a different column but I guess that's a mysql thing. So the anwser to my question would be something along the lines of "laravel adds custom configuration that is not included by default in mysql cli and that can be found in config/database.php". If you want answer something like that and I'll mark it as the accepted answer. — Noel De Martin, Mar 22 '18 at 14:47

Indra · Accepted Answer · 2018-03-22T14:54:51.030

0

Edit:

Laravel has a custom configuration that is not included by default in mysql cli. That can be found in config/database.php

I personally do this to bypass that. This is what works for me since I want to keep strict mode on. If you don't care about that please use the first paragraph solution.

public function up()
    {

        Schema::table('posts', function ($table) { // you can drop the $table param. Also Schema::table might be redundant so try without first
            DB::statement('ALTER TABLE posts MODIFY text_html LONGTEXT COLLATE utf8_unicode_ci');
        });
    }

You don't need collect if your Db is already utf8_unicode_ci

edited Mar 22 '18 at 14:54

answered Mar 22 '18 at 11:35

Indra

692
6
18

I'll mark this as accepted because changing the config works, but your code example does not change any configuration so that is still executing with strict mode. – Noel De Martin Mar 22 '18 at 14:51
@Noel De Martin agreed, but some people might want to keep strict mode on so that's why I added that. I will update my answer to reflect that clearly – Indra Mar 22 '18 at 14:53
Yeah I want to keep strict mode on overall, so what I ended up doing is using this line so that strict mode is only disabled in this migration: `DB::getPdo()->prepare("set session sql_mode='NO_ENGINE_SUBSTITUTION'")->execute();` (I did this but I don't recommend copy-pasting for anyone who doesn't understand what this is) – Noel De Martin Mar 23 '18 at 08:43

Why running Laravel / Doctrine raw database queries is not the same as using mysql cli?

1 Answers1