\Phalcon\Mvc\Model::find/findFirst/findBy() searches within wrong schema/database

[swen100]

Hi,

I have a model-class in which I can set the source and database/schema dynamically. So I am able to switch the schema without having to create a separate definition for each model.

Maschine-Details

• Phalcon version: 3.1.1
• PHP Version: 7.0.17
• Operating System: Windows
• Installation type: php-extension downloaded from phalconphp.com
• Zephir version: Version 0.9.6a-dev-3a72ba9bb5
• Server: Apache
• DatabaseManagementSystem: MySQL 5.6.34

Expected Behavior

I get two different results - one from database 1 and one from database 2.

Actual Behavior

Phalcon searches within first database. After changing the schema, phalcon tries to get informations about database 2, but after that it still searches within database 1!

Code

SQL

CREATE DATABASE `test_1`;
USE `test_1`;
CREATE TABLE `test_table` (
  `id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `text` VARCHAR(255) NOT NULL DEFAULT 'blah',
PRIMARY KEY (id));
INSERT INTO `test_1`.`test_table` (`id`) VALUES (1); 

CREATE DATABASE `test_2`;
USE `test_2`;
CREATE TABLE `test_table` (
  `id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `text` VARCHAR(255) NOT NULL DEFAULT 'blubb',
PRIMARY KEY (id));
INSERT INTO `test_2`.`test_table` (`id`) VALUES (1); 

PHP

namespace Mapserver\Models;

class TestModel extends \Phalcon\Mvc\Model
{
    public static $database = '';
    public static $source = 'test_table';

    public function initialize()
    {
        $this->setConnectionService('db');
        $this->setSchema(static::$database);
        $this->setSource(static::$source);
    }

    public function getSource()
    {
        return static::$source;
    }

    public function getSchema()
    {
        return static::$database;
    }

}

// I set the database/schema: 
\Mapserver\Models\TestModel::$database = 'test_1';

// I try to find an object which is in database 1
$obj_1 = \Mapserver\Models\TestModel::findFirstById(1);
var_dump($obj_1->getSchema() );
var_dump($obj_1->toArray());
//Query is o.k, all works fine.

// I change the database/schema
\Mapserver\Models\TestModel::$database = 'test_2';

// I try to find an object which is in database 2
$obj_2 = \Mapserver\Models\TestModel::findFirstById(1);
var_dump($obj_2->getSchema() );
var_dump($obj_2->toArray());

Results

Note, that $obj_2->getSchema() gives the correct database!

string(6) "test_1"
array(2) {
  ["id"]=>
  string(1) "1"
  ["text"]=>
  string(4) "blah"
}

string(6) "test_2"
array(2) {
  ["id"]=>
  string(1) "1"
  ["text"]=>
  string(4) "blah"
}

Take a look at the query-log:

[11:53:34] SELECT IF(COUNT(*) > 0, 1, 0) FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME`= 'test_table' AND `TABLE_SCHEMA` = 'test_1'
[11:53:34] DESCRIBE `test_1`.`test_table`
[11:53:34] SELECT `test_table`.`id`, `test_table`.`text` FROM `test_1`.`test_table` WHERE `test_table`.`id` = :0 [1]
[11:53:34] SELECT IF(COUNT(*) > 0, 1, 0) FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME`= 'test_table' AND `TABLE_SCHEMA` = 'test_2'
[11:53:34] DESCRIBE `test_2`.`test_table`
[11:53:34] SELECT `test_table`.`id`, `test_table`.`text` FROM `test_1`.`test_table` WHERE `test_table`.`id` = :0 [1]

After changing the schema it queries: "DESCRIBE test_2.test_table" but then it does: "SELECT ... FROM test_1.test_table WHERE ..."

Issue may be related to these old ones: phalcon/cphalcon#3018 and phalcon/cphalcon#2415

[Jurigag]

Well it's only problem with find/findfirst looks like:

$di = new FactoryDefault();
$di->set(
    'db',
    function () {
        $adapter = new \Phalcon\Db\Adapter\Pdo\Mysql(
            [
                'host'     => 'localhost',
                'username' => 'root',
                'password' => '',
                'dbname'   => 'phalcon_test',
                'options'  => [
                    PDO::ATTR_EMULATE_PREPARES   => false,
                    PDO::ATTR_STRINGIFY_FETCHES  => false,
                    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
                ],
                'charset'  => 'utf8',
            ]
        );

        return $adapter;
    }
);

class Robots extends \Phalcon\Mvc\Model
{
    public static $database = '';

    public function initialize()
    {
        $this->keepSnapshots(true);
    }

    public function getSchema()
    {
        return static::$database;
    }
}

Robots::$database = 'phalcon_test';
var_dump(Robots::findFirst()->toArray());
/** @var Model\Manager $modelsManager */
$modelsManager = $di->get('modelsManager');
Robots::$database = 'phalcon_test2';
$robots = $modelsManager->load('Robots', true);
$query = new Model\Query('SELECT * FROM Robots', $di);
var_dump($query->execute()->toArray());
var_dump(Robots::findFirst()->toArray());

/vagrant/www/test.php:52:
array (size=7)
  'id' => int 1
  'name' => string 'Robotina' (length=8)
  'type' => string 'mechanical' (length=10)
  'year' => int 1972
  'datetime' => string '1972-01-01 00:00:00' (length=19)
  'deleted' => null
  'text' => string 'text' (length=4)
/vagrant/www/test.php:58:
array (size=0)
  empty
/vagrant/www/test.php:59:
array (size=7)
  'id' => int 1
  'name' => string 'Robotina' (length=8)
  'type' => string 'mechanical' (length=10)
  'year' => int 1972
  'datetime' => string '1972-01-01 00:00:00' (length=19)
  'deleted' => null
  'text' => string 'text' (length=4)

[Jurigag]

Actually it's PHQL(once parsed phql queries to sql are no longer parsed again) caching problem, doing Model\Query::clean(); after query fixes an issue.

@sjinks @sergeyklay any idea how to fix it while still having phql caching?

[Jurigag]

Well i guess https://github.com/phalcon/cphalcon/blob/master/phalcon/mvc/model/query.zep#L2440

Here uniqueId should takie into account database name? Like schema.

Just https://github.com/phalcon/cphalcon/blob/master/phalcon/mvc/model/query.zep#L2427 parse method require changes to fix this.

~I just propose to move let model = manager->load(realModelName, true); from _prepareSelect to parse() method, set it as property and getSchema in parse and just use key like uniqueId!schema~

Though i don't like this idea at all. Schema name should be IMHO parth of PHQL query so uniqueId is properly returned by internal parsing by selecting from diffrent database. If not provided will use default database.

[Jurigag]

Though is parsing whole PHQL for different database even needed? Maybe we could somehow handle it in more "dirty" way for performance reasons?

[pengjun1128]

I also have this bug，how to fix it?

[swen100]

Hi pengjun1128,

well, after nearly one year this bug is still there and nobody is interested in solving it further 😒

The thing I do is clearing the query-cache after every affected query:

\Phalcon\Mvc\Model\Query::clean(); 

[swen100]

this bug is sadly still present. Even with PHP 7.2.7 and Phalcon 3.4.0

Code

SQL

CREATE DATABASE `test_1`;
USE `test_1`;
CREATE TABLE `test_table` (
  `id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `text` VARCHAR(255) NOT NULL DEFAULT 'blah',
PRIMARY KEY (id));
INSERT INTO `test_1`.`test_table` (`id`) VALUES (1); 

CREATE DATABASE `test_2`;
USE `test_2`;
CREATE TABLE `test_table` (
  `id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `text` VARCHAR(255) NOT NULL DEFAULT 'blubb',
PRIMARY KEY (id));
INSERT INTO `test_2`.`test_table` (`id`) VALUES (1); 

PHP

<?php
class TestModel extends \Phalcon\Mvc\Model
{

    /**
     * @var string
     */
    public static $database = '';

    /**
     * @var string
     */
    public static $source = 'test_table';

    /**
     * @return string
     */
    public function getSource(): string
    {
        return static::$source;
    }
    
    /**
     * @return string
     */
    public function getSchema(): string
    {
        return static::$database;
    }

    /**
     * @return void
     */
    public function initialize()
    {
        $this->setConnectionService('db');
        $this->setSchema(static::$database);
        $this->setSource(static::$source);
        $this->keepSnapshots(true);
    }

}


$di = new \Phalcon\Di\FactoryDefault();
$di->set(
    'db',
    function () {
        $adapter = new \Phalcon\Db\Adapter\Pdo\Mysql(
            [
                'host'     => 'localhost',
                'username' => 'root',
                'password' => '',
                'dbname'   => 'test_1',
                'options'  => [
                    \PDO::ATTR_EMULATE_PREPARES   => false,
                    \PDO::ATTR_STRINGIFY_FETCHES  => false,
                    \PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'
                ],
                'charset'  => 'utf8',
            ]
        );

        return $adapter;
    }
);


// I set the database/schema: 
TestModel::$database = 'test_1';

// I try to find an object which is in database 1
var_dump($obj_1->getSchema() );
var_dump($obj_1->toArray());
//Query is o.k, all works fine.

// I change the database/schema
TestModel::$database = 'test_2';

// I try to find an object which is in database 2
$obj_2 = TestModel::findFirstById(1);
var_dump($obj_2->getSchema() );
var_dump($obj_2->toArray());


# works
$modelsManager = $di->get('modelsManager');
$robots = $modelsManager->load('TestModel', true);
$query = new \Phalcon\Mvc\Model\Query('SELECT * FROM TestModel', $di);
var_dump($query->execute()->toArray());

Results

Note, that $obj_2->getSchema() gives the correct database!

string 'test_1' (length=6)

array (size=2)
  'id' => int 1
  'text' => string 'blah' (length=4)

string 'test_2' (length=6)

array (size=2)
  'id' => int 1
  'text' => string 'blah' (length=4)

array (size=1)
  0 => 
    array (size=2)
      'id' => int 1
      'text' => string 'blubb' (length=5)

[geoglis]

This bug is still present, (even with Phalcon 3.4.1 zephir version 0.10.10 ). Present since mai 2017! To clean the query-cache after every affected query is no solution. In my opinion, there were some good suggestions... I know that not many users are affected with this bug, but thats no excuse for it that nobody seems to care about it.

[ruudboon]

Added it this to 4.0. We need to investigate this. If this take a lot of effort we move it to 4.1.

[geoglis]

👍

[niden]

This actually cannot be fixed as it is described in the issue. The reason being is that the getSchema and getSource are final methods in the model for the v4 version.

The only two ways that I can think that one can achieve this would be by creating two models, where the source/schema are defined in each class's initialize method. The second option is to create a helper method that would use the Query Builder and would select the appropriate schema for your case.

In the future v4 versions we intend on introducing another ORM that will implement the data mapper pattern. At that point this task will become trivial.

[ruudboon]

The impact for fixing this before 4.0 it too high. We need to move this to 4.1. If this is fixed early in the 4.1 development and we don't need interface changes we can cherry-pick this to merge into a 4.0.x release.

[stale[bot]]

Thank you for contributing to this issue. As it has been 90 days since the last activity, we are automatically closing the issue. This is often because the request was already solved in some way and it just wasn't updated or it's no longer applicable. If that's not the case, please feel free to either reopen this issue or open a new one. We will be more than happy to look at it again! You can read more here: https://blog.phalcon.io/post/github-closing-old-issues

[swen100]

This bug still exists. I just tried it with Phalcon v5.0.0beta3 :(