[4.x]: DB Replica

[DenyEs]

What happened?

Description

I am trying to set up DB replica on one of our websites and I am not having much luck. It used to work fine on Craft 3 but after an upgrade to Craft 4, I am getting memory exhausted error and the website won't load at all.

PHP memory_limit is set to 512M but no matter what I change it to, it has no effect on the issues I am having.

This is my app.php config

<?php

use craft\helpers\App;

return [
    'modules' => [
        'my-module' => \modules\Module::class,
    ],
    'components' => [
        'cache' => craft\cache\DbCache::class,
        'session' => function() {
            // Get the default component config
            $config = craft\helpers\App::sessionConfig();

            // Override the class to use DB session class
            $config['class'] = yii\web\DbSession::class;

            // Set the session table name
            $config['sessionTable'] = craft\db\Table::PHPSESSIONS;

            // Instantiate and return it
            return Craft::createObject($config);
        },
        'queue' => [
            'ttr' => 900
        ],
        'db' => function() {
            // Get the default component config (using values in `db.php`):
            $config = App::dbConfig();

            // Define the default config for replica connections:
            $config['replicaConfig'] = [
                'username' => App::env('DB_REPLICA_USER'),
                'password' => App::env('DB_REPLICA_PASSWORD'),
                'tablePrefix' => App::env('DB_TABLE_PREFIX'),
                'attributes' => [
                    // Use a smaller connection timeout
                    PDO::ATTR_TIMEOUT => 10,
                ],
                'charset' => 'utf8',
            ];

            // Define the replica connections, with unique DSNs:
            $config['replicas'] = [
                ['dsn' => App::env('DB_REPLICA_DSN_1')],
            ];

            // Instantiate and return the configuration object:
            return Craft::createObject($config);
        }
    ],
    'bootstrap' => ['my-module'],
];

Steps to reproduce

1. Use the above config on Craft 4 to configure the replica

Expected behavior

Website loads up without any issues

Actual behavior

Website is fully blank and the following error can be found in the phperrors.log file.

[05-Jul-2024 09:50:58 UTC] PHP Fatal error:  Allowed memory size of 536870912 bytes exhausted (tried to allocate 20480 bytes) in /Users/user/Documents/sites/website/vendor/yiisoft/yii2/db/Query.php on line 708
[05-Jul-2024 09:50:58 UTC] PHP Fatal error:  Allowed memory size of 536870912 bytes exhausted (tried to allocate 20480 bytes) in /Users/user/Documents/sites/website/vendor/yiisoft/yii2/web/Response.php on line 818

Craft CMS version

4.10.2

PHP version

8.3

Operating system and version

Mac OS Sonoma 14.5 as well as Ubuntu 22.04 LTS

Database type and version

MySQL 8

Image driver and version

No response

Installed plugins and versions

No response

[brandonkelly]

Just to be sure, you’re not getting the memory error if you remove the replica config settings, correct?

[DenyEs]

That's correct, if I remove the replica settings, the website works normally without any issues at all

[brandonkelly]

How high up have you tried setting the memory limit? Have you confirmed that the memory limit changes are going into effect? (You can verify by removing the replica config (so the site loads) and going to Utilities → PHP Info and checking the memory_limit value there.)

Also check config/general.php and make sure you’re not setting the phpMaxMemoryLimit config setting.

[DenyEs]

I tried 128M all the way to 1024M but the default PHP config for the server is 512M. I did make sure it changed by disabling it between changes and checking utilities section in the CMS. The memory limit change had no effect on the error

The weird thing is that it worked on Craft 3, but fails to load on Craft 4

[brandonkelly]

I’m guessing it’s due to an infinite recursion bug somewhere, that might be triggered by your replica config. It could be due to a module or plugin you have installed. Is there any chance you can send your database, Composer files, and any custom plugin/module files, over to support@craftcms.com so we can try to reproduce and look into it from there?

[DenyEs]

I can't at the moment, I would have to discuss this with the team. One thing I tried is to implement the replica settings on our testing sandbox environment, which is completely separate project without any custom plugins or modules and it comes back with the same error.

[bluestormdesign]

Hi Brendon, I'm picking this up in the absence of Denis this week. Can I send the database for the sandbox app Denis mentions above to support@craftcms.com? There are no custom plugins or modules.

[brandonkelly]

@bluestormdesign Sure, as long as the issue is reproducible, we should be able to work with that.

[angrybrad]

@bluestormdesign @DenyEs I'm not able to reproduce this. Can you:

• Comment out this line to make sure there's nothing in your module that's interfering:

  'bootstrap' => ['my-module'],

• Confirm that DB_REPLICA_DSN_1 is pointing to a different database instance than the primary/write instance defined in your config/db.php file or via your CRAFT_DB_* environment variables.

[bluestormdesign]

@bluestormdesign @DenyEs I'm not able to reproduce this. Can you:

• Comment out this line to make sure there's nothing in your module that's interfering: 'bootstrap' => ['my-module'],
• Confirm that DB_REPLICA_DSN_1 is pointing to a different database instance than the primary/write instance defined in your config/db.php file or via your CRAFT_DB_* environment variables.

I can confirm that the DB_REPLICA_DSN_1 is pointing to a different database instance. I have also commented out 'bootstrap' => ['my-module'], and can confirm that nothing in my module is interfering.

[bluestormdesign]

Running locally with a read replica same error:

[10-Jul-2024 12:04:08 UTC] PHP Fatal error: Allowed memory size of 536870912 bytes exhausted

[angrybrad]

Likely related to https://github.com/yiisoft/yii2/issues/14305 but we'll do some more digging to see if we can come up with a workaround.

[angrybrad]

@bluestormdesign Did some more testing and added a new comment on https://github.com/yiisoft/yii2/issues/14305#issuecomment-2229082265

As a workaround until the underlying issue is addressed, it looks like you won't be able to use database read/write splitting and the DbCache component at the same time.

[DenyEs]

@angrybrad Hi Brad, I tried to replace DbCache component with Redis to see if that's going to work with database read/write splitting but I am hitting a dead end with this configuration as well.

Just with Redis alone, website loads up but the second I enable replica config, I get the PHP Fatal error you can see below. One thing changed tho, now it's pointing to the different function entirely - preloadSingles()

 Fatal error: Allowed memory size of 536870912 bytes exhausted (tried to allocate 65536 bytes) in /Users/denis/Documents/sites/aoc/vendor/craftcms/cms/src/helpers/Template.php on line 397

I had a look in my general.php config and tried to both enable and disable the option but it doesn't make a difference at all.

This is my app.php file with redis and replica.

<?php

use craft\helpers\App;

return [
    'modules' => [
        'my-module' => \modules\Module::class,
    ],
    'components' => [
        'cache' => function() {
            $config = [
                'class' => yii\redis\Cache::class,
                'keyPrefix' => Craft::$app->id,
                'defaultDuration' => Craft::$app->config->general->cacheDuration,

                // Full Redis connection details:
                'redis' => [
                    'hostname' => App::env('REDIS_HOSTNAME') ?: 'localhost',
                    'port' => App::env('REDIS_PORT'),
                    'password' => App::env('REDIS_PASSWORD') ?: null,
                ],
            ];

            return Craft::createObject($config);
        },
        'session' => function() {
            // Get the default component config
            $config = craft\helpers\App::sessionConfig();

            // Override the class to use DB session class
            $config['class'] = yii\web\DbSession::class;

            // Set the session table name
            $config['sessionTable'] = craft\db\Table::PHPSESSIONS;

            // Instantiate and return it
            return Craft::createObject($config);
        },
        'queue' => [
            'ttr' => 900
        ],
        'db' => function() {
            // Get the default component config (using values in `db.php`):
            $config = App::dbConfig();

            // Define the default config for replica connections:
            $config['replicaConfig'] = [
                'username' => App::env('DB_REPLICA_USER'),
                'password' => App::env('DB_REPLICA_PASSWORD'),
                'tablePrefix' => App::env('DB_TABLE_PREFIX'),
                'attributes' => [
                    // Use a smaller connection timeout
                    PDO::ATTR_TIMEOUT => 10,
                ],
                'charset' => 'utf8',
            ];

            // Define the replica connections, with unique DSNs:
            $config['replicas'] = [
                ['dsn' => App::env('DB_REPLICA_DSN_1')],
            ];

            // Instantiate and return the configuration object:
            return Craft::createObject($config);
        },
    ],
//    'bootstrap' => ['my-module'],
];