MySQL 4.1 (I think) problem with query [SOLVED]

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Can you hit this database with phpMyAdmin or Query Browser?
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post 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

Code: Select all

include "db_connect.php";
Not sure how that could make any difference, but I'm grasping at straws.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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?
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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());
}
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post 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.
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post 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.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post 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.
User avatar
Buddha443556
Forum Regular
Posts: 873
Joined: Fri Mar 19, 2004 1:51 pm

Post by Buddha443556 »

How about checking MySQL permissions? Does the user have permission to SELECT that table?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post 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.
Post Reply