Sunday 5 May 2013

Error dropping database


MySQL: ERROR 1010 (HY000): Error dropping database (can't rmdir './sampledb', errno: 39)


Error:

          mysql> drop database sampledb;
          ERROR 1010 (HY000): Error dropping database (can't rmdir './sampledb', errno: 39)

Solution:

Open new terminal
         # cd /var/lib/mysql
         var/lib/mysql# ls
                         sampledb
/var/lib/mysql# rm -fr sampledb

Open MySQL

       mysql> drop database sampledb;
           ERROR 1008 (HY000): Can't drop database 'foodb'; database doesn't exist
      mysql> create database sampledb;
           Query OK, 1 row affected (0.00 sec)
      mysql> drop database sampledb;
           Query OK, 0 rows affected (3.38 sec)

Saturday 23 February 2013

Stored Procedure in MySQL

 


Select
        DELIMITER //
            CREATE PROCEDURE GetAllPicture()
            BEGIN
                     SELECT *  FROM image_infos;
             END //
        DELIMITER ;

         call  GetAllPicture();

Stored Procedures Parameters in MySQL

            In MySQL, a parameter has one of three modes IN, OUT and INOUT

           IN this is the default mode. IN indicates that a parameter can be passed into stored procedures but any 
           modification inside stored procedure does not change parameter.        
         
          OUT this mode indicates that stored procedure can change this parameter and pass back to the calling 
          program.

          INOUT obviously this mode is combined of IN and OUT mode; you can pass parameter into  
          storedprocedure and get it back with the new value from calling program.

 IN

              DELIMITER //
               CREATE PROCEDURE GetAllPictureById(IN user_id INT)
               BEGIN
                         SELECT * FROM image_infos WHERE  id = user_id;
               END //
               DELIMITER ;

OUT
             DELIMITER //
               CREATE PROCEDURE GetAllPictureById(IN user_id INT,OUT total_user INT)
               BEGIN
                         SELECT COUNT(*) AS total_user FROM image_infos WHERE  id = user_id;
               END //
               DELIMITER ;

          

Friday 28 December 2012

MySQL Installation

MySQL Installation

How to install MySQL in Ubuntu?

  • Open your terminal and type the bellow command
         #sudo apt-get install mysql-server
install_mysql
  • It will ask for your password, Type your ubuntu password and press  enter key, it will ask for confirmation
confirm_install
  • Type y and press enter key
  • During the installation you will be prompted for a password. Choose a secure and type the password and press enter. It will also ask to re enter password
set_mysql_password

Once the installation is complete, the MySQL server should be started automatically. You can run the following command from a terminal prompt to check whether the MySQL server is running:

sudo netstat -tap | grep mysql

When you run this command, you should see the following line or something similar:
tcp        0      0 localhost.localdomain:mysql           *:* LISTEN -
If the server is not running correctly, you can type the following command to start it:

sudo /etc/init.d/mysql restart

Overall Process to install MySQL


 To install MySql database:
sudo apt-get install mysql-server
# To start MySql server:
/etc/init.d/mysql start
# To stop MySql server:
/etc/init.d/mysql stop
# To restart MySql server:
/etc/init.d/mysql restart
# To check the status of  MySql server:
/etc/init.d/mysql status