Page 2 of 3
Posted: Thu Jan 12, 2006 12:35 pm
by RobertGonzalez
Bill H wrote:The following query works fine in every location I put it (running MySQL 4.0 or earlier) except on one site which is running MySQL 4.1.
So are all your sites running 4.1? Another thing to look at is how MySQL 4.1 handles date values. Sometimes you need to wrap dates in '#' although I think that is on a different RDBMS. Sorry I couldn't be of more help.
Posted: Thu Jan 12, 2006 3:53 pm
by Bill H
So are all your sites running 4.1?
No, and that's what I'm trying to explore. I knew that 4.1 introduced some differences, but a full day of searching yesterday didn't lead to me finding out what they were. My wife is better at using search engines than I am, but she wouldn't know what MySQL was if it fell on her.
Like date bring a reserved word when it was not before. I do not doubt the validity of that, but I cannot find any reference to it. When did that change? I am very careful to never use reserved words for column names, so I can and will change the name of the column.
Edit: MySQL 4.1 documentation online does not list date as a reserved word. The reserved word listing specifically says that date is one of the words that it allows to be used unquoted.
How does the handling of dates differ in 4.1, and how do they need to be handled now? Can the date handling for 4.1 be used in earlier versions? I will search some more, knowing that one difference is specific to date handling, but if anyone can give me a tip it would save me some time. Probably a lot of time, given how wrapped in futility yesterday was.
Posted: Thu Jan 12, 2006 4:12 pm
by RobertGonzalez
This is the MySQL web site manual information on 4.0 to 4.1 changes.
http://dev.mysql.com/doc/refman/4.1/en/ ... m-4-0.html
Posted: Thu Jan 12, 2006 4:24 pm
by Bill H
Thanks. I just finished wading through that, along with the page "1.6.2.1. Features Available in MySQL 4.1" and found some stuff relating to timestamps and to column names, but nothing relating to a WHERE clause and a DATE field, and nothing saying that "date" was a reserved word. (In fact the docs specifically say it is not.)
So far I am finding nothing to tell me how to modify the code to make it run on 4.1.
Posted: Thu Jan 12, 2006 5:24 pm
by RobertGonzalez
Can you hit this database with phpMyAdmin or Query Browser?
Posted: Thu Jan 12, 2006 5:40 pm
by Bill H
Yes, the and the following query runs successfully and returns one row (as it should)
Code: Select all
SELECT * FROM eh_event WHERE Date='2006-01-06'
The problem seems from the error statement to be in the $Link variable
ie: "mysql_query(): supplied argument is not a valid MySQL-Link resource.."
but other places on this server db_connect.php is connecting okay.
The only difference I can see is that all other connections are made in the root directory of the site, using
Code: Select all
include "calendar/db_connect.php";
while the one that is failing is in the calendar subdirectory using
Not sure how that could make any difference, but I'm grasping at straws.
Posted: Thu Jan 12, 2006 6:00 pm
by RobertGonzalez
Ok, next question... Are you even connecting to the database from your script? Is this a DB connection issue or just a query link identifier issue?
Posted: Thu Jan 12, 2006 6:23 pm
by AKA Panama Jack
Bill H wrote:Yes, the and the following query runs successfully and returns one row (as it should)
Code: Select all
SELECT * FROM eh_event WHERE Date='2006-01-06'
The problem seems from the error statement to be in the $Link variable
ie: "mysql_query(): supplied argument is not a valid MySQL-Link resource.."
but other places on this server db_connect.php is connecting okay.
That means that the query statement was invalid and it couldn't create a link-resource for it. And DATE is definately a reserved word if you check the manual.
http://dev.mysql.com/doc/refman/4.1/en/ ... tions.html
The Manual wrote:DATE(expr)
Extracts the date part of the date or datetime expression expr.
mysql> SELECT DATE('2003-12-31 01:02:03');
-> '2003-12-31'
DATE() is available as of MySQL 4.1.1.
If you do not have it enclosed in backticks (`) then Mysql will try and process it as a function. It will error out and give you the EXACT problem that you are having at this time.
Posted: Thu Jan 12, 2006 6:30 pm
by RobertGonzalez
I was thinking, do you have error checking set on the db connection...
Code: Select all
if (!$Link = mysql_connect($db_host, $username, $password))
{
die("Could not connect to the database: " . mysql_error());
}
if (!mysql_slect_db($db_name, $Link))
{
die("Could not select the db: " . mysql_error());
}
Posted: Thu Jan 12, 2006 6:56 pm
by Bill H
From The documentation currently online at
http://www.mysql.com:
The words in the following table are explicitly reserved in MySQL 4.1.
(The word "date" is not included in the list that follows.)
MySQL allows some keywords to be used as unquoted identifiers because many people previously used them. Examples are those in the following list:
(The word "date" is included in the list that follows.)
I do believe that "date" is not the same as "DATE()".
See my earlier post. In phpmyadmin, on the server that is presenting the problem, the query:
Code: Select all
SELECT * FROM eh_event WHERE Date='2006-01-06'
runs without error and returns the one row whose date is 2006-01-06.
I do not see how the query statement is the source of the problem, and as long as we keep flogging that dead horse we will not get to what is the problem.
The include file "db_connect.php" is used 11 places on this website to connect to this server and this database. In ten of those places it does connect and provides a valid $Link identifier and the web page in question gets filled with data from the database. In this one instance it does not. I will add the "die" statements to the db_connect.php to see if the connection failure is occurring within that file this one time it is included while it is not failing the other ten places.
Edit: it is not dying or reporting an error.
Posted: Thu Jan 12, 2006 7:27 pm
by AKA Panama Jack
Ok, I give up you are being obtuse on purpose.
ANY function in Mysql is a reserved word and Mysql will try to process it if it is not backticked.
If you use a function like DATE as a field/column name without backticks it will FAIL.
If you still don't get it then don't expect it to get fixed.
Posted: Thu Jan 12, 2006 10:36 pm
by Bill H
Then why in the name of all that's holy does
http://www.mysql.com say on its website in its official damned documentation:
MySQL allows some keywords to be used as unquoted identifiers because many people previously used them. Examples are those in the following list:
ACTION
BIT
DATE
That is under the definition of reserved words in mysql 4.1. Not "did allow" or "used to allow" but "allows." And it is specific to MySQL 4.1.
Further I have rewritten this damned query in your style with backticks, with single quotes, with double quotes, without quotes and in every other stinking fashion I can think of to write it. I have copied it from your all knowing post, and it still doesn't work.
So if I am being obtuse then I do most abjectly apologize to your all-knowingness, but what you are offering is not helping.
Posted: Thu Jan 12, 2006 10:37 pm
by Buddha443556
How about checking MySQL permissions? Does the user have permission to SELECT that table?
Posted: Thu Jan 12, 2006 11:36 pm
by RobertGonzalez
Another question. Do all of your other queries work on your 4.1 server? I don't think the problem is with the wording or syntax of the query, I think it is something deeper than that.
The link identifier is returned upon connection to the database and is subsequently passed along the connection until the connection closes. You don't necessarily need to pass the link identifier to the mysql PHP functions (in the manual it says that the link id is optional). My first thought would be to echo out your link identifier when you connect to the database server. Error check the connection as well. Then error check the database selection. If you can connect to the server AND you can get a database connection AND you can run every other query under the sun, the n the next logical step is to make sure the variable being passed to query is accurate and valid. Beyond that, I am as pure out of ideas as everyone else.
Posted: Fri Jan 13, 2006 12:24 am
by Bill H
Does the user have permission to SELECT that table?
Well, that's a pretty good question. This host is a real schlock, and in fact I know nothing about the user. Sound wierd? Yeah, I know. In the admin, one creates the database, sets the MySQL password and one is done. The whole concept of creating users and granting permissions is absent from the server admin. So I asume the single, default user is a superuser with ALL permissions, which I am not happy about. (I did NOT select this hosting company.)
Everah, thank you for that. I have error checked the db connection and the db selection, no problems. And my other queries are running okay on the 4.1 server. I'm going to try tomorrow taking this routine out of the subdirectory and putting it into the root where the all rest of the querying routines are. Can't imagine why that would matter, but... (This host is so wierd that who knows what settings they might have. I could be losing the connection due to the directory level.)
As to "date" being reserved I have been told elsewhere, and I need to verify the validity of it, that MySQL doesn't evaluate anything in a query as a function unless it is followed by parentheses. So Date and DATE() are indeed different, and "date" is not reserved, while "DATE()" is reserved. If so, perhaps I am not all that obtuse, deliberately or otherwise.