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

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

Post by Bill H »

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. The eh_event is a date (not datetime) column which is indexed. I get told that $Result and $Row are invalid. The connection to the database is golden, and the $D variable is legit.

I've printed out the $Query and it typically is SELECT * FROM eh_event WHERE Date='2006-01-06'

Code: Select all

$Query = "SELECT * FROM eh_event WHERE Date='$D'";
     $Result = mysql_query($Query, $Link);

     while ($Row = mysql_fetch_array($Result))
     {
Last edited by Bill H on Fri Jan 13, 2006 6:45 am, edited 1 time in total.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

try

Code: Select all

$Query = "SELECT * FROM `eh_event` WHERE `Date` = '$D'";
If that doesn't work...what is the exact error message?
User avatar
Skittlewidth
Forum Contributor
Posts: 389
Joined: Wed Nov 06, 2002 9:18 am
Location: Kent, UK

Re: MySQL 4.1 (I think) problem with query

Post by Skittlewidth »

Bill H wrote: The eh_event is a date (not datetime) column which is indexed.
But in your select statement eh_event needs to be the table name.


Do you mean to :

Code: Select all

Select eh_event from tablename where date = '$d'
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Yeah, i wondered that, but as he said it had worked on other servers, i presumed not :?
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 »

Sorry, my bad, I read an article the other day on something called "sleep inertia." Seems your brain doesn't wake up for sometimes several hours after you do. I'm not making that up -- scientific study. So right now I'm functioning without a brain.

Anyway. Yes, eh_event is the table. I meant to say that Date is the date column that is indexed. I have always wondered why date was not a reserved word, since it is a column type. I'll try quoting it and let you know.

Edit: Nope, that didn't do it. Here's the error

Code: Select all

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /var/www/html/calendar/calendar.php on line 37
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

remove the $Link argument.. some servers just don't like getting the link resource..
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

so, what did you change the query to?
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 »

Okay, I just noticed, its the $Link it doesn't like, not the $Query.

Here's the thing. This is a school site. There are several schools and this code in it's entirety is simply pasted to each site. All of the same code is there. All of it, not just part. The school names are changed, and the content of the database, but the code in it's entirety is the same.

To keep ife simple, I have one function named db_connect that is "included" to connect to the database.

Code: Select all

$Host = "localhost";               // server name
$User = "user";                // database param
$Password = "pass";              // database param
$dbName = "name";                // database name

$Link = mysql_pconnect($Host, $User, $Password);
mysql_select_db($dbName, $Link);   // link to the main database by default
This works everywhere on three sites, including the calendar, and it works everywhere on this site except the calendar. The only difference between the sites is all are running versions of MySQL earlier than 4.1 except this one. The only difference between other locations in this site where the db is accessed and the calendar is that the other locations are in the root and the calendar is in a subdirectory, and this is the only one selected by Date. (The others are sorted by date.) The db_connect is in the subdirectory with the calendar, and functions in the root can use it, but the calendar, seemingly, cannot.

Edit: $Query = "SELECT * FROM eh_event WHERE 'Date'='$D'";

Edit2: Tried without the $Link. No joy.

Don't give up guys, kep the ideas coming.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Code: Select all

var_dump($Link, $Res);
throughout your code, mysql_<function>(<args>) or die(mysql_error()); is another nice idea
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 »

Now this I find somewhat interesting:

Code: Select all

include "db_connect.php";                   // get connection to db

$D = sprintf("%d-%02d-%02d", $_SESSION['Cyr'], $_SESSION['Cmth'], $day);
$Query = "SELECT * FROM eh_event WHERE 'Date'='$D'";
$Result = @mysql_query($Query) or die(mysql_error());
Throes this error:

Code: Select all

1 Access denied for user 'username'@'localhost' (using password: NO)
What is this not using password stuff? Every time I've seen errors it's been "(using password: YES)" and to the best of my knowledge this db does require a password. Is that "NO" as significant as I think it is?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

hmm... does it complains with the $Link argument specified?
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 »

No, it dies, but with no reported error.
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post by AKA Panama Jack »

Wrong

Code: Select all

$Query = "SELECT * FROM eh_event WHERE Date='$D'";

Wrong

Code: Select all

$Query = "SELECT * FROM eh_event WHERE 'Date'='$D'";

Right

Code: Select all

$Query = "SELECT * FROM eh_event WHERE `Date`='$D'";
You cannot use reserved words in a MySql Query as field/column names without enclosing them in backticks. Backticks are NOT single quotes. They are usually located on the same key as the tilde (~) key on QWERTY keyboards.

Date is now a reserved word in later versions of Mysql. In earlier versions you could get away with using Date as a field/column name without enclosing it in backticks.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Your error may be from a change in the MySQL use of their password hash algorithm. From 4.0.X to 4.1+ their password hashing changed from 16 bit to 46 bit (or something like that). That means that any user in 4.0.X format will not be able to access the database because their passwords are 16 characters long and the database's stored password is 46 characters long.

The workaround for this is to edit the my.ini file on the database server, under the [mysqld] section, adding the following line:

Code: Select all

[mysqld]
#Use old password encryption method (needed for 4.0 and older clients).
old_passwords
This will force newer versions of MySQL to use the older version of the password hash algorithm, allowing your code to be run on both platforms.

More information on this issue can be found at the MySQL website.
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 »

Not to be argumentative, but that query works fine on six servers -- returns results and fills the calendar with results. It does not work on this one server. Secondly, just to be sure, I have reworked the query statement to death, ticks, backticks, no ticks, the lot. None of them do anything other than die without an error.

I will try anything. This host is a real schlock (ie. you can only create one db, and cannot create any users so I assume the default user is ALL privelidges, which rather sucks. The admin panel is a chamber of horrors.) and I can't help but think it is some bizarre thing that they are doing, but...

Here's what they say they are running:
- PHP 4.3.11 (register_globals = ON / CGI)
- Perl 5.8.3 (/usr/bin/perl)
- Fedora Core 2 - Linux (custom kernel)
- Apache 2 (custom build)
- Sendmail 8 (/usr/sbin/sendmail)
- Bind 9
- MySQL 4.1 (localhost only)
Post Reply