I'm trying to set up a script but any user with a password that I've set up in MySQL (except for root user) is not able to access the databases. If the user does not have a password, it works. Any suggestions about why I'm getting errors such as the following would be welcome:

Warning: Access denied for user: 'poll@localhost' (Using password: YES) in sys:/apache/html/poll/db/include/class_mysql.php on line 32

Warning: MySQL Connection Failed: Access denied for user: 'poll@localhost' (Using password: YES) in sys:/apache/html/poll/db/include/class_mysql.php on line 32
Connection Error

MySQL Error : Connection Error
Error Number: 1045 Access denied for user: 'poll@localhost' (Using password: YES)
Browser : Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.0.3705)
Referer : http://www.mysite.net/poll/
PHP Version : 4.2.4-dev
Server : Apache/1.3.27 (NETWARE) PHP/4.2.4-dev mod_jk/1.2.2-dev
Script Name : /poll/db/index.php

Is this a new installation of MySQL? If so, any passwords and permissions you apply to users at runtime will only take effect after giving it a FLUSH PRIVILeges command (or after restarting the server, of course).

It's not really a new installation, but I'm just now starting to use it more. I assume the SQL command is FLUSH PRIVIL to FLUSH PRIVILeges? I had been able to log in with the different users I set up before even with passwords. It's only recently that only the root user can log in. Other accounts I've set up allow the user to access the databases but only if no password exists for that user. I'm just not sure why that's happening and what to do to fix it.

Urgle! No, it should be FLUSH PRIVILEGES (don't know what happened to my shift key, there :).

One thing to check: you are assigning the passwords (e.g. "my_secret") with values such as:

password('my_secret')

...are you? The reason I ask, is that the DB never actually stores the password; only its MD5 hash. So if you were to assign a user with a statement like:

INSERT INTO user (host,user,password,select_priv) VALUES ('localhost','bo_selecta','my_secret','Y');

The user won't be able to log in, since they'd have to type in something for their password which generated 'my_secret' AFTER having been put through the MD5 hash. Instead, you'd insert something like:

INSERT INTO user (host,user,password,select_priv) VALUES ('localhost','bo_selecta',password('my_secret'),'Y ');

Then, when you look at the user table in the 'mysql' database, you should see something like:

5jkHG7nH0943gFGYj0jythg

For the password (being the output generated by putting the actual password through the hash).

Just a thought.

I checked the user database and found a password had been created and gone through the MD5 hash. I also noticed (and I'm not sure whether it has any relevance to the login problem) that the root user has two entries, one for localhost and one for host %. Whereas the new users I've created that are unable to log in have one entry for host %. I also see at the bottom of some pages a message that says:
Error
The additional Features for working with linked Tables have been deactivated. To find out why click here.

I also tried setting a new password using the statement below and received an error:
#1064 - 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 'user( host, user,
PASSWORD , select_priv ) (I'm using MySQL 4.0.18)

Well the problem appears to be a lack of a space between the table name 'user' and the bracket.

As for your users being granted a host entry of "%", this is simply an implementation of the SQL92 '%' wildcard character, which you will become increasingly familiar with as you progress with MySQL (and database managers in general). It means simply "Any host", and so, by implication, sould permit users logging on from "localhost" (i.e. locally, on the same machine) to connect - although, as a further aside, MySQL defaults to rerouting all requests from localhost through its Unix Socket (or the equivalent "pipe" file on Win NT) because these connections are much faster.

FWIW, Debian's Woody and Sarge versions of MySQL both default to "skip-networking", which is to say MySQL will disallow any netweorked connections, restricting access to connections from any host other than "localhost" (and event those requests are, like I say, rerouted through the socket, which is the only reason they can get through). So, if you're running Woody or Sarge and find that yourself, logging on locally as root, say, can log in, but no one else can (no matter what privileges you give them) then that's the reason. I don't think this is the case in this instance, but I thought I'd pass it on as a helpful hint, sould you ever find yourself in that situation.

You could *try* adding a localhost entry (which, by the time I've finished typing all this nonsense, you'll probably have already done, anyway :) and see if they can log on then, but I can't see why that should be the case.

Tried several combinations of moving the space in the statement below and still get the #1064 error.

SQL-query :
INSERT INTO user( host, user,
PASSWORD , select_priv )
VALUES ('localhost', 'poll', password( 'polldb' ) , 'Y')

MySQL said:
#1064 - 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 'user( host, user,
PASSWORD , select_priv )
VALUES ( 'localho

I did also try creating a user with localhost as the host instead of the wildcard character. Still didn't work.

I also see in the user table that the root user's MD5 code for the password is different between the localhost and % accounts. Seemed strange to me. Why would that be the case?

Okay, try:

................space here
                |
                V
INSERT INTO user ( host, user,
PASSWORD , select_priv )
VALUES ('localhost', 'poll', password( 'polldb' ) , 'Y')


The passwords must be different, in either case, simple as that: it's the only way that can happen. The definition of a one-way-hash algorithm is that it always generates the same garbled output from the same input, and that two input strings have a vanishingly small chance of 'collision' (taht is to say, they are extraordinarily unlikely to both generate the same output string).

(FWIW, this is why, on the rare occasions someone has tried to hack code on the open source code repositories, its is immediately detected: add one character to the entire Linux kernel, and you get an entirely different checksum as the output. Since the checksum is what you use to verify the authenticity of the code, any changes are immediately apparent.)

That's where I tried putting a space in the statement. I tried it again... with the same result:

MySQL said:
#1064 - 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 'user( host, user,
PASSWORD , select_priv )
VALUES ( 'localho

I even put several spaces there and they were apparently removed.

You haven't used SPACE after TABLENAME "user" and before "(" I believe.

It works perfectly for me.

Cheers!

_________________________
-Vijay G
Strive for Perfection