Loading a SQL backup without altering your configuration

MySQL has a number of modes you can set to control various aspects of its behavior.

For example, there is a mode called NO_ZERO_DATE which prevents setting a date like 0000-00-00. In the past, MySQL allowed this, but over time newer versions have become strict and now prevent it.

So what should you do if you're moving to a new database which has the more strict defaults applied, and you need to migrate data that contains invalid data, like zero dates?

You could override the strict default on the new server, and carry forward the old behavior, but now you're just carrying forward more technical debt to be cleaned up later.

Ideally, you'd clean up the data first prior to migration, but that might not be practical due to your timeline or the amount of data to be corrected.

My recommendation is a pragmatic approach somewhere between these two options. You can change the sql_mode just for one session, without permanently altering the server's configuration.

First, you need to look up what sql_mode the target server is using:

SELECT @sql_mode;

Let's say that produces the following list of modes: ONLY_FULL_GROUP_BY,ANSI,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION.

Now, inside the .sql dump file you're using to do the migration, you can insert a line at the very top to change the sql_mode just for the duration of the import.

Set the new mode to be the same as the old mode, but without the two flags related to "zero dates":

SET SESSION sql_mode='ONLY_FULL_GROUP_BY,ANSI,NO_ENGINE_SUBSTITUTION';

Of course, consider this a temporary measure to help you complete the migration. I'd still recommend going back and taking the time to also clean up the data post-migration.

Here to help,

Joel

P.S. Laravel is pretty secure by default, but Aaron published a course to go even deeper on Laravel security.