landed
By:
landed

Joomla websites keep loosing connection to databases (I think)

June 20, 2017 728 views
Joomla Ubuntu 16.04

nginx log sample line

2017/06/20 15:37:26 [error] 20796#20796: *11706 FastCGI sent in stderr: "PHP message: PHP Warning:  session_write_close(): Failed to write session data (user). Please verify that the current setting of session.save_path is correct (/var/lib/php/sessions) in /var/www/site.com/html/libraries/joomla/session/handler/native.php on line 194" while reading response header from upstream, client: 94.2.238.187, server: site.com, request: "GET //jreviews/videos?m=6b22D HTTP/1.1", upstream: "fastcgi://unix:/run/php/php7.0-fpm.sock:", host: "site.com"

Here is what you see - https://www.evernote.com/l/APHebuV1LQRCDabAqZ6UdEpPcxskMoTzilY

The error is so bad the application fails and this has been linked to a connection problem with the database. Is it possible the connection dies. It is intermittent and quite frequent.

Thanks for any help..this is going to kill my SERPS that aren't great and one of the reasons for moving to DO.

5 Answers

I'm not seeing any indication of a database issue, the error seems pretty clear: it's trying and failing to save php session data at the path specified in the error message. You need to make sure the path in the settings (php settings, not joomla) is correct, and that the filesystem permissions and ownership are also correct.

Hi @landed

The default PHP session.save_path is /var/lib/php/sessions, but if somehow the session cleaner script has never been able to run, then this directory might contain an extreme number of files, which means it cannot create new sessions.
Run this command ls /var/lib/php/sessions | wc -l to get the current count of files.

@landed

The issue looks to be more so with PHP than it does MySQL, at least from the error in your post. You can check the MySQL error log by running:

tail -25 /var/log/mysql/error.log

If that doesn't work, you may need to adjust the path depending on the directory that is used by your version of MySQL.

You should be able to do an ls on the /var/log directory to find the correct one.

ls /var/log

From there, we can see if MySQL is actually crashing and if so, what is being logged. If we find that MySQL is crashing, it's most likely due to limited RAM, in which case I'd recommend upgrading your Droplet to the next size up. So if this is a 512MB Droplet, upgrade to a 1GB Droplet.

Optimizations can be done on smaller Droplets, though it's constant and on-going. It'd be far easier to upgrade and add free RAM to what's available unless your comfortable modifying core configuration and troubleshooting.

...

That said, since this is an [error] and not a [warning], it's something you need to fix. Generally when PHP can't write to the sessions directory, it either doesn't exist or you simply don't have permission to write to it. That seems to be the case here.

If you run:

ls -al /var/lib/php

the ./sessions directory should be owned by root and have default permissions of drwx-wx-wt. If you seen any variance there, then that may very well be the cause.

In most cases, it's better to actually write session data to another directory, other than the global, if possible. In some cases this can be configured by the script or application, in others, there are no direct options to do so. You'd have to check and see if Joomla supports this. If it does, you could simply create a new directory below your public directory and call it session and change to use that directory. That way you're in full control over the directory and how it's handled.

  • I can't seem to reply .. well I can some short text but I am unable to post my code output..I got a spam mark against my name here as I posted (failed when I tried with a spam warning) the url?

    • Error displaying the error page: Application Instantiation Error: Could not connect to MySQL.
      Warning: session_write_close(): Failed to write session data (user). Please verify that the current setting of session.save_path is correct (/var/lib/php/sessions) in /var/www/site.com/html/libraries/joomla/session/handler/native.php on line 194
      

      So I now have errors turned on and do find the DB issue comes first. I am guessing this is why the session is not saved currently.

      Also it looks like the database for this site has issues now and I am going to replace with a backup. Something was wrong with mysql and I tried to repair tables that were mentioned in the sql logs...maybe a database can get corrupted if it runs out of local memory..this is all new to me as I come from a shared host experience. I need to develop my own understanding and learn this stuff. You guys are helping big time so many thanks. It's really starting to get as expensive as a managed VPS if I go to the next level up. I did my homework and deduced that the small droplet should be enough for some hardly visited websites.

    • @landed

      If I see it as spam, I can remove the block on my end and allow it through :-). If this happens, tag me and we can get that resolved. They are doing some work on the community, so that could be why some posts are going to spam -- I'll touch base with them and see if I can get more details.

      • It is happening I can't post the sql errors...can you check why that might be on my account? Thanks. It may be nothing is on my account but something to do with the type of content in the mysql (I am doing in code block)
        I have raised a ticket with support because the mysql server is repeatedly dying without load as far as I see it. I just tried to create an empty database and it died on me.

        • @landed

          I'm not seeing a comment in the spam queue from your username, though as a quick alternative, please try pasting to a PasteBin and providing me with the link:

          https://pastebin.com

          ...

          That said, what it sounds like, to me, is that MySQL is crashing because it doesn't have enough RAM.

          You can run MySQL on a 512MB Droplet, though it's not always easy as you need to be comfortable tweaking and tuning the configuration.

          A standard installation of MySQL may use anywhere from 300-500MB of RAM, so that doesn't leave much room for other services. If another service claims the free RAM first, MySQL will be denied and ultimately will crash as a result.

          Let's take a look at the error logs and see if I can confirm that for you.

@landed

This is what I thought I'd see:

2017-06-21T15:43:49.711386Z 0 [ERROR] InnoDB: mmap(137428992 bytes) failed; errno 12
2017-06-21T15:43:49.711400Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool

The first error specifically means that MySQL isn't able to allocate the RAM it needs to continue to function normally, thus it crashes. In this specific scenario, you need to upgrade to at least 1GB of RAM so that MySQL has the RAM it needs to function normally.

  • It was running fine. Is there a possibility that something got filled that I can empty or rebooting the whole server? I get that I can just increase but really thought that I should be able to run a few sites that are really low traffic on a small instance. If I need to upgrade then it blows the concept as I will soon be at a VPS managed service level cost. Maybe I need to look at AWS again...as I have low useage really.
    BTW thanks for all your help you are a credit to the DO forum and the industry.

    • @landed

      This isn't an issue with DigitalOcean and could happen on any provider, including AWS :-). MySQL requires a good chunk of RAM, whether you're running one site, or multiple.

      This is a very common error on low-RAM VPS's -- even in a managed environment where a provider is handling everything for you, there's only so much that can be done with so little RAM. Tweaking and tuning the configuration might help, but there's no guarantee as you're often tuning MySQL to make more efficient use of all available RAM in that case (which isn't going to work for you).

      Often there's a limit to how hands on a managed provider will be before they tell you the same thing I did :-).

      I've managed database servers for the better part of the last 10-15 years and in that time, I've worked with clients ranging from 512MB of RAM on a VPS up to most recently, 1-2TB of RAM (yeah, huge amounts!). It's actually pretty hard to run MySQL and other services in low-RAM environments. It's a constant tweak and tune scenario.

      In a low-RAM environment, you're not just tweaking MySQL, you're tweaking Apache/NGINX, PHP, and your application.

      ...

      All aspects of your VPS require RAM -- Apache/NGINX, PHP, MySQL, etc -- and in a low-RAM environment, they're sort of fighting for it. When it becomes a struggle, services fail.

      Upgrading to 1GB should fix that for you though.

      • It looks like I am able to upgrade as a test and rollback if it fails ..
        I suppose very strongly you are right - of course you are. Wonder what would the small instance be good for.
        And if the mysql is then happy maybe I can run a number of websites making it more economical in that way. Thanks for the help.

        • @landed

          As long as you scale just the CPU & RAM, you can fall back to a smaller instance if you need to. If you scale CPU, RAM, and Disk, you won't be able to downgrade.

          The smaller instances are ideal for those separating and clustering, as well as for testing. Static sites that don't require a database are also well-suited for 512MB since you're only running a web server and perhaps PHP/NodeJS/Python.

          For example, if I wanted to create a mini cluster for testing or proof of concept of a configuration, I'd probably use the 512MB Droplets.

          I'd put NGINX/PHP-FPM or Apache/PHP on one server and MySQL/MariaDB on another, then connect over private networking. In this scenario, I don't need to run MySQL/MariaDB on the main web server and it gets its own dedicated resources that aren't shared with other services.

          It's a little more hands on and a manual setup, but that's a basic example of what we could use them for :-).

With the memory issue and the database quits - does this damage the data?

Have another answer? Share your knowledge.