Query is not updated after adding a column to table

Hi there,

1. Create a table: tbl_test with columns id, color

2. Create a view: qry_test: SELECT * FROM tbl_test

3. Add some records to the table

4. Start the view and look at the results of the view: It works, all data from the table is displayed

5. Create a new column in tbl_test: price

6. Start the view and look at the results of the view: The NEW column is NOT displayed

7. Refresh the view: The new column is still not displayed

The new column will not be displayed until I delete the view and create it new.

Video: https: // www.loom.com / share / dc7b5e9613ed4662b1e2275bca3272bb

What am I doing wrong or is this a bug?

Jorg

My version: 12.3.0.6656 (64 Bit) OS: Windows 10

You're probably seeing an older cached status of the view definition.

Your video shows you are not using build 6656 but the official 12.3.0.6589. Please update to the latest build, click the "View" tab and click "Refresh", then look at the "Data" tab to see if the new column is now displayed.

If not, please look at the view definition. Perhaps the server modified your original query.

I updated to build 6656 shortly after my first post to check if there is a change. It's still the same behavour. So I posted: "My version: 12.3.0.6656 (64 Bit) OS: Windows 10".

I checked it again after restart, and there is no change.

The view definition was not modified. It's still: "SELECT * FROM tbl_test".

I still hope HeidiSQL is not fooling you with loading the original (probably outdated) view definition. Please check what SHOW CREATE VIEW myview returns.

SHOW CREATE VIEW qry_test

returns

View: qry_test

Create view: CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW qry_test AS select tbl_test.id AS id,tbl_test.color AS color from tbl_test

Character set client: utf8mb4

Collation connection: utf8mb4_general_ci

The column "price" which was created in the table after the view creation is missing in the view definition.

That's it. The server modified the view definition while you saved it, and HeidiSQL fools you with the original "*" version. In the end you need to modify the view definition after adding a column to a referenced table.

This is what I did the last months. But for me as a professional it's not the way I want walk until the rest of my days. When I develop a solution for a client there is a lot of table modifications. That's part of my job.

Do you think you can find a solution in HeidiSQL that automates the process of updating the view definition (in HeidiSQL) after modifying a table (also in HeidiSQL)?

No, such an automatism must be part of the server, as table modifications can also be done outside HeidiSQL.

I'm just googling around and found this topic with still a manual alter-view approach.

It's also in the documentation of MariaDB: The view definition is "frozen" at creation time, so changes to the underlying tables afterwards do not affect the view definition. 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.

https://mariadb.com/kb/en/create-view/

After all I found the solution to update the definition of a view containign"SELECT * FROM" with the lowest effort:

1. Do the table modifications you want
2. Open the view for edit. If you just added columns to the table, there will be no error when opening the view. If you removed or changed a column in the table, there will be an error. Ignore the error.
3. Now the trick: Step into the "Name" entry of the view and add a "Space" (= " ") to the view name. After that remove that "Space" immediately.
4. Then save the view.

Open the view again to check if the error is gone. It will be gone.

In addition to step 2 "If you just added columns to the table, there will be no error when opening the view.": Even if there is no error, you must do the steps 3 and 4. Otherwise the the new columns will not be integrated into the view.