Connect remote database

Need Help
1

Hello:
I’m trying to manage remote database table(contents) has association with table in my database (users)… everything working fine … but when I’m trying to get contents list with related users … the association search for users table into remote database where the contents table is … how to get list from remote database table has local database association? like below:

$data = $this->Contents->find()->contain(['Users']);

contents table: exist in remote database
users table: exist in local database

of course I set the connection for ContentsTable to point to remote database correctly like below:

public static function defaultConnectionName(): string
{
    return 'Pt'; // remote database connection name
}

Thanks in advance

2

Does the ContentsTable work without doing any contain() or association?

The problem with this is the fact, that depending on the type of association between Contents and Users you either have a join or a separate query.

And you can’t join 2 tables from 2 different database servers. It’s technically possible to join 2 tables from 2 different databases on the same database server, but that’s the extent of what you can do there.

1 Like
3

Some of this can be managed by using the “select” strategy instead of the default “join” when defining model associations.

1 Like
4

Actually I have my two software on two subdomains and tow separated hosts…
one on pt.propertyturkey.com and another on ptpms.propertyturkey.com … now I can select everything correctly with association ( remote->locally and vise versa ) from remote database by regular association by setting up defaultConnectionName() to remote database, and it works fine locally … but online gives me error

SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to user ‘ptpropertyturkey_pt’@‘localhost’ for table ‘users’

is there way to make the two separated software permitted to each other? … being it works locally , I think it’s applicable online

5

This looks like it’s correctly trying to make the remote query, but being blocked by permissions. That’s a database setup issue, nothing to do with Cake.

6

Thank you so much guys …
actually it turns out not cakephp at all, but permissions issue …
I ended up with work around solution … its not accomplishable thing to make association with remote table , becauase at the end you need to give the local user full previlige over localdb and remotedb … I don’t know if there is a way to do this between two servers …

my solution:
I placed all databases at one server, where I’m able to grant my user previliges for all databases … and it worked perfectly

7

As I said, yes. Of course, you need permission on each database to do whatever you are trying to do on that database. This holds no matter where the database is relative to your application. If you’re only reading from that second database, then you don’t need full privileges there, only those related to reading. But if it’s not possible to have those permissions added there, then you are just not ever going to be able to work with that database.