MariaDB/MySQL - Fix error "Unknown/unsupported storage engine: InnoDB" by using recover mode

Although the InnoDB storage engine has mechanisms to preserve data, problems such as power failure or sudden restart can still cause errors in MySQL/MariaDB data. These errors can make the system unable to boot, unable to restore the normal data state. Some of the error messages we often encounter are listed below.

[Note] InnoDB: Starting shutdown...
[ERROR] InnoDB: Database was not shut down normally!
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[Note] Plugin 'FEEDBACK' is disabled.
[ERROR] InnoDB: Starting crash recovery from checkpoint LSN=1637690
[ERROR] Unknown/unsupported storage engine: InnoDB
[ERROR] Aborting

Note: Some crashes are caused by other causes but also return the error message "Unknown/unsupported storage engine: InnoDB". In that case, there will be more detailed information in the MySQL log to help you find the cause and handle it. As for the cases where the service can't tell why InnoDB is unsupported or unrecoverable, most of the time, data errors are caused. You can handle that case according to the instructions in this article.

To use this mode is very simple. You just need to specify the value innodb_force_recovery in the installation file of MySQL or MariaDB. Then restart the service. MySQL will restart and try to use recovery mode to recover the corrupted data.

[mysqld]
innodb_force_recovery = 1

MySQL has a total of 6 recovery modes in ascending severity. You often choose to get the most suitable mode for your system. Please try again with a higher level if the error is not resolved.

1 (SRV_FORCE_IGNORE_CORRUPT)
Lets the server run even if it detects a corrupt page. Tries to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.

2 (SRV_FORCE_NO_BACKGROUND)
Prevents the master thread and any purge threads from running. If an unexpected exit would occur during the purge operation, this recovery value prevents it.

3 (SRV_FORCE_NO_TRX_UNDO)
Does not run transaction rollbacks after crash recovery.

4 (SRV_FORCE_NO_IBUF_MERGE)
Prevents insert buffer merge operations. If they would cause a crash, does not do them. Does not calculate table statistics. This value can permanently corrupt data files. After using this value, be prepared to drop and recreate all secondary indexes. Sets InnoDB to read-only.

5 (SRV_FORCE_NO_UNDO_LOG_SCAN)
Does not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed. This value can permanently corrupt data files. Sets InnoDB to read-only.

6 (SRV_FORCE_NO_LOG_REDO)
Does not do the redo log roll-forward in connection with recovery. This value can permanently corrupt data files. Leaves database pages in an obsolete state, which in turn may introduce more corruption into B-trees and other database structures. Sets InnoDB to read-only.

Finally, after troubleshooting, restore the "innodb_force_recovery" configuration to level 0 and then restart the database service to keep the system working properly.

Also note that this is only a workaround, it may also fail if the data is badly corrupted. Our advice remains that every database system should always have a mechanism to automatically back up data.

Read More

Ubuntu - MySQL Can't set password for root account, even though all command are successful

After a fresh install of MySQL or MariaDB on an Ubuntu server, you can run the "mysql" command line without being asked for your password, even if you have successfully changed the password. While you still cannot access root account from other software like Navicat, php ... the error encountered is "Access denied for user 'root'@'localhost' (using password: YES)"

What happened ?

you can run the "mysql" command without any password because by default, the root account is configured to login via the AUTH_SOCKET plugin. So MySQL Command-Line Client can always connect successfully without being asked for a password while other software can't connect even if the correct password is entered.

Security issue?

In a way, the mysql root account should only be accessed from the server, and a person who already has root privileges with Ubuntu can obviously optionally reset the mysql root account password. So this default configuration should not be a problem.

However, if you are a strict person or are familiar with other operating systems like Centos, you will be very uncomfortable with this configuration. If so, bring it back to the same as mysql on Centos by following the steps below.

Solving problems

Access the MySQL command-line. 

mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 8

Server version: 8.0.21-0ubuntu20.0 (Ubuntu)

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

mysql>

Switch to the 'mysql' database

use mysql;

Verify current status

select Host,User,authentication_string,plugin from mysql.user;

You can see: The MySQL ROOT account has no password configured and using the authentication plugin named auth_socket.

Now configure the ROOT account to use mysql_native_password plugin

ALTER USER root@localhost IDENTIFIED WITH mysql_native_password;

Set new password for root account

ALTER USER root@localhost IDENTIFIED BY 'newpassword';

Flush privileges

FLUSH PRIVILEGES;

exit;

Done

Problem solved !

Read More

MySQL: Fix Error 2013 - Lost connection to MySQL server during query

Case: You have successfully connected to a MySQL/MariaDB database. But when trying to import a large SQL file, an error occurred.

Error Code: 2013. Lost connection to MySQL server during query

Reason:

- Timeout due to slow connection or too much data (Not the server's fault)

- Memory allocated is not enough: the data fields are too large ....

Quick fix:

Increase memory for Mysql package.

Find config file

Eg:

Xampp Windows: C:\xampp\mysql\bin\my.ini

Linux: /etc/my.cnf

Edit file my.ini (my.cnf), find [mysqld] block, to change max_allowed_packet to a larger value (16, 32, or 128 M)

Eg:

[mysqld]
.....
max_allowed_packet=128M

Then restart MySQL

*Note: 

- If max_allowed_packet is not found, you can add it to your [mysqld] block yourself.

- Increasing memory capacity may affect the stability of MySQL server. So you can freely change it on your local computer, but if you are on the product server, please consider carefully. Another solution to importing large mysql files on the server is to use the command line.

Done ! Solved !

Read More

[PHP] Laravel Exception: Malformed UTF-8 characters, possibly incorrectly encoded

This error can be caused from many laravel modules such as Datatables, Json Output, Monolog ... So this is a very difficult error to analyze and fix. In this article we try to find some way to fix that bug in laravel (in the common cases).

Why did you get this error? and how to fix it?

* Case 1: In case of failure due to wrong configuration of database connection

The most common is when you connect to a second database. Developers often have a configuration that lacks the "charset" and "collation" parameters, causing the error as you see it:

Exception Message:↵↵Malformed UTF-8 characters, possibly incorrectly encoded

To fix it please configure the connection parameters are complete.

Eg:

'mysql' => array(
            'driver'    => 'mysql',
            'host'      => 'localhost',
            'database'  => 'database_name_1',
            'username'  => 'user_1',
            'password'  => 'password_1'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
	    ....
 ),
/*mysql2 if you use two database*/
'mysql2' => array(
            'driver'    => 'mysql',
            'host'      => 'hostForDB2',
            'database'  => 'database_name_2',
            'username'  => 'user_2',
            'password'  => 'password_2'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
	    ...
),

* Case 2: Regex Error - Caused by some regex function in route, url validate ....

PREG_BAD_UTF8_ERROR 'Malformed UTF-8 characters, possibly incorrectly encoded'

*Case 3: JSON Encode, Json output error - Caused by Monolog, Datatables Logs..

To Fix Case 2 & 3 you can try converting the input to utf-8, or remove non-utf-8 characters.

Eg1: Function to auto convert any string to utf-8 encoding

$newString = mb_convert_encoding($inputString, "UTF-8", "auto");

Eg2: fix utf-8 encoding for Datatables column

$datatable->editColumn('title', function(Post $node) {
            return mb_convert_encoding($node->title,"UTF-8", "auto");
        });

Done ! For any other questions you can leave them in the comment section at the end of this article. Thank you.

Read More

MySQL: Fix Error 1071 "Specified key was too long"

MySQL/MariaDB Error 1071 when try to import SQL File:

#1071 - Specified key was too long; max key length is 767 bytes

Sometime error is "max key length is 1000 bytes"

Reason:

From MySQL 5.6 and MariaDB 10.2.2 InnoDB File Format default is Barracudar. Barracudar is newer file type ,support many new format types (DYNAMIC and COMPRESSED). But in older version of MySQL or MariaDB default InnoDB File Format is still Antelope. So if you want to import a database using Barracudar(exported from newer version), you also need to change the current file format to Barracudar.

Solved:

To do that, run the following command in the Mysql command line (Root account is required):

SET GLOBAL innodb_file_format = Barracuda;
SET GLOBAL innodb_file_per_table = on;
SET GLOBAL innodb_default_row_format = dynamic;
SET GLOBAL innodb_large_prefix = 1;
SET GLOBAL innodb_file_format_max = Barracuda;
FLUSH PRIVILEGES;

Done ! The problem has been solved, you can try imoport SQL file again.

Tip: You can run Mysql command line from linux terminal or run Query in Navicat, PHPMyadmin ....

mysql -h localhost -u root -p

Refer: https://dev.mysql.com/doc/refman/5.6/en/innodb-file-format-enabling.html

Read More

Yii2: Get raw SQL Query from an Activerecord or a query builder

This tutorial show you how to dump the Sql Query from an Activerecord and more.
It can help us to debug Sql query in some case when yii debug is not work.

Example:

$listBooks = Books::find()->where('author=2')->all();

To get raw SQL query with all parameters included try:

$query = Books::find()->where('author=2');
echo $query->createCommand()->sql;
echo $query->createCommand()->getRawSql();

It not only works with Activerecord .

Refer : http://chris-backhouse.com/Yii2-Output-the-SQL-from-a-query-builder/1027

Read More

Yii2: Unknown Property Exception after adding column to table

Unknown Property – yii\base\UnknownPropertyException
Getting unknown property: app\models

What happens when you get an error Exception "Getting unknown property", after just added a field to the table in the database ?

1. Check your property name, was it correct?
2. Clear cache if you using cache. Yii2 will cache the table schema so if you change the table you need to clear the cache

Read More

[MySql] Reset MySQL root password on Linux

1. Login to server as root/su

2. Stop the MySQL service
service mysqld stop
* Note : Be carefully, all your application will be temporarily unable to use mysql.
3. Start MySQL Safe mode with skip grant tables option
mysqld_safe --skip-grant-tables & 
(press ctrl+C to exit, if required)

4. Start the MySQL service
service mysqld start

5. Log into the MySQL server without any password
mysql -u root -p mysql

6. Reset the password for ‘root’ user
UPDATE user SET password=PASSWORD('new-password') where user='root';

7. Flush privileges
flush privileges;

8. Stop MySQL Safe mode
killall mysqld

9. Start the MySQL service again
service mysqld start

10. Try to Log-in with the new password
mysql -u root -p 
<enter new password when prompted>

Read More

MySQL: Allowed to remote connect to MySQL server

MySQL Error : Host 'xxx.xx.xxx.xxx' is not allowed to connect to this MySQL server

How to fix it ?

STEP 1: Change mysql config :

vi /etc/mysql/my.cnf

Comment out following lines or edit to your client ip :

#bind-address           = 127.0.0.1
#skip-networking

Restart mysql server:

service mysql restart

STEP 2: Change GRANT privilege :

Login MySQL using command line .
Then run a command like below to grant access for user. Replace 'username' and 'password' with your username and password.

CREATE USER 'username'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;

* '%' mean you can remote access from any ip address, You can replace it with a specified ip address.

Then flush MySQL :

FLUSH PRIVILEGES;

If it's still not working. Let's check your server firewall (iptables ...) and client firewall (windows firewall, antivirus software)

Read More

PHP: Create big unique ID like Facebook or MongoDB

Most of web application using a relational database management system (MSQL, SQL ..etc) to store data as records. We need an unique id to use as a key Primary.
How to do it ?
* SQL AUTO INCREMENT a Field :
--> 1,2,3,4,5..... #anyone can count the number of your records and more

* PHP uniqid :
--> 541cf7f7d685e, 541cf7f7d685e, 541cf7f7d685e .....  #somewhat similar?

* Or use a custom function like this :

function gen_id() {
    return sprintf( '%04x%04x%04x%04x%04x%04x%04x%04x',
        // 32 bits for "time_low"
        mt_rand( 0, 0xffff ), mt_rand( 0, 0xffff ),

        // 16 bits for "time_mid"
        mt_rand( 0, 0xffff ),

        // 16 bits for "time_hi_and_version",
        // four most significant bits holds version number 4
        mt_rand( 0, 0x0fff ) | 0x4000,

        // 16 bits, 8 bits for "clk_seq_hi_res",
        // 8 bits for "clk_seq_low",
        // two most significant bits holds zero and one for variant DCE1.1
        mt_rand( 0, 0x3fff ) | 0x8000,

        // 48 bits for "node"
        mt_rand( 0, 0xffff ), mt_rand( 0, 0xffff ), mt_rand( 0, 0xffff )
    );
}

Result : 6800cd499f25434aac48c0bb51f46307, caa02c4831194690ad8458c3a4e07a0c, 7488a4c459d64f8dbacd2b79f44ed4bf ......

- differences
- professional (like facebook, google, MongoDB ...)
- high performance
- unrestricted

Reference : PHP uuid lib

Read More