Is it possible to update views columns automatically (Mysql)?

I have the following scenario:

There is one database (main) that holds common information such as 'modules', 'menu', etc and one database for each project containing a view for each of those tables in main database. For example:

main

modules (id, description)
menu (id, label, href, ...)
...

project1

modules_view
menu_view

Those views where created as simple as:

CREATE VIEW menu_view AS SELECT * FROM main.menu

Now, I have to create an 'order' column for the menu but the view columns aren't being updated.

ALTER TABLE `menu` ADD `menu_order` INT NOT NULL AFTER `href`;

Is it possible to maintain the views columns updated without having to do it mannualy each time I have to create a new column in the main table?

OBS: there are 10+ projects... So it will become harder to maintain as this number grows

Solution

You are defining the view using *:

CREATE VIEW menu_view AS
    SELECT * FROM main.menu;

Alas, the * is interpreted when the view is created, not when the view is run. You can actually see in the metadata that the columns are defined.

The only way to do what you want is to recompile/alter the views for the new columns.

This is explained in the documentation:

The view definition is “frozen” at creation time and is not affected by subsequent changes to the definitions of the underlying tables. For example, if a view is defined as SELECT * on a table, new columns added to the table later do not become part of the view, and columns dropped from the table will result in an error when selecting from the view.