MySQL/MariaDB create view error Illigal mix of collations

https://dba.stackexchange.com/questions/170610/how-to-retrieve-the-query-i-used-to-create-a-view




SHOW CREATE VIEW not work since it will dump create command within CONVERT()

Example:
 CREATE ALGORITHM=UNDEFINED DEFINER=`user`@`%` SQL SECURITY DEFINER VIEW `ViewFollowAndFollowerCount` AS select `t1`.`user_id` AS `user_id`,`t4`.`user_name` AS `user_name`,`t2`.`followCount` AS `followCount`,`t3`.`followerCount` AS `followerCount`,`t1`.`created_at` AS `follow_created_at` from (((`UserFollowData` `t1` left join `ViewFollowCountGroupByUserId` `t2` on((`t1`.`user_id` = `t2`.`user_id`))) left join `ViewFollowCountGroupByFollowUserId` `t3` on((`t1`.`user_id` = `t3`.`follow_user_id`))) left join `UserData` `t4` on((convert(`t1`.`user_id` using utf8mb4) = `t4`.`user_id`)))

I got error something like: "Illegal mix of collations "

This caused by CONVERT() on dump /export (?) when convert collation ie. to utf8mb4...
Detail here https://bugs.mysql.com/bug.php?id=54278
So to fix/ work around I have to export / show create view without convert().

https://stackoverflow.com/questions/8150804/how-to-fix-a-broken-sql-view

SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS

WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';

 More about error detail:


After that I can create view. However some view I got another error:

ERROR 1356 (HY000): View 'yourdb.ViewFollowCountGroupBy' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

This somehow related to permissions:

https://www.shellhacks.com/mysql-show-users-privileges-passwords/
https://chartio.com/resources/tutorials/how-to-grant-all-privileges-on-a-database-in-mysql/

So to show user grant:
 SHOW GRANTS FOR 'user_name';
or SHOW GRANTS;

https://stackoverflow.com/questions/2126225/why-is-a-grant-usage-created-the-first-time-i-grant-a-user-privileges
https://sites.google.com/site/nazmulhudadba/error-1372-hy000-password-hash-should-be-a-41-digit-hexadecimal-number
https://stackoverflow.com/questions/20070988/mysql-error-1356-views
https://bugs.mysql.com/bug.php?id=76484

https://easyengine.io/tutorials/mysql/remote-access/
I don't know (or not pay attention) that root or any MySQL user can have different password on different HOST, I mean root can have no password with @localhost but have 'password' on other IP or internet '%'.