Slashdot Log In
MySQL Gets Functions in Java
Posted by
michael
on Fri Dec 19, 2003 02:25 PM
from the we-love-you-krow dept.
from the we-love-you-krow dept.
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?"
This discussion has been archived.
No new comments can be posted.
The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
Full
Abbreviated
Hidden
Loading... please wait.
Now how about. (Score:5, Interesting)
Re:Now how about. (Score:5, Informative)
Parent
Re:Now how about. (Score:3, Informative)
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. (Score:3, Informative)
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
Re:Now how about. (Score:3, Troll)
A real man would other get off his ass and code it himself, or support the damn developers who do. Note "Bitch on
Hmmmm (Score:5, Insightful)
Re:Old hat (Score:4, Insightful)
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.
Parent
database language? (Score:5, Funny)
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)
judf uses the Java Native Interface... (Score:5, Informative)
Sounds like... (Score:3, Insightful)
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... (Score:5, Insightful)
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?
Parent
That's great (Score:5, Insightful)
Re:That's great (Score:3, Insightful)
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 (Score:4, Informative)
Parent
Production Quality OpenSource! (Score:3, Funny)
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.
Java in the DB - very, very bad idea (Score:3, Insightful)
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 (Score:5, Informative)
> 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:Seems to make sense - start the VM once, call it as many times as you want.
Parent
Re:Java in the DB - very, very bad idea (Score:5, Informative)
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.
Parent
and here is a great reasons not to (Score:3, Interesting)
keep in
Re:Java in the DB - very, very bad idea (Score:5, Interesting)
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.
Parent
Oracle already does this... (Score:5, Informative)
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... (Score:3, Informative)
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
Re:Oracle already does this... (Score:3, Informative)
Where is MySQL anyways? (Score:5, Interesting)
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? (Score:5, Interesting)
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.
Parent
Re:Where is MySQL anyways? (Score:5, Interesting)
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.
Parent
Re:Where is MySQL anyways? (Score:4, Informative)
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
Parent
Why you would do this . . (Score:3, Interesting)
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.
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.
simple. (Score:5, Funny)
i will as soon as i can get her to drink this liter of vodka.
Not my first priority (Score:3, Insightful)
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 (Score:3, Insightful)
PHP UDF (Score:5, Informative)
http://talks.php.net/show/phpquebec/27 [php.net]
http://www.sklar.com/page/article/myphp [sklar.com]
S
While we're at it... (Score:3, Funny)
--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
Oh come on, NOT stored procedures (Score:5, Informative)
This is all about writing functions, like no_null in
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...)
PHP has this for over a year (Score:3, Informative)
Re:PHP has this for over a year (Score:3, Insightful)
Cool. Very cool. But those execution times are pretty bad. You certainly would want to plan carefully how you used this.
--Richard
Embedded Ruby is not thread-safe (Score:4, Informative)
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.
Ruby? (Score:4, Interesting)
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... :-)
My bad (Score:4, Insightful)
Parent
Re:Keep this out. (Score:4, Insightful)
The 'lusers' may not use this new feature but Java programmers will and hopefully will enjoy it.
Parent
Re:Keep this out. (Score:3, Insightful)
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 the
Re:Keep this out. (Score:5, Insightful)
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.
Parent
Re:Keep this out. (Score:5, Informative)
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.
Parent
Re:Keep this out. (Score:3, Interesting)
I work in such a company, as an application programmer. Where I work, it's not up to the DBA to make things
Re:waaaaiiiit a minute... (Score:4, Informative)
> 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:Nifty!
Parent
Re:mysql (Score:3, Interesting)
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/h
Re:give me a break (Score:3, Interesting)
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:PHP would have been a much better choice (Score:3, Informative)
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.
except that for the skill requirements (Score:3, Insightful)
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
Re:Krow (Score:4, Informative)
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.
Parent