Slashdot Mirror
MySQL 5 Production in November
thatoneguyfromphoeni writes "CIO.com is reporting that MySQL AB is eyeing Nov. for the production release of MySQL 5. 'The company is calling version 5 its most significant upgrade yet. It adds a handful of features considered important for enterprises that have long been available from market leaders Oracle Corp., IBM Corp. and Microsoft Corp. Chief among them are triggers, views and stored procedures.'"
http://developers.slashdot.org/article.pl?sid=05/1 0/05/2041240&from=rss
Different link, but same idea anyway.
MySQL has finally caught up to the state of the enterprise relational database industry...as it was in 1999. Points for effort, but everyone else is still ahead with core features like integrity, leaving them free to build on new and better features. Bundling with PHP will only get you so far.
Sneaking in just in time before MS SQL 2005 can get out the door (or perhaps just after) is good for this.
I recently showed the latest rev to the SQL devs here, and they were most impressed. Most of the complaints about it were gone; the new GUI is miles beyond what they had before, and the new features (views, stored procedures, better VARCHAR support) have people thinking that for smaller projects, MySQL will work out just as well as MS-SQL, and at a fraction of the cost, if any cost at all.
You can never go home again... but I guess you can shop there.
What is the real impact on MySQL 5.0 now that Oracle has bought InnoDB?s p
http://www.eweek.com/article2/0,1895,1869989,00.a
See the MySQL documentation for triggers, views, and stored procedures, respectively. To answer your question, if you don't know what they are, you probably don't need them.
For any developers that use stored procedures on a regular basis, you will appreciate how significant a step forward in the right direction this is! The investor in me thinks Oracle stock might be a good thing to buy :)
LINUX ONLINE POKER: Linux Poker
Not everyone needs the same set of features in their database product. Some people want something that is very lightweight and fast.
Other people want bulletproof "unbreakable" databases with thousands of features. Some people want something right in the middle.
Having a variety of solid choices is not a bad thing. You should't be affraid of a little competition, as it is good for the entire market.
Kinda odd that they'd make such an earth-shattering announcement on a 404 page?
Once I was a four stone apology. Now I am two separate gorillas.
As much as I love (and use) mySQL, it's still not, nor will it likely ever be (never say never) an enterprise solution. Prove to me that mySQL is robust enough to be the backend service for a major bank's mortgage application, for example. It's simply not. As a previous poster already mentioned, mySQL has finally caught up to the base set of features that all major DBMS's had years ago. Now, after that rant, I will say this. mySQL is great at what it's designed to do. I use mySQL as the backend for personal websites and applications. It's (relatively) lightweight, simple, easy to administer, and, best of all, free as in beer (not withstanding products purchased from mySQL AB). So before you get all huffy about what I said in the first paragraph, just remember that mySQL is great at what it's made for, it's just not made to be an enterprise solution.
A trigger is something that is invoked on the database server when a predefined even occurs. For instance, an update to table 1 in database A could make the database server update table 2 in database B automatically.
A view is a way of making a pseudo-table. You can create something that looks like a single table, but can contain columns from multiple tables. If you have table 1 with columns A, B, C, D and table 2 with columns E, F, G, H , you can create a view 3 with columns A, C, F, H.
A stored procedure is something that is precompiled and put on the database server that performs a number of actions when called by a client. It can replace a complex series of SQL statements, say, in such a way that performance is much improved over having separate statements that would need to make multiple calls to the server.
So they finally add features that postgresql has. And don't forget the more difficult to use licensing they have imposed compared to postgresql.
Oracle has just announced that they are open sourcing their famous database platform. Long live free software.
The above post is factually incorrect. Oracle acquired Innobase Oy, the firm responsible for InnoDB, a database engine commonly used with MySQL. Many people are obviously skeptical of this acquisition.
It's highly unlikely that they would ever donate their enterprise product to the open-source community. Suffice it to say that the shareholders would not be pleased with a bankruptcy filing.
Do you like German cars?
Errr, ok I'll bite. Anybody correct me if I'm wrong, since 99% of my SQL work has been MySQL 4.xx these are all things that I've heard of but never used so I may be off by a bit:
Triggers Automatic stored procedures executed on some change to the database. Views Basically a persistent select/join that looks like a separate table. Any changes to the view are "copied through" and made to the underlying separate tables. Stored procedures Code that lives on the database backend that can be call RMI-style by your application. Usually pushed there by the application at some point. Stored procedures can sometimes make changes more efficiently than the application itself because the data doesn't need to flow back and forth between your layers as much.I agree. Many people don't need an enterprise database. For, say, a blog, forum, or a simple access-restricted website, MySQL is more than good enough.
I was using all of these in PostgreSQL in 1999.
MySQL is just *now* releasing stored procedures? Unless I'm missing something, that means that client applications have been sending dynamic SQL to the server all this time. Dynamic SQL means poor query optimization, table-level SELECT, UPDATE, and INSERT privs, and a much easier vector for SQL injection attacks.
Feel free to chime in if that's wrong, but to me it sounds like in this case, free = vulnerable.
With Love,
AC
Thank you, much appreciated. :)
You have attempted to launch a SQL injection attack on slashdot.
You have failed.
Please try again with the correct schema.
What sort of hardware is behind RubyForge?
240000 hits/day is just under 3 hits/second, after all.
When you consider the power of today's hardware, it should be able to cope with such a load, even when doing fairly heavy database activity.
Cyric Zndovzny at your service.
Not having them has made me a better designer, but I've bitched and moaned the entire way. Will I become more effective? Will I get lazy? Will I care?
Paper Pusher
In case you haven't already heard, Oracle purchased the makers of Innodb! Bye Bye InnoDB
ERROR 1064: You have an error in your SQL syntax near '5
)' at line 6
It makes no sense why they didn't buy out Innobase a while ago. Now that Oracle owns Innobase, they are ultimately at Oracle's mercy for much of their development since MySQL uses Innobase's code for a lot of their work. They really should have bought them out a while ago and integrated them into MySQL as a company before Oracle could get their hands on them.
Click here or a puppy gets stomped!
I know that much has been said on the subject and each side has some valid points. Basically if you don't need anything that mysql doesn't have then it's great. But here are two questions that I am honestly intersted in hearing some answers to:
1) You always hear about people who moved from mysql to postgres and "never looked back." I definitely fit into that category and it's pretty obvious to me after using both system why I would never ever ever want to switch back to mysql. I can't even count the number of times I need to do something in postgres that mysql either doesn't do or doesn't do well and I don't even know what I would do if I were still on mysql. So the question is: is there ANYONE who has made the move from mysql to postgres and then regretted it? Even for little things like full text search that doesn't require setting up a separate module?
2) Is there ANYONE who has made the switch from postgres to mysql and stuck with it and not regretted it?
I know you have to be careful with anecdotal evidence but I have simply never heard any story from any person ever that has regretted the move to postgres from mysql and I have never heard of anyone who has switched from postgres to mysql.
Is there anyone out there with a story that fits into one of those categories that would paint mysql in a posative light?
Not everyone needs the same set of features in their database product. Some people want something that is very lightweight and fast.
:)
Agreed. If you're not going to use feature X, Y, or Z then there's no point in buying a product that has it over one that doesn't. Just be damn sure that you won't ever need those features though -- if you discover that you do then you may be in for a world of hurt.
That said, the free RDBMS's are covering 90-95% of most people's needs at this point. If you need non-relational, or much more advanced replication/scalability capabilities then you're much better off sticking with the "big boys".
Other people want bulletproof "unbreakable" databases
The one nit I wanted to pick -- sorry, but a bulletproof "unbreakable" database is not an optional feature. If the database backend isn't solid and reliable then you should not use it for anything beyond toy programs or data. I believe MySQL long ago put data corruption issues behind it, so I'm not slamming them, but data loss (or rather, non-data loss) isn't something you should ever consider optional.
Yes, you still have to backup, archive, etc. your data, no matter what vendor you use. But that should be for disaster recovery scenarios and hardware failure only -- not for "oh fuck, a bug ate my db". Or, even worse, "What do you mean the database has been corrupted for the past 3 months?".
You should't be affraid of a little competition, as it is good for the entire market.
You say that merely because you aren't king. King's generally disagree with the idea.
they never are, though.
it's probably more like "8 hits per second for 8 hours; 1 hps for 16."
which is still a joke for static content, but for dynamic, it's respectable.
I mean, I never see a host offering MySQL. all they offer is PostgreSQL!
So maybe people will clean up Oracle's _nightmarish_ security track record, because unfixed critical bugs for years is not an uncommon sight in Oracle land. I'd take security over features anytime.
Don't get me wrong, i know you're just a troll, although MySQL was/is lacking in some ways, as i'm perfectly aware of that, but nothing compares to not giving a sh*t about security especially in case of an enterprise level software costing so much in cash and resources.
These Oracle issues are well known as a lot of security experts prove it day by day.
It takes a man to suffer ignorance and smile
Be yourself no matter what they say
Does MySQL5 make their default table type ACIDic, or do you still have to use InnoDB if you want that?
It seems silly (well, dangerous) to have triggers, etc. without transactions, so I'm inclined to think they finally went ACID. But if that's the case, who cares if Oracle bought Inno, so I'm inclined to think they didn't.
Anyone actually know?
MySQL has finally caught up to the state of the enterprise relational database industry
One of the things that is missing in most if not all databases (AFAIK) is an effective mechanism to deal with data dependencies. A data dependency exists whenever two or more applications of components share read/write access to the same records in a database. We need a simple to use mechanism that will notify relevant applications of changes in a shared record. Resolving dependencies in a timely manner is extremely important to system or network reliability. Data dependency problems also exist within applications but that's a slightly different issue, one which is made all the more hard to deal with by the algorithmic nature of software.
I hope I never have to maintain any database you're responsible for...
Performance wise, how does this new release of MySQL compare with other open source databases like PostgreSQL, Firebird and perhaps even SQLite?
Cyric Zndovzny at your service.
Yes, mostly mainframes, but I've no doubt that some industries were running
"enterprise" apps 5 years ago on platforms that aren't as robust as MySQL5 is now. Yes, software has become more demanding in the past few years, but the fundamentals haven't changed. If you could run 'enterprise' solutions on SQL Server 6.5 (and I saw companies doing it - and gosh, they didn't even have row level locking!) surely some "enterprise" industries can use MySQL5 today.
creation science book
I would like to add to your third definition. A stored procedure is nothing more than a pre-parsed and pre-planned SQL command. Whenever you send a SQL command your RDBMS has to waste precious time figuring out if the SQL command is valid, then how to execute it properly. When you use a stored procedure, it is already parsed and planned, so when you call it it will go straight to the first step of the execution plan. This is why stored procedures are so much faster than interpreted SQL even when it is just one query. While yes, one of the things you get is the ability to run a bunch of commands at a time, what makes it special is that these commands are already validated and planned.
Pedro
----
The Insomniac Coder
Let's see... All the heavy hitters that are making use of MySQL are using it because... it's such a shitty database? Tell me this againe? Me thinks the stagnation of PostgreSQL in the enterprise market has some people stamping on sour grapes...
What has always been nice about mySQL is the ability to use different table types for different needs. If you can't use Inno, use Sleepy Cat's Berkeley DB.
Just do an ALTER TABLE with a TYPE clause.
ALTER TABLE table_name TYPE = BDB;
It's that easy.
In synopsis, these three things make DBMSs more intelligent, and allow them to be the repository of business rules while your client apps remain simpler and easier to maintain.
I see Views (duh) as a versatility tool.
You can have a view that merges several tables in whatever way you see fit to solve a problem, then at call time You have to write less, You can make more concise SQL code.
Let's give you an example: You have the sales detail info for your company's retail stores, and given the data's size you have individual tables for each year or semester, you could use a view so when coding that gargantuan report of pencil sales from 1999 to 2004 summarized by minute (*grin*) you only have to build the SQL statement as if it were a single table. In this case, by wrapping your jumbo SQL instruction in a view, you've made your life easier.
Triggers are useful because you can program the DB to react to certain data and execute something. You could program a trigger to react when an employee record goes to certain status (say, he/she is fired or quits) and change the status of this employee's records all over the database to 'immobile' or 'retired'. You could do it on your client code, but it's not always much elegant. It's better to abstract these operations so the client code doesn't gum out in this kind of details. Also, care must be taken to keep the triggers light, or not use them at all when perforance is paramount.
Stored procedures also give you the ability to abstract operations. Say you have the previous example, If you issued the operation with explicit sql it might look like " UPDATE employee SET Status = 9 WHERE Employee_Id = 73829992 ", with a stored procedure you could make it look more mnemonic, self documenting: " spChangeEmployeeStatus @Id=73829992, @NewStatus=9 ". This update SQL is a one liner, so maybe it's not the best example, the more complex ones are better candidates. In a performance - conscious application you might want to zap those triggers and instead put all the logic in stored procedures and execute them as needed.
According to mysql.com, only the paid version of mysql (network) will get the enterprise-level tools. The free (community edition) is not certified for enterprise operation, hence still (far) inferior to postgresSQL.
SCO may claim they paid mySQL Corp. solely to ensure to MySQL database runs on SCO Unix, and they will then turn around and sue saying mySQL added enterprise features to mySQL using SCO funds/resources that were supposed to be allocated to it running on SCO (and they'll make up BS on how mySQL doesnt run properly/optimally on SCO cause of the resource diversion).
Seems ridiculous that they would sue. But, we all know the track record.
mySQL is tainted by SCO money (unless the corp decides to reurn money and cancel agreement), so use postgresql instead.
It's nowhere near as mature as InnoDB, at least so it could be used as a drop-in replacement:
g e-engine.html)
n odb-will-fork-save.html
"Even though Berkeley DB is in itself very tested and reliable, the MySQL interface is still considered gamma quality. We continue to improve and optimize it."
(http://dev.mysql.com/doc/refman/5.0/en/bdb-stora
The people at Sleepy Cat see a possible opportunity though:
http://devtoe.blogspot.com/2005/10/oracle-buys-in
For blogs, SQLite would be more appropriate. I believe MovableType already supports SQLite. This would allow people to run blogs on cheaper hosts.
If you're not using firefox, you're not surfing the web, you're suffering it.
---
that whole SCO/MySQL partnership thing. WTF are they thinking? http://www.eweek.com/article2/0%2C1895%2C1855483%2 C00.asp
Personally, I don't think that my personal blog needs an unbreakable database back-end. I only have 64MB of RAM on my server which has to hold the kernel and the entire LAMP stack. I can tweak and tune MySQL to run very, very quickly on less than 12MB of RAM. In my case, lightweight and functional outweigh any feature or reliability constraints. If you plan for failure, it doesn't hurt nearly so bad. That is why I perform regular backups, and gasp, even practiced a recovery scenario a few times. I certainly don't doubt that there are many cases where having 100% data reliability is the most important thing. My blog and personal websites don't fall into that category though.
mostly right, sortof. Have a look here: http://developers.slashdot.org/comments.pl?sid=165 189&cid=137834925 189&cid=13783454
and
http://developers.slashdot.org/comments.pl?sid=16
One of the biggest advantages of enterprise level dbs is that they are not file based. That is there is one or more files that can store many table/indexes/etc.
Postgres recently added tablespaces, which allow you to specify a sub folder for these files. This is better then before, but not nearly where it needs to be.
There are advantages to having single files versus a larger file, but most are administrative in nature. It can also lessen the effect of corruption. A sinlge table might fail and would not effect others. If a big database file corrupts it can damage a lot of data.
Having a single files allows quite a few things.
First it greatly reduces File system level fragmentation. The file grows once and the sectors are right next to each other. When you have 10+ gigs of data this is a real concern.
Second it create a unified caching mechanism. The big file is broken into pages, generally 8k, which in turn store data rows. The data is not only user data, but indexes, system information. Other pages are used to store stats about other pages, and have header information about the file itself. Why is this important to caching, because you simply have a cache table, everytime a page is loaded it gets cached. Writing to page happens in memory and then written to disk. Enterprise dbs have huge caches. This is why 64 bit is so important for dbs, so we can have larger then 4 gig caches.
Third is backing up. Some might say the file backup is easier, I beg to differ. Especailly when it gets big. When you go to backup you backup each page in the file. You mark each one as being backedup. At the end of the backup you backup the write ahead log. This allows you to restore to exactly the time the backup finished. Lastly, a diff backup simple looks at each page that has changed since the backup and backs only those pages up. Diffs can be very fast and faster to restore then a write ahead log.
Also, single files on different drives arrays to increase performance. This is also capable with tablespaces. The good part is that the database knows only the file id that tables go on, and then file id corresponds to a file name on any path. With the right tool you can move the files around easily.
Replication is also easier because writes are to a file id and page id. The replica database can have files place on any drive at any map point as long as the data.
Both mysql and postgres got a way to go, but they are very nice products and one day can easily compete with the big boys. Although it will be a while before they are able to run high end clustered box with shared storage and super high speed interconnects, but if you need that kind of power, you've probably got the money...actually you absolutly do if you can afford the hardware.
Well, I didn't switch. But as a shared server admin who's been running MySQL for years and years, I did take a looong look at PostgreSQL. It had all the features that MySQL didn't have, and that was good. However, the authentication stuff sucked badly, so I retreated back to MySQL for the 50+ users needs.
Truth to be told, the server runs both. It's just that I don't want to create new users for PostgreSQL - I personally only want to deal with MySQL. Another admin handles PG.
I see the one huge thing in PostgreSQL 8.2 is 2 phase commit.
What this means is you can run a multi-master replication system of databases. MySQL, eat your heart out!
Other things of note: One guy claims he sees a 20%-40% improvement in speed in smaller queries. That is breath-taking.
And the equivalent (and frankly, better implementation of) table partitioning is here! Now there is NO REASON to use Oracle over PostgreSQL. Oracle has lost all of its competitive advantages when 8.1 is released!
Predictions: New Oracle installations drop significantly. PostgreSQL becomes the new DB of choice among the illiterati in the DB community. Salaries for PostgreSQL admins and developers increase significantly, causing a huge demand in PostgreSQL training. News articles about people implementing PostgreSQL and having problems (man bites dog is no longer implementing PostgreSQL, but NOT.) IT costs drop significantly. Oracle lowers licensing fees and relaxes their strict licensing terms. Oracle begins shifting focus from the database itself to the apps running on the database.
The radical sect of Islam would either see you dead or "reverted" to Islam.
It works quite nicely and I hope they take the opportunity to improve from both mySQL and Sleepycat sides. It's always been my favorite table choice with mySQL.
Mostly I bring it up to get out of the Oracle bought InnoDB rut because one of the cool features of mySQL is the availability of tables to use. And I guess that is one of the great features of open source in general.
Some people want something that is very lightweight and fast.
Which is why they are switching to PostgreSQL.
Your assumptions about the footprint and speed of PostgreSQL are way out of date.
PostgreSQL is much easier to administer, much easier to develop for, and much better solution that it used to be for high-performance and low-performance shops. It has grown from its niche and began occupying the niches owned by MS SQL, MySQL, and Oracle.
The radical sect of Islam would either see you dead or "reverted" to Islam.
Isn't Innodb providing all the advanced features (ACID) for MySQL? What good is 5.0 when Innodb is walking with Oracle now? Where can they dig up yet another engine?
Off Topic? Did the moderator lose his funny bone? Does the moderator know who John Titor is? Humor related to the story is never off-topic.
Slashdot: Where nerds gather to pool their ignorance
"MySQL doesn't have triggers or stored procedures and views" to "Even if it does have triggers, stored procedures and views, it's still not a real database like Postgres/Oracle/SQLServer".
We have two websites, Boats.com and Yachtworld.com - Boats has an Oracle backend databsae, and YW has MySQL using the InnoDB engine.
The uptime is about the same for the two. We've had some issues on the Yachtworld database box due to 3ware drivers in Linux - they were corrupting pages in the database. Guess what? Innodb recovered without any lost data. Twice. This was a driver/hardware/linux issue, not a MySQL issue. We now appear to have a stable set of drivers, and I expect the MySQL database to hit 100% uptime pretty much every month.
Yachtworld gets several million distict page views per day, whereas Boats.com gets half a million.
Our MySQL database runs on a dual-opteron server, with 8 gig of RAM, with 6 gig of it allocated to the innodb block buffer pool (it caches row and index data so you don't have to go to disk).
Try doing that in Oracle 10g on Linux. The SGA (Shared Global Area) can't get larger than 1.7 gig unless you,
1) Use memory as a temporary file system so that Oracle can cache a bit more, and you also get the benefit of dicking around for several days, trying to configure your machine to try to take advantage of it (if it even can - we were never successful).
2) Remap all the shared libraries so that they load in a lower memory address, to squeeze another few hundred meg of memory.
Postgres (last I checked) preferred to let the OS do the data-caching. Thanks, but no thanks. And no 64-bit version (though I've read a few people have managed to compile one, I wouldn't trust it unless Postgres gave it the thumbs up).
MySQL with InnoDB is straighforward (it's use of tablespaces, replication, tuning, and even compiling from source - someone with mediocre Linux skills like myself can do it without issue every time).
MySQL with InnoDB is very fast, very reliable, and has awesome support via the MySQL mailing lists.
MySQL is very well documented, with lots of great third party books that don't cost an arm and a leg (unlike an Oracle library).
MySQL does not have stored procedures, triggers, and views in the current production version.
Here's what I think of that:
1) Triggers are hidden application logic that are very hard to debug, and are easily overlooked or forgotten by developers. Business logic (other than defensive logic like unique indexes, primary keys, foreign keys, not-null columns) does not belong in the database. They belong in the middle tier. They also make it much more difficult to move to another database.
2) Stored procedures are like PERL - it's very easy to make a mess unless you are very careful. They are also hidden logic, and very difficult to debug. And again, keep that logic out of the database. They also make it much more difficult to move to another database.
3) Views are a nice feature, but most often used to support business and reporting. I don't like managers connecting to the database to run queries (SELECT * FROM very_large_table_1, very_large_table_2; and suddenly you have cartesian join that results in tens of millions of rows coming back, bogging everything down). To do reports, views aren't necessary.
If you think MySQL is not a "real" database, it is, and has been since 4.0. As an Oracle (and now MySQL DBA), I can honestly say that I can't wait to dump Oracle and get the Boats.com website over to MySQL.
And for the few people who made comments like, "Do you really want your bank running on MySQL?": many banks run on old, legacy hardware and systems. Transactions are written out in many places (with geographic diversity) to ensure that a hardware or software crash is recoverable. There is no reason why you couldn't put MySQL in a situation like that, so long as the same precautions are taken.
Some people want something that is very lightweight and fast.
In which case they should use something like SQLite, which unlike MySQL doesn't claim to be more than it is, has a better license and actually sticks with standards.
Your predictions ignore non-technical issues. Oracle has brand recognition, massive marketing, and consultants. Large enterprises want a significant company backing their software. Oracle may be expensive, but they provide the services large companies want. Most IT managers are familiar with Oracle and have no idea what PostgreSQL is.
Developers: We can use your help.
Will be a busy month for us DBA types. Should be fun.
Whenever MySQL vs PostgreSQL comparison comes up, there are always people who claim that MySQL still has its own niche of the "not ACID but fast" database for basic things. Is it alone there, though? How well does it compare with e.g. SQlite?
Not to echo, but I made the switch and have never regretted it. Postgres is so much easier and more professional.
http://blog.grcm.net/
You
would trust his data
Your comment
to such a provable piece of shit.
The MySQL database used to run Slashdot.
Suppose I create a table:
create table my_table(
id int autoincriment,
fk1 int references other_table (id)
) type=innodb;
Hmmm... Foreign keys are not enforced (with no warning issued by MySQL as to this behavior) but if I do
create table my_table(
id int autoincriment,
fk1 int,
foreign key fk1 references other_table (id)
) type=innodb;
Now they are enforced. Why the difference?
If I do a lot of inserts/updates/deletes on an innodb table, how do I recover space?
If you want a real RDBMS at a fraction of the cost or no cost at all, I would suggest going with PostgreSQL. My site has a whitepapers section where we offer a MySQL to PostgreSQL migration guide.
LedgerSMB: Open source Accounting/ERP
if you have a db with a few TB of data, and some complex queries going on, it would be pretty impressive to serve 8 pps.
now, I'm sure rubyforge isn't at that level, but from what I remember about the SF code base, I doubt it's trivial either.
More accurately, if you don't what they are, you probably don't know that you need them. Or even better, if you don't know what they are, I really hope you're not supposed to be a DBA, designing and maintaining database systems.
The above post is accurate, but don't be confused and think "prepared SQL" is the same thing. Prepared SQL is similarly pre-parsed and pre-planned so you get decent performance benefits. And no chance of SQL injections hacks. (Why do so few people use prepared SQL? It's not complicated!)
Also bear in mind that a stored procedure could be *anything*. Postgres allows you to use perhaps half a dozen common languages; Oracle and DB2 similarly. I have no idea what MySQL allows- probably similar. You could write your whole application in the DB if you really wanted to.
http://blog.grcm.net/
Beta 3 is expected to be the last beta version of PostgreSQL. This month, it seems likely that PostgreSQL 8.1 RC1 will be released. I would expect PostgreSQL 8.1 to come out in November or December of this year.
LedgerSMB: Open source Accounting/ERP
We have the data available to us.
g e_site_status.html
http://rubyforge.org/docman/view.php/5/11/rubyfor
The PostgreSQL database contains about 3.2 million records and takes up 600 MB of disk space.
600 MB is obviously not a few TB. It's not even 1 GB!
RubyForge is currently running on a single machine with two 2.8 GHz Xeon CPUs, 2 GB of RAM, and a hardware RAID 5 SCSI array of 210 GB.
They have 2 GB of RAM for a 600 MB database. Even assuming the web server, mail server, Linux, etc., take half of the available real memory (probably unlikely), that still leaves 1 GB or so for the database. More than enough for the database itself to be resident in RAM.
Cyric Zndovzny at your service.
Bad example. If an employee quits or is fired and you have to change status in many different places, your schema is broken. It's a decent example for a trigger, but as with most uses of triggers it's not something you should actually do. Having triggers available is important, but you need to be very selective about when you use them.
Then again, your trigger example could just be the first step in a system-wide cleanup. By mixing views and triggers, you can slowly change your underlying architecture without affecting your currently-running applications.
In most cases, I'd still build a stored procedure for that one-liner. It simplifies your permissions model by allowing you to grant access only to stored procedures, and also allows you to give a user the ability to only change that status column (because that's all the stored proc does). If you were doing this with dynamic sql, you'd have to give UPDATE access on that table, and now the caller could change columns other than status. This also keeps status updates uniform across applications, where each app that needs to update a status calls this stored procedure rather than writing another UPDATE query. Also, using stored procedures helps prevent SQL injection by using typed arguments (you have to call the procedure using bound parameters rather than just passing the dynamic string '"exec sp_foo @id = " + myId', of course), and there is the minor performance benefit of compiling that statement so that the engine doesn't need to parse it every call.
I'd go as far as saying that in most cases you should use stored procedures rather than triggers, as the logic is more straightforward. Triggers have their place, but as I already said you should be careful about when you use them.
1) Inconsistancy in how create statements are handled. For example:
;-) )
create table table2 (
id int autoincriment,
fk int references table1 (id)
) type=innodb;
does not enforce the foreign key even in 5.0 while:
create table table2(
id int autoincriment,
fk int,
foreign key (fk) references table1 (id)
) type=innodb;
does enforce them.
2) Even with strict mode, any application can turn it off, allowing it to add bad data (try adding Feb 31, 20005 as a date in MySQL with strict mode off). This is a violation of Date's Central Rule.
3) "clustering" only works on tables that are cached entirely in RAM so if you use this you must have a beefy machine.
I know you like DB2, so you won't mind if I point out that an area Oracle has a problem is that it treats empty strings and NULL varchars as identical. Unfortunately no RDBMS is perfect.
Now for additional information on your complaints....
quite a few deviations from ansi sql - everything from comments to weird create statements
One should point out specifically that operators are non-standard leading to *very* unportable code.
mysql's performance on innodb was better for mixed environments, but innodb has a bloat problem that can get serious.
This is caused by the fact that innodb uses something like PostgreSQL's MVCC system but lacks an ability to vacuum the tables.... And MySQL people attack PostgreSQL for the need to vacuum the database....
no support for query parallelism, partitioning, etc - isn't 1/40th the speed of a commercial product for many queries.
BTW, PostgreSQL 8.1 will have much more useful table partitioning, and there is a project (Bizgress) aimed at adding parallelism across nodes in business intelligence environments (we had this discussion before once on Slashdot
LedgerSMB: Open source Accounting/ERP
Slight modification: A stored procedure is nothing more than a pre-parsed and pre-planned SQL batch . You can do much more than a single command in a stored procedure. Not that you couldn't use a stored procedure for a single command, or even shouldn't. I'm a big fan of only interacting via stored procedures between my app and my database. That applies even when all I need is a "trivial" query or update.
The create table example should read:
create table table2(
id int autoincriment,
fkey int references table1 (id)
) type=innodb;
LedgerSMB: Open source Accounting/ERP
Alas, I still cannot replicate one database from one server, and another database from another.
Maybe in version 8...
aoeu
SQLite!
They use MySQL all over the place. Local search, shopping, finance, groups... the list goes on. These are applications that scale way beyond any enterprise application.
I realise that Open Sores is the way forward, so can the much over-hyped MySQL do "transactions" yet? Oracle and Sybase have had this feature for years!!!
If you have to ask what a transaction is, you clearly don't "get it" LOL!
I'm serious! I work for a fortune 100 investment bank, any technological edge we can get will enhance our competitiveness at the expense of our rivals. (we even use the shareware OS Linux on some of our blade-servers!) and I hear MySQL is great for "hobbyists" but can a "shareware" solution really cut it in the Investment Banking Space????
That's as wrong-headed as telling Windows users who're plagued by security problems, "If you don't know what user accounts and file system permissions are, you probably don't need them."
Ignorance of complex but powerful features is not the same as the considered rejection of those features.
Forgive me if I don't get excited. I don't use products from companies that make deals with the devil.
MySQL working with SCO
What worries me is that new acquisition of InnoBase by Oracle a few days ago.
InnoBase is the maker of InnoDB, which is the full featured dual licensed storage engine with transactions, referential integrity, hot backups and more.
The GPL version of MySQL will not be affected should Oracle decide to misbehave.
What may get affected is the commerical version of MySQL. Oracle can demand a hefty price for relicensing InnoDB, when the contract is up for renewal hence choking MySQL AB financially, by depriving it from the revenue stream of commerical licensing MySQL with InnoDB.
This may in turn cause long term trouble for the community by depriving it from contributions by MySQL.
I hope Oracle does not do that, but still, they are a corporation with no open source culture, and may have the mentality of choking the competition, using the very rules of open source dual licensing.
Or, they may be softening MySQL to buy them cheap in the near future ....?
2bits.com, Inc: Drupal, WordPress, and LAMP performance tuning.
More likely, if you don't know what they are, and you are evaluating database engines, you are underqualified for your job.
sic transit gloria mundi
MySQL has a column-level privilege system, not a table-level privilege system. It also has never had "static SQL"; however, you're confusing stored procedures with prepared statements. MySQL has had prepared statements and a cache of query optimizations for frequently called statements since the 4.0 release.
So yeah, of course, Cessnas should definitely be more common than Boeings, but buses don't fly at all.
Are you adequate?
actually, slashdot, sourceforge, etc. use db2. They switched over a couple years ago, but they don't like to talk about it.
Do you even lift?
These aren't the 'roids you're looking for.
postgresql joins qmail and openbsd in the proud and honored tradition of insulting potential end users with a supremacist attitude and ego the size of galaxies... ...and then join in the feverish hand wringing wondering why few people want to use it...
(hint: you win few converts by talking down to them)
I think you are just trolling.
i d=4297&group_id=1#mysql
To being with Slashcode ( what drives this ) will *only* work with MySQL ( http://www.slashcode.com/docs/INSTALL ) and has never supported db2.
Secondly, i have a sourceforge project and i can connect to a MySQL database from my project's sf-hosted server-side page.
http://sourceforge.net/docman/display_doc.php?doc
Lima India November Uniform X-ray
Get real! This is the same kind of predictions about USENET imploding that occured every few months so many years ago. Never happened. And since when is it such a horrible thing to express an opinion? If you look back over my posts I stated the reasons I made a particular choice. Did not say anyone else had to follow that choice. If you like mysql and it does the job for you then use it. I've been there done that and finally decided that postgresql for various reasons works better for the projects I work on. kevinadi has even stated in his own posts that he sees benefits in using postgresql. Funny what learning a few new things can do for one isn't it. :)