[PHOENIX-1181] client cache fails to update itself after a table was altered from a diff client

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.0.0, 3.1.0
• Fix Version/s: 3.1.0, 4.1.0

Environment

I have tried on both the 3.0 and 3.1 latest as of 2014/08/18

• Assignee: Unassigned
• Reporter: Jody Landreneau
• Created: 18/Aug/14 23:49
• Updated: 21/Nov/15 02:17
• Resolved: 20/Aug/14 07:03

Description

Description:
If you have multiple phoenix clients running, which could be on several physical machines(diff vms), changing the schema of a table(by adding or removing a field) will result in errors in the clients that didn't issue the alter. This appears to be due to an internal client cache that is not refreshed. I note that the connections get their cache from this shared client cache so creating/closing connections does not help.

Repro:
1) A somewhat simple way to repro is to open the SQuirrel client and issue the following:
create table if not exists test_simpletable (
id VARCHAR NOT NULL,
field1 BIGINT
CONSTRAINT pk PRIMARY KEY (id))

2) use the following code snippet to run a client that inserts into the simple table and then pauses, allowing you to issue an alter in the SQuirrel client by adding an additional field("alter table test_simpletable add field2 BIGINT"). It then proceeds to attempt another insert.

public class ConnCacheTest {
    public static final String PHOENIX_JDBC_URL = "jdbc:phoenix:localhost";
    public static final String INSERT_ONE_FIELD = "upsert into test_simpletable (id, field1) values ( ?, ?)";
    public static final String INSERT_TWO_FIELD = "upsert into test_simpletable (id, field1, field2) values ( ?, ?, ?)";
    
    public static void main(String[] args){
        PhoenixConnection conn = null;
        try {
            Class.forName("org.apache.phoenix.jdbc.PhoenixDriver");
            Properties props = new Properties();

            // here we insert into the orig schema with one column
            conn = (PhoenixConnection) DriverManager.getConnection(PHOENIX_JDBC_URL, props);
            PreparedStatement stmtInsert1 = conn.prepareStatement(INSERT_ONE_FIELD);
            stmtInsert1.setString(1, "key1");
            stmtInsert1.setLong(2, 1L);
            stmtInsert1.execute();
            conn.commit();
            stmtInsert1.close();
            conn.close();

            // While sleeping for a min, alter the table to have an additional
            // column. Do the alter through a separate client like SQuirreL.
            System.out.println("Starting to wait, make the alter!!!");
            Thread.sleep(1000*60*1);
            
            // this insert will try to insert to two columns
            conn = (PhoenixConnection) DriverManager.getConnection(PHOENIX_JDBC_URL, props);
            PreparedStatement pstmt2 = conn.prepareStatement(INSERT_TWO_FIELD);
            pstmt2.setString(1, "key2");
            pstmt2.setLong(2, 2L);
            pstmt2.setLong(3, 2L);
            pstmt2.execute();
            conn.commit();
            pstmt2.close();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}

Result:
org.apache.phoenix.schema.ColumnNotFoundException: ERROR 504 (42703): Undefined column. columnName=FIELD2
at org.apache.phoenix.schema.PTableImpl.getColumn(PTableImpl.java:513)
at org.apache.phoenix.compile.FromCompiler$SingleTableColumnResolver.resolveColumn(FromCompiler.java:254)
at org.apache.phoenix.compile.UpsertCompiler.compile(UpsertCompiler.java:307)
at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.compilePlan(PhoenixStatement.java:442)
at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.compilePlan(PhoenixStatement.java:433)
at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:250)
at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:242)
at org.apache.phoenix.util.PhoenixContextExecutor.call(PhoenixContextExecutor.java:54)
at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:241)
at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:190)
at org.apache.phoenix.jdbc.PhoenixPreparedStatement.execute(PhoenixPreparedStatement.java:147)
at org.apache.phoenix.jdbc.PhoenixPreparedStatement.execute(PhoenixPreparedStatement.java:152)

Expected:
I would expect there to be some timeout setting on the client cache(I believe the server side has this setting) or when a new connection is created it gets the latest schema from metadata? This would allow users to set time on their connections.