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

[Matija Grabnar]

Lianna, thank you!

  I put the module you wrote in my lib directory, and I modified the 
call I make to dbicdump to
include the loader class:

dbicdump -I ./lib
            -o dump_directory=./lib \
            -o debug=1 \
            -o 
loader_class='MyApp::Schema::Loader::DBI::mysql::ExtraColumnInfo' \
            MyDB \
            'dbi:mysql:dbname=mydb' \
            someusername \
            notthepassword

But it still doesn't record the character set details into the Result 
files that it generates (or anywhere else that I can find). And that 
means that the install/upgrade scripts also don't get the info.

I am using DBIx::Class::Schema::Loader 0.7039, do I need a later 
version? Or am I missing something else?


Best regards,
                  Matija Grabnar

On 08/12/2014 04:41 PM, Lianna Eeftinck wrote:
> 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 
> <mailto: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 <http://irc.perl.org#dbix-class>
>     SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
>     Searchable Archive:
>     http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>
>
>
>
> _______________________________________________
> 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@lists.scsys.co.uk

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20140814/deb99e83/attachment.htm>