DBD::MariaDB + DBIx::Class = woes

bliako has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monk Programmers

In a previous question (Is it worth migrating from MySQL to MariaDB?) I was asking if it is a good move to switch from MySQL to MariaDB. And the consensus was Yes. However. I forgot to mention one crucial part of my settings: that I am relying heavily on DBIx::Class which has no loader for MariaDB. The "solutions" are 2:

Install DBD::mysql and use the DSN : connect("DBI:mysql:...", ...) (also see Re: MariaDB : read credentials from file fails (via dsn option mariadb_read_default_file)) - this is a dead end for me because DBD::mysql fails to install, I need to investigate how to install DBD::mysql on my particular system. I can say that this route worked on another system where said module did install OK.
Use a DSN with DBI:MariaDB. DBIx::Class will complain that it does not know this DB driver. Install DBIx::Class::Storage::DBI::MariaDB and add __PACKAGE__->ensure_class_loaded('DBIx::Class::Storage::DBI::MariaDB');
in the custom-code part of the Schema.pm auto-generated file (by DBIx). This sounds straightforward and DBIx converts my SQL to packages OK. The only problem (so far) is that the generated files do not contain a set_primary_key() for those tables which my SQL code explicitly declares one column to be the PK. Instead it has a:
```
__PACKAGE__->add_unique_constraint(
  "PRIMARY", ["id"]
);
[download]
```
This should work but it doesn't! Because further on, DBIx::Class complains with:
```
Error:  {UNKNOWN}: DBIx::Class::ResultSource::_pri_cols_or_die(): Oper
+ation requires a primary key to be declared on 'MyApp::Schema::XYZ' v
+ia set_primary_key ...
[download]
```
My hack was to add a __PACKAGE__->set_primary_key("id"); in the custom-code part of the auto-generated schema file for each table that needs it.

Fine! This creates the schema files and finally connects to DB. BUT my tests fail with surreal output. The culprit, I found out hours later, is that none of my tables' PK is ... auto_increment'ed (as it was stated in my SQL code) !!!!!!!!!

Currently, this directive is set in said file, like so:
```
# when I am using DBI:mysql ...
__PACKAGE__->add_columns(
  "id", {
      "is_auto_increment" => "1",
      "data_type" => "mediumint",
      ...
   }
);
__PACKAGE__->set_primary_key("id");
[download]
```
The auto_increment directive is currently missing from my DBIx::Class auto-generated files. From a diagonal look in the documentation I did not see how to modify these directives for each column in the custom-code part of the auto-generated files. I did try to re-issue a __PACKAGE__->add_columns(...) in the custom-code part hoping that it will modify it but I did not see any change.

To summarise, I am looking for a solution in using DBD::MariaDB (and not DBD::mysql) with DBIx::Class with primary keys declared explicitly and also to be auto-incremented if that is what my SQL states.

I have filed an issue with DBIx::Class::Storage::DBI::MariaDB at https://github.com/Siemplexus/DBIx-Class-Storage-DBI-MariaDB/issues/4 and plan to do the same with DBIx::Class.

bw, bliako

Comment on DBD::MariaDB + DBIx::Class = woes Select or Download Code

Replies are listed 'Best First'.
Re: DBD::MariaDB + DBIx::Class = woes by NERDVANA (Priest) on Nov 07, 2023 at 19:01 UTC
If you mean that you are using Schema Loader to generate DBIC result classes from the live schema of the database, then I'm pretty sure your bug lies in DBIx::Class::Schema::Loader::DBI. In particular, all the things that happen in ::Schema::Loader::DBI::mysql will not be happening with mariadb. You could just copy/paste that class into your local project lib dir and rename to miriadb and see if it fixes things.	[reply]
Re: DBD::MariaDB + DBIx::CLass = woes by choroba (Cardinal) on Nov 07, 2023 at 12:09 UTC
Have you tried taking DBIx::Class::Storage::DBI::mysql, run it through `s/mysql/MariaDB/` [download] and save it to `DBIx/Class/Storage/DBI/MariaDB.pm` to create the missing driver? Many Perl users would thank you if it worked and you published your work :-) `map{substr$_->[0],$_->[1]\|\|0,1}[\\|\|{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^ARGV,3]`	[reply] [d/l] [select]
Re^2: DBD::MariaDB + DBIx::CLass = woes by bliako (Abbot) on Nov 07, 2023 at 12:35 UTC
Oh but they did! DBIx::Class::Storage::DBI::MariaDB is exactly what you are suggesting bar some indentation differences. Anyway the whole thing looks like the Bermuda triangle with three vertices: DBIx::Class, DBD::MariaDB, SQL::Translator. Whole aeroplanes get lost in there...	[reply]
Re^3: DBD::MariaDB + DBIx::CLass = woes by choroba (Cardinal) on Nov 07, 2023 at 15:02 UTC
I'm out of ideas. I've never used `DBIx::Class` more than a toy, as for any serious SQL work, I found it easier to write the actual SQL statements myself. Your journey is another example what I've been afraid of. `map{substr$_->[0],$_->[1]\|\|0,1}[\\|\|{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^ARGV,3]`	[reply] [d/l] [select]
Re^4: DBD::MariaDB + DBIx::CLass = woes by bliako (Abbot) on Nov 07, 2023 at 15:20 UTC
Re^2: DBD::MariaDB + DBIx::CLass = woes by 1nickt (Canon) on Nov 07, 2023 at 12:33 UTC
To be fair, as mentioned in the OP, there is DBIx::Class::Storage::DBI::MariaDB already. The way forward always starts with a minimal test.	[reply]
Re: DBD::MariaDB + DBIx::Class = woes by rbrew (Novice) on May 08, 2024 at 13:10 UTC
I recently ran into this same issue and think I may have created a work-around. I described what I did to resolve in a blog entry here: https://blogs.perl.org/users/usna78/2024/05/mariadb-10-and-perl-dbixclassschemaloader.html	[reply]
Re^2: DBD::MariaDB + DBIx::Class = woes by bliako (Abbot) on May 08, 2024 at 17:09 UTC
Thank you rbrew for this detailed solution. I know you are querying the DB, but did you, perchance, see the problem of the auto-incremented PK I mentioned above? (i.e. problem specifying a PK and then problem making it auto-incrementing).	[reply]
Re^3: DBD::MariaDB + DBIx::Class = woes by rbrew (Novice) on May 11, 2024 at 13:08 UTC
My symptom was similar to yours. When I ran schema loader to generate result classes the primary keys were not being included in the Result Class .pm files and the auto_increment entry was always missing. In addition, the many_to_many, has_many and belongs_to relationships were not included. Also for integer types, the signed/unsigned specification was missing. The edits I describe seems to correct all of those problems. I do not usually generate my database tables from DBIx::Class result classes because I use DbSchema to create my tables and to populate by database. I typically use DBIx::Class::Schema::Loader to generate the result classes for use by my Perl code. I made some more changes as now described in my revised blog posting and the basic functionality of $schema->deploy works. Auto_increment and primary key creation works. What does not seem to work properly are timestamp types where the extra involves ON UPDATE current_timestamp(). Neither does deploying with {add_drop_tables => 1}, in that the deployment fails because tables will not drop before trying to recreate them from the Result Classes. More testing is needed to isolate the problems.	[reply]
Re^4: DBD::MariaDB + DBIx::Class = woes by NERDVANA (Priest) on May 13, 2024 at 23:04 UTC
Re^4: DBD::MariaDB + DBIx::Class = woes by bliako (Abbot) on May 13, 2024 at 13:54 UTC
Re^2: DBD::MariaDB + DBIx::Class = woes by bliako (Abbot) on Sep 20, 2024 at 20:22 UTC
rbrew++ thank you, I have finally managed to follow your detailed guide in the link you posted above and tested my quite complex app and can report that all is well as far as functionality and testing is concerned. bw, bliako	[reply]