Symfony+Propel:设置连接排序规则

Question

我使用Symfony 2.1.6框架，并通过一个MySQL服务器5.5.28将Propel 1.6.7作为数据库ORM集成在一起，并且我在将数据库排序设置为utf8_unicode_ci时遇到了问题。

我的app/config/config.yml

...
propel:
    dbal:
        driver:               %database_driver%
        user:                 %database_user%
        password:             %database_password%
        dsn:                  "%database_driver%:host=%database_host%;dbname=%database_name%;charset=%database_charset%"
        options:
            MYSQL_ATTR_INIT_COMMAND: "SET NAMES utf8 COLLATE utf8_unicode_ci"
        attributes:           {}

(参数已在app/config/parameters s.yml中定义)

如果没有options，它可以工作，但是对于字符集问题(MySQL默认为utf8_general_ci排序规则，但数据库模式是用utf8_unicode_ci定义的)。如果我像上面那样使用options，那么Propel/Symfony会引发一些例外：

in /var/www/vendor/propel/propel1/runtime/lib/Propel.php at line 725  -+
at ErrorHandler ->handle ('2', 'Illegal string offset 'value'', '/var/www/vendor/propel/propel1/runtime/lib/Propel.php', '725', array('source' => array('MYSQL_ATTR_INIT_COMMAND' => 'SET NAMES utf8 COLLATE utf8_unicode_ci'), 'write_to' => array(), 'option' => 'MYSQL_ATTR_INIT_COMMAND', 'optiondata' => 'SET NAMES utf8 COLLATE utf8_unicode_ci', 'key' => '1002'))
in /var/www/vendor/propel/propel1/runtime/lib/Propel.php at line 725  -+
at Propel ::processDriverOptions (array('MYSQL_ATTR_INIT_COMMAND' => 'SET NAMES utf8 COLLATE utf8_unicode_ci'), array())
in /var/www/vendor/propel/propel1/runtime/lib/Propel.php at line 668  -+
at Propel ::initConnection (array('dsn' => 'mysql:host=127.0.0.1;dbname=symfony;charset=utf8', 'user' => 'root', 'password' => '********', 'classname' => 'DebugPDO', 'options' => array('MYSQL_ATTR_INIT_COMMAND' => 'SET NAMES utf8 COLLATE utf8_unicode_ci'), 'attributes' => array(), 'settings' => array()), 'default')
in /var/www/vendor/propel/propel1/runtime/lib/Propel.php at line 576  -+
at Propel ::getMasterConnection ('default')
in /var/www/vendor/propel/propel1/runtime/lib/Propel.php at line 602  -+
at Propel ::getSlaveConnection ('default')
in /var/www/vendor/propel/propel1/runtime/lib/Propel.php at line 552  -+
at Propel ::getConnection ('default', 'read')
in /var/www/vendor/propel/propel1/runtime/lib/query/ModelCriteria.php at line 1160  -+
at ModelCriteria ->find ()
in /var/www/src/Acme/AppBundle/Controller/DefaultController.php at line 20  -+
at DefaultController ->fooAction ()
at call_user_func_array (array(object(DefaultController), 'fooAction'), array())
in kernel.root_dir/bootstrap.php.cache at line 1426  -+
at HttpKernel ->handleRaw (object(Request), '1')
in kernel.root_dir/bootstrap.php.cache at line 1390  -+
at HttpKernel ->handle (object(Request), '1', true)
in kernel.root_dir/bootstrap.php.cache at line 1566  -+
at HttpKernel ->handle (object(Request), '1', true)
in kernel.root_dir/bootstrap.php.cache at line 617  -+
at Kernel ->handle (object(Request))
in /var/www/web/app_dev.php at line 25  -+

我知道我可以将my.cnf更改为直接从服务器配置定义MySQL默认排序规则，但是我需要在运行时设置它.我是不是遗漏了什么？如何在app/config/config.yml中设置MYSQL_ATTR_INIT_COMMAND

Answer 1

如果您看到源代码这里，

$value = $optiondata['value']; // this is line no 275
if (is_string($value) && strpos($value, '::') !== false) {
    if (!defined($value)) {
       throw new PropelException("Invalid PDO option/attribute value specified: ".$value);
    }
    $value = constant($value);
}

它期望选项数据中有一个“值”键，因此尝试如下

propel:
    dbal:
        driver:               %database_driver%
        user:                 %database_user%
        password:             %database_password%
        dsn:                  "%database_driver%:host=%database_host%;dbname=%database_name%;charset=%database_charset%"
        options:
            MYSQL_ATTR_INIT_COMMAND: { value: "SET NAMES utf8 COLLATE utf8_unicode_ci" }
        attributes:           {}

或

您还可以定义必须在连接打开后立即执行的查询。

propel:
    dbal:
        default_connection:         default
        connections:
            default:
                # ...
                options:
                    ATTR_PERSISTENT: false
                attributes:
                    ATTR_EMULATE_PREPARES: true
                settings:
                    charset:        { value: UTF8 }
                    queries:        { query: 'INSERT INTO BAR ('hey', 'there')' }

金融时报：https://github.com/propelorm/PropelBundle/blob/1.1/Resources/doc/configuration.markdown

Propel运行时配置文件

Answer 2

关于更改my.cnf的目的，如果您要使用的数据库只需要特定的排序规则设置，那么我认为最好在创建DB时覆盖排序规则设置，方法是使用“create db_name字符集utf8 COLLATE utf8_unicode_ci”命令，而不是通过编辑my.cnf全局更改它。