dotProject 2.0.4 on GoDaddy.com shared hosting
22nd January 2007
In one of my recent posts about project management software I stated the desire to extensively test dotProject 2.0.4. However, GoDaddy.com shared hosting appears incompatible with dotProject: the right to CREATE TEMPORARY TABLES in MySQL is not granted, but is needed by dotProject.
Here's sample error:
query failed(CREATE TEMPORARY TABLE tasks_sum SELECT task_project, COUNT(distinct task_id) AS total_tasks,
SUM(task_duration * task_percent_complete * IF(task_duration_type = 24, 8.0, task_duration_type))/
SUM(task_duration * IF(task_duration_type = 24, 8.0, task_duration_type)) AS project_percent_complete FROM `tasks` GROUP BY task_project)
I tried looking for solutions, and here's what I found...
First of all, I tried issuing the "grant" command to try enabling temporary tables:
GRANT CREATE TEMPORARY TABLES ON dot_projects.* to dot_projects@mysql123.secureserver.net;
Expectedly, this didn't work: "access denied for user ....".
Then I tried looking for a ready-to-apply solution. Here are the links relevant to the topic, if you happen to prefer "the source", and not the retelling: link 1, link 2, link 3, link 4, link 5.
What should have helped, according to all that reading:
- download this file
- replace your /modules/project/index.php with the downloaded file, and
you're done!it didn't work, read below
What we need to do: instead of creating temporary tables each time they are needed (what is impossible on GoDaddy shared hosting), we need to create the necessary tables once. Then, instead of dropping temporary tables, we just truncate our "static" tables.
Here's how we proceed.
1. Issue once the following MySQL statements (using, e.g., phpMyAdmin):
(click the "PLAIN TEXT" box headers for the "copy-paste"-able plain text versions)
-
CREATE TABLE IF NOT EXISTS `tasks_problems` (
-
`task_project` INT( 11 ) DEFAULT NULL ,
-
`task_log_problem` TINYINT(1) DEFAULT NULL
-
) TYPE=MyISAM;
-
-
CREATE TABLE IF NOT EXISTS `tasks_critical` (
-
`task_project` INT( 11 ) DEFAULT NULL ,
-
`critical_task` INT( 11 ) DEFAULT NULL ,
-
`project_actual_end_date` DATETIME DEFAULT NULL
-
) TYPE=MyISAM;
-
-
-
CREATE TABLE IF NOT EXISTS `tasks_sum` (
-
`task_project` INT( 11 ) DEFAULT NULL ,
-
`total_tasks` INT( 6 ) DEFAULT NULL ,
-
`project_percent_complete` VARCHAR( 11 ) DEFAULT NULL,
-
`project_duration` VARCHAR( 11 ) DEFAULT NULL
-
) TYPE=MyISAM;
-
-
CREATE TABLE IF NOT EXISTS `tasks_summy` (
-
`task_project` INT(11) DEFAULT NULL,
-
`my_tasks` varchar(10) DEFAULT NULL
-
) TYPE=MyISAM;
-
-
CREATE TABLE IF NOT EXISTS `tasks_users` (
-
`task_project` INT(11) DEFAULT NULL,
-
`user_id` INT(11) DEFAULT NULL
-
) TYPE=MyISAM;
This creates all the necessary tables.
Now,
in classes/query.class.php at line 393 (dotProject 2.0.4, or search for the matching line)
comment out this line:
-
$q = 'CREATE TEMPORARY TABLE ' . $this->_table_prefix . $this->create_table;
so it reads:
-
//$q = 'CREATE TEMPORARY TABLE ' . $this->_table_prefix . $this->create_table;
Now we won't get any errors about the tables which fail to be created.
Next... I noticed that despite replacing the index.php file (with the one from the "should have worked" list), which replaces "dropTable" with truncate requests, tables still disappear.
I found that most of the functions from index.php in 2.0.4 are taken out to projects.class.php, and that is where the tables are deleted. Thus, index.php file with fixes is no longer a valid solution 
I restored original index.php, and replaced the following text in projects.class.php (lines 388-392):
-
// Let's delete temproary tables
-
$q = new DBQuery;
-
$q->dropTemp('tasks_sum, tasks_summy, tasks_critical, tasks_problems, tasks_users');
-
$q->exec();
-
$q->clear();
with this:
-
// Let's delete temporary tables
-
$sql = "TRUNCATE TABLE tasks_sum";
-
db_exec($sql);
-
$sql = "TRUNCATE TABLE tasks_summy";
-
db_exec($sql);
-
$sql = "TRUNCATE TABLE tasks_critical";
-
db_exec($sql);
-
$sql = "TRUNCATE TABLE tasks_problems";
-
db_exec($sql);
-
$sql = "TRUNCATE TABLE tasks_users";
-
db_exec($sql);
-
-
$q = new DBQuery;
At this point, I no longer received error messages while on the "projects" tab. However, I'm still unsure how this code in projects.class.php on lines 409-471 works with the createTemp commented out in query.php:
-
// GJB: Note that we have to special case duration type 24 and this refers to the hours in a day, NOT 24 hours
-
$q->createTemp('tasks_sum');
-
$q->addTable('tasks');
-
$q->addQuery("task_project, COUNT(distinct tasks.task_id) AS total_tasks,
-
SUM(task_duration * task_percent_complete * IF(task_duration_type = 24, ".$working_hours.", task_duration_type))/
-
SUM(task_duration * IF(task_duration_type = 24, ".$working_hours.", task_duration_type)) AS project_percent_complete, SUM(task_duration * IF(task_duration_type = 24, ".$working_hours.", task_duration_type)) AS project_duration");
-
if ($user_id) {
-
$q->addJoin('user_tasks', 'ut', 'ut.task_id = tasks.task_id');
-
$q->addWhere('ut.user_id = '.$user_id);
-
}
-
$q->addGroup('task_project');
-
$tasks_sum = $q->exec();
-
$q->clear();
-
-
// temporary My Tasks
-
// by Pablo Roca (pabloroca@mvps.org)
-
// 16 August 2003
-
$q->createTemp('tasks_summy');
-
$q->addTable('tasks');
-
$q->addQuery('task_project, COUNT(distinct task_id) AS my_tasks');
-
if ($user_id) {
-
$q->addWhere('task_owner = '.$user_id);
-
} else {
-
$q->addWhere('task_owner = '.$AppUI->user_id);
-
}
-
$q->addGroup('task_project');
-
$tasks_summy = $q->exec();
-
$q->clear();
-
-
// temporary critical tasks
-
$q->createTemp('tasks_critical');
-
$q->addTable('tasks');
-
$q->addQuery('task_project, task_id AS critical_task, MAX(task_end_date) AS project_actual_end_date');
-
$q->addJoin('projects', 'p', 'p.project_id = task_project');
-
$q->addOrder("task_end_date DESC");
-
$q->addGroup('task_project');
-
$tasks_critical = $q->exec();
-
$q->clear();
-
-
// temporary task problem logs
-
$q->createTemp('tasks_problems');
-
$q->addTable('tasks');
-
$q->addQuery('task_project, task_log_problem');
-
$q->addJoin('task_log', 'tl', 'tl.task_log_task = task_id');
-
$q->addWhere("task_log_problem> '0'");
-
$q->addGroup('task_project');
-
$tasks_problems = $q->exec();
-
$q->clear();
-
-
if ($addProjectsWithAssignedTasks) {
-
// temporary users tasks
-
$q->createTemp('tasks_users');
-
$q->addTable('tasks');
-
$q->addQuery('task_project');
-
$q->addQuery('ut.user_id');
-
$q->addJoin('user_tasks', 'ut', 'ut.task_id = tasks.task_id');
-
if ($user_id) {
-
$q->addWhere('ut.user_id = '.$user_id);
-
}
-
$q->addOrder("task_end_date DESC");
-
$q->addGroup('task_project');
-
$tasks_users = $q->exec();
-
$q->clear();
UPDATE: I decided to slightly improve the performance of dotProject specifically in this case. So I changed the 'engine' for the above-mentioned tables to 'memory'. If you followed this post, you now may want to issue the following SQL statements:
-
ALTER TABLE tasks_problems engine=memory;
-
ALTER TABLE tasks_critical engine=memory;
-
ALTER TABLE tasks_sum engine=memory;
-
ALTER TABLE tasks_summy engine=memory;
-
ALTER TABLE tasks_users engine=memory;
This change will make MySQL keep those tables in RAM. As they are used as temporary tables, this decreases disk I/O a lot while working with dotProject. Of course, YMMV - to apply this change or not depends on where is your bottleneck.
If you didn't yet do anything from the start of the post but intend to apply the above-described changes, then just change "type=MyISAM" to "type=memory" in the SQL box in the beginning.
I hope it works fine, if not - will have to update this post.
Here are the links to the two modified files, rename them as necessary and replace original files:
modules/projects/projects.class.php
Comments and improvements are welcome.
May 5th, 2007 at 9:03
Thanks for your help.
It is working now.
May 5th, 2007 at 9:39
I'm glad it helped!
Since I applied the described solution, there were no other related error messages or problems, so it should work just fine.
June 21st, 2007 at 4:52
Thanks for this info. It really helped and saved me a lot of time.