+1 Generally, we do encourage PR authors to use idempotent SQL changes including dropping a view instead of altering it. Perhaps the specific issue of altering view slipped past our code review, I can see it in:
engine/schema/src/main/resources/META-INF/db/schema-410to420.sql:ALTER VIEW `cloud`.`user_view` AS engine/schema/src/main/resources/META-INF/db/schema-41120to41200.sql:ALTER VIEW `cloud`.`disk_offering_view` AS engine/schema/src/main/resources/META-INF/db/schema-41120to41200.sql:ALTER VIEW `cloud`.`service_offering_view` AS engine/schema/src/main/resources/META-INF/db/schema-4930to41000.sql:-- Alter view storage_pool_view engine/schema/src/main/resources/META-INF/db/schema-4930to41000.sql:-- Alter view image_store_view Regards. ________________________________ From: Marcus <shadow...@gmail.com> Sent: Tuesday, July 7, 2020 20:58 To: dev@cloudstack.apache.org <dev@cloudstack.apache.org> Subject: mysql view changes during upgrade I just wanted to drop a line to the community - perhaps this has been addressed already and I'm just disorganized. I see that in the 4.12 SQL upgrade scripts we use "ALTER VIEW" rather than the previous pattern of "DROP VIEW IF EXISTS" + "CREATE". The problem I've seen with ALTER VIEW is that in order to alter view, the SQL may need to be run by the same user that initially created the view. This can cause issues for clusters or for DBs that have been migrated. Consider an installation that has started on a local MySQL instance, the view may have DEFINER=`cloud`@`localhost` - if the database is migrated off to a remote MySQL server, the user cloudstack is using may now be seen as `cloud`@`server1` - at which point ALTER VIEW is no longer going to work. DROP works, and CREATE will update the view to DEFINER=`cloud`@`server1`. I guess this is my plea to the community to think about not using ALTER VIEW in the future for SQL upgrades :-) rohit.ya...@shapeblue.com www.shapeblue.com 3 London Bridge Street, 3rd floor, News Building, London SE1 9SGUK @shapeblue
