Saturday, January 2, 2010

Yii tip: CDbConnection failed to open the DB connection: SQLSTATE[HY000] [2005] Unknown MySQL server host 'localhost' (11004)' in ... CDbConnection.php:262

UPDATE:  I've been told by several people that they see this same exception but that their particular fixes differed from mine.  I think it's fair to say that this is a generic exception that gets thrown when there is an inability to connect to the MySQL server (for a variety of possible reasons).  I would suggest that if you see this exception you first check your database configuration to make sure the connection string is correct and, if it is, look at other potential causes for an inability to connect -- one of which is contained in the post below. If all fails, you may want to start your debugger up and/or put some debugging code in the framework code itself in order to see if you can get any clues as to the source of your exact problem.

I'm using Yii Framework 1.1 beta and I recently upgraded my MySQL version to 5.1. My development machine runs Windows Vista and I installed MySQL using the 5.1 msi file. When I attempted to configure my Yii application's connection to my database, located in MY_PROJECT_FOLDER/protected/config/main.php, and create a model using yiic shell, I got this error: CDbConnection failed to open the DB connection: SQLSTATE[HY000] [2005] Unknown MySQL server host 'localhost' (11004)' in ... CDbConnection.php:262

Here's my database configuration:
'db'=>array(
  'class' => 'CDbConnection',
  'connectionString'=>'mysql:dbname=mydb;host=localhost',
  'username' => 'mydbusr',
  'password' => 'mydbuserpassword',
)

Here's the full stack trace when I attempted to create the model using this database:
C:\MY_PROJECT_FOLDER\protected>yiic shell ../index.php
Yii Interactive Tool v1.0 (based on Yii v1.1rc)
Please type 'help' for help. Type 'exit' to quit.
>> model MyNewModel
exception 'CDbException' with message 'CDbConnection failed to open the DB connection: SQLSTATE[HY000] [2005] Unknown MySQL server host 'localhost' (11004)' in
C:\MY_PROJECT_FOLDER\yii-1.1rc.r1585\framework\db\CDbConnection.php:262
Stack trace:
#0 C:\MY_PROJECT_FOLDER\yii-1.1rc.r1585\framework\db\CDbConnection.php(237): CDbConnection->open()
#1 C:\MY_PROJECT_FOLDER\yii-1.1rc.r1585\framework\db\CDbConnection.php(216): CDbConnection->setActive(true)
#2 C:\MY_PROJECT_FOLDER\yii-1.1rc.r1585\framework\base\CModule.php(357): CDbConnection->init()
#3 C:\MY_PROJECT_FOLDER\yii-1.1rc.r1585\framework\base\CApplication.php(391): CModule->getComponent('db')
#4 C:\MY_PROJECT_FOLDER\yii-1.1rc.r1585\framework\cli\commands\shell\ModelCommand.php(260): CApplication->getDb()
#5 C:\MY_PROJECT_FOLDER\yii-1.1rc.r1585\framework\cli\commands\ShellCommand.php(144): ModelCommand->run(Array)
#6 C:\MY_PROJECT_FOLDER\yii-1.1rc.r1585\framework\cli\commands\ShellCommand.php(99): ShellCommand->runShell()
#7 C:\MY_PROJECT_FOLDER\yii-1.1rc.r1585\framework\console\CConsoleCommandRunner.php(62): ShellCommand->run(Array)
#8 C:\MY_PROJECT_FOLDER\yii-1.1rc.r1585\framework\console\CConsoleApplication.php(88): CConsoleCommandRunner->run(Array)
#9 C:\MY_PROJECT_FOLDER\yii-1.1rc.r1585\framework\base\CApplication.php(135): CConsoleApplication->processRequest()
#10 C:\MY_PROJECT_FOLDER\yii-1.1rc.r1585\framework\yiic.php(31): CApplication->run()
#11 C:\MY_PROJECT_FOLDER\protected\yiic.php(7): require_once('C:\MY_PROJECT_FOLDER\...')
#12 {main}

The problem baffled me for a while since I was able to connect to mysql via the command line without any issues. The resolution was a MySQL configuration. When I set up MySQL initially I locked it down as much as I could in order to prevent unauthorized people from being able to interact with my database instance. One of the steps I took, which caused problems for Yii, was disabling MySQL TCP/IP Networking -- this limited use of the database to local named pipe connections only.

In order to rectify this I ran the MySQL Instance Configuration utility (located at: MYSQL_DIR/bin/MySQLInstanceConfig.exe) and ticked the "Enable TCP/IP Networking" connection on the networking options screen (note that you need to select the "Detailed Configuration" path). If it freezes up when you attempt to execute the reconfiguration it may be a permissions issue. If you're running Vista you should right click on the MySQLInstanceConfig.exe file and select "Run As Administrator" and try the reconfiguration again.

I physically disconnected my development machine from the Internet so I'm not nearly as concerned about security. If your computer is connected to the net you may want to scrutinize changes to your configuration more carefully.

6 comments:

  1. You might also try to add your MySQL port number to the db array in the configuration file of yii.
    Example:
    'db' => array(
    'class'=>'CDbConnection',
    'connectionString' => 'mysql:host=****;port=3306;dbname=****',
    'username' => '****',
    'password' => '****'
    ),

    Worked for me!

    ReplyDelete
  2. it's means that mysql server didn't installed properly (try to use command "telnet localhost 3306", that displays something like this: "7
    5.0.15-nt☺5)noe,Jn,в!☻nZY/{D|iu5B." - my version 5.0.15, if connection timeouted - try to reinstall your mysql or downgrade)

    ReplyDelete
  3. You may also have to try the alternate of "127.0.0.1" and "localhost" if your database is on your local machine... try one if the other doesn't work.

    ReplyDelete
  4. i have some problem with this...
    if i use localhost, is cannot
    if i user 127.0.0.1, it is can work.
    but i cannot acces phpmyadmin in my browser. There error message
    ERROR 2005: Unknown MySql Server Host 'localhost' (11004).

    I looking this file : MYSQL_DIR/bin/MySQLInstanceConfig.exe
    but i cannot find this file in my xampp.
    i running xampp 1.7 with os windows7ultimate.

    Please email me for this solusion : makarim_susilo@yahoo.com

    i come from indonesia. thanks before

    ReplyDelete
  5. try to edit php.ini and enable
    extension=php_pdo.dll extension=php_pdo_mysql.dll

    ReplyDelete