This note is OBSOLETE. Newer versions of the application no longer require a direct connection to the MySQL database server (as previous version required).
Hi everybody. I will try to explain the most common problems when connecting to the MySQL database server and how to solve them.
If you are the MySQL admin, note that most checkings can be done from the phpMyAdmin web interface (which may be enabled in your DB server).
What are my database server parameters?
If you installed/configured your own osCommerce server you already have that information.
If you don't remember or are not sure, check the osComerce file catalog/includes/configure.php. Near the end of the file there are the following constants: DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD and DB_DATABASE, which correspond to the following OSCPMWin options (in the Configuration dialog, Database section): Host, Username, Password and Database. The default MySQL port is 3306.
What if the hostname is "localhost"?
The hostname to be entered in the OSCPMWin configuration should be the server name as seen by other computers. Find out what is the name of the host (maybe www.yourstoredomain.xxx?) If in doubt, ask your service provider or server admin.
Also, check that your user is configured to be used both from localhost and from the server name. If in doubt, ask the server admin, or, if you are the admin, check the permission table of MySQL (check the Host field of the user).
What to use as port number?
The port number that you can configure in the OSCPMWin application is usually 3306. If the host name (as configured in osCommerce) includes a ":xxxx number" that is the port number. If there is none, use the default (3306).
If the hostname includes a path (like :/tmp/mysql.sock or something) then the osCommerce store is connected thru a local socket, which cannot be used by the OSCPMWin application. Find out if the DB server can receive connections thru TCP.
How to check if the TCP connectivity of the MySQL server?
Use a telnet application (like the one included with Windows). In the command prompt invoke the command "telnet hostname 3306", where hostname is the name of the MySQL server.
a) If you receive a message like "cannot open a connection to the host at port 3306: connection error" then you cannot establish a link to the DB server. Maybe there is a firewall or simply the MySQL server is configured to not accept TCP connections. If MySQL is using a different port number you would get this same error. Also, this can happens if you are using the wrong hostname. In the application, this problem is usually displayed as a 10060 error.
b) If you receive a message like "P*Host 'yourcomputer' is not allowed to connect to this MySQL server" where "yourcomputer" is the name of your Windows workstation, then MySQL is configured to deny remote connections (or at least, remote connections from your workstation).
c) If you get a message like "4.0.15*******" then your MySQL server can receive TCP connections from your computer.
In case a) you should check with the network admin (maybe that's yourself) if there is something blocking the connection. Check for firewalls (including local firewalls in your computer). Check the connection from within the server itself (if you have access). Check if the server has several names, test using the IP address.
In case b) you need to reconfigure MySQL (or ask the server admin) to allow remote connections from your computer. If you cannot do this, check if you have an SSH account in the server and try with SSH tunneling enabled in the OSCPMWin. (With the SSH tunnel the server sees your connection as local).
In case c) you should be able to connect from the OSCPMWin application. Just a note, even if you are using a 4.0.x MySQL server, select the protocol version 3.23; there is a bug when choosing 4.0 (you would see the message "Requested database driver was not found").
How to check if I have a SSH account?
If you are using a shared hosting service maybe your provider gives you SSH access; check the features of your hosting service or ask them.
If you are using a colocation service then you for sure has several SSH accounts.
If you are using your own servers (or your company's) then you for sure has one or several SSH accounts. If you are not sure (because you are not the admin) ask the server admin to provide you with an SSH account.
If your server admins have any concerns about giving you SSH access to the server, explain them that you will only use it for tunneling your MySQL connection, that the login shell can be changed to a dummy shell so nobody will run remote commands with that account (refer them to this page: http://www.mariovaldez.net/software/sleepshell/).
The version 0.1.0.316 of the OSCPMWin application has a very silly bug when trying to use the SSH tunnel. It was expected that you already has made a tunnel with the puTTY applications, so it would fail if you have never used puTTY with your server. I have uploaded a new version with a "TEST SSH" button in the configuration dialog to let you 1) test your SSH parameters and 2) confirm manually the SSH connection when doing that for the first time.
Note that OSCPMWin depends on PLINK (which is included with OSCPMWin), a puTTY SSH open-source application to create the SSH tunnel to the server. For increased security all puTTY programs ask you the first time you connect to a remote SSH server if you are sure you trust the remote server, and you should type "y". So far I have not automated this step, so use the new "TEST SSH" button in the configuration window (available since version 0.1.0.319).
I receive a message like "Access denied" or something like that.
Your user is not allowed to connect from your workstation. Most MySQL servers are configured by default to allow connection only from the same computer they are running. Remote connections should be enabled in a per-user basis.
The solutions are 2:
1) Ask the server admin to enable your user as remote, or, if you are the server admin, change the Host field of the user permission record to "%" or to the name of your workstation.
2) Use the SSH tunnel. As explained before, the server will see your connection as coming from the same server, allowing you to connect.
What if I cannot use a direct DB connection?
There is no solution for this (today). I'm aware that this is a problem for many users using shared hosting services. I'm working on it.
I'm designing a HTTP interface to do the DB transactions as an alternative to the direct MySQL protocol. But that is not still ready.
NOTE: There is currently a test version which does not require direct database connections. For more details, visit: http://www.mariovaldez.net/webapps/forums/viewtopic.php?t=294.
Where is the documentation?!
There is none today. I understand your feelings (I'm a software user myself). I will write the docs and hope to release them by December. I cannot stress enough that this is a preview version; think of it as an alpha or beta version. Please bear with me in the mean time.
Originally I expected to release the software until December, but my users were so excited that I couldn't keep it for ourselves any longer.
I will try to help you all in these forums as much as I can.
If you have any questions, comments or suggestions, please post to the forum.
Regards,
Mario A. Valdez-Ramirez.