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.