MySQL Gets Functions in Java

Java Coward writes "Eric Herman and MySQL's Brian "Krow" Aker have released code to allow the DBMS MySQL to run Java natively inside of the database. The code allows users to write functions inside of the database that can be then used in SELECT/INSERT/UPDATE statements. So when will someone do Ruby?"

Apparently somebody already did

[GillBates0]

So when will someone do Ruby?

MySQL Ruby Interface [google.com]
Google Cache here [216.239.57.104].

Google's your friend [google.com]

judf uses the Java Native Interface...

[tcopeland]

....to start up the Java VM. From judf.cc:

// Create the Java VM
jint res = JNI_CreateJavaVM (&jvm, (void **) &env, &vm_args);

Embedding a Ruby interpreter would reduce startup time, probably.

Re:Now how about.

[jamie]

Replicate to a slave DB that isn't used for anything but backups. On the slave, you can do a 'mysqldump -x'. That'll block updating while it does the write, but you won't care. The only problem arises if your hardware is too slow to catch up replication before the next time you do the dump, in which case you're kind of screwed anyway. This works on both myisam and innodb tables.

Re:waaaaiiiit a minute...

[tcopeland]

> you are storing java functions/objects
> in the database?

Nope, they're external to the DB.

> program your own functions like
> insert/modify/etc in java

You can program functions in Java, and then call them from MySQL queries. From the README:

To run the sample Java DBMS function
mysql> SELECT judf("test/GreenBar", COLUMN1, COLUMN2) FROM foo;

Nifty!

Re:Keep this out.

[Blackknight]

I care because when you have 200 shared hosting accounts on one server all it takes is one idiot to load things down.

Most of the time we detect who it is and suspend their account, but I still wouldn't want them running java code inside mysql.

Oracle already does this...

[XaXXon]

The thing no one seems to have mentioned is that Oracle already does this and has for many years. I can't find any docs on it off-hand, but I know you can just drop a .jar file into Oracle and it will let you do similar stuff. This is nice because it lets you use a common language for doing your stored procedures instead of learning a different language for each database (e.g. Oracle uses PL/SQL).

People who are saying "what's the use of this" or "This is just going to bog down the database" most likely have never worked in the industry. Stored procedures are a very common part of large systems and adding this functionality to MySQL will go a long ways in promoting MySQL use in bigger companies.

Re:Java in the DB - very, very bad idea

[tcopeland]

> Is mySQL process going to start the
> whole new JVM on every hit?

No. Look at judf.cc. There's a judf_init and a judf_deinit. judf_init starts up the VM and hangs on to it in here:

static JavaVM *jvm = NULL;

Seems to make sense - start the VM once, call it as many times as you want.

Re:Java in the DB - very, very bad idea

[laird]

"The ability to write stored procedures in Java has been in Oracle for some time but I still cannot figure out why anyone would do that."

Here are some reasons:

1) Java runs _way_ faster than PL/SQL. This is because lots of people have been working in making Java run very efficiently compared to PL/SQL. I've seen people port from PL/SQL to Java stored procedures justified purely by increased system performance.

2) It allows for consistent coding between database-resident and application server-resident code. This means that you don't need to train people in two very different languages to get work done.

3) It allows for code portability between the database and application-server. This lets you tune performance. For example, if you have some code that does tons of database I/O, it may run far more efficiently inside the database rather than accessing the database across a network.

I don't know how well the MySQL guys integrated Java yet, but in Oracle it's pretty wonderful compared to using their weird, slow, proprietary language.

Re:Oracle already does this...

[KenSeymour]

Yes Oracle has this. IIRC so does DB2.

One place where I worked, they had a bunch of Java stored procedures doing things you could have done in PL/SQL.
They later re-wrote them because the performance is so much worse.

There are, however, things it might make sense to do in a Java Stored Procedure. Publishing a message using JMS from a trigger is an example.
I am not sure if Oracle has created utility packages so you could do it from PL/SQL.
But having a Java stored procedure in this case would allow you to use the same message class that is used by the subscribers to the message.

I don't think this will bog down non-Java users of MySQL.

Years ago, when I was using DB2, I noticed that they had external stored procedures that could be written in nearly any language. C, Java, COBOL, you name it.

Re:Now how about.

[Cajal]

There needs to be someway of doing online back ups of MySQL with out spending money.

Why not just use PostgreSQL? It's had hot-backup of tables for years.

Re:Now how about.

[vt0asta]

Number one big time issue is how do you get the changes that occured to the files during the backup so that the database is consistent.

It's trivial really. Announce to the database that you want to perform an online backup. This marks the database in a special mode that lets it know that data writes are going to be at the block level vs. row level.

Also data modifications are still written to the datafiles and also are always written to special files (simultaneously and at the sime time :-p). These files are called redo logs, and the database cycles through a set of them. When a database is running in a condition in which it can allow online backups, it writes a copy (called an archive) of the redo log file to a special directory before that redo log file is used again.

During an online backup you backup all of the "archived" redo log files for the entire time it takes you to backup the data files. Once you backup the datafiles, and the archived redo log files, you can then take the database out of backup mode, and start doing row level modifications again.

When it's time to restore, the database sees that the restore datafiles are in an incosistent state but that's "ok", because it also knows you have competently saved all of the "missing" changes. The database rolls through the archived log files applying the changes that are neccessary until the database is in a consistent state.

PHP UDF

[TheTomcat]

There's similar functionality s/java/php/g, here:

http://talks.php.net/show/phpquebec/27 [php.net]
http://www.sklar.com/page/article/myphp [sklar.com]

S

Oh come on, NOT stored procedures

[Jhan]

This is all about writing functions, like no_null in

select no_null(oftennullfield)||" "||otherfield from...

MySQL has always had an expansion framework for adding you own functions to the SQL, it's just that traditionally you had to have a compilable language to do that. Now, you can use Java methods as well. (Still not a bright idea IMHO, but...)

Re:That's great

[krow]

Stored Procedures are in 5.0.

Re:Now how about.

[Anonymous Coward]

For MyISAM and InnoDB tables, this is already possible. the mysqldump utility has arguments that allow you to do the dump within LOCK/UNLOCK TABLES statements, or within a single transaction. So, dump all InnoDB tables within a single transaction, and then all MyISAM tables within a lock. This gives you a consistent snapshot of your database, with a possible rift between your MyISAM and InnoDB tables, if you have some of both. But if there are data integrity constraints that span tables of both types, perhaps more of your tables should be InnoDB! mysqldump does not require the database to be shut down, so you can do this on the fly. There's a miniscule slowdown while in progress, of course, but it's a very fast method. Then you are left with SQL files that can be read back into MySQL anytime you want.

Re:Now how about.

[HarrisonFisk]

You actually can take online backups of InnoDB without buying the InnoDB Hot Backup tool. The non-free tool allows you to take online *binary* backups, but if you want to take an online data backup you can do so using mysqldump with the --single-transaction method. This will work basically identical to how you take the online backup with pg_dump. It takes a snapshot in time using the normal transaction isolation level semantics and dumps it into a plain text file. Keep in mind that dumping the data is slower than doing a binary backup (also for restore) but that is true for any database. If you just need online backup capabilities, it is built in. If you need it to be faster than you can choose to buy the online binary backup feature.

PHP would have been a much better choice

[acostin]

It's a pity the MySQL guys are trying to reinvent Oracle.

Java in the database is so 98, that makes me wonder why all companies are trying to conquer the world with the same approach..

PHP would make just a more natural choice for stored procedures, and the approach in MySQL should be to allow stored procedures, triggers and referential integrity (this should be native like in PostgreSQL).

It's weak typed and has a pretty standard MySQL api. However, a metalanguage over PHP to minimize the API and to make it transparent would be nice.

Alexandru

PHP has this for over a year

[MelloDawg]

PHP Dev David Sklar implemented this over a year ago: http://www.sklar.com/page/article/myphp [sklar.com]

Re:PHP would have been a much better choice

[pHDNgell]

It's a pity the MySQL guys are trying to reinvent Oracle.

If that's their motivation, they're missing by far. Postgres is *way* closer to oracle.

Postgres does have a plPHP as you're describing. I wouldn't say that being a weakly typed language or having a standard API for talking to a particular type of database make for a good language.

But postgres allows you to make that decision for yourself. Stored functions and procedures may be written in any language and it's easy to plug them in.

Re:Hmmmm

[Corhonio]

SQL is a high level language for querying/getting stuff, it is not a programming language.C and assembly are used for programming at different levels.Mixing the previous two is considered bad software engineering practice and should be used only when it's really needed.
The puprpose of SQL is not to worry about how to get but what to get.Injecting function calls inside your queries will make your and others life definitely harder especialy in the large projects you mentioned.
I am quite confident that the above will harm performance ass well.
Databe functions(!=injecting function calls inside queries) are definitely usefull. MS access is for SOHO/small companys use.
Chris

Re:Oracle already does this...

[_fuzz_]

The interesting thing about Oracle is that it doesn't use a standard JVM. They call it "way-ahead-of-time" compilation instead of "just-in-time" (JIT). The java bytecode is compiled into native code and optimised at the time the stored procedure is installed. Pretty good idea if you ask me.

Re:Where is MySQL anyways?

[jadavis]

PostgreSQL has more enterprise features, but it's not used as much as MySQL. It seems pretty solid, though. We toyed with a bit, but my boss decided to go with MySQL mainly because he had heard of it before.

Yeah, postgres has always had a recognition problem. I like it because of the data integrity features, and the only feature I would really like is point-in-time-recovery (incremental backup, whatever you want to call it).

It's strange how much recognition matters, even when postgres runs the .info registry, the .org registry, and I think the american chemical society has a database >1TB. I'm a postgres fan, so it's a little disappointing to see it rejected like that. I think it will help a lot when they get the windows port out.

Re:Keep this out.

[phatsharpie]

The usage of Java for online animation has really dropped with the growth in Flash. In fact, for a long while I uninstalled the Flash plugin from my browsers because I was so sick of the ads.

However, Java has become more relevant in the past 3 to 4 years as a server-side language. J2EE is an excellent web application platform, and tremendous growth has occurred in that arena. Also, J2ME is also becoming more relevant with the growth in cellphones and PDAs that supports it.

-B

Re:Keep this out.

[gumbi west]

The other replys have tried to say this but missed the point. This is an add-on. Not a part of MySQL. You will notice it is not distributed by MySQL.

As they say in my home country, (to be pronounced with a crazy accent) "If you don't want a monkey, don't buy a monkey."

Re:Java in the DB - very, very bad idea

[laird]

Well, in my experience Java stored procedures are faster than PL/SQL stored procedures, and Oracle says that that's typically the case, but I don't have benchmarks.

I agree that the strongest argument for running Java inside the database is because Java ia full featured programming language. Your example with string parsing sent shivers down my spine.

Embedded Ruby is not thread-safe

[jdoeii]

So when will someone do Ruby?

Not soon. Ruby cannot be embedded in a threaded application without using a giant mutex. Only one thread at a time can call Ruby interpreter.

Re:That's great

[Tassach]

The fact that /. uses MySQL is irrelevant. ./ is not a mission-critical system -- it too is, at the end of the day, nothing but a toy. It's not a serious business system. There's no significant consequence if a message gets lost or garbled; it doesn't matter that transactions are processed in a certian order; it doesn't really matter if it's security is compromised. In fact, ./ has very minimal requirements and there are a lot of other products which would be equally adequate as well: a flatfile system like maildir, an ISAM system like dBase, or a pseudo-relational database like MySQL or MS-Access. If you're building a toy system, a toy database might be sufficient.

MySQL lacks several essential features which necessary to implement a proper client/server application -- stored procedures and transactions most of all. Stored procedures are essential for several reasons: security, performance, and efficiency. Stored procedures have better performance because they are pre-compiled; there's significantly less overhead to call an SP than there is to process an ad-hoc query. Stored procedures make programming more efficent because you can write a single, complex set of queries and re-use it across multiple clients.

Perhaps most importantly, stored procedures have several security advantages. If your database allows ad-hoc queries to run against it, it is vulnerable to an injection attack -- an attacker could potentially run any SQL query he wants against your database (EG: update Account set balance = 1000000 where AccountNumber = 123456). A stored procedure acts similarly to a setuid/setgid program in unix, in that it runs with it's owner's permission instead of the user's. This means that you can allow a user to modify a table in a single, very specific manner that they would otherwise not be allowed to touch. Good security is achieved by defense in depth, and stored procedures give you an additional layer of security.

If cost is a concern, then there are alternatives to Oracle: Postgres is signifiantly closer to being a real database than MySQL; and there are zero-cost licenses available from several of the commerial database vendors. Most notably, Sybase 11.0.3.3 for Linux [sybase.com] is available at no cost for any purpose. While a little dated compared to more recent releases, it is still far more mature than any open-source database. Open-source advocacy (zealotry) should never get in the way of making sound engineering decisions.

Re:Krow

[krow]

Auditing the code should be trivial for anyone (judf is actually quite small).

I resigned because I had been working on Slash for 3 years and wanted to do something new. I rather like the people who run this site, and still follow the development of it and point out feature improvements from time to time. I no longer develop the code myself, except for a few sites that I happen to help with.

Just to poke another hole in this, if I was fired would I still be an author on the site some 7 months later? I think not.