Category: MySQL

Install MariaDB 10.3 on CentOS 7
MariaDB is an Open Source MySQL drop-in replacement. MariaDB provides a repository for various operating systems at

https://mariadb.org/download/?tab=repo-config

To install MariaDB 10.3 on CentOS 7, create a file
```
vi /etc/yum.repos.d/MariaDB.repo
```
Add
```
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
```
Install the MariaDB server and client with
```
yum install MariaDB-server MariaDB-client
```
Enable MariaDB to start on boot
```
systemctl enable mariadb
```
Start MariaDB
```
systemctl start mariadb
```
November 22, 2019

MySQL ERROR Access denied you need SUPER privilege for this operation

WHen restoring a MySQL database, i get following error

root@ns3043014:~# mysql -u NEW_DB_USER -p'PASSOWRD_HERE' DB_NAME < BACKUP_FILE.sql
ERROR 1227 (42000) at line 4382: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
root@ns3043014:~#

I opened the file in vim editor. On checking line 4382, i found following

/*!50013 DEFINER=`OLD_DB_USER`@`localhost` SQL SECURITY DEFINER */

This is because some stored procedures use old database user as definer. To fix you need to find and replace all entry for old username with new username.

In vim, i can run

:%s/OLD_DB_USER/NEW_DB_USER/g

Or delete the DEFINER line from SQL file.

sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' BACKUP_FILE.sql > BACKUP_FILE-modified.sql

sed -i old 's/\DEFINER\=`[^`]*`@`[^`]*`//g' BACKUP_FILE.sql

Now restore BACKUP_FILE-modified.sql with

mysql -u NEW_DB_USER -p'PASSOWRD_HERE' DB_NAME < BACKUP_FILE-modified.sql

November 5, 2019

MySQL Recovering after a crash using tc.log

After MySQL upgrade from MraiaDB 10.1 to 10.3, MySQL failed to start. On checking log, found

root@localhost:/var/log/mysql# cat error.log
2019-09-23  6:30:10 0 [Note] InnoDB: Using Linux native AIO
2019-09-23  6:30:10 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-09-23  6:30:10 0 [Note] InnoDB: Uses event mutexes
2019-09-23  6:30:10 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-09-23  6:30:10 0 [Note] InnoDB: Number of pools: 1
2019-09-23  6:30:10 0 [Note] InnoDB: Using SSE2 crc32 instructions
2019-09-23  6:30:10 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2019-09-23  6:30:10 0 [Note] InnoDB: Completed initialization of buffer pool
2019-09-23  6:30:10 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2019-09-23  6:30:10 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2019-09-23  6:30:10 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-09-23  6:30:10 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-09-23  6:30:10 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2019-09-23  6:30:10 0 [Note] InnoDB: Waiting for purge to start
2019-09-23  6:30:10 0 [Note] InnoDB: 10.3.17 started; log sequence number 682398229; transaction id 1563655
2019-09-23  6:30:10 0 [Note] Plugin 'FEEDBACK' is disabled.
2019-09-23  6:30:10 0 [Note] Recovering after a crash using tc.log
2019-09-23  6:30:10 0 [ERROR] Can't init tc log
2019-09-23  6:30:10 0 [ERROR] Aborting

root@localhost:/var/log/mysql#

On /var/lib/mysql folder, there is a tc.log file with 0 size. The error is solved by removing this file.

mv /var/lib/mysql/tc.log /root/

September 23, 2019

MariaDB DEFAULT NULL ERROR 1064 (42000)

When trying to restore a MySQL databse bakcup taken on MySQL 5.7 server to MairaDB 10.1 server i get following error.

root@server:~# mysql -u vcon_user -p vcon_sbc < vcon_sbc.sql
Enter password: 
ERROR 1064 (42000) at line 287: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` ti' at line 3
root@server:~#

Source server

root@ip-172-31-16-209:~# mysql --version
mysql  Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using  EditLine wrapper
root@ip-172-31-16-209:~#

Destination server

root@server:~# mysql --version
mysql  Ver 15.1 Distrib 10.1.38-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
root@server:~#

I opend the SQL file in editor and checked line 287, i run the SQL in mysql, that resulted in error

MariaDB [test]> CREATE TABLE `current_job_status_reports` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `jobs` json DEFAULT NULL,
    ->   `created_at` timestamp NULL DEFAULT NULL,
    ->   `updated_at` timestamp NULL DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` ti' at line 3
MariaDB [test]>

I run the command on a MariaDB 10.2 server and it worked.

To fix the problem, upgrade MariaDB to version 10.2 or above. In this case, i went with MariaDB 10.04 and it worked fine.

July 27, 2019

Install MySQL as Service in Windows
When you manually install MySQL from zip, no MySQL service get created.

To run MySQL Server as a service in windows, go to the folder where “mysqld.exe” is located and run following command
```
mysqld.exe install
```
If you want a differnt name for service, run
```
mysqld.exe install SERVICE_NAME
```
You can delete service with command
```
sc delete SERVICE_NAME
```
March 3, 2019

MySQL 8 apt Error The following signatures were invalid

When trying to install MySQL 8 on Ubuntu 18.04, i get following error

root@ssdnodes-45790:~# apt update
Ign:1 http://download.webmin.com/download/repository sarge InRelease
Hit:2 http://download.webmin.com/download/repository sarge Release                  
Get:3 http://repo.mysql.com/apt/ubuntu bionic InRelease [16.9 kB]                                              
Hit:5 http://archive.ubuntu.com/ubuntu cosmic InRelease                                                            
Hit:6 http://archive.ubuntu.com/ubuntu cosmic-security InRelease
Err:3 http://repo.mysql.com/apt/ubuntu bionic InRelease
  The following signatures were invalid: EXPKEYSIG 8C718D3B5072E1F5 MySQL Release Engineering 
Hit:7 http://archive.ubuntu.com/ubuntu cosmic-updates InRelease
Reading package lists... Done                     
W: GPG error: http://repo.mysql.com/apt/ubuntu bionic InRelease: The following signatures were invalid: EXPKEYSIG 8C718D3B5072E1F5 MySQL Release Engineering 
E: The repository 'http://repo.mysql.com/apt/ubuntu bionic InRelease' is not signed.
N: Updating from such a repository can't be done securely, and is therefore disabled by default.
N: See apt-secure(8) manpage for repository creation and user configuration details.
root@ssdnodes-45790:~#

To fix this error, run

apt-key adv --keyserver keys.gnupg.net --recv-keys 8C718D3B5072E1F5

Now apt update will work.

To list keys, run

root@ssdnodes-45790:~# apt-key list
/etc/apt/trusted.gpg
--------------------
pub   dsa1024 2003-02-03 [SCA] [expires: 2022-02-16]
      A4A9 4068 76FC BD3C 4567  70C8 8C71 8D3B 5072 E1F5
uid           [ unknown] MySQL Release Engineering 

pub   dsa1024 2002-02-28 [SCA]
      1719 003A CE3E 5A41 E2DE  70DF D97A 3AE9 11F6 3C51
uid           [ unknown] Jamie Cameron 
sub   elg1024 2002-02-28 [E]

/etc/apt/trusted.gpg.d/ondrej_ubuntu_php.gpg
--------------------------------------------
pub   rsa1024 2009-01-26 [SC]
      14AA 40EC 0831 7567 56D7  F66C 4F4E A0AA E526 7A6C
uid           [ unknown] Launchpad PPA for Ondřej Surý

/etc/apt/trusted.gpg.d/ubuntu-keyring-2012-archive.gpg
------------------------------------------------------
pub   rsa4096 2012-05-11 [SC]
      790B C727 7767 219C 42C8  6F93 3B4F E6AC C0B2 1F32
uid           [ unknown] Ubuntu Archive Automatic Signing Key (2012) 

/etc/apt/trusted.gpg.d/ubuntu-keyring-2012-cdimage.gpg
------------------------------------------------------
pub   rsa4096 2012-05-11 [SC]
      8439 38DF 228D 22F7 B374  2BC0 D94A A3F0 EFE2 1092
uid           [ unknown] Ubuntu CD Image Automatic Signing Key (2012) 

/etc/apt/trusted.gpg.d/ubuntu-keyring-2018-archive.gpg
------------------------------------------------------
pub   rsa4096 2018-09-17 [SC]
      F6EC B376 2474 EDA9 D21B  7022 8719 20D1 991B C93C
uid           [ unknown] Ubuntu Archive Automatic Signing Key (2018) 

root@ssdnodes-45790:~#

February 18, 2019

MySQL Got error 24 Too many open files
For ubuntu, see Ubuntu 18.04 MariaDB 10.2 Too many open files

On taking MySQL backup with mysqldump, i get following error
```
mysqldump: Got error: 1030: "Got error 24 "Too many open files" from storage engine MyISAM" when using LOCK TABLES
mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_f68_2.MAD' (Errcode: 24 "Too many open files")' when trying to dump tablespaces
```
This is because open_files_limit limit in MySQL. To verify login to MySQL server and run
```
select @@open_files_limit;
```
This will give you current value of open_files_limit.

To increase, edit /etc/my.cnf file, add
```
vi /etc/my.cnf
```
Add under [mysqld] section
```
open_files_limit = 5000
```
Now restart MySQL server
```
systemctl restart mysql
```
January 8, 2019
MySQL Out of resources when opening file
On a MySQL server got the error
```
Out of resources when opening file '/tmp/#sql_318d_0.MAD' (Errcode: 24 "Too many open files")
```
This is because number of files allowed to open by MySQL is set to too low. To view current settings, in MySQL command prompt, run
```
select @@open_files_limit;
```
Or
```
show variables like "open%";
```
To increase the value, edit MySQL configuration file, this normally located in /etc/my.cnf, on some servers, it will be on /etc/mysql/my.cnf, add following under [mysqld] section.
```
open_files_limit = 5000
```
Now restart MySQL
```
systemctl restart mysql
```
Verify it is changed with command
```
select @@open_files_limit;
```
January 1, 2019
mysqldump packet bigger than max_allowed_packet
When backing up a MySQL database using mysqldump command, got following error
```
[root@vps189 ~]# mysqldump sok_main > sok_main.sql
mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' when dumping table `wp_options` at row: 7068
[root@vps189 ~]#
```
To fix, you need to edit MySQL config file
```
vi /etc/my.cnf
```
Add following under [mysqld] section
```
max_allowed_packet=2G
```
Now restart MySQL with
```
systemctl restart mysql
```
You can verify the value is changed by running following SQL command in MySQL prompt
```
show variables like 'max_allowed_packet';
```
Or my running following command in command line
```
mysqladmin variables | grep max_allowed_packet
```
On a VPS, editing /etc/my.cnf did not changed the value in MySQL for some reason. I got it fixed by running mysqldump with –max_allowed_packet=2G option.
```
mysqldump --max_allowed_packet=2G DB_NAME > DB_NAME.sql
```
January 1, 2019

Reset MySQL 5.7 root password on Ubuntu 16.04

First stop MySQL with command

service mysql stop

Now run

mysqld_safe --skip-grant-tables

On Ubuntu 16.04 server with MySQL 5.7, when i run, i get following error.

root@sok-06:~# mysqld_safe --skip-grant-tables
2018-12-29T06:13:26.191918Z mysqld_safe Logging to syslog.
2018-12-29T06:13:26.195961Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2018-12-29T06:13:26.199396Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.
root@sok-06:~#

To fix, this error, create a the folder and change ownership to user mysql

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

Now start a new terminal, login to MySQL with command

mysql -u root -p

You can press enter for password, you will be logged in with no password.

To reset MySQL root password, run

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'YOUR_NEW_MYSQL_PW';
exit

Now kill the MySQL process with command

killall mysqld

Start MySQL normally with

service mysql start

December 29, 2018

Extract Backup of one database from mysqldump all databases
When you backup MySQL Server with command
```
mysqldump  --all-databases > all-db.sql
```
You get a MySQL backup file containing all databases.

If you want to generate MySQL backup for single database from this file, you can run
```
sed -n '/^-- Current Database: `DB_NAME`/,/^-- Current Database: `/p' all-db.sql > DB_NAME.sql
```
See mysqldump
August 12, 2018
MySQL Repair

Can’t open and lock privilege tables: Table ‘./mysql/db’ is marked as crashed
MySQL Can’t create new tempfile
mysqlcheck
How to Repair MyISAM Tables

May 2, 2018