[Dbix-class] Deployment seems to destroy CHARACTER SET settings on MySQL tables

[Lianna Eeftinck]

package MyApp::Schema::Loader::DBI::mysql::ExtraColumnInfo;
use strict;
use warnings;
use base 'DBIx::Class::Schema::Loader::DBI::mysql';
use mro 'c3';

my $mysql_text_types = qr/^(?: var(?:char)? | char(?:acter)? |
(?:tiny|medium|long)?text )$/msxi;

sub new {
    return shift->next::method( @_ );
}

sub _extra_column_info {
    my ( $self, $table, $col, $info, $dbi_info ) = @_;

    my $extra_info = $self->next::method( $table, $col, $info, $dbi_info );

    if ( $info->{data_type} =~ /$mysql_text_types/msxi ) {
        my ( $character_set_name, $collation_name ) = eval {
            $self->dbh->selectrow_array( qq{
               SELECT character_set_name, collation_name
               FROM information_schema.columns
               WHERE table_name = ? AND lower(column_name) = ?
            }, {}, $table->name, lc( $col ) );
        };

        if ( my $has_information_schema = not $@ ) {
            $extra_info->{ extra }{ mysql_collate } = $collation_name;
            $extra_info->{ extra }{ mysql_charset } = $character_set_name;
        }
    }

    return $extra_info;
}

1;


And include it in the schema loader options too:

loader_class => 'MyApp::Schema::Loader::DBI::mysql::ExtraColumnInfo'

It's actually reading the database where this information gets lost, it's
never stored in the YAML generated by DBIx::Schema::Loader, so it won't be
output properly either until it is added.

-- Lianna



On 10 August 2014 15:32, Matija Grabnar <matija at serverflow.com> wrote:

> I am developing a Catalyst application that uses DBIx::Class. I thought I
> would do things right and use DBIx::Class::DeploymentHandler right from the
> start.
> I was able to get it to deploy and upgrade my tables very nicely.
> However, I have run into a show stopper now:
>
> The character set settings of tables and strings are not preserved. That
> means that if I declare a table like this:
>
> create table graph_category (
> id serial primary key,
> category varchar(190) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
> unique index i_graph_category_category(category)
> ) engine=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
>
> and then do
> dbicdump -o dump_directory=./lib \
>            -o debug=1 \
>            MyDB \
>            'dbi:mysql:dbname=mydb' \
>            someusername \
>            notthepassword
>
>
> And then a script containing
>  my $dh = DBIx::Class::DeploymentHandler->new(
>      {
>          schema              => $schema,
>          script_directory    => "$FindBin::Bin/../dbicdh",
>          databases           => 'MySQL',
>          sql_translator_args => { add_drop_table => 1 },
>          force_overwrite     => $force_overwrite,
>      }
>     );
>
> $dh->prepare_install;
> $dh->install;
>
> The table in the database will become:
>
> CREATE TABLE `graph_category` (
>   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
>   `category` varchar(190) DEFAULT NULL,
>   PRIMARY KEY (`id`),
>   UNIQUE KEY `i_graph_category_category` (`category`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
>
> In other words, all the character set info has disappeared. Since the
> requirements specify a lot of UTF8 work, I find that quite distressing. And
> in production, I'm afraid it could lead to data corruption.
>
> Does anybody know of a way to avoid this problem (short of discarding
> DeploymentHandler altogether)?
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/
> dbix-class at lists.scsys.co.uk
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20140812/42bbc8ae/attachment.html>