mybatis端口配置错误

MyBatis 端口号配置错误分析
一、典型错误堆栈
1. 连接超时错误
java
// 连接超时（端口错误导致无法建立连接）
org.springframework.jdbc.CannotGetJdbcConnectionException: 
Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: 
Connection timed out (Connection timed out)

// 完整错误堆栈
Caused by: java.sql.SQLNonTransientConnectionException: 
Connection timed out (Connection timed out)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:828)
    at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:448)
    at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:241)
    at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198)
    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138)
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:364)
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206)
2. 连接拒绝错误
java
// 连接被拒绝（端口无服务监听）
com.mysql.cj.jdbc.exceptions.CommunicationsException: 
Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. 
The driver has not received any packets from the server.

Caused by: java.net.ConnectException: Connection refused (Connection refused)
    at java.net.PlainSocketImpl.socketConnect(Native Method)
    at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
    at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
    at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
    at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
    at java.net.Socket.connect(Socket.java:607)
    at com.mysql.cj.protocol.StandardSocketFactory.connect(StandardSocketFactory.java:155)
二、错误配置示例
1. application.yml 端口错误配置
yaml
# 错误的端口号配置
spring:
  datasource:
    url: jdbc:mysql://localhost:3307/my_database  # 错误端口：3307，正确应该是3306
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver
    hikari:
      connection-timeout: 30000  # 30秒超时
      maximum-pool-size: 10

# 或者使用属性文件
# application.properties
spring.datasource.url=jdbc:mysql://localhost:3307/my_database  # 错误端口
2. mybatis-config.xml 端口错误
xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" 
                          value="jdbc:mysql://localhost:3307/my_database"/>  <!-- 错误端口 -->
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
</configuration>
三、不同数据库的默认端口
1. 常见数据库默认端口
数据库	默认端口	错误配置示例
MySQL	3306	jdbc:mysql://host:3307/db
PostgreSQL	5432	jdbc:postgresql://host:5433/db
Oracle	1521	jdbc:oracle:thin:@host:1522:sid
SQL Server	1433	jdbc:sqlserver://host:1434;databaseName=db
MariaDB	3306	jdbc:mariadb://host:3307/db
2. 不同环境的端口配置
yaml
# 多环境端口配置示例
---
spring:
  profiles: dev
  datasource:
    url: jdbc:mysql://localhost:3306/dev_db  # 开发环境

---
spring:
  profiles: test
  datasource:
    url: jdbc:mysql://test-db:3306/test_db  # 测试环境

---
spring:
  profiles: prod
  datasource:
    url: jdbc:mysql://prod-db:3307/prod_db  # 生产环境使用自定义端口
四、排查和诊断步骤
1. 网络连通性检查
bash
# 1. 检查端口连通性
telnet localhost 3306  # 测试默认端口
telnet localhost 3307  # 测试配置的端口

# 2. 查看MySQL服务状态和端口
sudo netstat -tlnp | grep mysql
# 或者使用 ss 命令
sudo ss -tlnp | grep mysql

# 3. 检查MySQL配置文件中的端口设置
sudo cat /etc/mysql/my.cnf | grep port
sudo cat /etc/mysql/mysql.conf.d/mysqld.cnf | grep port

# 4. 直接连接测试
mysql -u root -p -h localhost -P 3306  # 使用正确端口
mysql -u root -p -h localhost -P 3307  # 使用错误端口
2. Java 诊断代码
java
@Component
public class PortConnectionValidator {
    
    private static final Logger logger = LoggerFactory.getLogger(PortConnectionValidator.class);
    
    @Value("${spring.datasource.url}")
    private String dbUrl;
    
    @PostConstruct
    public void validateDatabasePort() {
        try {
            // 从URL中提取主机和端口
            String host = extractHost(dbUrl);
            int port = extractPort(dbUrl);
            
            logger.info("检查数据库连接: {}:{}", host, port);
            
            // 测试端口连通性
            testPortConnectivity(host, port);
            
            // 测试数据库连接
            testDatabaseConnection();
            
        } catch (Exception e) {
            logger.error("数据库端口检查失败: {}", e.getMessage());
            suggestSolutions();
        }
    }
    
    private String extractHost(String url) {
        Pattern pattern = Pattern.compile("jdbc:mysql://([^:/]+)");
        Matcher matcher = pattern.matcher(url);
        return matcher.find() ? matcher.group(1) : "localhost";
    }
    
    private int extractPort(String url) {
        Pattern pattern = Pattern.compile("jdbc:mysql://[^:]+:(\\d+)");
        Matcher matcher = pattern.matcher(url);
        if (matcher.find()) {
            return Integer.parseInt(matcher.group(1));
        }
        return 3306; // 默认端口
    }
    
    private void testPortConnectivity(String host, int port) throws IOException {
        try (Socket socket = new Socket()) {
            socket.connect(new InetSocketAddress(host, port), 5000);
            logger.info("✅ 端口 {}:{} 连接成功", host, port);
        } catch (IOException e) {
            logger.error("❌ 端口 {}:{} 连接失败: {}", host, port, e.getMessage());
            throw e;
        }
    }
    
    private void testDatabaseConnection() throws SQLException {
        // 实际的数据库连接测试
        // 这里可以使用DataSource进行连接测试
    }
    
    private void suggestSolutions() {
        logger.info("可能的解决方案:");
        logger.info("1. 检查MySQL服务是否启动: sudo systemctl status mysql");
        logger.info("2. 检查防火墙设置: sudo ufw status");
        logger.info("3. 确认MySQL监听端口: sudo netstat -tlnp | grep mysql");
        logger.info("4. 修改application.yml中的端口配置");
    }
}
五、解决方案
1. 修正配置
yaml
# 修正端口配置
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/my_database  # 使用正确的3306端口
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver
    hikari:
      connection-timeout: 10000  # 适当减少超时时间
      maximum-pool-size: 5

# 或者使用环境变量
spring:
  datasource:
    url: jdbc:mysql://${DB_HOST:localhost}:${DB_PORT:3306}/${DB_NAME:my_database}
2. 动态端口检测
java
@Configuration
public class DynamicDataSourceConfig {
    
    @Bean
    public DataSource dataSource(Environment env) {
        HikariDataSource dataSource = new HikariDataSource();
        
        // 尝试多个可能的端口
        String url = findWorkingDatabaseUrl(env);
        dataSource.setJdbcUrl(url);
        dataSource.setUsername(env.getProperty("spring.datasource.username"));
        dataSource.setPassword(env.getProperty("spring.datasource.password"));
        
        return dataSource;
    }
    
    private String findWorkingDatabaseUrl(Environment env) {
        String baseUrl = env.getProperty("spring.datasource.url");
        String[] possiblePorts = {"3306", "3307", "3308", "3309"};
        
        for (String port : possiblePorts) {
            String testUrl = baseUrl.replaceFirst(":\\d+/", ":" + port + "/");
            if (testConnection(testUrl, env)) {
                System.out.println("找到可用的数据库端口: " + port);
                return testUrl;
            }
        }
        
        throw new RuntimeException("无法找到可用的数据库端口");
    }
    
    private boolean testConnection(String url, Environment env) {
        try (Connection conn = DriverManager.getConnection(
                url, 
                env.getProperty("spring.datasource.username"),
                env.getProperty("spring.datasource.password"))) {
            return true;
        } catch (SQLException e) {
            return false;
        }
    }
}
六、预防措施
1. 配置验证脚本
bash
#!/bin/bash
# validate-db-config.sh

# 提取配置中的端口
CONFIG_FILE="src/main/resources/application.yml"
DB_URL=$(grep "url:" $CONFIG_FILE | awk '{print $2}')
PORT=$(echo $DB_URL | grep -oP 'jdbc:mysql://[^:]+:\K\d+')
HOST=$(echo $DB_URL | grep -oP 'jdbc:mysql://\K[^:]+')

echo "配置的数据库连接: $HOST:$PORT"

# 检查端口连通性
if timeout 5 telnet $HOST $PORT 2>&1 | grep -q "Connected"; then
    echo "✅ 端口 $PORT 连接成功"
else
    echo "❌ 端口 $PORT 连接失败"
    echo "建议检查:"
    echo "1. MySQL服务是否运行在端口 $PORT"
    echo "2. 防火墙设置"
    echo "3. 网络连通性"
fi
2. 启动时自动检测
java
@Component
public class DatabasePortChecker {
    
    @EventListener(ApplicationReadyEvent.class)
    public void checkDatabasePort() {
        try {
            // 获取当前配置
            String url = environment.getProperty("spring.datasource.url");
            int port = extractPort(url);
            
            // 记录使用的端口
            logger.info("当前使用的数据库端口: {}", port);
            
            // 验证端口范围
            if (port < 1024 || port > 65535) {
                logger.warn("数据库端口 {} 不在常规范围内 (1024-65535)", port);
            }
            
        } catch (Exception e) {
            logger.warn("数据库端口检查异常: {}", e.getMessage());
        }
    }
}
七、不同场景的错误特征
1. 开发环境
症状：连接超时或连接被拒绝

常见原因：MySQL服务未启动或使用错误端口

解决：启动MySQL服务或修正端口为3306

2. Docker环境
症状：连接被拒绝

常见原因：容器端口映射错误

解决：检查docker-compose.yml中的端口映射

3. 生产环境
症状：连接超时

常见原因：安全组/防火墙阻止访问

解决：检查网络安全组规则

关键识别特征：

Connection timed out - 端口无响应

Connection refused - 端口无服务监听

Communications link failure - 网络连接问题

应用启动失败，无法创建数据库连接