Set default collation so that it doesn't depend on mysql's system configuration - Githubissues

agallou commented 10 years ago

Author Name: Samuel Verschelde (@stormi) Original Redmine Issue: 115, http://mageia-app-db.tuxette.fr/issues/115 Original Date: 2011/02/17 Original Assignee: Adrien Gallou

On an ubuntu server 10.04, dant3 had all the tables using the latin1_swedish_ci collation. It should be utf8_general_ci.

Is there a propel setting for that ?

agallou commented 10 years ago

Original Redmine Comment Author Name: Samuel Verschelde (@stormi) Original Date: 2011/03/12 17:53:26 +0100

can wait for 0.3 :)

agallou commented 10 years ago

Original Redmine Comment Author Name: Vyacheslav Blinov (@dant3) Original Date: 2011/03/18 10:05:38 +0100

Propel have a setting that can be added to schema.xml and define table engine and collation:

<parameter name="Collation" value="utf8_general_ci"/>
<parameter name="Engine" value="MyISAM"/>

Just as example:

<database name="bookstore">
      <table name="author" idMethod="native">
        <vendor type="mysql">
          <parameter name="Engine" value="MyISAM"/>
          <parameter name="Version" value="9"/>
          <parameter name="Collation" value="utf8_general_ci"/>
          <parameter name="Comment" value="Author Table"/>
          <parameter name="Row_format" value="Dynamic"/>
          <parameter name="Rows" value="0"/>
          <parameter name="Avg_row_length" value="0"/>
          <parameter name="Data_length" value="0"/>
          <parameter name="Max_data_length" value="4294967295"/>
          <parameter name="Index_length" value="1024"/>
          <parameter name="Data_free" value="0"/>
          <parameter name="Auto_increment" value="1"/>
          <parameter name="Create_time" value="2005-12-16 16:32:36"/>
          <parameter name="Update_time" value="2005-12-16 16:32:36"/>
          <parameter name="Check_time" value=""/>
          <parameter name="Collation" value="latin1_swedish_ci"/>
          <parameter name="Checksum" value=""/>
          <parameter name="Create_options" value=""/>
          <parameter name="Comment" value="Author Table"/>
        </vendor>
        <column name="id" type="INTEGER" required="true" autoIncrement="true" primaryKey="true">
          <vendor type="mysql">
            <parameter name="Field" value="id"/>
            <parameter name="Type" value="int(11)"/>
            <parameter name="Null" value=""/>
            <parameter name="Key" value="PRI"/>
            <parameter name="Default" value=""/>
            <parameter name="Extra" value="auto_increment"/>
          </vendor>
        </column>
...

agallou commented 10 years ago

Original Redmine Comment Author Name: Vyacheslav Blinov (@dant3) Original Date: 2011/03/20 11:38:36 +0100

I found a way how to simply change collation on tables if you have wrong one:

mysql -B -N --host=localhost --user=root --password=YOUR_PASS -e "SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' CONVERT TO CHARACTER SET utf8  COLLATE utf8_general_ci;') FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'madb';" | mysql --host=localhost --user=root --password=YOUR_PASS

However it seems that non latin symbols will be dismissed so you will have to insert data again.

agallou commented 10 years ago

Original Redmine Comment Author Name: Samuel Verschelde (@stormi) Original Date: 2011/10/30 23:09:27 +0100

Does the change made in propel.ini-dist solve this problem ?

propel.database.encoding   = utf8

agallou commented 10 years ago

Original Redmine Comment Author Name: Vyacheslav Blinov (@dant3) Original Date: 2012/01/22 12:07:51 +0100

Samuel Verschelde wrote:

Does the change made in propel.ini-dist solve this problem ?

[...]

This does not helps, on initialization of new madb instance I had this in propel.ini and got latin1_swedish_ci again on kubuntu 11.10 with default mysql settings.

agallou / mageia-app-db

Set default collation so that it doesn't depend on mysql's system configuration #170