We are currently using Oracle as our database in both DEVELOPMENT and PRODUCTION environments. However, since Oracle is so STUPID expensive, the higher-ups are considering switching our DEV to MySQL.
I have a bad feeling about this, so I’m putting this out there: How much of a difference is there in query between Oracle and MySQL? Am I going to have to write conditionals to detect the environment and use different queries for each? Or are they similar enough that I don’t have to worry about it?
I’ve never been part of nor heard of a database migration that has went smoothly. Unless your existing application is programmed in such a way to facilitate the migration it is likely to be a large effort depending on the size of the projects or number of them that need to be migrated. Not to mention installing the oracle dependencies for PHP can be a very painful experience both in your production environment and locally.My recommendation would be to not allow anyone to underestimate or undermine the effort it is going to take to complete a migration like this properly. Some people will probably try to tell you it will be really easy but unless you are using an ORM of some kind like Doctrine or a platform that facilities switching between databases like some CMSs it probably won’t be that simple.A few backs I was part of a project which they went from MySQL to SQLServer and that took about a year to complete but included factoring out all the inline SQL in php into rest service calls and building a .NET rest layer around the database that relied heavily on SPs.
Thank you for your insight, @motor_man. We are running a ColdFusion shop, here, and I have not been able to successfully set up any ORM (most likely due to highly restrictive security protocols.)
I’m going to try to push against, this, then. I know Oracle is way over-charging for the licenses, but with as many projects as we have, it would take years to convert everything.
your Coldfusion applications won’t change hardly at all
you will have to tweak some sql inside your CFQUERY tags – yes, Oracle vs MySQL sql syntax is different, but only in a few minor areas like date functions, string functions, and so on
migration of the database tables shouldn’t be that hard, unless you’ve got some weird ass esoteric Oracle datatypes
Unfortunately, since I’m not the DBA I’m not sure if we have any weird ass esoteric Oracle datatypes. But I’m sure this would be more trouble than it’s worth. I’m going to see if I can convince my bosses that this is a bad move.
I always keep dev as close to prod as possible. You don’t want to code something that works perfectly fine in dev and then explodes in production. Mostly because you can’t debug it locally so you’d have to do trail and error to get it working again in production, which is risky at best.
Yeah, I think having MySQL on dev and Oracle on production is a crazy idea unless your application is super simple in its use of the database. MySQL may lack some of the features of Oracle or may implement them differently and then you have a problem.
Sorry to ask - does Oracle charge you additional $$$ for using it on a dev server? I’m not knowledgeable about their licences.
There isn’t anything preventing someone from running a light weight version of oracle locally. I worked on a ruby project a year back and when I got there they were running Postgres locally and Oracle in prod. One of of the things I did was figure out how to run a light weight oracle locally instead of running two separate db engines which had a host of issues.
The difference between a Lamborghini and a Hyundai…
The question, however, is do you want/need either?
If you are like most people, a Toyota Camry or a Honda Accord would do. (Or maybe a BMW if you have the money.)
If saving money is the goal, I would look into PostgreSQL because I think it has a much more enterprise-based solution, is open-source, and can probably compete head-to-head with Oracle in most areas.
MySQL grew up a lot since the early 2000s, but there is a reason large corporations use Oracle!
But since you likely aren’t WalMart or Citibank or Amazon, does your company really need Oracle (i.e. a Lamborghini)?
Also, I would like to add that I would rather run a medium-to-large business on a ell architected MySQL or PostgreSQL solution than a pile of garbage using Oracle. Database like Oracle will help you when you have a robust codebase and database design and you push the envelope on what Oaracle can do that something like MySQL can’t. But that describes 2-3% of the use-cases out their in the business world.
I’d like to remind you all of this sentence in the OP:
That is, the OP is currently already using Oracle in production and in development, the question is if it would be a good idea to switch to MySQL for development only (i.e., production will keep running on Oracle).
The fact that the question is being asked is enough reason to give pause.
I get the incentive, to save on the cost of the database. What seems to being not considered is the cost of developer time.
IMHO, just having different development and production instances can introduce discrepancies that can cause problems. Don’t get me wrong, I believe that having development and production separate is a very good thing. Much better to foobar development than production and needing to put out fires after the fact. Only that care needs to be taken that the two are as similar to each other as possible to minimize the occurrence of unexpected problems.
To intentionally have different databases for development and production seems a folly to me. I imagine that many databases have much in common with other databases. I do know that the few I have worked with do have differences, be it functions, datatypes, value limits etc. The answer to how much of a problem the differences are is always the same - It depends.
Developers will need to have extensive knowledge of both databases and the data that is being stored in them. Possible? Sure. A wise decision? I think not.
But I’m sure this would be more trouble than it’s worth. I’m going to see if I can convince my bosses that this is a bad move.