You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Alan Burlison <Al...@sun.com> on 2009/09/08 15:59:59 UTC

'Read timed out' error on update query

Environment: Derby 10.5.3.0

I'm trying to do a large update, and after about 15 seconds the update 
statement fails with the following error:

Error: A communications error has been detected: Read timed out.
SQLState:  08006
ErrorCode: 40000
Error occured in:
update users_to_collectives
set relationship_type_id relationship_type_id + 1
where relationship_type_id >= 4 and relationship_type_id <= 12

The table in question has 13769 rows in total and the update touches 
most of the rows - 12957.

Interestingly, if I do a

select count(*) from users_to_collectives

immediately before the update I don't get the error but the update takes 
a long time (~ 5 mins) to complete.

If I reconnect to the DB after the failed update and reissue the query, 
it completes very quickly (< 1 second) and with no errors.

There are no errors or warnings reported in derby.log.

The DB schema is at https://auth.opensolaris.org/xmlrpcapi/dbschema.jsp

Is this a bug?  Has it already been reported?

-- 
Alan Burlison
--

Re: 'Read timed out' error on update query

Posted by Alan Burlison <Al...@sun.com>.
Alan Burlison wrote:

> I'm trying to do a large update, and after about 15 seconds the update 
> statement fails with the following error:

If I recode the update as the equivalent set of updates:

update users_to_collectives set relationship_type_id = 13
where relationship_type_id = 12;
update users_to_collectives set relationship_type_id = 12
where relationship_type_id = 11;
update users_to_collectives set relationship_type_id = 11
where relationship_type_id = 10;
update users_to_collectives set relationship_type_id = 10
where relationship_type_id = 9;
update users_to_collectives set relationship_type_id = 9
where relationship_type_id = 8;
update users_to_collectives set relationship_type_id = 8
where relationship_type_id = 7;
update users_to_collectives set relationship_type_id = 7
where relationship_type_id = 6;
update users_to_collectives set relationship_type_id = 6
where relationship_type_id = 5;
update users_to_collectives set relationship_type_id = 5
where relationship_type_id = 4;

I see the following timings:

406 Row(s) Updated
Query 10 of 19 elapsed time (seconds) - Total: 0.793, SQL query: 0.793

2 Row(s) Updated
Query 11 of 19 elapsed time (seconds) - Total: 0.015, SQL query: 0.015

799 Row(s) Updated
Query 12 of 19 elapsed time (seconds) - Total: 1.952, SQL query: 1.952

989 Row(s) Updated
Query 13 of 19 elapsed time (seconds) - Total: 2.905, SQL query: 2.905

106 Row(s) Updated
Query 14 of 19 elapsed time (seconds) - Total: 0.072, SQL query: 0.072

4,677 Row(s) Updated
Query 15 of 19 elapsed time (seconds) - Total: 61.596, SQL query: 61.596

789 Row(s) Updated
Query 16 of 19 elapsed time (seconds) - Total: 1.828, SQL query: 1.828

889 Row(s) Updated
Query 17 of 19 elapsed time (seconds) - Total: 2.374, SQL query: 2.374

4,300 Row(s) Updated
Query 18 of 19 elapsed time (seconds) - Total: 47.398, SQL query: 7.398

-- 
Alan Burlison
--

Re: 'Read timed out' error on update query

Posted by Alan Burlison <Al...@sun.com>.
Dag H. Wanvik wrote:

>> Environment: Derby 10.5.3.0
>>
>> I'm trying to do a large update, and after about 15 seconds the update
>> statement fails with the following error:
>>
>> Error: A communications error has been detected: Read timed out.
>> SQLState:  08006
> 
> Could it be DERBY-2026?

Yep, that's it- I'm using SQuirreL to issue the SQL and I notice it has 
a logout timeout setting.  I've set that to 0 which means disabled, and 
now the update completes, although it still takes an absolute age:

12,957 Row(s) Updated
Query 10 of 12 elapsed time (seconds) - Total: 327.772, SQL query: 
327.772, Building output: 0

Thanks.

-- 
Alan Burlison
--

Re: 'Read timed out' error on update query

Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Alan Burlison <Al...@Sun.COM> writes:

> Environment: Derby 10.5.3.0
>
> I'm trying to do a large update, and after about 15 seconds the update
> statement fails with the following error:
>
> Error: A communications error has been detected: Read timed out.
> SQLState:  08006

Could it be DERBY-2026?

Dag