sqitch deploy not working if .my.cnf password is declared with single quotes

[Shto1337]

Just started with sqitch for mysql.

Installed cpan MySQL::Config according to #261
-> MySQL::Config is up to date (1.04).

Setup is an Ubuntu 22 trying to connect to mysql instance with version 8.0.36-28 Percona Server (GPL), Release '28', Revision '47601f19'

Given the following ~/.my.cnf file, deploy will not work if the password is quoted in single quotes:

$ cat ~/.my.cnf
[mysql]
user=me_user
host=my.awesome.host
password='MyPassword1!'

[client]
user=me_user
host=my.awesome.host
password='MyPassword1!'

$ sqitch deploy 

Access denied for user 'me_user'@'10.248.99.99' (using password: YES)

Although, this is perfectly valid with the standalone mysql client and defaults file

$ mysql --defaults-file=~/.my.cnf
mysql: Warning: Charset id '33' csname 'utf8' trying to replace existing csname 'utf8mb3'
mysql: Warning: Charset id '83' csname 'utf8' trying to replace existing csname 'utf8mb3'
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 147
Server version: 8.0.36-28 Percona Server (GPL), Release '28', Revision '47601f19'$

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]>

[theory]

Could you have multiple versions of Perl installed, with Sqitch built against one and MySQL::Config built against another? To see, what's the output of these two commands?

head -1 $(which sqitch)
which perl

[Shto1337]

head -1 $(which sqitch)
#!/usr/bin/perl -w -CA

which perl
/usr/bin/perl

dpkg -l | grep perl | grep mysql
ii libdbd-mysql-perl:amd64 4.050-5ubuntu0.22.04.1 amd64 Perl5 database interface to the MariaDB/MySQL database

[theory]

Sure looks like the same Perl. How did you install MySQL::Config?

[Shto1337]

Said in the first post, as you mentioned in #261 (comment)

cpan MySQL::Config

[theory]

Weird. Presumably this prints something:

/usr/bin/perl -MMySQL::Config -E 'say MySQL::Config->VERSION'

The docs say:

For MySQL, if the MySQL::Config module is installed, usernames and passwords can be specified in the /etc/my.cnf and ~/.my.cnf files. These files must limit access only to the current user (0600). Sqitch will look for a username and password under the [client] and [mysql] sections, in that order.

And it sure looks like that's how your file is configured, though maybe check the permissions?

What does your sqitch.conf look like?

The other thing to do is to put some debugging here:

https://github.com/sqitchers/sqitch/blob/a7dfdb3b16979bf9cdd414af10eb1a7236a36066/lib/App/Sqitch/Engine/mysql.pm#L55C5-L62

Perhaps something like this:

--- a/lib/App/Sqitch/Engine/mysql.pm
+++ b/lib/App/Sqitch/Engine/mysql.pm
@@ -56,8 +56,11 @@ has _mycnf => (
     is => 'rw',
     isa     => HashRef,
     default => sub {
-        eval 'require MySQL::Config; 1' or return {};
-        return scalar MySQL::Config::parse_defaults('my', [qw(client mysql)]);
+        eval 'require MySQL::Config; 1' or do { print STDERR "MySQL::Config failed to load: $@\n"; return {};
+        my $cfg = scalar MySQL::Config::parse_defaults('my', [qw(client mysql)]);
+        use Data::Dumper;
+        say "Config: ", Data::Dumper::Dumper($cfg);
+        return $cfg;
     },
 );
 

[Shto1337]

/usr/bin/perl -MMySQL::Config -E 'say MySQL::Config->VERSION'
1.04

/etc/my.cnf does not exist
~/.my.cnf has 600

$ ls -lah ~/. | grep my
-rw-------  1 user user  198 Nov 19 23:39 .my.cnf

cat sqitch.conf
[core]
	engine = mysql
	# plan_file = sqitch.plan
	# top_dir = .
# [engine "mysql"]
	# target = db:mysql:
	# registry = sqitch
	# client = mysql
[engine "mysql"]
	client = /usr/bin/mysql
        target = db:mysql://my.awesome.host/sakila

I tried modifying the code as you said, here's the result and its weird:

$ sqitch deploy
Unable to load App::Sqitch::Engine::mysql
Trace begun at /usr/share/perl5/App/Sqitch/Engine.pm line 152
App::Sqitch::Engine::load('App::Sqitch::Engine', 'HASH(0x55ca32b17dc8)') called at /usr/share/perl5/App/Sqitch/Target.pm line 69
App::Sqitch::Target::__ANON__('App::Sqitch::Target=HASH(0x55ca330a1f18)') called at (eval 316) line 22
App::Sqitch::Target::engine('App::Sqitch::Target=HASH(0x55ca330a1f18)') called at /usr/share/perl5/App/Sqitch/Command/deploy.pm line 130
App::Sqitch::Command::deploy::execute(undef) called at /usr/share/perl5/App/Sqitch.pm line 207
App::Sqitch::try {...}  at /usr/share/perl5/Try/Tiny.pm line 100
eval {...} at /usr/share/perl5/Try/Tiny.pm line 93
Try::Tiny::try('CODE(0x55ca315da4e8)', 'Try::Tiny::Catch=REF(0x55ca32af4768)') called at /usr/share/perl5/App/Sqitch.pm line 227
App::Sqitch::go('App::Sqitch') called at /usr/bin/sqitch line 15

Why does it states it cannot load Unable to load App::Sqitch::Engine::mysql if I can deploy it successfully if I don't include " or ' for my credentials in my ~/.my.cnf file...?

Anyway, I tried reinstalling it by using the command
cpan App::Sqitch::Engine::mysql which passed all tests
-> Same issue but the debug code remained in the mysql.pm file and the detailed error does not appear anymore(as above), only the initial issue, namely "Access denied for..."

If it helps in any way, I'm using the latest sqitch release, namely 1.2.1-1 installed as per documentation from https://sqitch.org/download/debian/
apt-get install sqitch libdbd-pg-perl postgresql-client libdbd-sqlite3-perl sqlite3

If it helps, I can give detailed steps to reproduce this with two VM's running ubuntu 22.04 LTS installed from scratch using the official iso/packages.

[theory]

No, it's because I had a syntax error in the patch; Line 59 needs another } at the end. Here's a fixed patch:

--- a/lib/App/Sqitch/Engine/mysql.pm
+++ b/lib/App/Sqitch/Engine/mysql.pm
@@ -56,8 +56,11 @@ has _mycnf => (
     is => 'rw',
     isa     => HashRef,
     default => sub {
-        eval 'require MySQL::Config; 1' or return {};
-        return scalar MySQL::Config::parse_defaults('my', [qw(client mysql)]);
+        eval 'require MySQL::Config; 1' or do { print STDERR "MySQL::Config failed to load: $@\n"; return {} };
+        my $cfg = scalar MySQL::Config::parse_defaults('my', [qw(client mysql)]);
+        use Data::Dumper;
+        say "Config: ", Data::Dumper::Dumper($cfg);
+        return $cfg;
     },
 );
 

Sorry about that.

[Shto1337]

Okay, seems that when I tried to reinstall via
cpan App::Sqitch::Engine::mysql
It was installed in a new location, namely:
/usr/local/share/perl/5.34.0/App/Sqitch/Engine

Changed the code there and reran sqitch deploy:

Config: $VAR1 = {
          'password' => '\'MyPassword1!\'',
          'host' => 'my.awesome.host',
          'user' => 'me_user'
        };

Access denied for user 'me_user'@'10.248.99.99' (using password: YES)

I also tried to put the password in " instead of ', but its not escaped:
'password' => '"MyPassword1!"',

[theory]

Ah-ha, very revealing. Looks like MySQL::Config doesn't support quoted values. Looks like its last release was in 2008, so not totally surprising. Can you do without the quoting?

[Shto1337]

I cannot because some passwords are more complex and need to be quoted... Hence... this issue opened :)

Should i open a bug request at https://rt.cpan.org/Public/Dist/Display.html?Name=MySQL-Config ...?
From what i saw from his other repos(of Darren), they don't seem to be maintained at all...

[theory]

That's the place to start, I guess, though the one ticket here has been open and unanswered for close to 8 years :-(

This might require someone starting over.