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.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.
MySQL 4.1 (I think) problem with query [SOLVED]
Moderator: General Moderators
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
- Bill H
- DevNet Resident
- Posts: 1136
- Joined: Sat Jun 01, 2002 10:16 am
- Location: San Diego CA
- Contact:
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.So are all your sites running 4.1?
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.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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
http://dev.mysql.com/doc/refman/4.1/en/ ... m-4-0.html
- Bill H
- DevNet Resident
- Posts: 1136
- Joined: Sat Jun 01, 2002 10:16 am
- Location: San Diego CA
- Contact:
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.
So far I am finding nothing to tell me how to modify the code to make it run on 4.1.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
- Bill H
- DevNet Resident
- Posts: 1136
- Joined: Sat Jun 01, 2002 10:16 am
- Location: San Diego CA
- Contact:
Yes, the and the following query runs successfully and returns one row (as it should)
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
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.
Code: Select all
SELECT * FROM eh_event WHERE Date='2006-01-06'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";Code: Select all
include "db_connect.php";- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
- AKA Panama Jack
- Forum Regular
- Posts: 878
- Joined: Mon Nov 14, 2005 4:21 pm
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.Bill H wrote:Yes, the and the following query runs successfully and returns one row (as it should)The problem seems from the error statement to be in the $Link variableCode: Select all
SELECT * FROM eh_event WHERE Date='2006-01-06'
ie: "mysql_query(): supplied argument is not a valid MySQL-Link resource.."
but other places on this server db_connect.php is connecting okay.
http://dev.mysql.com/doc/refman/4.1/en/ ... tions.html
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.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.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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());
}- Bill H
- DevNet Resident
- Posts: 1136
- Joined: Sat Jun 01, 2002 10:16 am
- Location: San Diego CA
- Contact:
From The documentation currently online at http://www.mysql.com:
See my earlier post. In phpmyadmin, on the server that is presenting the problem, the query:
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.
I do believe that "date" is not the same as "DATE()".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.)
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'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.
- AKA Panama Jack
- Forum Regular
- Posts: 878
- Joined: Mon Nov 14, 2005 4:21 pm
- Bill H
- DevNet Resident
- Posts: 1136
- Joined: Sat Jun 01, 2002 10:16 am
- Location: San Diego CA
- Contact:
Then why in the name of all that's holy does http://www.mysql.com say on its website in its official damned documentation:
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.
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.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
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.
- Buddha443556
- Forum Regular
- Posts: 873
- Joined: Fri Mar 19, 2004 1:51 pm
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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.
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.
- Bill H
- DevNet Resident
- Posts: 1136
- Joined: Sat Jun 01, 2002 10:16 am
- Location: San Diego CA
- Contact:
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.)Does the user have permission to SELECT that table?
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.