MySQL to Get Injection of Google Code

inkslinger77 writes to mention that MySQL has published their software roadmap out through 2009 and it includes an injection of code from Google. Google remains relatively secretive about how their systems work but they are one of the largest users of MySQL. Earlier this year Google signed a Contributor License Agreement which provides a framework for them to contribute code to MySQL. "The search company has done a lot of work customizing MySQL to meet its special needs, which include better database replication, and tools to monitor a high volume of database instances, Axmark said in an interview at MySQL's user conference in Paris. MySQL will include some of those capabilities in future versions of its database, probably in point upgrades to MySQL 6.0, which is scheduled for general availability in late 2008, Axmark said."

Comments

Injection?

[Tetsujin]

Somehow when I put "SQL" and "injection" together, I don't like the result...

Well, except for when it involves Little Bobby Tables...

Re:Injection?

[Phroggy]

Yeah, I was about to post the same thing. Can we use some different terminology when talking about helpfully contributing code to a database project?

Re:Injection?

[necro2607]

Yeah, who writes these headlines? It's like, let's throw together the most fucking sensationalist possible combination of words to evoke certain responses in peoples' minds when they read this headline. Instead of just writing something constructive like "MySQL adds code from Google", it has to be some sensationalistic crap so as to make people go "OMG SQL injection?!? Sum1 haxed MySQL??" and immediately read the article. What is this, FOX News or something? :P

Hells yeah

[rsborg]

Eat that, Oracle.
Seriously the database layer is being commoditized, and MySQL and PostgreSQL are leading the way.

My only question, was Google required to disclose these changes, or are they just doing the right thing (again)?

Re:Hells yeah

[Dan Berlin]

We don't distribute it, so we aren't required to submit the changes back.
We of course, try to contribute back all the changes we possibly can.

If you look around, you'll see we just don't publicize all the changes we contribute back (and we in fact, didn't publicize this one ourselves).

Re:Hells yeah

[Daniel Dvorkin]

Look in the corporate space. Oracle is everywhere. SQLServer is around (not popular in my experience). Mysql is nowhere.

Define "coprorate space." Big companies tend to be Oracle or SQL Server shops, true; really big companies tend to be Oracle or DB2. But there are a lot of small and medium-sized businesses using MySQL -- and because there a lot more SMBs than there are megacorporations, and because DBA demand doesn't scale linearly (a 10,000-employee corporation doesn't need a hundred times as many DBAs as a hundred-employee corporation) there's plenty of MySQL work out there. Postgres, unfortunately, not so much.

Re:Hells yeah

[RobBebop]

We don't distribute it, so we aren't required to submit the changes back. We of course, try to contribute back all the changes we possibly can.

Ahh, the Google workaround for the GPL. Selling web-services that RUN Free Open Source Software and NOT selling software. You get to keep the really good changes to yourself. =P

Personally, I'd like to see Google put there code out there as GPL and risk having a meaningful competitor. The benefit of having some really useful software available for high school and college aged kids to learn from would be immeasurable. Also, the transparency and openness of this would be reminiscent of some of the advances made by Bell Labs in the 1970s.

"Do no evil!" - Sweet

[y86]

It's nice to see them giving back.

If only Microsoft would give back on all the mods it has made to the Unix tools. Example: http://technet.microsoft.com/en-us/interopmigration/bb380242.aspx

Re:"Do no evil!" - Sweet

[Machtyn]

Do we really *want* Microsoft to give back the code they've made to *nix tools?

• Anything Microsoft has tried to give to the FLOSS community has had strings attached.
• Just take a look at any of their OS's, particularly ME and Vista.
• Take a look at OOXML.
• Take a look at their "open" license and note that it isn't very open.
• Even Solitaire has a EULA!

Okay, so I made that last one up. I'd provide links, but I'm lazy. I'd also try and look up a eula for solitaire, but I should be doing the work I'm actually paid for, heh.

Re:MySQL?

[nuzak]

Probably because it's a decently-performing ISAM engine with builtin replication. It's not terribly safe (index file integrity is terribly brittle) or smart (it only recently learned there isn't such a date as Feb 30), but you can still at least write ad hoc queries to your tabular data. I doubt Google is using it for HR or CRM.

Re:MySQL?

[LWATCDR]

I prefer PostgreSQL but MySQL isn't crappy.

For years MySQL offered better write a few read a lot databases than PostgreSQL. It may still offer better performance for those types of operations. That is the way most websites used MySQL. It is a good tool for some applications. Slashdot is one of them.
Yes I think PostgreSQL is better but MySQL isn't crappy.

Re:MySQL?

[Frosty Piss]

Why did Google choose such a crappy database?

Clearly they know something you don't.

Transplant to Postgres?

[Doc Ruby]

I prefer Postgres to MySQL. I wonder whether these MySQL revisions will be generic enough to use to improve Postgres.

I also wish these two databases interoperated more. I'd like to use a MySQL proxy to my Postgres server, so apps depending on MySQL could still work, but use Postgres to actually process the data (or just serve as a master DB for replication). Porting apps between DBs, and huge projects to join across different apps' tables in different types of DB servers should be ancient history. Mixed DB-type clusters might not be high performance, but they'd get the iterative development started, after which performance could be just an optimization, which is the right way to do it anyway.

Re:Transplant to Postgres?

[Abcd1234]

I prefer Postgres to MySQL.

Good for you. Of course, your applications may very well be of a different class, and so perhaps Postgres is a better solution. But remember, if you're doing mostly reads, and not a ton of writes, mysql will blow the socks off virtually any other solution. And, coincidentally, that pretty well describes most web applications in general, and probably Google in particular.

Re:Transplant to Postgres?

[shish]

if you're doing mostly reads, and not a ton of writes, mysql will blow the socks off virtually any other solution.

I have a site with 3GB of database, updated once daily, in bulk; the rest of the day it's doing several reads per second over the whole thing (indexed so that it can jump to the right parts for each query; but each query tends to hit a different 5-10% of the rows, so all the data is "active"). I found switching from mysql to postgres gave quite a noticable performance increase -- the whole server was no longer crying in pain and grinding to a halt under heavy load~

Note that the DB server only has 512MB RAM -- while the database was smaller than that, mysql was indeed the faster :3

Re:Transplant to Postgres?

[ShieldW0lf]

Here's a simple DB Abstraction layer for Postgres.  Just stored procedures, so there's zero possibility for SQL Injection.

<?php
class Biz
{
// Internal Database Functions
    static private $cs;

    static function SetConnString($connstring)
    {
        Biz::$cs = $connstring;
    }

    static private function SendQuery($q)
    {
        $db = pg_connect(Biz::$cs);
        $r = pg_send_query($db, $q);
        return;
    }

    static private function SendParamQuery($q, $args)
    {
        $db = pg_connect(Biz::$cs);
        $r = pg_send_query_params($db, $q, $args);
        return;
    }

    static private function QueryForRow($q)
    {
        $db = pg_connect(Biz::$cs);
        $r = pg_query($db, $q);
        $a = pg_fetch_all($r);
        return $a[0];
    }

    static private function ParamQueryForRow($q, $args)
    {
        $db = pg_connect(Biz::$cs);
        $r = pg_query_params($db, $q, $args);
        $a = pg_fetch_all($r);
        return $a[0];
    }

    static private function QueryForRows($q)
    {
        $db = pg_connect(Biz::$cs);
        $r = pg_query($db, $q);
        $a = pg_fetch_all($r);
        return $a;
    }

    static private function ParamQueryForRows($q, $args)
    {
        $db = pg_connect(Biz::$cs);
        $r = pg_query_params($db, $q, $args);
        $a = pg_fetch_all($r);
        return $a;
    }

    // Business Functions
    static function AddJournalEntry($PersonID, $EntryTitle, $EntryDetails, $EntryDate)
    {
        $JournalEntry = Biz::ParamQueryForRow('SELECT * FROM addjournalentry($1, $2, $3, $4);', array($PersonID, $EntryTitle, $EntryDetails, $EntryDate) );
        $JournalEntryID = $JournalEntry['addjournalentry'];
        return $JournalEntryID;
    }

    static function GetJournalEntry($EntryID)
    {
        return Biz::ParamQueryForRow('SELECT * FROM getjournalentry($1);', array($EntryID) );
    }

    static function GetPersonJournalEntries($PersonID, $PageSize = 20, $PageNumber = 1)
    {
        return Biz::ParamQueryForRows('SELECT * FROM getpersonjournalentries($1, $2, $3);', array($PersonID, $PageSize, $PageNumber));
    }

}
?>

Re:Transplant to Postgres?

[CastrTroy]

See, that model falls apart once you have a database with 50 tables. Because you have to load and parse a lot of script for every page load. I'm not sure how much (if anything) is cached by PHP, and how much recompiling has to be done on each load. Also, if you want to break you stuff up into a lot of different classes, with each class in a different file, then it quickly becomes a large mess of stuff to include in each page. I find that this is one of the major advantages of having a compiled language.

Re:Transplant to Postgres?

[dfetter]

Applications requiring one database or another should be ancient history. Why on earth would that be? Modern DBMSs aren't just data buckets. They're full-on application servers that happen to have a really killer storage subsystem. In the Free Software space, there's only one that's really modern in that sense, and it's not MySQL ;)

However, whenever you look up a how to access MySQL from PHP, you'll find stuff that recommends using all the mysql_* functions. This is the quick way to creating an app that absolutely depends on MySQL. That's only a problem because of MySQL's limitations, not because it maxes out MySQL's capabilities.

Really people should be using PDO, instead of mysql_ or pgsql_. Of course the real solution is to use a database abstraction layer, but I've never found a good way to create an n-tier web appliction in PHP. I've never seen an automatic database abstraction layer that was worth a plugged nickel over the long haul. Other kinds--the kind where you create a database-interaction library and mandate that all database calls go through it--can scale out quite huge. The aforementioned approach has the added bonus of real OO design, which makes it *much* easier to maintain. :)

Re:Transplant to Postgres?

[Doc Ruby]

I am in fact using DBI-Link to let Postgres queries connect to data in an LDAP DB. You have good reason to proudly plug the SW :).

You don't know any developers in NYC who would be good for throwing a MySQL datamart up on top of that, would you?

Re:Transplant to Postgres?

[ShieldW0lf]

I've got 86 tables at the moment in my latest project, and 231 stored procedures. It's very easy to manage, very secure, and it's easy to reuse the functionality.

I don't bother using classes to represent the business objects, I just use database views to control the shape of the data that comes back across the stored procedures and pass the properly formatted arrays to another set of functions that generate the appropriate HTML/XHTML/XML as needed.

When it comes time to do caching, I'll have the ability to add that caching to the data layer within the Biz class and to the middle layer within the appropriate view functions as the usage data comes back.

As it scales, I'll have no dirty objects within objects within objects to worry about, just arrays and strings to cache and database replication to set up within Biz.

The database views and stored procedures are the db admins contract with the script writer, while the shape of the various View functions are the script writers contract with the designer. Each role has a great deal of latitude with clearly defined boundaries.

I've written similar systems that used a more object oriented approach, and while it can work well, I've found that either you have the pain in the ass of maintaining an objects clean/dirty state and using lazy writes, or you have the objects operate as a thin layer over a bunch of db functions and you end up with more db traffic than you need. This avoids that completely.

Each their own, but this works well for me.

Re:Very Niiiice

[Chineseyes]

Why on earth would you compare MySQL with Access? I'm more of a Postgres guy myself but even Mysql deserves better than that.

Re:Very Niiiice

[Shados]

Access isn't "competing" in the same area as MySQL though, SQL Server Express is. MS Access would be more competing with SQLite i beleive (which I never used, so don't quote me, but I beleive that is a less server-centric open source DB?).

JET (Access' database engine) is more of a data storage engine with SQL support than an RDBMS, which MySQL is (which could have been debatable until v.5 I guess, hehehe )

6.0 in 2008?

[JumboMessiah]

Mysql 5.1 has been in preproduction since November 2005 and still isn't available as a GA release (aka don't use it in production). Are they sure they can get a 6.0 GA release out by next fall?

This is really good of Google to contribute this back, I'm just wondering how long it will be before we all can utilize their changes. I hate to see the code stay stuck in the devel cycle for three years when Goggle is using it to their advantage right now at this very moment.

Re:Very Niiiice

[jack_csk]

Access is more than just the database. It is a compact tool with little bit of reporting and application development. As a matter of fact, you can have Access connecting to other database engines via ODBC (though the performance sucks in my experience).

Google needs to add an SQL function

[LiquidCoooled]

They need to add a GOOGLE function to allow queries to be searched nicer.

SELECT * FROM articles WHERE GOOGLE('boobies');

something similar might be available but it is a PITA to list the fields to search and specify the operators etc

Re:Google needs to add an SQL function

[RoloDMonkey]

I know your just trying to be funny, but what you are describing already exists in MySQL. It's called FULLTEXT.

Re:Google needs to add an SQL function

[j. andrew rogers]

"They need to add a GOOGLE function to allow queries to be searched nicer.

SELECT * FROM articles WHERE GOOGLE('boobies');"

I don't know about MySQL, but you can pretty trivially write extensions to PostgreSQL that do exactly this kind of thing. In fact, I've written a number of such extensions to PostgreSQL that make Google resources seem like local resources in a PostgreSQL database. (This kind of deep and easy customizability is one of the things I find to be a killer feature of PostgreSQL relative to many/most other databases.)

Re:Very Niiiice

[CastrTroy]

I'd say that OpenOffice Base competes with Access. As does Filemaker Pro (do they still sell that?). Most other databases do not because they are just storage engines, and don't really offer much in terms of a user interface for creating data entry forms, or displaying reports.

Re:MySQL?

[LurkerXXX]

MySQL cannot enforce foreign keys constraints on MyISAM tables. It 'kinda' can on Innodb tables.

Having them and enforcing them so they are actually useful are 2 different things.

And if you'd bother to RTFA, you would see that MySQL is moving to away from Innodb to 'falcon'. "but some InnoDB features, like foreign key support and full-text indexing, won't be supported until MySQL 6.1.".

So MySQL is moving away from the only table types that can actually 'kinda' enforce the use foreign keys at all.

I think that would make you the douche.

Re:MySQL?

[marcansoft]

That's like saying PCs are toys, because banks use mainframes to handle your credit card transactions.

That a device or program isn't suited for a certain task doesn't mean it's a toy.

Re:MySQL?

[Anonymous Coward]

It's not terribly safe (index file integrity is terribly brittle) or smart (it only recently learned there isn't such a date as Feb 30)

http://en.wikipedia.org/wiki/February_30

Umm No...

[cmdrbuzz]

You'd be wrong then. Have a look at the Oracle Store and you can get Standard One for $149 per user (5 User minimum @$745.00)
Or you could get unlimited users for $4995 per CPU....

Oracle is expensive, its just not that ridiculously expensive.

Re:Umm No...

[cmdrbuzz]

Yep Standard is quite different to Enterprise Edition but you can't seriously be comparing MySQL with Oracle Enterprise Edition!

I can't think of anything that is in MySQL that Standard Edition doesn't deliver. And if your looking at EE then really for $40k per CPU you'd be running
something that'd be using the EE features like Label Security or some of the Partitioning / OLAP stuff.

Admittedly as an OCP I might be biased but I wouldn't go near MySQL when there are things like PostgreSQL around, something about wanting my Data Integrity...
Postgres compares nicely to Standard Edition (well once you factor in the costs its pretty neat for the smaller / lower end stuff) but really EE is only competing with
DB2 and even then only DB2 on zOS (which rocks!)

Good stuff coming out of google

[shmert]

For the Java coders out there, Google is also releasing google collections, which looks quite nice. There's a new interview here with the authors. It's fun stuff to poke around in, and appears to be extremely well-written code.

Once this stabilizes, I'll probably be using it. It's nice to see such a direct impact on my work from their contributions. Thanks guys!

Re:Very Niiiice

[david.given]

...SQLite i beleive (which I never used, so don't quote me, but I beleive that is a less server-centric open source DB?).

It's a non-server public domain database library. Yes, really public domain. Its databases live in files and there's no server component whatsoever; it's intended for use by a single application (although it supports file locking so that multiple processes can access the same database).

I use it in one of my apps. It's awesome. It's tiny (about 300kB), it's simple (no messing about with starting servers), it's blazing fast (for some tasks, it's up to an order of magnitude faster than PostgreSQL or MySQL --- for others, it's slower, though: benchmarks). More and more programs are starting to use it simply as a document store. It's great.

If you find yourself wanting to use SQL in a situation where only a small number of apps at a time are going to want access, or even if you just want to teach yourself SQL (it's got an excellent command line shell), do check it out.

Re:MySQL?

[LurkerXXX]

MySQL has many 'gotchas'. Google around for many sites with lists of them. They are slowly cleaning them up, they have a very bad track record of not keeping data clean. Even their latest 'strict' rules still aren't all that strict. The gotchas have traditionally contained plenty of broken foreign key problems.

The latest versions of Innodb are much better, (earlier versions didn't do any of what you said very well) but now they are going to be moving away from even that.

I do use SQL Server, and Oracle, and Postgresql. Firebird looks interesting, but I haven't had time to play with it. I've had the unfortunate experience of having to work with some MySQL databases, but I refuse to work with that anymore. I prefer mature databases with a full set of database features. Broken databases written by folks who say idiotic things like 'You don't need transactions' don't interest me.

Re:MySQL?

[einhverfr]

Actually, there are several cases where you may think that MySQL has foreign keys when it doesn't. So the support of foreign keys is not entirely complete.

If innodb is not installed, you get a MyISAM table without the foriegn key enforcement and not even a warning is given on table creation (you do get a warning when you insert, but the application is unlikely to be watching).

CREATE TABLE table2 (
        id int autoincrement primary key,
        foreign_id int references table1(id),
        test text
) type=innodb;

CREATE TABLE table2 (
      id int autoincrement primary key,
      foreign_id int,
      test text,
      FOREIGN KEY foreign_id REFERENCES table1(id)
) type=innodb;

In one of the above examples (won't say which one ;-) ), you don't get foreign key enforcement. No warning. just no enforcement.

Yes, MySQL has foriegn keys. It doesn't have them 100% but it does have them.