Menu
▾
▴
#131 SQL Select Error on MySQL 4.1 for date value
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!
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");
}
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.
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.
Logged In: YES
user_id=608879
This has been fixed in the 1.2.7 Max release. Should be
backwards compatible.