Wrong default value for timestamp columns in mysql about lithium HOT 25 CLOSED

Hi ertko,

please provide more information, especially about the model call you did, the schema you have set up, type of database and so on. Otherwise it's hard to track your issue down.

Thank you!

from lithium.

The schema :
CREATE TABLE posts (
id int(11) NOT NULL AUTO_INCREMENT,
title varchar(100) NOT NULL DEFAULT '',
body text NOT NULL,
postAt timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

The database:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.5.9 MySQL Community Server (GPL)

The view:

Post Successfully Saved

<?=$this->form->field('title');?>
<?=$this->form->field('body', array('type'=>'textarea'))?>
<?=$this->form->submit('Add Post')?>

And in the controller:

The model:
namespace app\models;

class Post extends \lithium\data\Model{

}

And i think the reason is when the framework build the sql it treated CURRENT_TIMESTAMP like string, but it shouldn't mysql wants a timestamp value not a string. you may try some mysql functions ,now() e.g.

Sorry for my poor english!

from lithium.

Thanks for your response, it really seems that this is an issue! We'll try to fix it as soon as possible.

from lithium.

ertko, It's not so much a bug as just not implemented at this stage, There are various deeper features of SQL such as this one which take more time to implement. This will be something best explored with other features which are similar in nature so they're handled at once. What should be possible is to set a field to date, time, datetime, timestamp etc... and feed it a numeric result from php's time(), I believe MySQL should happily accept this.

from lithium.

Howard3, thanks for the clarification. I set this ticket to "enhancement" and maybe we can track it further until this or something similar gets implemented.

@ertko, please drop by on irc #li3 if you need further assistance in solving this.

from lithium.

Thank's for your time.I solved this.

from lithium.

This is still an issue:

//The (pruned) Model
namespace app\model;
class Stories extends \lithium\data\Model {
    protected $id;
    protected $name;
    protected $modified;
}

//Controller Code
$model = Stories::create();
$model->save($this->request->data);

//SQL
CREATE TABLE `stories` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) 

The query generated by Database::create:

INSERT INTO `stories` (`name`, `modified`) VALUES ('some name', 'CURRENT_TIMESTAMP');

from lithium.

MySQL expects timestamps to be strings (see Date and Time Literals so this commit (e15b210) makes all timestamp fields invalid.

from lithium.

@L-P Just the else clause will be invalid? It should change to return date('Y-m-d H:i:s', strtotime($value));

And then obviously the test needs to be fixed as well.

from lithium.

you need to quote the output too, or else it just creates a sql syntax error i think

from lithium.

@L-P @jrgns
something like this:
https://gist.github.com/2788415

from lithium.

If the value is not numeric it should be because it already is a valid datetime/timestamp string so date(, strtotime()) is a bit redundant. But yes, it should be like this (but without the double else).

As a BC quickfix, I just removed the else.

from lithium.

Here is a diff against 55fd800 to add two failing tests:

--- a/tests/cases/data/source/DatabaseTest.php
+++ b/tests/cases/data/source/DatabaseTest.php
@@ -103,10 +103,10 @@ class DatabaseTest extends \lithium\test\Unit {
        $this->assertIdentical('NULL', $result);

        $result = $this->db->value('1234567', array('type' => 'timestamp'));
-       $this->assertIdentical(date('Y-m-d H:i:s', 1234567), $result);
+       $this->assertIdentical("'".date('Y-m-d H:i:s', 1234567)."'", $result);

-       $result = $this->db->value('now', array('type' => 'timestamp'));
-       $this->assertIdentical(time(), $result);
+       $result = $this->db->value('2012-05-25 12:34:56', array('type' => 'timestamp'));
+       $this->assertIdentical("'2012-05-25 12:34:56'", $result);
    }

    public function testValueByIntrospect() {

For some reason $this->connection was null in Database so I did not submit a pull request.

from lithium.

Yes, in MockDatabase $this->connection is null so it will fail on some tests.
But I don't understand why 'CURRENT_TIMESTAMP' return null.
Imo it would be :

if (strtolower($value) === 'current_timestamp') {
    return 'CURRENT_TIMESTAMP';
} 

from lithium.

CURRENT_TIMESTAMP does not return null (the PHP keyword) but 'NULL' (a string of the MySQL keyword) thus letting MySQL filling the field with the current timestamp.

from lithium.

Yeah sorry, i mean NULL. NULL will fill the field with the default value of the schema which is not exactly the same.
So replacing CURRENT_TIMESTAMP to NULL will fill the field with the current timestamp only if CURRENT_TIMESTAMP is the default value for the field. So imo it's better to keep CURRENT_TIMESTAMP

from lithium.

according to http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html why not supporting all of them ?

this way #402 would be solved at the same time.

from lithium.

A more general PR has been done to address "SQL expression" issues. Thanks @nateabele & @gwoo for the awesome solution.

from lithium.

Fixed in #492. See there for new syntax.

from lithium.

Hi guys! does anybody know if this issue has been fixed? Cause i've been having trouble with this.

from lithium.

@norms791 can you provide the code that is causing the error as well as the error/stack trace?

from lithium.

Code for the Model:

Controller:

View:

<?=$this->form->field('name');?>
<?=$this->form->field('lastNames');?>
<?=$this->form->field('birthday');?>
<?=$this->form->field('email');?>
<?=$this->form->field('Password');?>
<?=$this->form->field('profilePicture');?>\
<?=$this->form->field('sex'); ?>
<?=$this->form->submit('Add User'); ?>

<p>Post Successfully Saved</p>

Database:
CREATE TABLE IF NOT EXISTS users(
id INT NOT NULL AUTO_INCREMENT,
name varchar(63) NOT NULL,
lastNames varchar(63) NOT NULL,
birthday DATETIME,
email varchar(127) NOT NULL,
password varchar(31) NOT NULL,
creationDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
profilePicture varchar(127),
sex int(1),
PRIMARY KEY (id)
)Engine = InnoDB;

It is working, the user is been saved in the database but the creationDate (TIMESTAMP) value is always 0000-00-00 00:00:00

from lithium.

Can you guys look at #855 as it relates to this issue? Thanks.

from lithium.

@norms791 currently database adatpters doesn't support some specific features (and it's the case of the CURRENT_TIMESTAMP default value). If you need a creationDate the simpler way is to add the following in your Users model :

public function save($entity, $data = null, array $options = array()) {
    if (!$entity->exists()) {
        $entity->creationDate = date('Y-m-d H:i:s'); //(object) 'CURRENT_TIMESTAMP' should work too anyway
    }
}

from lithium.

The real issue here is that the default value for TIMESTAMP fields is not respected when set to CURRENT_TIMESTAMP. Adding a save() method to the model is just a workaround until there's a real fix.

from lithium.