How to grant permissions to mysql in github actions

Question

I'm trying to do some testing with github actions.
previous post: test-not-found-in-github-actions

Now that the test is recognized, all that's left is to configure mysql.
I'm getting the error "User access is denied" https://github.com/duri0214/portfolio/actions/runs/4250191595/jobs/7391059026#step:4:569

Usually this error goes away by granting privileges to mysql.
It has been confirmed on the production server that django works by giving the following nine permissions.

-- in VirtualPrivateServer
mysql> CREATE DATABASE portfolio_db DEFAULT CHARACTER SET utf8mb4;
       Query OK, 1 row affected (0.01 sec)
mysql> CREATE USER 'python'@'%' IDENTIFIED BY 'XXXXXXXX';
mysql> grant CREATE, DROP, SELECT, UPDATE, INSERT, DELETE, ALTER, REFERENCES, INDEX on portfolio_db.* to 'python'@'localhost';

However, I don't really understand how to type this mysql command.
I get an error even if I issue a command to github actions as follows.
I was able to confirm that the secret was output
https://github.com/duri0214/portfolio/actions/runs/4249942981/jobs/7390568462#step:4:424

mysql -u${{ secrets.DB_USER }} -p${{ secrets.DB_PASSWORD }} -e 'CREATE DATABASE portfolio_db DEFAULT CHARACTER SET utf8mb4;'
mysql -u${{ secrets.DB_USER }} -p${{ secrets.DB_PASSWORD }} -e 'CREATE USER '${{ secrets.DB_USER }}'@'localhost' IDENTIFIED BY '${{ secrets.DB_PASSWORD }}';'
mysql -u${{ secrets.DB_USER }} -p${{ secrets.DB_PASSWORD }} -e 'grant CREATE, DROP, SELECT, UPDATE, INSERT, DELETE, ALTER, REFERENCES, INDEX on portfolio_db.* to '${{ secrets.DB_USER }}'@'localhost';'

Warning: arning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user '***'@'localhost' (using password: YES)
Error: Process completed with exit code 1.

I've tried many things, but I'm getting tired...
Anyone know how to solve this?

additional

for Azeem san :)

Django will use the information found here to create a throwaway database. this

At this time, I don't know if the credentials are from '.env' or disposable credentials.

As far as I can see the log, I was able to create the migration file, but it seems that I cannot log in to the database. this

If disposable authentication information is fine, 'echo' around here may not be necessarythis

Answer 1

Your settings.py doesn't take HOST and PORT from the env vars so using MySQL won't work with services because Django's default for HOST is localhost and that's why it looks for the Unix domain socket. With an env var HOST set to 127.0.0.1, it'll connect on the TCP PORT.

So, I resorted to using the default preinstalled MySQL instance:

sudo systemctl start mysql

You were creating the test_portfolio DB but the tests create and destroy it themselves so its creation was redundant and created conflicts when the tests are run. You should create your actual DB e.g. portfolio, not test_portfolio.

mysql -uroot -proot -e "CREATE DATABASE 'portfolio';"
mysql -uroot -proot -e "SHOW DATABASES;"

Here is the workflow that I tested on my side:

name: python_django_test

on: workflow_dispatch

jobs:
  ci:
    runs-on: ubuntu-latest

    steps:
    - name: Checkout
      uses: actions/checkout@v3
      with:
        repository: duri0214/portfolio

    - name: Set up Python
      uses: actions/setup-python@v4
      with:
        python-version: 3.8
        cache: pip

    - name: Set up and run tests
      env:
        DB_ENGINE: django.db.backends.mysql
        DB_NAME: portfolio
        DB_USER: root
        DB_PASSWORD: root
      run: |
        sudo systemctl start mysql
        mysql -u$DB_USER -p$DB_PASSWORD -e "CREATE DATABASE $DB_NAME;"
        mysql -u$DB_USER -p$DB_PASSWORD -e "SHOW DATABASES;"
        python3 --version
        python3 -m venv venv
        . venv/bin/activate
        python -m pip install --upgrade pip
        python -m pip install -r requirements.txt
        cd mysite
        python manage.py makemigrations register
        python manage.py makemigrations vietnam_research gmarker shopping linebot warehouse
        python manage.py migrate
        export DJANGO_SECRET_KEY="$(base64 <<< "$RANDOM|TeStiNg|$RANDOM" | tr -d '\n')"
        python manage.py test

Please observe DJANGO_SECRET_KEY in there. Your app requires this so I just generated a random value for this. You might want to use your own.

Also, now the tests run but some of them fail so you need to adjust those accordingly. It's now on your app side. GHA workflow is working fine.