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:

• https://stackoverflow.com/questions/1270944/mysql-innodb-not-releasing-disk-space-after-deleting-data-rows-from-table
• https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html

ATTENTION

Big delete operation may corrupt MySQL/MariaDB table!

PHP: Datetime format for MySQL

<?php

$MyDate=date('Y-m-d');
$MyDateTime=date('Y-m-d H:i:s');

?>

Delphi: TFDConnection

• Create FDGUIxWaitCursor1 and change ScreenCursor
• Add FireDAC.Dapt into Uses list

How to Start Conection

  FDConnection1.ResourceOptions.MacroCreate:=False; // '&', '!' characters fails to execute correctly
  FDConnection1.ResourceOptions.MacroExpand:=False; // '&', '!' characters fails to execute correctly
  FDConnection1.Params.Clear;
  FDConnection1.Params.Add('DriverID=SQLite');
  FDConnection1.Params.Add('Database='+mydir+'database.db');
  try
    FDConnection1.Connected := true;
  except
    on E: EDatabaseError do
      ShowMessage('Exception raised with message' + E.Message);
  end;

CRUD Operations

function Escape(Str: string):string;
begin
  Result:=QuotedStr(Str);
end;
 
var
  results: TDataSet;
  query: String;
  myid: string;
  mystart: int64;
begin
  query := 'select ID, START from FILES where FILENAME='+ESCAPE(filename)+' LIMIT 1;';
  try
    FDConnection1.ExecSQL(query, nil, results);
  except
    on E: Exception do
      showmessage('Exception raised with message: ' + E.Message);
  end;
 
  if not results.IsEmpty then
  begin
    results.First;
    while not results.Eof do
    begin
      myid:=results.FieldByName('ID').AsString;
      mystart:=results.FieldByName('START ').AsInteger;
      //mystart:=results.Fields[1].AsInteger;
      results.Next;
    end;
  end;
end;
 
var
  query: String;
begin
  query := 'insert into FILES (FILENAME,START) values ('+Escape(FileName)+','+Escape(mystart.ToString)+');';
  try
    FDConnection1.ExecSQL(query);
  except
    on E: Exception do
      showmessage('Exception raised with message: ' + E.Message);
  end;
 
  query := 'update FILES SET FINISH='+Escape(myfinish.ToString)+' where ID='+Escape(myid)+';';
  try
    FDConnection1.ExecSQL(query);
  except
    on E: Exception do
      showmessage('Exception raised with message: ' + E.Message);
  end;
end;

If you don’t know return query results set or not, there is workaround – TFDquery.OpenOrExecute. But internal exception when query does not return result set.

Delphi: TSQLConnection

Requires sqlite3.dll

How to Start Conection

  SQLConnection1.Params.Add('Database='+mydir+'database.db');
  try
    SQLConnection1.Connected := true;
  except
    on E: EDatabaseError do
      ShowMessage('Exception raised with message' + E.Message);
  end;

CRUD Operations

function Escape(Str: string):string;
begin
  Result:=QuotedStr(Str);
end;

var
  results: TDataSet;
  query: String;
  myid: string;
  mystart: int64;
begin
  query := 'select ID, START from FILES where FILENAME='+ESCAPE(filename)+' LIMIT 1;';
  try
    SQLConnection1.Execute(query, nil, results);
  except
    on E: Exception do
      showmessage('Exception raised with message: ' + E.Message);
  end;

  if not results.IsEmpty then
  begin
    results.First;
    while not results.Eof do
    begin
      myid:=results.FieldByName('ID').AsString;
      mystart:=results.FieldByName('START ').AsInteger;
      //mystart:=results.Fields[1].AsInteger;
      results.Next;
    end;
  end;
end;

var
  query: String;
begin
  query := 'insert into FILES (FILENAME,START) values ('+Escape(FileName)+','+Escape(mystart.ToString)+');';
  try
    Form1.SQLConnection1.ExecuteDirect(query);
  except
    on E: Exception do
      showmessage('Exception raised with message: ' + E.Message);
  end;

  query := 'update FILES SET FINISH='+Escape(myfinish.ToString)+' where ID='+Escape(myid)+';';
  try
    Form1.SQLConnection1.ExecuteDirect(query);
  except
    on E: Exception do
      showmessage('Exception raised with message: ' + E.Message);
  end;
end;

Delphi: In-Memory SQLite

DriverID=SQLite
Database=:memory:

begin
  SQLConnection1.Params.Add('Database=:memory:');
end;

More Information: https://www.sqlite.org/inmemorydb.html

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

• bind-address: for connection from outside
• sql_mode: default value includes STRICT_TRANS_TABLES
• slow_query: default value is disabled
• skip-host-cache and skip-name-resolve: solves too much error in log (…has been resolved to the host name…)

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 version	Default sql_mode setting
MariaDB 10.2.4	STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO , NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION
MariaDB 10.1.7	NO_ENGINE_SUBSTITUTION, NO_AUTO_CREATE_USER
<= MariaDB 10.1.6	No 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"