SQL failure accessing overview screen

Help
2006-10-19
2013-03-22
  • Steve Cichosz

    Steve Cichosz - 2006-10-19

    I originally posted this at the bottom of a long thread started well over a year ago. I think this might more accurately be logged as a new problem

    OS:    Fedora C5
    MySQL: 5.0.22-1
    OPT:   1.2.7 Recently upgraded from 1.2.6 on a Fedora C3 system and migrated to the current FC5 installation.

    I receive an SQL error whenever I access the Overview screen, most noteably upon login. I have cut & pasted the SQL from the error message into a mysql session logged in as the database owner. I receive the same error. This error does not occur on the original FC3 environment. I suspect it is an SQL incompatibility with the new database version since the same SQL pasted into the mysql 3.23 version on the FC3 installation returns an empty set, but no error.

    Error:
    Unknown column 'requests.aproj' in 'on clause'

    SQL:
    select distinct(requests.id), title, priority, status, deadline, aproj, initial_estimate_seconds/3600
    from requests, request2task, task, person_task left join projects on requests.aproj = projects.id
    where (projects.active != 0) and requests.id = request2task.request and request2task.task = task.id
    and person_task.taskID = task.id and person_task.personID = 9 and (FROM_UNIXTIME(EndDatePlanned) >= FROM_UNIXTIME(1160985600)) and
    (FROM_UNIXTIME(EndDatePlanned) < DATE_ADD((FROM_UNIXTIME(1160985600)), INTERVAL 7 DAY)) order by priority asc

    Current requests table Structure:
    +--------------------------+-----------------------+------+-----+---------------------+----------------+
    | Field                    | Type                  | Null | Key | Default             | Extra          |
    +--------------------------+-----------------------+------+-----+---------------------+----------------+
    | id                       | mediumint(8) unsigned | NO   | PRI | NULL                | auto_increment |
    | title                    | varchar(255)          | NO   |     | NULL                |                |
    | aproj                    | mediumint(8) unsigned | NO   | MUL | 0                   |                |
    | src                      | mediumint(8) unsigned | NO   |     | 0                   |                |
    | owner                    | mediumint(8) unsigned | NO   | MUL | 0                   |                |
    | date                     | timestamp             | YES  | MUL | CURRENT_TIMESTAMP   |                |
    | request                  | mediumtext            | NO   |     | NULL                |                |
    | deadline                 | timestamp             | YES  | MUL | 0000-00-00 00:00:00 |                |
    | status                   | smallint(6)           | NO   | MUL | 1                   |                |
    | notify_src               | tinyint(1)            | NO   |     | 1                   |                |
    | reminded                 | mediumint(8) unsigned | NO   |     | 0                   |                |
    | priority                 | varchar(255)          | NO   |     | NULL                |                |
    | CatID                    | mediumint(9)          | NO   |     | 0                   |                |
    | milestone                | mediumint(9)          | NO   |     | 0                   |                |
    | resolved_date            | timestamp             | YES  |     | 0000-00-00 00:00:00 |                |
    | initial_estimate         | time                  | NO   |     | 00:00:00            |                |
    | initial_estimate_seconds | int(10) unsigned      | NO   |     | 0                   |                |
    +--------------------------+-----------------------+------+-----+---------------------+----------------+

    Any assistance that can be provided would be appreciated.

     
    • Bogdan Stancescu

      The only thing I can think of trying is to define an alias for requests and use that in the JOIN:

      select [...]
      from requests r, [...] left join projects on r.aproj = projects.id [...]

      You can try editing the query that way and see if it works. Would appreciate feedback regardless of success.

       
    • Steve Cichosz

      Steve Cichosz - 2006-10-19

      Gave it a go, no joy.

      ERROR 1054 (42S22): Unknown column 'r.aproj' in 'on clause'

      One thing I found interesting is that I had overlooked changing the "requests.id" reference in the distinct function of the selection list the first time through and that reference was sited in the error:

      ERROR 1054 (42S22): Unknown column 'requests.id' in 'field list'

      instead of simply reporting the join as being the problem. Once I changed the reference in the field list it to using the alias it went right back to the left join being the problem.

       
    • Steve Cichosz

      Steve Cichosz - 2006-10-23

      I've been poking around on the mysql site to see if I could discern the problem with the SQL when run in the latest version of the mysql database on my server as listed above. I have changed the format of the query and it runs now. Unfortunately it returns no rows in either the current database or the one the original format runs in unchanged. So I can't be certain it will return the correct data when it does find a matching set of values in the future.

      ORIGINAL SQL:

      select distinct(requests.id),
             title,
             priority,
             status,
             deadline,
             aproj,
             initial_estimate_seconds/3600
      from requests,
           request2task,
           task,
           person_task left join projects on (requests.aproj = projects.id)
      where (projects.active != 0)
        and requests.id = request2task.request
        and request2task.task = task.id
        and person_task.taskID = task.id
        and person_task.personID = 9
        and (FROM_UNIXTIME(EndDatePlanned) >= FROM_UNIXTIME(1161590400))
        and (FROM_UNIXTIME(EndDatePlanned) < DATE_ADD((FROM_UNIXTIME(1161590400)),
             INTERVAL 7 DAY))
      order by priority asc

      AS CHANGED:

      select distinct(requests.id),
             title,
             priority,
             status,
             deadline,
             aproj,
             initial_estimate_seconds/3600
      from request2task,
           task,
           person_task left join (projects, requests) on (requests.aproj=projects.id)
      where (projects.active != 0)
        and requests.id = request2task.request
        and request2task.task = task.id
        and person_task.taskID = task.id
        and person_task.personID = 9
        and (FROM_UNIXTIME(EndDatePlanned) >= FROM_UNIXTIME(1161590400))
        and (FROM_UNIXTIME(EndDatePlanned) < DATE_ADD((FROM_UNIXTIME(1161590400)),
             INTERVAL 7 DAY))
      order by priority asc

      Now I need to figure out where to make these changes in the php files. As always, hints, tips, clues, ideas and feedback of about any kind is welcome.

      Steve

       
    • Steve Cichosz

      Steve Cichosz - 2006-10-23

      I've updated the "./overview/user_todo.inc" file to include the SQL changes listed above and all is well. I'd be happy to post this file someplace for anyone who needs it.

      Steve
      Steve@Cichosz.org

       

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks