News:

CRITICAL UPGRADE CPG RELEASE 1.6.30
Version 1.6.30 removes a serious security issue that could allow unauthorized access to modify your site. Upgrade as soon as possible.

Main Menu

Coppermine db overloading host's server

Started by phil1011, June 05, 2026, 04:45:59 AM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

phil1011

I have over 70,000 images and hundreds of albums. Chronic problem with my host has been that the overloaded db has been impacting the host's server. So they disabled my coppermine db. Cannot access anything anymore.

They want me to switch to VPS hosting. Do you think their cheapest VPS plan would resolve my problem? Or if you have any suggestions for a solution, let me know.
https://www.pair.com/vps-hosting/

ron4mac

A few years ago, I suggested to you that you try a theme caching plugin. If your site is mainly public facing (users don't log in), the plugin will greatly reduce total database access. Many gallery pages are cached and the database is only accessed for them at whatever interval you set.

If caching doesn't work for you ... yes, their cheapest VPS plan should provide all you would need.

phil1011

Host informed me that most of the requests are from bots. They recommended Cloudflare so I've signed up and changed the nameservers. It should reduce unwanted traffic. Host re-enabled the coppermine db. Hopefully this will work in the long term.

Joe Carver

In addition to what ron4mac has suggested and what you have done so far, turninig off logging detailed Hit Statistics will take a large load off of your DB. Bot/crawler traffic has been hugely inreasing as of late.

mdafun

I can confirm this major issue; it's probably due to AI bots, that in the last year are incredible increasing.

Although I've installed the Theme Cache (theme_cache) plugin: v1.1 and restricted the display of full-size photos to registered users only, I'm still getting the following errors:
Fri, 06 Nov 26 22:23:45 +0100 – Unable to connect to database: 1226: User 'XYZ' has exceeded the 'max_user_connections' resource (current value: 9)

This is becoming a huge problem because it brings the whole site to a standstill. I've actually had to buy a second database just for the three Coppermine galleries I have on the site, but I remain convinced that there's some sort of vulnerability through which attackers or bots are managing to run queries even without being logged in.
Otherwise, there's no explanation for the problem – the album views are all cached?!?

mdafun

I've also tried making countless changes to the code using ChatGPT (searches restricted to registered users, other changes to the .htaccess file) but without resolving anything.

ron4mac

Make sure with the theme_cache plugin that you have activated it for your theme by creating a writeable 'cache' folder in your theme directory.
Can you provide any links?

406man

I appreciate the site has gone off line but if there's a way of showing us its home page we may be able to suggest some changes, in a similar way to the dialogue for this site a few years back:

https://coppermine-gallery.com/forum/index.php/topic,80732.msg390374.html

phill104

I would also suggest comparing your file system with a vanilla copy of coppermind just in case you cave been compromised somehow.
It is a mistake to think you can solve any major problems just with potatoes.

mdafun

https://www.funivie.org/cantieri/
https://www.funivie.org/funigallery/
the cache is enabled, folder is writable and I can see the html files, so it works.

But every so often – not every day – the script is inundated with requests that overload the database. This happens every 3–4 days.
Files are all updated

ron4mac

Does aruba.it allow you to have more than one database user? If it does, you might try having one user for Wordpress access and a different one for CPG access. Since aruba.it has a strict cap of 9 concurrent connections by the same user, it wouldn't take too much for bots hitting both the Wordpress and CPG pages to occasionally run into that limit. Regardless of the caching plugin, every CPG access requires at least one database access. You might look into whether there is a Wordpress cache that is more total, requiring no database access. Failing all else, you might have to consider a more robust hosting plan.

406man

It's useful to be able to see the web site. The lower part of the gallery home page has a large table containing the album categories :  https://www.funivie.org/funigallery/

For example, "Revolving cableway system"s and "automatic hooking ropeway systems".  Creating this table with the album and picture totals requires a very large number of database accesses and will be contributing to the high server load. I suggest temporarily disabling the display of this table to see how much the load decreases. If it makes a worthwhile difference you have confirmed that there will be an improvement when implementing a lower impact alternative. Or maybe it will make little difference.

mdafun

Quote from: ron4mac on November 17, 2026, 03:51:52 AMDoes aruba.it allow you to have more than one database user?
To inspect and try to solve, I used another database, with a different username, for galleries, independent from the rest of wordpress/vbulletin.
In this database there are the 3 galleries of coppermine; of course, here too, three galleries can make multiple simultaneous calls to the database, but as far as I understand, the calls are queued; according to the technicians, some calls remain open and are not completed. Otherwise, it wouldn't be possible to have more than 10 users in the gallery at the same time, would it?

Quote from: 406man on November 17, 2026, 09:09:03 AMFor example, "Revolving cableway system"s and "automatic hooking ropeway systems".  Creating this table with the album and picture totals requires a very large number of database accesses and will be contributing to the high server load.
Do you intend the list of categories? Otherwise, how am I supposed to grant access to all the albums? But shouldn't all this be cached?

ron4mac

This from Claude:

What the error means

MySQL/MariaDB's max_user_connections caps how many simultaneous connections a single account can hold. Your limit is 9, and the account XYZ hit it. On shared hosting this limit is usually set per MySQL user, so:

If every database uses the same user XYZ, all your sites and scripts share one pool of 9 connections.
If each database had its own user, each would get its own pool of 9.

Why separate users might help

If you run several sites or apps (say, multiple WordPress installs) all connecting as XYZ, they compete for the same 9 slots. A traffic spike or a few slow queries on one site can starve the others. Separate users would isolate them.

The catch with Aruba

On many Aruba shared plans, the database user is tied to the database itself. You create a database and get a username (often the same as the database name) automatically, and you may not be able to create extra users or assign one user to several databases. If your control panel only lets you create databases with auto-assigned credentials, you may already effectively have one user per database. In that case the limit is being hit by a single site or script, and splitting users won't fix it.

Things worth checking first

Does XYZ serve multiple databases or sites? If so, separating them is a reasonable fix, if the panel allows it.
Persistent connections. If your code uses mysqli_pconnect or PDO with PDO::ATTR_PERSISTENT, connections stay open and pile up. Switch to normal connections.
Unclosed connections or slow queries. Scripts that never close connections, or queries that run long, hold slots open. Cron jobs and bots hitting the site can also add up.
Plugins. In WordPress, heavy plugins, backup jobs, or security scanners running at the same time can easily consume 9 connections.
Caching. A page cache (e.g., WP Super Cache, LiteSpeed Cache if available) cuts the number of DB hits dramatically.
Traffic spikes or bots. Check your access logs for crawlers or abusive requests.

Practical next steps

Look in the Aruba control panel under the MySQL/database section to see whether you can create additional users or assign them per database.
If you can't, ask Aruba support whether the limit can be raised or whether a plan upgrade has a higher max_user_connections. A 9-connection cap is quite tight for anything beyond a small site.
Meanwhile, reduce load with caching and by eliminating persistent connections.

mdafun

With SHOW VARIABLES LIKE 'max_user_connections';
the result is
max_user_connections 100

ron4mac

Quote from: mdafun on <strong>Yesterday</strong> at 06:55:39 PMWith SHOW VARIABLES LIKE 'max_user_connections';
the result is
max_user_connections 100
Why the error that you are showing above then?  ???

mdafun

That's why I'm saying there are clearly other issues with CPG. Next time this happens, I'll post the database log.

I've also restricted access to search.php to registered users only (a setting that should be added to the settings page on the next releases), even though we know full well that searches can also be carried out via thumbnails.php (which is now restricted to registered, but has always been open to guests for 20 years without any problems).
All these issues have arisen over the last year.

on search.php
define('IN_COPPERMINE', true);
define('SEARCH_PHP', true);

require('include/init.inc.php');

[b]//LIMIT TO REGISTERED
if (!USER_ID) {
    cpg_die(ERROR, 'La ricerca è riservata agli utenti registrati.', __FILE__, __LINE__);
}
//END ADD[/b]

if (!USER_ID && $CONFIG['allow_unlogged_access'] == 0) {
    $redirect = 'login.php';
    header("Location: $redirect");
    exit();
}

phill104

As I suggested above, check your file systems for any files that should not be there.

Then check your servers access logs and see if you can detect where all these just are being generated from. Often the servers access and error logs can be very revealing in these cases.
It is a mistake to think you can solve any major problems just with potatoes.