Variable ‘innodb_buffer_pool_size’ is a read only variable

Wondering how to resolve variable ‘innodb_buffer_pool_size’ is a read only variable? We can help you.

At Bobcares, we offer solutions for every query, big and small, as a part of our Server Management Service.

Let’s take a look at how our Support Team help a customer  deal with this Plesk error.

How to resolve variable ‘innodb_buffer_pool_size’ is a read only variable?

Typically, error will look as shown below:

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

Usually, it is not possible to change server-wide variables from PHPMyAdmin.

Today, let us see the steps followed by the Support Techs to change the server-wide variables in the MySQL configuration file.

1. Firstly, connect to the Plesk server via SSH for Linux or RDP for Windows/

2. Then, open the MySQL configuration file my.cnf or my.ini in any text editor

Locations of the file are:

for CentOS/RHEL:

/etc/my.cnfCopy Code

for Debian/Ubuntu:

/etc/mysql/my.cnfCopy Code

Then, for Windows:

%plesk_dir%Databases\MySQL\my.iniCopy Code

3. Next, add or change the needed variables under the [mysqld] section

[mysqld]

innodb_buffer_pool_size=103809024Copy Code

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

4. Then, save the changes and close the file

5. Finally, restart the MySQL service:

for CentOS/RHEL:

service mysqld restartCopy Code

Next, for Debian/Ubuntu:

service mysql restartCopy Code

Then, for Windows:

Open Task Manager > Services and restart MySQL56 service

[Need a solution to another query? We are just a click away.]

Conclusion

Today, we saw steps followed by our Support Engineers to resolve ‘innodb_buffer_pool_size’ is a read only error.