MariaDB: InnoDB Corruption

Error Message (daemon.log)

[ERROR] InnoDB: Corruption of an index tree: table DatabaseName.CorruptedTableName

How to Fix Quickly

Edit file: /etc/mysql/my.cnf

Add line:

[mysqld]
innodb_force_recovery = 1

MySQL/MariaDB should work perfectly fine now.

What to do next

  1. Drop CorruptedTable
  2. Recreate CorruptedTable
  3. Disable innodb_force_recovery

MySQL: Big Delete

MySQL InnoDB doesn’t release disk space after deleting data rows from table!

  • If you have innodb_file_per_table = 0, the only option is to dump ALL tables, remove ibdata*, restart, and reload. That is rarely worth the effort and time.
  • If you have innodb_file_per_table = 1, OPTIMIZE TABLE will give space back to the OS, but you do need enough disk space for two copies of the table during the action.

Debian MariaDB database location: /var/lib/mysql

Read more:

ATTENTION

Big delete operation may corrupt MySQL/MariaDB table!

PHP & MySQL: Emoji Problem

Emoji characters are 4 bytes!

MySQL by default only uses a three byte encoding and so values in the four byte range (eg. Asian characters and Emojis) can not be stored. MySQL does provide full four byte UTF-8 support, but it requires certain database settings to be configured.

‘utf8_unicode_ci’ was the recommended collation. We now recommend using ‘utf8mb4_unicode_ci’ which supports four byte characters (utf8_unicode_ci only supports three).

If you want to save emojis in MySQL/MariaDB database:

  • Database and table’s charset must be: utf8mb4_unicode_ci
  • Connection also needs to be utf8mb4 not utf8 for it to work

HestiaCP MariaDB Configuration

Add the lines below to the file: /etc/mysql/my.cnf

Under [mysqld]

sql_mode="ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
slow_query_log
slow_query_log_file=/var/log/mysql/mariadb-slow.log
skip-host-cache
skip-name-resolve

How to Back Up and Restore a MySQL/MariaDB Database

Dump to SQL File

sudo mysqldump -u username -p databasename > /root/mybackup.sql

Transfer to Remote Server

Using SSH Password:

scp /root/mybackup.sql 123.123.123.123:/root/mybackup.sql

Using KEY:

scp -i id_rsa /root/mybackup.sql 123.123.123.123:/root/mybackup.sql

Import SQL File

mysql -u username -p databasename < /root/mybackup.sql

MariaDB SQL_MODE

Default SQL_MODE Settings

From versionDefault sql_mode setting
MariaDB 10.2.4STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO , NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION
MariaDB 10.1.7NO_ENGINE_SUBSTITUTION, NO_AUTO_CREATE_USER
<= MariaDB 10.1.6No value

Disable strict mode on MariaDB

STRICT_TRANS_TABLES leads INSERT problem if default value is not defined for table columns.

Edit the file:

/etc/mysql/my.cnf

Add line: (under [mysqld])

sql_mode="ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Allow Remote Access to MySQL/MariaDB

1. BIND-ADDRESS

Edit the file:

/etc/mysql/my.cnf

Add line: (under [mysqld]) (USE NEW WAY if this doesn’t work)

bind-address = 0.0.0.0

New Way (WORKS): Add the following lines at the end of your my.cnf

[mysqld]
skip-networking=0
skip-bind-address

How to check whether MySQL is allowing remote access:

netstat -ant | grep 3306

LOCALHOST ONLY:

tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN

ALLOWS REMOTE ACCESS:

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN

2. FIREWALL

Allow firewall access to 3306 TCP port.

MySQL Unicode

utf8mb4_unicode_ci is based on the official Unicode rules for universal sorting and comparison, which sorts accurately in a wide range of languages.

utf8mb4_general_ci is a simplified set of sorting rules which aims to do as well as it can while taking many short-cuts designed to improve speed. It does not follow the Unicode rules and will result in undesirable sorting or comparison in some situations, such as when using particular languages or characters.