Autarchy of the Private Cave

Update: the same solution seems to still apply to dotProject 2.1.2; the post instructions have been modified for dP 2.1.2.

Update 2: fsmullin suggested a method to fix a similar error in index_table.php, the files_count_max cannot be found error when you click the FILES tab/menu item. His suggestion is now incorporated into the post.

Update 3: this post is still relevant for dotProject 2.1.3.

In one of my recent posts about project management software I stated the desire to extensively test dotProject 2.0.4. However, many shared hosting providers appear incompatible with dotProject: the right to CREATE TEMPORARY TABLES in MySQL is not granted, but is needed by dotProject.

Here’s sample error (2.0.4):

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)

For 2.1.2, sample error would be:

Table ‘tasks_total’ doesn’t exist

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@x.myserver.com;

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, and link 4.

However, none of the solutions described behind the links worked for me.

Here’s my solution (error fix).

To fix, instead of creating temporary tables each time they are needed (what is impossible on many shared hostings), 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):
   1. CREATE TABLE IF NOT EXISTS `tasks_problems` (
   2. `task_project` INT( 11 ) default NULL ,
   3. `task_log_problem` TINYINT(1) default NULL
   4. ) TYPE=MyISAM;
   5.  
   6. CREATE TABLE IF NOT EXISTS `tasks_critical` (
   7. `task_project` INT( 11 ) default NULL ,
   8. `critical_task` INT( 11 ) default NULL ,
   9. `project_actual_end_date` DATETIME default NULL
   10. ) TYPE=MyISAM;
   11.  
   12. CREATE TABLE IF NOT EXISTS `tasks_sum` (
   13. `task_project` INT( 11 ) default NULL ,
   14. `total_tasks` INT( 6 ) default NULL ,
   15. `project_percent_complete` VARCHAR( 11 ) default NULL,
   16. `project_duration` VARCHAR( 11 ) default NULL
   17. ) TYPE=MyISAM;
   18.  
   19. CREATE TABLE IF NOT EXISTS `tasks_summy` (
   20. `task_project` INT(11) default NULL,
   21. `my_tasks` varchar(10) default NULL
   22. ) TYPE=MyISAM;
   23.  
   24. CREATE TABLE IF NOT EXISTS `tasks_users` (
   25. `task_project` INT(11) default NULL,
   26. `user_id` INT(11) default NULL
   27. ) TYPE=MyISAM;
   28.  
   29. CREATE TABLE IF NOT EXISTS `tasks_total` (
   30. `task_project` INT( 11 ) DEFAULT NULL ,
   31. `total_tasks` INT( 6 ) DEFAULT NULL
   32. ) TYPE=MyISAM;
   33.  
   34. CREATE TABLE IF NOT EXISTS `files_count_max` (
   35. `file_versions` INT(11) default 0,
   36. `file_lastversion` float default '0',
   37. `file_version_id` INT(11) default 0,
   38. `file_project` INT(11) default 0
   39. ) TYPE=MyISAM;

   This will create all the necessary tables.
   Note: tasks_total and files_count_max tables are 2.1.2-specific.
   Another note: for 2.1.2, the tasks_sum table is one field shorter:

   1. CREATE TABLE IF NOT EXISTS `tasks_sum` (
   2.   `task_project` int(11) default NULL,
   3.   `project_percent_complete` varchar(11) default NULL,
   4.   `project_duration` varchar(11) default NULL
   5. ) TYPE=MyISAM;
2. Now, in classes/query.class.php at line 393 (for dotProject 2.0.4; just search for the matching line in 2.1.2) replace this line:
   1. $q = 'CREATE TEMPORARY TABLE ' . $this->_table_prefix . $this->create_table;

   with these:

   1. //$q = 'CREATE TEMPORARY TABLE ' . $this->_table_prefix . $this->create_table;
   2. $q = 'INSERT INTO ' . $this->_table_prefix . $this->create_table;

   Now we won’t get any errors about the tables which fail to be created.

3. Next, replace the following text in modules/projects/projects.class.php (lines 388-392 for 2.0.4):
   1. // Let's delete temporary tables
   2. $q  = new DBQuery;
   3. $q->dropTemp('tasks_sum, tasks_summy, tasks_critical, tasks_problems, tasks_users');
   4. $q->exec();
   5. $q->clear();

   with this code:

   1. // Let's delete temporary tables
   2. $sql = "TRUNCATE TABLE tasks_sum"; db_exec($sql);
   3. $sql = "TRUNCATE TABLE tasks_summy"; db_exec($sql);
   4. $sql = "TRUNCATE TABLE tasks_critical"; db_exec($sql);
   5. $sql = "TRUNCATE TABLE tasks_problems"; db_exec($sql);
   6. $sql = "TRUNCATE TABLE tasks_users"; db_exec($sql);
   7. $sql = "TRUNCATE TABLE tasks_total"; db_exec($sql);
   8. $q  = new DBQuery;

   Note: $sql = “TRUNCATE TABLE tasks_sum”; db_exec($sql); line is 2.1.2-specific.
   At this point, you will no longer receive error messages while on the “projects” tab.

4. (this point applies to 2.1.2 only) Replace lines 222 and 223 in modules/files/index_table.php
   1. $r->dropTemp('files_count_max');
   2. $r->exec();

   with:

   1. //$r->dropTemp('files_count_max');
   2. //$r->exec();
   3. $sql = "TRUNCATE TABLE files_count_max"; db_exec($sql);
5. Optionally, if you want to improve the performance of dotProject specifically for these tables, change the ‘engine’ for the above-mentioned tables to ‘memory’. To do that, execute the following SQL statements:
   1. alter table tasks_problems engine=memory;
   2. alter table tasks_critical engine=memory;
   3. alter table tasks_sum engine=memory;
   4. alter table tasks_summy engine=memory;
   5. alter table tasks_users engine=memory;
   6. alter table tasks_total engine=memory;
   7. alter table files_count_max engine=memory;

   This change will make MySQL keep these 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.

Comments and further improvements are welcome.