Error On Using Db Migration W/ Mysql

Aninemity · September 12, 2013, 7:55pm

Hi All

I’m still learning Yii, and am hitting a roadblock. I’m trying to figure out the db Migration tools, and have this happen:


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

Stack trace:

#0 /Applications/MAMP/htdocs/yii-sandbox/framework/db/CDbConnection.php(330): CDbConnection->open()

#1 /Applications/MAMP/htdocs/yii-sandbox/framework/db/CDbConnection.php(308): CDbConnection->setActive(true)

#2 /Applications/MAMP/htdocs/yii-sandbox/framework/base/CModule.php(387): CDbConnection->init()

#3 /Applications/MAMP/htdocs/yii-sandbox/framework/cli/commands/MigrateCommand.php(442): CModule->getComponent('db')

#4 /Applications/MAMP/htdocs/yii-sandbox/framework/cli/commands/MigrateCommand.php(451): MigrateCommand->getDbConnection()

#5 /Applications/MAMP/htdocs/yii-sandbox/framework/cli/commands/MigrateCommand.php(482): MigrateCommand->getMigrationHistory(-1)

#6 /Applications/MAMP/htdocs/yii-sandbox/framework/cli/commands/MigrateCommand.php(84): MigrateCommand->getNewMigrations()

#7 [internal function]: MigrateCommand->actionUp(Array)

#8 /Applications/MAMP/htdocs/yii-sandbox/framework/console/CConsoleCommand.php(172): ReflectionMethod->invokeArgs(Object(MigrateCommand), Array)

#9 /Applications/MAMP/htdocs/yii-sandbox/framework/console/CConsoleCommandRunner.php(71): CConsoleCommand->run(Array)

#10 /Applications/MAMP/htdocs/yii-sandbox/framework/console/CConsoleApplication.php(92): CConsoleCommandRunner->run(Array)

#11 /Applications/MAMP/htdocs/yii-sandbox/framework/base/CApplication.php(180): CConsoleApplication->processRequest()

#12 /Applications/MAMP/htdocs/yii-sandbox/framework/yiic.php(33): CApplication->run()

#13 /Applications/MAMP/htdocs/yii-sandbox/projects/trackstar/protected/yiic.php(7): require_once('/Applications/M...')

#14 /Applications/MAMP/htdocs/yii-sandbox/projects/trackstar/protected/yiic(4): require_once('/Applications/M...')

I’m using MAMP. have set my main.php and console.php settings to the following:


		'db'=>array(

			'connectionString' => 'mysql:host=localhost;dbname=yii_trackstar',

			'emulatePrepare' => true,

			'username' => 'yii',

			'password' => '######',

			'charset' => 'utf8',

		),

In researching this, i’ve seen suggestions of changing localhost to 127.0.0.1 and for adding a unix socket command to the end of the connectionString but neither work.

I’ve checked the db and user settings, and in fact could connect via the main.php settings through the app itself. I’m a bit lost on this one… any help?

nineinchnick · September 12, 2013, 8:56pm

When connecting to MySQL if you specify host by ‘localhost’ it is treated as a switch to unix socket. Now a unix socket is a special file. It’s location is specified in the my.cnf file. I don’t use MySQL much but this error could refer to it.

There are many sections in the my.cnf file as it is used both by the server and all clients. Search for a [client] section and check the path there.

When you change ‘localhost’ to ‘127.0.0.1’ the driver will use a TCP socket, but the server could have it disabled for security reasons. It should listen only on 127.0.0.1 not to allow external connections or a good firewall should be installed and proper passwords set on superuser accounts.

Now this all doesn’t matter if your db component configuration is the same in both main.php and console.php and in the webapp the connection works. Maybe it has something to do with user permissions, the web server and CLI version of PHP may run under different users.

I would advise to check one thing at a time, maybe start with the TCP socket. Don’t rush into it and revert changes if it doesn’t resolve the issue. It’s good to know how services interact with each other, the apps we’re all writing sometimes do the same.

Aninemity · September 12, 2013, 10:47pm

Thanks for the advice nineinchnick.

I’ve checked the path on the unix socket compared to the my.cnf file and that is right and I can’t see any change to my error when I add it.

When I change localhost to 127.0.0.1, the error changes to ‘connection refused’ instead of ‘No such file or directory’.

I’m not sure about the TCP socket, or where to check it really. I’m working solely on a localhost at the moment, and the firewall is currently off, so that shouldn’t be impacting anything.

That’s what doesn’t make sense to me. As far as I’m aware I’ve checked the user permissions for the database and everything seems to be correct. I’m not sure, but what strikes me here is the error message is ‘No such file or directory’. If I change the password in the console.php file so that it’s incorrect, I get the ‘connection refused’ error. That makes me think it’s connecting, but just not running something.

I’ve been fighting this all day and don’t feel like i’ve made any progress. I’m not sure what else to check?

nineinchnick · September 13, 2013, 6:34am

A quick search on google revealed this. You got two php.ini files in two locations:

/etc/php.ini
/Applicaton/MAMP/:bla:/php.ini

The CLI version of PHP uses the first one and web app uses second. Compare them using your favourite diff tool and look for MySQL socket path.

They should look like this:




mysql.default_socket = /var/run/mysqld/mysqld.sock

mysqli.default_socket = /var/run/mysqld/mysqld.sock

pdo_mysql.default_socket = /var/run/mysqld/mysqld.sock

Remember to copy values from the working one to the other, check and revert if it doesn’t help.

Aninemity · September 13, 2013, 3:03pm

Thanks - I think I’ve made some progress.

Okay, I have a file in /ect/php.ini.default but not just php.ini

Looking at that file compared to the mamp php.ini, the /ect/ file lists pdo_mysql.default_socket=/var/mysql/mysql.sock

the directory /var/mysql/ doesn’t exist. I’ve tried replacing it with the MAMP one, which is pdo_mysql.default_socket=/Applications/MAMP/tmp/mysql/mysql.sock and it doesn’t work still.

That put me on the right path though, and I found this: db connection string which worked.

thanks for your help (and patience)! I’m still learning some of the terminal stuff and so it’s very new to me!

nineinchnick · September 13, 2013, 5:51pm

I didn’t know you can pass unix socket path in the connection string Anyway, if you issue the command:




php --ini

it will tell you which ini files it is using. If it points to /etc/php.ini but it doesn’t exist and there’s only /etc/php.ini.default I would assume you could copy it to /etc/php.ini and then apply your modifications.

kartikv · September 29, 2013, 7:27pm

The MAMP server uses a different php executable and setting. I suppose you are running [font="Courier New"]yiic migrate[/font] command directly (if so this will use the php executable from your PATH settings like /usr/bin which maybe different than the MAMP version and its ini settings). Instead try this command:

[font="Courier New"]$MAMP_PHP/bin/php yiic.php migrate[/font]

where [font="Courier New"]$MAMP_PHP[/font] is the location of your MAMP PHP install… for example [font="Courier New"]/Applications/MAMP/bin/php/php5.5.3[/font]

I would typically add an alias to my [font="Lucida Console"].bash_profile[/font] for such needs. For example:

[font=“Courier New”]alias php_mamp = ‘/Applications/MAMP/bin/php/php5.5.3/bin/php’[/font]

So you can directly run commands using this alias like:

[font="Courier New"]php_mamp yiic.php migrate[/font]

AlanCooper · October 2, 2013, 2:48pm

I must not understand what is the problem

rereadyou · June 17, 2014, 7:26am

nineinchnick:

When connecting to MySQL if you specify host by ‘localhost’ it is treated as a switch to unix socket. Now a unix socket is a special file. It’s location is specified in the my.cnf file. I don’t use MySQL much but this error could refer to it.

There are many sections in the my.cnf file as it is used both by the server and all clients. Search for a [client] section and check the path there.

When you change ‘localhost’ to ‘127.0.0.1’ the driver will use a TCP socket, but the server could have it disabled for security reasons. It should listen only on 127.0.0.1 not to allow external connections or a good firewall should be installed and proper passwords set on superuser accounts.

Now this all doesn’t matter if your db component configuration is the same in both main.php and console.php and in the webapp the connection works. Maybe it has something to do with user permissions, the web server and CLI version of PHP may run under different users.

I would advise to check one thing at a time, maybe start with the TCP socket. Don’t rush into it and revert changes if it doesn’t resolve the issue. It’s good to know how services interact with each other, the apps we’re all writing sometimes do the same.

Many Thanks, this works for me on YII2.

ihilliard · June 19, 2014, 10:43am

My Macbook produced the same error as above as the MacOS has another version of php installed. To get MAMP and Yii2 working it is necessary to define a few more variables in the connection string such as the port and which mysql.sock to use.

I’m on MAMP3 + Yii2+ Mavericks, this code works…

‘components’ => [

    'db' =&gt; [


        'class' =&gt; 'yii&#092;db&#092;Connection',


        'dsn' =&gt; 'mysql:host=localhost;port=8889;dbname=mydbname;unix_socket=/Applications/MAMP/tmp/mysql/mysql.sock',


        'username' =&gt; 'myuser',


        'password' =&gt; 'mypassword',


        'charset' =&gt; 'utf8',


    ],

Hope its useful !

Daisyontas · August 19, 2014, 10:15am

How to crack this application ?

davidnewcomb · February 10, 2015, 9:06pm

There is a more fuller article here describing why it is happening, and what you can do to keep your configuration the same. It also talks about the consequences of using 127.0.0.1 instead of localhost:

Yii migration tool no such file or directory

alex-ks · May 11, 2015, 12:21pm

Ian Hillliard:

My Macbook produced the same error as above as the MacOS has another version of php installed. To get MAMP and Yii2 working it is necessary to define a few more variables in the connection string such as the port and which mysql.sock to use.

I’m on MAMP3 + Yii2+ Mavericks, this code works…

‘components’ => [
    'db' =&gt; [


        'class' =&gt; 'yii&#092;db&#092;Connection',


        'dsn' =&gt; 'mysql:host=localhost;port=8889;dbname=mydbname;unix_socket=/Applications/MAMP/tmp/mysql/mysql.sock',


        'username' =&gt; 'myuser',


        'password' =&gt; 'mypassword',


        'charset' =&gt; 'utf8',


    ],
Hope its useful !

Thanks!

This part of the line helped me:

;unix_socket=/Applications/MAMP/tmp/mysql/mysql.sock’

Kabinenkoffer · August 26, 2015, 7:22am

I hade the same issue. This worked for me. The host part was the solution. (MAMP 3, Yii2, OSX 10.9.5)

i’m also using an alias like

alias phpmamp=’/Applications/MAMP/bin/php/php5.6.10/bin/php’

in .bash_profile


'db' => [

            'class' => 'yii\db\Connection',

            'dsn' => 'mysql:host=localhost;port=8889;dbname=mydbname',

            'username' => 'myuser',

            'password' => 'mypassword',

            'charset' => 'utf8',

        ],

andrewrice · February 21, 2016, 9:34pm

Thanks a mill, simply adding the port=8889; bit fixed the issue!