Percona Live MySQL User's Conference, San Francisco, April 10-12th, 2012 Book: "Pentaho Kettle Solutions", Matt Casters, Roland Bouman, & Jos van Dongen, Wiley 2010 Book: "Pentaho Solutions", Roland Bouman & Jos van Dongen, Wiley 2009

Sunday, June 03, 2007

What MySQL can do to enter the off-line Web

Disclaimer - views expressed in this blog (and this entry) are my own and do not necessarily reflect the views of MySQL AB

Ever since I wrote my blog entry about Google Gears and the query tool for the browser embedded offline Google Gears database service, I have been wondering how MySQL might fit in here.

I have heard an idea to write a MySQL storage engine for SQLite and although I do not think this is necessarily a bad idea, I don't think it will be immediately useful for typical applications powered by Google Gears. Personally, I think the following things might be of more use:

A modification of the Google Gears browser extension that allows a local MySQL database to be used instead of the embedded SQLite database

This would be interesting in case you would need a particular MySQL feature that is not available in SQLite. I'm thinking mainly of stored procedures but more so about replication.

The local MySQL server could be setup as a master that replicates to a remote slave. The remote slave would be hosted by your company and that backend would somehow merge all the private slaves from its employees into the corporate database.

Of course, the idea for the SQLite storage engine could fit here too - a local MySQL Server with the SQLite storage engine could be set up as master to replicate to the corporate slave.

A Google Gears Worker Pool application that synchronizes the embedded SQLite database with the remote, corporate MySQL database

This idea would of course require some server component that may be accessed by the Worker application, and it would presumably take the form of a web-service. This could be implemented either as a piece of middleware that sits in a HTTP server or as a component that is part of the MySQL server itself. This product would achieve in part the same functionality as replication from the client's master to the remote slave but I think there are a number of advantages here.

First of all, the implementation would not be intrusive to Google Gears - users can still use the ordinary Google Gears extension, and MySQL AB does not need to ensure that their modifications to the browser extension are compatible with future Google Gears developments.

Second, this application could in principle allow a true synchronization rather than just replication. In other words, it could pull data from the corporate database as needed, and push local modifications to be merged with the remote database. Obviously the possibilities here would depend on the flexibility of the server-side component accessed by the worker pool application.

Thirdly, users would not need to install an extra MySQL server on their webclient host. Arguably this could be seen as a disadvantage for MySQL AB as it would result in fewer installations of MySQL Server, but personally I don't think this is the right angle. I think that for most people, an extra MySQL Server that sits on their machine just for the purpose of replication will not be very attractive.

To me, using the Google Gears Worker Pool to drive synchronization seems like the most sensible choice that fits good with the whole offline-web idea. But what do you think? Can MySQL play a role in the offline web like SQLite can now?

8 comments:

Tom Gleeson said...

Instead of inventing a new web service why not use something like Amazon's S3? Place triggers on tables to create 'redo/flashback' log entries; a workerpool module syncs this to S3; a MySQL instance wakes up (maybe use an EC2 instance booted up when needed!)and consolidates the data placed on S3 and maybe lays down more data to be published back to the clients. The possibilities are endless, this technology is getting my creative juices flowing. SQLite rocks, MySQl show embrace this little gem of a product and make integration with it as easy as possible.
Tom

Anonymous said...

This kinda sounds like a hammer searching for a nail.

Roland Bouman said...

Tom: Interesting...I gotto think about that.

Anonymous: Well, it may sound like that, but think about it...it won't be no good if the data stays on that client forever right?

The off-line facilities offered by Google Gears could be used for completely off-line applications, but I don't think they were intended for that purpose.

I see all kinds of possibilities for applications that are sometimes connected, but which are likely to lose to connection, mostly due to mobility, or outage of a (wireless) network. The application can be designed so that a lot of the issues associated with a broken connection can be solved by failing over to the offline datastore.

However, eventually, there needs to be some way of consolidating the locally stored data - there must be synchronization. That is where my idea for a worker pool application to synchronize with a remote MySQL server kicks in.

sheeri said...

See, I think this has great uses. The whole idea behind offline work is that you can sync, do work offline, and resync when you get back. Consider offline e-mail, for instance. You can compose and queue mail to send, and it gets sent and new mail retrieved when you go back online.

I would love a way to sync something like my Google mail. Or even something like my livejournal, my friends' livejournal. I'd love a personal copy of the aggregate feed on planetmysql.com. Or simply keep an always-current, always available copy of the MySQL manual.

Andrew Dashin said...

Why not? It is just needed to create a lightweight mysql edition. But as for me I think this problem can be more complicated than it could look like.
But, is it really needful?

Andrew Dashin said...

Hi, Roland.

I have found an interesting project - http://starkravingfinkle.org/blog/2007/09/hello-js-ctypes-goodbye-binary-components/

Probably you'll be interested...

Stephen Francis said...

I am looking for exactly this kind of thing. Did it go anywhere?

Roland Bouman said...

Hi Stephan!

Google Gears (with embedded SQLite) is actively maintained by Google. Some google applications (like gmail) use it to support offline applications. The Google Chrome browser includes Google Gears by default.

I don't know of any fork of gears that allows you to use MySQL. That said, google gears is open source (BSD license if I am not mistaken) so you could give it a go to embed another database.

May I ask why you need Google Gears with MySQL instead of SQLite?

regards,

Roland