Problems with mysql - initial db migration

[RummsMurmel]

Hello,

I installed eramba-community as described in
https://www.eramba.org/learning/courses/12/episodes/274#install_community

Here is my .env

DB_HOST=mysql
DB_DATABASE=docker
DB_USERNAME=docker
DB_PASSWORD=<my_alphanumeric_pw>
CACHE_URL=Redis://?server=redis&port=6379&password=&timeout=3
MYSQL_ROOT_PASSWORD=<my_alphanumeric_root_pw>
USE_PROXY=1
PROXY_HOST=<our_http_proxy>
PROXY_PORT=8080
USE_PROXY_AUTH=0
PROXY_AUTH_USER=
PROXY_AUTH_PASS=
PUBLIC_ADDRESS=https://<our_dns_name>
DOCKER_DEPLOYMENT=1
LDAPTLS_REQCERT=never

No change on the mysql configuration:

[mysqld]
disable-log-bin
max_allowed_packet=128M
sql_mode=NO_ENGINE_SUBSTITUTION
innodb_strict_mode=0
innodb_lock_wait_timeout=200
innodb_stats_on_metadata=OFF
binlog_row_image=MINIMAL
sync_binlog=1
local_infile=OFF
master_verify_checksum=ON
relay_log_purge=1
binlog_expire_logs_seconds=604800
slave_sql_verify_checksum=1
skip-host-cache
skip-name-resolve
slow_query_log=1
slow_query_log_file=/tmp/mysql-slow.log
log_bin_trust_function_creators=OFF
log_bin=OFF
bind-address=0.0.0.0

# performance config - uncomment if you need "performance_schema" monitoring
#performance_schema=ON
#performance-schema-instrument='statement/%=ON'
#performance-schema-consumer-statements-digest=ON
#innodb_monitor_enable=all

[mysql]

[mysqldump]
quick
quote-names
max_allowed_packet=128M

[client]
bind-address=0.0.0.0

But the following error is thrown in the initial db-migration step.
This error happens with eramba:latest (3.20.0) and also with a clean installation of eramba:3.19.3

eramba  | Running post process...
eramba  | Post process failed with error: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'docker.DashboardCalendarEvents.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by in /var/www/eramba/app/upgrade/vendor/cakephp/cakephp/src/Database/Statement/MysqlStatement.php:39
eramba  | Stack trace:
eramba  | #0 /var/www/eramba/app/upgrade/vendor/cakephp/cakephp/src/Database/Statement/MysqlStatement.php(39): PDOStatement->execute(NULL)
eramba  | #1 /var/www/eramba/app/upgrade/vendor/cakephp/cakephp/src/Database/Statement/StatementDecorator.php(180): Cake\Database\Statement\MysqlStatement->execute(NULL)
eramba  | #2 /var/www/eramba/app/upgrade/vendor/cakephp/cakephp/src/Database/Log/LoggingStatement.php(75): Cake\Database\Statement\StatementDecorator->execute(NULL)
eramba  | #3 /var/www/eramba/app/upgrade/vendor/cakephp/cakephp/src/Database/Connection.php(337): Cake\Database\Log\LoggingStatement->execute()
eramba  | #4 /var/www/eramba/app/upgrade/vendor/cakephp/cakephp/src/Core/Retry/CommandRetry.php(70): Cake\Database\Connection->Cake\Database\{closure}()
eramba  | #5 /var/www/eramba/app/upgrade/vendor/cakephp/cakephp/src/Database/Connection.php(340): Cake\Core\Retry\CommandRetry->run(Object(Closure))
eramba  | #6 /var/www/eramba/app/upgrade/vendor/cakephp/cakephp/src/Database/Query.php(250): Cake\Database\Connection->run(Object(Cake\ORM\Query))
eramba  | #7 /var/www/eramba/app/upgrade/vendor/cakephp/cakephp/src/ORM/Query.php(1148): Cake\Database\Query->execute()
eramba  | #8 /var/www/eramba/app/upgrade/vendor/cakephp/cakephp/src/Datasource/QueryTrait.php(288): Cake\ORM\Query->_execute()
eramba  | #9 /var/www/eramba/app/upgrade/vendor/cakephp/cakephp/src/ORM/Query.php(1096): Cake\ORM\Query->_all()
eramba  | #10 /var/www/eramba/app/upgrade/vendor/cakephp/cakephp/src/Datasource/QueryTrait.php(305): Cake\ORM\Query->all()
eramba  | #11 /var/www/eramba/app/upgrade/plugins/Dashboard/src/Lib/DashboardCalendarManager.php(179): Cake\ORM\Query->toArray()
eramba  | #12 /var/www/eramba/app/upgrade/plugins/Dashboard/src/Lib/DashboardCalendarManager.php(198): Dashboard\Lib\DashboardCalendarManager->cleanUpDuplicates()
eramba  | #13 /var/www/eramba/app/upgrade/plugins/Dashboard/src/Command/EventsSyncCommand.php(56): Dashboard\Lib\DashboardCalendarManager->sync()
eramba  | #14 /var/www/eramba/app/upgrade/src/Command/PostProcessRunCommand.php(113): Dashboard\Command\EventsSyncCommand->execute(Object(Cake\Console\Arguments), Object(Cake\Console\ConsoleIo))
eramba  | #15 /var/www/eramba/app/upgrade/src/Lib/Updates/Migrator.php(395): App\Command\PostProcessRunCommand->execute(Object(Cake\Console\Arguments), Object(Cake\Console\ConsoleIo))
eramba  | #16 /var/www/eramba/app/upgrade/src/Lib/Updates/Migrator.php(339): App\Lib\Updates\Migrator->genericPostProcessSync()
eramba  | #17 /var/www/eramba/app/upgrade/src/Lib/Updates/Migrator.php(102): App\Lib\Updates\Migrator->runPostProcess()
eramba  | #18 /var/www/eramba/app/upgrade/src/Command/DatabaseInitializeCommand.php(96): App\Lib\Updates\Migrator->migrate('default')
eramba  | #19 /var/www/eramba/app/upgrade/src/Command/DatabaseInitializeCommand.php(90): App\Command\DatabaseInitializeCommand->execute(Object(Cake\Console\Arguments), Object(Cake\Console\ConsoleIo))
eramba  | #20 /var/www/eramba/app/upgrade/src/Command/DatabaseInitializeCommand.php(90): App\Command\DatabaseInitializeCommand->execute(Object(Cake\Console\Arguments), Object(Cake\Console\ConsoleIo))
eramba  | #21 /var/www/eramba/app/upgrade/src/Command/DatabaseInitializeCommand.php(90): App\Command\DatabaseInitializeCommand->execute(Object(Cake\Console\Arguments), Object(Cake\Console\ConsoleIo))
eramba  | #22 /var/www/eramba/app/upgrade/vendor/cakephp/cakephp/src/Console/BaseCommand.php(179): App\Command\DatabaseInitializeCommand->execute(Object(Cake\Console\Arguments), Object(Cake\Console\ConsoleIo))
eramba  | #23 /var/www/eramba/app/upgrade/vendor/cakephp/cakephp/src/Console/CommandRunner.php(334): Cake\Console\BaseCommand->run(Array, Object(Cake\Console\ConsoleIo))
eramba  | #24 /var/www/eramba/app/upgrade/vendor/cakephp/cakephp/src/Console/CommandRunner.php(172): Cake\Console\CommandRunner->runCommand(Object(App\Command\DatabaseInitializeCommand), Array, Object(Cake\Console\ConsoleIo))
eramba  | #25 /var/www/eramba/app/upgrade/bin/cake.php(12): Cake\Console\CommandRunner->run(Array)
eramba  | #26 {main}.
eramba  | Migration process failed.
eramba  | Initialisation process failed.
eramba exited with code 1

I would be very grateful for a hint.

Greetings
Adrian

[shrkz1]

Did you modify the simple-install yml file for deploying eramba? can you show it here?
it seems the mysql you are running has a different sql_mode configured, as the error says "this is incompatible with sql_mode=only_full_group_by", but sql mode should be in fact no_engine_substitution.

[thebub]

Did you run the Docker container in a Docker Desktop, Rancher Desktop or WSL environment?

I realized, that in such environment the mysql/conf.d/custom.cnf is lot loaded by the mysqld, as it has the wrong file permissions. This might be due to the WSL mapping from the Windows NTFS filesystem to the container filesystem.
In my initial setup (Rancher Desktop) the custom.cnf was cloned from the repo as world writeable. Whereas when cloned on a Linux system, the file has the following permissions:

$ ls -al mysql/conf.d/custom.cnf 
-rw-r--r-- 1 vscode vscode 803 Jan  8 12:45 mysql/conf.d/custom.cnf

In the log of the mysql container I observed a line regarding the ignore custom.cnf file. When starting the containers from a Linux host, it worked fine.