By mediafrenzy on

Hi, my main drupal site usually works fine, and has been for years, but today it went down due to mysql issues on my server.

During this time, I realized that a message is shown to all the search engine visitors reaching my inoperative site "Unable to connect to database server", along with the database username etc!

Naturally I don't want my site to be down in the first place, but if it ever is, I don't want to hand out my DB username, or display all the drupal help info - does anyone know how to change the "Unable to connect to database server" message/page?

⋅ Categories: Drupal 4.7.x

Comments

mediafrenzy’s picture

mediafrenzy commented

Anyone got any tips on how to change this?

hexa’s picture

hexa commented

I would like to know the same.

eagereyes’s picture

eagereyes commented

I just had the same problem. It would be cool to be able to configure a static HTML file that is used in case no db connection can be established. Then I can make it say whatever I want (i.e., that the site is experiencing problems), and keep the world from knowing my db user and host.

EagerEyes.org

FlemmingLeer’s picture

FlemmingLeer commented

I don't think you can remove that message unless you go deep into the core of Drupal.

mediafrenzy’s picture

mediafrenzy commented

Shame really. Seems an odd way to do things in my opinion.

I actually just saw this error at drupal.org a couple of minutes ago! Too many sql connections caused it for them apparently, and so their host and db username was displayed to god only knows how many visitors...

sepeck’s picture

sepeck commented

I am still unsure why this is a problem. I understand the 'obscure everything' approach in theory, just don't really agree with it. In drupal.org's case, the database server is not directly Internet accessible so the information doesn't actually do you any good.

I believe there were some changes in Drupal 6 regarding this.

-Steven Peck
---------
Test site, always start with a test site.
Drupal Best Practices Guide -|- Black Mountain

-Steven Peck
---------
Test site, always start with a test site.
Drupal Best Practices Guide

mediafrenzy’s picture

mediafrenzy commented

Not a huge issue, but personally in the event of a server/mysql problem, I would prefer not to display to all of my visitors a message along the lines of "Your Drupal site is broken - heres a bunch of your technical database/server specifications"...

I would instead prefer to be able to point even to a static html page, probably something which simply says "MyWebsiteName is currently experiencing technical difficulties, we'll be back online shortly! Thank you".

Seems to me theres simply no need to display all the details to the visitors.

Also, I know Drupal.org wasn't really put at any great risk, I only mentioned it as I had just seen it moments before replying to the latest comments in this thread.

800series’s picture

800series commented

Unfortunately, this is not such a trivial issue for me. Apparently my server host's MySQL database goes down for maintenance at the same time everyday, so this message is on my site for all the world to see for the same 30-50 minutes every day.

This is another one of my (very few) "I can't believe they coded it like this" head-scratchers. I'm overall very impressed with Drupal, but this bug is kicking my butt. I suppose I can afford to have my site unavailable with a nice customer-facing site-down message -- eagereyes' suggestion (quoted below) would be a fine solution.

However, having:

"Unable to connect to database server. If you still have to install Drupal, proceed to the installation page."

...with "installation page" as a clickable link to http://www.example.com/install.php -- for such a big chunk of each day -- just doesn't meet my needs for professionalism. (I'm not blaming Drupal for my host's daily downtime, obviously, but the error page facing the customer just isn't going to cut it for me.)

(I was thrilled when I found Administer > Site configuration > Error reporting > "Write errors to log", rather than writing them to the screen, but I was kind of crushed when I found that this setting doesn't affect the error page we're discussing in this thread.)

I'm not giving up on Drupal -- And I'm relatively new to these message boards, so if this can be submitted as a change request or something, please let me know. Much appreciated. I find 100 great things about Drupal for every 1 catastrophe like this, so I'm holding out some hope.

Thanks.

It would be cool to be able to configure a static HTML file that is used in case no db connection can be established. Then I can make it say whatever I want (i.e., that the site is experiencing problems), and keep the world from knowing my db user and host.

mediafrenzy’s picture

mediafrenzy commented

30-50mins mysql downtime every day is pretty bad - you might want to look for a new host?

In any case, thanks for the support of this issue, I think plenty of folks would agree that its not ideal to be displaying this kind of info to visitors, even in rare downtime situations...

eagereyes’s picture

eagereyes commented

It's true that most db servers probably/hopefully are only accessible from the web server, so the leaked information is of no use to anybody. But I completely agree with 800series that it makes the site look amateurish and broken. While half an hour to an hour of downtime per day is clearly unacceptable, every affordable hosting provider has its downtimes, and that message will appear on every site now and then. And it seems so easy to provide a fixed location for an html file, and if it's there, it gets shown instead of this message.

But that makes all the difference: a properly formatted error message that looks like the site and not some default installation page layout. That page could even link to a bunch of static pages so that there is at least some content there (like contact info), even when the entire site is really down.

EagerEyes.org

mediafrenzy’s picture

mediafrenzy commented

Ok so there seems to be some more support for this, so where to from here? I'm not too familiar with how we can now go about trying to get this added to drupal core?

eagereyes’s picture

eagereyes commented

I also noticed yesterday (when upgrading to 5.2) that the page that's shown when the site is offline is also rather ugly. This is a much smaller problem obviously, but it could be addressed in a similar way with a configurable static page.

I'm going to be searching for support requests regarding this, and if there is none, I'd suggest we start one. We could also try to get this into Drupal 6, though it might be too late for that now (code freeze).

EagerEyes.org

ericatkins’s picture

ericatkins commented

Well said. I have the same concerns.

---
the Hushed Casket | Eric Atkins

nels’s picture

nels commented

I was able to achieve this by changing includes/database.mysql.inc, line 106-131 , and includes/theme.inc around line 441.
(I changed the <p> style to include style="display:none" for the 'the mysql error was' part, for starters.)

Then all that's left is fixing the maintenance.css

this definitely should NOT be done on a live site. I'm sure it's possible to destroy something vital in there, though I didn't have any serious problems. (this is not yet live on vcsb.com, but should be later tonight)

Hip hop funk at www.vcsb.com

wwwoliondorcom’s picture

wwwoliondorcom commented

Where is the message for drupal 6 ?

Hi,

The text is not in this file on Drupal 6, do you know where to edit it ?

Thanks.

allella’s picture

allella commented

Drupal 6 allows for creating a custom maintenance template file into which you can put any static (X)HTML code and custom error message you want to show.

Below I posted a simple example of how to setup the custom offline maintenance template in Drupal 6 If this isn't clear then Google search for "maintenance-page.tpl.php" and you'll find more robust examples.

allella’s picture

allella commented

I'm in complete support of providing a basic update to core to allow overriding the default error message when the database connection fails. The security and lack of professionalism associated with the current message seem enough to warrant such a change.

I removed lines 103-116 and 121-131(Drupal 5.1 and 5.2) from /includes/database.mysql.inc and replaced with two simple PHP includes as a fast fix. This covers the case when the connection fails due to a bad user or password (lines 103-116) and when the usr/pwd is correct, but the database cannot be found (lines 121-131). This seems to be a work-around for MySQL. If you've got a different database then it looks like modifying the database.pgsql.inc (PostgreSQL) or database.mysqli.inc (MySQLi) would do the same.

  if (!$connection) {
    // Redirect to installer if using default DB credentials
    if ($url['user'] == 'username' && $url['pass'] == 'password') {
      include_once 'includes/install.inc';
      install_goto('install.php');
    }
     // START of Removed Code

       // NEW CODE - access the global $conf array, which is initialized in the current site's /site/sitename/settings.php file
     global $conf;

       // NEW CODE - This includes (and displays) a particular file in the event of a DB failure.  
       // It looks at the 'theme_settings' value of the $conf array in settings.php.
       // Alternatively, you could hard-code the include below if you are running a single site
     include_once 'themes/'.$conf['theme_default'].'/default-error-page.php'; 

    // END of Removed Code
    exit;
  }

  if (!mysql_select_db(substr($url['path'], 1))) {
     // START of Removed Code

       // NEW CODE - access the global $conf array, which is initialized in the current site's /site/sitename/settings.php file
     global $conf;

       // NEW CODE - This includes (and displays) a particular file in the event of a DB failure.  
       // It looks at the 'theme_settings' value of the $conf array in settings.php.
       // Alternatively, you could hard-code the include below if you are running a single site
     include_once 'themes/'.$conf['theme_default'].'/default-error-page.php'; 

    // END of Removed Code
    exit;
  }

As a change to core, I'd suggest allowing the Drupal Admin specify a default failure script via the backend, like on Administer » Site configuration » Error Reporting (i.e. /admin/settings/error-reporting ). However, rather than specifying a single error node page, it would make sense to look for a particular file (php, HTML, or some such) in the current theme's folder. Specifying a node's address or static file location would work, but would not be appropriate for multiple sites running on a single code base. I'm guessing the theme could be pulled from the settings.php file in the event the database is inaccessible (as shown in the code above. Though, I don't know enough about core to know if this would be the best solution.

On a related note, Drupal has been awesome for hosting multiple sites on a single code base. We did 6 sites on a single 5.1 base. I've run into some other non multi-site friendly features, like configurations in the 'Error Reporting' page, robots.txt and Sitemap. I've previously commented on some multi-site hacks for robots.txt and Sitemaps.

I know it's easy to make suggestions from the sidelines. I don't exactly have the expertise, time or desire to be a Drupal core programmer. However, if anyone reads this and is making such changes or wants to pass them along to the core developers then I'd be glad to share code I've written to hack / extend the core for multiple instances on one code base.

That is all. Thanks.

Jim

OrangeCoat.com

sepeck’s picture

sepeck commented

However let's change some perceptions. You play with core code, you are qualified to start contributing to Drupal core and learning what you don't know and helping with what you do. We need more people willing to take a little time every week. Drupal core is already complex and the number of people we have working on it is relatively flat at any given time. We need more people for perspective, skill, expertise and sustainability. If people don't have time, less nice things will get done.

Drupal core 5 is frozen, no features will be added or changed with it. Drupal 6 I think has a more theme-able method to do this. One strategy with patches like this is to create a patch and an issue against drupal project and mark it won't fix. That way it is there for the adventurous to try on their sites.

-Steven Peck
---------
Test site, always start with a test site.
Drupal Best Practices Guide -|- Black Mountain

-Steven Peck
---------
Test site, always start with a test site.
Drupal Best Practices Guide

dvessel’s picture

dvessel commented

And here is the issue for displaying a maintenance page with no db connections. It's not in yet.

http://drupal.org/node/141727

allella’s picture

allella commented

Just converted to MySQLi and, sure enough, it's pretty much the same process as I described above for the mysql.inc This time I edited the Drupal 5.2 /includes/mysqli.inc, starting at line 83, as follows:

  if (mysqli_connect_errno() >= 2000 || mysqli_connect_errno() == 1045) {

    // START OF REMOVED CODE

    global $conf;    // NEW CODE - reference the global array $conf, set in '/site/sitename/settings.php'

    include_once 'themes/'.$conf['theme_default'].'/default-error-page.php';	// NEW CODE - include the new error page

    // END OF REMOVED CODE
  }
  else if (mysqli_connect_errno() > 0) {
    // START OF REMOVED CODE

    global $conf;    // NEW CODE - reference the global array $conf, set in '/site/sitename/settings.php'

    include_once 'themes/'.$conf['theme_default'].'/default-error-page.php';	// NEW CODE - include the new error page

    // END OF REMOVED CODE
}

Remember, in order for this to work you'll need to uncomment the section of code which defines the $conf array in the appropriate settings.php. If you use multiple themes then you'd set the theme_default -> 'yourthemenamehere' value equal to your desired theme name for each settings.php. This makes the code work regardless of the theme selected.

Alternatively, you can just hardcode the error file to use a single theme error file, like

include_once 'themes/yourthemenamehere/default-error-page.php'; // NEW CODE - include the new error page

OrangeCoat.com

mrgoltra’s picture

mrgoltra commented

.. to have a backup db server that would kick in if primary db server is not accessible? Just wondering.

mediafrenzy’s picture

mediafrenzy commented

I'm pretty sure this sort of thing is possible, but I would think it would be a very complicated process - both to setup, and to maintain. At least for someone who wasn't totally comfortable and familiar working with multi server website configurations.

Quint’s picture

Quint commented

Yeah, I was thinking along a similar vein, but more of a static fall-back version. There are scraper programs that can download an entire site "for offline viewing". I think Adobe Acrobat does it too. If the Drupal database is not available, could Drupal just re-direct the queries to static pages? I don't think the backup file(s) would have to be too large either, because it would only have to contain the text, the images aren't lost when a db goes down. As a matter of fact, this might be a level of caching that could be useful even when the db is not down.

Quint

Quint’s picture

Quint commented

It looks like what I had in mind pretty much already exists in the Boost module -- http://drupal.org/project/boost

With some tweaks this could cover up a database-down situation. Or actually, just don't run the 'clean up' cron.

Quint

scedwar’s picture

scedwar commented

Is this in Drupal 6.0?

Having just experienced the same thing with my own site I'm going to be hacking core if necessary to ensure that this doesn't happen. An error screen shown to end users should be aimed at end users, not database admins...

dvessel’s picture

dvessel commented

It is in. Look inside your settings.php near the bottom. It has a few notes on what you need to do.

If you know how to work with template suggestions, then you can add a file named "maintenance-page-offline.tpl.php" which is picked up only when the db is dead.

⎋ joon park

tcconway’s picture

tcconway commented

Any further learning on this?

I'm still looking for a good way to change the "Unable to connect to database server" page. Did anyone yet figure this out yet?

I did find a way to modify the "Site Off-line page" by following the info on http://drupal.org/node/141727#comment-551330 -- this works perfect. Is this the same approach to modify the database server page?

thanks!

allella’s picture

allella commented

The code snippets I added above are a hack to core which works for MySQL or MySQLi connections. It requires modifying two core files. I don't believe there is a way to do it without hacking core.

Hacking core is generally a very bad idea. Considering Drupal 5 is unlikely to undergo many new versions besides security fixes, I'd guess you wouldn't need to do too many future hacks if you went this route. However, I understand this functionality is in Drupal 6. Most of the popular contributed D6 modules should be production ready now or in the coming months, so you could move to D6 instead of hacking D5.

OrangeCoat.com

allella’s picture

allella commented

Basic example of using Drupal 6's maintenance theme files

In your site's settings.php file the $conf array should be uncommented and the 'maintenance_theme' value set to the theme name where the maintenance themes are located.

 $conf = array(
   'site_name' => 'My Site',
   'theme_default' => 'garland',
   'anonymous' => 'Visitor',
   'maintenance_theme' => 'garland',
);

Garland comes with a sample maintenance-page.tpl.php file. This template file will override the page.tpl.php if/when you put the site in maintenance mode, via /admin/settings/site-maintenance If you create your own, or modify an existing, maintenance-page.tpl.php file then be careful about removing Drupal theme variables, like $content, $left, $right, $header, etc, or you may have trouble administering the site.

If you add a maintenance-page-offline.tpl.php file to your theme folder, as defined in the settings.php, then if/when Drupal is unable to connect to the database that template will be used.

wwwoliondorcom’s picture

wwwoliondorcom commented

How to modify the UNABLE TO CONNECT TO DATABASE message on Drupal 6 ?

Hi,

Is your code made to replace the maintenance page ?

I don't really understand how to customize the UNABLE TO CONNECT TO DATABASE message on Drupal 6, can you help ?

Thanks.

allella’s picture

allella commented

In Drupal 6 you can define create a template file (.tpl.php) within a theme folder. The content of which are displayed when the database is unable to connect. Generally you'd put static XHTML into this template file because when the database is down you can't get many of the Drupal variables to render because there is no database.

See Theming the Drupal 6 maintenance page for more details.

It's always a good idea to clear the cache /admin/settings/performance when you add new template files. To test you can modify the $db = line of your settings.php file to use a bogus connection username, password, or database name.

wwwoliondorcom’s picture

wwwoliondorcom commented

Thank you, but is there any new easy way to change the mySql unable to connect page now ?

allella’s picture

allella commented

This is the new easy way, as of Drupal 6. In Drupal 5 you had to hack core.

In Drupal 6 you can add a maintenance-page.tpl.php file to your themes folder and then clear the Cache on /admin/settings/performance. Pretty easy.