packet 254 not implemented -- still struggling with

[marcstein]

I know that this is not a new issue, but I'm trying to connect to a MySQL 8 server, which has been set to use legacy (v5.7) authentication. This is working in other languages that have an issue with the new authentication process (e.g. Nim), but I can't get it to work in Crystal. I've tried it on both OSX and Ubuntu. No difference.

I've tried the published solutions like CREATE/ALTER USER 'test'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypassword'; and the user is definitely using the native_password auth.

Short of switching languages or databases, can anyone suggest something to try?

Thanks in advance for any suggestions!

[Exception.txt] (https://github.com/crystal-lang/crystal-mysql/files/12506665/Exception.txt)

[willy610]

Into MySQL 9 now. Everyone must encourage more security.
Workaround as for ver 8 is not possible anymore as I understand.
I will try too contribute by implementing the 'sha2_password'
There are work in progress as I understand.

Please give me links etc to what is achieved so far. (Especially Crystal source..)
(Someone locked into LUA. Perhaps also Rust will give some feedback. And Ruby and Js)

[ysbaddaden]

The rust crate implements the protocol (not a wrapper) and has support for the caching_sha2_password auth. It would likely be helpful to understand how it works, in addition to the mysql-client C library maybe.

[bcardiff]

The protocol documentation can be found at https://dev.mysql.com/doc/dev/mysql-server/latest/page_caching_sha2_authentication_exchanges.html

[willy610]

Thanks.
Find sources and also notes in this threads of course.
My approach is to implement caching_sha2_password only. >= mysql 9
Will remove older versions behavior in the source. In order to avoid attacks from smarter hackers than me.
Approach, but than we got 2 libraries?

Is golang worth looking at?

[bcardiff]

I don't think that dropping the current authentication method is a good call. First it would be disruptive when upgrading an application, and second, I am not sure there is a risk in the client keep supporting old authentication scheme. Is the server that should be configured to not allow it. Am I wrong?

If needed we could make it harder to use old authentication, but I would not suggest dropping the support for it.

It seems that a couple of years ago I started to do some refactors to support sha2_password in master...bcardiff:crystal-mysql:sha2_password but I didn't finish it. I don't recall any specifics so feel free to ignore it. But the key seems to be extracting the list of authentication plugins available.

Please share if you are blocked, contributions are appreciated! 🙇

[willy610]

Thank you for the link.
Got your points on dropping old behavior.
I will do more rethinking on how make maintainable source.
Perhaps by enabling cutting methods, cutting case, avoiding complex conditions mixing compile time and run time too much etc
Than we have verifications on older version of mysql server. Work to do!

[willy610]

Just for the record:
One of the variants of 'caching_sha2_password' is now working.
That is the 'PasswordFastAuthSuccess' .
( And the old one 'mysql_native_password' works)

Same image works on

• Mac Mini M4 Pro and mysql Ver 9.0.1 for macos15.1 on arm64 (Homebrew)
  no implement of 'mysql_native_password'
  and on
• Mac Mini M2 Pro and mysql Ver 8.0.3 for macos14.2 on on arm64 (Homebrew)
  implements 'mysql_native_password' only

Still a lot of work to do!!
By the way - allow me to say crystal and golang are a very good environment, maridb client is well enginered, Rust will not even compile the source. Don't mention the mysql original client source (C/C++). But I love C otherwise!

I don't now if it's matter just now but I'm working a git crystal/mysql source copy and not a cloned one.

[bcardiff]

👏

I'm not sure how you are going to send a PR if you are not working from a fork. But let's not make that a stopper.

Feel free to send your progress somehow in case you rather have feedback at this stage.

Thanks

[willy610]

The capability 'perform_full_authentication' defined by mysql server seems to be hard to implement.
And perhaps also unnecessary?

Looking into the mysqlserver 'sql/aut/sha_2password.cc' I found
/*
We either failed to authenticate or did not find entry in the cache.
In either case, move to full authentication and ask the password
*/
if (vio->write_packet(vio, (uchar *)&perform_full_authentication, 1)) return CR_AUTH_HANDSHAKE;

This is the only placed where that capability is used.
So...
Use case:

1. The client sends wrong user and/or password.
   How to provide new values in a 'perform_full_authentication' round trip?
   Suggestion is: raise 'denied'

2. The user and/or password has been valid sofar but is actually changed by someone.
   How to provide new values in a 'perform_full_authentication' round trip?
   Suggestion is: raise 'denied'

So my suggestion at the moment is to not implement 'perform_full_authentication'.
(instead raise 'denied')
It will never be used!! And it will imply a mighty set of new crystal source.

I can't see any difference - as a Use Case - whether the DB.open holds fixed or parametric values of user/password.
Either it works or one get a 'perform_full_authentication'.
Don't try to perform a meaningless? 'perform_full_authentication' but pick up proper values into source or parameters in the DB.open

What to do?

Besides that the auth 'caching_sha2_password' and 'mysql_native_password' works as mentioned above
so now I'm working on commands.
There are some issues with messages sequencing.
And styling, documentation, test/verifications and more

[bcardiff]

Adding support to just caching_sha2_password seems fine.

If you are not familiar with Docker and GitHub actions tweaking those to add CI for the additions can happen later, by someone else. No worries.

If you have instructions on how you setup a mysql using the newer authentication and that works for you, I can take care of the CI setup probably. But feel free to keep working on those for sure.

[willy610]

Thank you for your patience.

I am 'struggling' to set up a proper folder with my changes so that that folder can be easily referenced as 'dependendcy: - mysql: - path: 'myworkingmysqllib' in all my old crystal mysql project shard.yml files.
Any hints/links on that topic?

Later on we can decide how to continue. At least I will match a cloned version
but it might end up in a .zip file ? or a PR or some kind of CI. OK?

[bcardiff]

In your apps shard.yml or shard.override.yml you should have

dependencies:
  mysql:
    path: /path/to/your/crystal-mysql

After that you need to do shards install, that should leave a symlink in your apps ./lib/mysql pointing to your working directory. You can also this step manually if you prefer.

With that you should be able to compile your apps with your crystal-mysql working copy.

Feel free to attach a .zip or send a link to it here in this issue if doing git/github is a blocker.

[willy610]

In order to earn time I will attach a zip file here.
I have built and run it on (as above)

• Mac Mini M4 Pro and mysql Ver 9.0.1 for macos15.1 on arm64 (Homebrew)
  no implement of 'mysql_native_password'
  and on
• Mac Mini M2 Pro and mysql Ver 8.0.3 for macos14.2 on on arm64 (Homebrew)
  implements 'mysql_native_password' only

1. I have done nothing on README.md, version.cr and no spec
2. There is an issue with crystal spec 'should connect with credentials'.
   Got syntax error from mysqlserver
3. Error messages from crystal spec are truncated
4. There might be more issues around any user/password commands
5. I introduced 'draining' of inmessages in order to really consume the TCP bytestream. Necessary?
6. I have an active Github Desktop showing changes on my modified clone but I'm really unsure how to continue. Fork to my account? I kind of looking for contribution credits, in the long term, to show my patience family!

crystal-mysql.zip

[willy610]

Hera is a good link too on ALTER USER ...

https://dev.mysql.com/blog-archive/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/

[willy610]

Zipped source above build and executed on

• mysql Ver 8.0.28 for macos10.15 on x86_64 (Homebrew)
• on user' test' the capability classic 'mysql_native_password' was invoked
• Crystal 1.8.2 (2023-05-09), LLVM: 15.0.7