0012357: Date migration in DB upgrade scripts is very slow

ID	Project	Category	View Status	Date Submitted	Last Update
0012357	mantisbt	upgrade	public	2010-09-15 16:02	2012-02-13 06:03

Reporter	rzg	Assigned To	grangeway
Priority	normal	Severity	minor	Reproducibility	always
Status	closed	Resolution	won't fix
OS	Windows

Summary	0012357: Date migration in DB upgrade scripts is very slow
Description	We just upgraded our company Mantis from 1.0 to 1.2.2. As part of the upgrade, the last modified and last updated dates needed to be changed in format, for hundreds of thousands of notes and bug history items. The upgrade script hit various multi-minute timeouts multiple times. Eventually I had to do the migrations myself. Each of these SQL commands took less than 5 seconds to complete: update mantis.mantis_bugnote_table set date_submitted_int = DATEDIFF(S, '19700101', date_submitted), last_modified_int = DATEDIFF(S, '19700101', last_modified) where last_modified_int = 1 (updated about 200000 rows in my case) update mantis.mantis_config_table set value = '100' where config_id = 'database_version' [later] update mantis.mantis_bug_history_table set date_modified_int = DATEDIFF(S, '19700101', date_modified) where date_modified_int = 1 (updated about 800000 rows in my case) update mantis.mantis_config_table set value = '115' where config_id = 'database_version' The same could LIKELY be accomplished in mysql using the DATE_ADD() function. This would dramatically improve the upgrade speed.
Tags	No tags attached.

jreese 2010-09-15 20:32 reporter ~0026725	If you would like to provide and test SQL queries for all supported database backends (mysql, postgresql, mssql, idb, oracle...) to update all the date fields, we will happily accept patches. In the meantime, the current solution works correctly across all database types, and specifically makes a call to disable the execution timeout.

rzg 2010-09-16 00:32 reporter ~0026727	A few items: First, I have no idea what was timing out precisely. Firefox calls IIS calls php's DLL to run the script. Zend Optimizer is tossed in there somewhere. And despite disabling the execution timeout, it timed out at about 5 minutes. Could be Firefox or IIS or PHP or something else, I have no idea, but our config is pretty vanilla for a Windows setup. Second, I can test patches on mssql; that is all. I don't have access to other environments. Third, you are under the impression that Mantis works out-of-the-box on mssql. It doesn't- you can't even log in... It took a number of changes to the code to get mantis to nominally work on mssql, many of which I found mentioned in comments on the docs wiki or comments here. e.g. Edit database_api L59 to "$ADODB_FETCH_MODE = ADODB_FETCH_DEFAULT", change tag_api L412 to "if ( !db_is_mssql() ) {" (should probably say is_mysql, but I don't know), various changes to config_api around L321 to correctly handle datatypes.... I get the impression that my company is one of the few large-scale users of Mantis on mssql, and I try to contribute back relevant improvements that we've done.

jreese 2010-09-16 11:06 reporter ~0026738	Reminder sent to: grangeway Paul I think is the only one on the team who has ever used MSSQL, let alone used it with Mantis. I personally don't have any Windows machines, so testing on MSSQL isn't even possible for me. I apologize for compatibility problems, but if you submit improvements as patches, and ping the developer mailing list with them, we can try our best to integrate them.

rzg 2011-06-10 12:52 reporter ~0028972	I think that the change in 0012601 partially negates the need to make this change. Large databases like ours will still probably need manual intervention, but I digress.

grangeway 2012-01-27 17:36 reporter ~0031069	And whilst thi is still an 'issue' to consider - I'm kinda thinking that most people will have hit this upgrade by now so it's probably too late! If anyone hasn't upgraded and is effected by this issue, please do email me paul - mantisforge.org will do

View Issue Details

Activities