#131 SQL Select Error on MySQL 4.1 for date value

OPT_1.X_(Max)
closed-fixed
None
5
2006-09-10
2005-02-24
Anonymous
No

On MySQL 4.1 and above all queries with date values in
it fail. This happens because MySQL changed the format
of the returned value of typ timestamp. Before the
format was a number. In this version of MySQL the
format is in form "yyyy-mm-dd hh:MM:ss".

A possible solution is to quote the value which is
inserted in the select string.
example from forum_lib.php:

$sql="select (forums.date>$newDate) from forums where
id=$msg";

change to:
$sql="select (forums.date>'$newDate') from forums where
id=$msg";

Above is one errormessage from the OPT system:

The following query failed:
select (forums.date>2005-02-24 18:12:34) from forums
where id=1
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 '18:12:34) from forums where
id=1' at line 1

This error occured while xxxx was accessing
/forums/Msg_List/

The user came here from
http://xxxxxx/forums/Msg_Add/?att_type=projects&att_id=2

Please make sure you include the lines above when
submitting a bug report!

Discussion

  • Matthias Munnich

    Logged In: YES
    user_id=596252

    I ran into the same problem and was able to fix it by adding
    quotes around 4 variables in two file. See the patch
    belowWARNING: I am a php illiterate so this might be a bad
    way to fix it, however is works for me.

    Langelino

    --- OPT_1-2-6_MAX/opt/index.php 2002-08-28
    04:15:36.000000000 -0700
    +++ index.php 2005-07-01 16:16:59.000000000 -0700
    @@ -81,13 +81,13 @@
    if ($CRM_comp==1)
    {
    $sql="select count(*) from news
    - where chindate>$lastlog";
    + where chindate>'$lastlog'";
    }
    else
    {
    $sql="select count(*) from news
    where (company=$CRM_comp or company=0)
    - and chindate>$lastlog";
    + and chindate>'$lastlog'";
    }
    $result=CRM_run_query($sql);
    $myrow=OPT_fetch_row($result);

    --- OPT_1-2-6_MAX/opt/main_menu/welcome/index.php
    2004-12-16 23:56:54.000000000 -0800
    +++ index.php 2005-07-01 16:17:47.000000000 -0700
    @@ -299,7 +299,7 @@
    // Other logins
    // ========================
    $lastlog=getvalue("people","prevlog",$CRM_user);
    - $sql="select count(*) from people where
    lastlog>$prevlog and id!=$CRM_user";
    + $sql="select count(*) from people where
    lastlog>'$prevlog' and id!=$CRM_user";
    $result=crm_run_query($sql);
    list($people_logged)=OPT_fetch_row($result);
    if ($people_logged) {
    @@ -311,7 +311,7 @@
    // ========================
    if ($CRM_user > 5)
    {
    - $doc_count=crm_elements("documents"," and
    chindate>$prevlog");
    + $doc_count=crm_elements("documents"," and
    chindate>'$prevlog'");
    if ($docs=OPT_num_rows($doc_count)) {
    echo(sprintf(__("%d documents have been posted
    since you last logged in"),$docs)."<br>\n");
    }

     
  • chrismai

    chrismai - 2005-09-28

    Logged In: YES
    user_id=1353122

    There are more bugs, if you use OPT with MYSQL 4.1. The most
    critical bug is, that there is a function nicedate () in
    include/gui.php.
    This function edits the date to show it in a better format.
    To use this function an error message will be shown, if you
    want to see the people/task report:

    The following query failed:
    select
    if (year(2005-09-28 13:28:49)!=year(now()),
    date_format(2005-09-28 13:28:49,"%b %d %Y"),
    if (dayofyear(2005-09-28 13:28:49)=dayofyear(now()),
    "Today",
    date_format(2005-09-28 13:28:49,"%b %d")
    )
    )
    from people where id="6"
    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 '13:28:49)!=year(now()),
    date_format(2005-09-28 13:28

    This error occured while Administrator was accessing
    /opt/tasks/report/people_task.php?proj=2

    The user came here from
    http://eisfair/opt/tasks/leftmenu/?proj=2.

    If you convert the variable $field in the function nicedate
    (), the date will never be shown. For example in the mask
    Forums the threads dont have a dateinformation or in the
    Admin area the logs for all users will not be shown.

     
  • Martin Vernooij

    Martin Vernooij - 2005-09-29

    Logged In: YES
    user_id=608879

    This is all because the functionality of the date field in
    MySQL. MySQL has two forms as can be seen from the MySQL
    function now()
    [copy/paste from
    http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html\]

    NOW()

    Returns the current date and time as a value in 'YYYY-MM-DD
    HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the
    function is used in a string or numeric context.

    mysql> SELECT NOW(); [1]
    -> '1997-12-15 23:50:26'
    mysql> SELECT NOW() + 0; [2]
    -> 19971215235026

    On the demo site, I don't have the problems, because the
    database was originally running on MySQL 3.23. By just
    copying the data I still have in the demo site the values
    like: 20050929164937. As longs as all tables use this,
    you're oke. But what I see happening is that a lot of new
    setups, automagically start filling the tables with the
    string format. While [2] is an arithmatic value, [1] is a
    string and the minus sign is interpreted by the database
    server. And the space brakes the sql statement all together.

    On the list there have been suggestions to quote every used
    date function/field, so effectively start using format [1].
    To be honest: I'm not convinced this is the right approach.
    I would prefer either format [2] or use the UNIX_TIMESTAMP()
    function. But whatever solution is choosen, it means quite
    some work to locate every used date functionality in the
    scripts. And as I said before: I'm pretty busy. And
    unfortunately for Outreach it's not on my top-priority list,
    as my paying customers are.

    As soon as I can find some time, I'll dig into this and
    probably go for an intermediate 1.2.7 release to get these
    breaking SQL statements out of the way.

     
  • Martin Vernooij

    Martin Vernooij - 2006-09-10

    Logged In: YES
    user_id=608879

    This has been fixed in the 1.2.7 Max release. Should be
    backwards compatible.

     
  • Martin Vernooij

    Martin Vernooij - 2006-09-10
    • assigned_to: nobody --> martinfst
    • status: open --> closed-fixed
     

Log in to post a comment.

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

Sign up for the SourceForge newsletter:

JavaScript is required for this form.





No, thanks