MySQL 1251 Error

상황 

MySQL 1251 Error

My Code

app.use(express.json());
const connection = mysql.createConnection({ dbconfig });
connection.query('SELECT * from User', (error, rows, fields) => {
    if (error) throw error;
    console.log('User info is: ', rows);
});
app.listen(PORT, () => {
    console.log(`The Server is Listening at ${PORT}`);
});

Error Code

Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client
    at Handshake.Sequence._packetToError (/Users/pjh/Desktop/chatandsns/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Handshake.ErrorPacket (/Users/pjh/Desktop/chatandsns/node_modules/mysql/lib/protocol/sequences/Handshake.js:123:18)
    at Protocol._parsePacket (/Users/pjh/Desktop/chatandsns/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/Users/pjh/Desktop/chatandsns/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/Users/pjh/Desktop/chatandsns/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/Users/pjh/Desktop/chatandsns/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/Users/pjh/Desktop/chatandsns/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/Users/pjh/Desktop/chatandsns/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (node:events:527:28)
    at addChunk (node:internal/streams/readable:315:12)
    --------------------
    at Protocol._enqueue (/Users/pjh/Desktop/chatandsns/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Protocol.handshake (/Users/pjh/Desktop/chatandsns/node_modules/mysql/lib/protocol/Protocol.js:51:23)
    at Connection.connect (/Users/pjh/Desktop/chatandsns/node_modules/mysql/lib/Connection.js:116:18)
    at Connection._implyConnect (/Users/pjh/Desktop/chatandsns/node_modules/mysql/lib/Connection.js:454:10)
    at Connection.query (/Users/pjh/Desktop/chatandsns/node_modules/mysql/lib/Connection.js:196:8)
    at Object.<anonymous> (/Users/pjh/Desktop/chatandsns/src/server.js:9:12)
    at Module._compile (node:internal/modules/cjs/loader:1105:14)
    at Object.Module._extensions..js (node:internal/modules/cjs/loader:1159:10)
    at Module.load (node:internal/modules/cjs/loader:981:32)
    at Function.Module._load (node:internal/modules/cjs/loader:822:12) {
  code: 'ER_NOT_SUPPORTED_AUTH_MODE',
  errno: 1251,
  sqlMessage: 'Client does not support authentication protocol requested by server; consider upgrading MySQL client',
  sqlState: '08004',
  fatal: true
}

 

 

 

이유

mysql에서 외부 접속을 허용해 주지 않고 연결을 하였기 때문에 오류가 발생하였습니다! 

 

해결 

외부 접속 허용 확인

: mysql> select host, user from mysql.user;

위의 명령어를 통해 외부 접속이 허용이 되어 있는지 확인하기!

만약 host에 localhost 이외의 주소가 없다면 외부 접속 허용이 불가능합니다.

서버와 같은 외부에서 허용하도록 하기 위해서는 host주소를 %로 지정해주면 된다.

mysql> CREATE USER '<userid>'@'%' IDENTIFIED BY 'root';

⇒ MySQL 데이타베이스와 계정 생성하기.

mysql> GRANT ALL PRIVILEGES ON *.* TO '<userid>'@'%' WITH GRANT OPTION;

⇒모든 db 및 테이블에 권한을 주고 로컬 및 리모트에서도 접속가능하도록 설정

mysql> FLUSH PRIVILEGES;

⇒설정한 권한 적용 (반드시 해야 적용이 된다.)

mysql> select host, user from mysql.user;

⇒ 제대로 잘 설정 되었는지 확인.

비밀번호 플러그인 확인

: MySQL 8.0에서는 MySQL 패스워드 플러그인 “caching_sha2_password” 때문에 클라이언트 프로그램에서 에러가 발생한다.

mysql> select host, user, plugin, authentication_string from mysql.user;

⇒ 데이터베이스의 패스워드 플러그인 확인하기.

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';

⇒ 플러그인을 “mysql_native_password” 플러그인으로 바꿔주기

 

참고 블로그 : [MySQL 8.0] nestjs 연동시 에러 해결 ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client