Mysql

Zend Server Mysql Socket problem with PDO [Solved]

Zend Server Mysql Socket problem with PDO [Solved]

Submitted by shane on Thu, 2010-04-29 20:16

The Problem

This answer has eluded me for months. I have Zend Server CE installed on my Mac (and linux web servers). I would use a DSN similar to the following (please note that my real password is not 'root').

mysql://root:root@localhost/thedatabase

This would result in an error in a PHP Fatal error of 

'Can't connect to local MySQL server through socket '/tmp/mysql.sock''

This drove me mad looking for an answer. I had not configured it to connect through a socket, why was it doing that? To add more injury to this insult, if I tried to connect to Zend Server's mysql socket (/usr/local/zend/mysql/tmp/mysql.sock) I would still get the same error message.

The Best Solution

It's as simple as specifying 127.0.0.1 in replace of localhost. I guess if you do this, you force PDO to use a TCP connection.

Solution Alternative

I haven't tried this, but you could also symlink from 

/tmp/mysql.sock

to 

/usr/local/zend/mysql/tmp/mysql.sock

.

I'm not a big fan of symlinks that reach outside of the application.

Cause

It seems there is a bug in PDO PHP 5.2.x that is not fixed until 5.3.x.

Resources:
http://forums.zend.com/viewtopic.php?f=44&t=568

Concatentate data from a query into a single row (Transpose)

Concatentate data from a query into a single row (Transpose)

Submitted by shane on Thu, 2010-04-15 06:00

Let's say you have a query that returns this

cert_id
---------
100
200
300
500
600

What I want is to have it return this in a query

cert_id
----------
100,200,300.500,600

After much searching on the topic of transpose, with some complex solutions offered, I stumbled onto the GROUP_CONCAT function in Mysql.
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_...

So my query looks like this 

        /* Show Gift Cert nubmers */
        (
          SELECT GROUP_CONCAT(cert_id) FROM ugiftcert_history uh
          WHERE so.entity_id = uh.order_id
          GROUP BY uh.order_id
        ) AS gc_numbers,

Onto greatness.

Weird characters after mysqldump export and import on new server

Weird characters after mysqldump export and import on new server

Submitted by shane on Tue, 2009-10-13 19:10

The other day I had to migrate a Wordpress database from one server to the other. I used mysqldump to export the SQL and data from the old server.

Then I used this to import

mysql -uusername -ppassword database < backup.sql

This went fine, but I found I was seeing weird characters in the front end and through phpmysqladmin. The "Collation" on both tables seemed the same.

Then I stumbled on a website that offered this change when importing

mysql -uusername -ppassword --default-character-set=utf8 database < backup.sql

Syndicate content