r2313: file modified and created dates incompatible with mysql < 4.1

tinyfly · 2007-05-04 22:10:06

If the 4.0.x branch is going to continue to be compatible with mysql 3.23+ then the data type “timestamp” may not be the best choice.

In the txp_file table the type on “modified” and “created” is “timestamp”. I have a client who is running mysql 4.0.21. When I save a file the time format that is supposed to get saved in the database is 0000-00-00 00:00:00. What actually gets saved is 00000000000000. The txp tags file_modifed and file_created no longer work.

If I change the type to “datetime” on “modified” and “created” and save a file then the dates are saved correctly as 0000-00-00 00:00:00. The related txp tags then work also. Same thing happens on mysql 4.0.17 .

From the mysql online documentation:

The TIMESTAMP data type has varying properties, depending on the MySQL version and the SQL mode the server is running in.

This seems like great fun but it looks like that after 4.1 it stablized.

Last edited by tinyfly (2007-05-05 16:16:09)

zem · 2007-05-04 23:46:56

tf,

Thanks. Can you post a specifc example of what’s in the database, what tag you’re using, and what the results are please?

tinyfly · 2007-05-05 16:48:21

Sure Alex,

I’ll be using mysql 4.0.21 as the basis of this. I uploaded a file with the name Construction_Schedule.pdf into txp through the files tab. What was saved in the database is this:

id: 18
filename: Construction_Schedule.pdf
category: construction
permissions: Empty
description: Construction Letting Schedule
status: 4
downloads: 0
modified: 20070424164734
created: 20070424161720
size: 29027

Here are the tags I used to output the file on the page:

<p>
<a href="<txp:file_download_link id="18" />" class="pdf"><txp:file_download_description /></a> 
<em class="content-type-info">( PDF file - <txp:file_download_size decimals="0" format="kb" /> )</em> 
- last updated on <txp:file_download_modified format="%b %e, %Y" />
</p>

And this is what the result was:

Construction Letting Schedule ( PDF file - 28KB ) - last updated on Dec 31, 1969

No matter what I set the time stamp to through txp or if I used file_download_created instead of modified it always stayed at 1969.

How I fixed it was:

mysqldump from 4.0.21
import that into my local dev environment which is running mysql 5.0.33
modifiy txp_file to use datetime
mysqldump with compatibility for 4.0 and import back into the 4.0.21 database.

This is all to the best of my recollection as I no longer have access to the 4.0.21 database.

Last edited by tinyfly (2007-05-05 16:49:23)

ruud · 2007-05-05 18:32:12

Since the ‘textpattern’ table also uses DATETIME for the posted/modified columns, I agree that it makes sense to also use DATETIME in the created/modified columns of the ‘txp_file’ table.

I’m also wondering why the ‘file_download_format_info’ function exists, when MySQL can do the conversion between DATETIME and UNIX timestamps all by itself.

Mary · 2007-05-05 22:46:02

Backwards compatibility (for plugins) would be my guess. Compare what the dev branch to the stable.

zem · 2007-05-06 00:07:16

The problem appears not to be with the database at all, but with the strtotime() function. The file_download_format_info function uses strtotime() to convert the database timestamp format to a unix timestamp. It should work with either format, 20070424164734 or 2007-04-24 16:47:34.

Try this:

<txp:php>dmp(strtotime('20070424164734'), strtotime('2007-04-24 16:47:34'));</txp:php>

You should get two identical numbers.

Can you post your diagnostics please?

Last edited by zem (2007-05-06 00:07:48)

ruud · 2007-05-06 09:08:00

The ‘created’ and ‘modified’ columns in txp_file did not exist in 4.0.4, there were added afterwards, so there’s no plugin compatibility to worry about. Apart from txp_lang, all other tables use DATETIME, so to perhaps txp_file should follow that same style.

Alex, the first “strtotime” in your question gives “-1”, the second “1177426054”

Last edited by ruud (2007-05-06 09:14:07)

zem · 2007-05-06 23:15:49

Can you post your diagnostics please, or at least the parts about OS and PHP versions?

It sounds like your server has an old version of strtotime() that doesn’t support all timestamp formats. We need to know how common that is.

Mary · 2007-05-06 23:23:11

Ruud: The database isn’t what I refer to, it doesn’t have any effect upon whether plugins use a given function or not.

ruud · 2007-05-07 09:53:50

Mary, that’s correct, but this particular function only deals with the created/modified columns. It existed in 4.0.3 and there it also dealt with filesizes, so if it was changed there, it can be changed (or removed, really) here too.

Alex, I’m using Debian Sarge with PHP 4.3.10 and MySQL 4.0.24

tinyfly · 2007-05-07 16:53:32

zem wrote:

Can you post your diagnostics please, or at least the parts about OS and PHP versions?

Unfortunately I can’t post the full diagnostics because I no longer have access to that install but I do remember the versions.

OS: Solaris 10/SunOS 5.10
PHP: 4.3.11
mySQL: 4.0.21

Last edited by tinyfly (2007-05-07 16:53:50)

wet · 2007-05-07 17:29:48

inode.at (major Austrian loooow tech host)

MySQL: 4.0.24-standard
Server: Apache/1.3.33 (Unix) FrontPage/5.0.2.2623 PHP/4.3.11 mod_perl/1.29

strtotime(‘20070424164734’) => -1
strtotime(‘2007-04-24 16:47:34’) => 1177426054

Dreamhost

MySQL: 5.0.24a-standard-log
Server: Apache/2.0.54 (Unix) PHP/4.4.4 mod_ssl/2.0.54 OpenSSL/0.9.7e mod_fastcgi/2.4.2 DAV/2 SVN/1.3.2

both => 1177458454

all-inkl.de (major German quality web host)

MySQL: 4.0.23-Max-log
Server: Apache/1.3.37 (Unix) PHP/4.4.4 with Suhosin-Patch FrontPage/5.0.2.4803 mod_fastcgi/mod_fastcgi-SNAP-0404142202 mod_ssl/2.8.28 OpenSSL/0.9.6i

strtotime(‘20070424164734’) => -1
strtotime(‘2007-04-24 16:47:34’) => 1177426054

XAMPP 1.5.3a

MySQL: 5.0.21-community
Server: Apache/2.2.2 (Win32) DAV/2 mod_ssl/2.2.2 OpenSSL/0.9.8b mod_autoindex_color PHP/5.1.4

both => 1177458454

Last edited by wet (2007-05-07 17:30:18)

Textpattern CMS

Textpattern CMS support forum

#1 2007-05-04 22:10:06