Database related Q&A
1. FAQ: I can't connect to MySQL DB from the IDE. It asks to install mysql connector/C
It means you have to install this MySQL client package available from MySQL AB web site, then open Tools->Settings->External Tools, select
libmysql.dll line and enter path to libmysql.dll installed with this package. Then restart the IDE to let it take changes
in effect
NOTE: because PhpED is a 32bit application (still), you have to provide path to 32bit version of the DLL, even if you run
64bit version of Windows.
2. FAQ: I can't connect to MySQL DB from my script
Call to mysql_connect() function returns an error: mysql_connect(): Client does not support authentication protocol
requested by server; consider upgrading MySQL client
It means that you have to install appropriate mysql library and mysql php extension. Without them you can't connect to
mysql database server version 4 or higher as they use different authentication protocol. Consider upgrading php.
3. FAQ: I can't connect to MySQL, authentication error returned
Whenever I try to connect to database I get "incorrect password for username@yy.yy.yy.yy" error. I'm sure I
specified correct password for the username
If you specified password for a username using grant SQL statement and the username was entered without a hostname or
IP address, MySQL will grant rights to the user in conjunction with the address from which you were connected to the
server. For example, if you executed an SQL script from the server console or your php script and this script granted
rights to Bob user, only Bob connected from the localhost will actually be allowed. Attempt to connect from a different
address will return authentication error (invalid password).
To resolve this problem, connect to the server console, run mysql client and grant rights to either user in conjunction
with your development machine IP address or with % wildcard like shown below:
grant all on mydbname.* to 'username'@'192.168.0.5' identified by 'some_pass';
grant all on mydbname.* to 'username'@'%' identified by 'some_pass';
See MySQL documentation on GRANT page for further details.
4. FAQ: I can't connect to MySQL (error 10061)
Whenever I try to connect to database I get "Can't conect to MYSQL server on xx.xx.xx.xx (10061)" error
Error 10061 means that one of the following possible problems takes place:
-no MySQL service is running on the port you selected for in MySQL settings or
-MySQL server is NOT running or
-MySQL is blocked by firewall either on your local machine or on the server or
-MySQL is bound to localhost while you're trying to connect to it using LAN/WAN IP address or hostname
- Make sure mysql service is running
- Make sure mysql is listening on TCP/3306 port and this port is selected for the DB account in PhpED.
- Make sure firewalls on your local machine and on the server permit connection to the service.
- Make sure mysql service is bound to the address that you're trying to reach it at
- Consider using SSH tunnel to connect to the database
The following commands will show you all listening sockets (and daemons PIDs) running on your computer.
Windows way:
netstat -naop TCP
Linux/unix way:
netstat -na|grep tcp
For example an output like below ensures that the process 932 is listening on port 3306 and bound to 0.0.0.0 (meaning ALL) address:
Proto Local Address Foreign Address State PID
TCP 0.0.0.0:135 0.0.0.0:0 LISTENING 1560
TCP 0.0.0.0:445 0.0.0.0:0 LISTENING 4
TCP 0.0.0.0:1025 0.0.0.0:0 LISTENING 1768
TCP 0.0.0.0:1031 0.0.0.0:0 LISTENING 4
TCP 0.0.0.0:3306 0.0.0.0:0 LISTENING 932
Then open Task Manager, enable PID column and see what process name corresponds to the PID (932 in my example).
|