Would requiring MySQL 4.1+ affect you?

[Hildy]

We've been considering upping our minimum requirements to include MySQL 4.1+, and dropping the seamless support for PostgreSQL. While it's nice to have that support through our database abstraction, it means we can't take advantage of some of the nicer MySQL-specific features like FULLTEXT indexing.

If we were to change the DB requirements to MySQL 4.1+, how would this affect you?

[jstanden]

We started Cerb4 with the idea of supporting MySQL, PostgreSQL, MSSQL, Oracle, etc. As development has progressed, we've hit several issues where our database functionality needs won't abstract or overlap effectively.

The major benefits of Cerb4 standardizing on MySQL 4.1+:
Fulltext searching: Right now we're implementing database-driven functionality where our supported platforms (MySQL and PostgreSQL, at the moment) overlap. On text searching, this currently centers on basic SQL-92 "LIKE" syntax searches. The downsides here are pretty obvious; there's no boolean support, wildcard searching is grossly inefficient when looking for multiple words and the order of keywords must be precise. The ideal situation is to have the full feature set of MySQL's FULLTEXT index type, which, while not perfect, opens up a more Google-like syntax that's much more useful than what we're currently doing.
Internationalization: It's much faster and cleaner to be able to depend on MySQL's encodings and multibyte behavior than to abstract how each platform approaches i18n. MySQL has its own tricks too, like "SET NAMES utf-8;" being issued per connection to ensure the client and server are handling multibyte encoding the same way.
Sub-queries/Foreign Keys/Cascades/Joins: With day-to-day functionality we haven't had much of a problem avoiding sub-queries or foreign keys for MySQL 3.23's benefit. However, to support this rather old version of MySQL we lose the ability to be efficient in database patches and maintenance tasks. While not exclusive benefits of MySQL 4.1+, cascaded deletes on foreign keys, sub-queries and joins in UPDATE/DELETE would make patching and maintenance much more efficient. We end up with a lot of N+1 queries presently, where if 'N' is based on a table like 'ticket' it may end up around 1 million repeats of a query. This ends up clashing with the default PHP execution time of 30 seconds (which is fine for rendering a typical page, and an eternal pain for upgrading/maintenance tasks in PHP when the execution time can't be overloaded at script level).
Transactions: Another needless concession to MySQL 3.23, without transactions we have a much harder time making sure partial data isn't written to the database -- especially while parsing tickets. Transactions ensure everything is successful or none of the changes are applied. We've always needed this, and MySQL 3.23 support has always been in the way.All of these issues contribute to the speed of development.

That's the case to be made on our end. Making this change would unstick several big feature requests, that are far more important to us than the marketing bulletin of 'database independence'. We'd really like to hear from you if this change would be a problem for you.

Thanks!

[jstanden]

So far things look good for the switch (especially through our daily conversations), but 13 replies on the poll here is such a tiny sampling of the community.

Someone should probably do a quick search for PostgreSQL, MSSQL and Oracle and direct those community members here.

[doodlepop]

Given the benefits of the MySQL 4.1+ requirement outlined in this thread, I really don't think it's reasonable NOT to do this.

I would suggest that anyone with PostgreSQL probably has a MySQL database engine around too, as there are so many systems that use MySQL that requirement has probably already been forced by something else.

For the MSSQL and Oracle users, yes, they would be a sticking point - although I'm willing to bet that the number affected is very small compared to the wider community disadvantage (and potentially lost sales) by not going ahead with these features?

[jstanden]

For the MSSQL and Oracle users, yes, they would be a sticking point - although I'm willing to bet that the number affected is very small compared to the wider community disadvantage (and potentially lost sales) by not going ahead with these features?

I completely agree. :)

[sbalourdos]

I understand that supporting multiple platforms makes for longer development times and more headaches however I'm sure you must have some sort of demand already for a MSSQL installation. I know I have been emailing sales about a MSSQL update and I get nothing but "it's coming" for an answer. Have you checked you with your sales department and asked them what they are hearing?

We only run MSSQL here at our company and I know other firms do the same whether it's MSSQL or Oracle. Bringing in another DB Server means that you have just one more thing to deal with such as backups, updates, and troubleshooting. Another thing to keep in mind is that some server admins may know one kind of DB server but may not be comfortable with running another easpecially in environments where a DBA is not present.

[jstanden]

I understand that supporting multiple platforms makes for longer development times and more headaches [...] Have you checked you with your sales department and asked them what they are hearing?

Hey sbalourdos!

Yeah, our entire company (sales, support, billing, development) has an ongoing group chat log going about things we're hearing from the community.

There's undoubtedly some demand for MSSQL and PostgreSQL, though it's a very small minority. If we could cleanly abstract all our database-driven functionality, I would like nothing more than to support everyone's native server environment.

For most of our development so far, we've been able to support most platforms inherently through just using "dumbed down" SQL-92 queries. However, the project-wide casualties of abstracting non-trivial database functionality (internationalization, boolean full text searches, transactions, subqueries, foreign key cascading deletes) far outweigh the minor casualties from companies who are locked into a single database vendor.

Multiple database support has been unique to 4.0, and we'd like to minimize the eventual hassle by making a decision early on.

I would really, really like to support MSSQL if it was practical. The wider audience is attractive until we realize it means we're giving a watered down product to more people. I'd rather give a stronger product to fewer people -- regardless of what our balance sheet says.

I would think PHP5 is the far bigger concession from these environments (of Java/Oracle or .NET/MSSQL) than MySQL would be. Right?

[stevem]

Jeff,
I've been absent for a bit and have not upgraded my personal system to 4 quite yet.

The other possible implementation I have would be for a Fortune 50 health care company and a ticket support queue for a group of 10 to 15 internal support staff. Our house is Oracle, and I just started thinking about a new system again for us today since my boss mentioned the budget cycle and we need to do something to replace our broken, customized piece of junk.

Not only will mySQL be a hard sell, but so would PHP believe it or not.

That does not mean that I'm not going to try...

[rfarnell]

Jeff,
I've been absent for a bit and have not upgraded my personal system to 4 quite yet.

The other possible implementation I have would be for a Fortune 50 health care company and a ticket support queue for a group of 10 to 15 internal support staff. Our house is Oracle, and I just started thinking about a new system again for us today since my boss mentioned the budget cycle and we need to do something to replace our broken, customized piece of junk.

Not only will mySQL be a hard sell, but so would PHP believe it or not.

That does not mean that I'm not going to try...

You have to go no further than the Press Releases on the MySQL site http://www.mysql.com/news-and-events/ to see that big external facing companies are using MySQL enterprise as their database of choice.

As for PHP then this is probably the best article I've seen http://www.killerphp.com/articles/why-php-business-perspective/

This is pretty good too back from May of last year:
http://www.nickhalstead.com/2007/05/11/10-reasons-why-php-is-still-very-much-alive/

Regards,

Rob

[doodlepop]

The major benefits of Cerb4 standardizing on MySQL 4.1+:
Transactions: Another needless concession to MySQL 3.23, without transactions we have a much harder time making sure partial data isn't written to the database -- especially while parsing tickets. Transactions ensure everything is successful or none of the changes are applied. We've always needed this, and MySQL 3.23 support has always been in the way.
Whatever the outcome of this thread, it's definitely time to stop writing for MySQL 3.23... I don't really understand the logic behind this - yet forcing PHP 5; they're both potential security and functionality issues, but only half of the equatation has been completed?

Regarding the rest of it, I don't have a broad enough RDBMS knowledge to know the full capabilities of each one. However, my understanding is that most of the options can do most of the things outlined above - so it should be possible to have the functionality and capture the MSSQL (for example) markets; it's more of an internal WGM question to consider whether you have the development capacity to handle the increased overhead of this both in development and support for that matter.

My personal preference would be for you to concentrate the product on the specific needs I have... but that wouldn't be very reasonable.

One final comment I'd like to make is that companies using whatever technologies are likely to be more willing to upgrade their systems to a later version of the same product(s) than change/add something different. So anyone using MSSQL 2000 is more likely to upgrade to 2005 than MySQL (in my experience anyway) as their existing infrastructure/expertise is more likely to translate to it. Of course, the speed at which that upgrade happens in the organisation in question may be an entirely different issue :P

My point is that if you need to use functionality from a later version (e.g. like the MySQL 3.23/4.1+ issue) it seems an easier choice to go ahead and do that, compared to the decision to lock other RDBMS users out of the product.

[dsugita]

Thanks for the comments doodlepop. I'm pretty sure the devs will be moving forward with the mysql 4.1+ items in the near future. Though if anyone else reading this has further comments/issues, please post them.