Testing MySQL GET_LOCK() using PHP

[ This Blog Was Moved To : http://www.diknows.com ]


Early this morning, a friend of mine was stuck in GET_LOCK() MySQL function. She was using it in her application. The function was working very well from the MySQL Console , but it was not working from PHP ( Neither from her application, nor from PHPMyAdmin web interface).

She asked my help, so I searched Google, and had a look at the MySQL Documentation about this function.

This function as it appears in the MySQL Documentation, does the following :

GET_LOCK(str,timeout)

Tries to obtain a lock with a name given by the string str, using a timeout of timeout seconds. Returns 1 if the lock was obtained successfully, 0 if the attempt timed out (for example, because another client has previously locked the name), or NULL if an error occurred (such as running out of memory or the thread was killed with mysqladmin kill). If you have a lock obtained with GET_LOCK(), it is released when you execute RELEASE_LOCK(), execute a new GET_LOCK(), or your connection terminates (either normally or abnormally). Locks obtained with GET_LOCK() do not interact with transactions. That is, committing a transaction does not release any such locks obtained during the transaction.

This function can be used to implement application locks or to simulate record locks. Names are locked on a server-wide basis. If a name has been locked by one client, GET_LOCK() blocks any request by another client for a lock with the same name. This allows clients that agree on a given lock name to use the name to perform cooperative advisory locking. But be aware that it also allows a client that is not among the set of cooperating clients to lock a name, either inadvertently or deliberately, and thus prevent any of the cooperating clients from locking that name. One way to reduce the likelihood of this is to use lock names that are database-specific or application-specific. For example, use lock names of the form db_name.str or app_name.str.

mysql> SELECT GET_LOCK('lock1',10);
        -> 1
mysql> SELECT IS_FREE_LOCK('lock2');
        -> 1
mysql> SELECT GET_LOCK('lock2',10);
        -> 1
mysql> SELECT RELEASE_LOCK('lock2');
        -> 1
mysql> SELECT RELEASE_LOCK('lock1');
        -> NULL

The second RELEASE_LOCK() call returns NULL because the lock 'lock1' was automatically released by the second GET_LOCK() call.

If multiple clients are waiting for a lock, the order in which they will acquire it is undefined and depends on factors such as the thread library in use. In particular, applications should not assume that clients will acquire the lock in the same order that they issued the lock requests.

Note

If a client attempts to acquire a lock that is already held by another client, it blocks according to the timeout argument. If the blocked client terminates, its thread does not die until the lock request times out. This is a known bug (fixed in MySQL 5.6).

So, after reading the above wonderful documentation, I thought of several reasons “Why would she not be able to obtain a lock using that function from PHP ? ” and I guessed the following :

  1. She may have called GET_LOCK and never called RELEASE_LOCK and tried to call GET_LOCK again with the same lock name.
  2. She may be calling GET_LOCK from a script where the connection is closed by the end of that script and then tries to check IS_FREE_LOCK from another script.
  3. Something is error in her SETUP ( Which I have no clue how to solve ).

After arguing with her over these points, she proved to me that she is doing every thing the right way. So, I created the following PHP Script to check if there is really and issue in PHP calling GET_LOCK MySQL function.


<?php

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die                      ('Error connecting to mysql');

$dbname = 'carpooling';
mysql_select_db($dbname);

$idProjetRevision=50;

function LockProjet( $idProjetRevision)
 {
  $sql = "SELECT GET_LOCK('LockedProject_$idProjetRevision', 28800) AS 'GetLock'"; //8 hours lock
  $res = mysql_query($sql);
  showValue($res);
}

function IsLockedProjet( $idProjetRevision)
 {
  $sql = "SELECT IS_FREE_LOCK('LockedProject_$idProjetRevision');"; //8 hours lock
  $res = mysql_query($sql);
  showValue($res);
}

function ReleaseProjet($idProjetRevision)
 {
  $sql = "SELECT RELEASE_LOCK('LockedProject_$idProjetRevision'); "; //8 hours lock
  $res = mysql_query($sql);
  showValue($res);
}

function showValue($res){
  # mysql_close();
 if (!$res) {
    die('Could not query:' . mysql_error());
  }
  $num=mysql_numrows($res);
  $i=0;
  while ($i < $num) {
    $first=mysql_result($res,$i);
    echo "RESULT IS : $first \n";
    echo "----------------------\n";
    $i++;
  }
}
LockProjet(50);
IsLockedProjet(50);
ReleaseProjet(50);
?>

When I tested this function on my Laptop, this was the result:

dino@linux-vxrd:~/Documents/php> php locks.php

RESULT IS : 1
----------------------
RESULT IS : 0
----------------------
RESULT IS : 1
----------------------
dino@linux-vxrd:~/Documents/php>

Which means it should be working fine. So, I asked her to use my code instead, it didn’t work either.
So, the last thing I though of is the privileges of the user she is using to connect to the database, I asked her to open MySQL Administration tool and check user privileges.


So, She checked it, but no use. So, both of us get fed up and she said she gonna do it her own way. I suggested using any ORM Framework, but she got no knowledge about it.
So, This is it.

Wish her best of luck.

All Of you too.

Creating Basic Financial JSF CRUD Application

[ This Blog Was Moved To : http://www.diknows.com ]


Today, I’m trying to revise my JSF, Hibernate Knowledge. I’m Creating a CRUD application.

Its a simple financial web application where there are admin/viewer privileges. The application will not be fully created in this tutorial.

The main idea is that the Admin can deposit/withdraw and the application should show his financial status to subscribed viewers.

In this basic tutorial I will introduce how to Generate JSF/Hibernate CRUD application using Netbeans 6.8 as a starting point for this small project.

I will use local MySQL as my database engine. And here is the ERD :

Financial Tutorial Image

ERD

And Here is the SQL :

USE financial;

/************ Update: Tables ***************/

/******************** Add Table: profile ************************/

/* Build Table Structure */
CREATE TABLE profile
(
 profileId INTEGER NOT NULL,
 profileName VARCHAR(50) NOT NULL,
 InsertBy VARCHAR(50) NOT NULL,
 UpdateBy VARCHAR(50) NOT NULL,
 InsertTime TIMESTAMP NOT NULL,
 UpdateTime TIMESTAMP NOT NULL,
 profile_state FLOAT NOT NULL DEFAULT 0,
 username VARCHAR(100) NULL
);

/* Table Items: profile */
ALTER TABLE profile ADD CONSTRAINT pkprofile
 PRIMARY KEY (profileId);

/******************** Add Table: record ************************/

/* Build Table Structure */
CREATE TABLE record
(
 recordId INTEGER NOT NULL,
 recordName VARCHAR(50) NOT NULL,
 InsertBy VARCHAR(50) NOT NULL,
 UpdateBy VARCHAR(50) NOT NULL,
 InsertTime TIMESTAMP NOT NULL,
 UpdateTime TIMESTAMP NOT NULL,
 profileId INTEGER NOT NULL,
 amount FLOAT NOT NULL DEFAULT 0,
 description TEXT NULL
);

/* Table Items: record */
ALTER TABLE record ADD CONSTRAINT pkrecord
 PRIMARY KEY (recordId);

/******************** Add Table: security ************************/

/* Build Table Structure */
CREATE TABLE security
(
 username VARCHAR(100) NOT NULL,
 password VARCHAR(20) NOT NULL,
 security_role VARCHAR(20) NOT NULL DEFAULT 'user'
);

/* Table Items: security */
ALTER TABLE security ADD CONSTRAINT pksecurity
 PRIMARY KEY (username);

/* Set Comments */
EXEC sp_addextendedproperty 'MS_Description', 'user or admin', 'user', 'schemaA',
 'table', 'security', 'column', 'security_role';

/************ Add Foreign Keys to Database ***************/

/************ Foreign Key: fk_profile_security ***************/
ALTER TABLE profile ADD CONSTRAINT fk_profile_security
 FOREIGN KEY (username) REFERENCES security (username);

/************ Foreign Key: fk_record_profile ***************/
ALTER TABLE record ADD CONSTRAINT fk_record_profile
 FOREIGN KEY (profileId) REFERENCES profile (profileId);

After wards, I created JSF Project in NetBeans 6.8 Like this :

Then, I created Hibernate Mappings From DB like this :

By clicking The Finish Button : The hibernate.reveng.xml gets created.

Now, we need to create the Hibernate Mapping Files XML and POJOs

And the result become like this :

So, We now need to Generate the JSF CRUD application like this :

After clicking the Finish button , the project result is the following :

And the result is the following .. after running the application:

I would like to know your opinions and your suggestions, Next I will introduce EJB , Spring Acegi and may be web services.

Hope somebody liked this tutorial.

Grails, MySQL : Cannot create PoolableConnectionFactory

[ This Blog Was Moved To : http://www.diknows.com ]


Ok, You read this post I assume you face the same problem I faced.

Quick Solution :

If this is your MySQL Connection URL :

“jdbc:mysql://localhost/DN_NAME”

Change it to

“jdbc:mysql://127.0.0.1/DB_NAME”

Understanding the solution :

Recently, I am developing a grails application. I was using the default in memory HSQLDB, as it is wonderful for changing the database design frequently. But, as I felt stability in the project I wanned to move the project to the next level where data get persisted. Hence, I tried to move to MySQL Database by modifying the DataSource.groovy File.

dataSource {
pooled = true
//    driverClassName = “org.hsqldb.jdbcDriver”
//    username = “sa”
//    password = “”

// MySQL Database configurations
driverClassName = “com.mysql.jdbc.Driver”
username = “root”
password = “123456”
}

And result was a very long exception

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘transactionManager’: Cannot resolve reference to bean ‘sessionFactory’ while setting bean property ‘sessionFactory’; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘sessionFactory’: Cannot resolve reference to bean ‘hibernateProperties’ while setting bean property ‘hibernateProperties’; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘hibernateProperties’: Cannot resolve reference to bean ‘dialectDetector’ while setting bean property ‘properties’ with key [hibernate.dialect]; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘dialectDetector’: Invocation of init method failed; nested exception is org.springframework.jdbc.support.MetaDataAccessException: Could not get Connection for extracting meta data; nested exception is org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Communications link failure

So, I tried to Google on PoolableConnectionFactor .. But Results was either

Change /etc/hosts.allow and check /etc/hosts

or

Some changes in /etc/my.cnf and grant access to the database user

And they all failed. So I created a new Java Project in NetBeans IDE, added the JDBC Driver Jar file, and wrote the following code:

String dbUrl = “jdbc:mysql://localhost:3306/DB_NAME”;
Class.forName ( “com.mysql.jdbc.Driver” );
Connection connection = DriverManager.getConnection ( dbUrl , “root” , “123456” );

So, the following single exception appeared :

Dec 5, 2009 9:02:41 AM testmysql.Main main
SEVERE: null
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

So, I Googled again and I found this post

http://forums.sun.com/thread.jspa?threadID=5339538

Which is long, and here is the main point of it :

Re: Communication link failure!!!!
Mar 16, 2009 3:49 PM (reply 8 of 12)  (In reply to #7 )
I’m happy to say that I resolved the issue. I replaced localhost with 127.0.0.1 and it worked perfectly. I’ve also checked what the browser gives as localhost and it’s the IPv6 localhost (to anyone who might be experiencing the same issue that’s 0:0:0:0:0:0:0:1) not the IPv4 one, so that was the problem. Not sure why this machine defaults to IPv6 though.

So, It seams the PC is defaulting to IPv6 not IPv4

and the solution is as I said above

Change the MySQL Connection URL from localhost to 127.0.0.1 (or whatever your localhost IP Address is )

NOTE: you can find you localhost IP Address in /etc/hosts

NOTE: If you use openSuse Linux ( Like I Do ) and you want to disable IPv6, you can do so using

Yast-> Network Manager

Wish you best of luck.

———————————————————————————————

New Updates :

I had another PoolableConnectionFactory exception. This time because I had reinstalled my Linux system, so my databases were gone. I got the following exception

Cannot create PoolableConnectionFactory (Unknown database ‘DB_NAME‘):
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown database ‘DB_NAME

Because, there was no Database scheme in the mysql database engine. So, to solve this:

  1. In console window type “mysql -u root -p” if your mysql username is root and you have a password.
  2. Create new database using “create database DB_NAME”.
  3. Exit the mysql console as you don’t need it, using “exit” command.

Now, you can restart your grails application and this exception will not show up again.

Wish you best of luck.

Simple Idea For Passing Data To Huge Number of Users

[ This Blog Was Moved To : http://www.diknows.com ]


Three years ago, I had a situation when I was building a web application for a stocks consultant. It should provide users with updates , in a page that gets automatically refreshed every 1 minute.

My problem was that when the number of logged in users exceed 100 users, the page turns to be extreemly slowly rendered.

The problem was that I made that page retrieve the data from database every time the page refresh. Ofcourse it should.

I thought of a simple solution, as there was one stocks consultant ( for me data editor ), and it was one page that is needed to get rendered. I decided not to make it dynamically retrieved, but dynamically created.

Which means, I create the HTML page directly after the stocks consultant finishs editing his data that will be shown to his clients ( At that time I wasn’t using AJAX , and the output was fairly simple ). And, With each refresh I retrieve the HTML static page, that was previously created.

After a while, I decided to make it a little more efficient, by checking the file’s SHA1 and compare it with the latest SHA1 of the same file, if they are the samee ( File didn’t change from last retrieval ), I return back a copy that was previously stored in Memory, else I retrieve the generated file, update the memory cache, then send the memory cache back to users.

Ofcourse, If I am creating this application right now, I would be using the same idea, except for saving data in JSON not HTML and send it back to users ( ofoucrse using AJAX ) and render it ( at the client side ).

I dunno actually if compressing JSON data using GZIP is applicable or what, I hope someone tell me that !! ,, ( ofcourse if I’m creating a similar application right now , I will be asking Uncle Google’s Help for GZIP with JSON )

Hope I could be of some help.