stuttter / ludicrousdb Goto Github PK

Installed LudicriousDB via Composer in the mu-plugins directory, moved the drop-in files into place, and setup my configuration. When I go to look at my test site I see a PHP Fatal Error:

Fatal error: Uncaught Error: Call to a member function get() on null in /var/www/spiritedmedia.dev/htdocs/wp-content/object-cache.php:129 Stack trace: #0 /var/www/spiritedmedia.dev/htdocs/wp-content/mu-plugins/ludicrousdb/ludicrousdb/includes/class-ludicrousdb.php(1931): wp_cache_get('localhost:3306', 'ludicrousdb') #1 /var/www/spiritedmedia.dev/htdocs/wp-content/mu-plugins/ludicrousdb/ludicrousdb/includes/class-ludicrousdb.php(1662): LudicrousDB->tcp_cache_get('localhost:3306') #2 /var/www/spiritedmedia.dev/htdocs/wp-content/mu-plugins/ludicrousdb/ludicrousdb/includes/class-ludicrousdb.php(638): LudicrousDB->check_tcp_responsiveness('localhost', 3306, 0.2) #3 /var/www/spiritedmedia.dev/htdocs/wp-content/mu-plugins/ludicrousdb/ludicrousdb/includes/class-ludicrousdb.php(1266): LudicrousDB->db_connect('SELECT meta_val...') #4 /var/www/spiritedmedia.dev/htdocs/wp-includes/wp-db.php(2488): LudicrousDB->query('SELECT meta_val...') #5 /var/www/spiritedmedia.dev/htdocs/wp-content/object-cache.php(1196): wpdb->get_results('SELECT in /var/www/spiritedmedia.dev/htdocs/wp-content/object-cache.php on line 129

We're using the latest version of WP Redis for our object caching.

It looks like the global $wp_object_cache isn't set, aka its null when LudicrousDB tries to get something from the cache.

I've not encountered this yet, but am curious if this will be an issue. Some places in our PHP code, plugins INSERT a new WP post or row to custom table, receive its ID, and then immediately do a SELECT on the next line where it builds an object/array.

My question is: if we have a slave configured in ludicrousdb, the insert will go to the master/writer, but will ludicrousdb realize it needs to perform the SELECT on the master, or will it immediately perform that SELECT query on the slave? (in which case it might not be there)

Our typical replication lag is 20 milliseconds, and I'm aware of configuring the lag awareness but I don't think that addresses this specific situation. That would just exclude the slave if it gets too far lagged. But my concern is querying a row that was just inserted one PHP line earlier. Would that be create a situation where the SELECT returns null instead of the newly created row?

I immediately got a fatal error on line 1033:

wp_die( $dbh . ' ' . $charset . ' ' . $collate );

It's trying to convert $dbh into a string, which it can't because $dbh is an object of type mysqli.

I didn't have a collate set, so that's why this line is being executed, but I still thought I would let you know.

On a sidenote, why do I need to set a collation in wp-config? HyperDB or Wordpress don't need it set, in fact, this code doesn't do anything with the $collate variable as it is skipped on line 1041. If, in the case of no mysqli it does get to the final part of the else statement (on line 1046) it still has a check to see if $collate is empty or not.

So I'm suggesting a change for line 1032 to read just if ( empty( $charset ) ) {

Or did I forget about something?

Hi guys,

I use ludicrousdb in several sites and never had an issue. All of a sudden I'm getting a strange behavior in a bunch of WP sites. I can't login. I got to wp-admin, type the correct user/pass and the get redirected to the login page.

Since there is nothing on error logs (I even enabled debug on some sites), I started testing and I figured out the problem is with the slave servers on db-config.php

When I comment out the slave servers everything is back to normal. The second I re-enable the slaves, no login.

Any ideas what could cause the issue or a way to get some debug info?

Hi there,

I have attempted to implement master slave and have no idea if it is working. Any help would be much appreciated.

Regards,

Stephen

Composer (via Packagist) installs ludicrousdb in

wp-content/mu-plugins/ludicrousdb

WordPress, however, doesn't load must use plugins that are in subdirectories.

Is it possible to update the composer package so it installs ludicrousdb directly in wp-content/mu-plugins instead?

My editors are getting this whenever they work on the WP backend when the db.php is enabled in /wp-content/.

This problems stops when I disable db.php.

Any ideas on how to resolve this? I'm on PHP 7.0.13 and Wordpress 4.6.1

In class-ludicrousdb.php in function check_connection() lines 1115-1132 there is code to retry making a database connection several times. On line 1123 there is the code that actually retries making a connection:

if ( $this->db_connect( false ) ) {
  if ( $error_reporting ) {
    error_reporting( $error_reporting );
  }

  return true;
}

However, looking at the first few lines of the function db_connect, it is clear that this thing is always going to return false and a connection will never be retried:

public function db_connect( $query = '' ) {
  // Bail if empty query
  if ( empty( $query ) ) {
    return false;
  }
  ...

The confusion probably stems from the fact that the original db_connect function from wp-db.php is defined as follows:
public function db_connect( $allow_bail = true ) {
This function does allow 'false' to be passed in. In fact, it looks like most of the check_connection() function was copied from wp-db.php, but not modified to work with LudicrousDB's db_connect() function.

I stepped through the code and see it not working correctly, I'm just wondering if I overlooked something? Please let me know if I can help out in any way.

So I have set up DB replication between a remote master server and a local slave server in the hopes that the site will be faster to load and mess around with locally. I have verified that the connection is working. However, when using LudicrousDB, the pages take 8-10 seconds "waiting for localhost" every time I navigate to a page, which is really unworkable.

My db-config.php looks like this:

//MASTER
$wpdb->add_database( array(
  'host'     => DB_HOST,
  'user'     => DB_USER,
  'password' => DB_PASSWORD,
  'name'     => DB_NAME,
  'write' => 1,
  'read'  => 0,
) );

//SLAVE
$wpdb->add_database( array(
  'host'     => DB_SLAVE,
  'user'     => DB_USER,
  'password' => DB_PASSWORD,
  'name'     => DB_NAME,
  'write'    => 0,
  'read'     => 1,
) );

And my wp-config.php looks like this:

define('DB_NAME', 'artlytic_example');

/** MySQL database username */
define('DB_USER', 'artlytic_exampleslave');

/** MySQL database password */
define('DB_PASSWORD', 'examplepassword');

/** MySQL hostname */
define('DB_HOST', 'XXX.XXX.XXX.XX');  // Actual IP here
define('DB_SLAVE', 'exampleserver.local:8889');

/** Database Charset to use in creating database tables. */
define('DB_CHARSET', 'utf8mb4');

/** The Database Collate type. Don't change this if in doubt. */
define('DB_COLLATE', '');

When I replace the DB_HOST server host with my local slave server, I can see in my MAMP's log that tons of queries are going on in the slave; however, in the setup above, I only see UPDATE or INSERTS for an _edit_lock meta_key in my wp_postmeta table (which must be from master writing those values to the slave). I also tried to set the 'read' on my master to 3 but it didn't change anything.

What is going wrong?

In WP Spider Cache, I went the route of including the actual drop-in files in a drop-ins directory.

This made it easier for developers of the plugin to separate out classes, functions, and configurations, and also made it a bit more obvious which files are WordPress drop-ins.

It also would allow for Ludicrous DB to live in its own directory, and for the drop-ins to be required instead. I think this is cleaner, but would like to get some feedback before making these changes.

Thoughts?

Getting the following notice errors.

Notice: Undefined variable: write in /var/www/wp-content/dropins/ludicrousdb/db.php on line 675

Notice: Undefined variable: write in /var/www/wp-content/dropins/ludicrousdb/db.php on line 703

On my local development server, some SQL_MODES are set globally such as NO_ZERO_DATE which is incompatible with MySQL, in fact that mode breaks the creation of new posts in Wordpress.

The default wp-db.php file checks for the currently set modes, strips the ones that are incompatible and then sets the new mode on line 861:
mysqli_query( $this->dbh, "SET SESSION sql_mode='$modes_str'" );

Ludicrousdb has the same set_sql_mode function on line 894, but it has a dbh_type_check() function which causes the set_sql_mode function to return when mysqli is being used! It then no longer removes the incompatible modes thus causing the incompatible sql modes to remain set, breaking Wordpress in some cases:

if ( $this->dbh_type_check( $dbh ) ) {
return;
}

Simply removing the above 3 lines fix the whole problem. Why was this check added to just quit if mysqli is detected?

We have this great query override:

apply_filters_ref_array

BUT, the code is set to return the data from the override:

		$return_val = apply_filters_ref_array( 'pre_query', array( null, $query, &$this ) );
		if ( null !== $return_val ) {
			return $return_val;
		}

BUT if we will look at wp-db->query()
if ( $query ) {
$this->query( $query );
} else {
return null;
}

The data from the query is NOT set to the data properties

We need to change it to be
$return_val = apply_filters_ref_array( 'pre_query', array( null, $query, &$this ) );
if ( null !== $return_val ) {
return $this->last_result=$return_val;
}

My WordPress installation is running with PHP 7 which uses mysqli instead of mysql. When using ludicrousdb I am getting 500 errors when trying to access my WordPress site.

Hi there,

I'm try to setup db partitioning for multisite with 15 child sites. Currently I'm config as Sample Configuration 2: Partitioning and it work. All child site tables are stored on blogdb. Now I want config 5 child sites on a db. Eg,

• Site id 2-5 stored on db name call child_db_2_5
• Site id 6-10 stored on db name call child_db_6_10
• Site id 11-15 stored on db name call child_db_11_15

How can I do that?

Thanks for your help,
Regards,

Hi,

pretty sure this is not a ludicrousdb issue per se - but I'm hoping for a bit of guidance/input.

We're working on a multisite install that pre-dates the WP utf8mb4 upgrade (tho is running latest version) - WP core didn't process the upgrade on our db because it's version is so old - original install dates back to 2012!. Now trying to switch from hyperdb to ludicrousdb to add utf8mb4 support (people really want emoji's it seems).

We upgraded the underlying db tables to new format (obv repeated for all tables)...

ALTER DATABASE WORDPRESS_DEV CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE table_names CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Modified wp-config.php db format...

define('DB_CHARSET', 'utf8mb4');
define('DB_COLLATE', 'utf8mb4_unicode_ci')

All good so far. Finally dropped in + configured ludicrous files, with the following...

$wpdb->charset = 'utf8mb4';
$wpdb->collate = 'utf8mb4_unicode_ci';

This gives us an empty post editor - content is present in db obviously. If we change ludicrous setting to use just utf8 charset....

$wpdb->charset = 'utf8';
$wpdb->collate = 'utf8mb4_unicode_ci';

Post editor content is now back.

The underlying table where the content is being drawn from definitely is set to utf8mb4 charset, so not entirely sure what's going on here - is it safe to leave it as utf8 format - or will that lead to issues down the line? What could cause empty post editor when using utf8mb4? Mysql server version is MySQL 5.6.34 but we are running slightly older amazon linux AMI's which are running PHP5.6 and the following php-mysql client...

# php -v
PHP 5.6.30 (cli) (built: Jan 19 2017 22:50:24) 

# php --ri mysql | grep Client
Client API version => 5.1.73

I don't think there is any issue here tho - PHP5.5.3+ supports utf8mb4.

Not sure where to turn next - any input greatly appreciated.

hi guys,

getting a little confused with installation. Is this to be installed like a regular plugin or does it replace wp-config.php file?

Cheers,

Stephen

Hi,

I am setting up a very large E-commerce store with 100s of millions of different product variations. Can I use this plugin to divide the wp_postmeta table across multiple servers?

Any help is much appreciated.

Wyatt

We recently upgraded to PHP 7.3 and receive this error

Deprecated: stripos(): Non-string needles will be interpreted as strings in the future. Use an explicit chr() call to preserve the current behavior in /ludicrousdb/includes/class-ludicrousdb.php

Do you support the latest version or have any advice on this error?

Any word on compatibility with WP 4.8?

Thanks!

Google recommends using Google Cloud SQL proxy, which creates a local UNIX socket, to connect to its managed SQL service. These sockets are generated in the format:

project:region:instance

So you end up with a local file like:

/run/cloud_sql_proxy/my-project:us-central1:my-instance

Using this local file in db-config.php like:

'host' => 'localhost:/run/cloud_sql_proxy/my-project:us-central1:my-instance

Generates thousands of global__r errors in the debug.log and basically breaks everything. I think the colons in the local file aren't being escaped / interpreted correctly and that makes the database unreachable.

Hello! There is in db-config.php:

// Multisite blog tables are "{$base_prefix}{$blog_id}_*" function my_db_callback( $query, $wpdb ) { if ( preg_match("/^{$wpdb->base_prefix}\d+_/i", $wpdb->table) ) { return 'blog'; } }

So, this fragment for WPMU must look like this?

function my_db_callback( $query, $wpdb ) { if ( preg_match("/^{$base_prefix}{$blog_id}_\d+_/i", $wpdb->table) ) { return 'blog'; } }

Hi, is there any way you could help me devise a very basic configuration for partitioning? Basically, I have a Wordpress users table (k2bi_users) on one database (1757) and then I have all the other 70 or so Wordpress tables (k2bi_xxx) on another database (1893). How can I set up the configuration so that it calls on the 1757 database for the users table and the other database for all the other tables?

I know it's a strange setup, and it may sound like I'm an idiot, but this is exactly what I need. Nothing else. The reason being I have a unique user registration system that can only exist on an external database, but the connection is extremely slow. That is why I want all my other tables to be on the internal database.

Thanks so much for the help! I'll be eternally grateful to figure this out 👍 .

I'm occasionally seeing this in my error logs:

PHP message: PHP Warning:  mysqli_num_fields() expects parameter 1 to be mysqli_result, boolean given in /srv/www/content/db.php on line 992

Been testing ludicrousdb after we were having some problems with hyperdb. Overall ludicrousdb has done better except there is one problem. The site is extremely slow and the PHP 7 slow log points to ludicrousdb.

Here is a sample of the PHP 7 slow log.

[24-Mar-2017 19:24:13] [pool websrv] pid 25139
script_filename = /srv/users/websrv/apps/websrv/public/index.php
[0x00007f1874414450] mysqli_query() /srv/users/websrv/apps/websrv/public/wp-content/mu-plugins/ludicrousdb/ludicrousdb/includes/class-ludicrousdb.php:1273

Any ideas?

This goes back all the way to HyperDB, so I know it wasn't you but are you able to explain these lines?

$this->dataset is never used and $dataset can never be empty :/

https://github.com/stuttter/ludicrousdb/blob/master/ludicrousdb/includes/class-ludicrousdb.php#L353-L361

The close method was added here . This brings ludicrousdb into line with changes in core. However, the close method is a copy and paste from what is in core in 4.5. Meaning it is overriding the close method with the same code. This is fine, but if the logic is exactly the same as core, it should be removed, as if you remove it, then it will just use the core method. This means if any changed happen in core, they will be reflected.

I'm running wordpress with php-fpm 7.2 and two mysql 5.7 servers, one as master one as slave. If I connect to either db directly pages load at least 3 seconds faster than if I use ludicrousdb. I have the master set with a read priority of 2 and write 1, slave is read 1 and write 0. Any known issues that might cause this? If not, how can I go about troubleshooting what's causing the overhead?

hi there,

does this work nicely with maria db?

thanks

Could you please tag/release versions when a new change/feature come in?

Thanks :)

https://blog.ircmaxell.com/2017/10/disclosure-wordpress-wpdb-sql-injection-technical.html instructs us to update both WP and ludicrousdb, but I don't see any recent updates.

I'm having a similar issue to #45 .

When I have a slave/master configuration set up using LudicrousDB, I have to login over & over again. It's almost like it's requiring me to re-authenticate every time it switches to a different database. Sometimes it will simply redirect me to the login page again after I successfully login; sometimes it actually logs me in, but then after I move to another page in the admin area, it requires me to login again.

When I remove LudicrousDB, it stops requiring me to login over & over again.

The standard wpdb->determine_charset method passes over the first if and proceeds to set charset / collation to utf8mb4 / utf8mb4_unicode_520_ci for an input of utf8 / ''.

This is happening because the default wpdb constructor sets a connection at the end while ludicrous does it after the ini_charset was called (last line in its constructor) somewhere in the query method.

Normally this is not a problem, but for clients not setting the exact precise charset/collation combo in their respective constants will see change in post conditions.

This is obvious if you try to run the Wordpress test suite with ludicrous inside, along with their default config file.

Ludicrous init_charset called here, dbh undefined at this time

This works well for what I am trying to accomplish with it. However, it would be optimal if this wasn't in the plugins directory. I do not want clients I will be hosting to even see this in their list of plugins. The best I can do with out altering the code is place read-only permissions, so it can't be deleted.

Is there a simple solution for this to be installed and used, that doesn't require it to be installed as a plugin?

Coming from this line: $this->last_connection = compact( 'dbhname', 'host', 'port', 'user', 'name', 'tcp', 'elapsed', 'success', 'queries', 'lag' );

The tcp cache functions set / get were converted to use wp_cache_ api. On the face of it, this is a good idea. However, database drop-ins are loaded before object-cache drop-in. This means that possibly have an a race condition where wp_cache_ is called before it exists.

I have a database server specified in wp-config.php, and I've added that in db-config.php along with another master replica.

If I disable ludicrousdb, this particular plugin (WP Statistics) works normally. If I enable ludicrousdb the plugin complains that certain DB table don't exist (they clearly do).

This is the error being generated in wp_debug.log

WordPress database error Unknown column 'Tables_in_' in 'where clause' for query SHOW TABLES WHERE Tables_in_ = 'wp_statistics_visitor' OR Tables_in_ = 'wp_statistics_visit' OR Tables_in_ = 'wp_statistics_exclusions' OR Tables_in_ = 'wp_statistics_historical' OR Tables_in_ = 'wp_statistics_pages' OR Tables_in_ = 'wp_statistics_useronline' OR Tables_in_ = 'wp_statistics_search' made by do_action('toplevel_page_wps_overview_page'), WP_Hook->do_action, WP_Hook->apply_filters, WP_Statistics_Admin_Pages::log, wpdb->print_error

I'm not sure why?

Hi @JJJ @spacedmonkey,

It looks like ludicrousdb isn't routing SELECT ... FOR UPDATE to the master db, because if it runs on a slave (which is set to readonly on purpose), the query fails as follows:

[31-Jan-2018 13:32:15 UTC] WordPress database error The MySQL server is running with the --read-only option so it cannot execute this statement for query SELECT * FROM BLA WHERE object_id = 1234 AND type = 'FOO' FOR UPDATE; made by ...

The SELECT logic in ldb probably thinks it's OK to send all queries to slaves, which doesn't seem to be the case.

I might be completely missing something here. If I take the DB server specified in wp-config.php offline, I get the dreaded 'Error establishing a database connection' message. I thought that one of the other servers defined in db-config.php would kick in?

I've set the parameters, and I'm not seeing any changes on my server logs. I even set intentionally broken perameters and nothing changes.

Edit: I noticed that the files are looking for the "ludicrousdb" folder but it doesn't say to switch the master folder to "ludicrousdb" and there aren't any error codes for it either.

It's working after switching that.

Hello,

Please find below configuration snnipet of db.php file.how to check write query goes to rds maria master database and read query goes to rds read replica.

$wpdb->add_database(array(
'host' => 'masterdatabase', // If port is other than 3306, use host:port.
'user' => 'xxxxx',
'password' => 'xxxxxx',
'name' => 'xxxxxx',
'write' => 1, // master server takes write queries
'read' => 0, // ... and read queries
));

/**

This adds the same server again, only this time it is configured as a slave.
The last three parameters are set to the defaults but are shown for clarity.
*/
$wpdb->add_database(array(
'host' => 'readreplicadbatabase', // If port is other than 3306, use host:port.
'user' => 'xxxxx',
'password' => 'xxxxxx',
'name' => 'xxxxx',
'write' => 0,
'read' => 1,
'dataset' => 'global',
'timeout' => 0.2,
));
It would be greatful if you suggest any changes to achieve this.

Is php7 support something you're looking into for a future build?

So far, this plugin is amazing!

What would be the optimal way to have a database for each site when using WordPress Multisite?

We tried the following, but it seems like $wpdb is NULL until later.

$wpdb->add_database( array(
	'host'     => DB_HOST,
	'user'     => DB_USER,
	'password' => DB_PASSWORD,
	'name'     => DB_NAME,
) );

$wpdb->add_database( array(
	'host'     => DB_HOST,
	'user'     => DB_USER,
	'password' => DB_PASSWORD,
	'name'     => DB_NAME.'_'.$wpdb->$blog_id,
	'dataset'  => 'blog',
) );

$wpdb->add_callback( 'mwmu_callback' );

// Multisite blog tables are "{$base_prefix}{$blog_id}_*"
function mwmu_callback( $query, $wpdb ) {
	if ( preg_match("/^{$wpdb->base_prefix}\d+_/i", $wpdb->table) ) {
		return 'blog';
	}
}

We wanna apply a dynamic rule that assumes DB_NAME.'_'.$wpdb->$blog_id always exists. That way, all we gotta do is create the database before creating the site every time.

Thank you for any feedback on this use! :)

Are thereany plans to support the pdo database driver?

Is it possible to load the read replica settings just for WP JSON / WooCommerce API requests? My plan is that all read requests from API calls go to the read replica, everything else should go to the master db.

I thought it should work like this:

if( ! empty( DATABASE_HOST_RR ) && ( ( defined( 'REST_REQUEST' ) && REST_REQUEST ) || ( defined( 'WC_API_REQUEST' ) && WC_API_REQUEST ) || isset( $_GET['wc-api'] ) ) ) {

$wpdb->add_database( array(
	'host'     => DATABASE_HOST_RR,
	'user'     => DB_USER,
	'password' => DB_PASSWORD,
	'name'     => DB_NAME,
	'write'    => 0,
	'read'     => 1,
	'dataset'  => 'global',
	'timeout'  => 0.2,
) );	

}

But I found out that the constant REST_REQUEST is not set when the ludicrousdb config is loaded.

Maybe the solution is to load the read replica settings via a custom plugin later (not in db-config.php file)?

ludicrousdb runs check_connection for each & every query, triggering a mysqli_ping. This is a complete waste of resources & slows down things unnecessarily.

Suggestion to change:
Scenario 1: default - fastest
run the connection check once and if connection is up, wp_cache_set the $dbh->thread_id in check_connection as a temporary/non-permanent group, so we will not mysqli_ping within this page load for the same connection.

Scenario 2: no writes must get lost - slower (depends on number of writes & caching of reads in object cache, but more secure); e.g. via constant
same as 1, except we check that if $operation === write we will still mysqli_ping to make sure no writes get lost even when the db server crashes while the page loads
this also only makes sense if you have a master-master setup where another master can accept the writes if one master goes down.

Scenario 3: no writes/reads must get lost - we always want to failover/check if connection is still up - slowest
this is what is currently implemented.

Note: by default object cache is only non-persistent, so the group we use won't matter.
For people using object caches however, they would need to add our group to the ignored groups.
Thus, if we don't want this, alternatively, we could just create a global variable and add the thread_ids to it (& check it) instead of using wp_cache

Since WordPress 4.6, functions.php is now required early in wp-settings.php.

This allows us to remove the get_caller() method and rely only on wp_debug_backtrace_summary() going forward.

Hi,

We've used ludicrousdb for years, with 'timeout' => 0.2 for master and slaves, but whenever the master db goes down and times out (as in, host is down rather than host up and mysql down, which would refuse the connection instantly), the site pretty much goes down because all write and read connections get stuck waiting for the server to come back up rather fail within 0.2s.

Any idea what we're doing wrong?

hey, might seem like a dumb question, but I'm trying to set a custom prefix on the tables because I need to run multiple wordpress install from the same database. usually this can be changed using

$table_prefix = 'ie_';

but it does not seem to work with the plugin enabled, is there a specific place this needs to go fo rit to work? (I have it in the wp-config.php)

and also, will this plugin respect:
define('CUSTOM_USER_TABLE', 'wn_users');
define('CUSTOM_USERMETA_TABLE', 'wn_usermeta');

thanks!

Hello,

I am trying to figure out how can I put code of Network topology / Datacenter awareness.

Will you please provide me a code example code of Network topology / Datacenter awareness?