Misc DB Fixes #1299
Workflow file for this run
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # this runner include the check only, only runs for pull_request | |
| name: vmangos Development DB Check | |
| on: | |
| pull_request: | |
| paths: | |
| - '**.sql' | |
| - '.github/workflows/db_check.yml' | |
| env: | |
| REPO_DIR : ${{github.workspace}} | |
| WORLD_DB : "world_full_14_june_2021" | |
| jobs: | |
| build: | |
| runs-on: ubuntu-latest # 18.04 has mysql5.7 and latest(20.04) has mysql8 | |
| # ... some other config ... | |
| steps: | |
| - name: Shutdown Ubuntu MySQL (SUDO) | |
| run: sudo service mysql stop # Shutdown the Default MySQL, "sudo" is necessary, please not remove it | |
| - name: Start docker mysql | |
| run: | | |
| sudo docker pull mysql | |
| sudo docker run --name mysqldb -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root -d mysql:5.6 --max_allowed_packet=128M | |
| sudo docker start mysqldb | |
| - name: Checkout update sql | |
| uses: actions/checkout@v2 | |
| - name: Checkout base sql | |
| run: | | |
| cd ${{github.workspace}} | |
| git clone https://github.com/brotalnia/database | |
| pwd | |
| 7z e -y ${{github.workspace}}/database/${{env.WORLD_DB}}.7z | |
| ls | |
| - name: Create tables | |
| run: | | |
| docker exec mysqldb sh -c 'exec mysql -u root -proot -e "CREATE DATABASE IF NOT EXISTS realmd DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"' | |
| docker exec mysqldb sh -c 'exec mysql -u root -proot -e "CREATE DATABASE IF NOT EXISTS characters DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"' | |
| docker exec mysqldb sh -c 'exec mysql -u root -proot -e "CREATE DATABASE IF NOT EXISTS mangos DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"' | |
| docker exec mysqldb sh -c 'exec mysql -u root -proot -e "CREATE DATABASE IF NOT EXISTS logs DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"' | |
| - name: Import data | |
| run: | | |
| docker exec -i mysqldb sh -c 'exec mysql -u root -proot realmd' < ${{github.workspace}}/sql/logon.sql | |
| docker exec -i mysqldb sh -c 'exec mysql -u root -proot logs' < ${{github.workspace}}/sql/logs.sql | |
| docker exec -i mysqldb sh -c 'exec mysql -u root -proot mangos' < ${{github.workspace}}/${{env.WORLD_DB}}.sql | |
| docker exec -i mysqldb sh -c 'exec mysql -u root -proot characters' < ${{github.workspace}}/sql/characters.sql | |
| - name: Apply Migration | |
| run: | | |
| cd ${{github.workspace}}/sql/migrations/ && chmod +x merge.sh && ./merge.sh | |
| [ -f "${{github.workspace}}/sql/migrations/world_db_updates.sql" ] && docker exec -i mysqldb sh -c 'exec mysql -u root -proot mangos' < ${{github.workspace}}/sql/migrations/world_db_updates.sql | |
| [ -f "${{github.workspace}}/sql/migrations/characters_db_updates.sql" ] && docker exec -i mysqldb sh -c 'exec mysql -u root -proot characters' < ${{github.workspace}}/sql/migrations/characters_db_updates.sql | |
| [ -f "${{github.workspace}}/sql/migrations/logon_db_updates.sql" ] && docker exec -i mysqldb sh -c 'exec mysql -u root -proot realmd' < ${{github.workspace}}/sql/migrations/logon_db_updates.sql | |
| [ -f "${{github.workspace}}/sql/migrations/logs_db_updates.sql" ] && docker exec -i mysqldb sh -c 'exec mysql -u root -proot logs' < ${{github.workspace}}/sql/migrations/logs_db_updates.sql | |
| - name: Export Dump | |
| run: | | |
| mkdir dbexport | |
| cd ${{github.workspace}}/dbexport | |
| mkdir db_dump | |
| docker exec mysqldb sh -c 'exec mysqldump -u root -proot mangos' > ${{github.workspace}}/dbexport/db_dump/mangos.sql | |
| docker exec mysqldb sh -c 'exec mysqldump -u root -proot realmd' > ${{github.workspace}}/dbexport/db_dump/logon.sql | |
| docker exec mysqldb sh -c 'exec mysqldump -u root -proot logs' > ${{github.workspace}}/dbexport/db_dump/logs.sql | |
| docker exec mysqldb sh -c 'exec mysqldump -u root -proot characters' > ${{github.workspace}}/dbexport/db_dump/characters.sql | |
| # cd db_dump | |
| # mkdir update_check_only_do_not_import | |
| # cp ${{github.workspace}}/sql/migrations/world_db_updates.sql ${{github.workspace}}/dbexport/db_dump/update_check_only_do_not_import/world_db_updates.sql | |
| # cp ${{github.workspace}}/sql/migrations/logon_db_updates.sql ${{github.workspace}}/dbexport/db_dump/update_check_only_do_not_import/logon_db_updates.sql | |
| # cp ${{github.workspace}}/sql/migrations/logs_db_updates.sql ${{github.workspace}}/dbexport/db_dump/update_check_only_do_not_import/logs_db_updates.sql | |
| # cp ${{github.workspace}}/sql/migrations/characters_db_updates.sql ${{github.workspace}}/dbexport/db_dump/update_check_only_do_not_import/characters_db_updates.sql | |
| - name: Create New tables | |
| run: | | |
| docker exec mysqldb sh -c 'exec mysql -u root -proot -e "CREATE DATABASE IF NOT EXISTS realmd2 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"' | |
| docker exec mysqldb sh -c 'exec mysql -u root -proot -e "CREATE DATABASE IF NOT EXISTS characters2 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"' | |
| docker exec mysqldb sh -c 'exec mysql -u root -proot -e "CREATE DATABASE IF NOT EXISTS mangos2 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"' | |
| docker exec mysqldb sh -c 'exec mysql -u root -proot -e "CREATE DATABASE IF NOT EXISTS logs2 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"' | |
| - name: Import data back in to verify the dump data | |
| run: | | |
| docker exec -i mysqldb sh -c 'exec mysql -u root -proot realmd2' < ${{github.workspace}}/dbexport/db_dump/logon.sql | |
| docker exec -i mysqldb sh -c 'exec mysql -u root -proot logs2' < ${{github.workspace}}/dbexport/db_dump/logs.sql | |
| docker exec -i mysqldb sh -c 'exec mysql -u root -proot mangos2' < ${{github.workspace}}/dbexport/db_dump/mangos.sql | |
| docker exec -i mysqldb sh -c 'exec mysql -u root -proot characters2' < ${{github.workspace}}/dbexport/db_dump/characters.sql |