Open georemo opened 1 year ago
sidorares
commented
1 year ago node.js mysql2 client is just bubbling up the error mysqlrouter gives to it Are you able to connect to mysqlrouter with any other client ( standard command line, GUI tools )?
georemo
commented
1 year ago Thank you for your comment and question. As mentioned in my statement, I am able to connect from the same server to the innodb cluster via the same mysqlrouter using mysql-shell. See below:
devops@cd-api-01 ~/cd-api (main)> mysqlsh --uri mysql://devops@localhost:6446
Please provide the password for 'devops@localhost:6446': ************
Save password for 'devops@localhost:6446'? [Y]es/[N]o/Ne[v]er (default No): y
MySQL Shell 8.0.34
Copyright (c) 2016, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
Creating a Classic session to 'devops@localhost:6446'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 347488
Server version: 8.0.34 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL localhost:6446 ssl JS > \sql
Switching to SQL mode... Commands end with ;
Fetching global names for auto-completion... Press ^C to stop.
MySQL localhost:6446 ssl SQL > use cd1213;select user_name, f_name, l_name from `user` where f_name='karl';
Default schema set to `cd1213`.
Fetching global names, object names from `cd1213` for auto-completion... Press ^C to stop.
+-----------+--------+--------+
| user_name | f_name | l_name |
+-----------+--------+--------+
| karl | Karl | Lulu |
+-----------+--------+--------+
1 row in set (0.0018 sec)I am also able to connect to server using mysql console:
devops@cd-api-01 ~/cd-api (main)> mysql -u devops -h 127.0.0.1 -P 6446 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 349127
Server version: 8.0.34 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
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> use cd1213;select user_name, f_name, l_name from `user` where f_name='karl';
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
+-----------+--------+--------+
| user_name | f_name | l_name |
+-----------+--------+--------+
| karl | Karl | Lulu |
+-----------+--------+--------+
1 row in set (0.00 sec)UPDATE: I have noted that while, mysql console is able to connect with devops@127.0.0.1, it thows the similar error when 'localhost' is used instead of 127.0.0.1. See below:
devops@cd-api-01 ~/cd-api (main) [1]> mysql -u devops -h localhost -P 6446 -p
Enter password:
ERROR 1045 (28000): Access denied for user 'devops'@'localhost' (using password: YES)However mysql-shell can connect with either of the two.
In the node.js project whether I use 127.0.0.1, the error is reported as if I used localhost instead of the loopback ip. So I try the server ip instead. Then now I get ER_HOST_NOT_PRIVILEGED.
Error: Host 'cd-api-01' is not allowed to connect to this MySQL server
at Packet.asError (/home/devops/cd-api/node_modules/mysql2/lib/packets/packet.js:728:17)
at ClientHandshake.execute (/home/devops/cd-api/node_modules/mysql2/lib/commands/command.js:29:26)
at PoolConnection.handlePacket (/home/devops/cd-api/node_modules/mysql2/lib/connection.js:478:34)
at PacketParser.onPacket (/home/devops/cd-api/node_modules/mysql2/lib/connection.js:97:12)
at PacketParser.executeStart (/home/devops/cd-api/node_modules/mysql2/lib/packet_parser.js:75:16)
at Socket.<anonymous> (/home/devops/cd-api/node_modules/mysql2/lib/connection.js:104:25)
at Socket.emit (node:events:513:28)
at Socket.emit (node:domain:489:12)
at addChunk (node:internal/streams/readable:315:12)
at readableAddChunk (node:internal/streams/readable:289:9) {
code: 'ER_HOST_NOT_PRIVILEGED',
errno: 1130,
sqlState: '',
sqlMessage: "Host 'cd-api-01' is not allowed to connect to this MySQL server",
sql: undefined
}NB: devops user is registered in the server as devops@%. Below is a query for database users:
devops@cd-api-01 ~/cd-api (main) [1]> mysql -u devops -h localhost -P 6446 -p
Enter password:
ERROR 1045 (28000): Access denied for user 'devops'@'localhost' (using password: YES)Mysql shell query on users:
MySQL 127.0.0.1:6446 ssl SQL > SELECT Host, User FROM mysql.user;
+-----------+---------------------------------+
| Host | User |
+-----------+---------------------------------+
| % | devops |
| % | goremo |
| % | john |
| % | mysql_innodb_cluster_1031920095 |
| % | mysql_innodb_cluster_1210804892 |
| % | mysql_innodb_cluster_1574385209 |
| localhost | devops |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------------------------+
newghost
commented
7 months ago I run into the same issue and it works after I add rejectUnauthorized: false. Share with you.
const connection = await mysql.createConnection({
host : '10.158.15.69',
port : 3306,
user : 'xxxxxx',
password : 'xxxxx!xxxxxx',
ssl: {
rejectUnauthorized: false
}
// database : 'cidr_db',
})
alexpirine
commented
4 weeks ago @newghost how did you come up with rejectUnauthorized: false and how many hours did you spend figuring this out?
It seems required to connect to Google Cloud SQL without using cloudsqlproxy.
Hi, I have a node.js project using "mysql2", "^3.6.0" and "typeorm", "^0.3.17". Then I have set up mysqlrouter in the same server as recommnded. mysql innodb cluster is set up with 3 nodes. There is no problem connecting to the cluster when connecting directly to any of the cluster nodes from the node.js project. However when I set the connection to go through mysql router, ER_ACCESS_DENIED_ERROR is thrown. Even as it fails, I am able to connect via mysqlrouter via mysql console or mysql-shell. Below is the output of the error: