[Bioperl-l] Support for MariaDB in Bio::DB:SeqFeature:Store::DBI::

[Schlachter, Kai]

Hi there,

 

I took over an older project to modernize the underlaying hardware / software.

This also involves the use of gbrowse, which in turn relies quite a lot on BioPerl.

 

As our freshly provided servers are all Debian based and we are highly encouraged to use a most recent version of Debian inside of our containers (docker), I ran into the following issue:

Debian no longer supports MySQL but has switched to MariaDB, which leads me to the following:

 

I tried to install gbrowse, resolving all the dependencies and stuff that came along. I also was able to compile the DBD::MariaDB as this is the required module for MariaDB-Connections.

However I was not able to find a Module in Bio::DB:SeqFeature:Store::DBI called MariaDB, there is only Mysql :(

Although both adaptors should share at least some basic functionality, it is not possible to install the Mysql-Version on a recent Debian because DBD::MySQL will refuse to work with mariadb because of a version mismatch (expected 8.x but reported 10.y).

 

Are there any plans / ideas to add mariadb support to the modules?

I am not an original perl programmer so I don’t think it’s a good idea to start implementing / contributing with such a complex thing.

 

Greetings,

 

Kai

[Peter Cock]

Hello Kai,

Interesting question (although I'm viewing it from a Python and Biopython perspective).

https://mariadb.com/kb/en/upgrading-from-mysql-to-mariadb/ suggests this should

just work, with existing MySQL bindings treating MariaDB as a newer version.

It sounds like your set is breaking in the Perl module DBD::mysql?

https://metacpan.org/dist/DBD-mysql

Looking at the change log there are entries from this year, and multiple mentions

of MariaDB - so I would expect this to work:

https://metacpan.org/release/DVEEDEN/DBD-mysql-5.008/source/Changes

Can you take a look at their issue tracker https://github.com/perl5-dbi/DBD-mysql

and see if you've hit a known issue?

Also can you double check the version of DBD::mysql you have installed?

Peter

_______________________________________________
Bioperl-l mailing list
Biop...@bioperl.org
https://mailman.open-bio.org/mailman/listinfo/bioperl-l

[Schlachter, Kai]

Hello Peter,

 

Thanks for your reply.

 

Updating of the database server process itself was no issue, also the migration of the data in the database was nothing unusual – this is also something I already did many times before in other projects.

 

So the next step I did was trying to install DBD::mysql as per some of the error messages I got from the application, this module was missing (which was correct, as I started with a plain Debian container). So I tried to install, but first I needed to get the mysql-config tool installed:

apt-get install libmariadb-dev-compat

 

After that I tried again with:

cpanm install DBD::mysql

 

and this failed, with the errors in the log:

….

DBD::mysql requires MySQL 8.x or newer for building. Version reported by mysql_config --version

: 10.11.6 at Makefile.PL line 451, <PIPE> line 3.

-> N/A

-> FAIL Configure failed for DBD-mysql-5.008. See /root/.cpanm/work/1725256110.1036/build.log f

or details.

…..

 

As there is a DBD::MariaDB, I thought: Yes that one would most likely do the trick and I could actually install it. For this to be used I also needed to set the configuration in gbrowse to use the MariaDB Connector (which is ok because this is what I just installed). However this does not work out as gbrowse used Bio::DB:SeqFeature:Store::DBI::mysql and now tries to use Bio::DB:SeqFeature:Store::DBI::MariaDB which is a module that does not exist.

 

It might be that everything just works fine if I install the mysql libraries into the container, sourced from MySQL directly instead of relying on Debian packages that come out of the box. But this feels kind of an ugly workaround.

 

Greetings,

 

Kai

[Adam Sjøgren]

Kai writes:

> So the next step I did was trying to install DBD::mysql as per some of
> the error messages I got from the application, this module was missing
> (which was correct, as I started with a plain Debian container).

Have you tried installing the Debian package that provides DBD::mysql?

· https://packages.debian.org/bookworm/libdbd-mysql-perl

That would be the first thing I would try, rather than building the
module from source :-)

Best regards,

Adam

--
"It was called Tinderbox so we could make the joke, Adam Sjøgren
'the tree is on fire'. Puns are very important in as...@koldfront.dk
naming tools."

[Peter Cock]

It sounds to me (as an interested third party observer of BioPerl), that there

is value in adding a MariaDB module to Bio::DB:SeqFeature:Store::DBI

even if it is as simple as an alias or near copy of the MySQL module?

It also sounds like a bug in DBD::MySQL if it refuses to work with version

10 (from MariaDB) by insisting on version 8. Do you know where their

issue tracker is? I would be surprised if that has not been discussed.

Peter

[Peter Cock]

Found it, and https://github.com/perl5-dbi/DBD-mysql/issues/423 seems

to answer my question, quoting what I took as the key point:

"DBD::mysql no longer builds against MariaDB. This also removes complexity."

So it looks like adding DBD::MariaDB support to BioPerl is the best solution?

Peter

[Timothy Parnell]

Regarding support for DBD::MariaDB, here is one (naive), hack solution: Simply copy the Bio/DB/SeqFeature/DBI/mysql.pm file as mariadb.pm, and search and replace “MySQL” with “MariaDB” as appropriate (watch capitalization, etc). Since MariaDB is (more or less) a drop-in replacement for MySQL, in theory this should work just fine. If I remember correctly, the SQL commands and structures are not terribly complicated and fairly standard, so I wouldn’t expect much more modification. If it does work, it might be worth a pull request.

Tim

[Fields, Christopher J]

I agree, this should be a simple way to get it going, and though it would likely result in code duplication it could eventually require settings specific to MariaDB (though as Tim mentioned it’s essentially a drop in replacement).

 

Kai, if you want to make a fork and then submit a pull request, I’m more than happy to test and add this to a new release.  One thing that would be beneficial is if you can get some basic tests running for it; IIRC this is a bit hacked in for the various DBI plugin modules.

 

chris

 

https://urldefense.com/v3/__https://mailman.open-bio.org/mailman/listinfo/bioperl-l__;!!DZ3fjg!6UzjoYFBT-qupAoTKJSjDhDnzGPDtnjbpi7RwrAfMEp3vRsAu1t7n3txH3-GPeS-GLzAXmmKUM3-Mukaza218K2MedsdUoU6Vfw$

[Schlachter, Kai]

Hi,

 

So I did my first steps towards creating a perl module although I am not a perl expert.

I also created a pull request to add MariaDB-Support to the project.

I did not find any test specifications, so I left those out of scope for the moment.

 

I will also try to see what happens if I add the module manually to our containers as long as it is not included in CPAN.

 

Greetings,

 

Kai Schlachter

[Schlachter, Kai]

Hi,

 

just to give you an update:

For the purpose I needed it, I was able to change the files to use MariaDB.

See the following pull request:

https://github.com/bioperl/Bio-DB-SeqFeature/pull/4

 

Greetings,

Kai Schlachter

[Fields, Christopher J]

Thanks Kai. I merged that request and will work on a new point release for CPAN

Chris

On Sep 5, 2024, at 6:35 AM, Schlachter, Kai <kai.sch...@dkfz-heidelberg.de> wrote:



_______________________________________________
Bioperl-l mailing list
Biop...@bioperl.org
https://urldefense.com/v3/__https://mailman.open-bio.org/mailman/listinfo/bioperl-l__;!!DZ3fjg!-QX_z6zzyv4Wfl0sG0ILBNkvGSm0XUsW1FwDZtQGbo18vGmLvLHiaY8DSClaWt0ttUvPazvFJM6IIp84XoVjwj8TbAEBSQmWZkFO2nI$

[Schlachter, Kai]

Hi Chris,

This is great news und will facilitate our container building process.

I also hope this enables many projects to use the most recent tech stack on debian based distributions (many projects I got involved were using CentOS and now found themselves in a bad position as the support ended in June this year).

Greetings,

Kai

---

Von: Fields, Christopher J <cjfi...@illinois.edu>
Gesendet: Mittwoch, 18. September 2024 14:51:11
An: Schlachter, Kai
Cc: biop...@bioperl.org

[Fields, Christopher J]

Sorry, this got delayed a bit but I just pushed out a new Bio::DB::SeqFeature release, v 1.7.5.  Let me know if you have any issues with it!

 

chris