R::Store($bean) Not Fully Working!!!

[Solotov]

Scenario

R::Store Is not detected by MySQL Triggers!

RedBeanPHP 4.3.4

Target MySQL TABLE

CREATE TABLE `sysxmltasks` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `id_sysxmltemplate` INT(11) UNSIGNED NOT NULL,
    `name` VARCHAR(50) NOT NULL,
    `worker_uid` VARCHAR(50) NULL DEFAULT NULL,
    `worker_uid_img` VARCHAR(50) NULL DEFAULT NULL,
    `imgroot_url` TEXT NULL,
    `type` VARCHAR(50) NOT NULL DEFAULT 'nueva',
    `accounts` VARCHAR(75) NULL DEFAULT NULL,
    `enable` TINYINT(4) NULL DEFAULT '0',
    `offset` INT(11) NULL DEFAULT '0',
    `success` INT(10) NULL DEFAULT '0',
    `errors` INT(10) NULL DEFAULT '0',
    `status` VARCHAR(20) NULL DEFAULT 'new',
    `status_images` VARCHAR(20) NULL DEFAULT 'new',
    `total` INT(10) NULL DEFAULT '0',
    `created_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `nextime` INT(11) NOT NULL DEFAULT '0',
    `update_date` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `FK_sysxmltasks_sysxmltemplates` (`id_sysxmltemplate`),
    CONSTRAINT `FK_sysxmltasks_sysxmltemplates` FOREIGN KEY (`id_sysxmltemplate`) REFERENCES `sysxmltemplates` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1752;

Launched Code ( PHP )

public function resetNxTime( $self, $params ) {
    ob_start();
    try {
        $id = $self->clean( $self->POST['id'] );

        // [ Issue Here ] In this case field "update_date" not are affected! and field "nextime" it's assigned correctly!

        /*$bean          = R::findOne( DbXmlTasks::table, 'id=?', [ $id ] );
        $bean->nextime = time();
        R::store( $bean );*/

        // Field "update_date" are triggered by MySQL normally and field "nextime" it's assigned correctly!
        R::exec( 'update sysxmltasks set nextime=UNIX_TIMESTAMP() where id=?', [ $id ] );

        $msg = 'success';
    } catch ( Exception $e ) {
        $msg = $e->getMessage();
    }
    ob_end_clean();
    die( $msg );
}

[gabordemooij]

Not sure whats going on but given a trigger:

CREATE TRIGGER qq BEFORE INSERT ONuserFOR EACH ROW SET NEW.created = 9;

and a piece of code like:

$_user = R::dispense('user');
R::store($_user);
print_r( R::find('user') );

I get:

 [properties:protected] => Array
                (
                    [id] => 3
                    [created] => 9
                    [bla] => 
                )

So, it's seems to work fine?

[Solotov]

Watch this

In this case trigger it is defined on column "update_date"

update_date DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

When execute this code

$bean = R::findOne( DbXmlTasks::table, 'id=?', [ $id ] ); $bean->nextime = time(); R::store( $bean );

the column update_date has not been updated by the trigger

And when i execute a direct query

R::exec( 'update sysxmltasks set nextime=UNIX_TIMESTAMP() where id=?', [ $id ] );

column update_date has been changed correctly by the trigger! ( ON UPDATE CURRENT_TIMESTAMP )

MariaDB versión 10.1.21

Thanks!

[gabordemooij]

Can you please provide the full SQL of the table definition?

[Lynesth]

Could it be because of the fact that it updates the whole bean ?

Does it work using Partial Beans (https://redbeanphp.com/index.php?p=/database#partialBeans) ?

[gabordemooij]

I should try that... that's why I requested the full table definition, so I can research the issue for you

On Thu, Oct 5, 2017 at 9:05 AM, Lynesth notifications@github.com wrote:

Could it be because of the fact that it updates the whole bean ?

Does it work using Partial Beans (https://redbeanphp.com/index. php?p=/database#partialBeans) ?

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/gabordemooij/redbean/issues/579#issuecomment-334377608, or mute the thread https://github.com/notifications/unsubscribe-auth/AAFe4mX_JTqc-8SGQm0djuz9sLWvv3oGks5spH_VgaJpZM4PrX1b .

-- Met vriendelijke groet,

       *SOFTWARE*

www.gaborsoftware.nl | Tel 0614655042 | KVK 66696534 Perenstraat 155, 2564RX Den Haag

[Solotov]

SQL SRC

CREATE TABLE `sysxmltemplates` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT(10) UNSIGNED NOT NULL,
    `name` TINYTEXT NOT NULL,
    `xpath_root` TINYTEXT NULL,
    `url` VARCHAR(350) NOT NULL,
    `dir_out` VARCHAR(350) NULL DEFAULT NULL,
    `size` INT(11) NOT NULL DEFAULT '0',
    `status` INT(11) NOT NULL DEFAULT '0',
    `notes` TEXT NULL,
    `creation_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `ip_alta` VARCHAR(100) NULL DEFAULT NULL,
    `random` VARCHAR(100) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=502
;

CREATE TABLE sysxmltasks (
        id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
        id_sysxmltemplate INT(11) UNSIGNED NOT NULL,
        name VARCHAR(50) NOT NULL,
        worker_uid VARCHAR(50) NULL DEFAULT NULL,
        worker_uid_img VARCHAR(50) NULL DEFAULT NULL,
        imgroot_url TEXT NULL,
        type VARCHAR(50) NOT NULL DEFAULT 'nueva',
        accounts VARCHAR(75) NULL DEFAULT NULL,
        enable TINYINT(4) NULL DEFAULT '0',
        offset INT(11) NULL DEFAULT '0',
        success INT(10) NULL DEFAULT '0',
        errors INT(10) NULL DEFAULT '0',
        status VARCHAR(20) NULL DEFAULT 'new',
        status_images VARCHAR(20) NULL DEFAULT 'new',
        total INT(10) NULL DEFAULT '0',
        created_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        nextime INT(11) NOT NULL DEFAULT '0',
        update_date DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        INDEX FK_sysxmltasks_sysxmltemplates (id_sysxmltemplate),
        CONSTRAINT FK_sysxmltasks_sysxmltemplates FOREIGN KEY (id_sysxmltemplate) REFERENCES sysxmltemplates (id) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1752;

[gabordemooij]

Okay, turns out that partial beans helps (https://redbeanphp.com/index.php?p=/database#partialBeans).

Also, this kind of trigger does not show up if I do 'show triggers' - so is it really a trigger anyways? A conventional trigger seems to work fine.

[Solotov]

Nice! Thank you!

[Lynesth]

Here is the explanation for the behavior:

An auto-updated column is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. An auto-updated column remains unchanged if all other columns are set to their current values. To prevent an auto-updated column from updating when other columns change, explicitly set it to its current value. Source: https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html

As Redbean inserts the current value of that columns when storing (unless using partial beans), it isn't automatically updated.

Edit: Didn't see it was a DATETIME column, so the following would not work.

So if you don't want to use partial beans, you may also try this:

Remove the NULL attribute from your update_date column and then, right before storing the bean, you can set it to NULL $bean->update_date = NULL;. That should work.

> TIMESTAMP columns by default are NOT NULL, cannot contain NULL values, and assigning NULL assigns the current timestamp. To permit a TIMESTAMP column to contain NULL, explicitly declare it with the NULL attribute. Source: https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html