GitHub Actions中MySQL建表与CSV数据插入失败排查求助

问题描述 #

我正在开发应用，计划在GitHub Actions中开展测试，使用MySQL数据库，需要将CSV格式的测试数据插入到GitHub Actions的容器中，但执行效果不符合预期：

1. 创建MySQL容器→执行成功；
2. 运行建表脚本→执行失败；
3. 向步骤2创建的表插入测试数据→执行失败。

所有步骤均未报错，但步骤2和3的脚本未实际生效，请求排查代码问题。

原代码 #

ci.yml

name: sample-ci

on:
  push:
    branches: [ "main" ]
  pull_request:
    branches: [ "main" ]

jobs:
  job-with-mysql-8_0:
    runs-on: ubuntu-latest
    services:
      db:
        image: mysql:8
        ports:
          - 3306:3306
        env:
          MYSQL_ROOT_PASSWORD: password
          MYSQL_DATABASE: sampleDB
        options: >-
          --health-cmd "mysqladmin ping"
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
    steps:
      - name: setup-go
        uses: actions/setup-go@v3
        with:
          go-version: 1.16
      - name: checkout
        uses: actions/checkout@v3

      - name: Show Docker containers
        run: docker ps -a
      - name: Show databases for root user
        run: mysql --protocol=tcp -h localhost -P 3306 -u root -ppassword -e "SHOW DATABASES"
      - name: Set up MySQL
        run: sudo /lib/systemd/systemd-sysv-install enable mysql
             sudo systemctl enable mysql.service
             sudo systemctl start mysql.service
      - name: Run sample.sql
        run: |
          mysql --protocol=tcp -h localhost -P 3306 -u root -ppassword -e "$(cat $(find ./ -name sample.sql))"
      - name: Show created tables
        run: mysql --protocol=tcp -h localhost -P 3306 -u root -ppassword -e "USE sampleDB"
             mysql --protocol=tcp -h localhost -P 3306 -u root -ppassword -e "SHOW TABLES"
      - name: Insert data into user table
        run: sudo /lib/systemd/systemd-sysv-install enable mysql
             sudo systemctl enable mysql.service
             sudo systemctl start mysql.service
             sh ./.github/scripts/sample.sh
      - name: Check inserted data
        run: mysql --protocol=tcp -h localhost -P 3306 -u root -ppassword -e "USE sampleDB"
             mysql --protocol=tcp -h localhost -P 3306 -u root -ppassword -e "SELECT * FROM user"

sample.sql

USE sampleDB;

  CREATE TABLE user(
  id INT,
  name VARCHAR(100) NOT NULL,
  PRIMARY KEY(id),
  UNIQUE(name)
);

sample.sh

mysql -uroot -ppassword --local-infile=1 sampleDB -e "LOAD DATA LOCAL INFILE '/docker-entrypoint-initdb.d/user.csv' INTO TABLE user FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES"

user.csv

id,name
1,aaa
2,bbb
3,ccc

问题排查与修复方案 #

核心问题分析 #

1. 主机MySQL与容器MySQL端口冲突
   配置里手动启动了主机本地的MySQL服务，直接占用了3306端口。后续所有mysql命令都连到了主机的MySQL，而不是services里的容器MySQL——主机MySQL里根本没有预先创建的sampleDB，建表和插入操作等于在空环境里执行，自然没效果。

2. SQL脚本执行方式有问题
   用mysql -e "$(cat sample.sql)"传递SQL内容，容易因为换行、字符解析问题导致脚本没正确执行，相当于白跑一趟。

3. LOAD DATA的文件路径错了
   sample.sh里写的/docker-entrypoint-initdb.d/user.csv是容器内部的路径，但user.csv明明在GitHub仓库里（也就是主机文件系统），而且LOAD DATA LOCAL INFILE读的是客户端（主机）的文件，不是服务器（容器）的，路径完全对不上。

4. 多命令分开执行掩盖了错误
   比如Show created tables步骤里，两个mysql命令分开跑，就算USE sampleDB失败，SHOW TABLES还是会在默认数据库里执行，返回空结果但不报错，你根本不知道前面的命令出问题了。

修复后的代码 #

ci.yml

name: sample-ci

on:
  push:
    branches: [ "main" ]
  pull_request:
    branches: [ "main" ]

jobs:
  job-with-mysql-8_0:
    runs-on: ubuntu-latest
    services:
      db:
        image: mysql:8
        ports:
          - 3306:3306
        env:
          MYSQL_ROOT_PASSWORD: password
          MYSQL_DATABASE: sampleDB
        options: >-
          --health-cmd "mysqladmin ping"
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
    steps:
      - name: setup-go
        uses: actions/setup-go@v3
        with:
          go-version: 1.16
      - name: checkout
        uses: actions/checkout@v3

      - name: Show Docker containers
        run: docker ps -a
      - name: Show databases for root user
        run: mysql --protocol=tcp -h localhost -P 3306 -u root -ppassword -e "SHOW DATABASES"
      # 删掉启动主机MySQL的步骤，完全用容器里的数据库
      - name: Run sample.sql
        run: |
          # 直接重定向执行SQL脚本，指定数据库避免手动USE
          mysql --protocol=tcp -h localhost -P 3306 -u root -ppassword sampleDB < $(find ./ -name sample.sql)
      - name: Show created tables
        run: |
          # 合并命令，确保在sampleDB里查 tables
          mysql --protocol=tcp -h localhost -P 3306 -u root -ppassword -e "USE sampleDB; SHOW TABLES"
      - name: Insert data into user table
        run: |
          # 删掉启动主机MySQL的步骤，直接执行插入脚本
          sh ./.github/scripts/sample.sh
      - name: Check inserted data
        run: |
          # 合并命令，确保在sampleDB里查数据
          mysql --protocol=tcp -h localhost -P 3306 -u root -ppassword -e "USE sampleDB; SELECT * FROM user"

sample.sh

# 明确连接到容器的MySQL，用主机上的user.csv路径
mysql --protocol=tcp -h localhost -P 3306 -uroot -ppassword --local-infile=1 sampleDB -e "LOAD DATA LOCAL INFILE './user.csv' INTO TABLE user FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES"

sample.sql、user.csv 无需修改

额外注意点 #

• 确保GitHub Actions runner能正常读取user.csv，checkout步骤默认会把文件权限设置好；
• MySQL 8.0默认禁用本地文件加载，必须加--local-infile=1参数开启；
• 彻底放弃主机上的MySQL操作，全程用services定义的容器数据库，避免环境冲突。

内容的提问来源于stack exchange，提问作者Jassy