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.
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.
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
0/5
1
2
3
4
5
This thread is closed, so you cannot post a reply.
Comments on can't access database - any ideas?
Expert
Usergroup: Customer
Joined: Aug 19, 2005
Location: England
Total Topics: 391
Total Comments: 1303
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!
developer
Usergroup: Administrator
Joined: Dec 20, 2001
Location: Diamond Springs, California
Total Topics: 61
Total Comments: 7868
The data is in the database? Is the prefix the same as the config specifies?
Expert
Usergroup: Customer
Joined: Aug 19, 2005
Location: England
Total Topics: 391
Total Comments: 1303
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.
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.
developer
Usergroup: Administrator
Joined: Dec 20, 2001
Location: Diamond Springs, California
Total Topics: 61
Total Comments: 7868
The user didn't have permissions -- perhaps it had permissions for some things but you didn't give it all permissions.
Expert
Usergroup: Customer
Joined: Aug 19, 2005
Location: England
Total Topics: 391
Total Comments: 1303
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