Category: MySQL

MySQL 8 Your password does not satisfy the current policy requirements
When transferring a website from one Cpanel server to another server running MySQL 8, I got the following error message
```
Cpanel::Exception::Database::Error/(XID u99as8) The system received an error from the “MySQL” database “mysql”: 
1819 (Your password does not satisfy the current policy requirements)
```
The error is due to validate_password component.

https://dev.mysql.com/doc/mysql-secure-deployment-guide/8.0/en/secure-deployment-password-validation.html

Method 1

Edit mysql configuration file, add following under [mysqld] section.
```
validate_password.policy=0
```
Policy 0 is LOW, default value is 1, that is MEDIUM.

Restart MySQL
```
systemctl restart mysqld
```
Method 2: Disable Password Validation Component

To check if MySQL Password Validation Component is enabled, run the command
```
mysql -sse "SELECT * FROM mysql.component"
```
If you have Password Validation Component enabled, you will see
```
[root@server2 ~]# mysql -sse "SELECT * FROM mysql.component"
1	1	file://component_validate_password
[root@server2 ~]#
```
To disable the component, run
```
mysql -sse "UNINSTALL COMPONENT 'file://component_validate_password';"
```
Back to MySQL 8
September 14, 2022
Change MySQL root password in Webuzo
Webuzo stores MySQL root password in file
```
/var/webuzo/my.conf
```
If you changed MySQL/MariaDB root password outside of Webuzo, you need to update this file.

You may also need to modify the file
```
/root/.my.cnf
```
This is only for command line access.

To change the MySQL root password on Webuzo, login to the Webuzo control panel at
```
https://your-server-ip:2005/
```
Go to
```
SQL Services > MYSQL Root Password
```
Back to Webuzo
July 21, 2022
Change MariaDB user password
To change the MariaDB root password, log in to MySQL as the user root
```
mysql -u root -p
```
Enter your current password when prompted.

Use the following command to change the root password
```
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NEW_PW_HERE');
```
Replace NEW_PW_HERE with your new MySQL root password.

Change a user password
```
SET PASSWORD FOR 'USER_HERE'@'localhost' = PASSWORD('NEW_PW_HERE');
```
Example
```
SET PASSWORD FOR 'admin'@'localhost' = PASSWORD('serverok123');
```
For more info, see

https://mariadb.com/kb/en/set-password/
July 19, 2022
How to create MySQL read only user
To create a MySQL database user with read-only access to a database, use the following command
```
GRANT SELECT, SHOW VIEW ON DB_NAME.* TO USER_NAME@'localhost' IDENTIFIED BY 'PASSWORD';
```
In the above SQL statement

DB_NAME = name of the database to which you need to give read-only access.

USER_NAME = username of the read-only user

PASSWORD = password of the read-only user.
June 23, 2022

MySQL Could not create unix socket lock file

When trying to reset MySQL 8 user root password, I got the following error message in the log file (/var/log/mysql/error.log).

[System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.29-0ubuntu0.20.04.3) starting as process 265880
[System] [MY-013576] [InnoDB] InnoDB initialization has started.
[System] [MY-013577] [InnoDB] InnoDB initialization has ended.
[ERROR] [MY-011292] [Server] Plugin mysqlx reported: 'Preparation of I/O interfaces failed, X Protocol won't be accessible'
[ERROR] [MY-011300] [Server] Plugin mysqlx reported: 'Setup of socket: '/var/run/mysqld/mysqlx.sock' failed, can't create lock file /var/run/mysqld/mysqlx.sock.lock'
[Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
[System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
[ERROR] [MY-010273] [Server] Could not create unix socket lock file /var/run/mysqld/mysqld.sock.lock.
[ERROR] [MY-010268] [Server] Unable to setup unix socket lock file.
[ERROR] [MY-010119] [Server] Aborting
[System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.29-0ubuntu0.20.04.3)  (Ubuntu).

The error is

[ERROR] [MY-010273] [Server] Could not create unix socket lock file /var/run/mysqld/mysqld.sock.lock.

To fix the error, create the folder, and change its ownership to user “mysql”.

mkdir /var/run/mysqld/
chown mysql:mysql /var/run/mysqld/

June 18, 2022

MySQL 5.7 Community Server GPG keys already installed but they are not correct

On a CentOS 7 server, when updating software packages with the yum update command, it fails with the following error message.

warning: rpmts_HdrFromFdno: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql


The GPG keys listed for the "MySQL 5.7 Community Server" repository are already installed but they are not correct for this package.
Check that the correct key URLs are configured for this repository.

To fix the error, run the command

rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

See MySQL

May 21, 2022

How to install MySQL 5.6 on CentOS 7
CentOS 7 come with MariaDB, a drop-in replacement for MySQL. In some cases, you need to install the Oracle MySQL server.

Download and install MySQL repository
```
rpm -ivh http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
```
Next, you can install the MySQL server by running the command
```
yum install mysql-community-server
```
Enable MySQL to start on boot
```
systemctl enable mysqld
```
Start MySQL with
```
systemctl start mysqld
```
To secure MySQL run
```
mysql_secure_installation
```
January 29, 2022

MySQL Specified key was too long; max key length is 767 bytes

When restoring a MySQL database, I got the error “Specified key was too long; max key length is 767 bytes”.

user@host [~]$ mysql new_db < wordpress-2021-12-19-b51f8a6.sql 
ERROR 1071 (42000) at line 745 in file: 'wordpress-2021-12-19-b51f8a6.sql': Specified key was too long; max key length is 767 bytes
user@host [~]$

MySQL version 5.6 and older versions have a limit of 767 bytes prefix limit.

https://dev.mysql.com/doc/refman/5.6/en/create-index.html

Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 767 bytes long for InnoDB tables or 3072 bytes if the innodb_large_prefix option is enabled. For MyISAM tables, the prefix length limit is 1000 bytes.

From the error message, the error was on line 745, when checking the SQL file, I found the following SQL statement.

CREATE TABLE `wpk4_gla_merchant_issues` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) NOT NULL,
  `issue` varchar(200) COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `code` varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `severity` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'warning',
  `product` varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `action` varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `action_url` varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `applicable_countries` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `source` varchar(10) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'mc',
  `type` varchar(10) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'product',
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_issue` (`product_id`,`issue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

To fix it, find

UNIQUE KEY `product_issue` (`product_id`,`issue`)

Replace with

UNIQUE KEY `product_issue` (`product_id`,`issue`(191))

This will limit the length of the issue column to 191 chars. Each character takes 4 bytes to store in utf8mb4 character set. The limit 191 is found by trying with default length for issue field 200, then reducing it until the SQL gets created properly. The calculation is 191 * 4 + length of id column < 767 The correct solution is to use newer MySQL versions like MySQL 5.7, which allows length up to 3072 bytes.

December 19, 2021

Disable MySQL Replication on Slave

To disable MySQL Replication on the Slave server, first, edit MySQL configuration file, usually in /etc/mysql folder, remove entries like

server-id = 2
log_bin                 = /var/log/mysql/mariadb-bin
log_bin_index           = /var/log/mysql/mariadb-bin.index
relay_log = /var/lib/mysql/relay-bin
relay_log_index = /var/lib/mysql/relay-bin.index
binlog_format=row

Also, remove any entries starting with

master- 
replicate-

Stop slave with command

STOP SLAVE;

Stop replication with

RESET SLAVE;

Stop MySQL

systemctl stop mysql

Remove relay logs

cd /var/lib/mysql
rm -f master.info relay-*`

Start MySQL server

systemctl start mysql

You may need to re-enable events that are disabled on Slave.

select * from information_schema.events where status = 'SLAVESIDE_DISABLED';

Enable each disabled event with

alter event  enable;

Example

MariaDB [(none)]> show  slave status;
+----------------+---------------+-------------+-------------+---------------+--------------------+---------------------+------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+----------------+--------------------+------------+-------------+-------------------------+-----------------------------+---------------+-----------+---------------------+-------------------------+
| Slave_IO_State | Master_Host   | Master_User | Master_Port | Connect_Retry | Master_Log_File    | Read_Master_Log_Pos | Relay_Log_File   | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_SSL_Crl | Master_SSL_Crlpath | Using_Gtid | Gtid_IO_Pos | Replicate_Do_Domain_Ids | Replicate_Ignore_Domain_Ids | Parallel_Mode | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State |
+----------------+---------------+-------------+-------------+---------------+--------------------+---------------------+------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+----------------+--------------------+------------+-------------+-------------------------+-----------------------------+---------------+-----------+---------------------+-------------------------+
|                | 144.76.16.199 | replication |        3306 |            10 | mariadb-bin.000134 |           715913856 | relay-bin.000014 |     617924553 | mariadb-bin.000028    | No               | No                |                 |                     |                    |                        |                         |                             |          0 |            |            0 |           818117404 |               0 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                  NULL | No                            |             0 |               |              0 |                |                             |                0 |                |                    | No         |             |                         |                             | conservative  |         0 |                NULL |                         |
+----------------+---------------+-------------+-------------+---------------+--------------------+---------------------+------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+----------------+--------------------+------------+-------------+-------------------------+-----------------------------+---------------+-----------+---------------------+-------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> RESET SLAVE;
Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> Bye
root@ArthAppServer1:/etc/mysql# systemctl restart mysql
root@ArthAppServer1:/etc/mysql# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.2.31-MariaDB-10.2.31+maria~xenial mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show slave status;
Empty set (0.00 sec)

MariaDB [(none)]>

November 3, 2021

Export MySQL Database table as an XML Document
To export a MySQL database table as an XML document, you can use mysqldump command like
```
mysqldump DB_NAME TABLE_NAME --xml
```
phpMyAdmin table export feature also gives the option to save Data in multiple formats.

Back to MySQL
September 27, 2021

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

When restoring a MySQL database, I get the following error

root@localhost:~# mysql -u sok_user -p'serverok123' sok_db < parkingcupid.sql
ERROR 1114 (HY000) at line 2137: The table 'field_data_field_monthly_price' is full
root@localhost:~

How to fix ERROR 1114 (HY000) table is full?

First, check if the disk on the server is full.

df -h

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

vi /etc/mysql/mariadb.conf.d/50-server.cnf

Under [mysqld] section, add

tmp_table_size=256M
max_heap_table_size=256M

Restart MySQL

systemctl restart mysql

After this change MySQL database restore worked without giving any error.

If the above solution did not fix your problem, check the following.

Check Mysql variables innodb_data_file_path

MariaDB [(none)]> select @@innodb_data_file_path;
+-------------------------+
| @@innodb_data_file_path |
+-------------------------+
| ibdata1:12M:autoextend  |
+-------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]>

If innodb_data_file_path variable have a max value set, you should update it. In this case, we don't have a max value.

Verify innodb_file_per_table is set to 1.

MariaDB [(none)]> select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]>

See MySQL

September 4, 2021

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

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

MariaDB [thrkhztbpt]> drop table users;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
MariaDB [thrkhztbpt]>

To fix the error, run

SET FOREIGN_KEY_CHECKS=0;

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

SET FOREIGN_KEY_CHECKS=1;

Example

MariaDB [thrkhztbpt]> drop table users;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
MariaDB [thrkhztbpt]> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [thrkhztbpt]> drop table users;
Query OK, 0 rows affected (0.00 sec)

MariaDB [thrkhztbpt]> 
MariaDB [thrkhztbpt]> SET FOREIGN_KEY_CHECKS=1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [thrkhztbpt]>

August 28, 2021

Category: MySQL

Method 1

Method 2: Disable Password Validation Component

Change a user password

How to fix ERROR 1114 (HY000) table is full?