Blog Directory ID: 3012
Blog URL: http://idiot-howto.blogspot.com/
Google Pagerank: N/A
Blog Description: Basic HowTo Collections For Newbies Based On Author's Experiences
Blog Category: Software Blogs
Blog Owner: William Anthony
Blog Added: June 07, 2008 05:36:11 AM
Audience Rating: General Audience
Blog Country: 
United States Number Hits: 8 |  Blog Rating: 0.00 |  Rate Blog: Submit rating |  Review Blog |
| Latest Blog Reviews | |||
| |||
| Add the Review Me Button to your Blog! | |||
 <a href="http://www.bloggingfusion.com//review-blog-3012.html"><img src="http://www.bloggingfusion.com//images/linkware/review-me-at-blogging-fusion.png" alt="Review How To For Idiots at Blogging Fusion Blog Directory" style="border:0px;" /></a> | |||
| Our Sponsors | |||
New Backup/Restore Statement in MySQL 6.0
Finally, what I've been waiting for long time is currently in progress !
New backup and restore syntax is available in MySQL begin from version 6.0.5. (it's still in alpha state at the time I write this post)
This syntax is use just like ordinary DDL/DML SQL syntax, so you wouldn't need to run external application eg. mysqldump anymore. The best of it, is the backup process won't block any client accesses, or vice versa. They can now work concurrently, except for the DDL syntax.
The syntax is so simple:
For example, to backup,
For more information about new BACKUP/RESTORE syntax in MySQL 6.0, see the documentation.
BACKUP/RESTORE using MySQL < 6.0 still need external tools. See my tutorial about Backup MySQL Data Using Java.
New backup and restore syntax is available in MySQL begin from version 6.0.5. (it's still in alpha state at the time I write this post)
This syntax is use just like ordinary DDL/DML SQL syntax, so you wouldn't need to run external application eg. mysqldump anymore. The best of it, is the backup process won't block any client accesses, or vice versa. They can now work concurrently, except for the DDL syntax.
The syntax is so simple:
BACKUP DATABASE [DATABASE] TO [PATH];
RESTORE FROM [PATH];
For example, to backup,
BACKUP DATABASE mycompanydatase TO '/home/staff/backup/backupfile.dat';to restore,
RESTORE FROM '/home/staff/backup/backupfile.dat';
For more information about new BACKUP/RESTORE syntax in MySQL 6.0, see the documentation.
BACKUP/RESTORE using MySQL < 6.0 still need external tools. See my tutorial about Backup MySQL Data Using Java.
Dealing With MySQL Cast() Limitation
If your application depends on data type of your database, you will notice that mysql cast() function has limitation that can't cast to specific datatype, like tinyint, smallint, mediumint, bigint, etc.
There is simple (dumb and idiot) workaround you can do to overcome this situation. Let's try it out.
Create a table with 5 column.
Populate each column with value of 10.
Now, let's try with a simple query.
When you multiply the columns with any number, the result data type is not same as the original data type. You can check it using this Java snippet:
When you run it, the result will looks like this:
That's the problem. Let's do something with this. While MySQL cast() has limitation, why not make our own cast function.
Now we already have our cast function, we should alter our query.
Let's compare the two queries with our Java snippet:
There is simple (dumb and idiot) workaround you can do to overcome this situation. Let's try it out.
Create a table with 5 column.
CREATE TABLE datatype_test (
tiny_int tinyint(4),
small_int smallint(6),
medium_int mediumint(9),
_int int(11),
big_int bigint(20)
);
Populate each column with value of 10.
INSERT datatype_test (
tiny_int,
small_int,
medium_int,
_int,
big_int
)
values (10,10,10,10,10);
Now, let's try with a simple query.
select (d.tiny_int*1) as tiny,
(d.small_int*1) as small,
(d.medium_int*1) as `medium`,
(d._int*1) as regular,
(d.big_int*1) as big
from datatype_test d;
When you multiply the columns with any number, the result data type is not same as the original data type. You can check it using this Java snippet:
import java.sql.*;
public class datatype_test {
public Connection cn;
public PreparedStatement st;
public ResultSet rs;
public static void main(String[] a) throws Exception {
new datatype_test();
}
public datatype_test() throws Exception {
connect();
showcase1();
}
private void connect() throws Exception {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost/test";
cn=DriverManager.getConnection(url,"user","password");
}
private void showcase1() throws Exception {
st = cn.prepareStatement(
"select (d.tiny_int*1) as tiny, " +
" (d.small_int*1) as small, " +
" (d.medium_int*1) as `medium`, " +
" (d._int*1) as regular, " +
" (d.big_int*1) as big " +
"from datatype_test d;");
if (st.execute()) {
rs = st.getResultSet();
rs.next();
ResultSetMetaData rsmt = rs.getMetaData();
int cols = rsmt.getColumnCount();
for (int i=1;i<=cols;i++) {
System.out.println(rsmt.getColumnLabel(i) + " = " +
rsmt.getColumnTypeName(i));
}
}
rs.close();
st.close();
}
}
When you run it, the result will looks like this:
tiny = BIGINT
small = BIGINT
medium = BIGINT
regular = BIGINT
big = BIGINT
That's the problem. Let's do something with this. While MySQL cast() has limitation, why not make our own cast function.
CREATE FUNCTION x_cast_to_tinyint(number bigint) RETURNS tinyint
BEGIN
return number;
END
CREATE FUNCTION x_cast_to_smallint(number bigint) RETURNS smallint
BEGIN
return number;
END
CREATE FUNCTION x_cast_to_mediumint(number bigint) RETURNS mediumint
BEGIN
return number;
END
CREATE FUNCTION x_cast_to_int(number bigint) RETURNS int
BEGIN
return number;
END
CREATE FUNCTION x_cast_to_bigint(number bigint) RETURNS bigint
BEGIN
return number;
END
Now we already have our cast function, we should alter our query.
select x_cast_to_tinyint(d.tiny_int*1) as tiny,
x_cast_to_smallint(d.small_int*1) as small,
x_cast_to_mediumint(d.medium_int*1) as `medium`,
x_cast_to_int(d._int*1) as regular,
x_cast_to_bigint(d.big_int*1) as big
from datatype_test d;
Let's compare the two queries with our Java snippet:
import java.sql.*;
public class datatype_test {
public Connection cn;
public PreparedStatement st;
public ResultSet rs;
public static void main(String[] a) throws Exception {
new datatype_test();
}
public datatype_test() throws Exception {
connect();
showcase1();
System.out.println();
showcase2();
}
private void connect() throws Exception {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost/test";
cn=DriverManager.getConnection(url,"user","password");
}
private void showcase1() throws Exception {
st = cn.prepareStatement(
"select (d.tiny_int*1) as tiny, " +
" (d.small_int*1) as small, " +
" (d.medium_int*1) as `medium`, " +
" (d._int*1) as regular, " +
" (d.big_int*1) as big " +
"from datatype_test d;");
if (st.execute()) {
rs = st.getResultSet();
rs.next();
ResultSetMetaData rsmt = rs.getMetaData();
int cols = rsmt.getColumnCount();
System.out.println("Without Cast");
for (int i=1;i<=cols;i++) {
System.out.println(rsmt.getColumnLabel(i) + " = " +
rsmt.getColumnTypeName(i));
}
}
rs.close();
st.close();
}
private void showcase2() throws Exception {
st = cn.prepareStatement(
"select x_cast_to_tinyint(d.tiny_int*1) as tiny, " +
" x_cast_to_smallint(d.small_int*1) as small, " +
" x_cast_to_mediumint(d.medium_int*1) as `medium`, " +
" x_cast_to_int(d._int*1) as regular, " +
" x_cast_to_bigint(d.big_int*1) as big " +
"from datatype_test d;");
if (st.execute()) {
rs = st.getResultSet();
rs.next();
ResultSetMetaData rsmt = rs.getMetaData();
int cols = rsmt.getColumnCount();
System.out.println("With Cast");
for (int i=1;i<=cols;i++) {
System.out.println(rsmt.getColumnLabel(i) + " = " +
rsmt.getColumnTypeName(i));
}
}
rs.close();
st.close();
}
}
The result is:Without Cast
tiny = BIGINT
small = BIGINT
medium = BIGINT
regular = BIGINT
big = BIGINT
With Cast
tiny = TINYINT
small = SMALLINT
medium = MEDIUMINT
regular = INTEGER
big = BIGINT
Java ByteCode Manipulation
| WARNING - Contents of this file are for educational purposes only. It is strongly suggested that you do not use this knowledge for illegal purposes! |
There are two ways to modify the compiled java code (assume you don't have the source or have loss it):
- Decompile all dependent file, make any modification to .java files created, then recompile.
Pros:- Easy to manage your modification.
- You don't have to decompile them next time you want to modify again.
Cons:- Hard/Impossible to do that on obfuscated file.
- Make any modification directly to .class file.
Pros:- It's so easy to do on obfuscated file.
Cons:- You have to learn about bytecode.
- It will be hard at beginning.
The method I will be discuss right now is option (b), handling .class file directly.
Now let's we begin with simple example.
public class bytecode1 {
public static void main(String[] a) {
System.out.println("Hello Worlf");
System.out.println("Hello Worlf");
System.out.println("Hello Worlf");
}
}
Save it with the name bytecode1.java, then compile it, you will got bytecode1.class. Now you may run it, by executing: java bytecode1
See the result? I've made typo mistake, it should display "Hello World", not "Hello Worlf".
It's too easy, isn't it? You can just use any hex editor to replace the "f" to "d". But how if I want only one "Hello World" line displayed? Can hex editor handling it? maybe yes, maybe no. I don't want to think about it while there are more elegant way to do it.
The tools I use are BCEL and CCK. Both of them are quite old version, but still usable. So download it, and run CCK. Select Menu File>Open, then click Choose File .... Open bytecode1.class file.
From menu above, select Code from main methods. The right side panel will display content of main method.
You see the pattern? System.out.println is consist of getstatic, ldc, and invokevirtual.
So what I'm going to do now is remove two System.out.println routines. This should be tricky, when you are trying to remove getstatic routine, it will say "cannot remove...still has targeter".
If you select "Line Numbers" from left panel, it will show you dependencies from source code to bytecode. Remove the coresponding line where you want to remove the code.
Assume I want to remove the last two System.out.println routines (The first won't removed), that is with offset 8 to 21, so in the "Line Numbers" section, I will remove source index 4 and 5 that link to the offset 8-21.
if you done that, go back to Code, and then delete offset 8-21.
Done. save it with File>Save Menu.
That's enough for today, I will cover another technique next time.
Backup MySQL Database From Java Part 3
This is the last part of MySQL Database Backup from Java tutorial series.
In previous post, already discussed about how to get the backup streams from Process Object to String variable. What are we going to do now is, saving the variable to a file. You should consider about compressing the output file if your data is somewhat like enterprise one.
To compress the file, there is ZipOutputStream class to utilize it.
You need to call setMethod() function to define what will you do with the stream. One of the option available is ZipOutputStream.DEFLATED, meaning you will compress the stream, another is ZipOutputStream.STORED, meaning you just store the stream without compress.
Another function to call if you choose DEFLATED method is setLevel(), which is setting the compression level. The value range from 0 (Deflater.NO_COMPRESSION) to 9 (Deflater.BEST_COMPRESSION).
It will looks like:
First, we collect all the data needed, see previous post
In previous post, already discussed about how to get the backup streams from Process Object to String variable. What are we going to do now is, saving the variable to a file. You should consider about compressing the output file if your data is somewhat like enterprise one.
To compress the file, there is ZipOutputStream class to utilize it.
You need to call setMethod() function to define what will you do with the stream. One of the option available is ZipOutputStream.DEFLATED, meaning you will compress the stream, another is ZipOutputStream.STORED, meaning you just store the stream without compress.
Another function to call if you choose DEFLATED method is setLevel(), which is setting the compression level. The value range from 0 (Deflater.NO_COMPRESSION) to 9 (Deflater.BEST_COMPRESSION).
It will looks like:
Explanation:byte[] data = getData().getBytes();
byte[] routine = getRoutine().getBytes();
File filedst = new File("example file.zip");
FileOutputStream dest = new FileOutputStream(filedst);
ZipOutputStream zip = new ZipOutputStream(
new BufferedOutputStream(dest));
zip.setMethod(ZipOutputStream.DEFLATED);
zip.setLevel(Deflater.BEST_COMPRESSION);
zip.putNextEntry(new ZipEntry("data.sql"));
zip.write(data);
zip.putNextEntry(new ZipEntry("routine.sql"));
zip.write(routine);
zip.close();
dest.close();
First, we collect all the data needed, see previous post
Initiating the file stream and the zip streambyte[] data = getData().getBytes();
byte[] routine = getRoutine().getBytes();
File filedst = new File("example file.zip");
Write the fileFileOutputStream dest = new FileOutputStream(filedst);
ZipOutputStream zip = new ZipOutputStream(
new BufferedOutputStream(dest));
zip.setMethod(ZipOutputStream.DEFLATED);
zip.setLevel(Deflater.BEST_COMPRESSION);
Close the stream.zip.putNextEntry(new ZipEntry("data.sql"));
zip.write(data);
zip.putNextEntry(new ZipEntry("routine.sql"));
zip.write(routine);
zip.close();
dest.close();
Backup MySQL Database From Java Part 2
After we know how to separate the backup process between data, stored routine, and trigger, next thing to do is implementing them in Java.
Consider the following code:
Please note the BUFFER variable, you may customized for your need. Just remember, the bigger value, consumes more heaps but decrease loops. The smaller value, consumes less heaps but increase loops.
The next step is how to store them.
Consider the following code:
Now we have two functions, one for dumping only data, the other dumping stored routines and trigger. Both of them will return String variable, that is your backup.private int BUFFER = 10485760;
private String getData(String host, String port, String user,
String password, String db) throws Exception {
Process run = Runtime.getRuntime().exec(
"mysqldump --host=" + host + " --port=" + port +
" --user=" + user + " --password=" + password +
" --compact --complete-insert --extended-insert " +
"--skip-comments --skip-triggers " + db);
InputStream in = run.getInputStream();
BufferedReader br = new BufferedReader(new InputStreamReader(in));
StringBuffer temp = new StringBuffer();
int count;
char[] cbuf = new char[BUFFER];
while ((count = br.read(cbuf, 0, BUFFER)) != -1)
temp.append(cbuf, 0, count);
br.close();
in.close();
return temp.toString();
}
private String getRoutine(String host, String port, String user,
String password, String db) throws Exception {
Process run = Runtime.getRuntime().exec(
"mysqldump --host=" + host + " --port=" + port +
" --user=" + user + " --password=" + password +
" --compact --skip-comments --no-create-info " +
"--no-data --routines " + db);
InputStream in = run.getInputStream();
BufferedReader br = new BufferedReader(new InputStreamReader(in));
StringBuffer temp = new StringBuffer();
int count;
char[] cbuf = new char[BUFFER];
while ((count = br.read(cbuf, 0, BUFFER)) != -1)
temp.append(cbuf, 0, count);
br.close();
in.close();
return temp.toString();
}
Please note the BUFFER variable, you may customized for your need. Just remember, the bigger value, consumes more heaps but decrease loops. The smaller value, consumes less heaps but increase loops.
The next step is how to store them.
Backup MySQL Database From Java Part 1
Nothing new with this topic because it has been asked and answered so many times in forums and mailing-lists, but still it's a quite hot topic.
The backup method I'm using in this blog entry is using mysqldump utility that bundled with MySQL download. Mysqldump has several useful argument that we can make to customize our backup files, such as dumping data, triggers, stored routines in separate files.
For example, backing-up data only should be,
and backing-up stored routine and triggers should be,
For complete argument list of mysql dump, click here.
The next step is how to implementing mysqldump in Java
The backup method I'm using in this blog entry is using mysqldump utility that bundled with MySQL download. Mysqldump has several useful argument that we can make to customize our backup files, such as dumping data, triggers, stored routines in separate files.
For example, backing-up data only should be,
mysqldump --host=myserver --port=3306 --user=william --password=secret --compact --skip-comments --complete-insert --extended-insert --skip-triggers mydataand backing-up stored routine and triggers should be,
mysqldump --host=myserver --port=3306 --user=william --password=secret --compact --skip-comments --no-create-info --no-data --routines mydataFor complete argument list of mysql dump, click here.
The next step is how to implementing mysqldump in Java
My Yahoo! Google NewsGator Stumbleupon PluckIT Rojo Bloglines My AOL Blogrolling ENewsblog NewsIsFree NetVibes del.icio.us Technorati Digg This FeedMailerOr if you prefer use one of our linkware images? Click here
Report Blog Listing - 
Refer Blog Listinghttp://www.bloggingfusion.com/how-to-for-idiots-blog-directory-3012.html