Connecting to SQL process with Zend

Question

I'm trying to connect my Zend application to a MySQL process running on a shared server. The basic config should be fine, as it was working with a LAMP server.

The problem is, I need to specify the host as being the an sql process: myprocess.db, rather than localhost:

resources.db.adapter = PDO_MYSQL
resources.db.params.charset = "utf8"
resources.db.params.host = mysqlprocess.db
resources.db.params.username = username
resources.db.params.password = password
resources.db.params.dbname = dbname

However, when I do, I get this:

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: 
Can't connect to local MySQL server through socket 'please_see_the_faq' (2) 
in /f5/metamusic/protected/application/controllers/SearchController.php on line 418

The host I'm using is NearlyFreeSpeech, and this message is apparently triggered when attempting to connect to SQL without specifying the process you're interested in: http://faq.nearlyfreespeech.net/section/mysql/mysqllocalhost#mysqllocalhost

Using the same details and mysql_connect($server, $user) works without issue, so it looks like Zend is somehow not using the correct host parameter.

Any ideas what's going wrong? Any help would be much appreciated.

Answer 1

I'm posting my eventual solution here for future reference:

It turns out, the database connection was already working. However, my call to mysql_real_escape_string() was failing, and the resulting error message suggested that the entire database connection had failed.

The solution was simply to replace the above call with Zend_DB_Adapter's quote(), and suddenly everything works.

Why this works on a LAMP machine and not a shared server, I have no idea. For now though, this is a good enough solution!

Answer 2

The host in the db config has to point to a database server. localhost or 127.0.0.1 are references for the database being on the same server as the application. In a hosting environment you usually have the server on a remote server so the host has to be either an IP address or a DNS name for the host.

Check the second question in the FAQ.

Update
My bad, that is about DSN and not DNS. Still, that's where the problem is. The resources.db.params.host directive in the config expects a reference to the database server and myprocess.db is neither a DNS name nor a IP address. You probably need localhost for that but then you will still be missing the DSN. I currently don't see how you set a DSN in PHP for MySQL and therefore Zend. Have a further look at this MYSQL DSN.

Update 2 You are correct with the socket and that this is related. I think the problem is the Zend PDO_MYSQL adapter. Zend funnels this directly to PDO(). There are this additional config options I mentioned above (MYSQL DSN) which is missing in the Zend implementation. Although the PDO_MYSQL adapter overrides the connect() method it does not look for this options.

However, there is another adapter mysqli which connects directly to MySQL and actually the same way as your test with mysql_connect(). It uses mysqli_real_connect() instead and that connection might understand the process name for the socket. So, you can try the following in your config:

resources.db.adapter = "mysqli"

Answer 3

try using

resources.db.params.host = myprocess.db