Resolve error 150 while restoring backup in MySQL Database

If you are getting error while importing or restoring the database dump like as mentioned below :

MySQL: Can’t create table ‘./dbname/data.frm’ (errno: 150)

This may come due to foreign key constraints defined in tables.  So If in backup file Referencing table is created before the Referenced table than this will throw error. So, in this case, you will need to make MySQL stop checking foreign key constraints while importing/restoring the backup.

So How to resolve it ? 

We can resolve it using below commands

SET FOREIGN_KEY_CHECKS=0;

You can also debug the issue by using below command if you have Process Privilege on the database.

SHOW ENGINE INNODB STATUS;

If you don’t have the process privilege than you will need to have one otherwise it will show you error like :

[SQL] SHOW ENGINE INNODB STATUS;
[Err] 1227 - Access denied; you need (at least one of) the PROCESS privilege(s) for this operation

Other reason can also be involved like:

  1. Storage engine types are different.
  2. All referenced keys MUST have either PRIMARY or UNIQUE index.
  3. Referencing Column MUST have identical data type to the Referenced column.
  4. Backup using the same constraint name which is already taken by other foreign keys.
  5. Column Collations Don’t Match.

 

 

 

What is utf8mb4 in MySQL?

MySql support two varities of utf-8 character and collation set are utf8mb3(only contains BMP characteres) and utf8mb4(utf-8 characters and supports supplementary characters)
In mysql 4.1 there is latin1 as a default character set and utf8mb3 is available as an option. But an optimization was chosen to limit utf-8 to 3bytes which support all modern languages.
But from mySQL 5.1(2010). Utf8 limit is started to support utf8 characters 4 bytes by using new utf8mb4.
And from now mySQL 8.1 is default character set. It improves the performance greatly and support almost every language even emojis too.

How to use character set utf8mb4 while creating table.

create table abc (a varchar(20)) character set utf8mb4;

To get the name of supported collation_name in your database.

SELECT collation_name FROM information_schema.collations WHERE collation_name LIKE ‘%cs’;

In the BMP, there are the Latin characters and symbols, transcriptions, other European characters and writing systems such as Greek and Cyrillic letters, African and Asian characters like Hiragana and Katakana, diacritical marks, Canadian syllables, Chinese, Japanese and Korean ideographs, symbols, and various other characters. In addition, space for private use of own-defined character is reserved in the BMP

MySQL: If and Else

In this article, we will talk about IF and Else control structure which will work inside the procedure and how an if function, which will even work in the simple query too,  explained below.

We can use If and else control structure in two ways in MySQL.

1. IF and ELSE control structure

If and Else control structure works only inside of a procedure. So if you are creating a procedure and you want to use if and else control structure in your stored procedure.

Syntax:  To use if and else

IF syntax :

IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] …
[ELSE statement_list] END IF

Example: To use If only

MySQL -> BEGIN
-> IF (userId <>”) && (userId <> 0) then
-> SELECT * FROM user_table where user_id  = userId;
-> END ;
-> END
In this case, this procedure will run the select statement only if userId is not equal to  empty string(“”) and 0

 Example: To use If, Else If and else, altogether

1
2
3
4
5
6
7
8
9
 BEGIN
IF (userType = "emp") then
select * from emp_table where user_id = userId;
ELSE IF (userType = "Admin")
select * from admin_table where user_id = userId;
ELSE
select * from user_table where user_id = userId;
END IF;
END

In this case, we are first checking the type of userType by comparing it with different values like emp and admin then and only we are providing the data from the table on the basis of userId.

2. IF Function in MySQL

Here we have one more important concept regarding If is the use of If Function. If Function can be used in a simple SQL query or inside a procedure. 

It is one of the most useful functions in MySQL, It will very useful when you want if and else like condition in the query like :

Let’s assume a table having a column name status which is having values in the form of ‘Y’ and ‘N’.

So if we run the query mentioned-below:

1
select status from emp_table;

it will return Y and N regarding all the user present in the table. but it will be not clear to some other people if they don’t know what status field store and how it is related to the user.

Now, if we write :

1
 select if(status = 'Y', 'active'  , 'not active') from emp_table;

Now it will return ‘active’  if status value is ‘Y’ and ‘not active’ if status value is ‘N’.

You can even use nested if function inside the query.

1
select if(status = 'Y', 'active'  , if(status = 'N','not active',"may be not active")) from emp_table;

Not only in selecting, you can use If Function in where clause also. For other details related to If Function click here

I hope you will now understand, how to use if and else control structure in MySQL. Please write a comment. It will help us to improve this article. Thanks!!