CODEWITHSUNDEEP
mysqllaravellaravel-5.3emojilaravel-migrations
Neel
Neel

Reputation: 9880

Changing Laravel MYSQL to utf8mb4 for Emoji Support in Existing Database

I am using Laravel 5.3 and I have already set-up my production server. All DB Migrations were already created using the following database config:

'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],

But now, some of my users had reported that they get an error when they try to save a form that has emoji 😊 icons in them. After searching I found out that I need to set the mysql charset to utf8mb4 for this to work so my config should have been something like this instead:

'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],

Since this is in a production server, I cannot do migrate:refresh. So my questions are:

  1. How can I change my existing database I created using laravel migration to use utf8mb4 instead of utf8 and also update laravel on the same? Is there an easier way to do this?
  2. If the above is possible, am I better off setting utf8mb4 for all tables or only use that for the 2 table columns where I will really be using emoji.

Thank you for your help.

Upvotes: 17

Views: 23645

Answers (3)

Saumini Navaratnam
Saumini Navaratnam

Reputation: 8850

  1. Use raw mysql query to write the update table migration script and run php artisan migrate command

     use Illuminate\Database\Migrations\Migration;

 class UpdateTableCharset extends Migration {

     /**
      * Run the migrations.
      *
      * @return void
      */
     public function up() {
             DB::unprepared('ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8mb4');
     }

     /**
      * Reverse the migrations.
      *
      * @return void
      */
     public function down() {
             DB::unprepared('ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8');
     }
 }

  2. My personal preference, update table. I don't have proof to say it is better

Note: You still have to keep the database config to utf8mb4.

Upvotes: 17

Patrick Breen
Patrick Breen

Reputation: 111

Based on @insomniak-dev answer, but will only shrink varchar when they exceed limits AND are used in an index. Otherwise, they are converted, but size is left as is. If a column will be shrunk, it then checks if any data will be truncated.

This also handles all text types and batches all conversions for each table into a single statement for speed.

Dryrun flag outputs usable sql instead of applying directly.

/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    $dryRun = true;
    $this->convertDb('mysql', 'utf8mb4', 'utf8mb4_unicode_ci', $dryRun);
    $this->convertDb('archive', 'utf8mb4', 'utf8mb4_unicode_ci', $dryRun);
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    $dryRun = true;
    $this->convertDb('archive', 'utf8', 'utf8_unicode_ci', $dryRun);
    $this->convertDb('mysql', 'utf8', 'utf8_unicode_ci', $dryRun);
}

private function convertDb($connection, $charset, $collate, $dryRun)
{
    $dbName = config("database.connections.{$connection}.database");

    $varchars = \DB::connection($connection)
        ->select(\DB::raw("select * from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE = 'varchar' and (CHARACTER_SET_NAME != '{$charset}' or COLLATION_NAME != '{$collate}') AND TABLE_SCHEMA = '{$dbName}'"));
    // Check if shrinking field size will truncate!
    $skip = [];  // List of table.column that will be handled manually
    $indexed = [];
    if ($charset == 'utf8mb4') {
        $error = false;
        foreach($varchars as $t) {
            if ($t->CHARACTER_MAXIMUM_LENGTH > 191) {
                $key = "{$t->TABLE_NAME}.{$t->COLUMN_NAME}";

                // Check if column is indexed
                $index = \DB::connection($connection)
                    ->select(\DB::raw("SHOW INDEX FROM `{$t->TABLE_NAME}` where column_name = '{$t->COLUMN_NAME}'"));
                $indexed[$key] = count($index) ? true : false;

                if (count($index)) {
                    $result = \DB::connection($connection)
                        ->select(\DB::raw("select count(*) as `count` from `{$t->TABLE_NAME}` where length(`{$t->COLUMN_NAME}`) > 191"));
                    if ($result[0]->count > 0) {
                        echo "-- DATA TRUNCATION: {$t->TABLE_NAME}.{$t->COLUMN_NAME}({$t->CHARACTER_MAXIMUM_LENGTH}) => {$result[0]->count}" . PHP_EOL;
                        if (!in_array($key, $skip)) {
                            $error = true;
                        }
                    }
                }
            }
        }
        if ($error) {
            throw new \Exception('Aborting due to data truncation');
        }
    }

    $query = "SET FOREIGN_KEY_CHECKS = 0";
    $this->dbExec($query, $dryRun, $connection);

    $query = "ALTER SCHEMA {$dbName} DEFAULT CHARACTER SET {$charset} DEFAULT COLLATE {$collate}";
    $this->dbExec($query, $dryRun, $connection);

    $tableChanges = [];
    foreach($varchars as $t) {
        $key = "{$t->TABLE_NAME}.{$t->COLUMN_NAME}";
        if (!in_array($key, $skip)) {
            if ($charset == 'utf8mb4' && $t->CHARACTER_MAXIMUM_LENGTH > 191 && $indexed["{$t->TABLE_NAME}.{$t->COLUMN_NAME}"]) {
                $tableChanges["{$t->TABLE_NAME}"][] = "CHANGE `{$t->COLUMN_NAME}` `{$t->COLUMN_NAME}` VARCHAR(191) CHARACTER SET {$charset} COLLATE {$collate}";
                echo "-- Shrinking: {$t->TABLE_NAME}.{$t->COLUMN_NAME}({$t->CHARACTER_MAXIMUM_LENGTH})" . PHP_EOL;
            } else if ($charset == 'utf8' && $t->CHARACTER_MAXIMUM_LENGTH == 191) {
                $tableChanges["{$t->TABLE_NAME}"][] = "CHANGE `{$t->COLUMN_NAME}` `{$t->COLUMN_NAME}` VARCHAR(255) CHARACTER SET {$charset} COLLATE {$collate}";
                echo "-- Expanding: {$t->TABLE_NAME}.{$t->COLUMN_NAME}({$t->CHARACTER_MAXIMUM_LENGTH})";
            } else {
                $tableChanges["{$t->TABLE_NAME}"][] = "CHANGE `{$t->COLUMN_NAME}` `{$t->COLUMN_NAME}` VARCHAR({$t->CHARACTER_MAXIMUM_LENGTH}) CHARACTER SET {$charset} COLLATE {$collate}";
            }
        }
    }

    $texts = \DB::connection($connection)
        ->select(\DB::raw("select * from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE like '%text%' and (CHARACTER_SET_NAME != '{$charset}' or COLLATION_NAME != '{$collate}') AND TABLE_SCHEMA = '{$dbName}'"));
    foreach($texts as $t) {
        $tableChanges["{$t->TABLE_NAME}"][] = "CHANGE `{$t->COLUMN_NAME}` `{$t->COLUMN_NAME}` {$t->DATA_TYPE} CHARACTER SET {$charset} COLLATE {$collate}";
    }

    $tables = \DB::connection($connection)
        ->select(\DB::raw("select * from INFORMATION_SCHEMA.TABLES where TABLE_COLLATION != '{$collate}' and TABLE_SCHEMA = '{$dbName}';"));
    foreach($tables as $t) {
        $tableChanges["{$t->TABLE_NAME}"][] = "CONVERT TO CHARACTER SET {$charset} COLLATE {$collate}";
        $tableChanges["{$t->TABLE_NAME}"][] = "DEFAULT CHARACTER SET={$charset} COLLATE={$collate}";
    }

    foreach ($tableChanges as $table => $changes) {
        $query = "ALTER TABLE `{$table}` ".implode(",\n", $changes);
        $this->dbExec($query, $dryRun, $connection);
    }

    $query = "SET FOREIGN_KEY_CHECKS = 1";
    $this->dbExec($query, $dryRun, $connection);

    echo "-- {$dbName} CONVERTED TO {$charset}-{$collate}" . PHP_EOL;
}

private function dbExec($query, $dryRun, $connection)
{
    if ($dryRun) {
        echo $query . ';' . PHP_EOL;
    } else {
        \DB::connection($connection)->getPdo()->exec($query);
    }
}

Upvotes: 11

Insomniak Dev
Insomniak Dev

Reputation: 139

for anyone willing to achieve this on the whole database i found myself needing such a script :

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Config;

class ChangeDbCharset extends Migration
{

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        $charset = "utf8mb4";
        $collate = $charset."_unicode_ci";
        $dbName = Config::get('database.connections.'.Config::get('database.default').'.database');
        $query = "ALTER SCHEMA $dbName DEFAULT CHARACTER SET $charset DEFAULT COLLATE $collate;\n"; 
        DB::connection()->getPdo()->exec($query);

        $dbName = Config::get('database.connections.'.Config::get('database.default').'.database');
        $result = DB::select(DB::raw('show tables'));
        $test = DB::select(DB::raw("select * from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE = 'varchar' AND TABLE_SCHEMA = '$dbName';"));
        //var_dump($test);
        foreach($test as $t)
        {
            $query = "ALTER TABLE $t->TABLE_NAME CHANGE $t->COLUMN_NAME $t->COLUMN_NAME VARCHAR(191) CHARACTER SET $charset COLLATE $collate; \n";
            echo $query;
            DB::connection()->getPdo()->exec($query);
        }
        $test = DB::select(DB::raw("select * from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE = 'text' AND TABLE_SCHEMA = '$dbName';"));
        foreach($test as $t)
        {
            $query = "ALTER TABLE $t->TABLE_NAME CHANGE $t->COLUMN_NAME $t->COLUMN_NAME TEXT CHARACTER SET $charset COLLATE $collate; \n";
            echo $query;
            DB::connection()->getPdo()->exec($query);
        }


        $result = DB::select(DB::raw('show tables'));
        foreach($result as $r)
        {
            foreach($r as $k => $t)
            {
                $query = "ALTER TABLE `$t` CONVERT TO CHARACTER SET $charset COLLATE $collate; \n";
                echo $query;
                DB::connection()->getPdo()->exec($query);
            }
        }
        echo "DB CHARSET set to $charset , $collate";
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        $charset = "utf8";
        $collate = $charset."_unicode_ci";
        $dbName = Config::get('database.connections.'.Config::get('database.default').'.database');
        $query = "ALTER SCHEMA $dbName DEFAULT CHARACTER SET $charset DEFAULT COLLATE $collate;\n"; 
        DB::connection()->getPdo()->exec($query);

        $dbName = Config::get('database.connections.'.Config::get('database.default').'.database');
        $result = DB::select(DB::raw('show tables'));
        $test = DB::select(DB::raw("select * from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE = 'varchar' AND TABLE_SCHEMA = '$dbName';"));
        //var_dump($test);
        foreach($test as $t)
        {
            $query = "ALTER TABLE $t->TABLE_NAME CHANGE $t->COLUMN_NAME $t->COLUMN_NAME VARCHAR(255) CHARACTER SET $charset COLLATE $collate; \n";
            echo $query;
            DB::connection()->getPdo()->exec($query);
        }
        $test = DB::select(DB::raw("select * from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE = 'text' AND TABLE_SCHEMA = '$dbName';"));
        foreach($test as $t)
        {
            $query = "ALTER TABLE $t->TABLE_NAME CHANGE $t->COLUMN_NAME $t->COLUMN_NAME TEXT CHARACTER SET $charset COLLATE $collate; \n";
            echo $query;
            DB::connection()->getPdo()->exec($query);
        }


        $result = DB::select(DB::raw('show tables'));
        foreach($result as $r)
        {
            foreach($r as $k => $t)
            {
                $query = "ALTER TABLE `$t` CONVERT TO CHARACTER SET $charset COLLATE $collate; \n";
                echo $query;
                DB::connection()->getPdo()->exec($query);
            }
        }
        echo "DB CHARSET set to $charset , $collate";
    }

}

Upvotes: 7

Related Questions