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?"

Comments

Now how about.

[DAldredge]

Now how about a way to do online backups of the new table types with out having to buy a license to do it?

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:Now how about.

[DAldredge]

If you have have a second system to run as a slave that may work. In quite a few realworld(tm) situations you don't have that luxury.

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

Re:Now how about.

[ErikZ]

Why don't you just support 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.

[DAldredge]

That is what I use.

It is just that the MySQL fanboys 'forget' to mention the little fact of the cost of they backup tools when they talk about how much beter MySQL is that 'DATABASE X'

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.

Re:Now how about.

[Spirilis]

What about recopying the master DB to the slave in the event that the slave becomes somehow inconsistent (like if a developer stupidly writes to the slave DB by accident)?

I know this is an exceptional situation and should not occur often, but... it'd still be nice to avoid downtime while doing this.

On the other hand, for minimal downtime, couldn't Linux LVM's snapshot volumes work favorably for this?

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.

Re:Now how about.

[bmedwar]

please don't use the term slave . :-)

Re:Now how about.

[PalmKiller]

oh my, what silliness you link to. I guess next they will be wanting more rights for those slave system

Re:Now how about.

[Anonymous Coward]

There doesn't need to be a free way of doing everything. Shell out the $$$$ you cheap fuck. Something that saves your job should not be free!

Re:Now how about.

[drix]

Yeah, fuck paying for a feature that took a lot of work to implement and that you want badly. The nerve of those assholes, charging money for their work.

A real man would other get off his ass and code it himself, or support the damn developers who do. Note "Bitch on /." is not listed. Free software is turning the world into a bunch of whining ingrates.

Re:Now how about.

[Electrum]

If you have have a second system to run as a slave that may work. In quite a few realworld(tm) situations you don't have that luxury.

Then run a second copy of MySQL on the same machine.

Re:Now how about.

[DAldredge]

Now that is an efficent use of resources.

Re:Now how about.

[gumbi west]

Yeah, and Oracle can also make that neat vacuum sound in your pocket. That's my favorite feature.

Re:Now how about.

[NuShrike]

offtopic, but Postgresql has had support for years also.

Re:Now how about.

[Sxooter]

Hehe, that's what having a windows port will do to your database I guess. Make you used to using spare machines all over the place. :-)

Re:Now how about.

[Sxooter]

Correct me if I'm wrong, but doesn't that basically lock your whole database against writes during the backup? Just wondering.

Re:Now how about.

[Sxooter]

I use Postgresql and prefer to it to MySQL, but this is neither the time nor the place to be such a pain in the arse. Do you really think the average MySQL user is gonna be converted like this? The Postgresql crowd is usually a bit more subtle than this. There are plenty of other issues why one might prefer one database over another, and neither MySQL nor Postgresql are the best or the worst for every job.

Re:Now how about.

[HarrisonFisk]

No, InnoDB is multiversioning, similar to Oracle and PostgreSQL. That means that it uses non-locking reads, assuming you are running in the default isolation level of REPEATABLE READ.

The reason this is possible is due to the fact that whenever anyone goes to write a row, it creates an entire copy of the affected row, where the old transaction sees the old copy of the row while the new transactions will see the new copy.

Keep in mind this can change if you set a different isolation level. For example SERIALIZABLE runs in the highest isolation level, so it needs to set more locks than the other levels. There is more info available here and here if you want to read more.

Re:Now how about.

[Sxooter]

So with the single transaction switch, is it guaranteed to be a coherent backup (i.e. all fk relationships should be clean and all that) or can you get a dirty read? Just wondering. Postgresql's hot backup literally runs in serializable mode, but since the back is read only, it doesn't actually lock anything.

Re:Now how about.

[HarrisonFisk]

Yes.

InnoDB takes a snapshot in time with REPEATABLE READ (and doesn't lock anything to do so). That means you can not get any one elses data injected while you are inside your transaction, which means you get a snapshot of only completed transactions. Here is a link where the creator of InnoDB talks about using mysqldump.

It sounds like PostgreSQL's SERIALIZABLE is very similar to InnoDB's REPEATABLE READ setting. It might be because PostgreSQL isn't really serializable in the mathmatical sense while InnoDB is. I'm not that familar with PostgreSQL's internals to know if it is similar to InnoDB's REPEATABLE READ level.

Re:Now how about.

[Sxooter]

So how does innodb get around the example problem and still provide good parallel performance?

Re:Now how about.

[HarrisonFisk]

It does Predicate Locking, exactly what is required in order to get true Serializability. Basically that is the best way in order to get transactions to truly be serialzable. There are other ways as well (table level locks, etc...) which also work, but allow less performance.

Predicate locking is only required in serializable. The more isolation you get, the more about of locking you will incur. That is what is assumed when you ask for the higher level of isolation. If you require true serializable behavior, then you are willing to accept that. If you don't need it, then you can use a lower isolation level. I believe that SQL Server and DB2 also use Predicate locking AFAIK when in serializable.

Hmmmm

[cluge]

I wonder how this affects performance especially compared to regular user defined functions? (Available in later releases of MySQL). This is indeed an interesting twist. It certainly can help speed up development of large projects (java works well in a large/many programmer env.) Like a lot of other tools, it remains to be seen how people put this to use. Too often people learn one thing, and like the saying goes, when all you have is a hammer, every problem looks like a nail. Lets see where this goes shall we?

Re:Hmmmm

[MCZapf]

SQL statements are code, just at a higher level than you're thinking of. Think of putting function calls in SQL as similar to embedding some assembly code into C source.

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:Hmmmm

[Sxooter]

OK, here's how it works. You take your logic for how data gets inserted, and you wrap it in a MySQL java function. You then restrict all update / insert access to said function, taking that ability away from users.

Now, timmy in accounting can program his Vic-20 to insert a payroll record, and no matter how goofy little timmy's BASIC program is, he can't screw up your data, because he doesn't have the power anymore. by restricting all updates to a single code path, you ensure the data cowboys don't go messing up your database.

And, you can then take your java functions with you to your next flavor of MySQL when you upgrade.

Krow

[Anonymous Coward]

Keep in mind that Krow used to be a VA Linux employee. He posted an occasional slashdot story, but spent most of his time running slashcode

Rumor has it that he was fired (or was going to be, so he "resigned"). I'd wait a couple weeks to make sure no backdoors are found.

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.

Apparently somebody already did

[GillBates0]

So when will someone do Ruby?

MySQL Ruby Interface
Google Cache here.

Google's your friend

Re:Apparently somebody already did

[tcopeland]

Right, that's a Ruby wrapper for MySQL, but does it allow you to instantiate and send messages to Ruby objects from MySQL?

I didn't see a way to do that... all the examples look like pretty straightforward query processing.

Re:Apparently somebody already did

[proj_2501]

that is not the question at hand. you have linked to a connector that allows ruby programs to access a mysql database.

there is no built-in ruby interpreter in mysql such that you can write functions of mysql in ruby.

not missing the point is your friend!

Re:Keep this out.

[jbplou]

I'm sure if you are the DB admin you will be able to turn this feature off.

database language?

[Anonymous Coward]

So when will someone do Ruby?
Warning, engaging humor mode *puts on asbestos suit just in case*

After someone does perl and python ?

(notice that both of those languages, as well as tcl, are already included in the other free database project: postgres)

Re:database language?

[pHDNgell]

Ruby, perl, python, tcl, and I believe java language (there's a pljava project, but I'm not sure what the status is on that one) plugins are all available for postgres. Many of them have been there for a while.

Re:database language?

[Scarblac]

Why do Python when you can just run Jython inside Java inside MySQL?

*hide*

Re:database language?

[tgrigsby]

I already did Ruby. Of course, I had to give her a Perl before I could give her the Python...

Oh, sorry, wrong forum...

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.

My bad

[GillBates0]

The above interface allows you to use MySQL via Ruby. What's required here is the converse.

Re:Keep this out.

[gustgr]

I don't know why everybody wants to keep distance from Java. It is a very nice language implementing the very well the OOP paradigm.

The 'lusers' may not use this new feature but Java programmers will and hopefully will enjoy it.

Re:Keep this out.

[carabela]

Yup, whatever happened to the idea of layers, tiers of clientapp, business logic and the dB in some storage area?

Sounds like...

[AndroidCat]

More code to bog down the servers with. I don't know if I see the need for Java inside the DB server. (Sure, server Java between the DB and the client app, but that doesn't require Java inside the DB server itself.)

I hope this isn't a "Hey wouldn't it be really neat!" feature. The last time that happened, someone at MS thought executable email would really neat.

Re:Sounds like...

[laird]

"More code to bog down the servers with"

Don't think of it that way. Think of it as "now we can code stored procedures in Java instead of weird, proprietary scripting languages" or "Now we can use MySQL for our application that requires stored procedures"!

Re:Sounds like...

[the uNF cola]

We all don't like learning the database language of a database. It's annoying that oracle, sybase, postgresql and mysql support different sets of ansi92 (or 98) sql, but they all have different gotchas..

limiting the # of rows of output is different between oracle, sybase and postresql/mysql.

None of them even have remotely the same stored proc language. Of course, everyone may embed a different language, but java seems to be a more common one.

Now when you go from sybase to oracle, you don't have to worry so much about the stored procedure code, since it'd all be in java anyway.. riight?

Re:Sounds like...

[GreggBert]

WooHoo !!! Java Stored Procedures, just like in Oracle. One more bullet in my chamber for replacing Oracle with MySql here at work. This isn't everything we need yet, but it's a big step forward and will help address some of the Oracle DBA's concerns with MySql.

Now, if I could only get them over the whining about having to learn another DBMS in order to support it....

Re:Sounds like...

[MattRog]

Oracle to PGSQL is a much better choice than Oracle to MySQL.

Re:Sounds like...

[AndroidCat]

That's not so bad then--so long as there's some standard for this Java code, it will hopefully simplify things.

I'd still worry about someone trying to jam in some huge Java monster code just because he can, but that's why we invented pointed sticks.

Re:Sounds like...

[GreggBert]

Agreed, however, some of the open source and "developer source" software that I think might be useful to us in the future in our workplace only works with MySql, out of the box. That and the fact that up until recently, PG was not available on Windows which, unfortunately, is one of our production platforms.

Re:Sounds like...

[jon3k]

You wanted a standardized language ... so you pick Java? JAVA!? /me throws hands up in the air

That link in your sig.

[DAldredge]

That is the most useless waste I time I have ever seen!

Now leave me alone as I am going to spend the next 2-3 hours playing with it. Really Cool.

Re:That link in your sig.

[tcopeland]

> That is the most useless waste I
> time I have ever seen!

Yeah, I get that a lot :-)

> Really Cool.

Thanks!

That's great

[j0hndoe]

I used Java stored procedures a lot back when I was working at a .com. For someone who's already using Java its a lot easier than learning each database vendors proprietary language. It's also good for keeping MySQL feature competitive with open source dbs, since Java stored prcedures have already been implemented for PostgreSQL

Re:That's great

[Brento]

I used Java stored procedures a lot back when I was...

Just FYI - MySQL still doesn't support stored procedures, let alone Java inside stored procedures. Seems like MySQL is trying to run before it's gotten the walking thing down yet.

Re:That's great

[drfrog]

amen brother!

yah and in perl {which i actually requested two years ago!}, c and others too

lets face it postgresql is way ahead of mysql if you want to do anything but simple selects!

ok im gonna stop now before i start trolling

Re:That's great

[krow]

Stored Procedures are in 5.0.

Re:That's great

[Tassach]

Java stored procedures are an idea that sounds neat on the surface, but in reality is completely brain-dead.

In an N-tier systems, the client and application tiers can scale horizontally -- if you need more horsepower, you just plant another server in the farm. Databases, on the other hand, only scale vertically (Even the heavyweight commercial enterprise-grade databases have difficulty scaling horizontally)

If your DB server can't handle the load, pretty much your only choice is to buy a bigger one. If this means going from a uniproc machine to a dual proc machine, the cost increment isn't huge; however, the cost of going from a 4-way to an 8-way or larger box is huge.

Java stored procedures just don't scale -- primarily because they suck resources like a pig. Database performance is typically limited by disk I/O and physical memory. If you have to run a Java VM and a bunch of java classes, that's a huge amount of memory that's getting sucked up -- memory that should be used to cache data. In my personal experience, the performance impact is at the order-of-magnitude level.

MySQL is not competitive with Postgres; and Postgres is not competitive with any of the commercial databases. As far as I'm concerned, MySQL is a worthless toy. If you are going to go to the trouble to write a client/server system, do it right and use a REAL database. The extra effort to do it right will pay of the long run.

Re:That's great

[GreggBert]

MySQL is a worthless toy...

Well, seeing how this "worthless toy" is responsible for storing your message and millions others like it here on /. I'd say it was more than a mere toy. For some applications (forums, web logs, archives, etc), MySql is more than enough to handle the load if the application is designed correctly and it sure beats paying for an Oracle license where one is not needed.

Re:That's great

[jon3k]

Get horizontal, check out TeraData. =)

http://www.teradata.com/

Re:That's great

[Brento]

Stored Procedures are in 5.0.

5.0 isn't out yet - it's a development tree. That's like saying MS SQL Server has C#-based stored procedures - it will, in the next version, but the current public version does not.

Re:That's great

[krow]

I will grant you that.

I have it running right now and I know it is working for others.

That and I know for a fact that the first binary release is pretty close to happening :)

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 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.

Comment removed

[account_deleted]

Comment removed based on user account deletion

Re:Keep this out.

[AndroidCat]

but Java programmers will and hopefully will enjoy it.

And when I want to move my app to a different db or support multiple dbs, and discover that the server side depends on this cute hack?

Re:Keep this out.

[j3110]

Did you not think about that post? Sounds like you just dislike Java so much that hearing it in the same sentance as MySQL makes you cringe.

1) Java isn't going to slow down any queries unless you use Java functions.
2) What do you care that someone else isn't smart enough to write good software?
3) MySQL as it stands has no other way to really embed functions easily, and it's actually more effecient to run code on the server and transfer data back afterwords.

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!

Production Quality OpenSource!

[Anonymous Coward]

Yay, this project has finally hit Alpha 3 version 0.1, which means that it's the closest OpenSource project yet to a 1.0 release.

Yes, I'm being sarcastic, I just think it's hilarious that someone would post this implying that the code is anywhere near done. It's barely beyond a twinkle in some kids eye.

JMS?

[Anonymous Coward]

If you have java in the database, it will make it possible to integrate JMS (messaging). A lot of the big databases let you do this, e.g. have a trigger that sends a message to invalidate a cache entry. That sort of thing can be useful.

Neat...

[gamlidek]

Now if only I can convince management that this is good. =/

-gam

Nativily?

[Otter]

Eric Herman and MySQL's Brian "Krow" Aker have released code to allow the DBMS MySQL to run Java nativily inside of the database.

"Nativily"? Boy, the Christmas spirit is just busting out everywhere today!

Re:Keep this out.

[j3110]

Then you port a small amount of code to the embedded languages of that DBMS. Every other DBMS has embedded functions except for MySQL, some even support Java.

Java in the DB - very, very bad idea

[BigGerman]

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.
Java is a nice programming language. Go write web apps, middleware, network software, desktop apps with it but not stored procedures.
Is mySQL process going to start the whole new JVM on every hit? Or VM is going to run separately and it is bridged somehow (God, not over the network)?
Now if you ask me, even stored procedures in general become more and more evil.
And in our age of $50 2Ghz CPUs and Gigabit ehternet the performance is no longer an issue.
To me, a database is a collection of tables and indexes with referencial integrity, failover and redundancy. It should do just one thing and do it well. Attempts to add features like that seem to be just a marketing thing by their new commercial overlords.

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:Java in the DB - very, very bad idea

[randolfe]

The knees jerk so fast on /. whenever Java is mentioned, in any context, that I'm surprised someone doesn't have their eye put out.

Of course there exist myriad reasons why one would prefer to standardize on a common language for DB SPs. Java, in this regard, is the most mature alternative at present. Even the notoriously skeptical Thomas Kyte and the pontificating Steven Feuerstein see the validity of Java in the database at the SP level.

Of course, *we* can all keep fighting amongst ourselves about such things while Visual Basic and C(flat) become the only languages we have to chose from for everything we endeavor to do.

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

[marktoml]

>) 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.

Yep, and it would run more efficiently there in any language (how about PL/SQL exposed as a web service?)

It is a nice option to have and in certain cases the JVM in the database is vastly more scalable than your client or middle tier (if that is where you major horsepower is). Sadly, like most performance choices, it is never cut-and-dried.

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

[jrexilius]

Sometimes people make choices and trade-offs between system characteristics (maintainability, standardization, security, portability, performance, etc. etc.). I see this as simply an additional method for balancing these things in system design.

I also understand peoples initial negative reactions to new features (based on experience where a tool or feature intended for intelligent use gets abused and misapropriated).

I personally would like to see this idea extended (and improved) to offer more options for distributing logic/work flow/data and making finer tuned decisions on balancing system characteristics.

Its not a bad idea. And if stupid people apply it poorly thats all the better for my career. ;-)

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

[ryanvm]

The knees jerk so fast on /. whenever Java is mentioned, in any context, that I'm surprised someone doesn't have their eye put out.

You must have knees like Olive Oil.

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

[nettdata]

and in certain cases the JVM in the database is vastly more scalable than your client or middle tier

Very true. I think that the coolest thing was probably how Oracle re-coded their threading and garbage-collection code to allow for HUGE scalability.

We were doing some benchmarking and found that our app "died" at about 3,000 sessions (as defined by us) in the tweaked/tuned native JVM, whereas the Oracle JVM handled almost 150,000 before croaking.

We were also messing around with hacking JavaSpaces and RMID to run within the Oracle JVM, and the initial performance and fault-tolerance findings were very impressive.

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.

Or Python?

[Qa1]

So when will someone do Ruby?

Or Python?

Re:Or Python?

[d99-sbr]

Well, now that you have Java, why not just use Jython?

(Jython is a Python interpreter written in Java)

Re:Or Python?

[BenjyD]

A program written in an interpreted language running on an interpreter running in a virtual machine running from inside a database? It's madness, I tell you!

Re:Or Python?

[GooberToo]

Just use PostgreSQL and be done with it. PostgreSQL already supports, Java, Python, Perl, tcl, PL/SQL, and probably many, many more that I'm forgetting.

Re:Or Python?

[The Unabageler]

postgres does, and has done for a while:

Server-side user defined functions can be written in several languages: C SQL PL/pgSQL (very similar to Oracle's PL/SQL) Tcl Perl Python (alpha) Ruby

Re:Or Python?

[Doug-W]

Which just means that we need to run the database under a linux instance of vmware running on an XP, in VirtualPC on an G4 OSX box someplace!

Comment removed

[account_deleted]

Comment removed based on user account deletion

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.

So when will someone do Ruby?

[Trigun]

Give me a bottle of scotch, a phone number, and cab fare.

And a camera.

Re:mysql

[Dreadlord]

untrue, MySQL IS ported to the most popular platforms too, Linux, Windows, Mac OS X, FreeBSD, and more... Please make sure of your facts before posting next time.

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: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:Oracle already does this...

[Cajal]

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.

True, but there are so many other barriers to using MySQL in "bigger companies" - like its near total lack of integrity checking, its penchant for silently altering table schema and types and for silently altering data. And, frankly, I'd rather not trust anything mission-critical to some brand-new, untested implementation of stored procedures in MySQL, when other databases (PostgreSQL, Phoenix, etc) have had these abilities for years.

Re:Oracle already does this...

[mabu]

Oracle's PL-SQL is a NIGHTMARE. While it does work, the language of their stored procedures is so inconsistent and archaic that it boggles description. Just the error messages alone from PLSQL send most new developers off on a wild goose chasing trying to figure out what's actually wrong. But it would be nice to have stored procs and functions within MySQL. Even PHP extensions within MySQL would have been my choice over Java.

Re:Oracle already does this...

[jpkunst]

Even PHP extensions within MySQL would have been my choice over Java.

Such a thing actually exists: myphp. It's very early in development (version 0.1) and it's not useable for real work yet. But it looks interesting.

JP

Re:Oracle already does this...

[thentil]

I can only guess that everyone is talking about stored procedures because they assume a database as 'talked about' as MySQL is *has* stored procedures (which it doesn't, until v5 is released -- last I saw, it was in alpha). From what I can see, I agree - this has little to do with stored procedures.

Re:Oracle already does this...

[drinkypoo]

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.

IIRC DB2 is from OS/400, yes? When I took my AS/400 class in Raleigh (all of which I have now forgotten from disuse) they told us that using OS/400 you can link together modules (or equivalent) from multiple languages into a single executable/program. Java, COBOL, C, whatever.

OS/400 is clearly bitchin'. People look at me funny when I tell them this, but I stand by my belief.

Re:Oracle already does this...

[_fuzz_]

Oracle's PL-SQL is a NIGHTMARE. While it does work, the language of their stored procedures is so inconsistent and archaic that it boggles description.

I didn't think it was that bad. It was actually a lot cleaner reading than Java stored procedures in Oracle. JDBC just obfuscates simple things. SQLj does help that situation, though.

The thing I liked about PL/SQL the best was how well the cursors worked. That makes a HUGE difference for some tasks.

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:Oracle already does this...

[nettdata]

Actually, some of the apparent "DBMS_*" utility packages are just wrappers for Java procedures, like the DBMS_SMTP packages to send emails.

Re:Oracle already does this...

[Sxooter]

FYI, the beta3 of plphp for postgresql is out, and after having tested it, it's pretty cool, and seems to work quite well.

Well...

[DAldredge]

Since the main page of slashcode.com hasn't been updated since August 30, 2003 perhaps he doesn't work on that site anymore?

Re:Well...

[krow]

Bingo :)

Re:Keep this out.

[geoffspear]

And still, where it the most common usage of java? Stupid web crap in banner ads and pop ups.

Repeat after me: Java is not the same thing as Javascript.

Re:mysql

[wawannem]

I wouldn't say that Java's main advantage is compatablity with almost every platform

I would say that it is definitely one of Java's advantages. I think this feature is useful from the perspective that more small applications will be written by developers that may not be skilled with SQL coding. Many open source projects start out with one developer, and that single developer might not have an arsenal of skills. That is where this sort of thing helps out, that single developer will now be able to use his/her favorite host-language (if Java is his/her favorite host-language) to solve a SQL problem that he/she may not be capable of solving with SQL. Later on, if/when the project grows, the embedded Java may get removed, but at least something had been released, and the concept gets proven.

Where is MySQL anyways?

[Chitlenz]

As an Oracle DBA, at a small company, we're constantly looking for less expensive SOLID alternatives to our traditional Oracle/Solaris approach to the back end.

When I say solid, I mean is able to handle very large files (excess of 50GB per datafile), has stored procedures and trigger infrastructure (a traditional MySQL weak point, and the main reason we've passed on it so far), an integrated backup system a la netbackup/RMAN, and prefereably a back end compiled scripting solution a la PL/SQL.

This looks like a sorta kinda solution to the last (PL/SQL alternative), but I'm curious to know about the rest, and also how it performs. Ideally for us, we'd also like to see better clustering and large system support examples in the real world before we embarked onto this particular voyage with.. say a production ERP system.

Are we talking about a good replacement for Access or for DB2 here?

Enquiring minds want to know ...

-chitlenz

Re:Where is MySQL anyways?

[BigGerman]

Postgresql seems to be the ticket.
I have been doing Oracle work for 12 years and find Postgres easy to learn and quite powerful.
Certainly ref integrity, triggers and PL/SQL like stored procedures are all there.
I currently have 80GB PostgreSQL database as a backend for pretty busy websites and it holds well.
THIS is not a solution, just a proof of concept. I looked at the code and it is not even thread-safe.

Re:Where is MySQL anyways?

[__past__]

Well, don't just look at MySQL. Maybe PostgreSQL, SAP DB or Firebird is an option for you, each of it has satisfied users that will assist you in evaluating them (well, I guess so. I never met any SAP DB user, satisfied or otherwise, but there must be some...)

Re:Where is MySQL anyways?

[jdgreen7]

We ended up moving all of our scattered Access Databases to MySQL about a year ago, and have never looked back. We still use Access as a front-end to get at the data, and everything has worked beautifully. Using Access gives us a consistent UI for each app, and it's quick and relatively painless to add new apps or features. And, using MySQL for the data is orders of magnitude faster that Access MDB files.

As far as 'enterprise level' features, MySQL is still missing Stored Procedures, easy 2-way replication, and clustering (there are many projects out there that add these features, but none of them are included in the main branch AFAIK). They keep getting closer with each release, though.

No, it's not ready to take on Oracle yet, but for mid-size shops (we regularly have 30-50 concurrent users all day from various remote locations), it's a great product. Slashdot runs it, and they seem to be able to handle quite a bit of a load. It's proven itself to me, but then again, I've never played with Oracle or DB2. It has a very active developer base, so things are changing all the time.

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.

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:Where is MySQL anyways?

[doom]

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.

Yes indeed. The McDonalds of the database world.

Re:Where is MySQL anyways?

[Hairy1]

Oh no, not another Oracle DBA. It seems that Oracle DBA's are trained into believing that everything should be done in stored procs, and that a database must have all the latest features that Oracle have dreamed up in order for it to be a good product.

I run a software company, and have made the decision that I will not use stored procs or product specific SQL. Where possible everything we do is cross platform in terms of databases. To date I have personally done a MS-SQL to Interbase, MS-SQL to Postgres and Access MDB to Postgres transition.

In each case the move took less than a days work, mostly dealing with issues around how different databases handle autoincrementing fields for ID's.

Oracle has a interest in having as much of your projects investment within Oracle. This way it makes life very difficult to change databases - you will have to throw away all that code and reimplement it.

However, if you decide from the start that you won't do it that way, then you are free to move between databases. Does that mean you have to give up on some cool features - yes. The OO type features of Postgres are great, and I would love to use them - if only they were supported on other databases.

The problem is that my products may be sold into companies that insist on MS-SQL, or Oracle, or whatever else. How can you do that if you tie yourself to a specific DB?

Re:give me a break

[ajaf]

I agree, migrating from another database to mysql is a pain in the ass and impossible, due to the lack of a lot of functionalities you said.
Postgresql, in the other side, is close to other databases, and migrating to it maybe is a pain in the ass too, but not impossible.
Last version of Postgresql is really fast, so why should I use Mysql?

Re:Keep this out.

[DAldredge]

Then don't compile in such support.

Re:Keep this out.

[rifter]

Did you not think about that post? Sounds like you just dislike Java so much that hearing it in the same sentance as MySQL makes you cringe.

I think the answer to both questions is yes.

1) Java isn't going to slow down any queries unless you use Java functions.

Which is exactly what the poster fears they will do.

2) What do you care that someone else isn't smart enough to write good software?

I think this is the heart of your disconnect with the poster. True, if you are using MySQL as most do, in a small shop, the dba and the application programmer are probably the same person. But in most shops employing RDBMS, you have one team that maintains the OS, one team that maintains the database, one team that maintains the network, and finally a team that writes and maintains the actual applications. What the poster is probably worried about is being a DBA when some application programmer uses Java badly and then his boss leans on him, as the DBA, to make the process work faster. In such a case you would deeply, deeply care.

3) MySQL as it stands has no other way to really embed functions easily, and it's actually more effecient to run code on the server and transfer data back afterwords.

Sounds like a job for stored procedures ala Oracle. If MySQL does not have them it should.

Re:Keep this out.

[Anonymous Coward]

Bad enough that none of them know how to write queries properly so they bog down the servers.

Oh, come on, everyone knows mySQL doesn't support subqueries because they allow a lazy user to slow down the database. You're supposed to do simple queries against the database, and then JOIN the results in your C application. Anyone who can't write a safe, fast JOIN in C is just a bad programmer, and should not be using an enterprise-level RDBMS like mySQL. Go back to FileMaker, you SQLamers!

(This troll brought to you by mySQL AB, the folks who brought you other hard truths about database programming, such as "Transactions are for sloppy programmers" and "Any relational integrity checking you can do in the DBMS, you can do easier in your application." Remember: Only asshole DBAs believe all that Codd & Date "relational" bullshit.)

Security

[pantycrickets]

Doesn't this open sort of a large can of worms from a security standpoint?

Why you would do this . .

[database_plumber]

This idea has been around for a while; at least since the late 1980's. The motivation for these kinds of DBMS features is that there are lots of programming situations where SQL's types and expressions aren't powerful enough, and that the language doesn't have a lot of modularity. User-defined functions are supposed to overcome this limitation.

This kind of feature brings MySQL closer to being an "Object-Relational" database.

http://en2.wikipedia.org/wiki/Object-relational_da tabase

From a theory point of view it goes a long way towards implementing the Relational Model's idea of a 'domain' (not just INTEGER, VARCHAR or whatever, but PART_NUM, PERSON_NAME etc). This is supposed to improve the integrity of the data in the database.

From a practical systems point of view it can have a big performance impact. If you're opening cursors and then looping over some Java code on the client to identify only those result rows that you're interested in, then you're paying a pretty big 'system tax' to transfer the data from the DBMS through the connection and into the external program's address space. Pushing the code (which will have to run anyway) into the DBMS eliminates the transfer overhead.

The point of the original Postgres was to figure out how you incorporate these features into a query processing framework. Most modern DBMS products have the feature; some of them do a better job implementing it than others.

Re:Why

[XJEEP.org]

it allows for stored procedures.

Re:Keep this out.

[laird]

"Sounds like a job for stored procedures ala Oracle. If MySQL does not have them it should."

That's the issue -- the poster's complaint actually has nothing to do with MySQL embedding Java in particular -- he's worried that if he allows users to execute stored procedures they can bog down his database.

I don't know about MySQL, but in Oracle, Sybase, etc., you can put a CPU time limit on queries so that if someone starts sucking the system down their queries time out.

Re:Keep this out.

[Razor Blades are Not]

Have you been swallowing the tripe from the MS developers journal again ?

Have you even worked with Java ? I don't mean puttering around at home in between failed job interviews. I mean in a production environment.

Javascript != Java.

It would be if it were true.

[Anonymous Coward]

Java is not well supported. Linux, solaris, windows, on x86 or sparc. That's about all that falls in the "well supported" category, maybe macosx. Other OS's and/or architectures have old, broken, or just plain don't have jdks. Maybe if Sun didn't insist on trying to control java in every way, and made it open, then it would really be something. But as is, its just as portable as C#.

Re:Keep this out.

[Tim C]

But in most shops employing RDBMS, you have one team that maintains the OS, one team that maintains the database, one team that maintains the network, and finally a team that writes and maintains the actual applications. What the poster is probably worried about is being a DBA when some application programmer uses Java badly and then his boss leans on him, as the DBA, to make the process work faster.

I work in such a company, as an application programmer. Where I work, it's not up to the DBA to make things run faster - if an application is too slow, it's our problem. True, we can call on the various other people to help us in tracking down the cause of the problem, working out what needs to be done to improve the situation, etc, but ultimately it's our responsibility. It may be that the solution is to increase the spec of the machine(s), or upgrade the network, but we'd still be very much involved in coming to that decision.

So, as a counter example - from my position as a programmer, I'd be rather upset if my otherwise well-performing code is crippled by an underspecced machine or poorly-configured database. I trust the people responsible to do their jobs properly, though.

MySQL java appserver?

[Doc Ruby]

Now can I run a MySQL server as an appserver, with SQL selectable java objects? I want mobile Internet java objects to travel among a directory of MySQL appservers, connected to live data through the sandbox.

Re:MySQL java appserver?

[baka_boy]

This doesn't put a SQL interface on top of Java objects, or even support serialization of said data to rows in the database. It just lets you extend the range of user-defined functions (i.e., data type conversions, aggregate operators, etc.) to those implemented in Java.

Re:MySQL java appserver?

[Doc Ruby]

Yeah, but with an embedded, callable JVM, can't a SQL query call a function defined in Java that uses the classloader to load a java object from a table, and run it? That class table could be filled with data arriving from the Internet. That architecture supplies the basics of an appserver, which could be fleshed out with the appropriate schema and management classes.

Re:MySQL java appserver?

[Doc Ruby]

Fucking anonymous coward. If you could read, or perhaps understood the meaning of the "buzzwords", you might see a spec for a free, open java appserver compatible with the large installed base of MySQL servers. Instead, your anonomyity overcomes your cowardice, and you shoot your mouth off about buzzwords. Recombobulate yourself back into hypospace, offer something worthwhile.

Re:Keep this out.

[zerocool^]

I know java is not javascript. I am just sick of seeing little popups and crap on webpages that use Java (real java). I.e. I watched the freakin sun little "cup and steam" and a grey space load before I get something that looks like a casino or something".

Such that I uninstalled Java.

Slammer anyone?

[bentfork]

Remember the SQLSlammer?
That was spread (as I recall) because of default passwords not being changed on sample databases. I know lots of people that dont change their default MySql root password. (I tellem but WTF, its their bandwidth...)

Seems like they are trying bring MySql in direct competition with M$SQL server... ( as a spreader of worms )

simple.

[flacco]

So when will someone do Ruby?

i will as soon as i can get her to drink this liter of vodka.

Re:simple.

[Sxooter]

Someone already did:

http://moulon.inra.fr/ruby/plruby.html

Oh wait, that's for Postgresql...

so is plphp, plperl, pltcl, plperl, plpgj (java), plR, plsh, and about ten or so more I can't remember right now.

Re:Keep this out.

[KenSeymour]

The /. crowd keeps hoping Java will go away. They don't want to learn it.

But it doesn't go away.

I have noticed that there are several technologies that are held in high regard outside of the Linux/Free Software that are despised within it.

One is Object Oriented programming. By extension, C++, Java, and UML also fit into this category.
I wonder how many folks who bash these things have ever actually bothered to learn them?
It is easier to say that XYZ is "bloated and ugly" than to say "I never learned these things so I can't say how useful they are."
I also see a lot of "Anyone that does not agree with me is a stupid, clueless, MCSE."

Name calling is a sign of weak arguments.

The article is about Java running on the server, behind the scenes. It doesn't "look" like anything.

I believe server side java is the most common usage of Java. Portability is less of an issue because you control the server environment.
You can support non-Unix users and still use Unix/Linux for your server.

Not my first priority

[tacocat]

Java is not my first choice in languages to support in a database language. PL would have at least been more adaptable.

Unfortunately with the Enterprise Popularity of Java, thanks to a strong Marketing Campaign by Sun Microsystems, MySQL is following the Corporate line of supporting Java regardless. This is a fine example of what may be a mis-direction of the MySQL developers being pushed into a Support the Corporate Enterprise stuff rather then doing good code on a good platform.

Re:Not my first priority

[Decaff]

Java not stable or reliable? What planet are you on? Java was designed for stability and reliability. The neatest thing in this context is the SecurityManager, which means you can restrict what it can do within an app server or database, unlike C code or whatever.

PHP UDF

[TheTomcat]

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

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

S

While we're at it...

[karmaflux]

I want MS-DOS debug embedded.

--F 200 L1000 0
--A CS:100
xxxx:0100 MOV AX,301
xxxx:0103 MOV BX,200
xxxx:0106 MOV CX,1
xxxx:0109 MOV DX,80
xxxx:010C INT 13
xxxx:010E INT 20
--G

BWAHAHAH

silly question perhaps..

[jrexilius]

But does functionality like this exist for other executables (C/C++,Perl,etc.). Wouldn't this be extendable to accomodate external functions in general?

Re:silly question perhaps..

[Decaff]

The huge advantage of Java is its security. The app server or database engine can include a customised implementation of the Java SecurityManager, to ensure that user code can't do bad things. This is why enterprise database engines allowed this kind of embedding for java but not C/C++ etc.

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:Keep this out.

[j3110]

In any case, you should know that there will be a "skip-java" directive in the config file :)

Re:Keep this out.

[j3110]

Yes, the disconnect is that I don't think the poster understands the intent of embedded procs like this. They are used to improve performance, not stifle it. That's almost like saying "I hope they don't implement sub queries, imagine how slow they will make the system then!". This is more true than stored procedures, but there are some times that sub queries are faster. It's a hell of a lot faster than runing N+1 queries through the DB because it doesn't support sub queries when you need them! Likewise, parsing through the data on the server is the best thing ever. It reduces network load, and moves some of the processing into the DBMS where the CPU spends most of it's time waiting on the disk. Overall, any kind of stored procedure like this can be very much better performing than what we have now.

get useful features first..

[Suppafly]

MySQL needs to finish cloning the functionality of those rdbms's like Oracle before they start adding crazy features with limited utility.

Re:Keep this out.

[WasterDave]

As a C++ programmer I ... kinda agree actually. But I still don't think running Java stored procedures is a good idea.

Note that this is a very different kettle of fish from running Java on the server. Server side Java is just getting bigger, basically, and one of the things that is making this happen is increasing scalability.

However, three tier apps are generally bottlenecked around the database - because by necessity there can only be one of them. Therefore, while stored procedures and triggers are valid parts of the enterprise developers' toolbox, they have to be used sparingly because of how critical performance is on the DB.

Cheers,
Dave

Re:Firebird SQL

[Just Some Guy]

Because it sucks most grievously. The day we killed Interbase for the last time was the day we celebrated. Long live PostgreSQL!

Re:give me a break

[LDoggg_]

Or how about adding TRUE stored procedures

What does the language have to do with whether or not its an actual stored procedure? Do only PL-SQL written stored procedures count as TRUE? - No thanks. Gimme something with actual objects and a clearly defined set of APIs.

Sub queries would be nice though. And how about sequences, and most of all views.

Seriously, what does this buy me when I can't even do a sub-query?

Well, I guess it buys you everything, but not exactly in an elegant manner.
Consider this:
Select username, someJavaFucntion(user_id) from users;

You could do all the sub-selecting you want in the java, just very likely not as effeciently as if it was a more complex SQL statement parsed by oracle.

Someone already did PHP.

[EMR]

I know someone already had something like this to allow you to execute PHP.. although only one statement at a time.. I have that code somewhere.. Should probably update it with all the other PHP stuff I have done to allow you to write entire functions in PHP and execute them w/in MySQL..

How fun would that be.. PHP calling MySQL which calls PHP..:-D.. Ahh straight from the Deptartment of Redundancy Dept.

Re:Postgresql

[The Unabageler]

also, postgresql has had support for adding third party languages for writing inline db functions for quite a while.

Re:Keep this out.

[esquimaux]

Gosh, like PostgresQL or Oracle?

Cool, actually. But...

[reverendslappy]

That's actually a sweet feature, but I would've thought they'd concentrate on other things first. I'd think that ANSI-92 compliance, real stored-procedures, replication, online backup, subqueries (especially for lazy folks like me), etc. are more important. (I'm sure somebody's said that already.)

Still, that's a nice feature, no doubt. Plus, I'm sure this was probably a bit easier to make happen than any of those higher-level things. Regardless, I'm not throwing stones, but I'd like to see more progress on the road to making mySQL a more enterprise-level DB. I'm mostly a MSSQL Server weenie, and with some improvement in that area, mySQL could really be a viable alternative in the small-to-midsize commercial database space. Even for free and with the ability to write UDFs in Java, I still gotta pick SQL Server. I'd like to be able get rid of that cost, but alas... Maybe Postgre...

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

Re:PHP would have been a much better choice

[Decaff]

Java is ideal for this kind of thing because it is secure, and can be very fast (with a well-designed VM) and because you can re-use code. Java was not a great idea in 98 as it was dead slow. Now it matches C speeds, its a pretty good choice for portable coding.

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.

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

Re:PHP has this for over a year

[repetty]

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

Cool. Very cool. But those execution times are pretty bad. You certainly would want to plan carefully how you used this.

--Richard

Better than pulling stuff to the client

[Tim Ward]

There are times where it is possible to compute stuff in, say, WHERE clauses using standard SQL, but it's an enormous pain and being able to write a little function in a "real" programming language makes things vastly easier.

MS Access programmers have been doing this for a very long time, with little VBA (or Access Basic if you're old enough) functions saving vast screeds of incomprehensible and possibly rather slow SQL arithmetic and/or substring manipulation.

With Java (or C or anything else) functions running on the server in MySQL you get all those wins plus the win of not having to pull the data to the client to look at it.

(Recent example: I had to write some MySQL SQL code to convert a field representing a latitude/longitude value from a numeric GSM encoding to a string in the format "dd:mm:ss.sss". Possible, but dozens of lines of SQL and utterly utterly horrible. One trivial Java function on the server would have been lots less tedious.)

and here is a great reasons not to

[kpharmer]

how many folks can you find who are *experts* good with both SQL & Java? (BTW,I don't mean that they can write simple joins, group bys and unions. I mean good enough to understand access paths and parallelism choices). Of the 100+ java developers I've worked with over the last four or so years I've only met *1* who would meet that critieria. So, exactly who's going to be making the performance-tuning decisions? Nope - bad idea, a simple tuning problem will need a committee to figure it out.

keep in mind that since we've mostly dropped the idea of writing all business logic in stored procedures, they're primarily being used these days for very simple procedures. Nothing fancy - convert an ip from a string to an integer, etc. You don't need *or want* a big language for this. Nor should you sweat too much that it is proprietary - who cares when you can learn the basics of the language in 5 minutes.

Re:and here is a great reasons not to

[nettdata]

You're also assuming that Java is to be used for SQL.

We currently use Java to perform SMTP/POP3 operations, make web service calls, perform Java-based "rsync's" between the DB and file systems, etc., all from within the DB and in a lot of cases being triggered by, well, a trigger.

While I agree that I would tend to abstract all SQL to some PL/SQL call that "DBA's who get it" have control over, there are LOTS of things that Java can do that are VERY handy, when viewed at in the application architecure POV and not just in a SQL context.

Re:and here is a great reasons not to

[Hobbex]

how many folks can you find who are *experts* good with both SQL & Java? (BTW,I don't mean that they can write simple joins, group bys and unions. I mean good enough to understand access paths and parallelism choices). Of the 100+ java developers I've worked with over the last four or so years I've only met *1* who would meet that critieria.

Database developers like to go on and on about how difficult it is, and how "non-experts" can't possibly learn to develop good databases. So we'll buy that: you can't find java developers who also have the magical unattainable quality of being able to handle a database.

But turn it around instead. How come your database "*experts*" can't learn java? I have java down as well as anyone, and I would claim that anybody with half a brain can become an expert at java development in a few months. Anybody who has experience with imperitive programming can learn it in a few weeks. For people who know OOP it is a matter of days.

Re:and here is a great reasons not to

[kpharmer]

> Database developers like to go on and on about how difficult it is, and how "non-experts" can't
> possibly learn to develop good databases.

No - the problem is that the development of best practice databases requires that the designer understand more than just the syntax of insert, update, delete, and select. And when you get right down to it, most developers don't even have the syntax straight - let alone understand the beginings of relational theory.

> How come your database "*experts*" can't learn java?
For the same reason that I don't expect java experts to also develop expertise in database tuning - each is a large and complex discipline.

> For people who know OOP it is a matter of days.
Really? They'll understand all the commonly-used libraries?
They'll understand when it is best to go straight from jsps to the database, when they should use entity beans, when to use EJBs and when to use CMP? They'll understand the strengths and weaknesses of each solution and exactly how to develop each?

these must be the 'magical unattainable' programmers - you know, the kind I've never met any on any of dozens of projects over the last four years.

At some point,

[alexborges]

Thats gonna make it slower than postgres. I say slay mysql.
Its best thing was that it wasnt hoggish so the normal, simple web-app site could use it.

Nowdays, postgres is faster than mysql, and more scalable, and better proven. An now JAVA? Come on, it cant get any slower than embeding java there.

Re:Keep this out.

[rifter]

"Sounds like a job for stored procedures ala Oracle. If MySQL does not have them it should."

That's the issue -- the poster's complaint actually has nothing to do with MySQL embedding Java in particular -- he's worried that if he allows users to execute stored procedures they can bog down his database.

Actually, I think that is exactly what the poster is worried about. Correct me if I am wrong, but I had the understanding that Oracle stored procedures were compiled C code. In this case, we are talking about Java which is interpreted. For that and various other reasons, we are dealing with far more impact on the database's performance.

except that for the skill requirements

[kpharmer]

No, a stripped-down version of PHP, python, etc would be far better than java.

Does the LAMP crowd write code in java? nope.

Do the DBAs write code in java? nope.

Does the java crowd use mysql? nope (if you're going to the cost of developing apps in java, it makes more sense to use postgresql if not oracle, db2, etc).

In java you can abstract the data persistence thru a variety of encasulation techniques. All of which creates such a mess than everyone's scrambling these days to figure out how to simplify it. Sounds like a mess to introduce deeper into the database.

And portability? Do you really think that java functions created for mysql will port *seamlessly* to postgresql, db2, or oracle? No way. And if they don't - why don't you simply write the functions in a tiny, simplistic language that everyone can use easily?

Re:except that for the skill requirements

[Decaff]

In java you can abstract the data persistence thru a variety of encasulation techniques. All of which creates such a mess than everyone's scrambling these days to figure out how to simplify it. Sounds like a mess to introduce deeper into the database.

Why abstract the data persistence? just use JDBC. At least this has some level of portability, unlike the messy database routine naming in PHP.

And portability? Do you really think that java functions created for mysql will port *seamlessly* to postgresql, db2, or oracle? No way. And if they don't - why don't you simply write the functions in a tiny, simplistic language that everyone can use easily?

'Yes way'. That's what Java is for. That is why MySQL is using the same language for this as postgresql and oracle.

Re:Keep this out.

[rifter]

"But in most shops employing RDBMS, you have one team that maintains the OS, one team that maintains the database, one team that maintains the network, and finally a team that writes and maintains the actual applications. What the poster is probably worried about is being a DBA when some application programmer uses Java badly and then his boss leans on him, as the DBA, to make the process work faster."

I work in such a company, as an application programmer. Where I work, it's not up to the DBA to make things run faster - if an application is too slow, it's our problem. True, we can call on the various other people to help us in tracking down the cause of the problem, working out what needs to be done to improve the situation, etc, but ultimately it's our responsibility. It may be that the solution is to increase the spec of the machine(s), or upgrade the network, but we'd still be very much involved in coming to that decision.

So, as a counter example - from my position as a programmer, I'd be rather upset if my otherwise well-performing code is crippled by an underspecced machine or poorly-configured database. I trust the people responsible to do their jobs properly, though.

And you would be right. I wish that you worked at my company. However, I have run into far too many clueless database application programmers to believe that your attitude is representative of the group as a whole. One thing I have noted is that there is a prevailing attitude among developers and those who hire them that understanding networking, OS, hardware, etc is unneccessary and indeed undesirable. This seems to be worse in database application development than anywhere else.

What this leads to is a situation where a programmer who not only is clueless in all those areas, but also does incredibly stupid things in their application runs into performance problems they do indeed attack the DBA, the network team, the OS/hardware team, etc and have them spin their wheels over problems that are really application problems. And when they are told what is going on at those levels they do not understand or believe it.

By contrast, you are correct that underspecced machines or poorly configured databases will screw over the best code in the world. But I think that the poster was complaining of the former situation. :)

Re:give me a break

[jon3k]

Transactinos: why by default? mysql's default settings favor speed.

Because in the real world, we have two pair of mirrored SCSI drives, and you write the TLog to the second set. The delay is almost totally negligible. Tell every company in the world using Oracle and MS SQL that they don't need transactions, no need to rollback failed transactions, and its just slowing them down.

Puh-leaze.

If you want mysql to compete as an enterprise DMBS, it needs to be a transaction based system.

Ruby?!

[Not The Real Me]

That's a joke, right?

MySQL functions written in Java? Sure
MySQL functions written in c/c++? Even better
MySQL functions written in PHP? Cool.
MySQL functions written in pl/sql or pg-pl/sql? Why not? Code compatibility with Oracle and Postgresql.

But Ruby? That would be for a target audience of what? Maybe 10 developers maximum?

Re:Ruby?!

[iggymanz]

Ruby has a huge following, but just not in the western world yet. I think it is superior in design than even LISP (I've been a paid LISP, Java, C++, C programmer in the past)

Re:Obligatory comment/troll

[jon3k]

Obligatory comment/troll about how much MySQL is not ready for the enterprise eventhough it actually is.

Spoken like a true linux zealot with absolutely no foundation in relational data management.

Do you realize that referential integrity is a fairly new feature in MySQL? Do you understand the reasoning behind a transaction based DBMS? How about the simple normal forms layed out by Boyce and Codd?

Anyone who's done any extensive DB work will tell you how lacking MySQL is. I *love* linux, its the only OS I run at home, and I follow the progress of the MySQL project with an almost religious fervor. I can't *WAIT* until it can compete in a production environment .. but its just not there yet. I'm counting the days until I can get this overpriced trash off our database servers. :(

Re:I would

[LDoggg_]

Jack Ruby?

Gross dude.

Old hat

[NineNine]

Oracle has had this functionality since 8.x. Java is *very* fast inside of Oracle, and is more efficient than PL/SQL in a few cases. Again, OSS is playing catch up.

Re:Old hat

[ibbey]

Oracle has had this functionality since 8.x. Java is *very* fast inside of Oracle, and is more efficient than PL/SQL in a few cases. Again, OSS is playing catch up.

SO? Oracle has lots of features that MySQL lacks. The point of the post isn't that this is some wonderful new feature never before seen in the world, only that it's new to MySQL.

Oh, and one of the fabulous features that Oracle has over MySQL is the price. I mean, who wouldn't prefer to spend many thousands of dollars? This is obviously another area where OSS needs to start playing catch-up.

Re:Old hat

[Lussarn]

MySQL can be installed in about 5 minutes and does what it does very well. Don't compare it to oracle. For more and more applications MySQL is a good choice. In true OSS spirit it's also very stable. I have one MySQL db running 814.71 days without restart serving 1586663205 under that time. Of course it's mostly simple selects. And that's what MySQL does well.

Re:Old hat

[Sxooter]

Horse puckies. Both Interbase/Firebird and Postgresql have had solid, reliable pls for many many years.

While JAVA stored procs are new to PostgreSQL, the lack of a specific language as a stored proc is pretty silly. PostgreSQL has solid reliable stored procedures in a dozen or so different languages, including perl, ruby, R, tcl, php, and C.

There areas where Oracle outshines Postgresql and MySQL, the are also areas which make it look bad in comparison. To think that the Open Source databases aren't competitive is to only look at one small segment (content storage databases like MySQL) and ignore the others, SAPDB (MySQL MAX??? stupid name, but a good database), Interbase / Firebird, and Postgresql.

Parent is a troll

[axxackall]

when have you last time checked Cygwin? They host PostgreSQL binaries for years. Works fine, flawlessly and fast.

Re:to bad postgress cant be backed up hot...

[asdfghjklqwertyuiop]

pg_dump(all) is an export

which is a backup.

Re:Keep this out.

[chez69]

java stored procedures are just in time compiled (at least in sybase they are) the compiled code is stored. You don't interpret it every time.

Re:Keep this out.

[goodviking]

I am just sick of seeing little popups and crap on webpages that use Java (real java).

How how is this a repudiation of the language or it's portability. If these we're implemeted via some sort of embedded perl or python, would these languages suck as well? If someone embeds a trojan or backdoor in some program written in C/C++, does that mean these languages now suck?

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."

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:give me a break

[mcbridematt]

Care to give me your IP. I'll have fun playing with your udp port 1434 :) Assuming that you don't have the Slammer patches installed :(

Python is broken

[jesterzog]

It's worth pointing out that the Python implementation may be either disabled or insecure, depending on the python interpreter installed on your system.

The rexec module, which is used by the postgres python code execution handler, has been disabled in Python 2.3 (I'm not sure about 2.4) due to "various known and not readily fixable security holes.

I was initially planning to use python for my postgres stored procedures, but now I'm just using pypgsql and I think I prefer it.

Fantastic

[thujone]

Does this mean that Friendster will render a page in 29 seconds versus 30 now?

Ruby?

[Glock27]

So when will someone do Ruby?

Perhaps when there's a Jython-like JVM based Ruby implementation?

Seriously (given the number of ignorant "why use Java it's so slooooow" posts) as far as I can tell the current Ruby implementations are slow compared to Java. Would you really want to use a slow interpreted language for database functions, rather than one with close-to-C performance?

Also on the subject of knee-jerk Java bashing, I can't understand why so many C++ programmers resist Java, tooth and nail. Yes, Java has a somewhat bulky memory footprint (that may not be such a problem going forward with all the new 64-bit architectures out there). However, you get a ton of niceties as well, and a very sane language compared with C++. Java runs very fast these days, given sufficient JVM heap. Gcj is also getting there in terms of being useful, and provides an OSS traditional ahead-of-time compiler for Java code. Java may not be an ECMA standard, but it is open enough to permit free implementations.

Java isn't perfect...but it is better than many of the alternatives, and deserves more respect than it seems to get here on /. and among programmers in general. At least it is well supported on Linux by it's originators, unlike C# and .Net.

OK, time to do something useful now... :-)

Re:Ruby?

[paulfwilliams]

Having used C++, Java, and .NET, let me say that Java is slow. Yes, I know, when a JIT happens, Java gets pretty fast. Unfortunately, the JIT is expensive.

At my former employer, our app's startup time stank because of all the JITting. The first time our users tried to do anything, it took an extraordinarily long time. They noticed, and while they lived with it, they always mentioned it. Any sudden, unexplained pause brings back fears of "is it still running, or did it hang?"

Something nice about .NET is the ability to pre-compile a set of classes for a given platform. Theoretically, if I install my app on a machine, that machine won't suddenly change instruction sets, so compiling the whole app during the install saves time on every program execution.

I dearly wish the Java Founders would include a pre-compilation option similar to .NET. Compiling the code every time it's run is such a waste of time.

1/2 Done!

[hattmoward]

See this article: Embedding Perl in MySQL
I did this on a test server -- definite geek factor here.
I'm not seeing any python links on google, but I'm sure someone will speak up if there are.

Re:Keep this out.

[laird]

"about. Correct me if I am wrong, but I had the understanding that Oracle stored procedures were compiled C code. In this case, we are talking about Java which is interpreted. For that and various other reasons, we are dealing with far more impact on the database's performance."

Actually, these days Java is compiled to native binaries (by hotspot), while Oracle's PL/SQL is "compiled" to bytecode that is interpreted. That's why Java stored procedires (in Oracle) generally (though not always) outperform PL/SQL stored procedures.

Decouple jvm and mysql

[lamecoffee]

Might be a nice idea for stability to decouple the java feature and the core dbms with a nice corba interface, to avoid crashing the database service by triggering jvm flaws. Otherwise some macro defined as

package badjava; public class KillBar { public static String exec(String[] args) { sun.misc.MessageUtils.toStdout(null); } }

and called via

SELECT judf("badjava/KillBar") FROM foo

might be a bit harmful !

Holy Christ!

[Puzzle Face]

HOLY CHRIST! What kind of DB files are in excess of 50GB!?!?!?!?! (Or do you meant the whole database?) Our company uses MySQL for a mere 6 million record table, and it works pretty fast. Straight up selects take milliseconds. Joins to that table seem to take forever. (Often in excess of 2 seconds per query.) The fact it isn't thread safe bugged us, but it's easy to make a wrapper class with critical sections. No biggy there. (I think future versions are thread safe though, we're still suck on 4.2) Real world? Seems to work fine. (What's really bizarre is that it actually works better on a Windows 2003 server as opposed to RHL 9.0. Benchmark tests available for those that don't believe me. :-) ).

Re:Keep this out.

[Hognoxious]

It is a very nice language implementing the very well the OOP paradigm.

And the OO paradidigm sucks, which is why you have to invent tossy fictitious objects like math so you can attach things like sin() which are, have always been, and always will be functions to them.

Re:give me a break

[jon3k]

Actually, as I understand it, MySQL recently implemented referential constraints into mysql.

I believe with the introduction of the 4.x.x release(s).