SQL query failure on login

  • Steve Cichosz
    Steve Cichosz

    This looks to be a very old posting, but I have just encountered this exact problem. I have this problem with admin and any other account I use to login.

    I just moved OPT to Fedore C5 from Fedora C3. Everything seems to be configured okay, the app comes right up and I can get past the login, but once logged in I get the error stated previously regarding an SQL syntax issue.

    I am using OPT Max version 1.2.0 I would like to upgrade but I cannot get into the DB to do so.

    The mysql database version is:
    Ver 14.12 Distrib 5.0.22, for redhat-linux-gnu (i386) using readline 5.0

    The error just for the record is:
    The requested URL /opt/main_menu/<hr>The following query failed:<br />select count(*) from news<br /> where chindate>2006-10-06 21:01:56<br />You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '21:01:56' at line 2<br><p>This error occured while Administrator was accessing /opt/index.php<p>The user came here from<p>Please make sure you include the lines above when submitting a bug report!<hr><div class= was not found on this server.
    Apache/2.2.2 (Fedora) Server at Port 80

    Any assistance would be greatly appreciated.


    • Steve Cichosz
      Steve Cichosz

      As one additional bit of info, I have attempted login with all the magic quote options in the php.ini set to on and off (true/false) and it has no effect either way.


      • This is due to the changes MySQL did to the date/time functions. It has all been fixed in the OPT 1.2.7 version.
        I understood you upgraded from FC3 to FC5, which also impies an upgrade of MySQL 3 (I believe) to MySQL 5. You're now in a rather odd position, as the auto upgrade may not work. I read, you also run OPT 1.2.0, so the autoupgrade probably points to the wrong server. You might be able to trich this by editing {inst_dir}/opt/UpgradeManager/index.php and change line with the upgrades variable to $upgrades=@file("http://opt.tiempo.nl/opt_releases/upgrades.xml");

        Best advise I can give is to download the upgrade tar file from http://opt.tiempo.nl/opt_releases/ (or download it directly: http://opt.tiempo.nl/opt_releases/OPT_1-2-7_MAX_upg.tar.gz  , extract it's contents and try the upgrade manually. Be sure to make a backup. You will need to execute the intermediate upgrades as well, as the database tables/fields have changed quite a few times. Sorry for the bad news, but this would be a lot easier if you had upgraded OPT before upgarding MySQL, which in your case happened automagically with the FC5 upgrade.

        Good luck, Martin

        • Steve Cichosz
          Steve Cichosz

          Thanks for the fast reply Martin.

          Fortunately I have been at this computer stuff for awhile and have learned that burning your technical bridges can be almost as bad as burning the personal ones. I have kept the original configuration completely in tact as a reference environment until the migration was complete. This was the idea behind migrating then upgrading, leave the original alone.

          Based on your advice my next approach is going to be to backup the DB and the code in the original system, run the autoupgrade and then migrate across. One thing I noticed in the emailed error was that my original statement of running 1.2.0 was incorrect, I am actually running 1.2.6. So I am presuming the autoupgrade->migrate strategy will work.

          I am on vacation all this coming week, but I may fiddle with this one evening from remote login.

          Thanks again for the assist.

        • Steve Cichosz
          Steve Cichosz

          I have completed the upgrade of the origin installation with only minor issues. The upgrade SQL statements failed for reasons of what looked to be translation of the single quotes in the statements to double quotes. I ran each statement by hand and commented them out of the XML. That looked to work fine.

          Next step was to migrate the origin OPT installation updated to 1.2.7 to the new hardware. Everything looks to have gone fine with one exception. Upon login I get a SQL failure:

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

          The error is encountered any time the overview screen is accessed. This is the 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

          And this is the table description which does appear to have a valid "aproj" column:

          | 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                   |                |

          I get the sense I am very close in that this and an inability to use the "admin" menu look to be the lst issues to solve. The upgrade looks to have worked since I can save double quotes in task comments now. (something broken in the previous version)

    • Steve Cichosz
      Steve Cichosz

      I thought the emaile error message for my last attempt might be useful as well:

      The OPT system at issued a database error:
      The following query failed:
      select count(*) from news
                    where chindate>2006-10-06 21:01:56
      You have an error in your SQL syntax; check the manual that corresponds to
      your MySQL server version for the right syntax to use near '21:01:56' at
      line 2
      This error occured while Administrator  was accessing /opt/index.php
      The user came here from
      OPT 1.2.6 Max
      POST variables:
               justlogged = 1
               username = admin
               password = admin
               lang = 0
               tz = -0700
               button = Login now

      Register globals: On
      User Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:
      Gecko/20060909 Firefox/

      I'll keep poking at it, but I would certainly appreciate any time saved with assistance.

      Thanks again and sorry to be going post crazy.