Symphony.

I am struggling with a very special problem, but maybe someone has an idea.

I have created an extension which defines a new field type, because for this field I need validation comparing values in a separate database. The new field extends the standard Input Field. I have four functions:

• displaySettingsPanel
• checkPostFieldData
• validate (using initialiseIntranetDatabase)
• initialiseIntranetDatabase

When a new entry is saved, everything works fine only if I do not initialise the special database. As soon as I do this, I get:

The Section you are looking, test for could not be found.

(Sic. This is Symphony speak.)

Here are the two main functions:

public function validate($data){
    if(!$this->initialiseIntranetDatabase()){
        $error = $this->IntranetDatabase->getLastError();
        $this->_engine->customError(E_USER_ERROR, '...');
    }

    $db_value = $this->IntranetDatabase->fetchRow(0, ...);
    if(...) return false;
    return true;
}

public function checkPostFieldData($data, &$message, $entry_id = NULL){
    $message = NULL;
    if(!$this->validate($data)){
        $message = __("'%s' contains invalid data. Please check the contents.", array($this->get('label')));
        return self::__INVALID_FIELDS__;
    }
    return parent::checkPostFieldData($data, $message, $entry_id);
}

The first function works like a charm. So does the second one. But why is saving broken?

For the moment I solved the problem by manually coding the database query.

Nevertheless it would have been nice to make use of the Symphony MySQL class…

I suppose you can’t change the database connection half way through the page lifecycle. Opening your own database connection makes the most sense since it gives you the most control. Are you connecting to your own database using the MySQL class’s connect method? I would have thought you could create a new MySQL object to do this.

I would have thought you could create a new MySQL object to do this.

That’s what I tried.

private function initialiseIntranetDatabase(){
    $error = NULL;

    $details = $this->_engine->Configuration->get('intranet-database');

    $driver = $details['driver'];
    $driver_filename = TOOLKIT . '/class.' . $driver . '.php';

    if(!is_file($driver_filename)){
        trigger_error("Could not find database driver '<code>$driver</code>'", E_USER_ERROR);
        return false;
    }

    require_once($driver_filename);

    $this->IntranetDatabase = new $driver;

    if(!$this->IntranetDatabase->connect($details['host'], $details['user'], $details['password'], $details['port'])) return false;
    if(!$this->IntranetDatabase->select($details['db'])) return false;
    if(!$this->IntranetDatabase->isConnected()) return false;

    if($details['runtime_character_set_alter'] == '1'){
        $this->IntranetDatabase->setCharacterEncoding($details['character_encoding']);
        $this->IntranetDatabase->setCharacterSet($details['character_set']);
    }

    return true;
}

As soon as I call this function (see my first post), saving the entry breaks.

The Section you are looking, test for could not be found.

I’ve fixed that up in my working copy. Will push to integration at some point.

I would have thought you could create a new MySQL object to do this.

Shouldn’t be any problem with creating a new MySQL object.

The error you are getting suggests that the redirection URL is wrong/incomplete. What does the URL look like upon saving?

Just so I am clear, the new entry is not created at all?

Alistair, redirection does not take place. Upon saving I get /symphony/publish/test/new/ with the mentioned error message.

Here is the entry in the sym_entries table:

id                 int(11) unsigned   12030     
section_id         int(11) unsigned   0     
author_id          int(11) unsigned   0     
creation_date      datetime           0000-00-00 00:00:00
creation_date_gmt  datetime           0000-00-00 00:00:00

There is nothing saved to the sym_entries_data_ tables. (Although I had some content there during my tests, but I can not replicate this. The entry_id was 0 in these cases.)

Looks bad, doesn’t it?

Any chance I could get a hold of the field you’ve written, so I can do some local testing?

Alistair, I have sent you an email with the extension and some instructions.

I think I have found the problem. PHP appears to use the same resource ID for all connections opened by the same user. So, if you are trying to use the same DB user as the one specified in the Symphony config to open a new connection, PHP will attempt to use the already open connection resouce, and I believe this is where the problem lies. As soon as I used a new/different user, the problem went away. A var_dump() of the Symphony DB object, and the one you create in your field show identical resource IDs. Change the user, and the resource ID changes.

I tested using raw mysql_connect() calls as well and it produced the same problem, so I don’t think Symphony is at fault here, rather it is some quirk with how PHP or MySQL establishes connections. Can you try using a different user and see if that helps?

One other thing you could try is switching the DB object to use a different DB/table temporarily, switching it back once you are done.

Symphony::Database()->select(Symphony::Configuration()->get('db', 'intranet-database'));
...
Symphony::Database()->select(Symphony::Configuration()->get('db', 'database'));

Not sure if that is feasible.

Alistair, I verified that using the same database user for a second connection is no good idea. Indeeed the connection id will be the same for both connections. And this is what I get upon saving the entry if I use Symphony’s MySQL class:

Fatal error: Call to a member function fetchFieldsSchema() on a non-object in /Users/me/Sites/dev/me/symphony/symphony/lib/toolkit/class.entry.php on line 128

But this is not my case. I am using a second database with different user and password. A var_dump verfies that the connection id is indeed different. If I save the entry, Symphony responds:

Entry updated just now.

But the entry das been saved “empty”, as described above. It is displayed with empty fields in the entry edit page.

If I switch to mysql_connect instead of Symphony’s MySQL class, everything works. You may check this out by replacing the validatePLZ function in the extension I sent you:

public function validatePLZ($data){
    $details = $this->_engine->Configuration->get('intranet-database');

    $link = mysql_connect($details['host'].':'.$details['port'], $details['user'], $details['password']);

    if (!$link) {
        $this->throwCustomError();
    }

    mysql_select_db($details['db']);

    $query = sprintf("SELECT `xxx` FROM `xxxxxx` WHERE `xxx` = '$data' LIMIT 1", mysql_real_escape_string($data));

    $result = mysql_query($query);

    if (!$result) {
        $this->throwCustomError();
    }

    if (mysql_num_rows($result) == 0) {
        return false;
    }

    return true;
}

So I am rather sure that the problem lies in Symphony’s MySQL class.

Here are some of the errors in find in the log file upon saving:

2009/09/29 10:08:23 > WARNING: 2 - mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'me'@'localhost' (using password: NO) in file /Users/me/Sites/dev/me/symphony/symphony/lib/toolkit/class.mysql.php on line 139
2009/09/29 10:08:23 > WARNING: 2 - mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in file /Users/me/Sites/dev/me/symphony/symphony/lib/toolkit/class.mysql.php on line 139
2009/09/29 10:08:23 > WARNING: 512 - MySQL Error (1045): Access denied for user 'me'@'localhost' (using password: NO) in query "INSERT INTO `sym_entries` (`section_id`, `author_id`, `creation_date`, `creation_date_gmt`) VALUES ('', '', '', '')" in file /Users/me/Sites/dev/me/symphony/symphony/lib/toolkit/class.mysql.php on line 401
2009/09/29 10:08:23 > WARNING: 512 - MySQL Error (1045): Access denied for user 'me'@'localhost' (using password: NO) in query "SELECT t1.*, t2.callback FROM `sym_extensions` as t1 
                                            LEFT JOIN `sym_extensions_delegates` as t2 ON t1.id = t2.extension_id
                                            WHERE (t2.page = '/administration/' OR t2.page = '*')
                                            AND t2.delegate = 'NavigationPreRender'
                                            AND t1.status = 'enabled'" in file /Users/me/Sites/dev/me/symphony/symphony/lib/toolkit/class.mysql.php on line 401
2009/09/29 10:08:23 > WARNING: 512 - MySQL Error (1045): Access denied for user 'me'@'localhost' (using password: NO) in query "SELECT * 
                                                    FROM `sym_extensions_delegates` 
                                                    WHERE `delegate` = 'AddCustomPreferenceFieldsets'" in file /Users/me/Sites/dev/me/symphony/symphony/lib/toolkit/class.mysql.php on line 401
2009/09/29 10:08:23 > WARNING: 512 - MySQL Error (1045): Access denied for user 'me'@'localhost' (using password: NO) in query "SELECT `id` FROM `sym_sections` WHERE `handle` = 'test' LIMIT 1" in file /Users/me/Sites/dev/me/symphony/symphony/lib/toolkit/class.mysql.php on line 401

Interesting: me@localhost is the second database user in this case! So what is happening? Why is it unable to connect? (The extension has checked that the connection has been established successfully.)

If it helps I can send you a dump of the database objects via email!

A lot of the mysql functions accept a resource id, but if one is not supplied it will attempt to use the last opened connection. Perhaps this is where the problem lies. Now that I understand the problem a little more, I will do some further testing. It is possible the MySQL class in Symphony is attempting to use a non-existent or incorrect resource id.

Alistair, have you ever found the time to investigate this issue any further? Since the website in question is using more and more connections to the second database (most of them not using Symphony’s database class, but being hand-coded), time has come to clean it up and abstract this “second database layer”. I am not sure what to do - try and use Symphony’s class or use proprietary PHP code for everything.