Laravel migrate command is not working for remote database

Question

I am working on a project using the Laravel 4.2 framework. I want to execute the command php artisan migrate but when I run this command it shows an error:

[PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1142 CREATE command denied to user 'abc'@'10.1.1.27' for table 'migrations'

I think I have placed the project files and the database on different servers, so that's why am I getting this error.

Database server: 10.1.1.56

Project server: 10.1.1.27 (localhost)

Curiosa

post your env file details – Savitt

Have no .env file in project – Curiosa

it maybe hidden.. – Savitt

are you checking from the ide? – Savitt

I am using xampp and want to execute command from cmd but database is on different location(server) – Curiosa

yeah i got that..but where are you giving the database credentials? – Savitt

like database user name database host and all that.. – Savitt

In app/config/database.php and params are: 'mysql' => array( 'driver' => 'mysql', 'host' => '10.1.1.56', 'database' => 'rnd_test', 'username' => 'abc', 'password' => 'abc123', 'charset' => 'utf8', 'collation' => 'utf8_unicode_ci', 'prefix' => '', ), – Curiosa

Let us continue this discussion in chat. – Savitt

@anil Make sure that your configuration is correct. – Reclamation

this has to do with your laravel database configuration – Chicken

yes laravel configuration is correct. when i connect with local database then migrate command is working but when changed to remote database then migrate command generate error – Curiosa

May I ask what is your mysql version? – Boatsman

Answer 1

This error indicates that the remote MySQL database isn't configured to allow certain operations executed by the user abc from the IP address 10.1.1.27. In many cases, MySQL users are set up to allow access from the the same host as the database server, but we need to explicitly GRANT access for clients connecting to the database from a remote host.

We can use the following commands to grant the user abc access from remote hosts. We must run these commands as a user that has the ability to grant privileges to other users (such as the database's root user):

GRANT ALL PRIVILEGES ON database.* TO 'abc'@'%';
FLUSH PRIVILEGES;

Replace database with the name of the application's database configured in .env or config/database.php. The wildcard '%' in 'username'@'%' indicates that we want to give permissions to that user from any IP address. If we don't want to allow the user access from any IP address, we can limit access to specific IP addresses by replacing the wildcard with the address to allow (10.1.1.27 in this case).

Depending on the needs of the project, we may not need to give a user all privileges to a database. See the documentation for a list of available privileges that we can assign.

Chandigarh

Answer 2

1142 CREATE command denied to user 'abc'@'10.1.1.27' for table 'migrations'

The above command simply means that the user don't have CREATE permission on the connected database. So first of all you have to grant the privileges to that user on the database and after that run the migration.

Explanation: When you run migrate, a table is created on with the name migration in the database that maintains the status of migration ion it, and you don'r have the CREATE permission that's why it is showing error.

Gramarye