That's real shame, but is there anything we can do about it now? We can't just switch them back, it will break compatibility with previous MariaDB versions...
Assigning to serg to decide.

Doesn't the FRM file contain version numbers ?
In that case you can "just switch them back" and add code that handles version that got it wrong explicitly...

It doesn't seem so, not for views at least. I diff-ed MySQL's 5.5 and MariaDB's 5.5 frm files for identical views and the only thing that differed was the algorithm code (and the timestamp).
There is some 'create-version' value, but it's just '1' for both versions.

I think at the very least you need to modify documentation and in every place that says that "MariaDB is drop-in replacement for MySQL" add a link to a page with caveats explaining this view format difference.

In MariaDB 10 we have views create-version=2.

So, if you don't care about views from MariaDB 5.5, you can swap algorithm values for views with create-version=1, it'll work for all MySQL versions and for MariaDB 10.x views, but it'll break MariaDB 5.x views loaded in MariaDB 10.x. Might be good enough for you. Want a patch?

If the question is directed to me then I don't think I want this patch. It will be only for us and won't be included in MariaDB proper, right? In such case I have a much simpler patch – swap values for VIEW_ALGORITHM_* constants. Putting in a hack depending on create-version (which actually is not related to the problem in any way) sounds dangerous.

I've sent patch for review )it is not fixing, but add parameters to the frm)

ok to push.
please make sure this unfortunate discrepancy is documented in KB

Why not just add something to mysql_upgrade for this?

Shit happens, so some variables got typoed. Have it be fixed by mysql_upgrade like the innodb tables in the mysql schema are. No one in their right mind truly expects going from virgin MySQL to Percona or MariaDB to be straight forward and any decent "MySQL" DBA expects issues switching between the three big MySQL players: MySQL, Percona and MariaDB.

If the three can't get their houses in order in regards to mysql.* table definitions (I'm looking at you innodb_*_stats) or constants for view algorithm types, take care of it in the mysql_upgrade script and give the day to day DBAs a fighting chance when it comes to switching between the installs and don't make us search bug databases or spend time looking at view definitions for differences when we do something as simple as installing a "drop in replacement" MySQL daemon.

I'd be super de dupery happy if DBAs weren't made to look stupid after spending weeks recommending MariaDB to their higher ups only to have a weekend's work scuppered by a typo in the view algorithm type definitions late on a Sunday forcing me to call a rollback on a switch from Percona because queries run like molasses now that anything involving my views can't use an index.

Like it or not, folks, MariaDB is rolling with the big boys and you're offering big bucks spending support for people willing to pay for it. You need a product to back it, and including a TokuDB engine that can't replicate properly because it can't tell the difference between a deleted row and an existing one (recent bug you happily dragged in) or screwing up the basic principle of pulling in a basic concept like views from other versions and screwing their algorithm isn't the way to get people scoffing at Oracle's (spit) MySQL or Percona and therefore picking you.

Your founder, Monty, taught us to expect better in the project he sold then forked. So do better.

We have tried our best to find a fix for this bug, but unfortunately we didn't come up with a way of how to we could distinguish between a MySQL view file and a MariaDB one.

If we try to change this back in a new MariaDB version, then all MariaDB users will be affected which is not good either.

We can't easily fix this in MySQL upgrade either as this script can't know if the view comes from from MySQL or MariaDB and thus we don't know which value is correct.

To ensure that things like this doesn't ever happen in the future, we have now added version numbers to the .FRM files for views. In the future if we get something wrong, we will able to fix it.

Back to the original problem.
If we could just find a suitable solution with minimum impact, then we would of course be happy to implement it. So far I have not seen any good suggestions.

After thinking about this for a bit, here is some things that may work and that we could consider adding to an upcoming MariaDB version to try to fix this:

• Fix that mysql_upgrade adds the version number to all views.
• Add an option to mysql_upgrade '--upgrade-from-mysql' where we fix the ALGORITHM=MERGE or ALGORITHM=TEMPTABLE encoding to match mariadb's for all views that doesn't have a MariaDB version number (ie, old view files).
• We could try to detect in mysql_upgrade if we are running on data from a MySQL version and automaticly enable --upgrade-from-mysql (may be hard but should not impossible).

The effect of the above would be that old MariaDB users would be unaffected and most MySQL users would be fine if they run mysql_upgrade once.

The problems would after this only affect users that comes from MySQL and don't run mysql_upgrade or if they abort mysql_upgrade in the middle and don't run it again with --upgrade-from-mysql

How would the above sound?

Having a switch for mysql_upgrade sounds like a stellar solution.

For anyone stumbling across this bug before it's fixed, a simple workaround in the absence of mysql_upgrade is:

mysqldump schema viewname | sed -e 's/ALGORITHM=TEMPTABLE/ALGORITHM=MERGE/g' | mysql -D schema

This will drop and recreate the view with the correct algorithm, though be careful in the cases of multiple views to list them in the correct order if any depend upon the existence of the others.

Thanks for the reply Monty.

Fixes committed. It need review...

revno: 4407
revision-id: sanja@askmonty.org-20150209014828-kx3cr36hgrvjhtrg
parent: svoj@mariadb.org-20150114135038-v50g2cul4vce63h8
committer: sanja@askmonty.org
branch nick: work-maria-5.5-MDEV-6916-check_view
timestamp: Mon 2015-02-09 02:48:28 +0100
message:
MDEV-6916: Upgrade from MySQL to MariaDB breaks already created views

CHECK/REPAIR commands and mysql_upgradesupport for upgrade from MySQL server support.

the review:

https://lists.launchpad.net/maria-developers/msg08148.html

corrections based on review:

https://lists.launchpad.net/maria-developers/msg08451.html

Thank you a lot for the work you have made!!!
The task is not abandoned completely I just have higher priority things, but I hope I'll have time soon to sort all this out.

Here is a part of this bug test suite