Data for ddl / upgrade_sql columns too long

[MK-Chris]

Hi

Trying to setup my Catalyst app to use this to make updates to the DB simpler when I put them live. I have the following (part script taken from [the Catalyst intro in the manual].(https://metacpan.org/pod/distribution/DBIx-Class-DeploymentHandler/lib/DBIx/Class/DeploymentHandler/Manual/CatalystIntro.pod)

my $dh = DH->new({
  schema           => $schema,
  script_directory => "$FindBin::Bin/../dbicdh",
  databases        => 'MySQL',
});

sub install {
  $dh->prepare_install;
  $dh->install;
}

When calling this, I get the following error:

DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st execute failed: Data too long for column 'ddl' at row 1 [for Statement "INSERT INTO dbix_class_deploymenthandler_versions ( ddl, version) VALUES ( ?, ? )" with ParamValues: 0='SET foreign_key_checks=0
CREATE TABLE `dbix_class_deploymenthandler_versions` ( `id` integer NOT NULL auto_increment, `version` varchar(50) NOT NULL, `ddl` text NULL, `upgrade_sql` text NULL, PRIMARY KEY (`id`), UNIQUE `dbix_class_deploymenthandler_versions_version` (`version`) )
SET foreign_key_checks=1SET foreign_key_checks=0

I believe this is because of limitations on the TEXT datatype in MySQL / MariaDB. The 001-auto-__VERSION.sql file generated from $db->prepare_install gives this:

CREATE TABLE `dbix_class_deploymenthandler_versions` (
  `id` integer NOT NULL auto_increment,
  `version` varchar(50) NOT NULL,
  `ddl` text NULL,
  `upgrade_sql` text NULL,
  PRIMARY KEY (`id`),
  UNIQUE `dbix_class_deploymenthandler_versions_version` (`version`)
);

Presumably from the table definition in DBIx::Class::DeploymentHandler::VersionStorage::Standard::VersionResult, which doesn't look like it can be overridden:

my $table = 'dbix_class_deploymenthandler_versions';

__PACKAGE__->table($table);

__PACKAGE__->add_columns (
  id => {
    data_type         => 'int',
    is_auto_increment => 1,
  },
  version => {
    data_type         => 'varchar',
    # size needs to be at least
    # 40 to support SHA1 versions
    size              => '50'
  },
  ddl => {
    data_type         => 'text',
    is_nullable       => 1,
  },
  upgrade_sql => {
    data_type         => 'text',
    is_nullable       => 1,
  },
);

However, my project is fairly large and the 001-auto.sql file gives the following stats:

I think I need to somehow change the definition so that ddl (and potentially upgrade_sql) are LONGTEXT fields, but I'm unsure how to do this - especially given that LONGTEXT is specific to MySQL and eventually I want to be able to install this on other database types as well - is this possible please?

Many thanks in advance!

[frioux]

If I were you I'd just break up the initial deployment into two or more steps.

On Mon, Dec 02, 2019 at 03:41:50AM -0800, Chris wrote:

Hi

Trying to setup my Catalyst app to use this to make updates to the DB simpler when I put them live. I have the following (part script taken from [the Catalyst intro in the manual].(https://metacpan.org/pod/distribution/DBIx-Class-DeploymentHandler/lib/DBIx/Class/DeploymentHandler/Manual/CatalystIntro.pod)

my $dh = DH->new({
  schema           => $schema,
  script_directory => "$FindBin::Bin/../dbicdh",
  databases        => 'MySQL',
});

sub install {
  $dh->prepare_install;
  $dh->install;
}

When calling this, I get the following error:

DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st execute failed: Data too long for column 'ddl' at row 1 [for Statement "INSERT INTO dbix_class_deploymenthandler_versions ( ddl, version) VALUES ( ?, ? )" with ParamValues: 0='SET foreign_key_checks=0 CREATE TABLEdbix_class_deploymenthandler_versions(idinteger NOT NULL auto_increment,versionvarchar(50) NOT NULL,ddltext NULL,upgrade_sqltext NULL, PRIMARY KEY (id), UNIQUEdbix_class_deploymenthandler_versions_version(version) ) SET foreign_key_checks=1SET foreign_key_checks=0

I believe this is because of limitations on the TEXT datatype in MySQL / MariaDB. The 001-auto-__VERSION.sql file generated from $db->prepare_install gives this:

CREATE TABLE `dbix_class_deploymenthandler_versions` (
  `id` integer NOT NULL auto_increment,
  `version` varchar(50) NOT NULL,
  `ddl` text NULL,
  `upgrade_sql` text NULL,
  PRIMARY KEY (`id`),
  UNIQUE `dbix_class_deploymenthandler_versions_version` (`version`)
);

Presumably from the table definition in DBIx::Class::DeploymentHandler::VersionStorage::Standard::VersionResult, which doesn't look like it can be overridden:

my $table = 'dbix_class_deploymenthandler_versions';

__PACKAGE__->table($table);

__PACKAGE__->add_columns (
  id => {
    data_type         => 'int',
    is_auto_increment => 1,
  },
  version => {
    data_type         => 'varchar',
    # size needs to be at least
    # 40 to support SHA1 versions
    size              => '50'
  },
  ddl => {
    data_type         => 'text',
    is_nullable       => 1,
  },
  upgrade_sql => {
    data_type         => 'text',
    is_nullable       => 1,
  },
);

However, my project is fairly large and the 001-auto.sql file gives the following stats:

I think I need to somehow change the definition so that ddl (and potentially upgrade_sql) are LONGTEXT fields, but I'm unsure how to do this - especially given that LONGTEXT is specific to MySQL and eventually I want to be able to install this on other database types as well - is this possible please?

Many thanks in advance!

-- You are receiving this because you are subscribed to this thread. Reply to this email directly or view it on GitHub: https://github.com/frioux/DBIx-Class-DeploymentHandler/issues/75

-- fREW Schmidt https://blog.afoolishmanifesto.com

[MK-Chris]

Thank you! Is that just a case of running the prepare routine then manually splitting some of the CREATE TABLE statements (and corresponding YML) into separate steps then having an schema version of however many steps I've created?

[frioux]

That seems right to me

On Tue, Dec 03, 2019 at 07:55:23AM -0800, Chris wrote:

Thank you! Is that just a case of running the prepare routine then manually splitting some of the CREATE TABLE statements (and corresponding YML) into separate steps then having an schema version of however many steps I've created?

-- You are receiving this because you commented. Reply to this email directly or view it on GitHub: https://github.com/frioux/DBIx-Class-DeploymentHandler/issues/75#issuecomment-561231345

-- fREW Schmidt https://blog.afoolishmanifesto.com

[MK-Chris]

Great, thanks so much!

[MK-Chris]

FYI, I struggled with this because the relationships meant I wouldn't have been able to split out enough to shorten the string enough, but I changed the install routine to this, which seems to work:

sub install {
  # Set "longtext" ddl / upgrade_sql columns if we're using a MySQL database
  $dh->version_storage->version_rs->result_source->add_columns(
  ddl => {
    data_type         => "longtext",
    is_nullable       => 1,
  },
  upgrade_sql => {
    data_type         => "longtext",
    is_nullable       => 1,
  }) if $dh->schema->storage->sqlt_type eq "MySQL";

  $dh->prepare_install;
  $dh->install;
}

[frioux]

👍

-- Sent from a rotary phone rented from Ma Bell

On Fri, Dec 6, 2019, 4:49 AM Chris notifications@github.com wrote:

FYI, I struggled with this because the relationships meant I wouldn't have been able to split out enough to shorten the string enough, but I changed the install routine to this, which seems to work:

sub install {

Set "longtext" ddl / upgrade_sql columns if we're using a MySQL database

$dh->version_storage->version_rs->result_source->add_columns( ddl => { data_type => "longtext", is_nullable => 1, }, upgrade_sql => { data_type => "longtext", is_nullable => 1, }) if $dh->schema->storage->sqlt_type eq "MySQL";

$dh->prepare_install; $dh->install; }

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/frioux/DBIx-Class-DeploymentHandler/issues/75?email_source=notifications&email_token=AAAB7YYWNOWA5DDAMCYNKVTQXJC4VA5CNFSM4JTUQII2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEGD7PAI#issuecomment-562558849, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAAB7Y2QZZ262IUU67RN6HDQXJC4VANCNFSM4JTUQIIQ .