Category: mySQL

How to calculate innodb_buffer_pool_size value

2160  admin  

Run to the following command to start calculation:

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

Example:

mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 60295069
Server version: 10.3.34-MariaDB MariaDB Server

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

… Read the rest

How to check if JSON_ARRAYAGG supported

2119  admin  
(last edited )

Run the following simple query

with t1 as ( select 0 as a ) select version(), JSON_ARRAYAGG(a) as j from t1 where a > 0;

Example:

MariaDB [(none)]> with t1 as ( select 0 as a ) select version(), JSON_ARRAYAGG(a) as j from t1 where a > 0;
+-----------------+------+
| version() | j |
+-----------------+------+
| 10.5.13-MariaDB | NULL |
+-----------------+------+
1 row in set (0.002 sec)

If it doesn’t support you should get:

MariaDB [(none)]> with t1 as ( select 0 as a ) select version(), JSON_ARRAYAGG(a) as j from t1 where a > 0;
ERROR 1305 (42000): FUNCTION JSON_ARRAYAGG does not exist

 

Source: https://mariadb.com/kb/en/json_arrayagg/… Read the rest

How to fix a login issue after WordPress Multisite split

2053  admin  

The slave installation will lose users

wp user list

+—-+————+————–+————+—————–+——-+
| ID | user_login | display_name | user_email | user_registered | roles |
+—-+————+————–+————+—————–+——-+
+—-+————+————–+————+—————–+——-+

And you won’t be able to create new admin user because you don’t have appropriate MySQL tables

wp user create admin [email protected]
… Read the rest

How to enable Slow Query Log

2043  admin  
(last edited )

Append into /etc/my.cnf the following:

slow_query_log = 1
slow-query_log_file = /var/log/mysql-slow.log
long_query_time = 2

Create /var/log/mysql-slow.log log file and set it as mysql user:

touch /var/log/mysql-slow.log
chown mysql:mysql /var/log/mysql-slow.log

Restart MySQL server:

/scripts/restartsrv_mysql

Monitor the logs using tail command:

tail -f /var/log/mysql-slow.log
… Read the rest

Mysqldump error: The user specified as a definer does not exist

2039  admin  
(last edited )

Error:

mysqldump myuser_db > myuser_db.sql

mysqldump: Got error: 1449: “The user specified as a definer (‘user_db’@’localhost’) does not exist” when using LOCK TABLES

To pass this error try to use –single-transaction

mysqldump --single-transaction myuser_db > myuser_db.sql

Should be good now

Source: https://stackoverflow.com/questions/26583103/mysqldump-got-error-1449/26583311… Read the rest

How to fix Unknown collation utf8mb4_unicode_520_ci error while importing data from an sql dump

2019  admin  
(last edited )

Error:

ERROR 1273 (HY000) at line 849: Unknown collation: ‘utf8mb4_unicode_520_ci’

To fix simply replace utf8mb4_unicode_520_ci to utf8mb4_unicode_ci inside your SQL dump

Example:

sed -i 's/utf8mb4_unicode_520_ci/utf8mb4_unicode_ci/g' yoursqldumphere.sql

or

replace 'utf8mb4_unicode_520_ci' 'utf8mb4_unicode_ci' -- yoursqldumphere.sql
… Read the rest

How to reset MODX admin password over MySQL cli

2004  admin  

You need to run the following query inside your MySQL cli

UPDATE modx_users SET hash_class = 'hashing.modMD5', password = MD5('3f8c369a185a95cfd198e5e') WHERE username = 'admin';

Note, update mysql table prefix, password, username to yours.

Source: https://docs.modx.com/3.x/en/building-sites/client-proofing/security/troubleshooting-security/resetting-a-user-password-manually… Read the rest

How to reset MySQL root password

1909  admin  

Stop MySQL server

service mysqld stop

Start in safe mode

mysqld_safe --skip-grant-tables &

Login mysql server

mysql

Reset your root password

UPDATE mysql.user SET Password=PASSWORD('strong-password-here') WHERE User='root';
FLUSH PRIVILEGES;
exit;

Shutdown your mysql server

mysqladmin -u root -p shutdown

Start your mysql server again

service mysqld start
… Read the rest