MySQL 5.6 conf changes [#35631]

Submitted by sgrayban on Sat, 12/20/2014 - 00:13

There a few changes in the my.cnf settings that you need to check and adjust for...

table_cache is now table_open_cache

open_files is now open_files_limit

see http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html for more info

For now I can't use the "Post-Installation Wizard" for mysql and set it for the Huge settings as it fails.........

MySQL could not be started with the new configuration :

Starting MySQL database server: mysqld . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . failed!

The old configuration has been restored.

Where is the code at that generates that my.cnf ?

Status:

Fixed (pending)

Comments

Submitted by sgrayban on Sat, 12/20/2014 - 01:09 Comment #1

Also the other MAJOR upgrade that MySQL 5.6 needs to allow larger then 32768 max_open_files you have to edit /etc/pam.d/common-auth and add this line...

session required pam_limits.so

Other wise MySQL will only allow max_open_files = 32768

Submitted by JamieCameron on Sat, 12/20/2014 - 15:11 Comment #2

Virtualmin doesn't set max_open_files or open_files or open_files_limit via the wizard though - the only thing it sets is table_open_cache, for MySQL versions at or above 5.1.3.

However, there may be some other directive that it is setting which is now obsolete .. .or maybe the startup failed for some other reason on your system. This should be visible in the MySQL error log file though.

Submitted by Blatant on Mon, 08/17/2020 - 12:19 Comment #3

I've also encountered a MySQL configuration error in the Post Installation Wizard. The version Vmin installedd was 5.7.31.

MySQL error log shows this during reconfiguration :

2020-08-17T16:53:32.353301Z 0 [Note] Giving 0 client threads a chance to die gracefully
2020-08-17T16:53:32.353361Z 0 [Note] Shutting down slave threads
2020-08-17T16:53:32.353379Z 0 [Note] Forcefully disconnecting 0 remaining clients
2020-08-17T16:53:32.353397Z 0 [Note] Event Scheduler: Purging the queue. 0 events
2020-08-17T16:53:32.353514Z 0 [Note] Binlog end
2020-08-17T16:53:32.354359Z 0 [Note] Shutting down plugin 'auth_socket'
2020-08-17T16:53:32.354386Z 0 [Note] Shutting down plugin 'ngram'
2020-08-17T16:53:32.354395Z 0 [Note] Shutting down plugin 'partition'
2020-08-17T16:53:32.354404Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2020-08-17T16:53:32.354413Z 0 [Note] Shutting down plugin 'ARCHIVE'
2020-08-17T16:53:32.354421Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2020-08-17T16:53:32.354468Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2020-08-17T16:53:32.354483Z 0 [Note] Shutting down plugin 'MyISAM'
2020-08-17T16:53:32.354500Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2020-08-17T16:53:32.354515Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2020-08-17T16:53:32.354524Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2020-08-17T16:53:32.354532Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2020-08-17T16:53:32.354540Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2020-08-17T16:53:32.354548Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2020-08-17T16:53:32.354557Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2020-08-17T16:53:32.354565Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2020-08-17T16:53:32.354573Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2020-08-17T16:53:32.354581Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2020-08-17T16:53:32.354589Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2020-08-17T16:53:32.354597Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2020-08-17T16:53:32.354646Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2020-08-17T16:53:32.354657Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2020-08-17T16:53:32.354665Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2020-08-17T16:53:32.354673Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2020-08-17T16:53:32.354681Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2020-08-17T16:53:32.354689Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2020-08-17T16:53:32.354697Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2020-08-17T16:53:32.354705Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2020-08-17T16:53:32.354712Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2020-08-17T16:53:32.354720Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2020-08-17T16:53:32.354728Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2020-08-17T16:53:32.354736Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2020-08-17T16:53:32.354744Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2020-08-17T16:53:32.354752Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2020-08-17T16:53:32.354760Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2020-08-17T16:53:32.354768Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2020-08-17T16:53:32.354776Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2020-08-17T16:53:32.354784Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2020-08-17T16:53:32.354792Z 0 [Note] Shutting down plugin 'InnoDB'
2020-08-17T16:53:32.354861Z 0 [Note] InnoDB: FTS optimize thread exiting.
2020-08-17T16:53:32.355161Z 0 [Note] InnoDB: Starting shutdown...
2020-08-17T16:53:32.455538Z 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
2020-08-17T16:53:32.455985Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 200817 17:53:32
2020-08-17T16:53:34.069815Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2720645
2020-08-17T16:53:34.071975Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2020-08-17T16:53:34.072001Z 0 [Note] Shutting down plugin 'MEMORY'
2020-08-17T16:53:34.072013Z 0 [Note] Shutting down plugin 'CSV'
2020-08-17T16:53:34.072067Z 0 [Note] Shutting down plugin 'sha256_password'
2020-08-17T16:53:34.072111Z 0 [Note] Shutting down plugin 'mysql_native_password'
2020-08-17T16:53:34.072397Z 0 [Note] Shutting down plugin 'binlog'
2020-08-17T16:53:34.072891Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

2020-08-17T16:53:37.012869Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-08-17T16:53:37.015402Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.31-0ubuntu0.18.04.1) starting as process 7047 ...
2020-08-17T16:53:37.022427Z 0 [Note] InnoDB: PUNCH HOLE support available
2020-08-17T16:53:37.022476Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-08-17T16:53:37.022488Z 0 [Note] InnoDB: Uses event mutexes
2020-08-17T16:53:37.022499Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2020-08-17T16:53:37.022509Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-08-17T16:53:37.022524Z 0 [Note] InnoDB: Using Linux native AIO
2020-08-17T16:53:37.022922Z 0 [Note] InnoDB: Number of pools: 1
2020-08-17T16:53:37.023094Z 0 [Note] InnoDB: Using CPU crc32 instructions
2020-08-17T16:53:37.026304Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-08-17T16:53:37.039562Z 0 [Note] InnoDB: Completed initialization of buffer pool
2020-08-17T16:53:37.042708Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-08-17T16:53:37.055798Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2020-08-17T16:53:37.068518Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-08-17T16:53:37.068686Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-08-17T16:53:37.335498Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-08-17T16:53:37.336783Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2020-08-17T16:53:37.336811Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2020-08-17T16:53:37.337573Z 0 [Note] InnoDB: Waiting for purge to start
2020-08-17T16:53:37.387839Z 0 [Note] InnoDB: 5.7.31 started; log sequence number 2720645
2020-08-17T16:53:37.388194Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2020-08-17T16:53:37.388493Z 0 [Note] Plugin 'FEDERATED' is disabled.
2020-08-17T16:53:37.390531Z 0 [Note] InnoDB: Buffer pool(s) load completed at 200817 17:53:37
2020-08-17T16:53:37.397946Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2020-08-17T16:53:37.397976Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2020-08-17T16:53:37.398924Z 0 [Warning] CA certificate ca.pem is self signed.
2020-08-17T16:53:37.398985Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2020-08-17T16:53:37.399121Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2020-08-17T16:53:37.399143Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2020-08-17T16:53:37.399183Z 0 [Note] Server socket created on IP: '127.0.0.1'.
2020-08-17T16:53:37.412497Z 0 [Note] Event Scheduler: Loaded 0 events
2020-08-17T16:53:37.412793Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.31-0ubuntu0.18.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

I'm getting blocked provisioning a live server that has to go up tonight! Can anyone help!!!

Edit : I did note that despite failing it did update the option to "Leave Unchanged", I've no idea what settings I actually got but I was aiming for Medium

Submitted by Ilia on Mon, 08/17/2020 - 12:16 Comment #4

Hi,

I've also encountered a MySQL configuration error in the Post Installation Wizard.

This MySQL issue has been fixed already and it will be included into upcoming Virtualmin 6.12

Meanwhile, you can fix it by running the following commands (on CentOS):

curl https://raw.githubusercontent.com/virtualmin/virtualmin-gpl/master/feature-mysql.pl -o /usr/libexec/webmin/virtual-server/feature-mysql.pl
curl https://raw.githubusercontent.com/virtualmin/virtualmin-gpl/master/wizard-lib.pl -o /usr/libexec/webmin/virtual-server/wizard-lib.pl
/etc/webmin/restart

Note: For Debian/Ubuntu you would need to replace libexec with share in path.