.docksal/etc/mysql/my.cnf not respected

[jmdodge]

Description
I'm trying to override the max_allowed_packet mysql variable by setting it in .docksal/etc/mysql/my.cnf, but it doesn't seem to work. I have tried fin restart and fin reset with no success.

Steps to reproduce the issue:

1. Import a small mysql file by using fin sqli <filename> - SUCCESS
2. Import a large (13gb uncompressed) mysql file - FAIL (mysql server has gone away)
3. Add max_allowed_packet = 1024M to my.cnf.
4. fin restart, try import again - FAIL.
5. fin reset, try import again - FAIL
6. fin bash, open mysql console, run SET GLOBAL max_allowed_packet=1073741824;. Import again - SUCCESS

Also worth noting: I'm using the 'acquia' DOCKSAL_STACK. Everything else is default.

[achekulaev]

Thanks for reporting issues! Could you check db container logs with fin logs db. Often on windows file permissions end up being too broad and mysql refuses to load my.cnf that has permissions for others.
Usually the way to solve it is to delete file and create it in babun setting permissions level proper for my.cnf

Update from your output log in #209 I see that this is exactly the case.

Warning: World-writable config file '/etc/mysql/my.cnf' is ignored

[jmdodge]

@achekulaev - I want to note that I do not see the world writable config error when I go directly through fin bash (but also my .docksal/etc/mysql/my.cnf overrides are not working either).

So then my question is, is the .docksal/etc/mysql/my.cnf file only intended to work when using fin commands?

[achekulaev]

Do you have a properly formatted my.cnf? Unlike php.ini, MySQL requires my.cnf files to always have group before your options. If you don't have it then server will not accept it. Can you make sure your my.cnf looks like this:

[mysqld]
max_allowed_packet=1024M

If it did not look like this. You need to edit it and perform fin reset.

is the .docksal/etc/mysql/my.cnf file only intended to work when using fin commands?

my.cnf is being fed into MySQL server during server startup. Server either accepts it or not. There is no difference what software you use to communicate to MySQL server afterwards.

[jmdodge]

@achekulaev - there are a few comments in the file that I left in, but that is basically what it looks like. It looks properly formatted to me. Here is a complete paste:

[mysqld]

# Enable slow query logging
#long_query_time=1
#slow_query_log=1
#slow_query_log_file=/dev/stdout

# Needed for large sql imports
max_allowed_packet = 1024M

Also, you are saying there is no difference between using MySQL from within fin bash vs from outside as in fin sqli, but it seems like this cannot be true since in one case a permissions warning is produced and in the other case it is not.

[achekulaev]

1. What image do you use for db?
2. Can you show fin logs db output?

[jmdodge]

DB_IMAGE='docksal/db:1.0-mysql-5.5'

fin logs db

db_1         | Including custom configuration from /var/www/.docksal/etc/mysql/my.cnf
db_1         | Initializing database
db_1         | Warning: World-writable config file '/etc/mysql/my.cnf' is ignored
db_1         | Warning: World-writable config file '/etc/mysql/my.cnf' is ignored
db_1         | 170505  1:34:50 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
db_1         | 170505  1:34:50 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.5.54) starting as process 59 ...
db_1         | Warning: World-writable config file '/etc/mysql/my.cnf' is ignored
db_1         | 170505  1:34:50 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
db_1         | 170505  1:34:50 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.5.54) starting as process 65 ...
db_1         |
db_1         | PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
db_1         | To do so, start the server, then issue the following commands:
db_1         |
db_1         | /usr/local/mysql/bin/mysqladmin -u root password 'new-password'
db_1         | /usr/local/mysql/bin/mysqladmin -u root -h db password 'new-password'
db_1         |
db_1         | Alternatively you can run:
db_1         | /usr/local/mysql/bin/mysql_secure_installation
db_1         |
db_1         | which will also give you the option of removing the test
db_1         | databases and anonymous user created by default.  This is
db_1         | strongly recommended for production servers.
db_1         |
db_1         | See the manual for more instructions.
db_1         |
db_1         | Please report any problems at http://bugs.mysql.com/
db_1         |
db_1         | Database initialized
db_1         | Warning: World-writable config file '/etc/mysql/my.cnf' is ignored
db_1         | 170505  1:34:51 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
db_1         | 170505  1:34:51 [Note] mysqld (mysqld 5.5.54) starting as process 69 ...
db_1         | MySQL init process in progress...
db_1         | 170505  1:34:51 [Note] Plugin 'FEDERATED' is disabled.
db_1         | 170505  1:34:51 InnoDB: The InnoDB memory heap is disabled
db_1         | 170505  1:34:51 InnoDB: Mutexes and rw_locks use GCC atomic builtins
db_1         | 170505  1:34:51 InnoDB: Compressed tables use zlib 1.2.3
db_1         | 170505  1:34:51 InnoDB: Using Linux native AIO
db_1         | 170505  1:34:51 InnoDB: Initializing buffer pool, size = 128.0M
db_1         | 170505  1:34:51 InnoDB: Completed initialization of buffer pool
db_1         | InnoDB: The first specified data file ./ibdata1 did not exist:
db_1         | InnoDB: a new database to be created!
db_1         | 170505  1:34:51  InnoDB: Setting file ./ibdata1 size to 10 MB
db_1         | InnoDB: Database physically writes the file full: wait...
db_1         | 170505  1:34:51  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
db_1         | InnoDB: Setting log file ./ib_logfile0 size to 5 MB
db_1         | InnoDB: Database physically writes the file full: wait...
db_1         | 170505  1:34:51  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
db_1         | InnoDB: Setting log file ./ib_logfile1 size to 5 MB
db_1         | InnoDB: Database physically writes the file full: wait...
db_1         | InnoDB: Doublewrite buffer not found: creating new
db_1         | InnoDB: Doublewrite buffer created
db_1         | InnoDB: 127 rollback segment(s) active.
db_1         | InnoDB: Creating foreign key constraint system tables
db_1         | InnoDB: Foreign key constraint system tables created
db_1         | 170505  1:34:52  InnoDB: Waiting for the background threads to start
db_1         | MySQL init process in progress...
db_1         | 170505  1:34:53 InnoDB: 5.5.54 started; log sequence number 0
db_1         | 170505  1:34:53 [Note] Event Scheduler: Loaded 0 events
db_1         | 170505  1:34:53 [Note] mysqld: ready for connections.
db_1         | Version: '5.5.54'  socket: '/tmp/mysql.sock'  port: 0  MySQL Community Server (GPL)
db_1         | Warning: World-writable config file '/etc/mysql/my.cnf' is ignored
db_1         | Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
db_1         | Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
db_1         | Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
db_1         | Warning: World-writable config file '/etc/mysql/my.cnf' is ignored
db_1         | Warning: World-writable config file '/etc/mysql/my.cnf' is ignored
db_1         | Warning: World-writable config file '/etc/mysql/my.cnf' is ignored
db_1         | Warning: World-writable config file '/etc/mysql/my.cnf' is ignored
db_1         | Warning: World-writable config file '/etc/mysql/my.cnf' is ignored
db_1         |
db_1         | 170505  1:35:04 [Note] mysqld: Normal shutdown
db_1         |
db_1         | 170505  1:35:04 [Note] Event Scheduler: Purging the queue. 0 events
db_1         | 170505  1:35:04  InnoDB: Starting shutdown...
db_1         | 170505  1:35:06  InnoDB: Shutdown completed; log sequence number 1595675
db_1         | 170505  1:35:06 [Note] mysqld: Shutdown complete
db_1         |
db_1         |
db_1         | MySQL init process done. Ready for start up.
db_1         |
db_1         | Warning: World-writable config file '/etc/mysql/my.cnf' is ignored
db_1         | 170505  1:35:06 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
db_1         | 170505  1:35:06 [Note] mysqld (mysqld 5.5.54) starting as process 1 ...
db_1         | 170505  1:35:06 [Note] Plugin 'FEDERATED' is disabled.
db_1         | 170505  1:35:06 InnoDB: The InnoDB memory heap is disabled
db_1         | 170505  1:35:06 InnoDB: Mutexes and rw_locks use GCC atomic builtins
db_1         | 170505  1:35:06 InnoDB: Compressed tables use zlib 1.2.3
db_1         | 170505  1:35:06 InnoDB: Using Linux native AIO
db_1         | 170505  1:35:06 InnoDB: Initializing buffer pool, size = 128.0M
db_1         | 170505  1:35:06 InnoDB: Completed initialization of buffer pool
db_1         | 170505  1:35:06 InnoDB: highest supported file format is Barracuda.
db_1         | 170505  1:35:06  InnoDB: Waiting for the background threads to start
db_1         | 170505  1:35:07 InnoDB: 5.5.54 started; log sequence number 1595675
db_1         | 170505  1:35:07 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
db_1         | 170505  1:35:07 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
db_1         | 170505  1:35:07 [Note] Server socket created on IP: '0.0.0.0'.
db_1         | 170505  1:35:07 [Note] Event Scheduler: Loaded 0 events
db_1         | 170505  1:35:07 [Note] mysqld: ready for connections.
db_1         | Version: '5.5.54'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
db_1         | 170505 18:07:00  InnoDB: ERROR: the age of the last checkpoint is 9434680,
db_1         | InnoDB: which exceeds the log group capacity 9433498.
db_1         | InnoDB: If you are using big BLOB or TEXT rows, you must set the
db_1         | InnoDB: combined size of log files at least 10 times bigger than the
db_1         | InnoDB: largest such row.
db_1         | 170505 23:18:04 [Note] mysqld: Normal shutdown
db_1         |
db_1         | 170505 23:18:04 [Note] Event Scheduler: Purging the queue. 0 events
db_1         | 170505 23:18:06 [Warning] mysqld: Forcing close of thread 15  user: 'root'
db_1         |
db_1         | 170505 23:18:06  InnoDB: Starting shutdown...
db_1         | 170505 23:18:07  InnoDB: Shutdown completed; log sequence number 14877381197
db_1         | 170505 23:18:07 [Note] mysqld: Shutdown complete
db_1         |
db_1         | Including custom configuration from /var/www/.docksal/etc/mysql/my.cnf
db_1         | Warning: World-writable config file '/etc/mysql/my.cnf' is ignored
db_1         | 170505 23:41:45 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
db_1         | 170505 23:41:45 [Note] mysqld (mysqld 5.5.54) starting as process 1 ...
db_1         | 170505 23:41:45 [Note] Plugin 'FEDERATED' is disabled.
db_1         | 170505 23:41:45 InnoDB: The InnoDB memory heap is disabled
db_1         | 170505 23:41:45 InnoDB: Mutexes and rw_locks use GCC atomic builtins
db_1         | 170505 23:41:45 InnoDB: Compressed tables use zlib 1.2.3
db_1         | 170505 23:41:45 InnoDB: Using Linux native AIO
db_1         | 170505 23:41:45 InnoDB: Initializing buffer pool, size = 128.0M
db_1         | 170505 23:41:45 InnoDB: Completed initialization of buffer pool
db_1         | 170505 23:41:45 InnoDB: highest supported file format is Barracuda.
db_1         | 170505 23:41:45  InnoDB: Waiting for the background threads to start
db_1         | 170505 23:41:46 InnoDB: 5.5.54 started; log sequence number 14877381197
db_1         | 170505 23:41:46 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
db_1         | 170505 23:41:46 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
db_1         | 170505 23:41:46 [Note] Server socket created on IP: '0.0.0.0'.
db_1         | 170505 23:41:46 [Note] Event Scheduler: Loaded 0 events
db_1         | 170505 23:41:46 [Note] mysqld: ready for connections.
db_1         | Version: '5.5.54'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
db_1         | 170505 23:44:24  InnoDB: ERROR: the age of the last checkpoint is 9445960,
db_1         | InnoDB: which exceeds the log group capacity 9433498.
db_1         | InnoDB: If you are using big BLOB or TEXT rows, you must set the
db_1         | InnoDB: combined size of log files at least 10 times bigger than the
db_1         | InnoDB: largest such row.
db_1         | 170508 21:40:27 [Note] mysqld: Normal shutdown
db_1         |
db_1         | 170508 21:40:27 [Note] Event Scheduler: Purging the queue. 0 events
db_1         | 170508 21:40:27  InnoDB: Starting shutdown...
db_1         | 170508 21:40:29  InnoDB: Shutdown completed; log sequence number 48222950354
db_1         | 170508 21:40:29 [Note] mysqld: Shutdown complete
db_1         |
db_1         | Including custom configuration from /var/www/.docksal/etc/mysql/my.cnf
db_1         | Warning: World-writable config file '/etc/mysql/my.cnf' is ignored
db_1         | 170508 21:40:53 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
db_1         | 170508 21:40:53 [Note] mysqld (mysqld 5.5.54) starting as process 1 ...
db_1         | 170508 21:40:53 [Note] Plugin 'FEDERATED' is disabled.
db_1         | 170508 21:40:53 InnoDB: The InnoDB memory heap is disabled
db_1         | 170508 21:40:53 InnoDB: Mutexes and rw_locks use GCC atomic builtins
db_1         | 170508 21:40:53 InnoDB: Compressed tables use zlib 1.2.3
db_1         | 170508 21:40:53 InnoDB: Using Linux native AIO
db_1         | 170508 21:40:53 InnoDB: Initializing buffer pool, size = 128.0M
db_1         | 170508 21:40:53 InnoDB: Completed initialization of buffer pool
db_1         | 170508 21:40:53 InnoDB: highest supported file format is Barracuda.
db_1         | 170508 21:40:53  InnoDB: Waiting for the background threads to start
db_1         | 170508 21:40:54 InnoDB: 5.5.54 started; log sequence number 48222950354
db_1         | 170508 21:40:54 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
db_1         | 170508 21:40:54 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
db_1         | 170508 21:40:54 [Note] Server socket created on IP: '0.0.0.0'.
db_1         | 170508 21:40:54 [Note] Event Scheduler: Loaded 0 events
db_1         | 170508 21:40:54 [Note] mysqld: ready for connections.
db_1         | Version: '5.5.54'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)

[achekulaev]

db_1         | Including custom configuration from /var/www/.docksal/etc/mysql/my.cnf

Looks included to me. I tried your config on Windows and it worked fine.

Does the file my.cnf have Linux line endings?

And how do you check that it's not working?

[jmdodge]

@achekulaev - the my.cnf file has linux line endings.

I describe the way I am testing if it works in my initial report above. Basically, I have a large sql file that I'm trying to import. It will fail very quickly if max_allowed_packet is not increased. I am also able to show variables in the mysql cli and see that they are not as expected. The only way I can increase it is by setting a global variable within the mysql CLI. After increasing it, I can import the file.

I guess it is seeming likely that the root of this problem is simply that the extra config file is ignored due to file permissions, which we can see clearly in the logs. The only thing I cannot reconcile is that Warning: World-writable config file '/etc/mysql/my.cnf' is ignored only appears when I am running fin commands, but not when I am running mysql commands from the command line within fin bash - but it is probably useless to pursue this until the primary issue with file perms is resolved.

[achekulaev]

/etc/mysql/my.cnf is not your config file. Your config file ends up in a different location inside db container. And as far as I see on logs this is happening. db container copies your custom config to /etc/mysql/conf.d/99-overrides.cnf inside itself. When it is doing that it reports: Including custom configuration from /var/www/.docksal/etc/mysql/my.cnf. And this is happening.

My initial supposition about permissions was wrong because I forgot how we made db to work.

[jmdodge]

Two things:

1. /etc/mysql/my.cnf has the line: !includedir /etc/mysql/conf.d/ - which presumably is the only mechanism for including anything within the conf.d subdirectory. So if /etc/mysql/my.cnf is ignored, supplemental configuration files would not be included - right?
2. If I fin bash and list the contents of the conf.d folder, it is empty:

docker@cli:/etc/mysql$ ls -la /etc/mysql/conf.d/
total 8
drwxr-xr-x 2 root root 4096 Apr  7 17:50 .
drwxr-xr-x 3 root root 4096 Apr  7 17:50 ..
-rw-r--r-- 1 root root    0 Jan 19 08:13 .keepme

[achekulaev]

Hmm. I suspect that you might have an older version of db image locally.
Could you show output of fin docker images ?

[achekulaev]

fin bash logs you into cli container while your my.cnf is inside db container.
To get into db container you can do fin bash db. Be warned that command line interface is very limited inside db container.

[jmdodge]

Thanks for that tip! Apologies if my lack of understanding for the general Docker architecture has made more of a mess here.

So if I run fin bash db and then try to run mysql, I see Warning: World-writable config file '/etc/mysql/my.cnf' is ignored. However, if I look in /etc/mysql/conf.d/, I see 99-overrides.cnf, and it's contents matches my expected override file.

So I am thinking that the issue is what I mentioned above - docker/docksal appears to be doing it's job, but since the primary my.cnf file is ignored, !includedir /etc/mysql/conf.d/ never actually runs, so my override file is moot. Whatchya think?

Here is the output of fin docker images:

REPOSITORY            TAG                 IMAGE ID            CREATED             SIZE
memcached             latest              c483d9f54256        2 weeks ago         83.7MB
docksal/cli           1.2-php5            11f47dc7d024        4 weeks ago         845MB
docksal/cli           1.2-php7            aef362412c9f        4 weeks ago         787MB
docksal/vhost-proxy   1.0                 2c8a8b1e62c4        2 months ago        164MB
docksal/dns           1.0                 20c7a535479d        3 months ago        61.6MB
docksal/ssh-agent     1.0                 148220cc033d        3 months ago        12.2MB
docksal/web           1.0-apache2.2       a9c03f8a5f0f        3 months ago        188MB
docksal/db            1.0-mysql-5.5       9fb8c0aed5fc        3 months ago        255MB
docksal/solr          1.0-solr4           fa6b236caf06        4 months ago        546MB
docksal/varnish       1.0-varnish4        35dae6b91ff9        4 months ago        99.5MB

[achekulaev]

I guess you're right about the root cause but I still can't figure out why it happens.

Your docker/db container seems to be of a proper version. I wonder what causes Warning: World-writable config file '/etc/mysql/my.cnf' is ignored. This file is not mapped from your host. It is a part of docker/db image and it's permissions should be just fine.

Could you show your fin config?

UPDATE: and could you also log into db with fin bash db and show output of ls -la /etc/mysql

[jmdodge]

Here's the ls -la:

root@db:/# cd /etc/mysql
root@db:/etc/mysql# ls -la
total 16
drwxr-xrwx  5 root root 4096 May  5 01:34 .
drwxr-xrwx 46 root root 4096 May  5 01:34 ..
drwxr-xrwx  2 root root 4096 May  5 01:34 conf.d
-rw-r--rwx  1 root root   99 Jan 17 18:05 my.cnf

By the way, I tried changing the perms from within fin bash db:

chmod o-w /etc/mysql/my.cnf

and after a fin restart, this appears to totally resolve the issue.

Here's fin config

---------------------
COMPOSE_PROJECT_NAME_SAFE: rogercpaprod
COMPOSE_FILE:
C:/Users/jmdod/.babun/cygwin/home/jmdod/.docksal/stacks/volumes-bind.yml
C:/Users/jmdod/.babun/cygwin/home/jmdod/.docksal/stacks/stack-acquia.yml
ENV_FILE:
C:/devdesktop/sites/rogercpa-prod/.docksal/docksal.env

PROJECT_ROOT: /c/devdesktop/sites/rogercpa-prod
DOCROOT: docroot
VIRTUAL_HOST: rogercpa-prod.docksal
VIRTUAL_HOST_ALIASES: *.rogercpa-prod.docksal
IP: 192.168.64.100
MYSQL: 192.168.64.100:32770

Docker Compose configuration
---------------------
networks: {}
services:
  cli:
    dns:
    - 192.168.64.100
    - 10.0.2.3
    environment:
      HOST_GID: '11076'
      HOST_UID: '1076'
      XDEBUG_ENABLED: '1'
    hostname: cli
    image: docksal/cli:1.2-php5
    volumes:
    - host_home:/.home:ro
    - docksal_ssh_agent:/.ssh-agent:ro
    - project_root:/var/www:rw
  db:
    dns:
    - 192.168.64.100
    - 10.0.2.3
    environment:
      MYSQL_DATABASE: default
      MYSQL_PASSWORD: user
      MYSQL_ROOT_PASSWORD: root
      MYSQL_USER: user
    hostname: db
    image: docksal/db:1.0-mysql-5.5
    ports:
    - 0:3306/tcp
    volumes:
    - project_root:/var/www:ro
  memcached:
    dns:
    - 192.168.64.100
    - 10.0.2.3
    environment:
      MEMCACHED_MEMORY_LIMIT: '128'
    hostname: memcached
    image: memcached
  solr:
    dns:
    - 192.168.64.100
    - 10.0.2.3
    hostname: solr
    image: docksal/solr:1.0-solr4
  varnish:
    depends_on:
      web:
        condition: service_started
    dns:
    - 192.168.64.100
    - 10.0.2.3
    environment:
      VARNISH_BACKEND_HOST: web
    hostname: varnish
    image: docksal/varnish:1.0-varnish4
    labels:
      io.docksal.virtual-host: varnish.rogercpa-prod.docksal
  web:
    depends_on:
      cli:
        condition: service_started
    dns:
    - 192.168.64.100
    - 10.0.2.3
    environment:
      APACHE_DOCUMENTROOT: /var/www/docroot
    hostname: web
    image: docksal/web:1.0-apache2.2
    labels:
      io.docksal.project-root: /c/devdesktop/sites/rogercpa-prod
      io.docksal.virtual-host: rogercpa-prod.docksal,*.rogercpa-prod.docksal
    volumes:
    - project_root:/var/www:ro
version: '2.1'
volumes:
  docksal_ssh_agent:
    external: true
  host_home:
    driver: local
    driver_opts:
      device: /c/Users/jmdod/.babun/cygwin/home/jmdod
      o: bind
      type: none
  project_root:
    driver: local
    driver_opts:
      device: /c/devdesktop/sites/rogercpa-prod
      o: bind
      type: none

---------------------

[achekulaev]

I don't see a single reason why that file would have those permissions inside db. Completely puzzled. Yeah I understand that changing those permissions would completely solve the issue but it's still puzzling why would it happend at the first place.

I'll keep it open. In case you see it again please don't hesitate to post here.

[achekulaev]

It might be related to the need of health check before proceeding with starting other services. We have #225 opened for that. I'm closing this for now in favor of that one. In case someone has similar issue please comment.