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

Wise Monks,

I am migrating from MySQL to MariaDB and my usual Perl DBD dsn which reads credentials from file (instead of storing/hardcoding then in perl code) fails.

Previously, with MySQL, the connection drill was something like:

my $dsn = 'DBI:mysql:database=testdb;mysql_read_default_file=creds.txt
+';
my @dsns = (
 $dsn,
 '', # username is read from file
 '', # password is read from file
 {"RaiseError" => 1, "PrintError" => 1}
);
my $dbh = eval { DBI->connect(@dsns) };
die $@ if $@;

[download]

Where the credentials file creds.txt reads like this:

[client]
user=testuser
password=apass

[download]

Above code is equivalent to running mysql client on the command line like mysql --defaults-extra-file="creds.txt". This command logs me in the DB fine from the CLI.

Now, modifying the dsn a bit for DBD::MariaDB:

my $dsn = 'DBI:MariaDB:database=testdb;mariadb_read_default_file=creds
+.txt';
...

[download]

Fails with failed: Access denied for user 'testuser'@'localhost' (using password: NO). Notice that it reads the username (testuser) correctly from the file. But ignores(?) the password. But using the CLI interface it logs me in fine (reads both username and password).

It is also weird that if I give it a fake file in the dsn above, it does not say (using password: NO). And the username is my login name. All these make me think that all works fine except from reading the password. Any hints?

thank you // bw, bliako

Replies are listed 'Best First'.
Re: MariaDB : read credentials from file fails (via dsn option mariadb_read_default_file)
by choroba (Cardinal) on Nov 03, 2023 at 22:18 UTC
    It seems to work for me, but the syntax of the config file is different: 
    [client]
user=testuser;password=apass

    [download]
    Update: This is not true. See below for what's going on and how to really fix it.

    map{substr$_->[0],$_->[1]||0,1}[\*||{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^*ARGV,3]
[reply]
[d/l]
[select]

      I was away from the computer over the weekend while I saw your answer and thought Doh! but I am trying it out now and it fails as it reads the username till the end of line i.e. it includes the password as part of the username. My MariaDB version is 10.8.8 in linux.

[reply]
        You are right, of course. I'm sorry for the confusion.

        But I think I've found the solution. The format of the file is OK with a newline, the problem is the invocation of the connect method: if you specify an empty string for the password, it has precedence over the one specified in the config. You need to specify undef for the password to get it replaced by the configuration value. 

        my @dsns = (
    $dsn,
     '',    # username is read from file
     undef, # password is read from file!!
     {RaiseError => 1, PrintError => 1}
);

        [download]
        map{substr$_->[0],$_->[1]||0,1}[\*||{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^*ARGV,3]
[reply]
[d/l]
[select]
Re: MariaDB : read credentials from file fails (via dsn option mariadb_read_default_file)
by choroba (Cardinal) on Nov 05, 2023 at 21:33 UTC
    Also note that the mariadb_read_default_file is equivalent to --defaults-file, not --defaults-extra-file (but it probably doesn't matter in the context of the question). See Custom Option File Locations.

    map{substr$_->[0],$_->[1]||0,1}[\*||{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^*ARGV,3]
[reply]
[d/l]
[select]
Re: MariaDB : read credentials from file fails (via dsn option mariadb_read_default_file)
by Polyglot (Chaplain) on Nov 06, 2023 at 02:42 UTC
    I use MariaDB with the exact same incantations as for mysql. In other words, I use the same DBI, the same DSN (with "mysql" in it instead of "MariaDB"), etc. I wonder if you might be changing more than is necessary. Essentially, whereas the code says "mysql", it actually points to the MariaDB installation (I don't have both on the same server, so it cannot possibly be accessing the database from anything other than the MariaDB installation). Now, perhaps someone will inform me that I have been doing it all wrong...but it has always worked for me this way.

    Blessings,

    ~Polyglot~

[reply]
      No, it should work this way, too. But DBD::MariaDB fixed some bugs DBD::mysql had and wasn't able to fix. Especially given your interest in languages using non-ASCII characters, you might be interested in how UTF-8 handling is different in the two Perl drivers.

      map{substr$_->[0],$_->[1]||0,1}[\*||{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^*ARGV,3]
[reply]
[d/l]
        For some years now I have been consistently creating my databases with the utf8mb4 charset. As long as the Perl script outputs to the DB in utf8 and reads from it in utf8, there have been no issues. How is the MariaDB improved over this?

        Blessings,

        ~Polyglot~

[reply]

      Thanks for the suggestion but I don't have DBD::mysql installed and so using it in the dsn fails for me.

[reply]

Back to Seekers of Perl Wisdom