Blog Archives

Transferring from MYSQL to Mongo DB

I recently wanted to transfer from MYSQL to MONGODB so I developed some routines to make the transfer. It’s pretty self explanatory but allows you to dump tables and delete data as required.

package mongo;
import java.math.BigDecimal;
import com.mongodb.BasicDBObject;
import com.mongodb.BasicDBObjectBuilder;
import com.mongodb.DB;
import com.mongodb.DBCollection;
import com.mongodb.DBObject;
import com.mongodb.MongoClient;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
 * @author Paul Clevett
public class Mongo {
 * @param args the command line arguments
 public static void main(String[] args) {
 try {
 // To directly connect to a single MongoDB server (note that this will not auto-discover the primary even
// if it's a member of a replica set:
Connection conn;
 try {
 // create a java mysql database connection
 String myDriver = "com.mysql.jdbc.Driver";
 String myUrl = "jdbc:mysql://***SERVERIPADDRESS****/**INSERTDATABASE***";
 conn = DriverManager.getConnection(myUrl, "***INSERTUSERNAME***", "**INSERTPASSWORD***");
// your prepstatements goes here...
 } catch (Exception e) {
 System.err.println("Got an exception! ");
MongoClient mongoClient = new MongoClient("***MONGODBIPADDRESS**", 27017);
 DB db = mongoClient.getDB("**MONGODBDATABASE****");
 char[] password = "r00t21".toCharArray();
 boolean auth = db.authenticate("root", password);
 dumpTable("alarms", conn, db);
 dumpTable("sites", conn, db);
 dumpTable("users", conn, db);
} catch (UnknownHostException ex) {
 System.out.println("Unkoown Host " + ex);
private static void deleteRecords(String sTableName,DB db) {
 BasicDBObject query = new BasicDBObject();
 DBCollection col1=db.getCollection(sTableName);
private static void dumpTable(String TableName, Connection conn, DB db) {
 CreateCollection(TableName, db);
 DBCollection coll = db.getCollection(TableName);
 Statement s = null;
 ResultSet r = null;
 try {
 // get the iVolt units table
 s = conn.createStatement();
r = s.executeQuery("select * from " + TableName);
} catch (Exception e) {
 try {
 while ( {
 Map fields = new HashMap();
for (int i = 1; i < r.getMetaData().getColumnCount() + 1; ++i) {
 String sColumnName = r.getMetaData().getColumnName(i);
if (r.getObject(sColumnName) != null) {
if (r.getObject(sColumnName).getClass() == BigDecimal.class) {
 fields.put(sColumnName, new BigDecimal(r.getObject(sColumnName) + "").doubleValue());
 } else {
 fields.put(sColumnName, r.getObject(sColumnName));
InsertRecord(createRecord(r.getMetaData().getColumnName(1), r.getObject(1) + "", fields), coll);
 } catch (Exception e) {
 System.out.println("Fail " + e);
private static BasicDBObject createRecord(String tablename, String DBName, Map keyvalues) {
 BasicDBObject result = new BasicDBObject(tablename, DBName);
 Iterator<Map.Entry<String, String>> i = keyvalues.entrySet().iterator();
 while (i.hasNext()) {
 String key =;
 result.append(key, keyvalues.get(key));
return result;
private static void InsertRecord(BasicDBObject doc, DBCollection coll) {
private static void PrintDatabaseNames(MongoClient mongoClient) {
List dbnames = mongoClient.getDatabaseNames();
 for (int i = 0; i < dbnames.size(); ++i) {
 System.out.println(dbnames.get(i) + "");
private static void PrintCollectionNames(DB db) {
 System.out.println("**** Collection Names ****");
 Set<String> colls = db.getCollectionNames();
for (String s : colls) {
private static void CreateCollection(String CollectionName, DB db) {
DBCollection collection;
 if (db.collectionExists(CollectionName)) {
 collection = db.getCollection(CollectionName);
 } else {
 DBObject options = BasicDBObjectBuilder.start().add("capped", false).get();
 collection = db.createCollection(CollectionName, options);

Setting up MON for mysql

Just a short post. I was trying to get two database servers to check each other for the running database. Here are the Parameters (I actually had to look at the package source to get them!).

msql-mysql.monitor --mode MySQL --database=dbname --user=user --password=pw

Tungsten Replicator setting up and Gotchas

Tungsten Replicator site Clicky

So we have a big MYSQL server with mission critical data on it.

I want to replicate it accross to another MYSQL database, they are both ubuntu servers with Percona MYSQL database.

1. Preparation

Master – Install RUBY, JAVA,

Slave – Install RUBY, JAVA

edit the hosts files on both machines in etc/hosts and add the other servers in each one and reference the IP address – just makes things easier.

2. SSH

Make sure you set up SSH so that you can ssh from one server to the other WITHOUT using passwords.


make sure all ports required 10000,10001 are available (particularly problematical PLESK, WEBMIN a whole host of things like these ports) on all machines. One server actually had a PLESK tool on it even though plesk was not installed!!


make sure everything is set up and restore a backup of the database before you start.

5. Helper scripts

Set up your tungsten. You’ll need to edit to make sure your nodes are correct and USER_CONFIG as per the tungsten documentation. – put in the tungsten folder e.g. tungsten-replicator-2.1.0-343

VERBOSE=1 ./cookbook/install_master_slave ./cookbook/ (actually I renamed this to tungsten and did a chmod +x tungsten)

if [ "$1" == "help" ] || [ "$1" == "" ]; then
 echo "Commands"
 ls installs/cookbook/tungsten/tungsten-replicator/bin/
installs/cookbook/tungsten/tungsten-replicator/bin/$1 $2 $3 $4 $5

So you can type

./tungsten replicator status

To run commands.

MYSQL Workbench SE

Well, I downloaded the community edition of MYSQL workbench and stopped pretty quick when I realised you needed the standard edition to reverse engineer database structures….after a lot of paperwork and purchase orders I now have the full SE version.

Good Points

It’s quick
It looks great
It has a LOT of options
Reverse engineering the database was faultless and really made it quick for me to do a database diagram.
PDF export is standard

Bad Points

On a system with 2 screens work bench gets very confused about where the middle of the screen is. Us programmers have terrible double vision so we have two screens, one for the source and one for the run. So I had to switch back to using one screen then work bench sorted itself out.

Printing, would it print in landscape? no, in the end I popped out the diagram to PDF first then printed it from there.

Is it worth £60 a year? yes, without a doubt. It’s not perfect by a long shot but runs pretty well. Only other complaint is (SUN ARE YOU READING THIS?) I have to run it under Windoze but my operating system of choice is (SUN) SOLARIS. So the SUN Workbench really needs to work on the SUN operating system since it’s owned by SUN.


MySQL is Not pronounced MY SQL

Ok – this is not exactly hot news. But I went to an EXCELLENT seminar on Friday last week with Sun and MYSQL. Which according to the founder is pronounced “MESQL” as the “MY” is the name of his daughter. Well, translated it’s “MY” not “ME”, I tried saying MESQL but it makes me sound like a Texan. (sorry if you Texan). So I’m sticking to MYSQL.

They spent a lot of time trying to convince us that the open source software model (that Sun use) is the way to go for software. You know, install it, make it work – if you want support pay for it. I liked this for a number of reasons and didn’t like it for other reasons.

Why I Like Open Source

– Customer and you can get the software, run it, test it, see if it’s what they need and then upgrade to a commercial license and support whent they are happy
– Customer can get the “best of breed” and mix and match
– Source code is available if supplier goes bust (ok, I know Sun & MYSQL are not going bust but it’s a thought)
– For the developer its less pressure than working on a project where you are up against deadlines for a client. However this also means the development may be less focussed on solving a particular problem, one of the comments was that Open Source payroll “Isn’t mature enough yet”, well that’s fine but its not cheese. It won’t just mature, someone, somewhere has to fund the development (see bad points). In this model:- 

a. Customer pays a license fee to use the commercial version (they call it Enterprise in the Open Source World)
b. Customer pays a support fee to use the commercial version – or hires in house people to support it
c. Customer pays when they are happy or at least ready to get professional help.
d. I like the 15 minute rule, if I can’t install it and have it running in 15 minutes (even though it mike take 2 hours) to download then its probably not ready.
– Open source is better for the smaller company because they can get other developers to contribute and people to file bug reports for example when something is not working.
– Open source means that developers get recognition in the developer community for work they have done especially if it is pioneering. Developers need encouragement and sometimes in a small team they don’t get it. Especially being the only developer in a company, it’s unlikely that those outside of the software engineering world have any understanding about the elegance and effort put into a peice of software, I’ve seen bad software that looks great but is terrible and great software that looks terrible but works like a dream. (often us developers need to hire a designer!).

Bad Points about open source

– Developers need to feed their kids – so unless someone funds the development then Developer doesn’t feed kids until someone pays a support contract, most developers are not rich enough to live on thin air.
– Unless someone funds it a project may take longer to develop than with the conventional hire a developer model and not be “quite” what the customer was looking for in the first instance. Thus the best open source software is the GENERAL stuff, that is not focused on doing a specific job, like payroll or transport. Because from the base platform you can build up to do something specific.

So, it’s scarey and nice at the same time. Yes, I love open source but Yes, I need to be paid to develop so I can feed my family (and they are hungry, especially the cat).

So what about MySQL itself, well it’s great, thats all there is too it. I love the way I can change database engines PER TABLE which means that for instance if I have an archive I can just use an archive database, I love the clustering, I even like the logo. Good job.

Sun buying it? Well I wrote an article about this on this blog anyway so you can trawl through if you like and read it.

Great Seminar, well worth going, only thing was, not enough freebies. Good grief Sun I travelled an hour accross London. I expect at least a CD and T-Shirt, not just a pen! A PEN!, ok they were giving away an IPhone, which was nice but I never found out who got that. I guess one of the big banks who were there.

If you fancy doing the presentations to yourself you can download them here.

Connection to two MYSQL databases through glassfish at once.

This one had me foxed for a bit, I had to connect to two different MYSQL databases in the same app.

The problem was that the MYSQL driver that I had installed with Glassfish was an old one. As soon as I updated it and then changed the database links in Netbeans (so they would roll out to Glassfish) and then it works like a dream, in fact I noticed that the latest MYSQL driver is considerably faster being that I have a remote database coming into a netbean and out to the client.

On the topic of Netbeans, they are WONDERFUL. I love writing code in beans and having it pull the data, sort it out and send it to whatever-client-I-so-decide whether web based, app based or mobile (or something else).

Lastly I’ve installed the latest Woodstock web componants into NB for a new project I’m doing and they are really great.

Sun Microsystems and MYSQL get Married

Wedding bells are in the air. But what does this mean?

The marriage of Sun Microsystems  and MYSQL means quite a lot. It is a very strategic move that Mr Msoft should have thought of. With Netbeans fast becoming a favorite amongst developers it means MYSQL could become the defacto back end database standard, leaving Mr Msoft sadly not invited to the reception.

For a developer learning Netbeans from .net is not rocket science, neither is working from Mr Msofts database server over to sunny MYSQL. So we could see a new breed of developer who’s first choice is always the MYSQL/Netbeans platform for anything from a small website up to a full enterprise system with glassfish.

Perhaps it could even become the point that servers will be rolled out with Ubuntu Linux on since we don’t really care what we run our java/mysql on – it makes no difference.

One of the main objections to java as a language and development tool is the cost of ownership is higher in the long run because you have to have better programmers, oh, sorry, did I say better, I meant more expensive, so you’re initial rejoicing at getting a cheap start up base becomes dogged by the fact you’re spending $100 US a day more on programmers to roll out the “Widget Stock System”. (man I’m biased).

Times are changing and I think for the better. Netbeans is a true RAD tool and now that MYSQL is part of the family you can build an enterprise system with Mr Msoft even getting a look in, except perhaps on the client machines operating system unless the user is running Linux or Mac.

So every blessing to your new marriage and I hope the honeymoon lasts forever. The nice thing is – we are all invited, at no cost.