Last modified: 2011-03-13 18:06:24 UTC

Wikimedia Bugzilla is closed!

Wikimedia migrated from Bugzilla to Phabricator. Bug reports are handled in Wikimedia Phabricator.
This static website is read-only and for historical purposes. It is not possible to log in and except for displaying bug reports and their history, links might be broken. See T3855, the corresponding Phabricator task for complete and up-to-date bug report information.
Bug 1855 - waste through varchar in database- use CHAR not DATETIME
waste through varchar in database- use CHAR not DATETIME
Status: RESOLVED WONTFIX
Product: MediaWiki
Classification: Unclassified
General/Unknown (Other open bugs)
1.5.x
PC Linux
: Lowest normal with 1 vote (vote)
: ---
Assigned To: Nobody - You can work on this!
http://dev.mysql.com/doc/mysql/en/dat...
:
Depends on:
Blocks: 1932 454 866
  Show dependency treegraph
 
Reported: 2005-04-09 11:49 UTC by Thomas R. Koll
Modified: 2011-03-13 18:06 UTC (History)
0 users

See Also:
Web browser: ---
Mobile Platform: ---
Assignee Huggle Beta Tester: ---


Attachments

Description Thomas R. Koll 2005-04-09 11:49:17 UTC
all timestamps should be type char and not varchar.
Comment 1 T. Gries 2005-04-09 11:55:01 UTC
(In reply to comment #0)
> all timestamps should be type char and not varchar.

At the moment, all timestamps for 1) ENotif email notification
http://bugzilla.wikipedia.org/show_bug.cgi?id=454 and 2) EAuthent e-mail address
authentication http://bugzilla.wikipedia.org/show_bug.cgi?id=866 _uses_ indeed
varchar(14), because I currently need to use more values than timestamp and NULL.

However, I fully admit, that the use of varchar can and need to be changed
(which I will take care of in due time for the 454 and 866 ).

I added dependencies in order to track the bugzilla.

Tom - for ENotif and EAuthent
Comment 2 Domas Mituzas 2005-04-09 11:58:32 UTC
they should not be chars, they should be timestamps ;-) anyway, 1.5 has the
timestamp code and switching all chars/varchars into timestamp is possible on
transition...
Comment 3 Domas Mituzas 2005-04-09 12:00:21 UTC
eh, mea culpa, mea maxima culpa, not timestamps, DATETIME's :)
Comment 4 T. Gries 2005-04-09 12:06:27 UTC
(In reply to comment #2)
> they should not be chars, they should be timestamps ;-) anyway, 1.5 has the
> DATETIME code and switching all chars/varchars into timestamp is possible on
> transition...

Domas:

What you said is (currently) not okay for ENotif - I discussed this two days ago
with Brion. 

Somone (might be you) has fully broken my code in CVS HEAD without saying me
this by adding $dbr->timestamp() all over (which broke my code, because it also
use 0 and 1 for certain actions.

So my next steps are:

- to restore ENotif and EAuthent in CVS HEAD (and REL1_4 locally) by restoring
my way of implementation including all your and Brion's valuable  suggestions !

I personally do not like, if other persons "wrap" database call without exactly
knowning what they potentially break (like in the
http://bugzilla.wikipedia.org/show_bug.cgi?id=454 case) and without contacting
the owner of the bug (here: me)

Tom


Comment 5 River Tarnell 2005-04-09 12:09:44 UTC
WONTFIX.  there's no point changing varchar to char when we could just 
correctly move to datetime. 
Comment 6 T. Gries 2005-04-09 12:11:54 UTC
The use of CHAR [sic] has the advantage to be independent of UNIX TIMESTAMP
rollover on 03:14:07 Tuesday, January 19, 2038 (UTC).
That is, why I propose NOT to change to DATETIME and to stick to CHAR (instead
of VARCHAR ).

If someone knows better, pls. reply.
Comment 7 River Tarnell 2005-04-09 12:13:34 UTC
If MySQL hasn't added 64-bit time_t support in the next 33 years, we're 
going to have more problems than MediaWiki not working. 
Comment 8 T. Gries 2005-04-09 12:19:47 UTC
added for quick reference purposes only
[1] http://dev.mysql.com/doc/mysql/en/datetime.html
[2] http://dev.mysql.com/doc/mysql/en/char.html
Comment 9 Thomas R. Koll 2005-04-09 12:57:44 UTC
Thanks Tom for adding the links, maybe I should have added earlier that varchar
wastes a byte for the length information. I don't know how many entries are in
the db right now, but it should be a few megabytes that are wasted.

I'm not exactly sure how mysql reads the entries, but a fixed width is always a
performance advantage to a variable width.
Comment 10 T. Gries 2005-04-10 06:48:05 UTC
I made a survey about the useful datatypes, the survey being missing in this
bugzilla
Storage requirements http://dev.mysql.com/doc/mysql/en/storage-requirements.html

VARCHAR(14)    15 bytes ***
CHAR(14)       14 bytes * proposed by Thomas R. Koll
DATETIME        8 bytes
TIMESTAMPS      4 bytes

*** currently used by Mediawiki software

Roadmap for this:

I need to know
- where to go ? 
- What datatype to be used in the future ?
Comment 11 Brion Vibber 2005-04-10 06:49:15 UTC
Will use DATETIME.
Comment 12 T. Gries 2005-04-10 06:57:01 UTC
OKAY, very good. 
 
I have updaters.inc ready for ENotif and EAuthent. 
The code goes like that and can be adapted for converting other internal mediawiki timestamps. 
 
$meta = $wgDatabase->fieldInfo( 'watchlist', 'wl_notificationtimestamp' ); 
if ($meta->type != 'datetime') { 
	echo "ENOTIF: Converting wl_notificationtimestamp field to datetime datatype.\n"; 
 
	/* ALTER TABLE /*$wgDBprefix*/watchlist MODIFY (wl_notificationtimestamp datetime); - convert on-the-fly */ 
	dbsource( "maintenance/archives/patch-email-notification-changetype.sql", $wgDatabase ); 
} else {  
	echo "no database changes needed.\n"; 
} 
 
Comment 13 T. Gries 2005-04-10 09:11:48 UTC
I propose to add in database.php  
 	/** 
	 * Compatibility function 
	 * Converts a timestamp in old format into the new Datetime format 
	 * in:  $ts string char(14) YYYYMMDDHHMMSS 
	 * out: $ts string in datetime format YYYY-MM-DD HH:MM:SS 
	 */ 
	function convTimestampChar14ToDatetime( $ts ) { 
		if (strlen($ts) != 14) { 
			return '0000-00-00 00:00:00'; 
		} else { 
			return substr($ts,0,4).'-'.substr($ts,4,2).'-'.substr($ts,6,2).' 
'.substr($ts,8,2).':'.substr($ts,10,2).':'.substr($ts,12,2); 
		} 
	} 
 
	/**  
	 * Compatibility function:  
         *  
	 * Converts a timestamp in old format into the new Datetime format  
	 * in:  $ts string char(14) YYYYMMDDHHMMSS  
	 * out: $ts string in datetime format YYYY-MM-DD HH:MM:SS  
	 */  
	function convTimestampChar14ToDatetime( $ts ) {  
		if (strlen($ts) != 14) {  
			return '0000-00-00 00:00:00';  
		} else {  
			return substr($ts,0,4).'-'.substr($ts,4,2).'-'.substr($ts,6,2).' '.  
                           substr($ts,8,2).':'.substr($ts,10,2).':'.substr($ts,12,2);  
		}  
	}  
  
This function is needed inter alia when you compare timestamps in varchar14 format YYYYMMDDHHMMSS to timestamps in the DATETIME  
format, which comes as YYYY-MM-DD HH:MM:SS  
  
unless we use in every SELECT statement retrieving timestamp value the (database-dependent) DATE_FORMAT MySQL function. 
 
Comment 14 Ævar Arnfjörð Bjarmason 2005-04-10 09:18:13 UTC
We already have a function for this if I understand what you're trying to do
correctly, take a look at wfTimestamp().
Comment 15 T. Gries 2005-04-10 09:19:59 UTC
(In reply to comment #14) 
> We already have a function for this 
You are right, function wfTimestamp(TS_MW, $ts) is exactly, what I need. 
 
My comment #13 is misleading and obsolete. 
Tom 
 
 

Note You need to log in before you can comment on or make changes to this bug.


Navigation
Links