mysql.yml

name: MySQL Integration Tests
on:
  pull_request:
    paths:
      - 'sql/tests/**'
      - 'sql/snsql/**'
      - 'sql/pyproject.toml'
      - '!sql/tests/setup/postgres/**'
      - '!sql/tests/setup/sqlserver/**'
      - '!sql/tests/setup/biqquery/**'
  workflow_dispatch:
jobs:
  container-job:
    runs-on: ubuntu-latest
    environment: CI
    strategy:
      fail-fast: false
      matrix:
        python-version: [3.9]
      max-parallel: 5
    steps:
      - uses: actions/checkout@v2
      - name: start mysql
        shell: bash -l {0}
        env:
          MYSQL_PASSWORD: ${{ secrets.MYSQL_PASSWORD }}
        run: |
          sudo systemctl start mysql.service
      - name: Set up miniconda
        uses: conda-incubator/setup-miniconda@v2
        with:
          miniconda-version: "latest"
          auto-update-conda: true
          auto-activate-base: true
          python-version: ${{ matrix.python-version }}
      - name: Upgrade pip
        shell: bash -l {0}
        run: |
          conda install pip
          conda update pip
      - name: Set MySQL Root password
        shell: bash -l {0}
        env:
          MYSQL_PASSWORD: ${{ secrets.MYSQL_PASSWORD }}
        run: |
          sudo mysql --user=root --password=root -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '${{env.MYSQL_PASSWORD}}';"
          sudo mysql --user=root --password=${{env.MYSQL_PASSWORD}} -e "CREATE USER 'runner'@'localhost' IDENTIFIED WITH mysql_native_password BY '${{env.MYSQL_PASSWORD}}';"
          sudo mysql --user=root --password=${{env.MYSQL_PASSWORD}} -e "GRANT ALL ON *.* TO 'runner'@'localhost'; FLUSH PRIVILEGES;"
          sudo mysql --user=root --password=${{env.MYSQL_PASSWORD}} -e "SHOW VARIABLES LIKE 'secure_file_priv';"
      - name: Setup SDK
        shell: bash -l {0}
        run: |
          cd sql
          pip install --no-cache-dir -r tests/requirements.txt
          pip install --no-cache-dir -r tests/setup/mysql/requirements.txt
          pip install --no-cache-dir  .
      - name: Check database connections test fixture
        shell: bash -l {0}
        env:
          MYSQL_PASSWORD: ${{ secrets.MYSQL_PASSWORD }}
          MYSQL_USER: runner
        run: |
          cd sql
          mkdir ~/.smartnoise
          cp tests/setup/mysql/connections-unit.yaml ~/.smartnoise
          python tests/check_databases.py
      - name: Copy CSV files
        shell: bash -l {0}
        env:
          MYSQL_PASSWORD: ${{ secrets.MYSQL_PASSWORD }}
        run: |
          cd sql/tests/setup/mysql
          sudo python copy.py
      - name: Install Tables to MySQL
        shell: bash -l {0}
        env:
          MYSQL_PASSWORD: ${{ secrets.MYSQL_PASSWORD }}
        run: |
          cd sql/tests/setup/mysql
          mysql --user=runner --password=${{ secrets.MYSQL_PASSWORD }} < create_tables.sql
      - name: Check DB install
        shell: bash -l {0}
        env:
          MYSQL_PASSWORD: ${{ secrets.MYSQL_PASSWORD }}
          MYSQL_USER: runner
        run: |
          cd sql
          mysql --user=${{env.MYSQL_USER}} --password=${{ env.MYSQL_PASSWORD }} -e 'USE PUMS_large; SELECT COUNT(*) FROM pums_large;'
          mysql --user=${{env.MYSQL_USER}} --password=${{ env.MYSQL_PASSWORD }} -e 'USE PUMS; SELECT COUNT(*) FROM pums;'
          mysql --user=${{env.MYSQL_USER}} --password=${{ env.MYSQL_PASSWORD }} -e 'USE PUMS_dup; SELECT COUNT(*) FROM pums;'
          mysql --user=${{env.MYSQL_USER}} --password=${{ env.MYSQL_PASSWORD }} -e 'USE PUMS_null; SELECT COUNT(*) FROM pums;'
          mysql --user=${{env.MYSQL_USER}} --password=${{ env.MYSQL_PASSWORD }} -e 'USE PUMS_pid; SELECT COUNT(*) FROM pums;'
          python tests/check_databases.py
      - name: Test SDK
        shell: bash -l {0}
        env:
          MYSQL_PASSWORD: ${{ secrets.MYSQL_PASSWORD }}
        run: |
          cd sql
          export SKIP_PANDAS=1
          pytest tests