Laravel mysql connection on remote host.

Well, does the user have permission to run a migration? That is an administrator task, not a user task. It would be bad practice to give the DB user for your app administrative privileges and its only a matter of time before you get hacked.

Of course the user has administrative privileges!

You say that the user has admin privileges, did you make sure that the access data like the port are set in your .env and you cleared the config cache with artisan after changing it?

Yes, the .env is properly configured and I have cleared the config cache!

Hmm, well, maybe try forcing it with php artisan migrate --env=production --force. Also, maybe try flushing the privileges table again.

In the database, you need to allow the database user to connect from whatever ip they are trying to connect from. It's not allowing remote connections. access denied for user @ 'IP'

It’s properly configured to do that. I even test allowing access from every IP. That was not the issue though!

I’m running my laravel app locally before uploading it to the server and just trying to connect the the prod database to check for any issues. The env is still locally and even used the —force flag.

Still nothing :/

The docs say you should show a app key for the DB. I have never deployed to Azure. Here is a quick screenshot of it. I am not sure it applies to your setup but is specific to laravel on Azure. https://docs.microsoft.com/en-us/azure/app-service/containers/tutorial-php-mysql-app

https://cl.ly/qKFD

I haven’t uploaded the app to azure yet. I’m testing it locally with the remote database

Ah, I thought you were setting up production. Ok, so you set the appropriate Azure MySQL user, password and port. And, you created the user and database on Azure to be used locally. Make sure the DB_HOST is correct.

Are you able to connect to the mysql server from your local webserver (so not your computer) with the following command:

mysql -u {username} -p{password} -h {remote server ip} {DB name}

So, I have azure where I will push for production. There I will have one app service for Laravel and one hosted MySQL database.

Now, I’m on my local machine and running on localhost and just trying to use the production database and it’s not working with my localhost.

@m-rk Yes, I’m able to connect but not through my app.

The Azure docs would indicate that you might need to allow the connection through the security section. But, if you can connect with workbench, can you perform operations on the DB from there?

Yes, I was able to create the database through the workbench, and even explicitly set the user priviliges for the specific database. But still nothing on the app

Hmm. When you connect with workbench. Do you use SSL or an SSH connection? Or only the IP, port, user, db and password?

Maybe the docs can help you out or point you in a direction https://dev.mysql.com/doc/refman/5.7/en/problems-connecting.html

There are some suggestions when this error happens:

If you cannot figure out why you get Access denied, remove from the user table all rows that have Host values containing wildcards (rows that contain '%' or '_' characters). A very common error is to insert a new row with Host='%' and User='some_user', thinking that this enables you to specify localhost to connect from the same machine. The reason that this does not work is that the default privileges include a row with Host='localhost' and User=''. Because that row has a Host value 'localhost' that is more specific than '%', it is used in preference to the new row when connecting from localhost! The correct procedure is to insert a second row with Host='localhost' and User='some_user', or to delete the row with Host='localhost' and User=''. After deleting the row, remember to issue a FLUSH PRIVILEGES statement to reload the grant tables.

Did you try flushing the privileges as also suggested in the quote here above?

Unfortunately it didn’t work.

Until you figure it out, I would suggest creating a config for the connection and using making a change in the boot process or middleware to access either of two configs depending on production or development. https://laracasts.com/discuss/channels/laravel/local-and-remote-database-connection?page=0

DB::connection('foo')->select(...);

http://laraveldaily.com/multiple-database-connections-in-the-same-laravel-project/

Thanks for your time. Hopefully I will figure it out sooner or later.

@Dragotic I recently came across this package that my help you connect easily to remote DB without much latency.

@ejdelmonico Can you tell me which package that is?

@dragotic Damn, not enough coffee. Sorry about that.

https://packagist.org/packages/stechstudio/laravel-ssh-tunnel

@dragotic How did you resolve it please?

Hi @dragotic did you manage to solve it? I'm having a similar issue on an amazon RDS database. BTW I am using the dns endpoint for my database at DB_HOST env. I am connecting through inside a VPS. I can connect with the follwing command:

mysql -u {username} -p{password} -h {remote server ip} {DB name}

But I can't with laravel.

Yikes, I managed to solve it. The issue was happening because nginx didn't have permissions to access to the directory, which means there was no readibility at all. Anyway, I switched it to project subfolder with proper permissions and ownership and now its working good :)