0

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.

3 Answers3

0

try using

resources.db.params.host = myprocess.db

Dinuka Thilanga
  • 4,220
  • 10
  • 56
  • 93
  • Sorry, no luck I'm afraid. It actually was 'host' to begin with, but I tried 'unix_socket' and 'server' out of desperation. I'll edit the question to prevent any further confusion. –  Aug 05 '11 at 07:45
0

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"
Adrian World
  • 3,118
  • 2
  • 16
  • 25
  • Yeah, I've read that - It says to use the alias `yourprocessname.db`, which is what I'm doing. This is the page is describes: http://i.imgur.com/qK4oU.png. –  Aug 12 '11 at 23:23
  • Hmm, well it definitely works just using `processname.db` as a parameter in PHP's mysql_connect(). I've just had a look in the Zend source, and it seems to use `sqlsrv_connect($serverName, $connectionInfo);`. –  Aug 13 '11 at 01:17
  • It's my understanding that mysql_connect chooses whether to connect via a socket or TCP according to the type of the host - could it be related to that, do you think? –  Aug 13 '11 at 01:19
  • Hi, I've fixed it, and the answer's somewhat frustrating. See my post below. For the record, using the alternative adapter had no effect on the problem, but it does work interchangeably with PDO_MYSQL for everything else I've tested. Thanks for all your help. –  Aug 13 '11 at 18:22
0

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!

  • Glad you found your problem. Now I am baffled why I didn't notice the `mysql_real_escape_string()` in your error message. The answer to why this does not work on a shared server seems to be simple because on your LAMP machine you don't have more than one MySQL processes but one default process. – Adrian World Aug 13 '11 at 18:45