Posted by alex.hoebart on Mon, Feb 14, 2022 - 4:00am --- Updated: Feb 14 '22 - 4:18am

Hello,

I was excited as I recently discovered Backdrop and I'm now evaluating it as potential upgrade for several Drupal 7 sites - some of them need access to a PostgreSQL database (in custom modules).

I understood that Backdrop core only works on MySQL, but the documentation suggests that it is possible to use other external databases (see https://docs.backdropcms.org/documentation/database-configuration#other-...).

However, I did not succeed in accessing my external database. If I follow the documentation exactly, I get the following error message:

$result = db_query("SELECT * FROM mytablename", array('target' => 'mydatabase'));
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'backdrop.mytablename' doesn't exist: SELECT * FROM mytablename; Array ( [target] => mydatabase )

It seems that the target option is completely ignored, as it tries to access the backdrop core database.

It seems that the given example for db_query is wrong, as options should be the third, not the second parameter.

But even when I change the call to db_query and add an empty array as the second (args) parameter, the result is the same (only that the array in the error message is empty).

Has anybody succeeded in connecting Backdrop to an external database? Looking at the code, it does not look like it is possible. But I was just hoping for it as the documentation clearly provides this option.

 

 

Most helpful answers

OK, I added a bug report here

OK, I added a bug report here: https://github.com/backdrop/backdrop-issues/issues/5510

I would also submit an issue for the documentation error, but that's not a bug or any other issue category available (or?): https://github.com/backdrop/backdrop-issues/issues/new/choose 
How to do that?

Related to Silkscreen: with all respect to that project, but a fork of a fork is in my understanding not the most sustainable way to keep an existing feature.

As you can see in my bug report, I tested to simply add the Drupal 7 pgsql driver and it seems to be possible to integrate it into Backdrop.

So, I'm hoping that I'm not the only person in the world who is looking into Backdrop and still using Drupal 7 with external PostgreSQL databases (and experiencing that also Drupal 9 is not ready for it, e.g. https://www.drupal.org/project/drupal/issues/1060476). I can really understand why Backdrop dropped PostgreSQL support from core, because not many contrib developers are willing and able to support different database types. But complete disconnection from non mysql databases would be a big loss in usage options for Backdrop, I think.

Comments

oadaeh's picture

Mon, Feb 14, 2022 - 5:19am by oadaeh

I haven't tried this myself, but according to the db_query() documentation, the place where target is supposed to go is the $options array, which is the third parameter of the function, not the second.

Try adding an empty array between the query and the 'target' array and see if that works.

 

Mon, Feb 14, 2022 - 5:23am by alex.hoebart

I also noticed that and tried it, but the result is the same, as mentioned above.

oadaeh's picture

Mon, Feb 14, 2022 - 9:27am by oadaeh

Sorry, I just went by the code you posted. You cannot exclude the second parameter and expect it to work, even if it doesn't do what you're expecting with it in place.

The code does make use of it:

Maybe what is happening is that it is being ignored or lost in getConnection(). You'll need to debug it at that point to see what is happening. Maybe the target you're specifying ends up either being in $ignoreTargets or not being in $databaseInfo.

Tue, Feb 15, 2022 - 1:00am by alex.hoebart

OK, I further debugged this:

If I do the correct db_query statement, I receive this (target option is ignored):

$result = db_query("SELECT * FROM mytable", array(), array('target' => 'other_server'));
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'backdrop.mytable' doesn't exist: SELECT * FROM mytable; Array ( )

I found out that the function getConnection does not find the right target database, because it is looking for $databaseInfo[$key][$target], but according to documentation, external database should be defined as $databases['other_server']['default'] - note that db_query is calling Database::getConnection only with $options['target'], so I can only set $target, but not $key which would select the other database.

I then tried to adapt my settings to $databases['default']['other_server'] to see if that would work.

And yes, it works with another mysql database. But this is obviously only intended for master/slave setups, not what I am intending to do.

Anyway, the switched setting also does not work with a pgsql database. I get a white screen and watchdog shows this warning:

Warning: require_once(/var/www/dhis-dev/web/backdrop/core/includes/database/pgsql/database.inc): failed to open stream: No such file or directory in require_once() (line 1796 of /var/www/dhis-dev/web/backdrop/core/includes/database/database.inc).

Also note that the documentation is even more wrong, because $target was never meant (in Drupal7) to connect to other databases than a slave. The correct way to query an external database would be:

db_set_active('other_server');
$result = db_query("SELECT * FROM mytable", array());
db_set_active('default');

Using the right settings ($databases['other_server']['default']) this works as expected for mysql, but not for pgsql databases - same result as above (Backdrop is looking for a pgsql database file it does not have).

My conclusion is that other database connections are not implemented in Backdrop and the documentation is wrong. Could be that the baby was thrown out of the bathwater when support for other database types was dropped in core.

I just wonder what the documentation is writing about, and if support for other external databases will be available again in the future. I'm hoping for it....

 

oadaeh's picture

Tue, Feb 15, 2022 - 5:49am by oadaeh

It would be helpful if you could open issues and post your findings (one for the documentation and one for the code), so that they get corrected: https://github.com/backdrop/backdrop-issues/issues

Also, as an FYI, there is a fork of Backdrop CMS called Silkscreen CMS that did not remove support for different databases. Other than a few changes like that, it is otherwise the same as Backdrop and follows the Backdrop release schedule. Maybe you would have better success with that:

Tue, Feb 15, 2022 - 8:11am by alex.hoebart

OK, I added a bug report here: https://github.com/backdrop/backdrop-issues/issues/5510

I would also submit an issue for the documentation error, but that's not a bug or any other issue category available (or?): https://github.com/backdrop/backdrop-issues/issues/new/choose 
How to do that?

Related to Silkscreen: with all respect to that project, but a fork of a fork is in my understanding not the most sustainable way to keep an existing feature.

As you can see in my bug report, I tested to simply add the Drupal 7 pgsql driver and it seems to be possible to integrate it into Backdrop.

So, I'm hoping that I'm not the only person in the world who is looking into Backdrop and still using Drupal 7 with external PostgreSQL databases (and experiencing that also Drupal 9 is not ready for it, e.g. https://www.drupal.org/project/drupal/issues/1060476). I can really understand why Backdrop dropped PostgreSQL support from core, because not many contrib developers are willing and able to support different database types. But complete disconnection from non mysql databases would be a big loss in usage options for Backdrop, I think.