usna78

Fixing DBIx::Class::Schema::Loader For Use With MariaDB 10 Client Software

I recently set up a virtual Server Running Rocky Linux 9 as a client from which to query a remote MariaDB database. I used perlbrew to install Perl 5.38.2. I installed client related RPMs for MariaDB 10.5, I installed DBIx::Class as a relational mapper that can create Perl Schema Result Classes for each table in the database. If you are new to DBIx::Class, you can review its purpose and features in DBIx::Class::Manual::Intro. The Result Classes used by Perl to query the database are stored on the client server in a schema directory. They are created with the DBIx::Class::Schema::Loader module.

I only work with databases as a home hobbyist, but I have successfully used DBIx::Class and related DBIx::Class::Schema::Loader from a CentOS 7 server running Perl 5.24.1 with MariaDB 5.5 client related RPMs. My intent was to replace a CentOS 7 virtual server with a Rocky 9 virtual server and upgrade from MariaDB 5 client to a MariaDB 10 client.

The CentOS 7 client used DBD::mysql which works fine with MariaDB 5 but would not install on the Rocky server which used MariaDB 10 RPMs. The reasoning behind the change in support offered by DBD::mysql is described here.

To begin my work-around I installed DBD::MariaDB and DBIx::Class::Storage::DBI::MariaDB on the client Rocky server.

To create my relationship classes, I ran the schema loader and was surprised that DBIx::Class::Schema::Loader did not produce correct Result Classes on the Rocky 9 server. Later I found that it did not work properly a on Rocky 8 server either. The common issue was MariaDB version 10 compatibility with DBIx::Class::Schema::Loader (and its dependencies).

I am not sure of everything that was wrong with the Result Classes, but the obvious problems were missing the primary keys entries, missing auto_increment entries, missing entries for many-to-many relationships and missing unsigned integer entries for all Result Classes which should have them.

I found the problem described (but not resolved) in November 2023 in this short Perl Monks thread.

Below I describe the steps I took to get DBIx::Class::Schema::Loader and its dependencies to produce Result Classes on the MariaDB 10 client (Rocky 8 and 9) servers that was identical to that being produced on the MariaDB 5 client CentOS 7 server, remembering that all of them where creating the Result Classes by connecting to the same remote database server. Although not significant for this issue, the remote database was running smoothly on a Rocky 8 server using MariaDB 10.3.

Essential Edits Directly Affecting Schema Loader

First I noticed that the DBIx/Class/Schema/Loader/DBI directory had a mysql.pm file but had no MariaDB.pm counterpart. So I made a copy of mysql.pm named MariaDB.pm and then I edited the new MariaDB.pm file to change references to mysql and MySQL to MariaDB. Note that almost all the edits can use MariaDB as the substitute for mysql and MySQL but entries in the "_extra_column_info" subroutine must use lowercase "mariadb". This is because they refer to lowercase terminology expected by DBD::MariaDB as can be seen in site_perl/5.38.2/x86_64-linux/DBD/MariaDB.pm.

In DBIx/Class/Schema/Loader/DBI/MariaDB.pm, for subroutine "_extra_column_info", keep references to mariadb in lowercase. For example:

• mysql_is_auto_increment should become mariadb_is_auto_increment
• $dbi_info->{mysql_type_name} should become $dbi_info->{mariadb_type_name}

In all other cases I used MariaDB (not mariadb) as the substitution.

DBIx/Class/SQLMaker/

I then realized that DBIx::Class::Schema::Loader depends on DBIx::Class::SQLMaker which supports a number of dependent modules such as MySQL.pm, MSSQL.pm, SQLite.pm and Oracle.pm, but did not have a corresponding MariaDB.pm file. So I made a copy of MySQL.pm named MariaDB.pm and then I edited the new MariaDB.pm file to change references to mysql and MySQL to MariaDB.

After the edits, the Schema loader started working correctly in that running Schema Loader created Schema Result Classes that matched the output of the older MariaDB 5 client.

Additional Edits not Directly Affecting Schema Result Classes Generation

Although apparently not directly related to the functioning of DBIx::Class::Schema:Loader, there are several other files that might need similar editing to provide full MariaDB functionality via DBIx::Class. Your Perl version may differ, but for me these modules are:

• DBIx/Class/PK/Auto/
• site_perl/5.38.2/SQL/Translator/Generator/DDL/
• site_perl/5.38.2/SQL/Translator/Parser/
• site_perl/5.38.2/SQL/Translator/Parser/DBI/
• site_perl/5.38.2/SQL/Translator/Producer/

The directories contain references to other database types but do not provide a MariaDB.pm file. Inside each of the above directories, I copied the mysql.pm file and named it MariaDB.pm. Then I edited each new MariaDB.pm file to change mysql and MySQL entries to MariaDB entries. These additional MariaDB.pm files are not required to get the loader to simply create the Schema Result Classes from a database. I need to test to see what effect adding the MariaDB.pm files to these directories has on DBIx::Class functionality.

The following four files make internal references to mysql or MySQL but did not have a reference to MariaDB. Therefore, I edited each file:

DBIx::Class::Storage::DBI::MariaDB.pm

For file DBIx/Class/Storage/DBI/MariaDB.pm, which is provided from CPAN, in subdirectory sqlt_type, edit to return 'MariaDB' instead of returning 'MySQL'.

Also change this line:
__PACKAGE__->sql_maker_class('DBIx::Class::SQLMaker::MySQL');
to this line:
__PACKAGE__->sql_maker_class('DBIx::Class::SQLMaker::MariaDB');

SQL::Translator::Utils.pm

In the Utils.pm file there is a subroutine named ‘parse_mysql_version’. Copy this subroutine, and name it ‘parse_MariaDB_version’. Then edit subroutine parse_MariaDB_version to change references to MariaDB instead of MySQL. Finally, edit the Export_OK entry to include parse_MariaDB_version.

SQL/Translator/Producer/ClassDBI.pm

Add a MariaDB entry to the %CDBI_auto_pkgs hash

SQL/Translator/Producer/Dumper.pm

Add a MariaDB entry to the %type_to_dbd hash

Results

After making the essential edits I was able to produce Schema Result Classes that appear to be accurate and not missing information. After making the additional edits the Schema Loader still worked correctly.

Caveats and Unknowns:

I have not been able to do production level testing on these changes. I suspect the additional edits and some as yet unidentified edits are needed for full DBIx::Class functionality with MariaDB 10. For example, version control for using the Schema to modify the database (instead of reading the database to create the schema) is probably not working.

I have only tested the schema result class build with a command such as:

use DBIx::Class::Schema::Loader qw/ make_schema_at /;
make_schema_at(
    'MyApp::Schema',
    {
        debug          => 1,
        dump_directory => './lib/test',
        create         => 'static',
        components     => [ 'TimeStamp' ]
    },
    [
        'dbi:MariaDB:database=database_name;host=192.168.xxx.xxx', 'username',
        'secret_pw', { AutoCommit => '1' }
    ],
);

My criterion for success was that (after just the essential edits) the MariaDB 10 clients (on my Rocky 8 and 9 servers) produced identical schema result classes as my MariaDB 5 client running on my CentOS 7 server when querying the same Maria 10 database.

My target database consists of 46 tables containing an assortment on foreign keys defining has_many, belongs_to and many_to_many relationships. The tables contain unsigned integers, unique keys, combined primary keys, auto_increment, integers (signed and unsigned), timestamp, NULLS and not_NULLS, char, varchar, and text data types.

This information is part of a work in progress and my testing is not yet complete. Use these changes at your own risk. They are offered without warranty. You should test thoroughly before incorporating them in important work. Generate your test schemas to a separate directory to avoid harming a known good schema.

If you can provide additional information, corrections and improvements, please share them.