How do I use the Class::DBI->sequence() method to fill 'id' field automatically in perl?

Question

I'm following the example Class::DBI.

I create the cd table like that in my MariaDB database:

CREATE TABLE cd (
  cdid   INTEGER   PRIMARY KEY,
  artist INTEGER, # references 'artist'
  title  VARCHAR(255),
  year   CHAR(4)
);

The primary key cdid is not set to auto-incremental. I want to use a sequence in MariaDB. So, I configured the sequence:

mysql> CREATE SEQUENCE cd_seq START WITH 100 INCREMENT BY 10;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT NEXTVAL(cd_seq);
+-----------------+
| NEXTVAL(cd_seq) |
+-----------------+
|             100 |
+-----------------+

1 row in set (0.00 sec)

And set-up the Music::CD class to use it:

Music::CD->columns(Primary => qw/cdid/);
Music::CD->sequence('cd_seq');
Music::CD->columns(Others => qw/artist title year/);

After that, I try this inserts:

# NORMAL INSERT
my $cd = Music::CD->insert({ 
    cdid    => 4,
    artist  => 2,
    title   => 'October',
    year    => 1980,
});

# SEQUENCE INSERT
my $cd = Music::CD->insert({ 
    artist  => 2,
    title   => 'October',
    year    => 1980,
});

The "normal insert" succeed, but the "sequence insert" give me this error:

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that
 corresponds to your MariaDB server version for the right syntax to use near ''cd_seq')' at line
 1 [for Statement "SELECT NEXTVAL ('cd_seq')
"] at /usr/local/share/perl5/site_perl/DBIx/ContextualFetch.pm line 52.

I think the quotation marks ('') are provoking the error, because when I put the command "SELECT NEXTVAL (cd_seq)" (without quotations) in mysql client it works (see above). I proved all combinations (', ", `, no quotation), but still...

Any idea?

My versions: perl 5.30.3, 10.5.4-MariaDB

Answer 1

The documentation for sequence() says this:

If you are using a database with AUTO_INCREMENT (e.g. MySQL) then you do not need this, and any call to insert() without a primary key specified will fill this in automagically.

MariaDB is based on MySQL. Therefore you do not need the call to sequence(). Use the AUTO_INCREMENT keyword in your table definition instead.