System Files and deferred bindings using database connection of parent model instead of default

[LukeTowers]

Cross posted from #385 on behalf of @informas

I have tried to reproduce the problem raised in my previous posting regarding errors that appear when moving user tables (and associated) to a secondary database.

The following are the steps followed for me.

1) Create 2 new MYSQL databases: winter_test_user (Main) winter_test_user_data (Auxiliary)

2) Download WinterCMS composer create-project wintercms/winter wintertestuser

3) Install WinterCMS php artisan winter:install

   Database type [MySQL]: 0
   Database Name []: winter_test_user
   Would you like to change the backend options? (URL, default locale, default timezone) (yes/no) [no]: no
   Configure advanced options? (yes/no) [no]: no

5) Install plugin users php artisan plugin:install Winter.User

php artisan plugin:list
+-----------------------+---------+-----------------+----------------+
| Plugin name           | Version | Updates enabled | Plugin enabled |
+-----------------------+---------+-----------------+----------------+
| Winter.Demo           | 1.0.1   | Yes             | Yes            |
+-----------------------+---------+-----------------+----------------+
| Winter.User           | 2.0.0   | Yes             | Yes            |
+-----------------------+---------+-----------------+----------------+ 

6) Copy the tables of the Winter.User plugin from the main database to the auxiliary database

+---------------------------------+
| Tables in winter_test_user_data |
+---------------------------------+
| user_groups                     |
| user_throttle                   |
| users                           |
| users_groups                    |
| winter_user_mail_blockers       |
+---------------------------------+

7) Create a new InforMAS.UserExtended plugin php artisan create:plugin InforMAS.UserExtended

8) Modify the Plugin.php file of the created plugin, as follow:

<?php namespace InforMAS\UserExtended;

use Backend; 
use System\Classes\PluginBase;

// Modules used by Winter.User plugin
use Winter\User\Models\User as UserModel;
use Winter\User\Models\UserGroup as UserGroupModel;
use Winter\User\Models\Throttle as UserThrottleModel;
use Winter\User\Models\MailBlocker as MailBlockerModel;

/**
 * UserExtended Plugin Information File
 */
class Plugin extends PluginBase
{
    public $require = ['Winter.User'];
    public $DB4Data = 'mysql_custom_data'; // Auxiliary DB for custom data
    
    /**
     * Returns information about this plugin.
     *
     * @return array
     */
    public function pluginDetails()
    {
        return [
            'name'        => 'User Extended',
            'description' => 'Clone de Winter User for work with tables in auxiliary database',
            ...
        ];
    }

    /**
     * Register method, called when the plugin is first registered.
     *
     * @return void
     */
    public function register()
    {
    }

    public function boot()
    {
        $this->extendModels();
    }

    protected function extendModels()
    {
        UserModel::extend(function($model) {
            $model->setConnection($this->DB4Data);
        });
        
        UserGroupModel::extend(function($model) {
            $model->setConnection($this->DB4Data);
        });  
        
        UserThrottleModel::extend(function($model) {
            $model->setConnection($this->DB4Data);
        });          
        
        MailBlockerModel::extend(function($model) {
            $model->setConnection($this->DB4Data);
        });          
    }
}

9) Modify config\database.php I add a new item to the connections array similar to mysql but changing the database name for the auxiliary database

        'mysql_custom_data' => [
            ...
            'database' => 'winter_test_user_data',
            ...
        ]

10) Refresh the plugin artisan plugin:refresh informas.userextended

11) Enter in Backend

12) I'm trying to create a new user I press the "+ New User" button and an error immediately appears

ERROR:
We're sorry, but an unhandled error occurred. Please see the details below.
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'winter_test_user_data.system_files' doesn't exist (SQL: select * from `system_files` where (system_files.id in (select `slave_id` from `deferred_bindings` where `master_field` = avatar and `master_type` = Winter\User\Models\User and `session_key` = 4eBL0FXKqn49pBY5cxcyPtlFpfa3YEq4dnJ1ldhW and `is_bind` = 1)) and system_files.id not in (select `slave_id` from `deferred_bindings` where `master_field` = avatar and `master_type` = Winter\User\Models\User and `session_key` = 4eBL0FXKqn49pBY5cxcyPtlFpfa3YEq4dnJ1ldhW and `is_bind` = 0 and id > ifnull((select max(id) from deferred_bindings where slave_id = system_files.id and master_field = avatar and master_type = Winter\User\Models\User and session_key = 4eBL0FXKqn49pBY5cxcyPtlFpfa3YEq4dnJ1ldhW and is_bind = 1 ), 0)) order by `sort_order` asc)
E:\websw10\wintercms\wintertestuser\vendor\laravel\framework\src\Illuminate\Database\Connection.php line 669

13) I copy the system_files table (which is currently empty) from the main database to the auxiliary database.

14) I'm trying to create a new user I press the "+ New User" button and immediately another error appears different from the previous one

ERROR:
We're sorry, but an unhandled error occurred. Please see the details below.
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'winter_test_user_data.deferred_bindings' doesn't exist (SQL: select * from `system_files` where (system_files.id in (select `slave_id` from `deferred_bindings` where `master_field` = avatar and `master_type` = Winter\User\Models\User and `session_key` = eRK6auj7sSyduuX6esRhwQVaGPcPpAQnFEGuM2mJ and `is_bind` = 1)) and system_files.id not in (select `slave_id` from `deferred_bindings` where `master_field` = avatar and `master_type` = Winter\User\Models\User and `session_key` = eRK6auj7sSyduuX6esRhwQVaGPcPpAQnFEGuM2mJ and `is_bind` = 0 and id > ifnull((select max(id) from deferred_bindings where slave_id = system_files.id and master_field = avatar and master_type = Winter\User\Models\User and session_key = eRK6auj7sSyduuX6esRhwQVaGPcPpAQnFEGuM2mJ and is_bind = 1 ), 0)) order by `sort_order` asc)
E:\websw10\wintercms\wintertestuser\vendor\laravel\framework\src\Illuminate\Database\Connection.php line 669

15) I copy the deferred_bindings table (which is currently empty) from the main database to the auxiliary database

16) I'm trying to create a new user

I press the "+ New User" button and it works perfectly

If instead I try the same situation on the table Groups (of users) ...

17) I remove the system_files and deferred_bindings tables from the auxiliary database

18) I'm trying to create a new group I press the "+ New Group" button It shows the form "Create User Group", I enter the corresponding data, but when pressing the "Create and close" button an error appears in a popup

POPUP ERROR
"SQLSTATE[42S02]: Base table or view not found: 1146 Table 'winter_test_user_data.deferred_bindings' doesn't exist (SQL: select * from `deferred_bindings` where `master_type` = Winter\User\Models\UserGroup and `session_key` = bKKpuCwcV1yD16UTXfCS7EbIY2x2uo72Ed1uLSmT)" on line 669 of E:\websw10\wintercms\wintertestuser\vendor\laravel\framework\src\Illuminate\Database\Connection.php

19) If I copy deferred_bindings table from main database to auxiliary database then I can create the groups without problems.

Take note: in the case of groups it seems that the system_files table is not necessary.

Any comments?

Thanks in advance!

[bennothommo]

I can see a rationale for attached data (files, deferred bindings) being stored in the same database as the parent model - for example, if one were setting up a secondary database that's perhaps more secure (or audited) for user data, it might be a security risk storing attachments or deferred data for those users in the main DB.

The only issues that I can see with this implied logic is that: a) this is not automated, so people who set up auxiliary databases will need to manually create the files and deferred binding tables on that DB, and b) the automatic truncation of old deferred binding records may not work.

[LukeTowers]

It's also not documented behaviour AFAIK, and it should at least be configurable.

[bennothommo]

Probably because it's not an common scenario to be running multiple databases in this fashion, so it's probably never come up before. Now that we know it, we definitely should document it at a minimum, and look at making it configurable down the track. I'm just thinking it's not a "bug", per se.

[github-actions[bot]]

This issue will be closed and archived in 3 days, as there has been no activity in the last 60 days. If this issue is still relevant or you would like to see it actioned, please respond and we will re-open this issue. If this issue is critical to your business, consider joining the Premium Support Program where a Service Level Agreement is offered.

[github-actions[bot]]

This issue will be closed and archived in 3 days, as there has been no activity in this issue for the last 6 months. If this issue is still relevant or you would like to see it actioned, please respond within 3 days. If this issue is critical for your business, please reach out to us at [email protected].

[LukeTowers]

@informas I believe this is fixed as of https://github.com/wintercms/storm/commit/2cd7370a6677b0dcd76c3d82101bc321de71d9f4