Laravel 3 Schema Table Column Collation

Question

I'm learning laravel, and I'm stuck on a simple process. I want the tables to be generated as UTF-8 but varchar and text fields are like latin-1.

Schema section in guide did not help at all. I found this GitHub entry but it does not work neither (throws me errors).

I have a schema like this:

<?php

class Create_Authors_Table {

    /**
     * Make changes to the database.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('authors',function($table){
         //$table->charset('utf8'); //does not work
         //$table->collate('utf8_general_ci'); //does not work

         $table->increments('id');
         $table->string('name')->charset('utf8'); //adding ->collate('utf8_general_ci') does not work
         $table->text('bio')->charset('utf8');
         $table->timestamps();
        });
    }

    /**
     * Revert the changes to the database.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('authors');
    }

}

This is the SQL output:

CREATE TABLE IF NOT EXISTS `authors` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `bio` text NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

But this is what I need:

CREATE TABLE IF NOT EXISTS `authors` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `bio` text NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

I even put collation key to my application/config/database.php

'mysql' => array(
    'driver'   => 'mysql',
    'host'     => '',
    'database' => '',
    'username' => '',
    'password' => '',
    'charset'  => 'utf8',
    'collation'=> 'utf8_unicode_ci', //this line was not there out of the box, googling provided me this
    'prefix'   => '',
),

What am I missing, how do I fix this?

Thanks,

Helmer

Answer 1

Just solved it, (using mysql 5.5 (5.5.29)) with Linux Mint 13 Mate (based on Ubuntu 12.04 LTS)

I added these lines on /etc/mysqld/my.cnf

[client]
default-character-set = utf8

[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
character-set-server = utf8
collation-server = utf8_unicode_ci

This way, SQL queries worked on shell as it should, but on laravel or phpmyadmin etc, they didn't work. So I did this:

I opened /etc/apache2/conf.d/charset and removed the ; from this line:

AddDefaultCharset UTF-8

Then I restarted apache2 and mysql, now it works as it should.

Later Edit: That GitHub Core Hack works like a treat. I ended up doing it.

Helmer

Answer 2

Support for MySQL character sets and collation has been added to Laravel 4 . Check this example.

https://github.com/laravel/laravel/pull/897

Unreliable

Yeah, but this question was asked when there was no Laravel 4 around :) – Helmer

Answer 3

Seems not implement actually for individual schema build in Laravel 3 master. (find nothing in source code)

I think you need to do this manually ( try with raw query )

Septempartite

Answer 4

I'm stuck on the same, I guess there is no way in Laravel to change/set collation of the charset. But you can change the default collation for charset in your my.ini/my.cnf of your MySQL Server!

[mysqld]
#...
collation-server = utf8_unicode_ci
#...

Intreat

Answer 5

Here is my solution

1.add public $charset; in the laravel/database/schema/table.php under public $engine; 2.replace the function create in laravel/database/schema/grammars/mysql.php like this

public function create(Table $table, Fluent $command)
{
    $columns = implode(', ', $this->columns($table));

    // First we will generate the base table creation statement. Other than auto
    // incrementing keys, no indexes will be created during the first creation
    // of the table as they're added in separate commands.
    $sql = 'CREATE TABLE '.$this->wrap($table).' ('.$columns.')';

    if ( ! is_null($table->engine))
    {
        $sql .= ' ENGINE = '.$table->engine;
    }
    if ( ! is_null($table->charset))
    {
        $sql.="DEFAULT CHARSET=".$table->charset;
    }
    return $sql;
}

3.now you can set any charset in your migration php such as $table->charset='utf8';

Spangle

Ya know, I loved your suggestion until I remembered I was using composer and a Git repository. Bah. Thanks for the suggestion though! :) – Winstonwinstonn