UUID and its implementation

An UUID is a Universally Unique ID meaning that it will never appear anywhere else in the world. For the requirement  of  the IDs to be universally unique, UUIDs may be our only choice. We only need this if we're sharing our data publicly.

While we use UUIDs, we  store them as a number and not as a string. We don't mean inbinary format. We mean as a 128 bit number, rather than a 288 bit string. For instance, the word 'hello' in ASCII is 68 65 6C 6C 6F, which is the number 448,378,203,247. Storing the string '68656C6C6F' requires 10 bytes. The number 448,378,203,247 requires only 5. All in all, unless you really need the first U in UUID, you can't do much better than auto_increment If you have millions of records, then the saving in storage space will improve the performance . If our IDs do not need to be universally unique, then we can use auto_increment, which guarantees that IDs will be unique within a table (since the value will increment each time).

in Java to generate UUID we can follow the below snippet:

import java.util.UUID;

public class GenerateUUID {
  
  public static final void main(String... aArgs){
    //generate random UUIDs
    UUID idOne = UUID.randomUUID();
    UUID idTwo = UUID.randomUUID();
    log("UUID One: " + idOne);
    log("UUID Two: " + idTwo);
  }
  
  private static void log(Object aObject){
    System.out.println( String.valueOf(aObject) );
  }
} 


Example run:
>java -cp . GenerateUUID
UUID One: 067e6162-3b6f-4ae2-a171-2470b63dff00 
UUID Two: 54947df8-0e9e-4471-a2f9-9af509fb5889

Since UUID is 128 bits and is written as hexadecimal, it's very easy to speed up and store the UUID.For that first, we need to use our programming language for removing the dashes

From 067e6162-3b6f-4ae2-a171-2470b63dff00 to 067e61623b6f4ae2a1712470b63dff00.

Now it's 32 chars (like an MD5 hash, which this also works with).

Since the binary format requires 1 bit to store what hexadecimal does in 4 bits, we will create a BINARY(16) field.

In mySql we can insert using:

INSERT INTO Table (FieldBin) VALUES (UNHEX("067e61623b6f4ae2a1712470b63dff00"))

and query using:

SELECT HEX(FieldBin) AS FieldBin FROM Table

Now in our programming language, we need to re-insert the dashes at the positions 9, 14, 19 and 24 to match your original UUID. If the positions are always different you could store that info in a second field.

Full example :

CREATE TABLE  `test_table` (
    `field_binary` BINARY( 16 ) NULL ,
    PRIMARY KEY (  `field_binary` )
) ENGINE = INNODB ;

INSERT INTO  `test_table` (
    `field_binary`
)
VALUES (
    UNHEX(  '067e61623b6f4ae2a1712470b63dff00' )
);

SELECT HEX(field_binary) AS field_binary FROM `test_table`

If you want to use this technique with any hex string, always do length / 2 for the field length. So for a sha512, the field would be BINARY (64) since a sha512 encoding is 128 characters long.

To add a new user to a MySQL database from the Linux command line

This method should work from any operating system that uses the MySQL client application from a terminal.
First we need to log into MySQL on a server so lets do that right now.

mysql -u root -p

Then enter your password and hit enter. Now we want to create a database which our new user will have privileges on. If you already have a database you can skip this step.

mysql > create database new_database;

Now with our new database in place called new_database we can move on and set up a user for this schema.

mysql > grant usage on *.* to new_database_user@localhost identified by user_password';

Now we want the new user to be able to do almost everything in the new database so we run:

mysql > grant all privileges on new_database.* to new_database_user@localhost;

Now to make sure all the settings we entered stick we run:

mysql > flush privileges;

Now you should be able to easily log in to the database called new_database on the localhost. If you wanted the user to be able to access the database from any location than you would run the following line instead of the one above:

mysql > grant usage on *.* to new_database_user@'%' identified by 'user_password';

Hope this helps people remember how to add users really is in MySQL on the command line.

Automated backup of mySql database

To make back up mysql database automatically,we may use task scheduler(for windows) or cron for linux server.

at winodws, we can execute a batch file using task scheduler. The content for batch file is:
--
@echo off
echo Running Rsa dump...
-h -u -p --result-file=
echo Done!
--


for my case the batch file was:
--
@echo off
echo Running Rsa dump...
G:\BACK_UP_Database\mysqldump\newer\mysqldump -h 10.76.12.51 -uroot -p*paul*db# --result-file="G:\BACK_UP_Database\RSA\backup_%DATE%.sql" rsadb
echo Done!
--

it should be noticeable that the user and password is stick to the -u and -p parameter. If we provide space among them then password will be prompt for to input.

The short date format should not contain slash. It causes problem to create file with that name. we may set the short date format as yyyy-MMM-dd .

After that we will have to configure the task manager according to our requirements.

Mysqladmin Commands in Linux

mysqladmin is a command-line utility the comes with MySQL server and it is used by Database Administrators to perform some basic MySQL tasks easily such as setting root password, changing root password, monitoring mysql processes, reloading privileges, checking server status etc.

1.To set MySQL Root password:

# mysqladmin -u root password YOURNEWPASSWORD

2.to Change MySQL Root password?

#mysqladmin -u root -p123456 password 'xyz123'

3.To check MySQL Server is running?

# mysqladmin -u root -p ping

4.To Check which MySQL version I am running?

# mysqladmin -u root -p version

5.To Find out current Status of MySQL server?

#mysqladmin -u root -ptmppassword status 

6.To check status of all MySQL Server Variable’s and value’s?

# mysqladmin -u root -p extended-status

7.To see all MySQL server Variables and Values?

# mysqladmin  -u root -p variables

8.To check all the running Process of MySQL server?

#mysqladmin -u root -p processlist

9.To create a Database in MySQL server?

# mysqladmin -u root -p create databasename

10.To drop a Database in MySQL server?

# mysqladmin -u root -p drop databasename

11.To reload/refresh MySQL Privileges?

# mysqladmin -u root -p reload;
# mysqladmin -u root -p refresh;

The reload command tells the server to reload the grant tables. The refresh command

 flushes all tables and reopens the log files.

12.To shutdown MySQL server Safely?

#mysqladmin -u root -p shutdown 

other commands:

# /etc/init.d/mysqld stop
# /etc/init.d/mysqld start

13.Some useful MySQL Flush commands

# mysqladmin -u root -p flush-hosts
# mysqladmin -u root -p flush-tables
# mysqladmin -u root -p flush-threads
# mysqladmin -u root -p flush-logs
# mysqladmin -u root -p flush-privileges
# mysqladmin -u root -p flush-status

14.To kill Sleeping MySQL Client Process?

to see: # mysqladmin -u root -p processlist

Run the following id with process id: 

# mysqladmin -u root -p kill 5 

15.To run multiple mysqladmin commands together?

#mysqladmin  -u root -p processlist status version 

16.To Connect remote mysql server:

# mysqladmin  -h 172.16.25.126 -u root -p

17.To execute command on remote MySQL server:

# mysqladmin  -h 172.16.25.126 -u root -p status

18.To start/stop MySQL replication on a slave server?

# mysqladmin  -u root -p start-slave

# mysqladmin  -u root -p stop-slave

19.To store MySQL server Debug Information to logs?

# mysqladmin  -u root -p debug

20.To view mysqladmin options and usage:

# mysqladmin --help 

Source 

Copy an existing MySQL table to a new table

To make a copy of the table tableOriginal which is in a different database called production into a new table called tableDuplicate in the currently selected database, use these two commands:

CREATE TABLE tableDuplicate LIKE production.tableOriginal ; 

INSERT into tableDuplicate SELECT * FROM production.tableOriginal ;

The first command creates the new table recipes_new by duplicating the structure of the existing table. The second command copies the data from old to new.

MySQL stored procedure vs function

The most general difference between procedures and functions is that they are invoked differently and for different purposes:

1. A procedure does not return a value. Instead, it is invoked with a CALL statement to perform an operation such as modifying a table or processing retrieved records.
2. A function is invoked within an expression and returns a single value directly to the caller to be used in the expression.
3. You cannot invoke a function with a CALL statement, nor can you invoke a procedure in an expression.

You can't mix in stored procedures with ordinary SQL, whilst with stored function you can.
e.g. SELECT get_foo(myColumn) FROM mytable is not valid if get_foo() is a procedure, but you can do that if get_foo() is a function. The price is that functions have more limitations than a procedure.
 

Syntax for routine creation differs somewhat for procedures and functions:

1. Procedure parameters can be defined as input-only, output-only, or both. This means that a procedure can pass values back to the caller by using output parameters. These values can be accessed in statements that follow the CALL statement. Functions have only input parameters. As a result, although both procedures and functions can have parameters, procedure parameter declaration differs from that for functions.
2. Functions return value, so there must be a RETURNS clause in a function definition to indicate the data type of the return value. Also, there must be at least one RETURN statement within the function body to return a value to the caller. RETURNS and RETURN do not appear in procedure definitions.
   
   • To invoke a stored procedure, use the CALL statement. To invoke a stored function, refer to it in an expression. The function returns a value during expression evaluation.
   • A procedure is invoked using a CALL statement, and can only pass back values using output variables. A function can be called from inside a statement just like any other function (that is, by invoking the function's name), and can return a scalar value.
   • Specifying a parameter as IN, OUT, or INOUT is valid only for a PROCEDURE. For a FUNCTION, parameters are always regarded as IN parameters.
   If no keyword is given before a parameter name, it is an IN parameter by default. Parameters for stored functions are not preceded by IN, OUT, or INOUT. All function parameters are treated as IN parameters.
   
   To define a stored procedure or function, use CREATE PROCEDURE or CREATE FUNCTION respectively:
   

   CREATE PROCEDURE proc_name ([parameters])
    [characteristics]
    routine_body
   
   
   CREATE FUNCTION func_name ([parameters])
    RETURNS data_type       // diffrent
    [characteristics]
    routine_body

   A MySQL extension for stored procedure (not functions) is that a procedure can generate a result set, or even multiple result sets, which the caller processes the same way as the result of a SELECT statement. However, the contents of such result sets cannot be used directly in expression.
   Stored routines are associated with a particular database, just like tables or views. When you drop a database, any stored routines in the database are also dropped.
   Stored procedures and functions do not share the same namespace. It is possible to have a procedure and a function with the same name in a database.
   In Stored procedures dynamic SQL can be used but not in functions or triggers.
   

   SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) can be used in stored procedures, but not stored functions or triggers. Thus, stored functions and triggers cannot use Dynamic SQL (where you construct statements as strings and then execute them). (Dynamic SQL in MySQL stored routines)

   
   Some more interesting differences between FUNCTION and STORED PROCEDURE:
   
3. Stored procedure is precompiled execution plan where as functions are not. Function Parsed and compiled at runtime. Stored procedures, Stored as a pseudo-code in database i.e. compiled form.
4. Stored procedure has the security and reduces the network traffic and also we can call stored procedure in any no. of applications at a time. reference
5. Functions are normally used for computations where as procedures are normally used for executing business logic.
6. Functions Cannot affect the state of database (Statements that do explicit or implicit commit or rollback are disallowed in function) Whereas Stored procedures Can affect the state of database using commit etc.
   refrence: J.1. Restrictions on Stored Routines and Triggers
7. Functions can't use FLUSH statements whereas Stored procedures can do.
8. Stored functions cannot be recursive Whereas Stored procedures can be. Note: Recursive stored procedures are disabled by default, but can be enabled on the server by setting the max_sp_recursion_depth server system variable to a nonzero value. See Section 5.2.3, “System Variables”, for more information.
9. Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger. Good Example: How to Update same table on deletion in MYSQL?

Note: that although some restrictions normally apply to stored functions and triggers but not to stored procedures, those restrictions do apply to stored procedures if they are invoked from within a stored function or trigger. For example, although you can use FLUSH in a stored procedure, such a stored procedure cannot be called from a stored function or trigger. 

To show Image in jasper report from BLOB field of MySql Database

Normally we save image in the blob field of mySql database table. To get that image  in the report page of jasper report we need to follow some steps.

1.set your field class type:    java.io.InputStream

2.Write Image Expression :     JRImageLoader.loadImage((byte [ ] )

                               (JRLoader.loadBytes($F{ImageData})))

                                ImageData is the Blob field in the table.

3.Image Expression Class :      java.awt.Image

4.add the following include