-710: Table <table> has been dropped, altered, or renamed.

During a recent meet-up with some IBM developers, we brought up the topic of error -710: Table <table> has been dropped, altered, or renamed. This error usually occurs when a prepared SQL statement has been invalidated by a schema change of some kind and can make code deployment or adding indices difficult in a live environment while applications are running.

IBM told us they have made significant progress with this problem. Firstly, in version 11.10, the onconfig parameter AUTO_REPREPARE was added which facilitated the automatic re-preparing of an already prepared SQL statement internally by the Informix engine if it had been invalidated by a schema change. Unfortunately this fix didn’t cover all scenarios, something IBM were open about at the time and there is an article in the IDS Experts blog on developerWorks covering the progress that was made then.

However, IBM now tell us they have made further progress on this problem to the point now where the -710 error should occur less frequently, if at all.

The developerWorks article describes six scenarios where a -710 error could occur in Informix 10.00 or earlier and the first three of these scenarios are fixed in Informix 11.10 when AUTO_REPREPARE is switched on. I thought it would be useful to create a test case for each of these and see if the problem has indeed been resolved.

The article gives more information but the six scenarios are:

  1. An index created on a table where a select statement has already been prepared and executed will cause that select statement to fail when it is executed again.
  2. A similar scenario where the select statement has not been previously used, a cursor is used and the select uses select *.
  3. A more complex example involving two tables, one with a trigger, and two procedures for which some code is supplied.
  4. Changing the number and type of columns in a select statement.
  5. Executing a prepared DDL statement, presumably where other DDL has been run on the table.
  6. An unspecified race condition.

All of this, apart from the last item, the race condition, is fairly easy to script up and test, although one slight caveat is that I prefer Perl-DBI and know little about languages like ESQL/C so I don’t have explicit control of cursors.

Thanks to a chum, I got hold of a copy of Informix 10.00.FC10 and ran through tests one to five:

sandbox_shm thompsonb@informix1:~ > ./710_test_cases.pl all

Running test 1: $prepare s1 from "select c1, c2 from t710";$execute s1;$create index i1 on t710(c1);$execute s1; -------> -710 error

Dropping existing table 't710':
done.
Creating table 't710':
done.
Inserting some data into table 't710':
done.
Selecting values:

c1 c2
10 10
20 20
30 30
56 56

Creating index:
done.
Re-using prepared statement to select values:

c1 c2
DBD::Informix::st execute failed: SQL: -710: Table (thompsonb.t710) has been dropped, altered or renamed. at ./710_test_cases.pl line 80.
Closing statement handle:
done.

Running test 2: $prepare s1 from "select * from t710 where c1 = 10";$declare curs1 cursor for s1;$create index i1 on t710(c1);$open curs1; -------> -710 error

Dropping existing table 't710':
done.
Creating table 't710':
done.
Inserting some data into table 't710':
done.
Preparing select statement:
done.
Creating index:
done.
Using prepared statement to select values:

c1 c2
DBD::Informix::st execute failed: SQL: -710: Table (thompsonb.t710) has been dropped, altered or renamed. at ./710_test_cases.pl line 113.
Closing statement handle:
done.

Running test 3: create procedure p1(c_a int, c_b int) returning integer;insert into A values(1001, 1001);update A set b=c_b where A.a=c_a; ==>(You have an update trigger defined on A which inserts into table B return 0;end procedure; create procedure p2() returning integer;define i integer;let i=p1(56, 56);create index i1 on B(b);return p1(56, 56); -- > -710 error when p1 is executedend procedure;

Dropping existing table 'a':
done.
Creating table 'a':
done.
Inserting some data into table 'a':
done.
Dropping existing table 'b':
done.
Creating table 'b':
done.
Inserting some data into table 'b':
done.
Dropping existing procedure 'p1':
done.
Creating procedure 'p1':
done.
Dropping existing procedure 'p2':
done.
Creating procedure 'p2':
done.
Creating update trigger on table 'a' which inserts into table 'b':
done.
Preparing statement to execute procedure 'p2':
done.
Executing procedure 'p2':
DBD::Informix::st fetch failed: SQL: -710: Table (thompsonb.b) has been dropped, altered or renamed. at ./710_test_cases.pl line 167.
done.
Closing statement handle:
done.

Running test 4: The number and type of columns in your SELECT list have changed.

Dropping existing table 't710':
done.
Creating table 't710':
done.
Inserting some data into table 't710':
done.
Preparing select statement from table 't710':
done.
Adding extra column to table 't710':
done.
Selecting from table 't710' using prepared statement:

c1 c2
DBD::Informix::st execute failed: SQL: -710: Table (thompsonb.t710) has been dropped, altered or renamed. at ./710_test_cases.pl line 195.
done.
Closing statement handle:
done.

Running test 5: If you are executing a prepared DDL statement, you might get -710 errors.

Dropping existing table 't710':
done.
Creating table 't710':
done.
Inserting some data into table 't710':
done.
Prepare a statement to add column 'c3' to table 't710':
done.
Add a column 'c4' to table t710:
done.
Execute the prepared statement to add column 'c3' to table 't710':
DBD::Informix::st execute failed: SQL: -710: Table (thompsonb.t710) has been dropped, altered or renamed. at ./710_test_cases.pl line 227.
done.
Closing statement handle:
done.

Exiting cleanly.

Great (in a way): all the test cases give a -710 error in Informix 10.00! I’ve put my other results in a table; a fail indicates that I got the -710 error:

Test Informix 10.00.FC10 Informix 11.50.FC9W2 without AUTO_REPREPARE Informix 11.50.FC9W2 with AUTO_REPREPARE Informix 11.70.FC5W1 without AUTO_REPREPARE Informix 11.70.FC5W1 with AUTO_REPREPARE
1 Fail Fail Pass Fail Pass
2 Fail Fail Pass Fail Pass
3 Fail Fail Pass Fail Pass
4 Fail Fail Pass Fail Pass
5 Fail Fail Fail Fail Fail

Unfortunately I do not have Informix 11.10 to test with and it is no longer available for download. It would be interesting to know whether it passes test 4 or not with AUTO_REPREPARE set.

Is the failure of test 5 by the latest releases important? I would say not. Preparing a DDL statement, then preparing and executing another on the same table before executing the first prepared statement is certainly not something I’ve seen done before in a real environment.

If anyone would like to have a go at reproducing my results or has any comments on my implementation of the test cases, I have posted the code here.

Advertisements