PHPMyAdmin error: Variable ‘innodb_buffer_pool_size’ is a read only variable

Symptoms

• in PHPMyAdmin > Startpage > Variables a similar error to the following message appears when changing the value of a server-wide variable:

  PLESK_ERROR: Error
  SQL query:
  SET GLOBAL innodb_buffer_pool_size = 2097152000
  MySQL said:
  #1238 – Variable ‘innodb_buffer_pool_size’ is a read only variable

Cause

It is not possible to change server-wide variables from PHPMyAdmin.

Resolution

To change the server-wide variables in the MySQL configuration file follow these steps:

1. Connect to the Plesk server via SSH for Linux or RDP for Windows/

2. Open the MySQL configuration file my.cnf or my.ini in any text editor
   Locations of the file are:

   • for CentOS/RHEL:

     /etc/my.cnf

   • for Debian/Ubuntu:

     /etc/mysql/my.cnf

   • for Windows:

     %plesk_dir%DatabasesMySQLmy.ini

3. Add or change the needed variables under the [mysqld] section

   [mysqld]
   innodb_buffer_pool_size=103809024

   Note: recommended range is 60~80% of RAM.

4. Save the changes and close the file

5. Restart the MySQL service:

   • for CentOS/RHEL:

     # service mysqld restart

   • for Debian/Ubuntu:

     # service mysql restart

   • for Windows:
     Open Task Manager > Services and restart MySQL56 service