Database operation failed on mysql 8.0
Closed this issue · 17 comments
import { Client } from 'https://deno.land/x/mysql@1.2.3/mod.ts';
const config = {
timeout: 10000,
pool: 3,
debug: true,
hostname: '127.0.0.1',
username: 'admin001',
password: 'admin001'
};
let client = await new Client().connect(config);
await client.execute(`CREATE DATABASE mydemo;`);
await client.execute(`USE mydemo;`);
await client.execute(`
CREATE TABLE user_info (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(100) NOT NULL,
is_top tinyint(1) default 0,
created_at timestamp not null default current_timestamp,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`);
await client.close();
console.log('success!')
Need to implement caching_sha2_password auth plugin
Need to implement caching_sha2_password auth plugin
https://github.com/manyuanrong/deno_mysql/blob/master/src/auth.ts#L40
It looks like a good working above code. Why do you remain inactive this code?
+------+-----------+-----------------------+
| User | Host | plugin |
+------+-----------+-----------------------+
| root | localhost | mysql_native_password |
+------+-----------+-----------------------+
Just In plugin mysql_native_password case,
In parse function of packet.ts, this.body.buffer[0] has a 0xff(Error) on switch and then get message "error: Uncaught Error: Got packets out of order throw new Error(error.message)".
I tested it in mysql8.0.
To achieve it requires multiple communications and server establishment negotiation, refer to #52
@magichim It's not a working code. I alos try it by uncommenting the code. SQL excution result will return in mess order.
@magichim It's 8.0.20. I'm also want to make it works on mysql 8.0, still in progress of learning on the new authentication of mysql.
This issue has been stale. Post code with version 1.2.3 encountered error:
error: Import 'https://deno.land/std@v0.17.0/strings/mod.ts' failed: 404 Not Found
And I tried to uncomment the cachingSha2Password, and there will be some mismatched issue that can be easily fixed I think. What I dont understand is u guys mentioned back and forth in #52.
I went through the official doc didn't see back and forth process specially for cachingSha2Password plugin. Or I missed something?
I'm note sure I resolve the problem u guys concerned. Let's talk a little more when u get some time.
@wenjoy If we don't use ssl connection, it seems that we need to get the public key from the server first.
I did not explore in depth, but I can refer to https://github.com/sidorares/node-mysql2/blob/master/lib/auth_plugins/caching_sha2_password.js
@manyuanrong Thanks for refs.
You're right. I got the public key in unsecure context.
I digged slightly deeper, but I had to stop when try to encrypt the password with public key for lacks of such module in deno. At least I cant find a appropriate one.
I'm not sure whether going further to figure out it with rust module or waiting deno to implement it.
Any thoughts or suggestions?
ref: denoland/deno#1891
@wenjoy I found the jsencrypt library on JSPM, but because it uses window.navigator, it cannot be used directly, I made some modifications to make it available to Deno. Maybe you can try to use it to temporarily replace crypto
@manyuanrong Thanks. That helps a lot. I will continue with that.
Since we only need to encrypt password, I quickly wrote one here (https://github.com/invisal/god-crypto). You can use as the following
import { RSA } from "https://github.com/invisal/god-crypto/raw/master/mod.ts";
const publicKey = RSA.parseKey("public_key_that_mysql_send_to_you");
const xorPassword = xor(`${password}\0`, handshakePacket.seed);
await new SendPacket(RSA.encrypt(xorPassword, publicKey));I tested it and it works.
Useful Information
@manyuanrong Thanks. I tried with jsencrypt, however it cant support oaep padding. I have to find other ways.
I do get two methods, one is bridge to rust's rsa crate, the other is a bundle of forge.
I choose the later as it has none business of platform difference at all.
And now seems we have extra option thanks @invisal . I would try to integrate that lib when I get time.
@wenjoy Nice job. If you want to integrate, you can simply change a few line of code and it will work.
In your src/auth_plugin/crypt.ts, You just change to the following code.
import { RSA } from "https://deno.land/x/god_crypto@v0.2.0/mod.ts";
function encryptWithPublicKey(key: string, data: Uint8Array): Uint8Array {
const publicKey = RSA.parseKey(key);
return RSA.encrypt(data, publicKey);
}