Why am I getting a MySQL Syntax Error 1064 when using "SOURCE [filename]" in Laravel, but-CodePudding

I'm using Laravel with Homestead and MySQL. I have a database dump file that I want to restore on command. When I run this from the command line I don't get any errors:

mysql -h localhost -u homestead -psecret homestead < /home/vagrant/Data/local-db-reset.sql

My local-db-reset.sql file reads:

DROP DATABASE IF EXISTS homestead;

CREATE DATABASE homestead;
USE homestead;

SOURCE /home/vagrant/Data/local-db-snapshot.sql;

However, when I run the same commands in PHP from within a custom artisan command, I get a syntax error 1064 from MySQL:

$db = 'homestead';
$snapshot = '/home/vagrant/Data/local-db-snapshot.sql;';

DB::transaction(function() use ($db, $snapshot) {
    DB::statement('DROP DATABASE IF EXISTS ' . $db . ';');
    DB::statement('CREATE DATABASE ' . $db . ';');
    DB::statement('USE ' . $db . ';');
    DB::statement('SOURCE ' . $snapshot . ' ;');
});

The exact error reads:

Illuminate\Database\QueryException  : 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 'SOURCE /home/vagrant/Data/local-db-snapshot.sql' at line 1 (SQL: /home/vagrant/Data/local-db-snapshot.sql ;)

The 1064 error is triggered by the SOURCE command, even though it evaluates to the same exact command as in the sql script above. Why? How can I address this? I need to be able to do this from an artisan command, or at least from php somewhere.

Please note, I'm not in a situation where artisan migrate:fresh is an option, so please forget that approach. I really need to restore this exact snapshot.

CodePudding user response：

This is likely because your webserver is using a different user to that which you use in your console.

I don't use Homestead myself, but with my setup, Apache runs under the user www-data.

You can check this by temporarily changing the permissions on your .sql file:

chmod 777 /home/vagrant/Data/local-db-snapshot.sql

If this resolves the issue, you need to work out the user your webserver is using. You should be able to do this by running sudo ps aux | grep nginx.

This should output something like this (NB: I substituted nginx for apache to suit my setup):

joundill@machine:~$ sudo ps aux | grep apache
root        1099  0.0  1.0 243836 40600 ?        Ss   00:46   0:00 /usr/sbin/apache2 -k start
www-data    3395  0.0  2.4 253292 96744 ?        S    01:36   0:01 /usr/sbin/apache2 -k start
joundill    4850  0.0  0.0   6436   672 pts/0    S    02:11   0:00 grep --color=auto apache
joundill@machine:~$

As you can see, the user www-data is running apache (in your case nginx). You'll need to grant this user access to your .sql file.

CodePudding user response：

A colleague of mine pointed out that SOURCE is not a SQL command, it's a meta-command to the mysql command-line client only. So I (essentially) refactored my code to use shell_exec() with the original mysql command-line command at the very top above, and things are working as they should be now.