issues with mysqldump file import with views

[Colin Zhao]

Looks like there are many issues with the import feature.  I looked many of them, but they are not the same as mine.

I have a local mysql 5.5 database that I have worked on for quite a while.  It has some view tables.  When I load it to cloud sql via a sqldump file, the import went through no errors, but I can't query any of the views, from my GEA app nor from the web console.  I get access denied error on select statements.  Selects on tables are fine.  

Instance Id: neptune-1:dev-1.  Database: team2.

I know sometime mysql load can run into problems loading views.  Views will appear as physical tables, and you can't query them.  I think this must be the situation, but I don't know what to do to resolve it.  

The same mysqldump loads ok with my local databases.

Please help me out.

Colin

[Amit Mondal]

Maybe you could drop the view, import, and then create the views.

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-d...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/87d24eec-66b7-4784-857f-b17480a0688f%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Colin Zhao]

That's what I am trying to do.  Mysql doesn't have a straight forward way to export view definitions that can be loaded to do drop and recreate.  Have to do it by hand or scripting.

I think this is a problem that cloud sql team needs to fix.

[Colin Zhao]

While waiting Google Cloud Sql team to investigate, here is my work around in case anyone else run into the same issue.

1. Do the mysqldump to create the dump file

2. Upload to Cloud Storage and import to your Cloud SQL

3. Now we need to fix the views.  Use the mysqldbexport to export the schema definition.  You can't use mysqldump.  Delete all other definitions but views.  Remove the the ALGORITHM part from every create view statement.  Add before every create statement a drop view if exists statement.  Keep this file properly and amend it every time you add/drop a view.

4. Test it in your local mysql.

5. Upload and import.  Apparently, Cloud SQL knows how to import mysqldump and mysqldbexport files.

mysqldbexport is part of the Mysql Workbench, not with the Mysql server.