Slashdot Mirror
Ask Slashdot: Which OSS Database Project To Help?
DoofusOfDeath writes "I've done a good bit of SQL development / tuning in the past. After being away from the database world for a while to finish grad school, I'm about ready to get back in the game. I want to start contributing to some OSS database project, both for fun and perhaps to help my employment prospects in western Europe. My problem is choosing which OSS DB to help with. MySQL is the most popular, so getting involved with it would be most helpful to my employment prospects. But its list of fundamental design flaws (video) seems so severe that I can't respect it as a database. I'm attracted to the robust correctness requirements of PostgreSQL, but there don't seem to be many prospective employers using it. So while I'd enjoy working on it, I don't think it would be very helpful to my employment prospects. Any suggestions?"
I've used Postgres commercially for years, with a number of employers. It's a great DB and having dealt with MySQL, SQL Server, Oracle, et al I'd never go back - though the softies tell me that SQL Server is much better these days.
I'd be surprised if you can't find plenty of work using Postgres. Maybe it's one of those things people don't feel comfortable talking about - like Delphi in the 90s. Plenty of people used it, but few would own up to what made up their "secret sauce".
It's seeing a constant rise in usage. Also many projects (spacewalk!) have it as the only viable alternative to Oracle.
Small companies with small to mid sized applications use it (see Jira or Fisheye, at Atlassian) as their main development platform.
Also you shouldn't use your USA'ish perspective and only do something because it will benefit your job or future employer. OSS is about sharing, fun, knowlege and getting better. Getting better at your job is a welcome side effect.
You will probably be happier in the fewer postresql shops. Think about it do you want to get it done quick and dirty or the right way?
No sir I dont like it.
If you are an active member committing to a major database's code, then it will help your employment prospects no matter what. If you're committing to PostgreSQL regularly, that's strong evidence you are good at what you do.
>> Any suggestions?
Hierarchical DBs have been making a comeback recently, often reclothed as "NoSQL" databases specializing in "big data" analysis. There seem to be many opportunities to make these databases more applicable to current problems or just easier for relational DBAs to understand and implement.
Maybe I'm reading what you're asking wrong, but I mean really, your base assumption of joining an OSS database project is to lend your self-admitted rusty expertise? No thanks.
If you want to get up to speed join a site like Stack Overflow and start ploughing through database questions, it'll exercise your brain and probably help some people who need it.
Then when you are in the groove again join a project.
Only you can answer that question. Good luck!
Oracle
Jehovah be praised, Oracle was not selected
Yeah some of the quirks in that video are like Javascript's ones.
They appear to be major wtf's however real world usage never hits them.
The quality of a product isn't determined by the number of quirky edge cases it has.
You might want to take a look at MariaDB, it's a continuation of the MySQL project by the original author of MySQL.
Bits of code, random ramblings: jakimfett.com
I actually love MySQL, but FWIW, someone noted a while back that Salesforce.com has announced intent to hire about 50 top gun PostgreSQL guys in the coming year. It seems obvious that they are preparing to unhook the money siphon leading to Oracle. Assuming Salesforce follows through, all the herd-following executives in the U.S. will want to do the same. So I predict that demand for PostgreSQL talent will be pretty good for many years.
The video shows a number of ways that MySQL seems to insert questionable data; ignoring NOT NULL, inserting default values when no default is specified, etc...
There are two databases that I have had to repair... Hypersonic and MySQL. MySQL I have to repair regularly in my MythTV box. Hypersonic states it should not be used in a production system. I have never had to repair Postgres, MSSQL, or Oracle.
Sure it is. But product quality has little to do with a product's popularity.
There's no -1 for "I don't get it."
I couldn't help but think the poster/story was just looking to ignite the normal PostgreSQL vs MySQL comments.
The Ginger Dog
Apple is using it (in their Server OS, anyway), so that's one big Postgres-using prospective employer that's worth noting.
As for other Open Source databases, there's also Ingres and all the various NoSQL databases.
Check out my sci-fi/humor trilogy at PatriotsBooks.
The real money these days is in Big Data, not RDBMS. If you are just starting out now, start with what's still big in five years, not what was big five years ago.
With respect, as a Javascript programmer, I can tell you that I hit a lot of WTF moments with Javascript on a regular basis. I'm pretty sure that web forms count as "real world usage"
https://kb.askmonty.org/en/community-contributing-to-the-mariadb-project/
We (yes, I work for the project) are always looking for new contributors. There are lots of exciting things happening right now.
I'm happily surprised to see that all the early comments are in support of PGSQL, with brief anecdotes to back it up. I 100% agree - even if your future hopeful employer uses MySQL (which has actually matured a great deal), everything you learn in PGSQL will teach you the underlying theory of WHY good databases are good. You can apply that to any roughly SQL database. Further, PGSQL is even closer to zero-cost than MySQL, in spirit. So, if you have to go up against beancounters advocating for your software, it still looks really good.
Their logo is awesome.
I work in a company in the growing wave (from single digit employer number to two digit, from two digits paying customers to 1000+) and we have choose PgSQL this summer after internal six months review between Oracle, Pgsql, MySql, two NoSQL. We liked PgSQL robustness, developing model, (enough) wide usage and other things which could start a flame here (like performance) and also for the decent offer of commercial third party support.
As every day user and admin I personally dislike some clues (like the 'philosophical' view about HINTs) but after some months of production usage (and some years spent with MySQL in past jobs) I would go for PgSQL again. Yes, it lacks mm replica but even for the most optimistic growing rate we are ok with this.
The developers planned mm replica in the next couple of years: what about giving a look at it ?
I recommend setting yourself about fixing some of that long list of fundamental flaws in MySQL.
Traditionally, especially in 2012, this amounts to listing stuff like "doesn't have transactions" which was fixed back in Bush the Second's first term.
Shoveling thru obsolete FUD to find the truth is a harder job than you'd think, which also shows "good little worker bee" stick-to-it-ive-ness
"Science flies us to the moon. Religion flies us into buildings." - Victor Stenger
Everyone knows the right answer is Couchdb... right guys? ... right? ... Hello?
-- This space for lease, low setup fee, inquire within!
I have used it at several employers and know of some hiring right now.
Most of these jobs end up filled with people who are used to other DBs though, since candidates are so rare.
0.11016 was uploaded six weeks ago, its not dead.
Its VERY popular internally to auto-generate DB diagrams from midnight cron jobs etc. I'm sure there's other ways to do it. But this was easy, fast, and the diagrams look good enough.
It can do a lot more than generate diagrams.
What it needs is a new artist. The dude in a tutu as a logo...
"Science flies us to the moon. Religion flies us into buildings." - Victor Stenger
From what I can see in the market, the most interesting would be either VoltDB(H-Store is open) or NuoDB (closed). But after reading the initial paper on Hana (SAP), I could definitely see the purpose of a database with elastic capabilities NuoDB with the database engines from both H-Store (Relational in-memory), C-Store (Column disk) and an object/document storage. Right now everybody is installing three different types databases in their environment, because each are the most performant for their type of application. It's a great technology landscape however, very exciting to follow.
Their logo is awesome.
Nope. For awesome, it should wear boots and you gotta believe me, I tell you no lies.
Questions raise, answers kill. Raise questions to stay alive.
MySQL I have to repair regularly in my MythTV box.
What are you doing, that I'm not? I have a heavily used system for 8 years or something like that and have never had to repair it.
"Science flies us to the moon. Religion flies us into buildings." - Victor Stenger
Do you think anyone who works with Oracle respects it ? This doesn't stop them earning a pile of cash though.
Stop being a prima donna and pick the one with the best employment prospects.
Alex
Actually I wasn't. I figured the /. crowd might have some knowledge about the relative acceptance and prevalence of the two databases in European business settings, and where things are moving.
For example, if the consensus was that PostgreSQL was so rarely used that it was a dead-end, then I'd suck it up and work on MySQL despite my misgivings.
But as long as PostgreSQL is showing some signs of life in a business setting, I'll perhaps try to pitch in on that.
I also figured that maybe there was some other up-and-coming database out there that I should take a look at. The /. community is good at bringing alternatives like this to light.
As far as flames, I should have been clearer about what I meant by "design flaws". I realize that it's somewhat subjective. What I should have said is that MySQL's behavior strikes me as a lot more surprising in some cases than does PostgreSQL's, and I didn't think that was going to chance. (Probably in a similar vein, I like strongly typed programming languages and compile-time correctness checks. I think it's a mindset kind of thing.)
The post is basically a troll for a video. The video is based on an old list of MySQL 4.x gotchas, many of which were fixed in the 5.x series. Most of them involve things like the semantics of NULL in special cases, truncation of indexed strings with trailing spaces, and similar stuff that an application shouldn't be relying on. There's a comparable list of PostGreSQL gotchas from the same source.
MySQL has political problems, because Oracle owns it and would prefer users buy their commercial products. The future of the free version is uncertain. The problems in the video aren't the ones to worry about.
It was unintentional. My apologies. It would have been fairer to write that I find MySQL's behavior surprising, and am attracted to working on databases that have behavior that's more in line with what I'd expect.
The video I linked to is what convinced me that MySQL behaved in ways I don't like. I prefer system designs that kick and scream when something bad is entered. MySQL seems geared towards letting some operations succeed and just using defaults, in cases where I would prefer a loud error report.
Does it still silently drop data that does not match the expected input?
Can you easily delete a table with foreign keys yet?
I am sorry if I don't keep checking to see if they finally fixed based problems. Way easier to just use postgres.
Wouldn't the perfect resume be, "I'm the guy who fixed MySQL"?
No sig today...
Power outages perhaps?
MySQL is bad about dealing with unreliable hardware or power.
It has poor defaults in this regard that seem to indicate a culture that values performance over integrity. That kind of thing tends to turn off a lot of RDBMS users.
A Pirate and a Puritan look the same on a balance sheet.
While you're at it, do me a favor and add "ON DUPLICATE KEY UPDATE" to Postgres. (If necessary, also add it to an SQL spec.) Thanks!
It's better to vote for what you want and not get it than to vote for what you don't want and get it.
- E. Debs
If you need a database, use one that tries to be ACID compliant first, then efficient second. Not the other way around. When your database model starts getting more complicated as does your skill set, you'll be thankful later.
I swear to God...I swear to God! That is NOT how you treat your human!
I beg to differ - it's all a matter of philosophy. Do you want something that's a stubborn mule of a server that fights your every attempt to get something done, or would you prefer something that is more forgiving and let's you later discover your own personal logic flaws with a, "haha! whoops! There, fixed!" moment? The video for me only serves to further reinforce the reason I use MySQL. For one thing, I have never made any of the bone-headed programmer errors that the host illustrated, but I appreciate the way MySQL handles "strange" situations gracefully. My guess is that this has allowed Google to fill itself up with far more useful pages that have content on them than pages with an obscure error code because of yet another strict condition being broken with Postgres.
The problems with MySQL aren't bugs, they're decisions. Decisions that can't be reversed for the sake of backwards compatibility.
my sig's at the bottom of the page.
http://en.wikipedia.org/wiki/Michael_Stonebraker Have a look at what he's done with Postgres, Vertica, VoltDB, and the other systems he's working on. You may find that contributing to this project aligns you with some great, very intelligent people -- that's opportunity for learning, opportunity for contributing, and opportunity for good networking.
PLEASE PLEASE PLEASE -- build a good _embedded_ SQL DB. Specifically, please modify Posgresql so that it can run on a cell phone or other embedded device. Postgresql is the right choice but the developers are entirely focused on big iron. The most common solution is to use SQLite with a custom front end. SQLite is a great library but it is less than ideal in embedded system that need DB access from several running programs.
Maybe no one's bothered to tell you, but these days JavaScript is actual programming. Look at Node.js, for example.
With respect, as a Javascript programmer, I can tell you that I hit a lot of WTF moments with Javascript on a regular basis. I'm pretty sure that web forms count as "real world usage"
With respect, as an actual programmer, I can tell you that "Javascript programming" is a misnomer for "Javascript scripting". I'm pretty sure that web forms don't count as "real world programming".
With respect, as a real actual programmer who has programmed in more than 20 different languages in my 30+ year carrer, Javascript programming is real programming. Your inability to use it for more than just "scripting" is not a failing of the language.
It seems to me that if there are so many flaws with MySQL, perhaps that would be a good place to become an important contributor! Imagine walking into a job interview and explaining how you have fixed several major flaws in MySQL...that would look much better to a hiring manager than simply having updated some online help file or other trivial tidbit left over in PGSQL. Just my $0.02.
You write: "I've done a good bit of SQL development / tuning in the past. ... My problem is choosing which OSS DB to help with."
That's akin to saying, "I've done a good bit of SCCA track racing in the past. My problem is choosing which engine builder to intern with." Or even, "I'm an amazing, although out-of-practice chef. Should I work on enhancing Viking or Maytag gas ranges?"
Using a database (or any product) very effectively often has little or not translation into working on the guts of the product.
You're special forces then? That's great! I just love your olympics!
Not to take anything away from PostgreSQL and MySQL (and their forks), but these are mature systems with extensive communities and a very complex code base. If you want to learn the architecture of a new class of open source database systems, as well as to have the opportunity to make a significant contribution to a project, then you should consider joining a NoSQL project, such as Neo4j or MongoDB.
I'm curious if those are still actually existent in >=5.0. I know I started avoiding MySQL in the bad old days, but from what I understand it's made a lot of strides in the conformance department.
I haven't bothered to look at it again since then, since Postgresql meets all of my needs, but I am curious. It can't still be that bad, can it? I can see all the bad old behavior being hidden behind default for legacy users, that's reasonable, but silent data corruption (and whether you're truncating strings or inventing dates when you hit NULL, you're corrupting data) doesn't seem like something people would put up with these days.
<xml><I><am><so><damn>Web 2.0</damn></so></am></I></xml>
I will note that Postgres also inserts a default value of NULL (same as MySQL) when no value is given and the column has no default value.
So if the default value is NULL then Postgres uses NULL as the default? SHOCK AND FUCKING HORROR. If that's the only "complaint" you can make about Postgres then it's basically perfect.
Like others said, PostgreSQL.
I won't think of using any other DB.
-ié
[iconv --from-code=utf-7]
If employment prospects are all that matter, stay away from the major SQL databases. They're mostly feature complete, have large established developer communities that are hard to break into (sometimes requiring employment at the sponsoring company) and often have a lot of legacy baggage that limits what you can accomplish.
Meanwhile, in the NoSQL world, people are busy re-inventing the wheel. You can take decades-old techniques and apply them to new features of these databases. For example, Redis doesn't have true clustering support. There's a preliminary draft and some exploration, but it's still really nascent. If you've got the DB chops to implement it and do it well, there's a ton of places that would hire you.
The downside is, of course, that you end up working with NoSQL databases, but your employment prospects to actual work and knowledge ratio is a lot higher.
Try GT.M for something different. It's a key-value database, dating back to the 60's, but still hitting hard in healthcare, vets and financial. Great performance. The underlying technology is called MUMPS. Other MUMPS databases (such as Intersystems Caché, closed source) use MUMPS internally, then offer SQL, XML, object, etc layers.
Pretty much all the test cases from that video fail on MySQL if the sql-mode is set to traditional. MySQL will throw an error when data would be truncated, throws an error when you try to insert a NULL value in a NOT NULL column, refuses to alter a table if the existing data would be truncated, throws an error on an invalid date, on select only returns a warning for division by 0 but throws an error on an insert of division by 0, throws an error if you try to insert a string into a numeric column and so on.
I understand of course that the strict modes aren't enabled by default but they're easy enough to enable if you choose to. Via my.cnf, the command line when mysqld is started up or while connected to the mysql server itself (for just that session, or globally for all sessions).
I didn't run through all their examples, but mostly because I got bored and all their examples that I did try were throwing errors (except the select 1/0 one, which issued a warning) with the sql-mode set to traditional on MySQL (postgresql is also a sql-mode option but I didn't play with that one since I've never used it before).
<Disclaimer> I am the maintainer of this project and the comments here are of my own and no one else's </Disclaimer>
I run a small project called the Technology Explorer (http://db2mc.sf.net).
The Technology Explorer (TE) is a light weight web based framework for prototyping interactive database applications. Being a flexible and customization interface (think phpMyAdmin meets Drupal or Lego for database administrators), the TE allows for rapid development of UI driven processes and visualizations of database information in hours, whereas tradition processes might take weeks or month. The TE is primarily used as a teaching tool for IBM DB2 users as well as demonstrating and prototyping new interfaces. It is also used as a management consoled across a multitude of databases and database vendors (DB2, Derby, SolidDB, MYSQL and ORACLE (Were just starting to add PostgreSQL support)).
Were always looking for help to expand our library of content for different database vendors. It take a lot of understand of a given DBMS to be able to produce useful material. I would be thrilled if someone would be interested in writing tutorial for other vendors as we are currently mainly focused around IBM DB2.
Well there you have it, my plug. Were a small project, easily accessible, low barrier to entry and contributions and still lots room for growth. If your interested come on over and take a look.
--
Kari: There are a lot of things we really don't want you to try at home!
Tory: Yes, try it at your neighbor's house.
First I should probably burn some karma and say "what a load of garbage". The headline asks what OSS database to HELP with, but the article summary might as well read "Which free SQL-compatible database to learn to use". And on top of that it contains the answer already, along with questionable dirt-showing on MySQL which makes it read like a guerilla-ad for PostgreSQL.
But in any case, it makes a major, huge difference whether the question is "which database codebase to contribute improvements to" or "which free database to learn for best amployment chances". Sounds like it's the latter, and in that case a follow-up question is what kind of employment. The one correct answer is "whichever database your employee is using" - don't expect to be able to choose a job on the basis of what database engine they happen to be using in one of the departments at the time. Second best answer is go with both; and again it makes a huge difference whether it's for self-employed web-site design or financial analysis for stock brokerage firm.
And if you actually went with MySQL, next question is which database engine. Huh, you ask? Well you see, MySQL is not a single database engine, in actuality it's a front-end to pluggable database engines. The stock release fetures at lest MyISAM, InnoDB, Heap, BDB, NDB and Archive (and few variations). In general it's a choice between MyISAM or InnoDB which are whole different story. When most people say "MySQL has such and such problem" they're actually talking about MyISAM, but MySQL has defaulted to InnoDB engine for years.
But the third and best answer is "none of the above". In most cases everybody seeking employment in relevant job will be fluent in SQL and have at least some experience with both MySQL and PosgreSQL, and it'll be rare for the employer to be at all interested in your ability to actually "hack" the database source. NoSQL databases offer ample opportunity to differentiate both on the job-market, and on the business competitiveness arena by improving the source-code (and in most cases as long as the binaries stay in-house, so can the source which makes bosses happy, but consult your OSS license).
Where's the list? The link was to a "video" with a static image and a whiny voice gabbling on - tl;dw.
I love PostgreSQL in theory but hate it in practice. It's a pain in the ass to work with... not very productive. For a long time, I felt it was worth it to endure this for the superior design, feature set, and technical correctness.
But one day I realized that I need to get things done, switched the MySQL. The learning curve was small but the main kicker was that things just worked and easily reworked. There are risks, limitations, and problems. It's very imperfect but I get things done now... and never have or care to think about the purist philosophies with which I used to love to indulge in.
In the end, you have to give up perfection to go anywhere.. Otherwise, it's like having to get half-way there first, meaning you have to get half-way to half-way first, etc. recursively forever.. With MySQL I take a reasonable number of precautions for things that can go wrong, ensure there are good backups, and deal with the others as they come.
Now I think MySQL is superior for practical use by a long shot. And I think that's why its adopted so heavily.
The key ingredients to successful technologies are:
(1) You can do something obviously cool or useful with it.
(2) It's quick and easy to learn and use.
And that's it. This is why so many successful things are made by idiots. Look at HTML. It was made by Tim Burners Lee back when he knew very little. But 12 year olds were picking it up and making cool (at the time) web pages. Now he know so much more and has tons of backing from heavy weight organizations and money but cannot seem to even force the success of the Semantic Web. It's hard to learn and hard to work with even when you learn it. Furthermore, it's not obvious to most what cool or useful things you can do with it. Proponents keep saying it'll mature and will be easier when tools and libraries are available to make it easier... That misses the point. Even the tools mostly suck and are buggy because the basic tech. is a pain in the ass to work with. There are philosophical visionaries galore but no substantial progress beyond what grants and job requirements force people to do... and there won't be.
Matthew
I'm attracted to the robust correctness requirements of PostgreSQL, but there don't seem to be many prospective employers using it.
Look harder; they're plenty of them out there.
Their logo is awesome.
Nope. For awesome, it should wear boots and you gotta believe me, I tell you no lies.
Son, son, you've gone too far.
William of Ockham had no beard. The most likely explanation is that it was chewed off by squirrels every morning.
If only they were actually edge cases(look carefully they mentioned one was a common Ruby on Rails mistake). MySQL's habit of pretending everything is alright when it's not has burned more than one of my previous employers.
But they missed the real WTFs like mysqldump creating dumps that need to be hand edited before MySQL will restore them or my all time favorite: mysql user authentication simply does a "SELECT * from mysql.users" and if the fields get reordered by a new MySQL release then logins will simply fail. The best part is that the officially documented way to fix that is a mysqldump followed by a restore which... deletes the table and puts the fields in the wrong order again. The last major MySQL upgrade of my employer's systems involved me starting the new install from an empty DB, restoring everything except the mysql.users table and recreating the accounts using a script.
Please don't pretend it's not a crap database. Those of us who have to deal with it every day know better.
The problems with MySQL aren't bugs, they're decisions. Decisions that can't be reversed for the sake of backwards compatibility.
Agreed. Or at least, that was my impression. That's what led me to assume that my sensibilities would be seriously mismatched with whichever person(s) decide the design of MySQL.
According to my design and development sensibilities, a lot of things in that video would have been high-priority bugs. The fact that they're still present in whichever, I assume recent, version of MySQL was shown in that video, suggests to me that the leaders of that project have a different definition of "bug" than I do.
A thousand times: PostgreSQL
When you start dealing with anything other than very basic website apps, MySQL's many significant deficiencies start becoming obvious. And the problems in the development seem to be institutionalized, it was this way even before Oracle took over. MySQL should be consigned to the dustbin of history.
With SQL Server, you set your transaction isolation level that you care about and then you begin a transaction - SQL Server will guarentee consistency in that transaction even if you're just doing multiple selects. And, SQL Server will not let you do a 'select for update'.
For example:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT TOP 100 * FROM MyTable
-- Rows in MyTable are now locked
Locks are released when the transaction is commited.
So which entry-level web host do you recommend for running web applications that use PostgreSQL? Most that I've seen either offer only MySQL or charge extra for PostgreSQL.
FIFY. Doesn't look that great now, does it?
Rethinking email
It doesnt matter which, it matters what. If you think MySQL has design flaws, fix them. That looks good on your resume. If you like or are interested on PostgreSQL then go and find what it need and fix it. That kind of low level hack-master job will get you hired in any company that does databases, not just postgres or mysql.
NO SIG
I've done countless mysql dumps and restores and never had to hand edit the dump files. If you're doing web work you can also use phpmyadmin to work with mysql, which is kind of nice.
I prefer PostgreSQL and would recommend it over MySQL though.
That's not precisely where I'm coming from. It's more that I only have enough free time to contribute to one OSS project. But I also need to position myself for getting database work in Europe. I'm trying to find some OSS DB project that resides in a happy intersection of those two goals.
Cowards!
Table-ized A.I.
Drizzle (Mysql fork) has a lot of low-hanging fruit & its modular nature helps.
Otherwise if you're really contributing just for notoriety, consider Sqlite. It has plenty of work needed & it's critical for 100s of big companies: Google, Apple, Adobe (Photoshop), many more.
Science & open-source build trust from peer review. Learn systems you can trust.
Choose MongoDB, its web-scale. http://www.mongodb-is-web-scale.com/
You think an employer that's using mysql is going to turn up their nose at you because you've been using postgresql?
How well mysqldump works depends on the data. It seems to have a problem with escaping some strings correctly. Right now we use both MySQL and PostgreSQL with the dream of moving everything to PostgreSQL.
I also figured that maybe there was some other up-and-coming database out there that I should take a look at. The /. community is good at bringing alternatives like this to light.
Did you take a look at MariaDB? It's a continuation/rework of MySQL by the guy who created MySQL...
Bits of code, random ramblings: jakimfett.com
Actually, I started looking at it when people mentioned it in this conversation.
After skimming their website, I'm still a bit sketchy on why MariaDB exists. It sounds like a fork, except that they seem to periodically re-sync with Oracle's MySQL releases. Is the idea to just keep on developing open-source versions of whichever features Oracle adds to MySQL?
I wonder if it's on a MyISAM or InnoDB table type, or if he has cheesy drive that is lying about write barriers, or if he's using a kernel that doesn't treat barriers correctly. You do lose a lot of write performance making mysql act safely.
For that matter, front end management applications, as well as clients/adapter libraries can always use a *lot* of work... In some cases features in the system are missing altogether, or more advanced features like connection pooling aren't well baked into the clients.
Michael J. Ryan - tracker1.info
That said, we're migrating a lot of our data from MS-SQL to MongoDB, and after that, will likely move those areas reliant on an SQL RDBMS to PostgreSQL... though this is one small shop.
Michael J. Ryan - tracker1.info
The simplest way to say it is that MySQL is really more of a data store than a database. You can store stuff in it, and it'll get the data back reasonably efficiently, but in terms of actually operating as a proper compliant database for critical information it just isn't designed that way. It works great for storing the back end for your web server, but if you wanted to store complex data in it and needed it to be 100% accurate, transactional, and reliable, the product just doesn't fit the bill. For all that it's got a paid "enteprise" edition, it's really more in the space of something like SQLite or SQL CE than it is in the space of Oracle, and again it's not an issue of whether it can scale or whether it's buggy, it just simply isn't designed to be compliant to the required level. That's largely the reason it works so well as a LAMP back end and is so easy to administer, but it just isn't fit for purpose for much more.
If you're using phpMyAdmin, then you aren't doing the kind of in-depth database development where you run into the problems the GP is talking about.
Hell, if you're using phpMyAdmin, you're casual DB user probably just trying to support your small webapp or CMS installation.
And if you do use phpMyAdmin, you'd be much better off with a platform native database tool with MySQL support, such as SequelPro (OSX) or MySQL Workbench for (Windows,OSX)
I'm out of my mind right now, but feel free to leave a message.....
Comment removed based on user account deletion
Hi. I just wanted to introduce myself. I'm the other guy who both reads slashdot and gets that joke ;-)
Guns don't kill people; Physics kills people! - John Lithgow as Dick Solomon on Third Rock From The Sun
I might have thought that, until I noticed that an overwhelming majority of the posts here back up the claim that Postgres is much better.
Guns don't kill people; Physics kills people! - John Lithgow as Dick Solomon on Third Rock From The Sun
I've been looking at columular databases recently and MonetDB seems to be very robust and has two decades of development against it. I would suggest you look at Postgresql if you want a row oriented db to help out on or MonetDB if you are looking for columular. If you are looking at more multidimensional db's then check out some of the OLAP servers on wikipedia at http://en.wikipedia.org/wiki/Comparison_of_OLAP_Servers Regards, John
My current employer and the previous two employers all used PostgreSQL in some capacity. None used MySQL.
What do you mean by "most popular."
I'm tired of hearing that "everyone uses..." No, they don't. MySQL is pretty popular with the open-source web-crowd but this is the same crowd that respects the engineering behind PHP. I've encountered plenty of people in that arena who would rather roll their own data-checks and treat the database as barely more than a key-value store than use the capabilities of the database and have to deal with handling exceptions. Bring up transactions, ACID compliance, data-integrity and the like at a PHP users group and you get blank-stares. The get-rich-quick-with-a-cute-kitten-website crowd cares not for such things (as an overgeneralization - there are plenty of high-traffic sites such as Instagram, hi5, Etsy and MyYearbook that run on PostgreSQL).
So where do you find PostgreSQL? Salesforce, National Weather Service, Nippon Telephone and Telegraph, Federal Aviation Administration, Sony Online Entertainment, TD Ameritrade, State of Wisconsin Courts, Afilias, BASF, Flightaware, Skype (a contributor of many PG utilities), Fujitsu, Launchpad (Ubuntu)...
And PostGIS is *the* go-to open-source geospatial database.
I've found the PostgreSQL community to be wonderful with opportunities to contribute at all levels. Answer questions on the mailing-lists, contribute to documentation, help at users-groups, give a talk at a conference. One always welcome contribution is doing testing and submitting results/patches during commitfests - and this gets you more involved with the code.
As to employment, it sounds like you prefer PostgreSQL. As such, PostgreSQL is by definition the most popular database among places you are interested in working. Do what you love.
~~~~~~~
"You are not remembered for doing what is expected of you." - Atul Chitnis
Having a truly open source (and community developed) fork is one of the main reasons, however we actually have been developing features independent of what oracle does. Pretty much the entire MySQL optimizer team left Oracle and moved to the MariaDB project. They took the time to fix subqueries so they were actually usable and improve many other features. Take a look at the comparison:
https://kb.askmonty.org/en/mariadb-vs-mysql-features/
Most of what's shitty about it is the MyISAM storage engine, which does approximately dick-all for enforcing integrity. It doesn't even have foreign key constraints. IIRC it can't do transactions either. The trade off is that it's slightly faster for some operations *eyeroll*
If MyISAM is good enough for your application then you may as well—no exaggeration—just use MongoDB or something.
InnoDB is much better. It's got some of the same not-confidence-inspiring quirks shown in the video but at least it supports transactions and foreign key constraints.
Biggest remaining differences off the top of my head are that Postgres supports a shitload more data types and data operations (many through plugins) like stuff related to geographic data and key-value stores (hey, you got NoSQL in my SQL!), and that Postgres has real separate databases, not just separate schema like MySQL, the difference there being strict separation of the data, so you can't, say, do a SELECT across two databases or even tell that there are other databases if you've only got a user account on one of them.
Lots of other under-the-hood stuff, I'm sure, but those are the main ones I can think of from a user's perspective.
Postgres is way, way more powerful, MySQL is (slightly) more widely supported and (IMO) the free tools, both command line and GUI, for working with it are easier to learn and generally friendlier.
MySQL's a completely miserable excuse for a relational database if you use MyISAM; it's only a mostly miserable excuse for a relational database with InnoDB.
They could always enabled strict_all_tables and things like ignoring NOT NULL and so on will not happen. I will note that Postgres also inserts a default value of NULL (same as MySQL) when no value is given and the column has no default value.
Of course it will, what did you expect? The point is that if you specified NOT NULL and then don't give it a value then it will refuse to let you insert that row BECAUSE IT IS INVALID.
How is that so hard to understand?
At least 3 of them are trying...
The silly thing is that having a default value of NULL and having no default value (effectively defaulting the default value to NULL) commonly aren't the same thing.
It's just the sort of nasty little corner case that breeds bugs. Like when many years ago Sybase's bulk loader entered random data when inserting NULL into a column with a default of NULL, and ruined our week. With no default, it would've worked fine.
I would believe that if it weren't for the fact that there are at least 3 forks from former MySQL leaders trying to fix all the junk in it that is screwed up. For example, read:
http://krow.livejournal.com/700783.html
Me too.. its many many years since I had any such problems with mysql and back then it was only when using the myisam storage engine and shitty hardware. Using innodb "just works" and it has never failed at recovering itself in the event of a hardware/server crash. I use it with truckloads of data and it does the job just fine as long as it is configured reasonably well.
Taking the OP's post at face value, there are two obvious messages that I can take away from the discussion:
1. That OP wants to help on an OSS database project, and
2. The OP wants their help on the OSS database project to be useful with future job prospects.
PostresSQL is probably an order of magnitude better than MySQL as a database product, which to me would suggest that it is less in need of help than MySQL is. On that basis, point 1 would suggest MySQL.
As the OP himself points out, the popularity of MySQL is vastly larger than PostgresSQL, so point 2 would also suggest MySQL.
Looking good for MySQL...
My philosophy on OSS projects that are not dead (or terminally dying with no hope of rescue) is to find the one I can contribute most to, where my input with both add meaningful value to the project and also avoid fragmenting the community too much. Coding to fix bugs in a crappy codebase with inherent design and structural flaws that are not addressed because the community throws a collective fit if you dare to criticise their beautiful work is like beating your head against a barbed-wire-covered brick wall, and I would definitely not recommend that. My suggestion might count against MySQL, but it seems to fit a lot of the OSS projects I have looked at so the problem there might be with my lack of diplomatic skills too :-)
Postgresql assuming the default is NULL when you don't explicitly set a default value seems reasonable. I don't want to have to type extra crap.
If you don't want nulls AND want a value to be explicitly specified then the proper way (which is supported in Postgresql ) is to create the column with "NOT NULL DEFAULT NULL".
I was looking for an easy way to automate character conversion from Latin-1 to UTF-8 for the forum software I use. I found out the hard way that the built-in MySQL recoder is completely broken, and will barf in different ways depending on which version number of MySQL you are using. No errors or warnings during the conversion for any version. You'll just find out later that all the field limits are wrong. You can only find out if it worked or not by inserting new rows and finding out if you get errors about data being too large to fit in the field, and whether it fails or not has nothing to do with the actual length of the data, but with whether you send 7-bit or 8-bit characters.
I gave up trying to get MySQL to do it, and wrote my own conversion tool.
And that's just for baby stuff for a web forum on a personal web site. I can only imagine what MySQL is like in an enterprise environment.
I see most people recommend that you go with just one or the other database; I don't agree, though - I think it might be better to broaden your scope.
My background: I'm the system manager for a smallish R&D department, and I have the enviable task of getting databases to run across a number of architectures - on LUW this is DB2, Oracle, Informix, Sybase/MSSQL and MySQL, but also things like Adabas, and on the mainframe, IMS and flat-file.
In my experience there isn't so much call for the very deep knowledge of each architecture, but knowing how to port applications across is very valuable. Especially if you know your way around MVS, there's a lot of money in that, believe you me.
Are you looking to contribute in the actual development of the database software or just looking at what database to use? I see lots of comments seem to imply the latter and focus on what is wrong with database x from a user viewpoint (a DBA is a user, an application developer is a user) - if you want to make your hands dirty you should really look into the community and how to get involved. MySQL has not been that great on Oracle days on external community so you might be better off looking at some of the numerous forks and how they treat their developers.
If you are looking for a way to get employed through knowing the insides of a database engine in the Open Source world I would suggest SQLite (it is really used everywhere nowadays) or as a little bit more niche product, Firebird - Firebird is a really nice database which can be used on embedded projects easily + has robust set of features and has a permissive license (I am a bit biased because the company I work for uses it as a core database engine...).
None, none are good. Take a look at Postgresql code or read their dev-mailing list, they're a bunch of amateurs trying to sort out the basics. I imagine MySQL is worse.
I recommend learning DB2, then you should get a set of suits, cut your hair and forget about OSS teenage projects and start focusing on a professional career. Good luck.
Thanks, I'll have a look. What about those issues raised in the video I linked to - the issues which I'd call correctness issues.
Has anything / will anything be done about that behaviors, or are they now permanent fixtures in the software?
It's kind of a combination of all those things. My situation is:
I guess another alternative is that I work on whichever database project is most interesting without regard to geographically limited job-finding requirements, and hope I can telecommute. But that seems like a risky assumption.
Comment removed based on user account deletion
I also prefer PostgreSQL, but clearly you have a lot more oportunities to fix things on MySQL :)
Why does making the decision about data storage backends is something that's touted as being good in any shape or form? Ultimately the query optimizer has all the information that's needed to make such a decision. If I want an SQL database, that's what I want, not any particular backend. I shouldn't need to worry about it.
A successful API design takes a mixture of software design and pedagogy.
As someone else mentioned, MariaDB and Percona are definite directions to go. You can replace MySQL directly with them--same protocol.
Support my political activism on Patreon.
I have my mythtv box all set up with mythwelcome; it wakes up when it needs to record something, and powers off when it's done, provided I'm not actually watching something at the time.
Maybe that's the difference?
I am using an admittedly older MythDora (umm, 10?) system with MythWelcome set up to use the alarm to wake it up when it needs to record something, and power it off when idle.
It usually decides to act up when I am away for a week on business or something, and then everyone's mad at me when I get back!
So I finally added a "mysqlcheck --autorepair" to the bootup sequence.
As a complete aside, I just ran out of disk space while still having 100GB free... ran out of inodes. Some cron job was periodically sending emails, and I had over 500,000 unsent emails in /var/spool. Took a good while to delete all those emails.
It's a matter of using the right tool for the job.. to use a car analogy - you could drive a Fiat Panda in a Formula 1 race and chances are you would still get to the finish line eventually but it's not really the best car for that particular job, however it is fine for getting around town and you'd look a bit silly going to the supermarket with an F1 car! (no room the shopping)
There are many storage engines available for mysql these days and each have their good and bad points so choose one based on your expected usage.
I'll second the various recommendations for Firebird.
About 10 years ago our senior engineer asked me to look into open source database systems as a back end for our product. The idea was to target customers who didn't want or couldn't afford Oracle, Sybase, etc. MySQL was out since it can't be use commercially without fee. PostgreSQL (at that time) lacked a robust transaction management system. Firebird was in its infancy, still known as Borland's Interbase, but it was fully open source and had the transaction management chops I needed.
In just a few weeks I had ported over 13K lines of Oracle embedded SQL to Firebird|Interbase. It worked very well, and was easy to install. It's speed, simplicity, and reliability quickly made it our go-to database for inhouse use. When Macintosh went Intel and db vendors stopped supporting Mac, we began using Firebird commercially. It's a champ.
"Love is a familiar; Love is a devil: there is no evil angel but Love." --William Shakespeare ('Love's Labors Lost')
PostGIS is very popular in the neogeo community (GIS extensions on top of PostgreSQL), primarily because it's supported by arguably the most important GIS server stacks (OSGeo's and Esri's). If geography plays any role in your career planning, or if you just want to keep your options open in that area, definitely choose PostgreSQL/PostGIS.
"Ahh! I see you're in that indeterminate Schrodinger state where - oh, uh
Oracle and MS SQL Server are the 2 main players for corporate and enterprise needs where I reside. Hosting solutions on the other hand use MySQL, Postgres and MS SQL Server
If you target working on MS, go for MS SQL Server. It's simply the best database available that runs on MS. Oracle's database and it's benefits don't outweight it's lack of management tools and poor MS installers.
If you target working on Linux, POSTGres is your best friend. I like their nice GUI tools (Much like MS SQL) to manage the DB. If you program like I do, you just want to get shit done and don't care about figuring out what that command was for creating an Index or table (Right click, Create).
If you are targetting becoming a software developper, you only need to know how to structure your data. Managing the database if a full time job in most large corporation/enterprises.
So, what other major SQL database products offer this choice? Why, or why not? There's your answer I think.
A successful API design takes a mixture of software design and pedagogy.
+1
You don't see lots of people saying they use PostgreSQL online, because, no one has to bitch about it. It works, it works great, and its documentation is astounding.
Everyone uses it, you just don't realize it, again, because no one bitches about it.
I'll never touch MySQL after having used it for a product, what a POS
Dick Grune's law: Producing correct software considered harmful to one's career.
-- hendrik
Knid of both. In the early 2000's I was a pretty intensively developing and tuning databases in MS SQL Server. Then in my master's work I worked on the design and guts of a non-relational database, which eventually became StreamBase.
Although I haven't yet worked on the guts of a relational database, I think I probably have enough database knowledge and programming skills to become a useful contributor to an OSS database project within a reasonable time frame.
Many of the items on the PostgreSQL "gotcha" list are annotated to say that they only affect older versions; in one case they mention it affects versions 7.4 and earlier. Versions 7.4, 8.0, 8.1, and 8.2 have all hit end-of-life after five or more years of support. Version 8.3 hits end of support in about three months. That would be a very short list if issues from ancient out-of-support versions were culled from it.
PostgreSQL: Versioning policy
Not being a DB admin, I get the notion that MySQL has the market share, while Postgre the future. So I'd think it would be easier to get a job within MySQL for statistical reasons.
I wouldn't plan my future on statistics though. If you do meaningful things, life is more meaningful. Same goes for funny.
Defining Statistics and Social Research
Sounds like you needed the --complete-insert option.
You get mysqldump output like this:
LOCK TABLES `user` WRITE; ... );
INSERT INTO `user` (`Host`, `User`, `Password`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Reload_priv`, `Shutdown_priv`, `Process_priv`, `File_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`, `Show_db_priv`, `Super_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Execute_priv`, `Repl_slave_priv`, `Repl_client_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`, `Alter_routine_priv`, `Create_user_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`, `max_questions`, `max_updates`, `max_connections`, `max_user_connections`) VALUES (
UNLOCK TABLES;
Those of us who've used MySQL for years with InnoDB and transactions know better. Yes, MySQL has faults if you don't know what you're doing. Yes, some people see that as a bad thing. Then again, a lot of the people who think MySQL is horrible can't recognize the need for non-RDBMS database systems either.
- Michael T. Babcock (Yes, I blog)
I'd love to see that list of failures running on InnoDB with a modern version of MySQL. Not to be argumentative, but it can't be that long of a list.
cf http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL#MySQL:InnoDB
- Michael T. Babcock (Yes, I blog)
Thanks that would have been helpful at the time and the backup scripts now use that option.
The point is not that MySQL cannot function as as an enterprise DB. The point is that it requires so much effort to do so. I should not have to hand edit a MySQL dump to get logins working again and even with this option I still would have needed to remove the portions that drop and recreate the table.