Slashdot Mirror
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?"
MySQL Ruby Interface
Google Cache here.
Google's your friend
An Indian-American Hindu committed to non-violent thought/speech/action alarmed by the global explosion of radical Islam
The Army reading list
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.
> 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!
The Army reading list
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.
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.
> 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.
The Army reading list
"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.
Enable 3D printed prosthetics!
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.
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.
:-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.
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
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.
No.
There's similar functionality s/java/php/g, here:
http://talks.php.net/show/phpquebec/27
http://www.sklar.com/page/article/myphp
S
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...)
I choose to remain celibate, like my father and his father before him.
Stored Procedures are in 5.0.
You can't grep a dead tree.
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.
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.
It's a pity the MySQL guys are trying to reinvent Oracle.
Java in the database is so 98, that makes me wonder why all companies are trying to conquer the world with the same approach..
PHP would make just a more natural choice for stored procedures, and the approach in MySQL should be to allow stored procedures, triggers and referential integrity (this should be native like in PostgreSQL).
It's weak typed and has a pretty standard MySQL api. However, a metalanguage over PHP to minimize the API and to make it transparent would be nice.
Alexandru
PHP Dev David Sklar implemented this over a year ago: http://www.sklar.com/page/article/myphp
/. is irrelevant.
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
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.
.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.
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
Social scientists are inspired by theories; scientists are humbled by facts.
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
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."
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.
Enable 3D printed prosthetics!
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.
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.
Why is it that the proponents of "one nation under God" are so eager to get rid of "liberty and justice for all"?
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.
You can't grep a dead tree.