SQL query failure on login

  • Atle Lagestrand

    Atle Lagestrand - 2003-06-16

    Keep on getting this error after logging in as an administrator:
    The following query failed:
    select * from documents where  and chindate>20030606141028
    You have an error in your SQL syntax near 'and chindate>20030606141028' at
    line 1

    Couldn't find anything in the index.php nor the include/OPT_libs.php. Anybody who has experienced the same...?

    • Anonymous - 2003-06-16

      You didn't indicate which version you're using.  This should be fixed now in version 1.0.3 Max.  It is still an open bug in 0.948 beta however.

      If you are using 1.0.3 Max, please indicate which user # the person seeing the problem is, whether they have admin access and which group they're in (first or not).

      • Steve Cichosz

        Steve Cichosz - 2006-10-07

        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 - 2006-10-07

          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.


          • Martin Vernooij

            Martin Vernooij - 2006-10-07

            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 - 2006-10-07

              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 - 2006-10-18

              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 - 2006-10-07

          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.



Log in to post a comment.

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

Sign up for the SourceForge newsletter:

No, thanks