db_set_active Issue - the {system} table isn't accessible under some conditions.

What happens if you put the table name in curly brackets { } as per Drupal coding standard, i.e.

db_query("INSERT INTO {name} VALUES ('','100','3','7','soontak1xxxx','kikilala','lalala','123456','soontak')");

How have you defined $db_url in settings.php?

http://api.drupal.org/api/function/db_set_active:

Description

Activate a database for future queries.

If it is necessary to use external databases in a project, this function can be used to change where database queries are sent. If the database has not yet been used, it is initialized using the URL specified for that name in Drupal's configuration file. If this name is not defined, a duplicate of the default connection is made instead.

Be sure to change the connection back to the default when done with custom code.

i.e. you need to define the connection string for the 2nd database in $db_url in settings.php.

Mine is like it :
$db_url2 = 'mysql://myuser:mypass@localhost/mydb';

and when i use db_set_active('db_url2'); it's keep the default database...

I still search why.

pffff.... Many thanks sry :)

For future googlers, I also received a "doesn't exist query" error message using db_set_active() when there was a syntax error in the SQL that was being fed into the new database. It breaks down there, doesn't get a chance to switch back to the default database, and then hangs itself looking for the system table. So, check your SQL! :P

@earnie: I don't understand matt's issue, and I don't get how your patch could some his issue.

It seems to me that the system module is safe to assume the default database is active everywhere you put db_set_active() calls (two in private system modules functions: system_themes_form_submit() and system_modules_uninstall(); one, system_get_files_database(), very early in the bootstrap process; and the last one very late in system_region_list() where modules should have reverted to the default database).

Back to CNW, even if I'm unsure there is really an issue here.

@earnie: can you explain what are the "cases where the system module looks in the system table before the db_set_active is called (added: to return it back to the default)"?

As you explained yourself, watchdog() already calls db_set_active() to ensure that the default database is used.

An as I explained myself in #13, the db_set_active() calls you added in your patch look unneeded to me:

• Two of them are in private system modules functions: system_themes_form_submit() and system_modules_uninstall(), where no other module could have changed the current database.
• One, system_get_files_database(), is very early in the bootstrap process, so it's unlikely that the database was changed at that point.
• The last one, in system_region_list(), is called very late during the page generation process. At that point modules should have reverted to the default database, or this is a bug in these modules, not in system.

So, to wrap up: (1) you didn't explain what bug you are trying to fix, (2) you didn't explain why your alterations fix the bug in question. That's at least a "code needs work to me".

Which function of the system module is called in that case?

That's full of crap. We don't need to fix what's not broken.

As I explained to you, the four places where you put db_set_active() are almost guaranteed to occur in the default db connection. The only place that could potentially be unsafe is system_region_list(), but at that point if some piece of code changed the current DB, it should have reverted back to the default one (if not, that's a bug in the module's code, not in core).

Please explain to me which query you see failing in system.module, and what's the sequence of function calls that leads there. First explain the issue, and then (and only then) suggest a solution.

I didn't intend to open a can of worms here. I was just pointing out the fact that if you execute some bad SQL after switching from the default to another database, the second db_set_active() will not return control to the original database, and the rest of the request is executed against the second database.

To reproduce the error, set up a second database the has NO drupal tables in it. Execute the following in your module:

<?php
  global $db_url;
  global $base_url;
  $default_url = $db_url;
  $db_url = array();
  $db_url['default'] = $default_url;
  $db_url['new'] = 'mysql://user:pass@localhost/new_db';
  db_set_active('new');
  $result = db_query("THIS IS BAD SQL");
  db_set_active();
?>

Now, MySQL will return a syntax error, but the user won't see this error. Rather, the user will see a Table 'new_db.table' doesn't exist error. In my best estimation, this is because the bad SQL executed against the second database causes the second db_set_active() to fail, not returning control to the original Drupal database. Whatever the next query is runs against a database that does not have the table(s) it is looking for.

I hope this description is a bit more clear. Let me know if I can expand on this any more.

OK so the question is, why does the finaly db_set_active() fail and how much do we care? If the bad SQL is really bad and obviously so then presumably there will be errors all over the screen or {watchdog}. But in the case of a complex or generated query it might be more subtle and harder to trace.

Maybe it's simply that when the "bad" query falls over then any PHP code after it won't be executed. http://api.drupal.org/api/function/drupal_error_handler/6 gets called, which invokes watchdog/hook_watchdog e.g. http://api.drupal.org/api/function/dblog_watchdog/6. This *temporarily* set the default DB to be active, so that the error can be logged. Maybe drupal_error_handler should "permanently" set (restore) the default DB to be active, if it is a DB error.

But the question in my mind is - what goes wrong with it as it currently is? Do you get loads of additional DB/query errors? It should still be fairly apparent where the original problem lies. Drupal doesn't try to compensate for badly coded modules.

[update] Actually any PHP error could have the same effect of preventing the connection being restored, hence if drupal_error_handler is mod'ded to reset the DB connection it should do so regardless of the error type. Possibly. Except for Notices/Warnings? which don't terminate PHP flow? I'm out of my depth!

I can't reproduce this issue on D6.

On D7, there is a small glitch when the implementation of hook_watchdog() is not cached yet by the registry (ie. when the website was not used at all before). Here is small patch that makes drupal_function_exists() more robust to a change of database. That should solve the issue on D7.

Proper status.

Patch looks cool. RTBC if testbot passes.

If there's a query that we know will 100% of the time need to be run on the main database, don't use db_query() but use Database::getConnection('default', 'default')->query() instead. We already do that in a few places, such as parts of the registry and in dblog_watchdog().

It's not something that modules will ever use outside of core, I suspect. The fact that you can do so is documented in the handbook docs. In *most* cases you do want to use db_query() so that you can do all sorts of exciting trickery with swapping the active database, and use a direct connection for your own stuff instead. If you're mixing your own external DB calls and Drupal calls a lot, you should use a direct connection object for your code, not Drupal's.

The only places that we want to make Drupal explicitly call the host database is places where code could get triggered from within some other routine unexpectedly, such as error handlers, the registry, etc.

See: http://drupal.org/node/310070

"Note that in the vast majority of cases you will not need to request the connection object directly. Rather, the procedural wrappers will do so for you. The only reason you would ever need to access a connection object directly is if you are doing complex manipulation of more than one database and you do not want to change the active database."

This *has* been documented since last September. Any time in core that I directly hit the main database, I also have a comment line or three explaining why so as to not confuse someone reading the code later. We should do the same here.

Hi,

Have A Look ON The Issue Below i Was checking/ testing for drupal 6.16:-

Settings.php:-

$db_url = array (
    "default" => "mysqli://root:@localhost/drupal_mian",
    "drupal7" => "mysqli://root:@localhost/drupal7_db",
    "project2" => "mysqli://root:@localhost/project2",
);
$db_prefix = array(
    'default'   => 'rts1_',
    'drupal7'     => 'd7_',
    'project2'  => 'proj1_'
);

IN code:-

db_set_active('project2');

//For Line Below Drupal 6 Giving error for me..!!!! Strange..!!!!
$node_obj=db_fetch_object(db_query("SELECT * from {node} WHERE nid=420"));

//Link Below is suposed to be for New Different Site......!! It is still for Default Site... ;-(
$link=l($node_obj->title,"node/$node_obj->nid",array('absolute'=>TRUE));
db_set_active('default');

Thanks & Regards
-Inder Singh
http://indersingh.com
http://shikarimata.com
http://indiauser.com

inders: Your inquiry does not involve Drupal 7, the system table, or anything even remotely related to this issue. What you want is a support ticket against Drupal 6, not a bug report. Please do not hijack issues.

The original issue here is fixed anyway, since we do have it documented that we should just access the default connection directly in those cases. So I'm going to mark it fixed.

Removing needless tags.

For future googlers, I also received a "doesn't exist query" error message using db_set_active() when there was a syntax error in the SQL that was being fed into the new database. It breaks down there, doesn't get a chance to switch back to the default database, and then hangs itself looking for the system table. So, check your SQL! :P

This... and I keep hitting the problem and thinking I've done something wrong outside of my SQL syntax. (I'm getting the system table missing error). The thing that finally helped me find *this* issue was running the code in Devel's Execute PHP page; where my SQL problem is actually showing up correctly (finally).