mysql-copy-tests.yml

name: MySQL Copy Instance Test

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

jobs:
  copy-instance-test:
    runs-on: ubuntu-latest
    services:
      source:
        image: mysql:lts
        env:
          MYSQL_ROOT_PASSWORD: root
        ports:
          - 13306:3306
        options: >-
          --health-cmd="mysqladmin ping"
          --health-interval=10s
          --health-timeout=5s
          --health-retries=3

    steps:
      - uses: actions/checkout@v4

      - name: Set up Go
        uses: actions/setup-go@v5
        with:
          go-version: '1.23'

      - name: Set up Python
        uses: actions/setup-python@v5
        with:
          python-version: '3.13'
      
      - name: Install system packages
        uses: awalsh128/cache-apt-pkgs-action@latest
        with:
          packages: libnsl2 # required by MySQL Shell
          version: 1.1

      - name: Install dependencies
        run: |
          go get .

          pip3 install "sqlglot[rs]"

          curl -LJO https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell_9.1.0-1debian12_amd64.deb
          sudo dpkg -i ./mysql-shell_9.1.0-1debian12_amd64.deb

      - name: Setup test data in source MySQL
        run: |
          mysqlsh -hlocalhost -P13306 -uroot -proot --sql -e "
            CREATE DATABASE testdb;
            USE testdb;
            CREATE TABLE users (
              id INT AUTO_INCREMENT PRIMARY KEY,
              name VARCHAR(100)
            );
            INSERT INTO users (name) VALUES ('test1'), ('test2'), ('test3');
            -- Make a gap in the id sequence
            INSERT INTO users VALUES (100, 'test100');
            INSERT INTO users (name) VALUES ('test101');

            -- A table with non-default starting auto_increment value
            CREATE TABLE items (
              id INT AUTO_INCREMENT PRIMARY KEY,
              name VARCHAR(100)
            ) AUTO_INCREMENT=1000;

            INSERT INTO items (name) VALUES ('item1'), ('item2'), ('item3');
          "

      - name: Build and start MyDuck Server
        run: |
          go build -v
          ./myduckserver &
          sleep 5

      - name: Run copy-instance test
        run: |
          # Set local_infile to true to allow loading data from files
          mysqlsh -uroot --no-password --sql -e "SET GLOBAL local_infile = 1;"

          # Copy the data from source MySQL to MyDuck
          mysqlsh -hlocalhost -P13306 -uroot -proot \
            -- util copy-instance "mysql://root:@127.0.0.1:3306" \
            --users false --ignore-version true

          # Verify the data was copied
          for table in users items; do
            mysqlsh -hlocalhost -P13306 -uroot -proot --sql -e "
              SELECT * FROM testdb.$table ORDER BY id;
            " | tee source_data_$table.tsv
            mysqlsh -uroot --no-password --sql -e "
              SELECT * FROM testdb.$table ORDER BY id;
            " | tee copied_data_$table.tsv

            diff source_data_$table.tsv copied_data_$table.tsv
          done