Possible bug with locking on mysql

[TBSliver]

Found an interesting bug when using on github actions with latest version (1.2.0)

basic setup is:

• spin up mariadb:latest as a service
• install App::Sqitch and DBD::mysql
• setup the target db using sqitch config --user target.my_setup.uri 'db:mysql://root:pass@127.0.0.1:$port/dbname'
• run sqitch deploy my_setup

On 1.2.0, I get an error of Unknown database 'sqitch'

Running it on 1.1.0 I get no issue and it creates the sqitch DB as needed

Only thing I can see thats changed is the new locking on mysql, I wonder if its exploding while trying to lock things?

[theory]

That is odd. Does it work if you comment-out the locking? You can "comment" it out by simply renaming try_lock in mysql.pm to something else (like _try_lock). That will disable the locking.

[ambs]

Hi, @theory
I had the same problem.

I did what you suggested and I got into the next issue: Authentication plugin 'caching_sha2_password'.
What is strange, as for complaining on the locking it would need to authenticate first.

But trying to go ahead, in order to debug this, is there anywhere in sqitch configuration where I can configure SSL?
As far as I recall, this is just adding ssl option to DBD::mysql DSN: mysql_ssl=1

Thanks
ambs

[theory]

So disabling _try_lock got you to the next failure? Odd that there is no error without disabling _try_lock.

Try adding ?mysql_ssl=1 to the target URI.

[ambs]

Got some more info.

I am not sure what is used if you do not specify a client path...
I was unable to make it work with the ?mysql_ssl=1 parameter

But then I just realized that using mysql in the command line I do not need any special parameter.
So, I specified the client path in sqitch.conf and I got authenticated.

Got back and recovered try_lock (after dropping the sqitch database), and it is still complaining on the database not existing.

As a side note that might be relevant.
I am using Oracle's mysql, version 8.

At the moment I got it working for me, but I am happy to help debugging whatever you need.
ambs

[ambs]

And yes, it's quite strange that the authentication failure did not show with try_lock.
ambs

[theory]

Okay, so it's working, but with the locking bit disabled? Without that it created the sqitch database?

Is mysql in your path?

[theory]

The CI/CD tests run fine against MySQL 8, most recently here, so I wouldn't think that would be an issue…

[ambs]

Yeah, mysql is in /usr/bin/mysql, in my path, and yes, with locking disabled, it created the sqitch database.

[ambs]

When you think things can't be stranger...

$   sqitch status
# On database db:mysql://root:@127.0.0.1:3306/MediaDB
Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
$   mysql -u root -p -P 3306 -h 127.0.0.1
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.29 MySQL Community Server - GPL
[...]
mysql> ^D
$   sqitch status
# On database db:mysql://root:@127.0.0.1:3306/MediaDB
No changes deployed

Isn't programming a joy?

[theory]

I am so mystified. What OS and MySQL install are you using? I'd like to find a Docker image to try to replicate these issues.

[ambs]

I am so surprised as you. I have no cue where to look to find more answers.
I am running Linux Mint, mysql client version community, 8.0.29
The server is running in a docker image, binding port 3306, with image mysql. Noticed now that I did not specify any version, so it should be running latest.
Happy to help as far as you give me hints where you want me to look :)