Webmastersite.net
Register Log In

can't access database - any ideas?

Comments on can't access database - any ideas?

babrees
Expert

Usergroup: Customer
Joined: Aug 19, 2005
Location: England

Total Topics: 391
Total Comments: 1303
babrees
Posted Dec 27, 2006 - 9:53 AM:

I have just moved all my sites to a new server. All went smoothly and databases are working fine, except one!!

This is the error message I get...



Cannot extract data from the database.
It seems that although we established a database connection we aren't able to access the database itself. It could be that the database is down (ask your web host about this), or that the mysql user doesn't have proper permissions to acces the database. MySQL returns this error: 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 '=1 AND =1' at line 1


I have checked and checked and checked that everything is correct. I have even deleted all files, databases, users and started afresh and it still won't connect to the database. Yes I have granted user permissions and I have checked for typos and anything else. I can't find anything wrong.

HELP! Anybody any ideas? And just with one site!
Paul
developer

Usergroup: Administrator
Joined: Dec 20, 2001
Location: Diamond Springs, California

Total Topics: 61
Total Comments: 7867
Paul
Posted Dec 27, 2006 - 11:19 AM:

The data is in the database? Is the prefix the same as the config specifies?
babrees
Expert

Usergroup: Customer
Joined: Aug 19, 2005
Location: England

Total Topics: 391
Total Comments: 1303
babrees
Posted Dec 27, 2006 - 12:43 PM:

Paul wrote:
The data is in the database? Is the prefix the same as the config specifies?


yes and yes

I also contacted my hosts tech support in case it was an issue with the site. They managed to fix it and this was their reply, which tbh I don't understand <G> but it might throw some sort of light for somebody else? I KNOW permissions were set correctly as I thought that was the problem so kept looking there, even resetting everything. my username was kritters_jill and I don't understand why it wanted localhost too.

When I went to the page it was giving off errors claiming that it can connect to the database, but then has issues. The MySQL error sheds some light on the issue:

MySQL returns this error: No database selected

Something in your code was either never selecting a database prior to running queries, or was attempting to select the database and did not have the proper permissions. It appeared to be permissions.. here is a copy/paste of my attempt to replicate a connection to the database:

--BEGIN PASTE--
root@helium [/home/kritters/public_html]# mysql -ukritters_jill -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1189769 to server version: 4.1.21-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use kritters_data;
ERROR 1044 (42000): Access denied for user 'kritters_jill'@'localhost' to database 'kritters_data'
mysql>
--END PASTE--

I have gone ahead and added 'kritters_jill'@'localhost' identified by the password you gave as having permission to ALL on kritters_data. If you want the user to have more restrictive permissions you'll need to get in there and re-do it, otherwise, it is now working.


Paul
developer

Usergroup: Administrator
Joined: Dec 20, 2001
Location: Diamond Springs, California

Total Topics: 61
Total Comments: 7867
Paul
Posted Dec 28, 2006 - 2:08 PM:

The user didn't have permissions -- perhaps it had permissions for some things but you didn't give it all permissions.
babrees
Expert

Usergroup: Customer
Joined: Aug 19, 2005
Location: England

Total Topics: 391
Total Comments: 1303
babrees
Posted Dec 28, 2006 - 2:51 PM:

Well, that's what I thought - but I had granted ALL permissions - double, triple, quadruple checked. Even deleted database and user and began again but still had the problem. weird
Search thread for
Download thread as
  • 0/5
  • 1
  • 2
  • 3
  • 4
  • 5



This thread is closed, so you cannot post a reply.