Pieter-Jan Pieter-Jan - 7 days ago 5
MySQL Question

Remote MySQL database connection Laravel 5 not working

I'm using laravel 5. i need to get some data from a remote MySQL database.

i've already set up my database connection in config/database.php.
This is how it looks:

'connections' => [

'mysql' => [
'driver' => 'mysql',
'host' => env('DB_HOST', 'localhost'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => false,
],

'remotemysql' => [
'driver' => 'mysql',
'host' => env('DB_HOST', '************'),
'database' => env('DB_DATABASE', 'osys'),
'username' => env('DB_USERNAME', 'Syn'),
'password' => env('DB_PASSWORD', '****************'),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => false,
],
],


The connection info is correct, i already tested it and i'm able to connect to the remote database.

To test it out i just fetched the database connection and the data in my controller to send it to a view to check whether or not everything is working alright. This is my controller:

...
use DB;
...

public function item()
{

$items = DB::connection('remotemysql')
->table('ip_products')
->get();
return view('admin.item', compact('items'));
}


and this is my view:

...
<tbody>
@foreach ($items as $item)
<tr>
<td>{{$item->id}}</td>
</tr>
@endforeach
</tbody>
...


When i try to load my view i get this error message:


SQLSTATE[42S02]: Base table or view not found: 1146 Table 'dvs.ip_products' >doesn't exist (SQL: select * from
ip_products
)


The error shows me that Laravel tries to get the table form the dvs database (wich is the main site database). So it isn't using the connection 'remotemysql'. If it couldn't connect to the remote database it would have got a connection error but i think its not using the remote connection at all.

What can i do to fix this issue ?

Answer

As You can see from error:

Table 'dvs.ip_products' >doesn't exist

where dvs is database name.

so according to Your config files:

'remotemysql' => [
    'driver'    => 'mysql',
    'host'      => env('DB_HOST', '************'),
    'database'  => env('DB_DATABASE', 'osys'),
    'username'  => env('DB_USERNAME', 'Syn'),
    'password'  => env('DB_PASSWORD', '****************'),
    'charset'   => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix'    => '',
    'strict'    => false,
],

env() method has 2 args: $key, $default

so it looks first to .env file to find $key in You case DB_HOST, DB_DATABASE... and if it's not defined will use $default that You define as second argument.

You can read about it here

Comments