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.

 

 

 

SQL Injection

SQL injection is a most common area which a hacker can exploit and which proves to be disastrous for your website. So as a professional developer you must avoid code which is prone to SQL injection in any way.

Example of SQL Injection

User will input: 105 or 1=1
your query will become:
SELECT UserId, Name, Password FROM Users WHERE UserId = 105 or 1=1;
This will always return rows even if 105 is not present as a user-id in the database.

This can cause damage by manipulating data or deleting data to your website database.

To avoid SQL Injection there are many ways:-

  1. use prepared statements while interacting with your database whether it is select, insert, update or delete statement. If you want to execute any statement in the database then you must use a prepared statement. But your code execution time increase as a prepared statement is too slow in PHP.
  2.  basic rules using Strings in database
    • have to be added via a native prepared statement
      or
    • have to be enclosed in quotes
    • special characters (frankly – the very delimiting quotes) have to be escaped
    • proper client encoding has to be set
      or
    • maybe hex-encoded
  3. basic rules using Numbers in database
    • have to be added via a native prepared statement
      or
    • should be formatted to contain only numbers, a decimal delimiter, and a sign
  4. basic rules using Identifiers in database
    • have to be enclosed in backticks
    • special characters (frankly – the very delimiting backticks) have to be escaped
  5. basic rules using Operators and keywords in the database.
    • there are no special formatting rules for the keywords and operators besides the fact that they have to be legitimate SQL operators and keywords. So, they have to be whitelisted.
  6. To avoid SQL injection you can use mod security which is an open source web firewall in the database server.

 

These are some simple ways by which you can protect your database from SQL injection.

 

Concepts of insert and limit in MySQL

Here we will discuss some useful concepts on insert statement and limits, which will be proved to be a boon to your development career if you don’t know these yet.

Let’s name them first

  1. On duplicate update or insert update.
  2. Insert into select.
  3. Insert Ignore.
  4. Use of Limit.

On duplicate update

If you want to insert in the table and on the conflict of the primary key or unique index it will update the conflicting row instead of inserting that row.

Syntax :

insert into table1 set column1 = a, column2 = b on dulplicate update column2 = c;

Now here, this insert statement may look different what you have seen earlier. This insert statement trying to insert a row in table1 with the value of a and b into column column1 and column2 respectively.

Let’s understand this statement into deep:

 

For example:-  here column1 is defined as the primary key in table1.

Now if in table1  there is no row having the value “a” in column1. So this statement will insert a row in the table1.

Now if in table1 there is a row having the value “a” in column2. So this statement will update the row’s column2  value with “c” where the column1 value is “a”.

So if you want to insert a new row otherwise update that row on the conflict of the primary key or unique index.

Insert into select

If you want to insert into a table from the selection of rows using some condition from another. It will insert one or more row at a time based on the selected rows.

Syntax:

insert into table2 (column1, column2, column3)
select column1, column2, column3
from table1
where condition;

This statement will be very useful if want inserts some calculated data in the table based on the value of data from another table or you want backup a data from one table into some other.

Insert ignore

In MySQL Insert ignore statement is another kind of insert statement in which while inserting a new row if there is an old row having the same primary key or unique index as the new row then it will delete the old rows data and insert new row data.

Insert ignore statement is same as the insert statement except in place of “insert” we write “insert ignore”.

Syntax:

insert ignore into table2 (column1, column2, column3)
values (value1, value2,value3);

insert ignore into table2
values (value1, value2,value3);

insert ignore into table2 set column1 = value1, column2 = value2, column3 = value3;

insert into table2 (column1, column2, column3)
select column1, column2, column3
from table1
where condition;

This statement can also be used as an alternative to “on duplicate update”.

Use of Limit

In MySQL,  Limit is used to show the specific number of rows while using select statement in MySQL.

Syntax:

select [column1,column2,column3] from table1 where [condition] limit 10.

This statement will show only 10 rows from the top. For example, If a select statement using a certain condition giving you 1000 or more records you want only 10. Then this requirement can be fulfilled using Limit.

There is one more way to use Limit

Syntax:

select [column1,column2,column3] from table1 where [condition] limit 11,10.

Now, this statement will again show 10 records but not from the top, actually, it will show you records from 11th to 20th row from the select statement.  For example, If a select statement using a certain condition giving you 1000 or more records. Now, you want records from 11th to 20th then you need to use limit as mentioned above. This will be very useful if you want to show data of 1000 records in the chunk of 10.

read some more concepts on MySQL on link

 

 

 

 

 

 

 

 

 

 

 

unix_timestamp(), from_unixtime(), date_add(), date_sub() and interval concept in MySQL

Epoch time, Unixtime, Unix timestamp, all these are same. It is basically no. of seconds passed from 1 january 1970 00 hrs 00 min 00 sec which might cause problems on January 19, 2038. This is short description about Epoch time.

 

Epoch time for a date having 0 hrs 0 min 0 sec in MySQL using unix_timestamp()

mySQL -> select unix_timestamp(“2017-12-07”);

1512585000

Epoch time for a date having 23 hrs 59 min 59 sec in MySQL using unix_timestamp()

mySQL -> select unix_timestamp(“2017-12-07 23:59:59”);

1512671399

Readable date from unix_timestamp() function in MySQL

mySQL -> select from_unixtime(1512671399);

2017-12-07 23:59:59

Find Epoch time of  after 30 days in MySQL using unix_timestamp() and date_add()

mySQL -> select unix_timestamp(date_add(NOW(), INTERVAL 30 day));

Now here is string in ‘%Y-%m-%d %h:%i:%s’ format. Now you can play with it on the basis of your need. As a developer, you will need a concept to apply it on a broad level.

like  select unix_timestamp(date_add(“2017-12-05”, INTERVAL 30 day));

It will give us Epoch time of 04-01-2018 00:00:00 And if you want to get 04-01-2018 23:59:59

like  select unix_timestamp(date_add(“2017-12-05 23:59:59”, INTERVAL 30 day));

Find This mySQL -> select unix_timestamp(date_sub(NOW(), INTERVAL 30 day));

Here date in string format is given example, here it can we valid date it will work like a charm.

Now let’s talk about the optimization:-

In Database searching process, to search for the number is way faster than searching for the string because in searching we mostly do comparison and Integer comparison is faster than String.

So storing a date in DB is always good as integer format instead of a string.

So always store a date in integer format. It will help you to optimize your searching in DB when you will have millions of records.

But there is a drawback which I mentioned above that it will work till 19 Jan 2038. after that, it will start creating problem.

For many other details :-Date Function

 

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

How to assign a value in a variable in mysql

First lets take a look at how can we define a variable in mysql

To define a varible in mysql
it should start with ‘@’  like @{variable_name} and this ‘{variable_name}’, we can replace it with our variable name.

Now, how to assign a value in a variable in mysql. For this we have many ways to do that

1. Using keyword ‘SET’.

Example :-
mysql >  SET @a = 1;

2. Without using keyword ‘SET’ and using ‘:=’.

Example:-
mysql > @a:=1;

3. By using ‘SELECT’ statement.

Example:-
mysql > select 1 into @a;

Here @a is user defined variable and 1 is going to be assigned in @a.

Now how to get or select the value of @{variable_name}.

we can use select statement like

Example :-
mysql > select @a;

it will show the output and show the value of @a.

Now how to assign a value from a table in a variable.

For this we can use two statement like :-

1. @a := (select emp_name from employee where emp_id = 1);
2. select emp_name into @a from employee where emp_id = 1;

Always be careful emp_name must return single value otherwise it will throw you a error in this type statements.

 

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!!

Search special characters or text using MATCH and AGAINST in MySQL

This concept comes under FULLTEXT index. If your table has FULLTEXT index in the fields having field types of varchar, char, text. It is faster to load fields having large data set in comparison to those who don’t have FULLTEXT index.

You need to create FULLTEXT index to a field or multiple fields to load or search text from tables.

Create index of FULLTEXT while creation

Create table `table_name` ( `field_name` varchar(100) FULLTEXT, FULLTEXT idx(`field_name`)) engine=InnoDB;

For this, you need MySQL engine MyISAM and from 5.6 and up we can also use InnoDB.

You can also create index by using Alter command

ALTER TABLE ` table_name` ADD FULLTEXT ( ` field_name ` ) ;

Now how to use MATCH() and AGAINST() in FULLTEXT search.

SELECT * FROM `table_name` WHERE MATCH(`field_name`) against('search_key');

 

Formal Definition as mentioned in MySQL Manual :

MATCH (col1,col2,…) AGAINST (expr [search_modifier])

search_modifier:

{
IN NATURAL LANGUAGE MODE
| IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
| IN BOOLEAN MODE
| WITH QUERY EXPANSION
}

Date related functions of MySQL

A developer has to deal with these below mentioned date related function of MySQL. So let’s understand these function in details.

List of MySQL Date function

  • NOW(): It will give you the current date and time.

For example:

MySQL > select NOW();
      > 2017-02-25 00:52:57;
  • CURDATE(): It will give you current date.

For example :

MySQL > select CURDATE();
> 2017-02-25;
  • UNIX_TIMESTAMP() : It will give you the current date and time in epoch format

For example:

MySQL > select UNIX_TIMESTAMP();
> 1487964177
MySQL > select UNIX_TIMESTAMP('2017-02-25 00:52:57');
> 1487964177
  • FROM_UNIXTIME(): It will give you DATE and TIME from the epoch time format.

For example:

MySQL > SELECT FROM_UNIXTIME(1447430881);
> '2015-11-13 10:08:01'
MySQL > SELECT FROM_UNIXTIME(1447430881) + 0;
> 20151113100801
  • DATE_FORMAT() : It will let you decide in what format you want to show date.

For example :

MySQL > select DATE_FORMAT(NOW(),'%m-%d-%Y')
> 11-04-2014
MySQL > DATE_FORMAT(NOW(),’%b %d %Y %h:%i %p’)
>Nov 04 2014 11:45 PM

Format you can use :

%a  Abbreviated weekday name (Sun-Sat)
%b  Abbreviated month name (Jan-Dec)
%c  Month, numeric (0-12)
%D  Day of the month with English suffix (0th, 1st, 2nd, 3rd …..)
%d  Day of month, numeric (00-31)
%e  Day of month, numeric (0-31)
%f  Microseconds (000000-999999)
%H  Hour (00-23)
%h  Hour (01-12)
%I  Hour (01-12)
%i  Minutes, numeric (00-59)
%j  Day of year (001-366)
%k  Hour (0-23)
%l    Hour (1-12)
%M  Month name (January-December)
%m  Month, numeric (00-12)
%p  AM or PM
%r  Time, 12-hour (hh:mm:ss followed by AM or PM)
%S  Seconds (00-59)
%s  Seconds (00-59)
%T  Time, 24-hour (hh:mm:ss)
%U  Week (00-53) where Sunday is the first day of week
%u  Week (00-53) where Monday is the first day of week
%V  Week (01-53) where Sunday is the first day of week, used with %X
%v  Week (01-53) where Monday is the first day of week, used with %x
%W  Weekday name (Sunday-Saturday)
%w  Day of the week (0=Sunday, 6=Saturday)
%X  Year for the week where Sunday is the first day of week, four digits, used with %V
%x  Year for the week where Monday is the first day of week, four digits, used with %v
%Y    Year, numeric, four digits
%y    Year, numeric, two digits