Category Archives: Databases

MySQL vs MongoDB: Comparison Between RDBMS and Document Oriented DB

MySQL vs MongoDB (NoSQL) database

MongoDB vs MySQL: Comparison Between Relational and Document Oriented Database

 

Both are used for storing the data and free to use that is both comes under open-sources software.
MongoDB might be unfamiliar at least to some of us as it is a relatively new compared to other established databases such as MySQL, PostgreSQL or Oracle.

Continue reading

How to import an SQL file using command line in MySQL?

Import MySQL database using command line

 

If you have a very large SQL dump file to import and you know very well that it is not very easy to import using phpMyAdmin, even if you split it in many pieces, it is also be hard using PHP script as it will time out after a particular time.

 

In this case there is only one acceptable and easy solution is import the file directly from MySQL command line.
As command Line will always be the most efficient, though not the most user-friendly way to accomplish a database export and import.

 

Follow the below easy steps to make your life easy.

 

The below example I am showing is executed on my local server(local host) and using XAMPP, but same will also work on Linux if you are using Linux for your application but you need to make sure that the file you want to import is of a .sql(.gz, .gzip) format.

 

The below method is perfect if

 

  • You do not have phpMyAdmin installed.
  • You do not have remote web access to the database server.
  • You do have SSH/Putty access.
  • The database file size is very large.
  • You want total control over the process.

Let’s start

 

1. Open a command prompt (or shell in Linux) with administrative privileges.

 

2. If you are in Windows set character set to Unicode. Linux is using UTF-8 by default.

chcp 65001

 

3. Connect to a MySQL instance using command line (If your MySQL path is different rather than drive D: as I have, you should be in that folder only)

D:\xampp\mysql\bin\mysql -h 192.168.1.1 --port=3306 -u root -p

If you are in local host you do not need host and port

D:\xampp\mysql\bin\mysql -u root -p

 

4. It will now ask for password: enter the password
—> <your-password>

 

5. You are now in MySQL shell. Set network buffer length to a large byte number. The default value may throw errors for such large data files

set global net_buffer_length=1000000;

 

6. Set maximum allowed packet size to a large byte number. The default value may throw errors for such large data files.

set global max_allowed_packet=1000000000;

 

7. Disable foreign key checking to avoid delays, errors and unwanted behavior

SET foreign_key_checks = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;

 

8.  Select your database as below

use <your-db-name>;

 

If you have not created the database yet, in which you want to import the database then below the above you can created the database using the below MySQL command

create database <your-db-name>; and after then run use <your=db-name>;

 

9. Provide your dump file full path name

source <DbName.sql>

If your file is not is the location you are currently in then provide the complete path of the file as below

source C:\scriptarticledotcom_db\dbdump150404.sql

 

It’s DONE!!

Remember to enable foreign key checks when process is complete else it will set for all of your databases as well and it will work improper!

 

Run the below MySQL commands for reset the global MySQL settings

SET foreign_key_checks = 1;
SET UNIQUE_CHECKS = 1;
SET AUTOCOMMIT = 1;

 

Hope the above will work, share you feedback or comments if you face any issue or have more suggestion to make the article more useful for developers.

 

 

How to prevent gaps/holes in a MySQL table AUTO_INCREMENT Primary Key field

Prevent gaps/holes in a MySQL table AUTO_INCREMENT Primary Key field.

 

Generally during the MySQL database development you create tables with a Primary Key, a very simple way to create a primary key is make it Auto-increment and unsigned integer with Not Null property right?

 

I think most of you generally follow the above process. Suppose you are playing a very large database and have a very large number of Ids, I know this is rarely happens but you should aware this one, you might face an issue where the new row insertion will through error.

 

Have a look on MySQL data type and their range

 

INT – A normal-sized integer that can be signed or unsigned.
– If signed, the allowable range is from -2147483648 to 2147483647.
– If unsigned, the allowable range is from 0 to 4294967295.
– Max allowed length is 11 digits.

 

TINYINT – A very small integer that can be signed or unsigned.
– If signed, the allowable range is from -128 to 127.
– If unsigned, the allowable range is from 0 to 255.
– Max allowed length is 4 digits.

 

SMALLINT – A small integer that can be signed or unsigned.
– If signed, the allowable range is from -32768 to 32767.
– If unsigned, the allowable range is from 0 to 65535.
– Max allowed length is 5 digits.

 

MEDIUMINT – A medium-sized integer that can be signed or unsigned.
– If signed, the allowable range is from -8388608 to 8388607.
– If unsigned, the allowable range is from 0 to 16777215.
– Max allowed length is 9 digits.

 

BIGINT – A large integer that can be signed or unsigned.
– If signed, the allowable range is from -9223372036854775808 to 9223372036854775807.

– If unsigned, the allowable range is from 0 to 18446744073709551615.
– Max allowed length is 20 digits.

 

Let a suppose you have specified your primary key as INT so the max number you can enter with in it is 4294967295 (if unsigned),
Yes it is not a small number and will not reach generally but if the system allow deletion of records/data on daily basis then what will happen, one day a new row will not insert and it will reach a max limit as you know once a row deleted that id will not use any more and you will have a lot of holes/gaps in the id that was not used.

 

I think you have got what I want to say. You have a table named as student with simply 2 columns
id and name and your table data are

 

1 student1
2 student2
3 student3
.. ..
20 student 20

 

Now you have deleted 2 and 3 id’s records then these id will never user by auto-increment id (primary key), you can use these id but you should know which id is not used and how to fetch this automatically, it will very hard to find if there are lot of rows.

 

How to fill or use this id or holes or gaps automatically.
Use the below query to insert the id instead of use this automatically. It will return NULL if no gap/hole is there in the primary key.

 

SELECT MIN(st1.id+1)
FROM student st1
LEFT JOIN student st2 ON st2.id = st1.id + 1
WHERE st2.id IS NULL

The above query returns you an id that can be used to insert the new row.

There are some most important note you should take care before using these.

 

You should not have values (id) on other tables associated (as a foreign key) with the deleted primary key. If this happen your rest of life will not fine. You should take care that when you insert a new entry with primary key that was taken from the gap, the gaped value should not be there in the associated tables.

You should clean ALL of your tables when you delete a primary key.

 

Quickly Deactivate or Activate All WordPress Plugins via Database or FTP

WordPress plugin activate deactivate all at once and Quickly

Sometimes during troubleshooting, it’s recommend to deactivate all plugins and activate them one by one and suppose at that time you will not able to login in WordPress admin (wp-admin) or white screen of death. At the same point we need to disable all the plugin or some plugin then in that case, How will you disabled/deactivate all or some plugin manually by the database or using FTP. Some time we need it to do very quickly.
I am sharing with you the technique by which you can quickly disable and (re)enable your entire set of WordPress plugins.

 

Deactivate All Plugins Using FTP

 

Navigate to the “/wp-content/” folder. You will see a folder called plugins. Rename the plugins folder to anything like  “plugins-deactivate”. Once you do this, all of your plugins will be deactivated automatically.

 

There may be due to some issue with plugin and it locked out your admin panel. If the issue was with your plugin, you should be able login to your “/wp-admin/” after deactivating the plugin. Once you do that, go back to your /wp-content/ directory and rename “plugins-deactivate” back to plugins. You can troubleshoot the problem by activating plugin one by one until your site breaks again.

 

 

Deactivate all plugins using phpMyAdmin

 

Login to your database using with phpMyAdmin and then have a backup.
I always suggest you to take Backup before doing any updates in table directly, so please in first step take backup of your database.
If you have backup of your database, you can quickly restore that and can revert your changes, so in this case(if there will some accident) you will always in safe hands.

 

Now, search a row in “wp_options” table that have “option_name” as “active_plugins”. Use the following SQL query (update your Database table prefix “wp_” if needed) for doing the same job.

 

SELECT * FROM wp_options WHERE option_name = ‘active_plugins’;

 

Once you found active_plugins column, click to edit it. You will see something like the below, depends on the number and type of plugins you have installed:

 

a:13:{i:0;s:19:”akismet/akismet.php”;i:1;s:23:”all_in_one_seo_pack.php”;i:2;s:28:”breadcrumb-navigation-xt.php”;i:3;s:37:”contact-coldform/contact_coldform.php”;i:4;s:13:”feedcount.php”;i:5;s:36:”google-sitemap-generator/sitemap.php”;i:6;s:13:”gravatars.php”;i:7;s:30:”nofollow-free/nofollowfree.php”;i:8;s:17:”ol_feedburner.php”;i:9;s:26:”simple_recent_comments.php”;i:10;s:18:”simple_twitter.php”;i:11;s:9:”top10.php”;i:12;s:16:”wp-db-backup.php”;}

 

This is a serialized array, you can unserialize this array by simply using the tool “http://www.functions-online.com/unserialize.html“. Copy the above string and click on run button you will get an array that represents every activated plugin on your site.

 

Thus, to quickly disable all plugins without using the wp-admin is cut the serialize array string as we have seen above and paste it into a safe notepad (text) file. After removing the code, click the button to save your changes and that’s it. All WordPress plugins are now deactivated (although you have all the plugins files in the same place “wp-content/plugins” and you can activate the plugin whenever you want by wp-admin area- if found working). This above method is obviously handy during, when the situation is critical and time-sensitive. Once you are ready to re-enable your entire set of plugins, simply copy & paste the preserved code(serialized array string) by notepad file and put it in the “active_plugins” field. Click save and done.

 

If you want to activate only some of those, unserialize the array by the above tool and make the array of plugins you want to activate then serialize that and put the string in the “activate_plugins” columns.

 

You can also go with a very simple SQL query to disable all plugins in one second.

UPDATE wp_options SET option_value = ” WHERE option_name = ‘active_plugins’;

 

 

This method works only for WordPress older versions(2.9 or older) for later use the below.

UPDATE wp_options SET option_value = ‘a:0:{}’ WHERE option_name = ‘active_plugins’;

How to move your wordpress site to a new domain – easy wordpress migration

WordPress stores the URL in his database, one of my colleague is new bie for WordPress.He has developed a new WordPress site and client has given all the data to him.After making the full site ready, he get stuck that four most important tables have URL in it, so should all the data need to re-enter by WordPress admin.

 

I know most of the WordPress newbie think like this, but it can be done by below eight very simple queries (in the case if you have not installed any large plugins)

 

What you need to done is just replace
@[email protected] and @[email protected] as per your website and run these query.

 

UPDATE wp_options SET option_value = REPLACE (option_value, “@[email protected]”, “@[email protected]”) WHERE option_name = ‘home’ OR option_name = ‘siteurl’;

 

UPDATE wp_posts SET guid = REPLACE (guid, “@[email protected]”, “@[email protected]”);

 

UPDATE wp_posts SET post_content = REPLACE (post_content, “@[email protected]”, “@[email protected]”);

 

UPDATE wp_posts SET post_content = REPLACE (post_content, CONCAT(‘src=”‘, “@[email protected]”), CONCAT(‘src=”‘, “@[email protected]”));

 

UPDATE wp_posts SET guid = REPLACE (guid, “@[email protected]”, “@[email protected]”) WHERE post_type = ‘attachment’;

 

UPDATE wp_postmeta SET meta_value = REPLACE (meta_value, “@ol[email protected]”, “@[email protected]”);

 

UPDATE wp_links SET link_url= REPLACE (link_url, “@[email protected]”, “@[email protected]”);

 

UPDATE wp_links SET link_image = REPLACE (link_image, “@[email protected]”, “@[email protected]”);

 

Now move your WordPress site to a new domain is very simple, isn’t it!!

If you still face any issue, post your comments.

What is the difference between GROUP BY and ORDER BY in MySql?

What is the difference between GROUP BY and ORDER BY in MySql?

 

Mostly this question has been asked by the interviewer, even if you have used it many times but at that time generally the guy not satisfied the interviewer by the answer.

 

Please go through the below and give very appropriate answer and increase your chances of selection.

 

GROUP BY

 

GROUP BY is used to make the group-set of records based on the fields of the table or you can say GROUP BY keeps related data items together.

 

GROUP BY clause often used with the aggregate functions (SUM, AVG, MAX) on the columns in a query the other columns should be in group by query, you can use HAVING clause to apply the condition on the group-set that will fetch by the query.

 

Let’s take an example:
Suppose we have a table student as below.

 

tbl_student

——————————————–
Id                     Name
——————————————–
01                     Nirmal
98                     Stephen
65                     Mahesh
65                     Hodge
90                     Pamlea
90                     Paul
90                     Shakira
——————————————-

 

Question 1: Due to some mistake by the programmer, there was some duplicate id for some students. Fetch which id is duplicated and how many times?

Query:

SELECT Id, count(Id)
FROM tbl_student
GROUP BY Id

 

Question 2: Fetch which id is duplicated and how many times and the Ids should be greater than 80.

SELECT Id, count(Id)
FROM tbl_student
GROUP BY Id
HAVING Id > 80

 

ORDER BY

 

The ORDER BY clause is used to sort the result-set by a specified column either by ASC or DESC.
By default, ORDER BY clause sorts the records in ascending order.

 

ASC = Ascending (shortest first)
Desc = Descending (largest first)

 

SELECT Name
FROM tbl_student
ORDER BY Name ASC

 

I think it will make sense?