Not the answer you need?
Register and ask your own question!

config file does not work as expected

cwhsucwhsu ContributorCurrent User Role Beginner
It seems that percona does not set the config as I expected, but I already set the configs in mysqld.cnf under percona-xtradb-cluster.conf.d folder.

This is the percona cluster version:
Server version: 5.7.20-18-57-log Percona XtraDB Cluster (GPL), Release rel18, Revision 4a4da7e, WSREP version 29.24, wsrep_29.24

/etc/mysql/my.cnf
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/percona-xtradb-cluster.conf.d/

/etc/mysql/percona-xtradb-cluster.conf.d/mysqld.cnf
# Template my.cnf for PXC
# Edit to your requirements.
[mysqld]
server-id=1
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin
log_slave_updates
expire_logs_days=7
wait_timeout=86400
innodb_buffer_pool_size = 300M # (adjust value here, 50%-70% of total RAM) 
innodb_log_file_size = 64M
innodb_flush_log_at_trx_commit = 1 # may change to 2 or 0
innodb_flush_method = O_DIRECT
slow_query_log=1
slow_query_log_always_write_time=1
#log_queries_not_using_indexes=1
#log_output='FILE'

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

The wait_timeout is not 86400 as expected
mysql> show variables like "%wait_time%";
+-------------------------------------+----------+
| Variable_name                       | Value    |
+-------------------------------------+----------+
| innodb_lock_wait_timeout            | 50       |
| innodb_print_lock_wait_timeout_info | OFF      |
| lock_wait_timeout                   | 31536000 |
| [B]wait_timeout[/B]                        | 28800    |
+-------------------------------------+----------+
4 rows in set (0.00 sec)

I tried to use systemctl to reload and restart mysql, but nothing changed.
Is there anything wrong about the config?

Comments

  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi cwhsu;

    28800 is the default value for wait_timeout, so either your option file is not being used, or that particular setting is getting overwritten by another option file. I'd first check the ownership and permissions on your "mysqld.cnf" option file. Normally the permissions should be something like "640" (chmod 640), and owned by whatever user you run your DB as (i.e. chown mysql:mysql). If that is not the issue, then I'd start looking at the other option files that can be used by default to see if the setting is getting overwritten somewhere else (especially /etc/my.cnf).
  • cwhsucwhsu Contributor Current User Role Beginner
    Hi scott,

    Here is the folder contents of /etc/mysql.
    ls 
    drwxr-xr-x  2 root root 4096 Jan 23 23:22 conf.d/
    -rw-r--r--  1 root root  380 Dec 13 20:58 my.cnf
    -rw-r--r--  1 root root  839 Jan 22  2017 my.cnf.fallback
    -rw-r--r--  1 root root  380 Dec 13 20:58 my.cnf.old
    -rw-r--r--  1 root root  380 Oct 24 01:18 percona-xtradb-cluster.cnf
    drwxr-xr-x  2 root root 4096 Feb  6 16:38 percona-xtradb-cluster.conf.d/
    
    ls percona-xtradb-cluster.conf.d/
    -rw-r--r-- 1 root root   78 Oct 24 01:18 client.cnf
    -rw-r--r-- 1 root root  876 Feb  6 16:38 mysqld.cnf
    -rw-r--r-- 1 root root  450 Feb  6 16:29 mysqld_safe.cnf
    -rw-r--r-- 1 root root 1064 Oct 24 01:18 wsrep.cnf
    

    The strange thing is that the owner and group is not mysql.mysql, but the service is runnable. Hence, I don't think that is an issue. IMO, mysql first tries to load config from /etc/my.cnf and based on the include directive, it would then load config from the included directories. I try to add an incorrect config in percona-xtradb-cluster.conf.d/mysqld.cnf and restart mysql which failed as expected. So it does read the config in mysqld.cnf. But configs in mysqld.cnf are still being overridden, and there are no other configs in other fiiles. I am very confused about this. Anyway, thanks for the response.
  • cwhsucwhsu Contributor Current User Role Beginner
    ok, I find the reason why I think it's not working as expected. I actually want to set wait_timeout in the config, but wait_timeout=30000 does not work. However, when I add interactive_timeout=30000 to config, It actually works for both wait_timeout and interactive_timeout. Is there anything about this in the percona document? How do I know which is the correct config parameters?
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi cwhsu;

    Yeah that's odd. I tested it out locally and am able to set both individually. Basically the wait_timeout variable is for non-interactive connections, and interactive_timeout is for interactive connections. Would be interesting to see if there is an XtraDB-cluster specific reason for it.

    Otherwise I'd try setting wait_timeout directly in /etc/my.cnf or /etc/mysql/my.cnf under the [mysqld] tag to see if it happens to pick it up that way. Still seems likely there is an option file issue going on there somewhere.
  • cwhsucwhsu Contributor Current User Role Beginner
    That's really odd. It looks like a bug to me because they should be able to set individually. Where should I report this?

    Well, I try to move wait_timeout to /etc/mysql/my.cnf under [mysqld] tag, and it still does not work.
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi Cwhsu;

    I'd see if lorraine.pocklington is able to ask support for you (she's the community manager here). I'm guessing it's not a bug, and just something we're missing, so hopefully they could be of assistance.
  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    Hi Cwhsu;

    I'd see if lorraine.pocklington is able to ask support for you (she's the community manager here). I'm guessing it's not a bug, and just something we're missing, so hopefully they could be of assistance.

    Hi Scott, thanks for assisting cwhsu so far! I will highlight this post to the team to see if they have any insight.
  • cwhsucwhsu Contributor Current User Role Beginner
    Let me know if I can provide more details for you guys. Becuase I actually launch a new EC2 to test this with my install script. It's basically just what percona provides in the online document, and I use apt to install percona. Thanks for the help!
  • krunalbauskarkrunalbauskar Advisor Inactive User Role Novice
    As per MySQL doc (PS and PXC inherit upstream semantics)

    https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_wait_timeout this means wait_timeout value is defined by global wait_timeout or interactive_timout value.


    I tried setting checking for client terminal (mysql) and given that it is an interactive terminal, session inherited interactive_timeout

    [setting in my.cnf]
    wait_timeout=86400
    interactive_timeout=1000000

    mysql> select @@global.interactive_timeout;
    +
    +
    | @@global.interactive_timeout |
    +
    +
    | 1000000 |
    +
    +
    1 row in set (0.00 sec)

    mysql> select @@session.interactive_timeout;
    +
    +
    | @@session.interactive_timeout |
    +
    +
    | 1000000 |
    +
    +
    1 row in set (0.00 sec)

    mysql> select @@global.wait_timeout;
    +
    +
    | @@global.wait_timeout |
    +
    +
    | 86400 |
    +
    +
    1 row in set (0.00 sec)

    mysql> select @@session.wait_timeout;
    +
    +
    | @@session.wait_timeout |
    +
    +
    | 1000000 |
    +
    +
    1 row in set (0.00 sec)

    Hope this help clarify the confusion.
  • cwhsucwhsu Contributor Current User Role Beginner
    Ah! that explains everything. Thanks for the support!
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.