Autarchy of the Private Cave

Tiny bits of bioinformatics, [web-]programming etc

    dotProject 2.0.4/2.1.2/2.1.3 on shared hosting

    22nd January 2007

    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.

    6. Comments and further improvements are welcome.

      Share

    10 Responses to “dotProject 2.0.4/2.1.2/2.1.3 on shared hosting”

    1. vilvendhan Says:

      Thanks for your help.
      It is working now.

    2. chronos Says:

      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.

    3. Syed Says:

      Thanks for this info. It really helped and saved me a lot of time.

    4. fsmullin Says:

      Thanks for your information!

      I thought I would share that 2.1.2 that the index_table.php file in modules > files needs to be modified as well to fix the ‘files_count_max’ cannot be found error when you click the FILES menu item.

      Create the following table:

      CREATE TABLE `files_count_max` (
      `file_versions` INT(11) default 0,
      `file_lastversion` float default ’0′,
      `file_version_id` INT(11) default 0,
      `file_project` INT(11) default 0
      ) TYPE=MyISAM;

      Change line 103 of index_table.php from:

      $r->createTemp(‘files_count_max’);

      to:

      $r->addTable(‘files_count_max’);

      comment out lines 222 and 223

      //$r->dropTemp(‘files_count_max’);
      //$r->exec();

      Insert the following line in their place:

      $sql = “TRUNCATE TABLE files_count_max”; db_exec($sql);

      That should fix the file error.

    5. Bogdan Says:

      Thank you for sharing!

      I’m rarely navigating to the Files tab, but when I tried I did get loads of error messages originating from index_table.php.

      I am trying your solution now. If it works for me, I’ll add it to the post text.

    6. Bogdan Says:

      fsmullin,

      your solution works not exactly as expected.

      Following your directions exactly as written, I ended with a “No Result(s)” message instead of the list of files.
      However, if I do not change line 103 from $r->createTemp(‘files_count_max’) to $r->addTable(‘files_count_max’) – the list of files is shown, and there are no errors generated (but the files_count_max table seems to stay empty).
      If I just comment line 103 out, then I have that “No Result(s)” message again instead of files listing.

      Do I understand it right that addTable() method is supposed to work on existing tables?

      I guess that my solution for other tables (i.e. editing the exact files_count_max temporary creation query to just populate the existing table) might also work here perfectly – have to try that.

    7. Bogdan Says:

      I guess my solution (with no replace of line 103 in index_table.php) works because of the change I’ve introduced into query_class.php, namely:

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

      I only wonder why the addTable() doesn’t work for me.

    8. Mike Dawson Says:

      Hi,

      This worked perfectly – I have made a .tar.gz and I can make a .patch file to post here if anyone wants to save them a little time…

    9. Bogdan Says:

      Mike,

      you can send me the patch to really_anything at bogdan.org.ua (this is a catch-all address; attachment shouldn’t be larger than 1 MiB).

      I will add your submission to the post.

      Thank you.

    10. fsmullin Says:

      Thanks for taking a look at this. I was working with a blank database and then had to turn my attention to other things for a while. I will integrate your solution.

    Leave a Reply

    XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>