View definition on CloudSQL becoming invalid if the data type of a column chnages

General Discussion
1

my database is in Google CloudSQL. and below is my liquibase script that

  1. creates a simple table
  2. creates first view based on that table definition
  3. creates second view based on the first view definition
  4. and then change the data type of a column in the table
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
	xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd"
	logicalFilePath="doesntmatter">
	<changeSet author="satkon" id="createTable">

		<createTable tableName="MYTABLE">
			<column name="UUID" type="java.sql.Types.VARCHAR(36)">
				<constraints nullable="false" />
			</column>
			<column name="NAME" type="java.sql.Types.VARCHAR(255)">
				<constraints nullable="false" />
			</column>
			<column name="DESCRIPTION" type="TEXT" />

			<column name="SPEC_CONTENT" type="TEXT" />

		</createTable>
	</changeSet>

	<changeSet author="satkon" id="createFirstView">
		<createView replaceIfExists="true" viewName="MYVIEW1">
            SELECT MYTABLE.UUID AS TABLEID, MYTABLE.SPEC_CONTENT AS SPEC_CONTENT
            FROM MYTABLE
        </createView>
	</changeSet>
	
	<changeSet author="satkon" id="createSecondView">
		<createView replaceIfExists="true" viewName="MYVIEW2">
            SELECT MYVIEW1.SPEC_CONTENT AS SPEC_CONTENT
            FROM MYVIEW1
        </createView>
	</changeSet>
	
	<changeSet author="satkon" id="updateDataType">
		<modifyDataType tableName="MYTABLE"
			columnName="SPEC_CONTENT" newDataType="MEDIUMTEXT" />
	</changeSet>

</databaseChangeLog>

by the end of this script execution, the definition of MYVIEW2 is becoming invalid and throws “eferences invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them” error. This is happening only on CloudSQL database. If I generate the SQL statements from liquibase using “updateSQL” option and execute those SQLs manually, then issue does not occur.

If liquibase is not involved there does not seem to be any issue. Can someone please help on this issue?

2

Welcome, @satkon !

Can you share the exact error text you’re getting? I’m not familiar with CloudSQL, and will need to ask the team next week who can help with this, and having the exact error may help.

3

thank you for the reply @tabbyfoo

It is pretty easy to reproduce this error on CloudSQL

once the liquibase script that I shared in the earlier post gets executed, if you run the query “desc MYVIEW2”, you will receive an error “Error Code: 1356. View ‘MYVIEW2’ references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them”

4

I’m not at all familiar with CloudSQL, but this is common object validation behavior in DBMS platforms when it comes to object dependencies and structure changes.

Same thing happens in Oracle. Now the way things are revalidated may differ between DBMS platforms. In oracle, I would add changesets after the table modification to compile each of the views:

alter view myview1 compile;
alter view myview2 compile;

Some changes may actually break the view to the point it needs to be recreated, so your mileage may vary.

Oracle will also automatically revalidate on first usage of the views, but proactively compiling them will make it so they never look invalid.

Maybe some of what I supplied will help you.

5

thanks @daryldoak … but for CloudSQL (or mysql) there doesn’t seem to be an equivalent of ‘alter view compile’ but looks like recreating the views when the table structure changes is a good practice to follow anyway…