MySQL ERROR 1114 (HY000) at line 2137: The table ‘X’ is full

MySQL MariaDB

When restoring a MySQL database, I get the following error

How to fix ERROR 1114 (HY000) table is full? First, check if the disk on the server is full.

In my case server disk was not full. Next try increasing the value for variables tmp_table_size and max_heap_table_size. Edit file

Under [mysqld] section, […]

Read More…

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails

MySQL MariaDB

When deleting a table in a MySQL database, I get the following error message

To fix the error, run

Now the drop table SQL command will work. After you dropped the table, re-enable foreign key check with

Example

[…]

Read More…

MySQL show processlist

MySQL

To see currently running MySQL processes, login to MySQL server, run in MySQL command prompt, run the command

To see the full SQL command, use

You can also use the following command from the bash command prompt

To see the number of SQL processes, use

See MySQL […]

Read More…

MySQL backup all databases

MySQL

To backup all databases on a MySQL server, run

This will create sql backup file for each database in your MySQL server on the current directory. The first command will create a file with all database names. Second command loop through the database names and create backup using mysqldump command. See MySQL Backup […]

Read More…

bash: mysql_safe: command not found

MySQL

On CentOS 7 server running MySQL 5.7, when trying to reset MySQL root password, I get an error

This is because MySQL 5.7 installation using yum removed the mysqld_safe binary file. To reset, you need to start MySQL using systemctl with skip-grant-tables options. So you don’t need mysqld_safe. To see how to reset MySQL […]

Read More…

CentOS 7 MySQL 5.7 root password reset

MySQL

To reset MySQL 5.7 root password on CentOS 7 server, do the following Stop MySQL Server

Set the MySQL MYSQLD_OPTS environment to start MySQL with –skip-grant-tables

Start MySQL with –skip-grant-tables

Login as user root

Update MySQL root password

Or

Exit MySQL command prompt

Stop MySQL server

Unset […]

Read More…

ERROR 1045 (28000): Plugin caching_sha2_password could not be loaded

When I try to connect to a MySQL 8 server, I get the following error

This is because of MySQL 8 use more secure authentication. This is not supported by your MySQL installation. What you can do is connect using MySQL 8 client or change the authentication method to use old authentication. To create […]

Read More…

Magento catalog_product_relation MySQL Error

On transferring a Magento website Database running on MySQL 5.7.34 to MariaDB 10.2.39, I get the following error

SQL Strict mode was already off on this MySQL server.

To fix the problem, edited the db.sql file, added

at the top of the file. After this change, the restore worked without any error. […]

Read More…