Linux: mysql

Monday, September 22, 2014

NodeJS MySql Connectivity : With Sample Application

Hi,

Here, i will share with you on NodeJS sample application with MySql Database connectivity.

I am assuming that you already installed the NodeJS, NPM and Socket.io on your machine.
If not, you can follow below link to install the above requirements
http://tarunlinux.blogspot.in/2014/09/nodejs-basics.html

Here, I will show you in step-wise :

Step 1: Install the mysql module... here also i am assuming that mysql is already installed in your machine.
Now install mysql module for the NodeJs dependency.
# sudo npm install mysql

That will install the MySql dependency in your machine.

Step 2: Now Create the server.js file for the nodejs:

var mysql = require('mysql'); // load mysql module
var client = require('socket.io').listen(8080).sockets;  //Load socket.io module

//Database connectivity
var db = mysql.createConnection({
    host: 'localhost'
    , user: 'root'
    , password: 'root'
    , database: 'zfskel'
    , port: '3306'    
});

db.connect(function(err){
    console.log(err);
});

//Create connection with the client and emit data on socket
client.on('connection', function(socket){
    db.query('SELECT * FROM employee')
      .on('result', function(data){

        socket.emit('all-data', [data]);
    });
});

Step 3: Create Index.html file for the front-end:

<html>
    <head>
        <title> NodeJs Mysql APP </title>
        <link rel="stylesheet" href="">
    </head>
    <body>
        <div class="data-set"></div>
    <script src="http://localhost:8080/socket.io/socket.io.js" type="text/javascript"></script>
    <script src="http://code.jquery.com/jquery-2.1.1.min.js" type="text/javascript"></script>
    <script>
    (function(){
        try{
            var socket = io.connect('http://localhost:8080');
        } catch(e){
            console.log(e);
        }
        
        if(socket !== undefined) {
            socket.on('all-data', function(data){
                for(var i=0; i < data.length; i++) {
                    $(".data-set").append('<li>'+ data[i].name +'</li>');
                }
            });
        }
    })();
    </script>
    </body>
</html>

Now your have 2 files i.e. server.js and index.html file.

Step 4: Now you need the sql file for the database connectivity:

CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `employee` VALUES (1,'tarun',26),(2,'varun',34);

Step 5: Now you have to run only server.js file form command line:
# nodejs server.js

Then open your browser and run the below url:
http://localhost:8080/

Here, you will see the MySql data on your browser...

I hope it will help you to understand NodeJs and its connectivity with the MySql connectivity.

Bye.. :)

Tuesday, November 1, 2011

MySql View DEFINER and SQL Security

Hi,

Some time back, I tried to backup a database from my server and restored it on my localhost mysql server. It had a few views. On taking backup from server i always getting issue i.e. :

mysqldump: Got error: 1449: The user specified as a definer ('msilink'@'localhost') does not exist when using LOCK TABLES

So I opened the dump file and found that the error was being caused by a line which looks like this :

/*!50013 DEFINER=`msilink`@`localhost` SQL SECURITY DEFINER */

This line appeared in every view definition. So the simple reason was that msilink@localhost, user which had created the view (on the server) and when importing the database on local system there was no user named ‘projects’ in MySql.

For that i just open the file and update the line: "50013 DEFINER=`msilink`@`localhost`" with "50013 DEFINER=`root`@`localhost`"

with the following command(In Linux VI Editor):

:%s/50013 DEFINER=`msilink`@`localhost`/50013 DEFINER=`root`@`localhost`/g

then save and close the file. The above solutions worked for me.

Solution is to create a view with SQL security invoker like this :

CREATE
SQL SECURITY INVOKER
VIEW system_users AS SELECT host,user,password FROM mysql.user;

The above statement allows this view to be viewed by any user who invokes the view.
By default the SQL SECURITY is DEFINER which means the definer user can only view it.

:: Alternative Solution ::
Create another user that have all the privileges like as root, viw the following query:

mysql > grant all PRIVILEGES on *.* to msilink@localhost IDENTIFIED BY 'root' WITH GRANT OPTION;

In General:
mysql > grant on to @localhost IDENTIFIED BY '' WITH GRANT OPTION;

That also solve your problem.