Connection ok to mysql, but queries fail

demeter

After 12+ hrs of troubleshooting, it's time to check with the community:

I installed Apache, php and mysql and want to get started with some code in php.

I created my first databases and tables using "Mysql Query Browser" and now want to access mysql from php.

I am able to connect to mysql with no issues ($connect = mysqli_connect("localhost", "xxxxx", "xxxxxx" or die ("no connection") results in no error message), but queries or creating new databases through php just won't work.

Anyone who can help me out of this misery?

Thanks!

PHP Version 5.2.10

System Windows NT BUPC-3J5GPD1 5.1 build 2600

Build Date Jun 17 2009 16:16:01

Configure Command cscript /nologo configure.js "--enable-snapshot-build" "--enable-debug-pack" "--with-snapshot-template=d:\php-sdk\snap_5_2\vc6\x86\template" "--with-php-build=d:\php-sdk\snap_5_2\vc6\x86\php_build" "--with-pdo-oci=D:\php-sdk\oracle\instantclient10\sdk,shared" "--with-oci8=D:\php-sdk\oracle\instantclient10\sdk,shared"

Server API Apache 2.0 Handler

Virtual Directory Support enabled

Configuration File (php.ini) Path C:\WINDOWS

Loaded Configuration File C:\WINDOWS\php.ini

mysql
MySQL Support enabled
Active Persistent Links 0

Active Links 0

Client API version 5.0.51a

Directive Local Value Master Value
mysql.allow_persistent On On
mysql.connect_timeout 60 60
mysql.default_host no value no value
mysql.default_password no value no value
mysql.default_port no value no value
mysql.default_socket no value no value
mysql.default_user no value no value
mysql.max_links Unlimited Unlimited
mysql.max_persistent Unlimited Unlimited
mysql.trace_mode Off Off

mysqli
MysqlI Support enabled
Client API library version 5.0.51a

Client API header version 5.0.51a

MYSQLI_SOCKET /tmp/mysql.sock

Directive Local Value Master Value
mysqli.default_host no value no value
mysqli.default_port 3306 3306
mysqli.default_pw no value no value
mysqli.default_socket no value no value
mysqli.default_user no value no value
mysqli.max_links Unlimited Unlimited
mysqli.reconnect Off Off

Bjom

what query and how do you try to execute it and what is the error message? here you can find ways to display the error message

demeter

hmmm, looks like even connecting results in an error.....below code results in the following output:

Test1Connect failed: Access denied for user 'wkern'@'%' to database 'vendors'

<html>
<head>
<title>My First PHP Program</title>
</head>
<body>
<?php

echo "Test1";

$link = mysqli_connect("localhost", "wkern", "xxxxxx", "vendors");

/ check connection /
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}

if (!mysqli_query($link, "SET a=1")) {
printf("Errormessage: %s\n", mysqli_error($link));
}

/ close connection /
mysqli_close($link);

echo "Test2";

</body>
</html>

Bjom

Either a typo in the username or in the password, or the user doesn't exist, or the user does not have rights (the role) to access the DB vendor. (The latter case would be solved by GRANT ... in the D😎.

Bjom

bradgrafelman

If the error message specifically says "to database..." then the user account you're trying to login in as does in fact exist but does not have permissions to use the specific database.

If you're the DBA (or have root/GRANT access), you'll need to GRANT the appropriate privileges to this database for the user account you're using.

demeter

I have granted the user all possible rights through MySQL Administrator, yet the issue persists....

Bjom

do you get a connection connecting as 'root'? (not that I advocate using it as your working db user, just to test if you can get ANY connection)

bradgrafelman

Try doing the following:

SHOW GRANTS FOR 'wkern';

Paste the output here so we can see what kind of permissions this user account has.

demeter

This is what I get when I run SHOW GRANTS FOR 'wkern'; in MySQL Query Browser. Thanks again to everyone for helping me with this!

'GRANT USAGE ON . TO 'wkern'@'%' IDENTIFIED BY PASSWORD '...' WITH….. MAX_QUERIES_PER_HOUR 1000 MAX_UPDATES_PER_HOUR 1000 MAX_CONNECTIONS_PER_HOUR 10 MAX_USER_CONNECTIONS 10'
'GRANT ALL PRIVILEGES ON mysql. TO 'wkern'@'%' WITH GRANT OPTION'
'GRANT ALL PRIVILEGES ON information\_schema. TO 'wkern'@'%' WITH GRANT OPTION'
'GRANT ALL PRIVILEGES ON test. TO 'wkern'@'%' WITH GRANT OPTION'
'GRANT ALL PRIVILEGES ON vendors.* TO 'wkern'@'%' WITH GRANT OPTION'

bradgrafelman

Dunno then... permissions look nonrestrictive as far as the database in question, as you've said.

Perhaps try doing a FLUSH PRIVILEGES command? Or even restarting the MySQL server altogether?

Bjom

Looks like the only thing left is experimenting.

Try changing the password to something failsafe like abcd and see what happens...

Try creating another DB and another user, both with very simple names using all lowercase letters - everything as plain and simple as possible. Then see what happens...

demeter

root access does not work either
FLUSH PRIVILEGES had no effect
I created a new user, simplest possible ID and password, all privileges assigned, same issue

Thanks anyways for all your hints, very much appreciated!!

Bjom

Some more wild guessing: Do you have multiple instances of mysql running and get directed to the wrong one?

Would re-installing mysql after making sure you got rid of EVERYTTHING be an option?

Your DBs can be preserved of course by backing them up...(I like MySQL Administrator for stuff like this)

Bjom