Closed grooverdan closed 1 year ago
gremo
commented
1 year ago Thanks @grooverdan. I don't have enough knowledge to say something about the grants... but if this would solve the log flooding when using the healtcheck, i'me very happy with the change.
Hope that this get merged soon and new images released!
jerdoe
commented
1 year ago Closes #430
@mmontes11, @gremo, @jerdoe, what do you think?
The grant on
mysql@::1mysql@127.0.0.1is excessive to the TCP requirements, not used in other healthcheck tests. Original I was thinking of including them for consistency, but any objection if that is dropped?tested with:
$ podman run --rm --env MARIADB_ROOT_PASSWORD=bob --env MARIADB_MYSQL_LOCALHOST_USER=1 --health-cmd='healthcheck.sh --su-mysql --connect' m106 2023-05-16 00:10:32+00:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:10.6.13+maria~ubu2004 started. 2023-05-16 00:10:32+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql' 2023-05-16 00:10:32+00:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:10.6.13+maria~ubu2004 started. 2023-05-16 00:10:32+00:00 [Note] [Entrypoint]: Initializing database files ... 2023-05-16 0:10:35 0 [Warning] You need to use --log-bin to make --expire-logs-days or --binlog-expire-logs-seconds work. 2023-05-16 0:10:35 0 [Note] Server socket created on IP: '0.0.0.0'. 2023-05-16 0:10:35 0 [Note] Server socket created on IP: '::'. 2023-05-16 0:10:35 0 [Note] mariadbd: ready for connections. Version: '10.6.13-MariaDB-1:10.6.13+maria~ubu2004' socket: '/run/mysqld/mysqld.sock' port: 3306 mariadb.org binary distribution@grooverdan, Thank you for being concerned. Apart from having some extra grant entries in the database (which I don't mind), what would be the flaws and side effects of using such an approach?
mmontes11
commented
1 year ago but any objection if that is dropped?
mariadb-operator does not rely on the mysql user for now, we're good.
jerdoe
commented
1 year ago Sorry, i am not sure of what your question is. You mean that this pull request is not the proper approach and ask if this is ok to drop it ?
grooverdan
commented
1 year ago The "ok to drop" was only referring to the additional grants, which I'll do for now. If there's a good reason to add them later that can be done too in a backwards compatible way.
grooverdan
commented
1 year ago Current problem with implementation highlighted with test case is:
$ docker run --name mariadbcontainer31740 --rm -e MYSQL_RANDOM_ROOT_PASSWORD=1 -e MARIADB_MYSQL_LOCALHOST_USER=1 m109 --plugin-load-add=simple_password_check
...
2023-05-17 03:57:35+00:00 [Note] [Entrypoint]: Temporary server started.
2023-05-17 03:57:35+00:00 [Note] [Entrypoint]: GENERATED ROOT PASSWORD: I],(@<YmxOwX@PJ3DNXe&Pjq5308`mj`
2023-05-17 03:57:35+00:00 [Note] [Entrypoint]: Securing system users (equivalent to running mysql_secure_installation)
ERROR 1819 (HY000) at line 18: Your password does not satisfy the current policy requirements (simple_password_check)Any update on this? When will get merged? Thanks
grooverdan
commented
1 year ago @gremo I've worked around the password policy complexity. Can you please review this?
Tested with:
$ podman run --rm --name m --env MARIADB_ROOT_PASSWORD=bob --health-cmd='healthcheck.sh --connect' --health-interval=1s m1011
2023-06-15 06:18:12+00:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:10.11.4+maria~ubu2204 started.
2023-06-15 06:18:12+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2023-06-15 06:18:12+00:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:10.11.4+maria~ubu2204 started.
2023-06-15 06:18:12+00:00 [Note] [Entrypoint]: Initializing database files
2023-06-15 6:18:12 0 [Warning] mariadbd: io_uring_queue_init() failed with ENOSYS: check seccomp filters, and the kernel version (newer than 5.1 required)
2023-06-15 6:18:12 0 [Warning] InnoDB: liburing disabled: falling back to innodb_use_native_aio=OFF
PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following command:
'/usr/bin/mariadb-secure-installation'
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the MariaDB Knowledgebase at https://mariadb.com/kb
Please report any problems at https://mariadb.org/jira
The latest information about MariaDB is available at https://mariadb.org/.
Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/
2023-06-15 06:18:13+00:00 [Note] [Entrypoint]: Database files initialized
2023-06-15 06:18:13+00:00 [Note] [Entrypoint]: Starting temporary server
2023-06-15 06:18:13+00:00 [Note] [Entrypoint]: Waiting for server startup
2023-06-15 6:18:13 0 [Note] Starting MariaDB 10.11.4-MariaDB-1:10.11.4+maria~ubu2204 source revision 4e2b93dffef2414a11ca5edc8d215f57ee5010e5 as process 114
2023-06-15 6:18:13 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2023-06-15 6:18:13 0 [Note] InnoDB: Number of transaction pools: 1
2023-06-15 6:18:13 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2023-06-15 6:18:13 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)
2023-06-15 6:18:13 0 [Warning] mariadbd: io_uring_queue_init() failed with ENOSYS: check seccomp filters, and the kernel version (newer than 5.1 required)
2023-06-15 6:18:13 0 [Warning] InnoDB: liburing disabled: falling back to innodb_use_native_aio=OFF
2023-06-15 6:18:13 0 [Note] InnoDB: Initializing buffer pool, total size = 128.000MiB, chunk size = 2.000MiB
2023-06-15 6:18:13 0 [Note] InnoDB: Completed initialization of buffer pool
2023-06-15 6:18:13 0 [Note] InnoDB: Buffered log writes (block size=512 bytes)
2023-06-15 6:18:13 0 [Note] InnoDB: 128 rollback segments are active.
2023-06-15 6:18:13 0 [Note] InnoDB: Setting file './ibtmp1' size to 12.000MiB. Physically writing the file full; Please wait ...
2023-06-15 6:18:13 0 [Note] InnoDB: File './ibtmp1' size is now 12.000MiB.
2023-06-15 6:18:13 0 [Note] InnoDB: log sequence number 45468; transaction id 14
2023-06-15 6:18:13 0 [Note] Plugin 'FEEDBACK' is disabled.
2023-06-15 6:18:13 0 [Warning] 'user' entry 'root@9bf1dd495e88' ignored in --skip-name-resolve mode.
2023-06-15 6:18:13 0 [Warning] 'proxies_priv' entry '@% root@9bf1dd495e88' ignored in --skip-name-resolve mode.
2023-06-15 6:18:13 0 [Note] mariadbd: ready for connections.
Version: '10.11.4-MariaDB-1:10.11.4+maria~ubu2204' socket: '/run/mysqld/mysqld.sock' port: 0 mariadb.org binary distribution
2023-06-15 06:18:14+00:00 [Note] [Entrypoint]: Temporary server started.
2023-06-15 06:18:14+00:00 [Note] [Entrypoint]: Securing system users (equivalent to running mysql_secure_installation)
2023-06-15 06:18:14+00:00 [Note] [Entrypoint]: Stopping temporary server
2023-06-15 6:18:14 0 [Note] mariadbd (initiated by: unknown): Normal shutdown
2023-06-15 6:18:14 0 [Note] InnoDB: FTS optimize thread exiting.
2023-06-15 6:18:14 0 [Note] InnoDB: Starting shutdown...
2023-06-15 6:18:14 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
2023-06-15 6:18:14 0 [Note] InnoDB: Buffer pool(s) dump completed at 230615 6:18:14
2023-06-15 6:18:14 0 [Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1"
2023-06-15 6:18:14 0 [Note] InnoDB: Shutdown completed; log sequence number 46718; transaction id 15
2023-06-15 6:18:14 0 [Note] mariadbd: Shutdown complete
2023-06-15 06:18:14+00:00 [Note] [Entrypoint]: Temporary server stopped
2023-06-15 06:18:14+00:00 [Note] [Entrypoint]: MariaDB init process done. Ready for start up.
2023-06-15 6:18:14 0 [Note] Starting MariaDB 10.11.4-MariaDB-1:10.11.4+maria~ubu2204 source revision 4e2b93dffef2414a11ca5edc8d215f57ee5010e5 as process 1
2023-06-15 6:18:14 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2023-06-15 6:18:14 0 [Note] InnoDB: Number of transaction pools: 1
2023-06-15 6:18:14 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2023-06-15 6:18:14 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)
2023-06-15 6:18:14 0 [Warning] mariadbd: io_uring_queue_init() failed with ENOSYS: check seccomp filters, and the kernel version (newer than 5.1 required)
2023-06-15 6:18:14 0 [Warning] InnoDB: liburing disabled: falling back to innodb_use_native_aio=OFF
2023-06-15 6:18:14 0 [Note] InnoDB: Initializing buffer pool, total size = 128.000MiB, chunk size = 2.000MiB
2023-06-15 6:18:14 0 [Note] InnoDB: Completed initialization of buffer pool
2023-06-15 6:18:14 0 [Note] InnoDB: Buffered log writes (block size=512 bytes)
2023-06-15 6:18:14 0 [Note] InnoDB: 128 rollback segments are active.
2023-06-15 6:18:14 0 [Note] InnoDB: Setting file './ibtmp1' size to 12.000MiB. Physically writing the file full; Please wait ...
2023-06-15 6:18:14 0 [Note] InnoDB: File './ibtmp1' size is now 12.000MiB.
2023-06-15 6:18:14 0 [Note] InnoDB: log sequence number 46718; transaction id 14
2023-06-15 6:18:14 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2023-06-15 6:18:14 0 [Note] Plugin 'FEEDBACK' is disabled.
2023-06-15 6:18:14 0 [Warning] You need to use --log-bin to make --expire-logs-days or --binlog-expire-logs-seconds work.
2023-06-15 6:18:14 0 [Note] InnoDB: Buffer pool(s) load completed at 230615 6:18:14
2023-06-15 6:18:14 0 [Note] Server socket created on IP: '0.0.0.0'.
2023-06-15 6:18:14 0 [Note] Server socket created on IP: '::'.
2023-06-15 6:18:14 0 [Note] mariadbd: ready for connections.
Version: '10.11.4-MariaDB-1:10.11.4+maria~ubu2204' socket: '/run/mysqld/mysqld.sock' port: 3306 mariadb.org binary distributionAfter a while of running without traffic:
$ podman ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
9bf1dd495e88 localhost/m1011:latest mariadbd 3 minutes ago Up 3 minutes (healthy) m
MariaDB [(none)]> show global status like '%con%';
+-----------------------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------------------+----------------------+
| Aborted_connects | 83 |
| Aborted_connects_preauth | 0 |
| Com_show_contributors | 0 |
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 0 |
| Connection_errors_peer_address | 0 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
| Connections | 86 |
| Feature_check_constraint | 1 |
| Max_used_connections | 2 |
| Performance_schema_cond_classes_lost | 0 |
| Performance_schema_cond_instances_lost | 0 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Slave_connections | 0 |
| Slaves_connected | 0 |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_finished_connects | 0 |
| Threads_connected | 1 |
| wsrep_cluster_conf_id | 18446744073709551615 |
| wsrep_connected | OFF |
+-----------------------------------------------+----------------------+
23 rows in set (0.001 sec)
MariaDB [(none)]> ^DByeWhile going down the healthcheck_connect user, should I go further and leave it unlocked with USAGE privileges corresponding to the required checks for non-replication based healtcheck.sh script (top of script).
Pros:
MARIADB_MYSQL_LOCALHOST_{USER,GRANTS} complexity and straight healtcheck.sh [--{option|test} ....] is needed.protocol=tcp in .my-healthcheck.cnf the --connect test is there by default with saves users from false --innodb-initialized tests which can become true early.
Cons:MARIADB_MYSQL_LOCALHOST_{USER,GRANTS} may get different behaviour as ${def['extra_file']:---defaults-extra-file="$datadir"/.my-healthcheck.cnf} propagates to _process_sql. This will only really be evident if MARIADB_MYSQL_LOCALHOST_GRANTS was necessary for the healtcheck to succeed.heathcheck_connect for --replication, env HEALTHCHECK_GRANTS option is needed. (alternative: pass the checks as an env accessible to init (healthcheck.sh with --init option creates required grants) and healthcheck?).gremo
commented
1 year ago I can't see the updated Docker image, hasn't it been published yet?
In addition, it would be great to document how to make the healthcheck work now that there is a new TCP user. Is this still valid?
db:
# ...
environment:
MARIADB_MYSQL_LOCALHOST_USER=true
MARIADB_MYSQL_LOCALHOST_GRANTS=USAGE
healthcheck:
test: ['CMD', '/usr/local/bin/healthcheck.sh', '--su-mysql', '--connect', '--innodb_initialized']I broke a few tests that have just been fixed. I was going to let it run in CI for a bit, and if fine, look at a release next week.
The change will be compatible, but there will be a simpler version for newly initialized datadirs.
Closes #430
@mmontes11, @gremo, @jerdoe, what do you think?
The grant on
mysql@::1mysql@127.0.0.1is excessive to the TCP requirements, not used in other healthcheck tests. Original I was thinking of including them for consistency, but any objection if that is dropped?tested with: