Database connection issue

slimandslam · January 11, 2010, 8:39pm

Just trying to do basic stuff with the Yii 1.1 shell:

>> model User tbl_user

Warning: PDO::__construct(): [2002] No such file or directory (trying to connect via unix:///tmp/mysql.sock) in /htdocs/yii/framework/db/CDbConnection.php on line 297

exception ‘CDbException’ with message ‘CDbConnection failed to open the DB connection: SQLSTATE[HY000] [2002] No such file or directory’ in /htdocs/yii/framework/db/CDbConnection.php:262

Stack trace:

#0 /htdocs/yii/framework/db/CDbConnection.php(237): CDbConnection->open()

#1 /htdocs/yii/framework/db/CDbConnection.php(216): CDbConnection->setActive(true)

#2 /htdocs/yii/framework/base/CModule.php(357): CDbConnection->init()

#3 /htdocs/yii/framework/base/CApplication.php(391): CModule->getComponent(‘db’)

#4 /htdocs/yii/framework/cli/commands/shell/ModelCommand.php(260): CApplication->getDb()

#5 /htdocs/yii/framework/cli/commands/ShellCommand.php(144): ModelCommand->run(Array)

#6 /htdocs/yii/framework/cli/commands/ShellCommand.php(99): ShellCommand->runShell()

#7 /htdocs/yii/framework/console/CConsoleCommandRunner.php(62): ShellCommand->run(Array)

#8 /htdocs/yii/framework/console/CConsoleApplication.php(88): CConsoleCommandRunner->run(Array)

#9 /htdocs/yii/framework/base/CApplication.php(135): CConsoleApplication->processRequest()

#10 /htdocs/yii/framework/yiic.php(31): CApplication->run()

#11 /htdocs/yii/pitch/protected/yiic.php(7): require_once(’/Applications/M…’)

#12 /htdocs/yii/pitch/protected/yiic(4): require_once(’/Applications/M…’)

#13 {main}

>>

My connection string for mysql is configured in protected/config/main.php:

‘db’=>array(

                    'connectionString' =&gt; 'mysql:host=localhost;dbname=yiidb1',


                    'emulatePrepare' =&gt; true,


                    'username' =&gt; 'root',


                    'password' =&gt; 'root',


                    'charset' =&gt; 'utf8',


            ),

The database exists and has a tabled named tbl_user in it.

Why doesn’t this work?

Y11 · January 11, 2010, 8:52pm

When the mysql:host is set to "localhost", PDO is trying to connect to the mysql server through unix socket. If your socket location is wrong for some reason, you get that error message. Either define the correct path to the socket, eg:


'connectionString' => 'mysql:host=localhost;dbname=yiidb1;unix_socket:/path/to/socket/mysql.sock',

or set mysql:host to 127.0.0.1 in order to connect through tcp.

// To fully fix the problem you should write the correct socket info into the php.ini. Then you don’t have to do it in the connection string.

slimandslam · January 11, 2010, 9:14pm

Y!!:

When the mysql:host is set to "localhost", PDO is trying to connect to the mysql server through unix socket. If your socket location is wrong for some reason, you get that error message. Either define the correct path to the socket, eg:
'connectionString' => 'mysql:host=localhost;dbname=yiidb1;unix_socket:/path/to/socket/mysql.sock',
or set mysql:host to 127.0.0.1 in order to connect through tcp.

// To fully fix the problem you should write the correct socket info into the php.ini. Then you don’t have to do it in the connection string.

Changed it to: ‘connectionString’ => ‘mysql:host=localhost;dbname=yiidb1;unix_socket:/Applications/MAMP/tmp/mysql/mysql.sock’,

Still failing:

>> model User tbl_user

Warning: PDO::__construct(): [2002] No such file or directory (trying to connect via unix:///tmp/mysql.sock) in /Applications/MAMP/htdocs/yii/framework/db/CDbConnection.php on line 297

exception ‘CDbException’ with message ‘CDbConnection failed to open the DB connection: SQLSTATE[HY000] [2002] No such file or directory’ in /Applications/MAMP/htdocs/yii/framework/db/CDbConnection.php:262

==

Y11 · January 11, 2010, 9:19pm

Sorry my fault. Should be unix_socket= instead of unix_socket:

If that works open php.ini and add/modify:


mysql.default_socket = "/Applications/MAMP/tmp/mysql/mysql.sock"

isreal · August 9, 2010, 3:10am

I tried this but not succesful, someone has this on the correct way?

thanks

ndusan · December 28, 2010, 3:54pm

Hi,

I have the similar problem. If I put in php.ini default_socket it’s working for the other things in my localhost, but for example if I’m trying to run doctrine from console (in yii) it’s showing me warning (that default_socket is not defined). So I have to go and write into config/main.php default_socket like above.

Is there some better solution then this hard code, because if I start sharing this project with someone who is running this on Win, path will be different and he will end up in problem.

Regards,

/Dusan

trejder · December 28, 2010, 10:11pm

Quite another advice, but strongly off-topic. Consider changing your server package from MAMP to XAMPP (here’s an article about it). I’ve been using it for years and have no problems with using Yii and MySQL database on localhost, both on Windows or Linux. So, it seems it is correctly pre-configured to use valid socket and no error messages ever appears.

ndusan · December 29, 2010, 7:59am

Ok, I found a solution. The thing is that on MAMP it’s looking for default_socket in /tmp/mysql.sock (this is default on other as well (WAMP, LAMP, XAMPP)).

In this case on MAMP it’s set in /Applications/MAMP/tmp/mysql/mysql.sock, so if you just open console and make link: sudo ln -s /Applications/MAMP/tmp/mysql/mysql.sock /tmp/mysql.sock you will be able to fix this problem, because now there is link between

Case closed

Of course delete things that were added before, so your connectionString should be like this:

‘connectionString’ => ‘mysql:host=localhost;dbname=yiidb1’,

trejder · December 29, 2010, 9:02am

Are you 100% sure that the connection you manually made isn’t setup by default on other servers? I never heard about any problems on XAMPP, when application is connecting to MySQL database, neither on Windows nor on Linux. Therefore I would be a little bit sceptical on saying that this problem remains the same on other server packages. Are you really sure that not having this connection set by default (what causes this problem) isn’t only MAMP feature? :]

ndusan · December 29, 2010, 9:12am

Hi,

I’m using Mac, so this is a problem that I have on MAMP

So this solution is only for MAMP users. And it’s true that it’s working ok on WAMP, LAMP and XAMPP

Regards,

/Dusan

FlyingMongoose · August 27, 2013, 4:26pm

I know this is a bit of a “Necro post” and as a new member I probably shouldn’t, however, I ran into this issue big time and it kept getting in the way of me moving forward. This resolution may work on MAMP as well, but I got it on XAMPP, the MySQL user I was using actually was set for accepting connections from “everywhere” a.k.a. % unfortunately, “everywhere” for some reason on a Mac doesn’t include 127.0.0.1 or localhost. I changed the MySQL to only accept localhost and it started working just fine.

I did still have to set up my connectionString as such though for XAMPP:


'connectionString' => 'mysql:host=127.0.0.1;dbname=DBNAME;unix_socket=/Applications/XAMPP/xamppfiles/var/mysql/mysql.sock',

If you don’t know your MySQL Socket path, check a phpinfo(); page.

Also, because XAMPP and MAMP seem to have a habit of enforcing the system time setting into the date time, I had to add this to the top of main.php to prevent other errors when attempting to launch the yiic shell




// Fix for server incompatibilities

date_default_timezone_set('UTC');

Honestly, if you wish to distribute, it might be worthwhile placing this in your main.php file anyway, and perhaps allowing the user to set it during install (since so manny different web servers differ so much out there).

pdatar · March 4, 2014, 10:25am

Hey Dusan,

I followed your instructions on setting the symbolic link from /tmp/mysql.sock to /Applications/MAMP/tmp/mysql/mysql.sock . It wasn’t rightly in the /tmp/ location so if the CDbConnection was going there to look for a file it makes sense for the error I’m getting to come up.

Here’s my error again




[error] [exception.CDbException] exception 'CDbException' with message 'CDbConnection failed to open the DB connection: SQLSTATE[HY000] [2002] No such file or directory' in /Users/pdatar/yii/framework/db/CDbConnection.php:382

So I’m not sure what’s left to. I’ve made sure I have a connection string. The other striking thing is that the same script seems to work without any problems in the web application. So I’m still putting my money on the fact that this is some sort of environment issue or a Yii configuration issue.

I’m on a Mac OS X 10.9 OS

Using MAMP for my PHP and MySQL.

PHP version is 5.

EDIT: Funnily enough after I wrote this post, I changed my connection string to the following




'connectionString'=>'mysql:host=127.0.0.1:8889;dbname=*****',

And that seemed to do the trick. Once again this is on a Mac with a MAMP setup. The web application works fine even if there’s localhost in the connection string. But it seems like the console application needs a bit more specific information.

Hope someone finds this useful

OnaNixon · April 12, 2023, 1:34pm

Thank you !!! i tried to solve the problem using TCP. and i did it successfully
i think the simple solution is to change the host from localhost to 127.0.0.1