Accessing remote mysql database from pimcore 5 - getting a redirect error

Hi,

I’ve added an external (legacy) mysql database to config.yml by importing databases.yml with the connection details. I know it is connecting, after various times it didn’t, but now it’s throwing this error when I try to access a page

An exception occurred while executing 'SELECT id FROM redirects WHERE active = 1 ORDER BY `priority` DESC':

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'mydbname.redirects' doesn't exist

Why is it doing this? What is the right way to access a remote mysql database by pimcore/symfony/doctrine best practice? I could have done this hours ago with plain php but I’m trying to do it right and learn. It’s only to import some data (I could have exported it as a csv file but I want to be able to easily replicate it in the future).

Thanks
John

It really seems that your remote database is missing a ‘redirects’ table. Is the remote db really a db of a Pimcore installation of the same version? I am not exactly sure, what you are trying to achieve. Do you wan’t to have both connections available at once?

Cheers, Igor

No Igor, it’s a legacy database from another app that I want to import data from. It’s not a Pimcore database.

As I say, I could access it using standard php easily enough but wanted to try to do it properly and thought this was the right way. Presumably not?

Thanks

Hi John,

I now understand what you mean. You can do that by adding something like this to your app/config/config.yml:

doctrine:
    dbal:
        connections:
            mylegacydb:
                driver: pdo_mysql
                host:   pimcore-mysqldb
                dbname: mylegacydb
                user:   mylegacydb
                password: mylegacydb
                charset: UTF8MB4

And then you can retrieve the connection in your code like this:

    $container = \Pimcore::getContainer();
    $db = \Pimcore::getContainer()->get('doctrine.dbal.mylegacydb_connection');
    /** @var \Doctrine\DBAL\Driver\PDOConnection $result */
    $result = $db->fetchAll('SELECT * FROM testTable;');

Hope this gets you further, if not, let me know and post your config here (without passwords of course).

Cheers, Igor

2 Likes

Absolutely perfect. Thanks Igor.

To check I know what is going on can I confirm that by using connections: and giving it a name here, Symfony is making the database connection, but Pimcore itself is not doing anything with.

But in my original use, when I was putting in my connection without a name, it was being treated by Pimcore as the default database for the whole Pimcore installation? And that’s why it was failing?

I’m working with Pimcore and Sylius (we’ve discussed it on Gitter) and new to Symfony so this is a learning curve but I’m getting there :wink: I’d actually just been testing similar on Sylius (using connections: and default_connection: etc) but this is perfect and does exactly what I needed. Thanks again.

Exactly, Pimcore is using the default DBAL connection and you were owriting its config. By naming your second connection, you can have both running simultaneously.

Glad I could help :slight_smile:

Cheers, Igor

1 Like

Hi Igor,

I followed this example and was able to connect and query a remote postgresql DB. But I am not able to get an encrypted password to work. I see in the parameters.yml there is a ‘secret’ stored there. How do I encrypt my password in this way?

Thank you.