Category Archives: mysql

Handling Null Values in SQL- ISNULL, NVL2, IFNULL, Coalesce

There can be situations when if a value is null in sql query, you want to replace it with something else. Different DBs provide you different options

Oracle- PLSQL
select NVL2(teamid, teamid, deptid) allocationid, empid, name from employee; //If team id is available that is returned as allocation id else deptid

IN MS-SQL we have ISNULL
select ISNULL(teamid, deptid) allocationid, empid, name from employee;

Similarly MySQL has IFNULL

Another option is COALESCE, this works for both Oracle and MS-SQL. This is slightly different from others in implementation that it makes columns to be skipped unless first non-null value is found

select COALESCE(teamid, deptid, organizationid) allocationid, empid, name from employee;

Forgot MySql password?

Forgot the root password for mysql database. Here are easy steps to recover

1. Restart MySql database without password in safe mode
/etc/init.d/mysql stop
mysqld_safe --skip-grant-tables &

2. Change root password 
mysql -u root
mysql> use mysql;
mysql> update user set password="mypassword" where User='root';
mysql> flush privileges;
mysql> quit

**please note that the password might be in encrypted form. use PASSWORD('some_pass') method 
mysql> update user set password=PASSWORD('mypassword') where User='root';

3. Restart database
/etc/init.d/mysql stop
/etc/init.d/mysql start

References: http://www.cyberciti.biz/tips/recover-mysql-root-password.html

Taking Mysql Backups

If you need to take a backup of myql database in sql format, it is pretty simple

mysqldump -u myDatabaseUsername -p Password >backup.sql

You will get the backup.sql file which you can restore anywhere

mysql – u myDatabaseUser -p -D databseName < backup.sql or simply execute file from the mysql prompt @backup.sql References: http://www.roseindia.net/mysql/mysql_backup_restore.shtml

For creating user and providing privileges
http://www.debuntu.org/how-to-create-a-mysql-database-and-set-privileges-to-a-user