Thanks for the modified check.

Are you primarily using MySQL? Seems to be a reoccurring theme where the modified field is not supposed to be null, and ends up being null due to a limitation in the MySQL server.

yes i' using mysql server.

i'm seeing the webdav table in a postgresql database and i have noticied the difference between the two database.

In the postgresql's webdav table the value of "modified" is not null, and is the same of the value of "entered".

In the mysql's webdav table the value of "modified" is null.

There is a difference in the "new_cdb.sql":

- in the postgresql version there is this statement:

modified TIMESTAMP(3) NOT NULL DEFAULT
CURRENT_TIMESTAMP,

- in the mysql version there is this other statement:

modified TIMESTAMP NULL,

Marco Pancioni

Antica Bottega Digitale

www.abd.it

Can't remember why, but i believe the 'modified' fields in all the database relations are NULL and this is specific to mysql only.

Could somebody from Centric CRM explain why this change was done?

Thanks,
Ananth

Centric CRM uses two timestamp columns for all tables...

A timestamp for when the record was created, and a timestamp for when the record was last modified (defaulting to the creation time)... the intent of modified is that it is never null.

However, when trying to do this in MySQL, it is up to the application to handle the second timestamp. The Centric CRM code needs to set the modified value to CURRENT_TIMESTAMP on insert (which it does in several places)... it should not default to null. This should be assigned as a task to complete.

The following FAILS in MySQL:

mysql> CREATE TABLE test (
-> something VARCHAR(200),
-> entered TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
-> modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
-> );
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Matt Rajkowski wrote:

The following FAILS in MySQL:

mysql> CREATE TABLE test (
-> something VARCHAR(200),
-> entered TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
-> modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
-> );
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

yes, this not works, but there is a trick to skip this error.

first you define the table:

CREATE TABLE test (
something VARCHAR(200),
entered TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
modified TIMESTAMP);

and second you must define a trigger like to this:

CREATE TRIGGER test_entries BEFORE INSERT ON `test` FOR EACH ROW SET
NEW.entered = IFNULL(NEW.entered, NOW()),
NEW.modified = NEW.entered;

I've tested it and i think this works for centric

Marco Pancioni

Antica Bottega Digitale

www.abd.it

How do you apply the patch?

I've created an sql file with all triggers that set more than one column to the value of CURRENT_TIMESTAMP.

With mysql only one column can be set to the value of CURRENT_TIMESTAMP.

This script solve this problem.

Marco Pancioni

Antica Bottega Digitale

www.abd.it

Marco,

Thanks for the triggers. We think a slight modification (to the way the 'modified' field is set in the trigger is necessary to support the back up and restore process)

• ****

CREATE TRIGGER web_tab_banner_entries BEFORE INSERT ON `web_tab_banner` FOR EACH ROW SET
NEW.entered = IF(NEW.entered = '0000-00-00 00:00:00', NOW(),NEW.entered),
NEW.modified = IF(NEW.modified = '0000-00-00 00:00:00', NEW.entered,NEW.modified)

• ****

Let me know what you think. Also, did you have to create these scripts by hand or did you have a program that inspected the schema and generated these?

Your feedback would be useful to make Centric CRM compliant with mysql going forward.

Thanks.

Kailash Bhoopalam wrote:

... We think a slight modification (to the way the 'modified' field is set in the trigger is necessary to support the back up and restore process)

• ****

CREATE TRIGGER web_tab_banner_entries BEFORE INSERT ON `web_tab_banner` FOR EACH ROW SET
NEW.entered = IF(NEW.entered = '0000-00-00 00:00:00', NOW(),NEW.entered),
NEW.modified = IF(NEW.modified = '0000-00-00 00:00:00', NEW.entered,NEW.modified)

• ****

Let me know what you think. Also, did you have to create these scripts by hand or did you have a program that inspected the schema and generated these?

...

Yes, you are right! i din't consider the backup and restore process! I think this modification must be applied.

I created this scripts by hand.

Marco.

Thanks Marco.