#605 Strange Date in last updated emails

[Anonymous]

Hi Guys can you help me with this little issue

The system is working as it should (Thanks for that)

My issue is

When a user adds a booking

The last updated section states 1970 as the date please see attached

Have i missed something

See attached

[Campbell Morrison]

Which version of MRBS are you using? Also which database (MySQL or PostgreSQL) and version? (The information is on the Help page).

[Anonymous]

Facility Booking System:

MRBS 1.4.11

Database:

MySQL 5.6.17

System:
Windows NT ICTHELPDESK 6.1 build 7600 (Windows Server 2008 R2 Standard Edition) i586
Server time:
01/10/2014 08:54:41

system date and time on the server is correct

PHP:

5.5.12

[Anonymous]

Hi Campbell
please find attached an example of when a booking was updated

the date states 1970 (has this project been going that long :) )

[Anonymous]

That date is just zero in the Unix timestamp format that is used for that field. I can't work out though why you are getting zero.

Campbell

[Anonymous]

Yes it has me stumped

[Anonymous]

Can you have a look at the structure of the entry table (eg by using phpMyAdmin) and see if the timestamp column is Nullable or whether it is Not Null.

Thanks, Campbell

[Anonymous]

Here is what I see

[Anonymous]

this is in the zone section

[Campbell Morrison]

On my system the default value for timestamp is "CURRENT TIMESTAMP" and under extra I have "ON UPDATE CURRENT TIMESTAMP". Can you go into phpMyAdmin and change the structure of the timestamp column by changing the default and attributes accordingly. I'm not quite sure how your system got into that state, but as a first step can you try that and see if it works. If it does then I'll start thinking about how we make sure that those are the standard settings. (I suspect that it is something to do with (a) newer versions of MySQL and (b) global MySQL settings).

Campbell

[Anonymous]

I will do this monday and report back

[Campbell Morrison]

Did you have a chance to try the change above?

Could you also run the SQL below in phpMyAdmin and let me know the results:

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

Thanks

[Anonymous]

Could you post a screenshot of the settings i need to make as it is a live system and i really dont want to break the system.

[Campbell Morrison]

Here it is

[John Beranek]

Campbell, MySQL does appear to have gained the ability to change behaviour at 5.6, as this section of http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html states:

TIMESTAMP and DATETIME columns have no automatic properties unless they are specified explicitly, with this exception: By default, the first TIMESTAMP column has both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP if neither is specified explicitly. To suppress automatic properties for the first TIMESTAMP column, use one of these strategies:

• Enable the explicit_defaults_for_timestamp system variable. If this variable is enabled, the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses that specify automatic initialization and updating are available, but are not assigned to any TIMESTAMP column unless explicitly included in the column definition.

I'd say this means we should be explicitly stating the auto-update properties of our timestamp fields in tables.my.sql now.

[Campbell Morrison]

Yes, I saw that - though the default behaviour is still for DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP, and my system, which is version 5.6.13, works as expected. Nevertheless it should do no harm and I plan to make the change.

I'm just waiting though to check that (a) changing the default and attributes fixes the problem and (b) to see the sql_mode settings to understand how it happened.

[Campbell Morrison]

Any more news?

[Anonymous]

Sorry guys work is hectic. Will get this done tomorrow

[Anonymous]

Ok changing the timestamp structure to the same as yours has worked for me

Much appretiated

[Anonymous]

here are the screen shots of the results of the sql info you wanted
ran them both together and on own same result.

[Campbell Morrison]

I've now explicitly set DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP in the trunk (Rev 2872) just in case those aren't the default settings for the MySQL server.

[Anonymous]

Thanks campbell

i selected default everything when installing my sql

i really need to look at swotting up a little

[Campbell Morrison]

Yes, and default everything should be OK. I still don't understand how this happened, but the change above will do no harm.

[Anonymous]

Thanks lot

[Anonymous]

I had the same issure."Last updated" was always 1970,the timestamp was null.After I changed
the structure of the timestamp column ,set the default "CURRENT TIMESTAMP",attributes "ON UPDATE CURRENT_TIMESTAMP'. It worked.
Thanks a lot.

会议室预订管理系统:MRBS 1.4.11
数据库:MySQL 5.6.17
操作系统:Windows NT WIN-MRBS 6.1 build 7601 (Windows Server 2008 R2 Enterprise Edition Service Pack 1) AMD64
PHP:5.5.12

[Campbell Morrison]

MRBS 1.5.0 has now been released, so updating to 1.5.0 will also solve the problem.