Bug: Inserting data using multiple databases uses default connection

[ledsummer]

PHP Version

7.3

CodeIgniter4 Version

4.1.8

CodeIgniter4 Installation Method

Composer (using codeigniter4/appstarter)

Which operating systems have you tested for this bug?

macOS

Which server did you use?

cli-server (PHP built-in webserver)

Database

MySql 8.0

What happened?

I'm trying to use multiple database connections for an API.

default - this DB connection will access the API authorization details dbother - this DB handles the get,post,put API request

when I try to use post request and insert data to dbother its shows error that its still accessing the default db. however, using get and update does work and I can access the dbother connection. Only the post has issue and still redirecting to the default db connection

Steps to Reproduce

create two db connection:

1. default - created in .env and database.php port 3306
2. dbother - created in .env and database.php por: 33060 (this is a remote database)

use post method by inserting data to dbother connection

Expected Output

A success message from my API and check if it's stored in the dbother connection.

Anything else?

title": "mysqli_sql_exception", "type": "mysqli_sql_exception", "code": 500, "message": "Table 'dbother.users' doesn't exist", "file": "/Users/barneycruzv/Desktop/web_dev/cda_api/vendor/codeigniter4/framework/system/Database/MySQLi/Connection.php", "line": 292, "trace": [ { "file": "/

[kenjis]

Can't reproduce. Could you show us exact Steps to Reproduce?

[ledsummer]

Sorry for the short details.

First setup .env and database to accommodate two database connection (multiple database)

1. default database connection (local machine DB)
2. otherDB database connection (remote DB)

create controller for inserting data in otherDB. Use post method.
in the controller I add the following script: $data //data to be inserted $emp = new UsersModel(); $emp->save($data);

create model UserModel where $DBGroup = 'otherDB' and $table='users'

after I run the code. it shows an error that during the post method it still connects to the default db connection. then atm I try to change the default db, $defaultGroup = 'otherDB' then the error removes. my problem is I'm not only using 2 DB connections ill-use almost 10 different db connections.

[kenjis]

Can't reproduce.

<?php
namespace App\Controllers;
use App\Models\UserModel;
class Home extends BaseController
{
    public function index()
    {
        $emp = new UserModel();
        $emp->save([]);
    }

    public function post()
    {
        return "<script>
            let f = new FormData();
            fetch('/', { method: 'POST', body: f });
            </script>";
    }
}

<?php
namespace App\Models;
use CodeIgniter\Model;
class UserModel extends Model
{
    protected $DBGroup          = 'otherDB';
    protected $table            = 'users';
}

Navigate http://localhost:8080/

InvalidArgumentException
otherDB is not a valid database connection group. 

Navigate http://localhost:8080/home/post and see Ajax request

{
    "title": "InvalidArgumentException",
    "type": "InvalidArgumentException",
    "code": 500,
    "message": "otherDB is not a valid database connection group.",

[ledsummer]

Do you have 2 databases? or do you have a working script on how to insert data in multiple databases using the 2nd database connection not the default, since in my case ci4 is calling the default connection for post request? But if I use only get there is no error and it can retrieve data from the second database connection

[kenjis]

I tried to reproduce according to your instruction, but it cann't be reproduced. You say the HTTP request method changes the behavior, but I can't reproduce it.

If you set $DBGroup = 'otherDB' in your model, the model connects to otherDB.

Please ask your support questions in the forums. Thanks! https://forum.codeigniter.com/forum-30.html

[ledsummer]

.env:

setup first the two connection in .env

database.default.hostname = 192.168.64.18:3306 
database.default.database = api_table 
database.default.username = 
database.default.password =
database.default.DBDriver = MySQLi
database.default.DBPrefix = 

database.otherDB.hostname = 127.0.0.1:33060 
database.otherDB.database = main 
database.otherDB.username = 
database.otherDB.password =  
database.otherDB.DBDriver = MySQLi
database.otherDB.DBPrefix =

then in database.php

public $defaultGroup = 'default';

public $default = [
        'DSN'      => '',
        'hostname' => 'localhost',
        'username' => '',
        'password' => '',
        'database' => '',
        'DBDriver' => 'MySQLi',
        'DBPrefix' => '',
        'pConnect' => false,
        'DBDebug'  => (ENVIRONMENT !== 'production'),
        'charset'  => 'utf8',
        'DBCollat' => 'utf8_general_ci',
        'swapPre'  => '',
        'encrypt'  => false,
        'compress' => false,
        'strictOn' => false,
        'failover' => [],
        'port'     => 3306,
    ];

public $otherDB = [
        'DSN'      => '',
        'hostname' => '127.0.0.1',
        'username' => '',
        'password' => '',
        'database' => '',
        'DBDriver' => 'SQLite3',
        'DBPrefix' => 'db_',  // Needed to ensure we're working correctly with prefixes live. DO NOT REMOVE FOR CI DEVS
        'pConnect' => false,
        'DBDebug'  => (ENVIRONMENT !== 'production'),
        'charset'  => 'utf8',
        'DBCollat' => 'utf8_general_ci',
        'swapPre'  => '',
        'encrypt'  => false,
        'compress' => false,
        'strictOn' => false,
        'failover' => [],
        'port'     => 33060,
    ];

UsersModel.php

class UsersModel extends Model
{
    protected $DBGroup          = 'otherDB';
    protected $table            = 'users';
    protected $primaryKey       = 'id';
    protected $useAutoIncrement = true;
    protected $insertID         = 0;
    protected $returnType       = 'array';
    protected $useSoftDeletes   = false;
    protected $protectFields    = true;
    protected $allowedFields    = [
        'last_name',
        'first_name',
        'middle_name',
        'birthdate',
        'contact_number',
        'email',
        'address',
        'type_id',
        'valid_id_number',
        'password',
        'hash',
        'is_verified'
    ];

public function addUser()
    {
        $rules = [
            'last_name'         => 'required',
            'first_name'        => 'required',
            'middle_name'       => 'required',
            'birthdate'         => 'required',
            'contact_number'    => 'required|exact_length[11]|regex_match[^[^1-9]]',
            'email'             => 'required|valid_email|is_unique[users.email]',
            'address'           => 'required|min_length[20]',
            'type_id'           => 'required',
            'valid_id_number'   => 'required',
            'password'          => 'required|min_length[5]',
        ];

        $messages = [
            "last_name" => [
                "required" => "Last name is required"
            ],
            "first_name" => [
                "required" => "First Name is required"
            ],
            "middle_name" => [
                "required" => "Middle Name is required"
            ],
            "birthdate" => [
                "required" => "Birthdate is required"
            ],
            "contact_number" => [
                "required" => "Contact Number is required",
                "exact_length" => "Contact Number must be 11 digit",
                "regex_match" => "Contact Number must start at 0"
            ],
            "email" => [
                "required" => "Email Address is required",
                "is_unique" => "Email Address is already used",
                "valid_email" => "Please use this format username@organizatio.com for email address"
            ],
            "address" => [
                "required" => "Full Address is required",
                "min_length" => "Please provide full address. Street, Barangay, City or Municipality"
            ],
            "type_id" => [
                "required" => "ID Type is required"
            ],
            "valid_id_number" => [
                "required" => "Valid ID number is required"
            ],
            "password" => [
                "required" => "Password is required",
                "min_length" => " Password must have a minimum lenght of 5"
            ],
        ];

        if (!$this->validate($rules, $messages)) {

            $response = [
                'status' => 400,
                'error'=> true,
                'name' => "ValidationError",
                'message' => $this->validator->getErrors()
            ];
        } else {

            $emp = new UsersModel();

            $data['last_name'] = $this->request->getVar("last_name");
            $data['first_name'] = $this->request->getVar("first_name");
            $data['middle_name'] = $this->request->getVar("middle_name");
            $data['birthdate'] = $this->request->getVar("birthdate");
            $data['contact_number'] = $this->request->getVar("contact_number");
            $data['email'] = $this->request->getVar("email");
            $data['address'] = $this->request->getVar("address");
            $data['type_id'] = $this->request->getVar("type_id");
            $data['valid_id_number'] = $this->request->getVar("valid_id_number");
            $data['is_verified'] = 1;
            $data['password'] = md5($this->request->getVar("password"));
            $data['hash'] = md5(rand(0, 1000));

            $emp->save($data);

            $response = [
                'status' => 201,
                'error'=> false,
                'name' => 'SuccessResponse',
                'message' => 'User successfully created'
            ];
        }

        return $this->respondCreated($response);
    }

routes.php

$routes->post("user", "EcooprisController::addUser");

This is the output:

{
    "title": "mysqli_sql_exception",
    "type": "mysqli_sql_exception",
    "code": 500,
    "message": "Table 'api_table.users' doesn't exist",
    "file": "/Users/barneycruzv/Desktop/web_dev/cda_api/vendor/codeigniter4/framework/system/Database/MySQLi/Connection.php",
    "line": 292,
    "trace": [
        {
            "file": "/Users/barneycruzv/Desktop/web_dev/cda_api/vendor/codeigniter4/framework/system/Database/MySQLi/Connection.php",
            "line": 292,
            "function": "query",
            "class": "mysqli",
            "type": "->",
            "args": [
                "SELECT 1\nFROM `users`\nWHERE `email` = 'test@gmail.com'\n LIMIT 1",
                0
            ]
        },
        {
            "file": "/Users/barneycruzv/Desktop/web_dev/cda_api/vendor/codeigniter4/framework/system/Database/BaseConnection.php",
            "line": 670,
            "function": "execute",
            "class": "CodeIgniter\\Database\\MySQLi\\Connection",
            "type": "->",
            "args": [
                "SELECT 1\nFROM `users`\nWHERE `email` = 'test@gmail.com'\n LIMIT 1"
            ]
        },

But if I change the public $defaultGroup = 'default'; into `public $defaultGroup = 'otherDB'; that error will be gone and It will work properly.

[kenjis]

In your model, protected $DBGroup = 'dbecoopris'; but you don't have DBGroup dbecoopris.

[ledsummer]

Yes i've change it already.

[ledsummer]

if I change the public $defaultGroup = 'default'; into public $defaultGroup = 'otherDB'; that error is gone and its working. my concern is what if I have multiple databases connection and all of them need to insert that will not be possible since ci4 only uses the connection under the public $defaultGroup

[kenjis]

Your code is $emp->save($data);, but the SQL is SELECT 1\nFROMusers\nWHEREemail= 'test@gmail.com'\n LIMIT 1.

I got it. The Validation object in the model has the default connection and use it.

[kenjis]

@ledsummer Please try:

[kenjis]

@ledsummer My previous comment was not correct. It seems in BaseModel, the Validation uses $this->DBGroup. So I don't know why your model uses the default DB group for validation.

[kenjis]

Aha! You don't use in Model validation. You are using Validation object in the controller. It uses the default DB group. So you can't validate well.

[kenjis]

This is not a bug. You don't specify DB group to your Validation object, so the default group is used.

Add the following in the controller:

protected function validate($rules, array $messages = []): bool
    {
        $this->validator = Services::validation();

        // If you replace the $rules array with the name of the group
        if (is_string($rules)) {
            $validation = config('Validation');

            // If the rule wasn't found in the \Config\Validation, we
            // should throw an exception so the developer can find it.
            if (! isset($validation->{$rules})) {
                throw ValidationException::forRuleNotFound($rules);
            }

            // If no error message is defined, use the error message in the Config\Validation file
            if (! $messages) {
                $errorName = $rules . '_errors';
                $messages  = $validation->{$errorName} ?? [];
            }

            $rules = $validation->{$rules};
        }

        return $this->validator->withRequest($this->request)->setRules($rules, $messages)->run(null, null, 'otherDB');
    }

[ledsummer]

Oh! now is working! I've forgotten to check the validation function. tsk. it takes me almost 2 days to solve this. Thank you! I very much appreciate your help!