XB2项目部署到服务器会偶尔出现socket链接断开问题

[persilee]

皓哥，最近跟着 XB2 的视频完成 API 的开发，也部署到了从腾讯购买的服务器上，但是，会隔一段时间(隔天)，当天晚上还可以连接，隔一天白天就连接不上，出现了几次，一般我重启服务就好了，查了好久不知道是哪里出了问题。

日志如下：

pm2 logs
0|image-ap | 🤔 This socket has been ended by the other party
0|image-ap | 🤔 Can't add new command when connection is in closed state
0|image-ap | 🤔 Can't add new command when connection is in closed state
0|image-ap | 🤔 Can't add new command when connection is in closed state
0|image-ap | 🤔 Can't add new command when connection is in closed state
0|image-ap | 🤔 Can't add new command when connection is in closed state
0|image-ap | 🤔 Can't add new command when connection is in closed state
0|image-ap | 😜 应用已启动
0|image-ap | 😋 数据库连接成功
0|image-ap | 🤔 NOT_FOUND
0|image-ap | 😜 应用已启动
0|image-ap | 😋 数据库连接成功
0|image-ap | 🤔 This socket has been ended by the other party
0|image-ap | 🤔 Can't add new command when connection is in closed state

api地址：https://api.lishaoy.net/posts
项目地址：persilee/image-api-node

[wanghao]

是的，我注意到这个问题了，应该是 mysql2 那个库的事。我再找找原因。

Issue: Error: Can't add new command when connection is in closed state

/usr/local/etc/my.cnf

mysql> SHOW VARIABLES
    -> ;

| Variable_name                                            | Value                                                                                                                                                                                                                                                                                                                                                                                                                                                                |

| activate_all_roles_on_login                              | OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| admin_address                                            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| admin_port                                               | 33062                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
| auto_generate_certs                                      | ON

/usr/local/mysql-8.0.18-macos10.14-x86_64/

connect_timeout | 10

wait_timeout | 28800

[mysqld]
wait_timeout=xx
interactive_timeout = xx

[persilee]

皓哥厉害啊，我查了好久都没找到决绝方案。

需要修改这2个配置吗，改成多大为好呢？

我看你发的链接 issues 里提到需要修改 max_allowed_packet 配置？

目前我服务里默认配置是，如下

| max_allowed_packet                                       | 67108864 
| connect_timeout                                              | 10
| interactive_timeout                                          | 28800

[persilee]

先修改这样，之后看看运行状态如何

[mysqld]
max_allowed_packet = 37108864
wait_timeout= 6
interactive_timeout = 6600

[wanghao]

测试测试，我昨天部署了一下，也遇到了同样的问题。

0|ravent-n | 🚧 This socket has been ended by the other party
0|ravent-n | 🚧 Can't add new command when connection is in closed state

/root/.pm2/logs/ravent-node-error.log last 15 lines:
0|ravent-n |   errno: 'EADDRINUSE',
0|ravent-n |   syscall: 'listen',
0|ravent-n |   address: '::',
0|ravent-n |   port: 3000
0|ravent-n | }
0|ravent-n | Error: Can't add new command when connection is in closed state
0|ravent-n |     at PromiseConnection.query (/mnt/ravent-node/node_modules/mysql2/promise.js:92:22)
0|ravent-n |     at Object.exports.getPosts (/mnt/ravent-node/src/post/post.service.ts:62:45)
0|ravent-n |     at exports.index (/mnt/ravent-node/src/post/post.controller.ts:36:25)
0|ravent-n |     at processTicksAndRejections (internal/process/task_queues.js:94:5)
0|ravent-n | Error: Can't add new command when connection is in closed state
0|ravent-n |     at PromiseConnection.query (/mnt/ravent-node/node_modules/mysql2/promise.js:92:22)
0|ravent-n |     at Object.exports.getPosts (/mnt/ravent-node/src/post/post.service.ts:62:45)
0|ravent-n |     at exports.index (/mnt/ravent-node/src/post/post.controller.ts:36:25)
0|ravent-n |     at processTicksAndRejections (internal/process/task_queues.js:94:5)

[wanghao]

我们应该可以换一种创建连接的方法试试。我觉得问题应该是：应用创建以后，创建一个数据连接，这个连接一段时间不用就断开了，这样如果再用这个连接执行 sql 就会报错。

Connection {
  _events: [Object: null prototype] {},
  _eventsCount: 0,
  _maxListeners: undefined,
  config:
   ConnectionConfig {
     isServer: undefined,
     stream: undefined,
     host: 'localhost',
     port: 3306,
     localAddress: undefined,
     socketPath: undefined,
     user: 'root',
     password: 'PASSWORD',
     passwordSha1: undefined,
     database: 'nid_node',
     connectTimeout: 10000,
     insecureAuth: false,
     supportBigNumbers: false,
     bigNumberStrings: false,
     decimalNumbers: false,
     dateStrings: false,
     debug: undefined,
     trace: true,
     stringifyObjects: false,
     enableKeepAlive: false,
     keepAliveInitialDelay: 0,
     timezone: 'local',
     queryFormat: undefined,
     pool: undefined,
     ssl: false,
     multipleStatements: false,
     rowsAsArray: false,
     namedPlaceholders: false,
     nestTables: undefined,
     typeCast: true,
     maxPacketSize: 0,
     charsetNumber: 224,
     compress: false,
     authPlugins: undefined,
     authSwitchHandler: undefined,
     clientFlags: 11203535,
     connectAttributes: undefined,
     maxPreparedStatements: 16000 },
  stream:
   Socket {
     connecting: true,
     _hadError: false,
     _parent: null,
     _host: 'localhost',
     _readableState:
      ReadableState {
        objectMode: false,
        highWaterMark: 16384,
        buffer: BufferList { head: null, tail: null, length: 0 },
        length: 0,
        pipes: null,
        pipesCount: 0,
        flowing: true,
        ended: false,
        endEmitted: false,
        reading: false,
        sync: true,
        needReadable: false,
        emittedReadable: false,
        readableListening: false,
        resumeScheduled: true,
        paused: false,
        emitClose: false,
        autoDestroy: false,
        destroyed: false,
        defaultEncoding: 'utf8',
        awaitDrain: 0,
        readingMore: false,
        decoder: null,
        encoding: null },
     readable: false,
     _events:
      [Object: null prototype] {
        end: [Function: onReadableStreamEnd],
        error: [Function: bound _handleNetworkError],
        data: [Function],
        close: [Function] },
     _eventsCount: 4,
     _maxListeners: undefined,
     _writableState:
      WritableState {
        objectMode: false,
        highWaterMark: 16384,
        finalCalled: false,
        needDrain: false,
        ending: false,
        ended: false,
        finished: false,
        destroyed: false,
        decodeStrings: false,
        defaultEncoding: 'utf8',
        length: 0,
        writing: false,
        corked: 0,
        sync: true,
        bufferProcessing: false,
        onwrite: [Function: bound onwrite],
        writecb: null,
        writelen: 0,
        bufferedRequest: null,
        lastBufferedRequest: null,
        pendingcb: 0,
        prefinished: false,
        errorEmitted: false,
        emitClose: false,
        autoDestroy: false,
        bufferedRequestCount: 0,
        corkedRequestsFree: [Object] },
     writable: true,
     allowHalfOpen: false,
     _sockname: null,
     _pendingData: null,
     _pendingEncoding: '',
     server: null,
     _server: null,
     [Symbol(asyncId)]: 4,
     [Symbol(kHandle)]:
      TCP {
        reading: false,
        onconnection: null,
        [Symbol(owner)]: [Circular] },
     [Symbol(lastWriteQueueSize)]: 0,
     [Symbol(timeout)]: null,
     [Symbol(kBytesRead)]: 0,
     [Symbol(kBytesWritten)]: 0 },
  _internalId: 0,
  _commands:
   Denque {
     _head: 0,
     _tail: 0,
     _capacityMask: 3,
     _list: [ <4 empty items> ] },
  _command:
   ClientHandshake {
     _events:
      [Object: null prototype] { end: [Function], error: [Function] },
     _eventsCount: 2,
     _maxListeners: undefined,
     next: [Function: handshakeInit],
     handshake: null,
     clientFlags: 11203535 },
  _paused: false,
  _paused_packets:
   Denque {
     _head: 0,
     _tail: 0,
     _capacityMask: 3,
     _list: [ <4 empty items> ] },
  _statements:
   LRUCache {
     [Symbol(max)]: 16000,
     [Symbol(lengthCalculator)]: [Function: naiveLength],
     [Symbol(allowStale)]: false,
     [Symbol(maxAge)]: 0,
     [Symbol(dispose)]: [Function: dispose],
     [Symbol(noDisposeOnSet)]: false,
     [Symbol(updateAgeOnGet)]: false,
     [Symbol(cache)]: Map {},
     [Symbol(lruList)]: Yallist { tail: null, head: null, length: 0 },
     [Symbol(length)]: 0 },
  serverCapabilityFlags: 0,
  authorized: false,
  sequenceId: 0,
  compressedSequenceId: 0,
  threadId: null,
  _handshakePacket: null,
  _fatalError: null,
  _protocolError: null,
  _outOfOrderPackets: [],
  clientEncoding: 'utf8',
  packetParser:
   PacketParser {
     buffer: [],
     bufferLength: 0,
     packetHeaderLength: 4,
     headerLen: 0,
     length: 0,
     largePacketParts: [],
     firstPacketSequenceId: 0,
     onPacket: [Function],
     execute: [Function: executeStart],
     _flushLargePacket: [Function: _flushLargePacket4] },
  serverEncoding: 'utf8',
  connectTimeout:
   Timeout {
     _idleTimeout: 10000,
     _idlePrev: [TimersList],
     _idleNext: [TimersList],
     _idleStart: 549,
     _onTimeout: [Function: bound _handleTimeoutError],
     _timerArgs: undefined,
     _repeat: null,
     _destroyed: false,
     [Symbol(refed)]: true,
     [Symbol(asyncId)]: 8,
     [Symbol(triggerId)]: 1 } }

[wanghao]

我先切换成使用 pool 试一下。

export const connection = mysql.createPool({
  host: MYSQL_HOST,
  port: parseInt(MYSQL_PORT, 10),
  user: MYSQL_USER,
  password: MYSQL_PASSWORD,
  database: MYSQL_DATABASE,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
});

[persilee]

你发的 issues 有提到过使用连接池 createPool 貌似也不行，这个 issues 是2019年提出的现在 2021 年了还是有人问这个问题，可见 mysql2 也没有一个确切的解决方案，

我经过测试，搞明白了一些， 如果这样设置：

[mysqld]
max_allowed_packet = 37108864
wait_timeout= 6
interactive_timeout = 6600

6 秒后就无法连接，mysql2 使用的是长连接进行连接数据库的，也就是，我们在创建应用、启动服务时，创建了一次连接，之后服务不停止，这个连接就一直在，而我们的请求是在连接创建之后执行的，所以，不能控制它；mysql2 提供了 connection.connect() 、 connection.end() ，我在连接前后分别使用它们，但是，也是不行。

所以，最终，我把 mysql 的配置修改成这样：

max_allowed_packet = 37108864
wait_timeout = 8640000
interactive_timeout = 8640000

也就是运行一个长连接存活 100 天，这样应该没有太大问题，但是也是有个隐患，如果，我们的node服务 100 之内没有重启过，结果就 GAMEOVER 了

[wanghao]

哈哈哈 ~~

[wanghao]

哦，你的应用有前端吗？

[persilee]

前端用 flutter 做，现在在弄，自己做着玩的

[wanghao]

服务端还得做些改进，比如在请求中包含当前用户，这样你可以在内容列表接口中包含当前用户是否点赞过内容项目的字段，或者在内容接口上包含用户是否点赞过内容的字段。

我改了一下 authGuard 中间件，又创建了一个 currentUser 中间件，这个 currentUser 可以放在应用全局使用。

/**
 * 验证用户身份
 */
export const authGuard = (
  request: Request,
  response: Response,
  next: NextFunction,
) => {
  console.log('👮🏼‍♀️ 验证用户身份');

  return request.user.id ? next() : next(new Error('UNAUTHORIZED'));
};

/**
 * 当前用户
 */
export const currentUser = (
  request: Request,
  response: Response,
  next: NextFunction,
) => {
  let user = {
    // 未登录的用户
    id: null,
    name: 'anonymous',
  };

  try {
    // 提取 Authorization
    const authorization = request.header('Authorization');

    // 提取 JWT 令牌
    const token = authorization.replace('Bearer ', '');

    if (token) {
      // 验证令牌
      const decoded = jwt.verify(token, PUBLIC_KEY, {
        algorithms: ['RS256'],
      });

      user = decoded as any;
    }
  } catch (error) {}

  // 在请求里添加当前用户
  request.user = user;

  next();
};

[persilee]

收到，这样在展示时用户就可以清楚自己点过赞的内容了

[wanghao]

对，需要改进一下。

[persilee]

好的，我也加上！

[wanghao]

wait_timeout

Command-Line Format	--wait-timeout=#
System Variable	wait_timeout
Scope	Global, Session
Dynamic	Yes
Type	Integer
Default Value	28800
Minimum Value	1
Maximum Value (Other)	31536000
Maximum Value (Windows)	2147483
The number of seconds the server waits for activity on a noninteractive connection before closing it.

On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout.

[persilee]

Maximum Value (Other) 31536000

了解，没事，最大值 365 天，我设置的 100 天，在范围之内。

[rockts]

这个 mysql 断开的问题，最后怎么解决的？修改了 my.ini 配置文件吗？

[wanghao]

嗯，可以修改配置试试。

[rockts]

/usr/local

我脑浊，找了半天没找到 my.ini , 搜了一下应该修改 /etc/my.cnf 里面。这个配置具体要怎么加呢？看了半天这个帖子没看明白