Archiwa MySQL

During daily work with MySQL databases, specific challenges such as missing tables or errors related to the unrecognized database performance_schema can arise. These problems can significantly affect the performance and stability of database systems, and their diagnosis and resolution can often be complicated and time-consuming. To facilitate this task, I have created this guide, which is the result of my experiences and proven practices. I provide a comprehensive approach to identifying and fixing issues related to performance_schema. It’s quite simple to import the schema from a newly built database.

Of course, it’s best to perform a backup of the database before the entire operation.

Initial Diagnosis in MySQL

Start by identifying the problem in the MySQL shell:

SHOW VARIABLES LIKE 'default_storage_engine';
ERROR 1146 (42S02): Table 'performance_schema.session_variables' doesn't exist
USE performance_schema;
ERROR 1049 (42000): Unknown database 'performance_schema';

SHOW VARIABLES LIKE 'default_storage_engine';

ERROR 1146 (42S02): Table 'performance_schema.session_variables' doesn't exist

USE performance_schema;

ERROR 1049 (42000): Unknown database 'performance_schema';

Creating New Performance Schema in MySQL

Create a new performance schema from the MySQL command line:

CREATE DATABASE performance_schema;
shutdown;
\q

CREATE DATABASE performance_schema;

shutdown;

Server Operations to Establish New MySQL Environment

Return to the system to configure a clean MySQL environment:

systemctl stop mysql.service
mkdir /var/lib/mysql_new
mysqld --initialize --user=mysql --basedir=/usr --datadir=/var/lib/mysql_new
chown -R mysql:mysql /var/lib/mysql_new
cd /var/lib/mysql_new

systemctl stop mysql.service

mkdir /var/lib/mysql_new

mysqld --initialize --user=mysql --basedir=/usr --datadir=/var/lib/mysql_new

chown -R mysql:mysql /var/lib/mysql_new

cd /var/lib/mysql_new

Then, start MySQL in safe mode to configure it – without external network interruptions:

sudo -u mysql mysqld_safe --datadir=/var/lib/mysql_new --skip-grant-tables --skip-networking

1	sudo -u mysql mysqld_safe --datadir=/var/lib/mysql_new --skip-grant-tables --skip-networking

Configuration and Verification in MySQL

Return to the MySQL command line to verify the configuration and configure the new database:

mysql -u root
SHOW DATABASES;
USE performance_schema;
SHOW TABLES;

mysql -u root

SHOW DATABASES;

USE performance_schema;

SHOW TABLES;

As you can see, here performance_schema is working correctly.
Update the root password in the MySQL environment to secure the new installation:

USE mysql;
UPDATE user SET authentication_string=PASSWORD('NewSecurePassword');
FLUSH PRIVILEGES;
shutdown;
\q

USE mysql;

UPDATE user SET authentication_string=PASSWORD('NewSecurePassword');

FLUSH PRIVILEGES;

shutdown;

Restart MySQL in safe mode to ensure all configurations are applied:

sudo -u mysql mysqld_safe --datadir=/var/lib/mysql_new --skip-networking

1	sudo -u mysql mysqld_safe --datadir=/var/lib/mysql_new --skip-networking

Configure MySQL Server to Run New Database

After securing the new MySQL configuration, update the server configuration to use the new data directory:

vi /etc/mysql/my.cnf
# Change datadir to:
datadir=/var/lib/mysql_new

vi /etc/mysql/my.cnf

# Change datadir to:

datadir=/var/lib/mysql_new

Restart the MySQL service to load the my.cnf file during mysql startup:

systemctl start mysql.service

1	systemctl start mysql.service

Export and Restore Performance Schema in MySQL

With the new configuration, export performance_schema using MySQL commands and prepare for its restoration:

mysqldump -u root -p --no-data --skip-lock-tables performance_schema > /root/performance_schema.sql

1	mysqldump -u root -p --no-data --skip-lock-tables performance_schema > /root/performance_schema.sql

It’s necessary to shut down MySQL:

shutdown;
\q

1 2	shutdown; \q

Restore the original MySQL data directory on the server, restart the MySQL service, and import the exported schema:

vi /etc/mysql/my.cnf
# Change datadir back to:
datadir=/var/lib/mysql_old
systemctl start mysql.service
mysql -u root -p performance_schema < /root/performance_schema.sql
systemctl restart mysql.service

vi /etc/mysql/my.cnf

# Change datadir back to:

datadir=/var/lib/mysql_old

systemctl start mysql.service

mysql -u root -p performance_schema < /root/performance_schema.sql

systemctl restart mysql.service

Make sure the default storage engine is running, e.g:

SHOW VARIABLES LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)

SHOW VARIABLES LIKE 'default_storage_engine';

+------------------------+--------+

| Variable_name | Value |

+------------------------+--------+

| default_storage_engine | InnoDB |

+------------------------+--------+

1 row in set (0.00 sec)

Don’t forget to remove the trash:

rm -rf /var/lib/mysql_new/ /root/performance_schema.sql

1	rm -rf /var/lib/mysql_new/ /root/performance_schema.sql

If you’ve reached this point, remember to delete the database backup – as long as everything is working. You did make one, right? 🙂

Introduction

Working with MySQL, you may encounter various errors that can disrupt system operations. Error code 1114 is one of them and indicates a situation where the table the user is trying to write data to is full. This issue is particularly significant in the MySQL replication system, where its resolution is crucial for ensuring work continuity.

Problem Description

Error 1114 manifests itself with the message: “Could not execute Write_rows event on table docs; The table ‘docs’ is full”. This means that new rows cannot be written due to exceeding the size of the temporary table. The detailed error message might look like this:

Login to MySQL:

# mysql -u root -p

1	# mysql -u root -p

Change variable values:

SET GLOBAL tmp_table_size = 268435456;  -- Set to 256M
SET GLOBAL max_heap_table_size = 268435456;  -- Set to 256M

1 2	SET GLOBAL tmp_table_size = 268435456; -- Set to 256M SET GLOBAL max_heap_table_size = 268435456; -- Set to 256M

After making these changes, all new connections to the MySQL server will use these updated values. You can verify them by performing:

SHOW GLOBAL VARIABLES LIKE 'tmp_table_size';
SHOW GLOBAL VARIABLES LIKE 'max_heap_table_size';

1 2	SHOW GLOBAL VARIABLES LIKE 'tmp_table_size'; SHOW GLOBAL VARIABLES LIKE 'max_heap_table_size';

SELECT @@tmp_table_size, @@max_heap_table_size;

1	SELECT @@tmp_table_size, @@max_heap_table_size;

Now replication can be resumed and should work better. However, remember to modify the configuration so that after restarting MySQL these variables are set correctly. It may be necessary here to resume replication (if it was previously stopped):

START SLAVE;

1	START SLAVE;

If the problem has been resolved, at this stage checking the replication status:

show slave status \G

1	show slave status \G

Should not return any errors.

Modify the configuration file /etc/mysql/my.cnf:

# cat /etc/mysql/my.cnf
...
tmp_table_size=256M
max_heap_table_size=256M
...

# cat /etc/mysql/my.cnf

...

tmp_table_size=256M

max_heap_table_size=256M

...

Restart MySQL service:

# systemctl restart mysql

1	# systemctl restart mysql

Before restarting the service, it is recommended to execute SHUTDOWN; in the MySQL client.
Remember to resume replication.

Important Notes

System Resources: Ensure the server has sufficient RAM to handle the increased variable values.
Performance Monitoring: After making the changes, monitor performance to verify that the problem has been resolved.
Configuration Durability: Changes to the configuration file should be permanent to avoid resetting values after a restart.

Additional Verification Steps

Check Available Disk Space: The problem might also stem from lack of available disk space. This can be verified using the following command:

# df -h

# df -h

Summary

Resolving the issue associated with error code 1114 in MySQL replication requires understanding and adjusting the system configuration. The described steps show how increasing the size of the temporary table can prevent this error, enabling smooth operation of the replication system.

Category: MySQL

Resolving performance_schema Issues in MySQL

Initial Diagnosis in MySQL

Creating New Performance Schema in MySQL

Server Operations to Establish New MySQL Environment

Configuration and Verification in MySQL

Configure MySQL Server to Run New Database

Export and Restore Performance Schema in MySQL

MySQL Troubleshooting – ErrorCode: 1114

Introduction

Problem Description

Important Notes

Additional Verification Steps

Summary