HELP: iASP & MySQL on Panther

[cyberwill]

Can anyone please help me. I'm really stuck trying to fix this on my own. I'm running iASP on Mac OSX (to let me develope ASP web pages.) For the life of me I can not get it to work with MySQl that I have installed on my machine. I have tried everything. Even the instructions at (http://www.halcyonsoft.com/gq15.asp), but I still doesn't work.

So here's what I've got.

OSX 10.2.8
iASP 2.1 (works fine except for MySQL)
SQL Driver = MySQL Connector/J 3.0 (mys3_st_It.so)
MySQL 4.0.1.3 (works fine with PHP and others.)
=====================================================

Here's the connection string that I'm using:

ConnectionString="DRIVER=com.mysql.jdbc.Driver; URL=jdbc:mysql://localhost:3306/test?user=root&password=password"

set con = Server.CreateObject("ADODB.Connection")

con.open(ConnectionString)
=====================================================

Here's the error that I get:

Vbscript Runtime error 'ASP 0185 : 3219'

Invalid authorization specification: Access denied for user: 'root@localhost' (Using password: YES)

/test.asp,line 8

What am I doing wrong?? I'm using the MSQL root account so it should work? Can anyone please help me?? I'd be eternally grateful.

 

[Westside guy]

Have you tried logging in to mysql directly, with the same account (root + password), and seeing if you have access to everything (i.e. look at databases, see tables, etc.?

BTW you probably know this, but "root" for mysql is totally independent from your computer's root account. So if you're using your OS X root password that's not gonna work, unless you've set the mysql root password to match it (not a good idea).

 

[cyberwill]

Thanks for your replay

Thanks for your reply. I can log in to MySQL using a program called "CoCoaMySQL" using the root+password. under the "users" table the root user has access to everything. I'm not sure if this matters but the "tables_priv" table is completely empty. I've had no trouble access MySQL through PHP, but it's a strong possiblity that I don't have somethign set up right one of the MySQL tables.

 

[Westside guy]

Hmm. Problem is I'm reasonably good with MySQL; but I've always done it from the command line, or via DBI in perl. So I'm not familiar with the tool you're using.

I'm wondering, though, if this is "root" confusion in mysql.

Can you drop into terminal and run mysql from there? Try this:

mysql -u root -p

You'll get prompted for a password; use the one you've been using. If mysql lets you in, type "show databases;" (the semicolon is important) and see what it tells you.

Depending on what happens there, it might be worth getting out of mysql (type "exit;"). Then try this again, but this time without a password:

mysql -u root

See if you can see the database list again ("show databases").

The reason I'm suggesting this is there are too many potential sources for the problem, as things are now. So it's better to first make sure your mysql access is working correctly; then go from there.

The basic syntax for mysql is pretty straightforward. You can use the documentation on their website for reference:

http://www.mysql.com/doc/en/index.html

 

[cyberwill]

So using the terminal comands you gave me, I was able to get in using the password. When I typed in "Show database;" it came back with a listing of all of the databases that I have.

Then I exited and trying going back into MySQL without a password, but it wouldn't let me. Here's the error I got.

"ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)"

Any thoughts?? I'm fairly new to MySQL, so there's a chance I may not have installed it right. (But I downloaded the install package from www.aaronfaby.com so It prety much did everything for me.)

Thanks again for all your help.

 

[tomf87]

What database is your ASP code accessing? Your root login may be working, but may not have access to the database you are requesting. For testing purposes only, you can give the root user unlimited access and test.

EDIT:

The error you received probably means the root user's unix password and mysql password are not the same.. Am I right?

 

[Westside guy]

Originally posted by cyberwill
So using the terminal comands you gave me, I was able to get in using the password. When I typed in "Show database;" it came back with a listing of all of the databases that I have. ...

Well that sounds ok - so the next thing to do is make sure the root user has access to the data itself (which it should!). So login as root (with password) like before. Then see if you can read the data in one of the tables. If the relevant database is called XXXXX do the following (what you type is in bold):

use XXXXX;

You should see a message "Database changed".

show tables;

You should see a list of the tables inside the database. Pick one that's relevant - let's say it's a table named YYYYYY:

select * from YYYYYY;

At this point mysql should list every bit of data in that table, or else (if it's empty) tell you "empty set" or some similar message.

If you get "access denied" somewhere in this process, then we know the problem is with the mysql access. If all this works, then the mysql end of things looks to be in order; then it'll be time to check the other parts of this.

Edit: I meant to ask - are the mysql server and the Webserver running on the same machine?

 

[Westside guy]

Another thing to try

Aha. I was poking around google looking for similar problems. You may want to try this from the command line, to see if it solves it:

grant all privileges on XXXXX.* to root@localhost identified by 'password';

Where XXXXX is the database name, and of course using the real password (and yes, type it inside of single quotes).

While root by default has access to everything from the command line, that's not the case for remote connections to the mysql server - this is something I'd forgotten.

Assuming this works, you may (once everything works as planned) want to use a user other than "root", just on general principles. Just grant another user the "all privileges" for the database. Once you have everything working for that user, then you can revoke "root@localhost"s privileges.

Edit: If it's not clear - the form "XXXXX.*" means all tables belonging to database XXXXX. So be sure to type the database's name and append the "dot asterisk" just as written above.

 

[cyberwill]

Hi. I tried the mysql test and it all worked fine. It didn't deine me access. I also tried the "grant" command you sugested.
It returned "Query OK, 0 rows affected (0.00 sec)" So it seemed to work. But still when I try to run the ASP page, i get the same error.

"Vbscript Runtime error 'ASP 0185 : 3219'

Invalid authorization specification: Access denied for user: 'root@localhost' (Using password: YES)

/TMP4dz99t74j9.asp,line 8 "

I also tied making another user called testuser and running the grant command, but that didn't make a difference.

Any sugesstions?

 

[Westside guy]

aargh. I'm almost out of ideas; but i still think this is a mysql error.

Okay, let's use the mysql database. Log in as root, then:

use mysql;

and

show tables;

There should be at least a "user" table and a "db" table - "user" is permissions across all databases on this server, as well as password information; and "db" shows permissions to specific databases. Look at what users have access to what:

select * from db;

Somewhere in there you should see a host "localhost" or "%", with the username "root" and the name of the database you're trying to access. All the other columns should be "Y".

Please let me know what you see.

 

[tomf87]

What are you seeing in your mysql log? Does it state the same as the ASP error?