Investigate mysql database url using password with special characters not working

[Oxyaxion]

Data

• I don't know I always use the "latest" branch -> go install github.com/go-shiori/shiori@latest :
• Mysql:
• FreeBSD:
• N/A:

Describe the bug / actual behavior

I've been using shiori for months. I do regular backup of my instances.

Until now all was fine.

I have the good idea to do an upgrade with another go install github.com/go-shiori/shiori@latest

Look like the new version changed the way we have to connect on Mysql

With something like this :

SHIORI_DATABASE_URL="mysql://username:password@(hostname:port)/database?charset=utf8mb4"

Problem this is a very poor connection method that doesn't allow strong passwords with special characters ... As I'm using hard password with ! and "#?" .

I've tried changing the database password to something simpler, but I still can't get shiori to connect to mysql .... Unable to correctly parse the mysql connection string ... Documentation is not very clear about the good parameter to use.

After several tries I decided to rollback from a previous backup (which worked perfectly I never updated it or touch anything for month). and reset my original MySQL password.

And since I only have this message on the log when I start shiori ...

"Error running migration: no migration found for version 5: read down for version 5 migrations/mysql: file does not exist"

I really don't know what happens ... And what is the missing file ?!?

The database password is correct and I can connect to it.

[fmartingr]

Hey @Oxyaxion, sorry that you are experiencing problems with the latest Shiori version. Let me address the two issues separately:

1. The connection URL can contain special characters (I'm guessing you refer to that when you say strong password), but you have to URL encode the entire parameter, since it's an URL, that should work.
2. Do you know which version you rolled back to? The five you see is the migration number 5 which adds a column to the database. I believe the system is trying to rollback the database changes as well, but since the version is older it can't find this file.

Let me know if you're willing to try number 1 again or if you prefer to keep as you are. if you do, please share the contents of your schema_migrations table to see which state it is in and we can follow from there.

[Oxyaxion]

Thanks so much for your quick response.

1 - Yes by strong password , I meant long password with a lot of special characteres.Thank for clarification I will try with a URL Encoder. Maybe it wil help a lot !

2 - Good question, how can I have the version in cli ? I guess there is a file somewhere in the repository that can give me the shiori version ... because looks like there is no such option like cli with --version or -v to catch the version ...

[Oxyaxion]

I tried again with the 1.60 version and passsword url encoded , It can parse correctly the password field.

But now it looks like it is not able to correctly parse the ip:port field or connect to the database

setenv SHIORI_DATABASE_URL "mysql://shiori_user:PASSWORDENCODED@tcp(172.16.0.20:3306)/shiori_db?charset=utf8mb4" ; /usr/local/sbin/shiori server -p 7070 --log-level debug

Output :

error opening database error="failed to parse database URL: parse \"mysql://shiori_user:PASSWORDENCODED@tcp(172.16.0.20:3306)/shiori_db?charset=utf8mb4\": invalid port \":3306)\" after host"

With a slightly different option, removing the parentheses, the error is different, but no better.

setenv SHIORI_DATABASE_URL "mysql://shiori_user:[email protected]:3306/shiori_db?charset=utf8mb4" ; /usr/local/sbin/shiori server -p 7070 --log-level debug

FATA[2024-03-24T10:04:00Z] error opening database  error="default addr for network '172.16.0.20:3306' unknown"

MySQL runs on another instance.

[Oxyaxion]

I finally succeeded in logging in with a password WITHOUT special characters.

Even encoded to UTF8 there are some characters that obviously don't work ! I don't know which ones .

The good method is :

# setenv SHIORI_DATABASE_URL "mysql://shiori_user:PasswordWithoutSpecialChar@tcp(172.16.0.20)/shiori_db?charset=utf8mb4" ; /usr/local/sbin/shiori server -p 7070 --log-level debug

Also luckily the port is by default 3306 because it looks like it also does not work by giving the port on the URL ....

FATA[2024-03-24T10:47:28Z] error opening database error="failed to parse database URL: parse \"mysql://shiori_user:PasswordWithoutSpecialChar@tcp(172.16.0.20:3306)/shiori_db?charset=utf8mb4\": invalid port \":3306)\" after host"

That could be an issue with specifics setup.

In any case, thanks for pointing me on the "UTF Encoding" and for your quick support !

[fmartingr]

This seems weird, let me reopen the issue and leave this open to investigate. Glad it ended up worked on your end even if you had to lower your database security.