Connect via WordPress "wpdb" to another database vs "mysqli_connect"

[dbareis]

Hi, I'm currently using a second "wpdb" object to connect to a MariaDB database.  Google gemini says this is not recommended because:

• Singleton Pattern: The wpdb object is designed to be a singleton, meaning there's only one instance of it per request. Creating a second instance can disrupt this pattern and lead to unexpected results.
• Resource Management: WordPress's built-in resource management system is optimized for a single wpdb instance. Creating a second instance can complicate resource management and potentially lead to issues like connection timeouts or memory leaks.
• Compatibility: WordPress's core functionality and many plugins and themes rely on the wpdb object. Introducing a second instance can interfere with these dependencies and cause compatibility problems.

I don't know what a singleton is so is what Gemini said true? I've seen many examples where they recommend a second wpdb object (in a second variable).

If it is OK, do I need to close this second connection myself?  If so what is the best way?  I'm assuming the open is an expensive operation. 

If I need to use "mysqli_connect", same question about opening/closing.

I'm looking at my use of "wpdb" because of these PHP error messages (and 500 & 503 server responses):

1. [14-Oct-2024 02:56:44 UTC] PHP Warning:  mysqli_real_connect(): (HY000/1203): User xxxusernamexxx already has more than 'max_user_connections' active connections in /home/yyyy/public_html/wp-includes/class-wpdb.php on line 1982
2. [14-Oct-2024 03:30:30 UTC] PHP Warning:  mysqli_real_connect(): (HY000/2006): MySQL server has gone away in /home/yyyy/public_html/wp-includes/class-wpdb.php on line 198

Now the website is low on resources and as a NFP organisation (which I volunteer at), getting it better resourced is problematic.  So the above could be due to lack of RAM.  But when it goes bad it is bad for a long time.  I suspect the timing of web crawlers is the main cause if load is the issue and I have have to turn them all off in "robots.txt"...

Thanks for any help :-)

[Barand]

If both databases are on the same server then you only need a single connection (A connection is made to the server, not a specific database. The database name in the connect function is just the default to use).

This allows you you access two databases in a single query. Specify the database.tablename when referencing the tables.

Suppose you want to copy tableA from DB1 to DB2...
 

CREATE TABLE DB2.tableA LIKE DB1.tableA;

INSERT INTO DB2.tableA SELECT * FROM DB1.tableA;

If DB1 is the default, it can be omitted EG

INSERT INTO DB2.tableA SELECT * FROM tableA;

 

[gizmola]

A singleton is an OOP design pattern.  The design of a singleton is such that there will only be ONE (a single) object of the singleton class.   Objects of a singleton class are made using a static method.

The wordpress wpdb class source code is here.  It does NOT implement a singleton pattern.  

The $wpdb global object however, is intended to be used throughout Wordpress and by any plugins.  

So you CAN create new objects of class wpdb, so long as you understand what that requires.

It is not clear from your question why you need a 2nd database connection.

As for your shared host, you should investigate the # of database connections they have configured.  

In phpMyAdmin you can use a query like this to see how many connections your hosting company has configured.

SHOW VARIABLES LIKE 'max_connections';

The default value is 151.  

I have heard of certain shared hosting companies setting this value arbitrarily low.  If you want to report back to us what you find, we can weigh in on the reasonableness of the limit.  In many cases you can open a ticket with the hosting company to have them increase the mysql parameter that controls this limit.  

 

As for closing a database connection, no you don't need to worry about closing it.  PHP has page/script scope, so as soon as the page is complete, PHP already disposes of all variables and releases any resources.  

If you have a small amount of code that makes a few queries and you are absolutely sure that you are done with the connection you made, then it doesn't hurt to do something like this:

 

$my_wpdb = new wpdb(....);

// make queries etc.

$my_wpdb->close();
unset($my_wpdb);

 

But it really is a micro-optimization that is not worth doing.