search

mysqljs / mysql

A pure node.js JavaScript Client implementing the MySQL protocol.
MIT License
18.3k stars 2.53k forks source link

Error: ER_HANDSHAKE_ERROR: Bad handshake #2287

Open digidax opened 4 years ago

digidax commented 4 years ago

Mysqljs is used in Node Red mysql-node. We have here a discussion about the Error: ER_HANDSHAKE_ERROR https://discourse.nodered.org/t/node-red-node-mysql-0-0-19-er-handshake-error-bad-handshake/20098

MySQL conversion dump:

Frame 4: 128 bytes on wire (1024 bits), 128 bytes captured (1024 bits)
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.130.188, Dst: 192.168.130.172
Transmission Control Protocol, Src Port: 3306, Dst Port: 51504, Seq: 1, Ack: 1, Len: 60
MySQL Protocol
    Packet Length: 56
    Packet Number: 0
    Server Greeting
        Protocol: 10
        Version: 5.0.77-log
        Thread ID: 3555
        Salt: YB{gp"PO
        Server Capabilities: 0xa22c
        Server Language: latin1 COLLATE latin1_swedish_ci (8)
        Server Status: 0x0002
        Extended Server Capabilities: 0x0000
        Authentication Plugin Length: 0
        Unused: 00000000000000000000
        Salt: }F$!wW/$cGxI

Frame 6: 146 bytes on wire (1168 bits), 146 bytes captured (1168 bits)
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.130.172, Dst: 192.168.130.188
Transmission Control Protocol, Src Port: 51504, Dst Port: 3306, Seq: 1, Ack: 61, Len: 78
MySQL Protocol
    Packet Length: 74
    Packet Number: 1
    Login Request
        Client Capabilities: 0xf3cf
        Extended Client Capabilities: 0x0007
        MAX Packet: 0
        Charset: utf8 COLLATE utf8_general_ci (33)
        Username: xxxxxxxxxxxx
        Password: xxxxxxxxxxxxxxxx
        Schema: test

Frame 8: 73 bytes on wire (584 bits), 73 bytes captured (584 bits)
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.130.188, Dst: 192.168.130.172
Transmission Control Protocol, Src Port: 3306, Dst Port: 51504, Seq: 61, Ack: 79, Len: 5
MySQL Protocol
    Packet Length: 1
    Packet Number: 2
    EOF marker: 254
        [Expert Info (Warning/Protocol): EOF Marker found while connection in wrong state.]
            [EOF Marker found while connection in wrong state.]
            [Severity level: Warning]
            [Group: Protocol]

Frame 9: 80 bytes on wire (640 bits), 80 bytes captured (640 bits)
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.130.172, Dst: 192.168.130.188
Transmission Control Protocol, Src Port: 51504, Dst Port: 3306, Seq: 79, Ack: 66, Len: 12
MySQL Protocol
    Packet Length: 8
    Packet Number: 3
    Request Command Unknown (67)
        Command: Unknown (67)
        Payload: 4e5a5355474f4b
            [Expert Info (Warning/Protocol): Unknown/invalid command code]
                [Unknown/invalid command code]
                [Severity level: Warning]
                [Group: Protocol]

Frame 10: 94 bytes on wire (752 bits), 94 bytes captured (752 bits)
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.130.188, Dst: 192.168.130.172
Transmission Control Protocol, Src Port: 3306, Dst Port: 51504, Seq: 66, Ack: 91, Len: 26
MySQL Protocol
    Packet Length: 22
    Packet Number: 4
    Error Code: 1043
    SQL state: 08S01
    Error message: Bad handshake

We have tested serveral things, without any success. Are any additional information required which are not posted in the node red forum thread?

Thanks a lot, Frank

dougwilson commented 4 years ago

The ER_HANDSHAKE_ERROR: Bad handshake is actually coming from the mysql server itself, not this module. So there is something about the handshake packet this module is producing that the server it is connecting to does not like. You can see in your trace the last thing is the server sending an error packet with "Bad handshake".

The question is, though, why is the server rejecting the handshake from this client? Unfortunately it is not something I am able to answer. Is there documentation somewhere on "Node Red mysql-node" that explains what it needs in the handshake?

dougwilson commented 4 years ago

Ok, sorry, I see you are saying that "Node Red mysql-node" is using this module to connect to a database. I presume that is a vanilla mysql 5.0.77 install from Oracle? If so, it is likely this module does not support that old of a mysql server version. It is not tested in CI, at least.

digidax commented 4 years ago

It's a mysql from CentOS 5 repo, I have update it to 5.0.95 without any success. A php application uses this server and some perl clients, also some bash scripts. Also in the same machine where node red connects with the mysqljs lib is installed mysql client as Ver 15.1 Distrib 5.5.64-MariaDB (CentOS 7) and it can login without any problems.

Here is the dump:

Frame 4: 128 bytes on wire (1024 bits), 128 bytes captured (1024 bits)
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.130.188, Dst: 192.168.130.172
Transmission Control Protocol, Src Port: 3306, Dst Port: 39984, Seq: 1, Ack: 1, Len: 60
MySQL Protocol
    Packet Length: 56
    Packet Number: 0
    Server Greeting
        Protocol: 10
        Version: 5.0.95-log
        Thread ID: 191305
        Salt: }[/w.x8K
        Server Capabilities: 0xa22c
        Server Language: latin1 COLLATE latin1_swedish_ci (8)
        Server Status: 0x0002
        Extended Server Capabilities: 0x0000
        Authentication Plugin Length: 0
        Unused: 00000000000000000000
        Salt: Q3K_|qECa>OS

Frame 19: 130 bytes on wire (1040 bits), 130 bytes captured (1040 bits)
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.130.172, Dst: 192.168.130.188
Transmission Control Protocol, Src Port: 40108, Dst Port: 3306, Seq: 1, Ack: 61, Len: 62
MySQL Protocol
    Packet Length: 58
    Packet Number: 1
    Login Request
        Client Capabilities: 0xa685
        Extended Client Capabilities: 0x200f
        MAX Packet: 16777216
        Charset: latin1 COLLATE latin1_swedish_ci (8)
        Username: xxxxxxxx
        Password: xxxxxxx
        Client Auth Plugin: 

Frame 21: 73 bytes on wire (584 bits), 73 bytes captured (584 bits)
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.130.188, Dst: 192.168.130.172
Transmission Control Protocol, Src Port: 3306, Dst Port: 40108, Seq: 61, Ack: 63, Len: 5
MySQL Protocol
    Packet Length: 1
    Packet Number: 2
    EOF marker: 254
        [Expert Info (Warning/Protocol): EOF Marker found while connection in wrong state.]
            [EOF Marker found while connection in wrong state.]
            [Severity level: Warning]
            [Group: Protocol]

Frame 22: 81 bytes on wire (648 bits), 81 bytes captured (648 bits)
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.130.172, Dst: 192.168.130.188
Transmission Control Protocol, Src Port: 40108, Dst Port: 3306, Seq: 63, Ack: 66, Len: 13
MySQL Protocol
    Packet Length: 9
    Packet Number: 3
    Request Command Unknown (84)
        Command: Unknown (84)
        Payload: 505848475a4e5600
            [Expert Info (Warning/Protocol): Unknown/invalid command code]
                [Unknown/invalid command code]
                [Severity level: Warning]
                [Group: Protocol]

Frame 23: 79 bytes on wire (632 bits), 79 bytes captured (632 bits)
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.130.188, Dst: 192.168.130.172
Transmission Control Protocol, Src Port: 3306, Dst Port: 40108, Seq: 66, Ack: 76, Len: 11
MySQL Protocol
    Packet Length: 7
    Packet Number: 4
    Affected Rows: 0
    Server Status: 0x0002
        .... .... .... ...0 = In transaction: Not set
        .... .... .... ..1. = AUTO_COMMIT: Set
        .... .... .... .0.. = More results: Not set
        .... .... .... 0... = Multi query - more resultsets: Not set
        .... .... ...0 .... = Bad index used: Not set
        .... .... ..0. .... = No index used: Not set
        .... .... .0.. .... = Cursor exists: Not set
        .... .... 0... .... = Last row sent: Not set
        .... ...0 .... .... = database dropped: Not set
        .... ..0. .... .... = No backslash escapes: Not set
        .... .0.. .... .... = Session state changed: Not set
        .... 0... .... .... = Query was slow: Not set
        ...0 .... .... .... = PS Out Params: Not set
    Warnings: 0
dougwilson commented 4 years ago

Right, I'm just saying that it is likely an issue with this module since it is untested in such old mysql server versions. Someone would need to investigate and fix the issue. I will try at some point, but of course pull requests are welcome if you are looking for a fix on some specific timeline.

digidax commented 4 years ago

Ok, thanks for taking time. I will discuss here to migrate to an actual MySQL / MariaDB Version.

Thank, Frank

ksaleemL1 commented 4 years ago

Hello,

I am getting a similar issue.

I am using SSL Profile of Amazon RDS. Our Aurora Postgres Cluster in AWS RDS was due maintenance. We updated it and it now has a new cert. Even since then, we have been getting below error when using the Amazon RDS profile. Can we please have a look.

Cannot connect to Database 2020-01-14 14:43:35{ Error: unable to get local issuer certificate 2020-01-14 14:43:35at TLSSocket. (/opt/app/node_modules/mysql/lib/Connection.js:320:48) 2020-01-14 14:43:35at TLSSocket.emit (events.js:182:13) 2020-01-14 14:43:35at TLSSocket.EventEmitter.emit (domain.js:442:20) 2020-01-14 14:43:35at TLSSocket._finishInit (_tls_wrap.js:629:8) 2020-01-14 14:43:35-------------------- 2020-01-14 14:43:35at Protocol._enqueue (/opt/app/node_modules/mysql/lib/protocol/Protocol.js:144:48) 2020-01-14 14:43:35at Protocol.handshake (/opt/app/node_modules/mysql/lib/protocol/Protocol.js:51:23) 2020-01-14 14:43:35at PoolConnection.connect (/opt/app/node_modules/mysql/lib/Connection.js:119:18) 2020-01-14 14:43:35at Pool.getConnection (/opt/app/node_modules/mysql/lib/Pool.js:48:16) 2020-01-14 14:43:35at /opt/app/node_modules/typeorm/driver/mysql/MysqlDriver.js:757:18 2020-01-14 14:43:35at new Promise () 2020-01-14 14:43:35at MysqlDriver.createPool (/opt/app/node_modules/typeorm/driver/mysql/MysqlDriver.js:754:16) 2020-01-14 14:43:35at MysqlDriver. (/opt/app/node_modules/typeorm/driver/mysql/MysqlDriver.js:267:51) 2020-01-14 14:43:35at step (/opt/app/node_modules/tslib/tslib.js:133:27) 2020-01-14 14:43:35at Object.next (/opt/app/node_modules/tslib/tslib.js:114:57) 2020-01-14 14:43:35at /opt/app/node_modules/tslib/tslib.js:107:75 2020-01-14 14:43:35at new Promise () 2020-01-14 14:43:35at Object.awaiter (/opt/app/node_modules/tslib/tslib.js:103:16) 2020-01-14 14:43:35at MysqlDriver.connect (/opt/app/node_modules/typeorm/driver/mysql/MysqlDriver.js:252:24) 2020-01-14 14:43:35at Connection. (/opt/app/node_modules/typeorm/connection/Connection.js:113:58) 2020-01-14 14:43:35at step (/opt/app/node_modules/tslib/tslib.js:133:27) 2020-01-14 14:43:35at Object.next (/opt/app/node_modules/tslib/tslib.js:114:57) 2020-01-14 14:43:35at /opt/app/node_modules/tslib/tslib.js:107:75 2020-01-14 14:43:35at new Promise () 2020-01-14 14:43:35at Object.awaiter (/opt/app/node_modules/tslib/tslib.js:103:16) 2020-01-14 14:43:35at Connection.connect (/opt/app/node_modules/typeorm/connection/Connection.js:105:24) 2020-01-14 14:43:35at Object. (/opt/app/node_modules/typeorm/index.js:196:82) code: 'HANDSHAKE_SSL_ERROR', fatal: true }

ksaleemL1 commented 4 years ago

Here is an article of AWS RDS Cert Updates

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL-certificate-rotation.html

dougwilson commented 4 years ago

@ksaleemL1 unless your error is _HANDSHAKE_ERROR: Bad handshake, it is not related, and so I'm hiding your posts so this issue can remain about the specific issue of _HANDSHAKE_ERROR: Bad handshake error.

ksaleemL1 commented 4 years ago

Do you want me to create a new issue for this?

From: Douglas Wilson notifications@github.com Reply-To: mysqljs/mysql reply@reply.github.com Date: Wednesday, January 15, 2020 at 1:32 PM To: mysqljs/mysql mysql@noreply.github.com Cc: Khurram Saleem KSaleem@loyalty.com, Mention mention@noreply.github.com Subject: Re: [mysqljs/mysql] Error: ER_HANDSHAKE_ERROR: Bad handshake (#2287)

@ksaleemL1https://github.com/ksaleemL1 unless your error is _HANDSHAKE_ERROR: Bad handshake, it is not related, and so I'm hiding your posts so this issue can remain about the specific issue of _HANDSHAKE_ERROR: Bad handshake error.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/mysqljs/mysql/issues/2287?email_source=notifications&email_token=AFIPZOY6AHGOAID2ZVCOWBLQ55JD7A5CNFSM4KHBHXQ2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEJBKFFI#issuecomment-574792341, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AFIPZO7FN6CJVHCHEI5TJI3Q55JD7ANCNFSM4KHBHXQQ.

The information contained in this e-mail message and any attachments is confidential. If the reader of this message is not the intended recipient or an agent responsible for delivering it to the intended recipient, you are hereby notified that any review, dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify the sender immediately by replying to this e-mail and delete the message and any attachments from your computer.

CovertLeopard commented 4 years ago

We have tested serveral things, without any success. Are any additional information required which are not posted in the node red forum thread?

@digidax What version of nodejs does your program run on?

digidax commented 4 years ago

Sorry @CovertLeopard, this is unfortunately no longer found because in the last weeks are done several npn updates. I can't not name exactly the version number. But I have tested with 5.5.61-MariaDB-38.13 successful and we're planning a very large migration process for the future. Instead of MySQL I'm using now InfluxDB.