DBIx::Class::Schema::Loader defining CURRENT_TIMESTAMP as a string rather than string reference

Question

I have a script that uses Class::DBIx::Schema->deploy to create a database for an application I'm building.

I'm using mysql 5.6.19

Some of the tables have datetime fields that have a default value of CURRENT_TIMESTAMP (which is valid for mysql versions > 5.6)

When I run the code to populate the DB, deploy is putting quotes around CURRENT_TIMESTAMP as follows:

CREATE TABLE `company_info` (
  `id` bigint unsigned NOT NULL auto_increment,
  `ugroup` bigint unsigned NULL,
  `created` datetime NULL DEFAULT 'CURRENT_TIMESTAMP',
  `num_employees` integer NOT NULL DEFAULT 1,
  `type` char(16) NULL,
  INDEX `company_info_idx_ugroup` (`ugroup`),
  PRIMARY KEY (`id`),
  CONSTRAINT `company_info_fk_ugroup` FOREIGN KEY (`ugroup`) REFERENCES `groups` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB

The quotes cause an error "Invalid default value for 'created'", removing the quotes and running this command from the command line works fine, if the quotes are left in place, it fails.

This is because in the Schema::Result, we have a string "CURRENT_TIMESTAMP" rather then a string reference \"CURRENT_TIMESTAMP"

Schema::Result::CompanyInfo looks as follows and is generated;

use utf8;
package Schema::Result::CompanyInfo;

# Created by DBIx::Class::Schema::Loader
# DO NOT MODIFY THE FIRST PART OF THIS FILE

use strict;
use warnings;

use base 'DBIx::Class::Core';
__PACKAGE__->load_components("InflateColumn::DateTime");
__PACKAGE__->table("company_info");
__PACKAGE__->add_columns(
  "id",
  {
    data_type => "bigint",
    extra => { unsigned => 1 },
    is_auto_increment => 1,
    is_nullable => 0,
  },
  "ugroup",
  {
    data_type => "bigint",
    extra => { unsigned => 1 },
    is_foreign_key => 1,
    is_nullable => 1,
  },
  "created",
  {
    data_type => "datetime",
    datetime_undef_if_invalid => 1,
    default_value => "CURRENT_TIMESTAMP",
    is_nullable => 1,
  },
  "num_employees",
  { data_type => "integer", default_value => 1, is_nullable => 0 },
  "type",
  { data_type => "char", is_nullable => 1, size => 16 },
);
__PACKAGE__->set_primary_key("id");
__PACKAGE__->belongs_to(
  "ugroup",
  "Schema::Result::Group",
  { id => "ugroup" },
  {
    is_deferrable => 1,
    join_type     => "LEFT",
    on_delete     => "SET NULL",
    on_update     => "CASCADE",
  },
);


# Created by DBIx::Class::Schema::Loader v0.07042 @ 2014-11-24 14:30:12
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:Q6PHwuB2Zk74lVC08u8CMQ


# You can replace this text with custom code or comments, and it will be preserved on regeneration
1;

Changing default_value => "CURRENT_TIMESTAMP", to default_value => \"CURRENT_TIMESTAMP", fixes the problem, however since this is generated by DBIx::Class::Schema::Loader I'd rather not manually edit all the related files.

Is this a bug or is there a way to tell DBIx::Class::Schema::Loader to create a string reference around CURRENT_TIMESTAMP?

Thanks

score 3 · Accepted Answer · answered Dec 12 '14 at 13:23

3

I believe the argument to default_value should be a string reference:

default_value => \"CURRENT_TIMESTAMP",

answered Dec 12 '14 at 13:23

Csson

158
4

Yes that works - thanks, except that the Schema file is generated by DBIx::Class::Schema::Loader, so do you know if there is a way to get DBIx::Class::Schema::Loader to correctly generate the file? I'll update the question. – mark Dec 12 '14 at 13:39

score 1 · Answer 2 · answered Jun 29 '16 at 15:22

There is a condition at c:\Perl\site\lib\DBIx\Class\Schema\Loader\DBI\mysql.pm (l. 305), that replaces the string by the reference. For some reason it is only for 'timestamp', but you can make a modification to deal with 'datetime' as well.

if ((not blessed $dbi_info) # isa $sth
    && lc($dbi_info->{COLUMN_DEF})      eq 'current_timestamp'
    && lc($dbi_info->{mysql_type_name}) eq 'timestamp') {

    my $current_timestamp = 'current_timestamp';
    $extra_info{default_value} = \$current_timestamp;
}

The "reason" is that CURRENT_TIMESTAMP for DATETIME is supported up from MySQL 5.6.5 http://stackoverflow.com/questions/168736/how-do-you-set-a-default-value-for-a-mysql-datetime-column/10603198#10603198 — ojinmor, Jun 30 '16 at 15:11

DBIx::Class::Schema::Loader defining CURRENT_TIMESTAMP as a string rather than string reference

2 Answers2