Mysql 8.X
Database name: db1.
In this database, I create a view:
CREATE VIEW vw1 AS
SELECT * FROM sometable;
When I check the source code of the view, instead of the code above, I can see:
SELECT * FROM db1.sometable;
I.e. MySQL engine automatically adds a database name prefix to every table I refer to in the view.
Now, I need to rename my database from db1 to db2. There is no built-in database rename functionality in MySQL. I have to take a backup, then drop the original database, then restore the backup under a new name.
Result: the vw1 view in my new db2 database tries to select rows from (now non-existing) db1 database, causing errors.
Now imagine 100s of databases, with 100s or 1000s of tables each. This issue makes things absolutely non-manageable.
Is there any way of stopping MySQL from adding database prefixes in view definitions?