1

I develop an application that uses MySQL and explicitly uses InnoDB table engine. In a few cases we have clients using this application with MariaDB or Percona.

I understand that MariaDB and Percona is a "drop in replacement", but my understanding is that this only means you do not have to migrate your data to switch from MySQL to MariaDB or Percona. It says nothing about the SQL syntax being identical for applications that interface with these databases.

I am aware that the DDL for virtual columns are not identical between MySQL and MariaDB, but I'm not clear if this is a difference between InnoDB and Xtradb, or MySQL and MariaDB.

Or to put this question another way, is my applications guaranteed to run on MySQL, MariaDB and Percona so long as they are at equivalent versions and all tables are InnoDB?

Improve this question

1 Answer 1

Reset to default
2

The InnoDB/XtraDB is a table engine, it has no say in parsing SQL. Thats another layer, which should be mostly engine independent.

MariaDB 5.5 is supposed to be 99.99% SQL compatible with MySQL 5.5 (there were some minor hiccups) and MariaDB 10.x is backward compatible with 5.5.

With MariaDB 10 and MySQL 5.6 (and now 5.7) the paths diverged somehow - some features were implemented in one but not other, some were implemented in both but with some differences (GTID, virtual/computed columns, differences in optimizer, ...).

So the answer is: There should be no difference in "standart" queries between using InnoDB/XtraDB or even MyISAM as both are "under" the SQL level of MySQL/MariaDB structure. You only have to be aware of differences in syntax for new features in 5.6+/10.0+ and those are not because of the engine differences (virtual columns for example are not handled by InnoDB afaik, but by the SQL layer - permanent computed column is just another normal column for InnoDB with the higher layer supplementing the data and the non-stored variant does not "touch" the engine at all and is always computed after the engine returns the columns needed).

Improve this answer
5
  • MySQL 5.7 introduced virtual columns and the DDL is not the same as MariaDB (percona.com/blog/2016/03/04/…). So if I understand what you are saying, I can expect SQL to remain compatiable so long as my application only makes use of features that were present in <=5.6. If I start using new SQL features introduced in 5.7, then I should anticipate that the SQL for those features may not be compatible with MariaDB/Percona?
    – Courtney Miles
    Commented Apr 28, 2016 at 21:35
  • 1
    @user2045006 Often MariaDB tries to make a MySQL-compatible variant of the syntax for new features but it is probably not the highest priority so may take time. .. Virtual columns have a positive side - only DDL is different but DML stays the same, and normal app is not usually supposed to use DDL. Your "install script" or similar tool tasked with running DDL might just keep two versions of the statements if needed.
    – jkavalik
    Commented Apr 29, 2016 at 5:13
  • 1
    Just some additions: Percona says they always guarantee to be a drop in replacement for the same major version of MySQL. So Percona Server 5.6.X is fully compatible with MySQL 5.6.X and the same for 5.7. While MariaDB 10+ (deliberately) diverges (hence the changed versioning) and once you switched there's no way back except mysqldump and import.
    – Károly Nagy
    Commented Apr 29, 2016 at 7:20
  • @KárolyNagy thanks, I somehow missed Percona in the question.. Yes, Percona versions are supposed to be just enhancements of the respective MySQL versions.
    – jkavalik
    Commented May 1, 2016 at 6:10
  • @KárolyNagy I disagree about the "deliberate diverges". As far as the MariaDB docs go, they state that their version is a drop-in replacement for MySQL. And 10+ versions are still drop-in replacements of MySQL 5.5 (as far as I can tell). But not yet of 5.6 and 5.7 (which were released after MariaDB 10). So one could claim that MySQL deliberately diverged from MariaDB new features ;)
    – ypercubeᵀᴹ
    Commented May 15, 2016 at 10:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.