Migrating 76 tables out of MS-SQL. Thanks DBIx::Class!

Just another day at the office moving databases into MySQL. 10 lines of DBIx::Class::Schema::Loader make_schema_at() and I'm done. :)


Unfortunately today the table names "Order" and "Service-tier2" blow up:

Bad table or view 'Order', ignoring: DBIx::Class::Schema::Loader::make_schema_at(): DBI Exception: DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Order WHERE ( 1 = 0 )' at line 1 [for Statement "SELECT * FROM Order WHERE ( 1 = 0 )"] at refresh_schema.pl line 9

Apparently "Order" is semi-reserved by MySQL, and dashes are trouble:

SELECT * FROM Order              # syntax error
SELECT * FROM `Order`            # works
SELECT * FROM Service-tier2      # syntax error
SELECT * FROM `Service-tier2`    # works

Another point for irc.perl.org #dbix-class, as ilmari pointed me to quote_char as the cure for what ailed me.

use strict;
use DBIx::Class::Schema::Loader qw( make_schema_at );
   "P3::Schema", {
      dump_directory     => ".",
   [ "dbi:mysql:protec", $user, $password, {
      quote_char => '`'
   } ],

Beauty. I'm still hitting this new warning because the column name 'can' (Client Access Number) collides with UNIVERSAL::can():

Column can in table ClContact collides with an inherited method.
See "COLUMN ACCESSOR COLLISIONS" in perldoc DBIx::Class::Schema::Loader::Base .

Oops! :) But that's an easy work-around in my Result/ class. I just have to pick anything that's not reserved:

{ accessor => "cant", data_type => "varchar", is_nullable => 0, size => 10 },

So to access that column I now call $clcontact->cant(). Why? Because now I can. :)

1 Comment

ironic, this can't->can fix actually worked for me when I was migrating a larger amount of tables out, I had a few hundred we were trying to move in and out, but kept getting errors until we put up the "can" haha :)

Bonnie Smith COO/Director FXP http://www.forexpulse.com

Leave a comment

About Jay @ Mutation Grid

user-pic Perl / web / database development since 1995. Contact us for your next project.