We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Database

Issue with mysql and CURRENT_TIMESTAMP on a timestamp

I use phinx to set-up my databases in a new project. I have the folowing configuration for one of my tables that I use.

  use Phinx\Migration\AbstractMigration;

  class NewsV1 extends AbstractMigration
  {
    public function change()
    {
      $this->table( 'news', [ 'id' => false, 'primary_key' => [ 'id' ]])
        ->addColumn( 'id', 'integer', [ 'length' => 10, 'signed' => false, 'identity' => true ])
        ->addColumn( 'thumbnail_id', 'integer', [ 'length' => 10, 'signed' => false, 'null' => true ])
        ->addColumn( 'title', 'string', [ 'length' => 64 ])
        ->addColumn( 'excerpt', 'string', [ 'length' => 255 ])
        ->addColumn( 'content', 'text' )
        ->addTimestamps()
        ->create();
    }
  }

The function addTimestamps() creates a created_at and an updated_at column in the table.

The created_at column is created as a timestamp with on create 'CURRENT_TIMESTAMP' and that is working as expected. As soon as you create a new record the created_at is set to the current timestamp.

The updated_at column is created without an update set to CURRENT_TIMESTAMP. So I changed the table configuration so that the updated_at now upadtes the column when the row is updated.

If I change the row in navicat (my tool to manage a database) the updated_at is changed. But when I issue a save or update in the controller, the news model is used in, the field does not get updated.

ofcourse I can force it in the model using somthing like this, but why should I have to when the database is configured to set the updated field.

    // todo: this should not be needed as the database has an update
    protected function beforeValidationOnUpdate()
    {
      $this->updated_at =date( "Y-m-d H:i:s", time( ));
    }

Am I doing something wrong or ?

Any help is appriciated



77.7k

That's strange, ON UPDATE CURRENT_TIMESTAMP is agnostic about the particular driver used to connect to it....

Are you sure the row gets updated when issuing save/update? It will fail silently, unless you check the returned bool variable.

public function newsAction($newsId)
{
    $news = News::findFIrstById($newsId);
    if(!$news) {
        // 404
        return;
    }
    $news->setColumn('value');
    if(!$news->update()) {
        // $news->getMessages(); contains the details
        throw new \Exception('Failed to update model!');
    }
}


4.5k

Here is my edit action

    public function editAction( $id )
    {
      $news =News::findFirst( $id );

      $form =new NewsForm( $news );

      // Check to see if we have a submit
      if( $this->request->isPost())
      {
        $form->bind( $this->request->getPost(), $news );

        // Validate the input
        if( $form->isValid( ))
        {
          $result =$news->save( );
          if( $result )
          {
            // todo: flash a success message

            $this->response->redirect( '/backend/news/' );
            return false;
          }
          else
          {
            // todo: flash a error message
            die( 'error1' );
          }
        }
        else
        {
          // todo: flash a error message

          var_dump( $form->getMessages( ));
          die( 'error2' );
        }
      }

      $this->view->setVar( 'form', $form );
    }

I know the data is updated as it should be, but the updated_at column is not changed unless I change it manually with the "beforeValidationOnUpdate". Don't mind the var_dumps, they will be replaced when I implement the flash messages. For now this is sufficient.

I have used update and have used save, neighter function seesm to triggers the update of the updated_at column. It should update as I have seen within navicat, but it does not.

Below is the defenition of the table in question

CREATE TABLE `news` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `thumbnail_id` int(10) unsigned DEFAULT NULL,
  `title` varchar(64) NOT NULL,
  `excerpt` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;


77.7k
Accepted
answer

My next bet would be that when you query the model, it fetches the updated_at, and when you save it, it writes back that value, overriding the CURRENT_TIMSTAMP.

Check this doc section: https://olddocs.phalcon.io/en/3.0.1/reference/models-advanced.html#skipping-columns



4.5k

hmm, that makes sense. Lets have a look if that might be the case.



4.5k

Yep, that does solve the issue, thanks. only 39 models to go.

Never thought it would be something like this because the create was working flawless.



77.7k

if all your columns are names createdat, you could write that logic to a base model class, and inherit the rest from there.



4.5k

Yep, I'll try that. Not sure if the code would break if a table doesn't have the columns. But that is easely tested. Thanks again.