Bedrock MySQL plugin does not work with MySQL 5.7 clients

[ahughes02]

5.7 client fails with ERROR 2027 Malformed packet, 5.6 works fine

[quinthar]

Hm, that's interesting. Can you copy/paste the terminal session that you're testing with so we can see the exact query and such that you were using? Also, can you share the exact version string of the mysql client (eg, run mysql --version)? Thanks!

[ahughes02]

In this case Bedrock is running locally

austin@vm:~/Desktop$ mysql --version
mysql  Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using  EditLine wrapper
austin@vm:~/Desktop$ mysql -h 127.0.0.1
ERROR 2027 (HY000): Malformed packet

I can get to bedrock with nc as expected

austin@vm:~/Desktop$ nc localhost 8888
ping

200 OK
commitCount: 1
nodeName: bedrock
processingTime: 0
totalTime: 0
waitTime: 0

If I revert to the MySQL 5.6 client it works.

[quinthar]

Hey there, sorry for the delay on this. Can you please run your test again, except start Bedrock with the -v parameter (to enable verbose logging), and then send me a copy of the subset of the /var/log/syslog showing where you attempt to connect to Bedrock using the MySQL client? Thanks!

[quinthar]

FYI, I've posted an Upwork job here if anybody would like to fix it and earn a quick $500: https://www.upwork.com/jobs/~01ea4d5a5acac33758

[ghost]

root@u1604:~# ps auxww | grep bedrock
root      3895  0.0  0.4 400332  9768 ?        Ssl  15:34   0:00 /usr/sbin/bedrock -fork -nodeName bedrock -db /var/lib/bedrock/bedrock.db -serverHost 0.0.0.0:8888 -nodeHost 0.0.0.0:8889 -priority 200 -pidfile /var/run/bedrock.pid -quorumCheckpoint 100 -readThreads 4 -plugins status,db,jobs,cache,mysql -v -cache 10001

i.e. -v verbose is on already (after normal bedrock install)

here is the output:

root@u1604:~# systemctl status bedrock
● bedrock.service - LSB: Start the Expensify Bedrock jobs/scheduling/cache server.
   Loaded: loaded (/etc/init.d/bedrock; bad; vendor preset: enabled)
   Active: active (running) since Sun 2017-01-08 15:34:26 AEDT; 2min 4s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 3874 ExecStop=/etc/init.d/bedrock stop (code=exited, status=0/SUCCESS)
  Process: 3884 ExecStart=/etc/init.d/bedrock start (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/bedrock.service
           └─3895 /usr/sbin/bedrock -fork -nodeName bedrock -db /var/lib/bedrock/bedrock.db -serverHost 0.0.0.0:8888 -nodeHost 0.0.0.0:8889 -priority 200 -pidfile /v

Jan 08 15:35:18 u1604 bedrock[3895]: xxxxx (STCPServer.cpp:57) operator() [main] [dbug] Accepting socket from '127.0.0.1:38230' on port 'localhost:3306'
Jan 08 15:35:18 u1604 bedrock[3895]: xxxxx (BedrockServer.cpp:473) postSelect [main] [info] Plugin 'MySQL' accepted a socket from '127.0.0.1:38230'
Jan 08 15:35:18 u1604 bedrock[3895]: xxxxx (MySQL.cpp:252) onPortAccept [main] [info] {MySQL} Accepted MySQL request from '127.0.0.1:38230'
Jan 08 15:35:18 u1604 bedrock[3895]: xxxxx (STCPManager.cpp:152) postSelect [main] [dbug] Connection to '127.0.0.1:38230' died (recv=0, send=1)
Jan 08 15:35:18 u1604 bedrock[3895]: xxxxx (STCPManager.cpp:255) closeSocket [main] [dbug] Closing socket '127.0.0.1:38230'
Jan 08 15:36:24 u1604 bedrock[3895]: xxxxx (STCPServer.cpp:57) operator() [main] [dbug] Accepting socket from '127.0.0.1:38232' on port 'localhost:3306'
Jan 08 15:36:24 u1604 bedrock[3895]: xxxxx (BedrockServer.cpp:473) postSelect [main] [info] Plugin 'MySQL' accepted a socket from '127.0.0.1:38232'
Jan 08 15:36:24 u1604 bedrock[3895]: xxxxx (MySQL.cpp:252) onPortAccept [main] [info] {MySQL} Accepted MySQL request from '127.0.0.1:38232'
Jan 08 15:36:24 u1604 bedrock[3895]: xxxxx (STCPManager.cpp:152) postSelect [main] [dbug] Connection to '127.0.0.1:38232' died (recv=0, send=1)
Jan 08 15:36:24 u1604 bedrock[3895]: xxxxx (STCPManager.cpp:255) closeSocket [main] [dbug] Closing socket '127.0.0.1:38232'

[ghost]

There was a breaking change in MySQL protocol version 5.7 related to depreciating "old" password hashing authentication facility. See some explanation here: http://dev.mysql.com/doc/refman/5.7/en/mysql-command-options.html#option_mysql_secure-auth

The suggested PR #79 makes Bedrock send full initial handshake packet including authentication plugin name which I believe is a required field in protocol since version 5.7. Instead of supporting full hashed password authentication Bedrock accepts any user/password provided by MySQL client as normal authentication is not supported.

Initial challenge random bytes are for now hardcoded, see code comments for further details.

[quinthar]

This should be fixed in HEAD, but leaving this issue open until it's been deployed. Reassigning to @righdforsa

[willfong]

Hi!

I built from the latest source, and looks like this is broken for the MariaDB 10.0 client:

root@ubuntu:~# Bedrock/bedrock -version
ee167e79892286715b34b8be24be950c1f77a307
root@ubuntu:~# apt-get install mysql-client-5.7 -y > /dev/null
root@ubuntu:~# mysql --version
mysql  Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using  EditLine wrapper
root@ubuntu:~# mysql -h127.0.0.1 -e "SELECT 1 AS foo, 2 AS bar;"
+------+------+
| foo  | bar  |
+------+------+
|    1 |    2 |
+------+------+
root@ubuntu:~# apt-get install mariadb-client -y > /dev/null
root@ubuntu:~# mysql --version
mysql  Ver 15.1 Distrib 10.0.29-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
root@ubuntu:~# mysql -h127.0.0.1 -e "SELECT 1 AS foo, 2 AS bar;"
ERROR 2012 (HY000): Error in server handshake
root@ubuntu:~# 

Please let me know if I should open this in a new issue.

Thanks, -will

[tylerkaraszewski]

I also can't connect to the bedrock MySQL plugin using the current head of the master branch, with this error:

 ~:$  mysql -h127.0.0.1
ERROR 2012 (HY000): Error in server handshake
 ~:$  mysql -V
mysql  Ver 14.14 Distrib 5.6.33, for debian-linux-gnu (x86_64) using  EditLine wrapper
 ~:$

This is on ubuntu 14.04, with a vanilla installation of mysql-client-5.6 from apt-get.

[ghost]

@tylerkaraszewski one of possible solutions would be to specify --default-auth=mysql_native_password option so the client won't try to switch to old_password in case of 5.6 i.e.

denis@u16:~/dev/bedrock/tmp$ mysql -V
mysql  Ver 14.14 Distrib 5.6.16, for debian-linux-gnu (x86_64) using  EditLine wrapper
denis@u16:~/dev/bedrock/tmp$ mysql -h 127.0.0.1 -p 3306 --default-auth=mysql_native_password
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: bedrock ce734f6d0aef44ac4f6dc632b021faeff7457e21

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> select 1
    -> ;
+------+
| 1    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

[ghost]

Another alternative would be to detect attempt of switching authentication plugin (server now proposes native, client wants old in case of version 5.6) and respond accordingly.

[quinthar]

To clarify, what does MySQL do, and are we doing exactly that? The goal would be to exactly emulate the most recent version of MySQL I would think.

[ghost]

@quinthar see https://dev.mysql.com/doc/internals/en/connection-phase.html

We're proposing in the latest version of Bedrock code mysql_native_password authentication. 5.7 client also have mysql_native_password by default so it happily accepts it. But 5.6 has mysql_old_password by default and it sends us "authentication method switch" packet as a response. Bedrock has no support of multiple authentication methods and switching between these.

So Bedrock do not really recognize the "method switch" request and just sends "OK" which client rejects as negotiation failure.

MySQL server supports generally multiple pluggable authentication plugins (and authentication as such) which Bedrock does not hence the difference.

Proper emulation would require full support for authentication.

[quinthar]

How difficult would it be to add a minimal level of authentication emulation to support these clients out of the box, without requiring a command line flag? My concern is that basically nobody is going to realize there is a flag that will make this work, and will instead just conclude Bedrock is broken.

On Sat, Mar 25, 2017 at 9:29 PM, Denis Ivaykin notifications@github.com wrote:

@quinthar https://github.com/quinthar see https://dev.mysql.com/doc/ internals/en/connection-phase.html

We're proposing in the latest version of Bedrock code mysql_native_password authentication. 5.7 client also have mysql_native_password by default so it happily accepts it. But 5.6 has mysql_old_password by default and it sends us "authentication method switch" packet as a response. Bedrock has no support of multiple authentication methods and switching between these.

So Bedrock do not really recognize the "method switch" request and just sends "OK" which client rejects as negotiation failure.

MySQL server supports generally multiple pluggable authentication plugins (and authentication as such) which Bedrock does not hence the difference.

Proper emulation would require full support for authentication.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/Expensify/Bedrock/issues/55#issuecomment-289257676, or mute the thread https://github.com/notifications/unsubscribe-auth/AAjG0s5pqay1UH_UjS74EKnLKPY7rCLUks5rpemSgaJpZM4K0eU2 .

[ghost]

The very minimum to support both 5.6 and 5.7 w/o CLI flags would be to handle "auth method switch" packet and reply so 5.6 would have an impression that it got the method switched to "old" one.

This would require detecting new connection time packet type and serializing another kind of response packet.

[quinthar]

I don't have a clear understanding of what that entails, but based on that description that doesn't sound too bad. Can you give that a shot?

On Sat, Mar 25, 2017 at 11:25 PM, Denis Ivaykin notifications@github.com wrote:

The very minimum to support both 5.6 and 5.7 w/o CLI flags would be to handle "auth method switch" packet and reply so 5.6 would have an impression that it got the method switched to "old" one.

This would require detecting new connection time packet type and serializing another kind of response packet.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/Expensify/Bedrock/issues/55#issuecomment-289261466, or mute the thread https://github.com/notifications/unsubscribe-auth/AAjG0l-uR7XRCzRHOEcgMCDA9JNrn72Gks5rpgTngaJpZM4K0eU2 .

[ghost]

@quinthar actually I was wrong, it required protocol version upgrade to 4.1 see https://github.com/Expensify/Bedrock/pull/127

It'd deal with MariaDB as well (tested it).