spacestation/api/core/HD_WS_Controller.php

<?php
defined('BASEPATH') or exit('No direct script access allowed');

class HD_WS_Controller extends CI_Controller
{
    protected $db_config;
    protected $last_connect_time = 0;
    protected $reconnect_count = 0;
    protected $last_ping_time = 0;

    public function __construct()
    {
        parent::__construct();
        $this->output->enable_profiler(FALSE);
        $this->loadDatabaseConfig();
        // 加载类
        $this->load->library('myResponse');
        $this->load->library('websocket/wsResponse');
        $this->load->library('websocket/validToken');
        $this->load->library('websocket/message');
        $this->load->model('ws/ws_conn_model');
    }

    /**
     * 手动加载数据库配置
     */
    private function loadDatabaseConfig()
    {
        $config_path = APPPATH . 'config/database.php';

        if (file_exists($config_path)) {
            include $config_path;

            if (isset($db) && is_array($db) && isset($db['default'])) {
                $this->db_config = $db['default'];
                // 确保使用短连接
                $this->db_config['pconnect'] = FALSE;
                $this->db_config['db_debug'] = FALSE; // 关闭调试，避免自动输出错误
                return;
            } else {
                echo "[" . date('Y-m-d H:i:s') . "] 找不到数据库default配置\n";
            }
        } else {
            echo "[" . date('Y-m-d H:i:s') . "] 找不到数据库配置文件\n";
        }
    }

    /**
     * 获取数据库连接（带自动重连和连接检查）
     */
    public function getDbConnection()
    {
        $now = time();

        // 检查现有连接是否有效
        if (isset($this->db) && $this->db->conn_id) {
            try {
                // 定期ping数据库（每30秒一次）
                if ($now - $this->last_ping_time > 30) {
                    $this->db->query('SELECT 1');
                    $this->last_ping_time = $now;
                }
                return $this->db;
            } catch (Exception $e) {
                echo "[" . date('Y-m-d H:i:s') . "] 数据库连接已断开: " . $e->getMessage() . "\n";
                // 关闭无效连接
                if ($this->db->conn_id) {
                    try {
                        $this->db->close();
                    } catch (Exception $e2) {
                        // 忽略关闭时的错误
                    }
                }
                unset($this->db);
            }
        }

        // 限制重连频率
        if ($now - $this->last_connect_time < 2) { // 2秒内只能重连一次
            if ($this->reconnect_count > 5) {
                throw new Exception('数据库重连过于频繁，请检查数据库状态');
            }
        }

        echo "[" . date('Y-m-d H:i:s') . "] 创建新的数据库连接...\n";

        try {
            // 创建新连接
            $this->db = $this->load->database($this->db_config, TRUE);

            if (!$this->db->conn_id) {
                throw new Exception('数据库连接失败');
            }

            // 测试新连接
            $this->db->query('SELECT 1');

            $this->last_connect_time = $now;
            $this->last_ping_time = $now;
            $this->reconnect_count++;

            echo "[" . date('Y-m-d H:i:s') . "] 数据库连接创建成功\n";

            return $this->db;

        } catch (Exception $e) {
            echo "[" . date('Y-m-d H:i:s') . "] 数据库连接失败: " . $e->getMessage() . "\n";
            unset($this->db);
            throw $e;
        }
    }

    /**
     * 安全的数据库查询（自动重试）
     */
    public function safeDbQuery($callback, $max_retry = 3)
    {
        $retry = 0;
        $last_error = '';

        while ($retry <= $max_retry) {
            try {
                $db = $this->getDbConnection();
                $result = call_user_func($callback, $db);
                // 重置重连计数（成功查询后）
                if ($retry == 0) {
                    $this->reconnect_count = 0;
                }
                return $result;

            } catch (Exception $e) {
                $last_error = $e->getMessage();
                $retry++;

                // 检查是否是连接错误
                $is_connection_error = $this->isConnectionError($last_error);

                if ($is_connection_error) {
                    echo "[" . date('Y-m-d H:i:s') . "] 数据库连接错误，重试 {$retry}/{$max_retry}\n";

                    // 如果是连接错误，强制重置连接
                    if (isset($this->db) && $this->db->conn_id) {
                        try {
                            $this->db->close();
                        } catch (Exception $e2) {
                            // 忽略关闭错误
                        }
                        unset($this->db);
                    }
                    if ($retry > $max_retry) {
                        throw new Exception('数据库操作失败，重试次数用尽: ' . $last_error);
                    }
                    // 等待后重试（递增等待时间）
                    usleep(100000 * $retry); // 100ms * 重试次数
                } else {
                    // 非连接错误，直接抛出
                    throw new Exception('数据库查询错误: ' . $last_error);
                }
            }
        }

        throw new Exception('数据库操作失败: ' . $last_error);
    }

    /**
     * 判断错误是否为连接错误
     */
    private function isConnectionError($error_message)
    {
        $connection_errors = [
            'MySQL server has gone away',
            'Lost connection to MySQL server',
            'Connection refused',
            'Too many connections',
            'Query execution was interrupted',
            'SSL connection has been closed',
            'Error reading result set'
        ];

        foreach ($connection_errors as $error) {
            if (stripos($error_message, $error) !== false) {
                return true;
            }
        }
        return false;
    }

    /**
     * 记录连接信息
     */
    public function recordConnection($fd, $user_id, $platform, $token, $ip)
    {
        return $this->safeDbQuery(function ($db) use ($fd, $user_id, $platform, $token, $ip) {
            // 先清理该用户可能存在的旧连接
            $db->where('user_id', $user_id);
            $db->where('platform', $platform);
            $delete_result = $db->delete('lc_ws_conn');
//            if (!$delete_result) {
//                // 删除失败不一定是错误，可能记录不存在
//            }
            // 插入新连接记录
            $connection_data = [
                'fd' => $fd,
                'platform' => $platform,
                'user_id' => $user_id,
                'token' => $token,
                'ip' => $ip,
                'connect_time' => date('Y-m-d H:i:s'),
                'last_activity' => date('Y-m-d H:i:s'),
                'status' => 1
            ];

            $insert_result = $db->replace('lc_ws_conn', $connection_data);
            if (!$insert_result) {
                throw new Exception('插入连接记录失败');
            }

            return $db->insert_id();
        });
    }

    /**
     * 删除连接记录
     */
    public function removeConnection($fd)
    {
        return $this->safeDbQuery(function ($db) use ($fd) {
            $db->where('fd', $fd);
            $result = $db->delete('lc_ws_conn');
            return $result;
        });
    }

    /**
     * 更新最后活动时间
     */
    public function updateActivity($fd)
    {
        return $this->safeDbQuery(function ($db) use ($fd) {
            $db->where('fd', $fd);
            $result = $db->update('lc_ws_conn', [
                'last_activity' => date('Y-m-d H:i:s')
            ]);
            return true;
        });
    }

    /**
     * 根据用户ID获取连接FD
     */
    public function getConnectionByUserId($platform, $user_id)
    {
        return $this->safeDbQuery(function ($db) use ($platform, $user_id) {
            $db->select('*');
            $db->from('lc_ws_conn');
            $db->where('platform', $platform);
            $db->where('user_id', $user_id);
            $db->where('status', 1);
            $query = $db->get();

            if (!$query) {
                throw new Exception('查询失败');
            }

            if ($query->num_rows() > 0) {
                return $query->row();
            }
            return null;
        });
    }

    /**
     * 手动关闭数据库连接
     */
    public function closeDbConnection()
    {
        if (isset($this->db) && $this->db->conn_id) {
            try {
                $this->db->close();
                echo "[" . date('Y-m-d H:i:s') . "] 数据库连接已关闭\n";
            } catch (Exception $e) {
                echo "[" . date('Y-m-d H:i:s') . "] 关闭数据库连接时出错: " . $e->getMessage() . "\n";
            }
            unset($this->db);
        }
    }
}