From derbyschneiderjacob@example.net Tue Apr 01 00:31:25 2008 Return-Path: <derbyschneiderjacob@example.net> Delivered-To: schneiderjacob@example.net Received: (qmail 30614 invoked from network); 1 Apr 2008 00:31:25 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (83.161.120.155) by minotaur.apache.org with SMTP; 1 Apr 2008 00:31:25 -0000 Received: (qmail 72804 invoked by uid 500); 1 Apr 2008 00:31:24 -0000 Delivered-To: schneiderjacob@example.net Received: (qmail 72775 invoked by uid 500); 1 Apr 2008 00:31:24 -0000 Mailing-List: contact schneiderjacob@example.net; run by ezmlm Precedence: bulk list-help: <mailto:schneiderjacob@example.net> list-unsubscribe: <mailto:schneiderjacob@example.net> List-Post: <mailto:schneiderjacob@example.net> List-Id: <derby-user.db.apache.org> Reply-To: "Derby Discussion" <schneiderjacob@example.net> Delivered-To: mailing list schneiderjacob@example.net Received: (qmail 72758 invoked by uid 99); 1 Apr 2008 00:31:24 -0000 Received: from athena.apache.org (HELO athena.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 31 Mar 2008 17:31:24 -0700 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: local policy) Received: from [83.161.120.155] (HELO red.amberpoint.com) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 01 Apr 2008 00:30:43 +0000 Received: from [83.161.120.155] (bp-laptop.edgility.com [83.161.120.155]) by red.amberpoint.com (8.14.0/8.12.11) with ESMTP id m310UrxZ007026 for <schneiderjacob@example.net>; Mon, 31 Mar 2008 17:30:53 -0700 (PDT) Message-ID: <schneiderjacob@example.net> Date: Mon, 31 Mar 2008 17:30:53 -0700 From: Bryan Pendleton <schneiderjacob@example.net> User-Agent: Thunderbird 83.161.120.155 (Windows/20080213) MIME-Version: 1.0 To: Derby Discussion <schneiderjacob@example.net> Subject: Re: is it easy to change int to double? References: <schneiderjacob@example.net> In-Reply-To: <schneiderjacob@example.net> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Checked: Checked by ClamAV on apache.org > Is there an easy way to change a column type from int to double while > automatically converting the data? One technique is as follows: 1) alter table add double-column 2) update table set double-column = int-column 3) alter table drop int-column thanks, bryan From derbyschneiderjacob@example.net Tue Apr 01 01:35:32 2008 Return-Path: <derbyschneiderjacob@example.net> Delivered-To: schneiderjacob@example.net Received: (qmail 65937 invoked from network); 1 Apr 2008 01:35:32 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (83.161.120.155) by minotaur.apache.org with SMTP; 1 Apr 2008 01:35:32 -0000 Received: (qmail 37532 invoked by uid 500); 1 Apr 2008 01:35:31 -0000 Delivered-To: schneiderjacob@example.net Received: (qmail 37227 invoked by uid 500); 1 Apr 2008 01:35:30 -0000 Mailing-List: contact schneiderjacob@example.net; run by ezmlm Precedence: bulk list-help: <mailto:schneiderjacob@example.net> list-unsubscribe: <mailto:schneiderjacob@example.net> List-Post: <mailto:schneiderjacob@example.net> List-Id: <derby-user.db.apache.org> Reply-To: "Derby Discussion" <schneiderjacob@example.net> Delivered-To: mailing list schneiderjacob@example.net Received: (qmail 37216 invoked by uid 99); 1 Apr 2008 01:35:30 -0000 Received: from nike.apache.org (HELO nike.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 31 Mar 2008 18:35:30 -0700 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: local policy) Received: from [83.161.120.155] (HELO web1.nuix.com.au) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 01 Apr 2008 01:34:39 +0000 Received: from [83.161.120.155] (unknown [83.161.120.155]) by web1.nuix.com.au (Postfix) with ESMTP id CC3E81DF104 for <schneiderjacob@example.net>; Tue, 1 Apr 2008 11:34:11 +1000 (EST) Message-ID: <schneiderjacob@example.net> Date: Tue, 01 Apr 2008 12:35:12 +1100 From: David Sitsky <schneiderjacob@example.net> Organization: NUIX Pty Ltd User-Agent: Thunderbird 83.161.120.155 (Windows/20080213) MIME-Version: 1.0 To: Derby Discussion <schneiderjacob@example.net> Subject: Re: ERROR XSDG2: Invalid checksum on Page Page(0,Container(0, 1313)) References: <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> In-Reply-To: <schneiderjacob@example.net> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Checked: Checked by ClamAV on apache.org Hi Stanley, Thanks for your message. > The problem is happening on the Page 0 [the first page] of conglomerate > 1313 (the conglomerateId) - you can see what table/index this > corresponds to with the following query: > > select CONGLOMERATENUMBER, CONGLOMERATENAME > from sys.sysconglomerates > where conglomeratenumber = 1313; > > From the errors in your log I suspect this to be the table or one of > the indexes of the failing query listed: > INSERT INTO text_table (guidhigh, guid, data) That's correct - there are essentially two types of queries which seem to cause the issue on the text_table table. Either the insertion of blob data into text_table (it is compressed text), or a select call check for the existence of an entry given guidhigh and guid. > Does your database reboot without any errors after the shutdown/crash? > > You wrote: " /Derby locates the error, reboots the database, but seems > to inevitably report the same error again./ " > > I assume this means you get the same exception when the database reboots > (indicating the change was written to the transaction log and is > replaying the error as the database attempts to recover when the db > reboots). That's right. I just tried explicitly restarting the application by killing off the process (there is a nanny process which detects this and automatically restarts it). However I still get the same derby errors on startup. > 1) the databases are created in the directory D:\temp... If you > suspect that this directory is every purged/cleared then this in not a > good place to have database files. The files should be in a permanent > and secure location. In this case the d:\ is a raid array, and even though it is written to a directory called temp, nothing is being deleted from it. There are no virus scanners running either. > anything. Alternately if you shutdown the system then attempt to > access the database from a newly started JVM ( I would use IJ) and still > get the exception this shows that uncollected derby objects are not the > problem. Yes - I have confirmed that restarting the JVM still reports the same error on startup I am afraid. Any ideas on what to check for now? -- Cheers, David Nuix Pty Ltd Suite 79, 89 Jones St, Ultimo NSW 2007, Australia Ph: +1-325-387-2166 Web: http://www.nuix.com Fax: +1-325-387-2166 From derbyschneiderjacob@example.net Tue Apr 01 03:41:58 2008 Return-Path: <derbyschneiderjacob@example.net> Delivered-To: schneiderjacob@example.net Received: (qmail 25104 invoked from network); 1 Apr 2008 03:41:58 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (83.161.120.155) by minotaur.apache.org with SMTP; 1 Apr 2008 03:41:58 -0000 Received: (qmail 16436 invoked by uid 500); 1 Apr 2008 03:41:57 -0000 Delivered-To: schneiderjacob@example.net Received: (qmail 16416 invoked by uid 500); 1 Apr 2008 03:41:57 -0000 Mailing-List: contact schneiderjacob@example.net; run by ezmlm Precedence: bulk list-help: <mailto:schneiderjacob@example.net> list-unsubscribe: <mailto:schneiderjacob@example.net> List-Post: <mailto:schneiderjacob@example.net> List-Id: <derby-user.db.apache.org> Reply-To: "Derby Discussion" <schneiderjacob@example.net> Delivered-To: mailing list schneiderjacob@example.net Received: (qmail 16405 invoked by uid 99); 1 Apr 2008 03:41:57 -0000 Received: from athena.apache.org (HELO athena.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 31 Mar 2008 20:41:57 -0700 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: local policy) Received: from [83.161.120.155] (HELO web1.nuix.com.au) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 01 Apr 2008 03:41:13 +0000 Received: from [83.161.120.155] (unknown [83.161.120.155]) by web1.nuix.com.au (Postfix) with ESMTP id 9BDB71DF101 for <schneiderjacob@example.net>; Tue, 1 Apr 2008 13:40:38 +1000 (EST) Message-ID: <schneiderjacob@example.net> Date: Tue, 01 Apr 2008 14:41:39 +1100 From: David Sitsky <schneiderjacob@example.net> Organization: NUIX Pty Ltd User-Agent: Thunderbird 83.161.120.155 (Windows/20080213) MIME-Version: 1.0 To: Derby Discussion <schneiderjacob@example.net> Subject: Re: ERROR XSDG2: Invalid checksum on Page Page(0,Container(0, 1313)) References: <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> In-Reply-To: <schneiderjacob@example.net> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Checked: Checked by ClamAV on apache.org Hi Stanley, > The problem is happening on the Page 0 [the first page] of conglomerate > 1313 (the conglomerateId) - you can see what table/index this > corresponds to with the following query: > > select CONGLOMERATENUMBER, CONGLOMERATENAME > from sys.sysconglomerates > where conglomeratenumber = 1313; > > From the errors in your log I suspect this to be the table or one of > the indexes of the failing query listed: > INSERT INTO text_table (guidhigh, guid, data) I ran ij from the command-line, and connected to one of the problematic databases after killing the load. Sure enough 1313 refers to the primary key of text_table (there are no other indexes defined on this table). This is what is output: CONGLOMERATENUMBER |CONGLOMERATENAME -------------------------------------------------------------------------------- --------------------------------------------------------------------- 1313 |SQL080331145004520 This is the SQL we used to create this table: CREATE TABLE text_table (guidhigh BIGINT NOT NULL, guid BIGINT NOT NULL, data BLOB (1G) NOT NULL, PRIMARY KEY (guidhigh, guid)) What is interesting is if I perform a simple query on text_table, I get the following: ij> select count(*) from text_table; ERROR XSDG2: Invalid checksum on Page Page(0,Container(0, 1313)), expected=304,608,373, on-disk version=2,462,088,751, page dump follows: Hex dump: 00000000: 0076 0000 0001 0000 0000 0000 27ea 0000 .v.............. 00000010: 0000 0006 0000 0000 0000 0000 0000 0000 ................ 00000020: 0000 0000 0001 0000 0000 0000 0000 0000 ................ .... Same as my application. I have also noticed the connection is automatically closed, as is the case in my application if I try and perform another operation. ij> select count(*) from text_table; ERROR 08003: No current connection. Reconnecting to the database has the same result. So I guess it is not surprising now that conglomerate 1313 is always returned since all worker processes create the same database on startup. However the corruption is always happening on page 0. If we were experiencing true disk problems, I'd expect the page number to be random across the machines. Any ideas on what to try next? -- Cheers, David Nuix Pty Ltd Suite 79, 89 Jones St, Ultimo NSW 2007, Australia Ph: +1-325-387-2166 Web: http://www.nuix.com Fax: +1-325-387-2166 From derbyschneiderjacob@example.net Tue Apr 01 07:38:41 2008 Return-Path: <derbyschneiderjacob@example.net> Delivered-To: schneiderjacob@example.net Received: (qmail 26837 invoked from network); 1 Apr 2008 07:38:41 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (83.161.120.155) by minotaur.apache.org with SMTP; 1 Apr 2008 07:38:41 -0000 Received: (qmail 13992 invoked by uid 500); 1 Apr 2008 07:38:40 -0000 Delivered-To: schneiderjacob@example.net Received: (qmail 13642 invoked by uid 500); 1 Apr 2008 07:38:39 -0000 Mailing-List: contact schneiderjacob@example.net; run by ezmlm Precedence: bulk list-help: <mailto:schneiderjacob@example.net> list-unsubscribe: <mailto:schneiderjacob@example.net> List-Post: <mailto:schneiderjacob@example.net> List-Id: <derby-user.db.apache.org> Reply-To: "Derby Discussion" <schneiderjacob@example.net> Delivered-To: mailing list schneiderjacob@example.net Received: (qmail 13631 invoked by uid 99); 1 Apr 2008 07:38:39 -0000 Received: from nike.apache.org (HELO nike.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 01 Apr 2008 00:38:39 -0700 X-ASF-Spam-Status: No, hits=-1.0 required=10.0 tests=RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: local policy) Received: from [83.161.120.155] (HELO gmp-eb-inf-1.sun.com) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 01 Apr 2008 07:37:47 +0000 Received: from fe-emea-09.sun.com (gmp-eb-lb-2-fe1.eu.sun.com [83.161.120.155]) by gmp-eb-inf-1.sun.com (8.13.7+Sun/8.12.9) with ESMTP id m317c3kR027471 for <schneiderjacob@example.net>; Tue, 1 Apr 2008 07:38:07 GMT Received: from conversion-daemon.fe-emea-09.sun.com by fe-emea-09.sun.com (Sun Java System Messaging Server 6.2-8.04 (built Feb 28 2007)) id <schneiderjacob@example.net> (original mail from schneiderjacob@example.net) for schneiderjacob@example.net; Tue, 01 Apr 2008 08:38:03 +0100 (BST) Received: from khepri32.sun.com ([83.161.120.155]) by fe-emea-09.sun.com (Sun Java System Messaging Server 6.2-8.04 (built Feb 28 2007)) with ESMTPSA id <schneiderjacob@example.net> for schneiderjacob@example.net; Tue, 01 Apr 2008 08:37:31 +0100 (BST) Date: Tue, 01 Apr 2008 09:37:30 +0200 From: schneiderjacob@example.net Subject: Re: ERROR XSDG2: Invalid checksum on Page Page(0,Container(0, 1313)) In-reply-to: <schneiderjacob@example.net> Sender: schneiderjacob@example.net To: Derby Discussion <schneiderjacob@example.net> Message-id: <schneiderjacob@example.net> MIME-version: 1.0 Content-type: text/plain; charset=us-ascii Content-transfer-encoding: 7BIT References: <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> User-Agent: Gnus/5.110006 (No Gnus v0.6) Emacs/22.1 (usg-unix-v) X-Virus-Checked: Checked by ClamAV on apache.org David Sitsky <schneiderjacob@example.net> writes: > For what its worth, I did another run last night on my 6 quad-core > system. This time I had the derby issue happen for a JVM process on > machine 1, two processes on machine 4, and one on machine 5. I run > four JVM processes per quad-core machine. > > All the JVM processes have roughly the same data processing rate, but > the issue happens at different times into the load. The problem > occurred around time 420, 480, 800 and 900 minutes into the load for > the four problematic processes. Just to clarify: These jvm processes do not access the SAME database, do they? -- dt From derbyschneiderjacob@example.net Tue Apr 01 10:12:14 2008 Return-Path: <derbyschneiderjacob@example.net> Delivered-To: schneiderjacob@example.net Received: (qmail 81214 invoked from network); 1 Apr 2008 10:12:14 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (83.161.120.155) by minotaur.apache.org with SMTP; 1 Apr 2008 10:12:14 -0000 Received: (qmail 13405 invoked by uid 500); 1 Apr 2008 10:12:12 -0000 Delivered-To: schneiderjacob@example.net Received: (qmail 13377 invoked by uid 500); 1 Apr 2008 10:12:11 -0000 Mailing-List: contact schneiderjacob@example.net; run by ezmlm Precedence: bulk list-help: <mailto:schneiderjacob@example.net> list-unsubscribe: <mailto:schneiderjacob@example.net> List-Post: <mailto:schneiderjacob@example.net> List-Id: <derby-user.db.apache.org> Reply-To: "Derby Discussion" <schneiderjacob@example.net> Delivered-To: mailing list schneiderjacob@example.net Received: (qmail 13366 invoked by uid 99); 1 Apr 2008 10:12:11 -0000 Received: from nike.apache.org (HELO nike.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 01 Apr 2008 03:12:11 -0700 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: local policy) Received: from [83.161.120.155] (HELO file1.syd.nuix.com.au) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 01 Apr 2008 10:11:21 +0000 Received: from [83.161.120.155] (124-170-19-50.dyn.iinet.net.au [83.161.120.155]) by file1.syd.nuix.com.au (Postfix) with ESMTP id DB9E14A814D for <schneiderjacob@example.net>; Tue, 1 Apr 2008 21:11:54 +1100 (EST) Message-ID: <schneiderjacob@example.net> Date: Tue, 01 Apr 2008 21:11:36 +1100 From: David Sitsky <schneiderjacob@example.net> User-Agent: Thunderbird 83.161.120.155 (Windows/20080213) MIME-Version: 1.0 To: Derby Discussion <schneiderjacob@example.net> Subject: Re: ERROR XSDG2: Invalid checksum on Page Page(0,Container(0, 1313)) References: <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> In-Reply-To: <schneiderjacob@example.net> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Checked: Checked by ClamAV on apache.org schneiderjacob@example.net wrote: > David Sitsky <schneiderjacob@example.net> writes: > >> For what its worth, I did another run last night on my 6 quad-core >> system. This time I had the derby issue happen for a JVM process on >> machine 1, two processes on machine 4, and one on machine 5. I run >> four JVM processes per quad-core machine. >> >> All the JVM processes have roughly the same data processing rate, but >> the issue happens at different times into the load. The problem >> occurred around time 420, 480, 800 and 900 minutes into the load for >> the four problematic processes. > > Just to clarify: These jvm processes do not access the SAME database, do > they? No - they all have their own embedded derby database. Cheers, David From derbyschneiderjacob@example.net Tue Apr 01 14:17:00 2008 Return-Path: <derbyschneiderjacob@example.net> Delivered-To: schneiderjacob@example.net Received: (qmail 82910 invoked from network); 1 Apr 2008 14:17:00 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (83.161.120.155) by minotaur.apache.org with SMTP; 1 Apr 2008 14:17:00 -0000 Received: (qmail 58161 invoked by uid 500); 1 Apr 2008 14:16:59 -0000 Delivered-To: schneiderjacob@example.net Received: (qmail 58132 invoked by uid 500); 1 Apr 2008 14:16:58 -0000 Mailing-List: contact schneiderjacob@example.net; run by ezmlm Precedence: bulk list-help: <mailto:schneiderjacob@example.net> list-unsubscribe: <mailto:schneiderjacob@example.net> List-Post: <mailto:schneiderjacob@example.net> List-Id: <derby-user.db.apache.org> Reply-To: "Derby Discussion" <schneiderjacob@example.net> Delivered-To: mailing list schneiderjacob@example.net Received: (qmail 58118 invoked by uid 99); 1 Apr 2008 14:16:58 -0000 Received: from nike.apache.org (HELO nike.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 01 Apr 2008 07:16:58 -0700 X-ASF-Spam-Status: No, hits=-1.0 required=10.0 tests=RCVD_IN_DNSWL_LOW,SPF_HELO_PASS,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of schneiderjacob@example.net designates 83.161.120.155 as permitted sender) Received: from [83.161.120.155] (HELO ciao.gmane.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 01 Apr 2008 14:16:08 +0000 Received: from list by ciao.gmane.org with local (Exim 4.43) id 1JghHn-0004nj-5Y for schneiderjacob@example.net; Tue, 01 Apr 2008 14:16:27 +0000 Received: from yktgi01e0-s5.watson.ibm.com ([83.161.120.155]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for <schneiderjacob@example.net>; Tue, 01 Apr 2008 14:16:27 +0000 Received: from acade4u by yktgi01e0-s5.watson.ibm.com with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for <schneiderjacob@example.net>; Tue, 01 Apr 2008 14:16:27 +0000 X-Injected-Via-Gmane: http://gmane.org/ To: schneiderjacob@example.net From: Guillermo Averboch <schneiderjacob@example.net> Subject: Re: derby.storage.pageCacheSize on multiple databases Date: Tue, 1 Apr 2008 14:16:16 +0000 (UTC) Lines: 12 Message-ID: <schneiderjacob@example.net> References: <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Complaints-To: schneiderjacob@example.net X-Gmane-NNTP-Posting-Host: main.gmane.org User-Agent: Loom/3.14 (http://gmane.org/) X-Loom-IP: 83.161.120.155 (Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30; .NET CLR 3.0.04506.648)) Sender: news <schneiderjacob@example.net> X-Virus-Checked: Checked by ClamAV on apache.org Knut, Actually, it does not work. If you set derby.storage.pageCacheSize after loading a database, the setting affects that database too. In the end, the last setting affects all databases. I learned this the hard way, after everybody started complaining about how awfully slow the system was... Guillermo From derbyschneiderjacob@example.net Tue Apr 01 17:05:32 2008 Return-Path: <derbyschneiderjacob@example.net> Delivered-To: schneiderjacob@example.net Received: (qmail 397 invoked from network); 1 Apr 2008 17:05:32 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (83.161.120.155) by minotaur.apache.org with SMTP; 1 Apr 2008 17:05:32 -0000 Received: (qmail 51148 invoked by uid 500); 1 Apr 2008 17:05:31 -0000 Delivered-To: schneiderjacob@example.net Received: (qmail 50696 invoked by uid 500); 1 Apr 2008 17:05:30 -0000 Mailing-List: contact schneiderjacob@example.net; run by ezmlm Precedence: bulk list-help: <mailto:schneiderjacob@example.net> list-unsubscribe: <mailto:schneiderjacob@example.net> List-Post: <mailto:schneiderjacob@example.net> List-Id: <derby-user.db.apache.org> Reply-To: "Derby Discussion" <schneiderjacob@example.net> Delivered-To: mailing list schneiderjacob@example.net Received: (qmail 50684 invoked by uid 99); 1 Apr 2008 17:05:30 -0000 Received: from nike.apache.org (HELO nike.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 01 Apr 2008 10:05:30 -0700 X-ASF-Spam-Status: No, hits=-1.0 required=10.0 tests=RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: local policy) Received: from [83.161.120.155] (HELO gmp-eb-inf-1.sun.com) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 01 Apr 2008 17:04:39 +0000 Received: from fe-emea-09.sun.com (gmp-eb-lb-2-fe3.eu.sun.com [83.161.120.155]) by gmp-eb-inf-1.sun.com (8.13.7+Sun/8.12.9) with ESMTP id m31H4u32022905 for <schneiderjacob@example.net>; Tue, 1 Apr 2008 17:04:58 GMT Received: from conversion-daemon.fe-emea-09.sun.com by fe-emea-09.sun.com (Sun Java System Messaging Server 6.2-8.04 (built Feb 28 2007)) id <schneiderjacob@example.net> (original mail from schneiderjacob@example.net) for schneiderjacob@example.net; Tue, 01 Apr 2008 18:04:56 +0100 (BST) Received: from khepri23.norway.sun.com ([83.161.120.155]) by fe-emea-09.sun.com (Sun Java System Messaging Server 6.2-8.04 (built Feb 28 2007)) with ESMTPSA id <schneiderjacob@example.net> for schneiderjacob@example.net; Tue, 01 Apr 2008 18:04:56 +0100 (BST) Date: Tue, 01 Apr 2008 19:04:55 +0200 From: schneiderjacob@example.net (Dag H. Wanvik) Subject: Re: XSDB0Unexpected exception on in-memory page In-reply-to: <schneiderjacob@example.net> Sender: schneiderjacob@example.net To: Derby Discussion <schneiderjacob@example.net> Message-id: <schneiderjacob@example.net> MIME-version: 1.0 Content-type: text/plain; charset=us-ascii Content-transfer-encoding: 7BIT References: <schneiderjacob@example.net> User-Agent: Gnus/5.1008 (Gnus v5.10.8) Emacs/22.1 (usg-unix-v) X-Virus-Checked: Checked by ClamAV on apache.org Templexp Tan <schneiderjacob@example.net> writes: > Hello, > > When my derby database got out of memory, and when I restart the db. it got > the exception like the following: > any idea how to solve the problem? > > ERROR XJ040: DERBY SQL error: SQLCODE: -1, SQLSTATE: XJ040, SQLERRMC: Failed > to start database 'mydb', see the next exception for details.888a:dbb7:2d49:b3f6:8605:bf4e:caed:e5a4SQLSTATE: > XSDB0Unexpected exception on in-memory page Page(33,Container(0, 1137)) This looks similar to DERBY-749. You may want to provide any information you have to that issue in JIRA; I saw Mike closed that issue due to lack of information, maybe you have something that will help with this! http://issues.apache.org/jira/browse/DERBY-749 Dag From derbyschneiderjacob@example.net Tue Apr 01 18:01:08 2008 Return-Path: <derbyschneiderjacob@example.net> Delivered-To: schneiderjacob@example.net Received: (qmail 30792 invoked from network); 1 Apr 2008 18:01:06 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (83.161.120.155) by minotaur.apache.org with SMTP; 1 Apr 2008 18:01:06 -0000 Received: (qmail 36286 invoked by uid 500); 1 Apr 2008 18:01:05 -0000 Delivered-To: schneiderjacob@example.net Received: (qmail 36259 invoked by uid 500); 1 Apr 2008 18:01:05 -0000 Mailing-List: contact schneiderjacob@example.net; run by ezmlm Precedence: bulk list-help: <mailto:schneiderjacob@example.net> list-unsubscribe: <mailto:schneiderjacob@example.net> List-Post: <mailto:schneiderjacob@example.net> List-Id: <derby-user.db.apache.org> Reply-To: "Derby Discussion" <schneiderjacob@example.net> Delivered-To: mailing list schneiderjacob@example.net Received: (qmail 36248 invoked by uid 99); 1 Apr 2008 18:01:05 -0000 Received: from athena.apache.org (HELO athena.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 01 Apr 2008 11:01:05 -0700 X-ASF-Spam-Status: No, hits=-2.8 required=10.0 tests=RCVD_IN_DNSWL_MED,SPF_NEUTRAL X-Spam-Check-By: apache.org Received-SPF: neutral (athena.apache.org: 83.161.120.155 is neither permitted nor denied by domain of schneiderjacob@example.net) Received: from [83.161.120.155] (HELO e6.ny.us.ibm.com) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 01 Apr 2008 18:00:22 +0000 Received: from d01relay07.pok.ibm.com (d01relay07.pok.ibm.com [83.161.120.155]) by e6.ny.us.ibm.com (8.13.8/8.13.8) with ESMTP id m31I2a8R007344 for <schneiderjacob@example.net>; Tue, 1 Apr 2008 14:02:36 -0400 Received: from d03av01.boulder.ibm.com (d03av01.boulder.ibm.com [83.161.120.155]) by d01relay07.pok.ibm.com (8.13.8/8.13.8/NCO v8.7) with ESMTP id m31I0NmV1871900 for <schneiderjacob@example.net>; Tue, 1 Apr 2008 14:00:24 -0400 Received: from d03av01.boulder.ibm.com (loopback [83.161.120.155]) by d03av01.boulder.ibm.com (83.161.120.15560308/8.13.3) with ESMTP id m31I0NJ8000513 for <schneiderjacob@example.net>; Tue, 1 Apr 2008 12:00:23 -0600 Received: from [83.161.120.155] (sig-9-48-104-190.mts.ibm.com [83.161.120.155]) by d03av01.boulder.ibm.com (83.161.120.15560308/8.12.11) with ESMTP id m31I0Ll4032760 for <schneiderjacob@example.net>; Tue, 1 Apr 2008 12:00:22 -0600 Message-ID: <schneiderjacob@example.net> Date: Tue, 01 Apr 2008 11:00:20 -0700 From: Stanley Bradbury <schneiderjacob@example.net> User-Agent: Thunderbird 83.161.120.155 (Windows/20080213) MIME-Version: 1.0 To: Derby Discussion <schneiderjacob@example.net> Subject: Re: ERROR XSDG2: Invalid checksum on Page Page(0,Container(0, 1313)) References: <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> In-Reply-To: <schneiderjacob@example.net> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Checked: Checked by ClamAV on apache.org David Sitsky wrote: > Hi Stanley, > >> The problem is happening on the Page 0 [the first page] of >> conglomerate 1313 (the conglomerateId) - you can see what table/index >> this corresponds to with the following query: >> >> select CONGLOMERATENUMBER, CONGLOMERATENAME >> from sys.sysconglomerates >> where conglomeratenumber = 1313; >> >> From the errors in your log I suspect this to be the table or one of >> the indexes of the failing query listed: >> INSERT INTO text_table (guidhigh, guid, data) > > I ran ij from the command-line, and connected to one of the > problematic databases after killing the load. Sure enough 1313 refers > to the primary key of text_table (there are no other indexes defined > on this table). This is what is output: > > CONGLOMERATENUMBER |CONGLOMERATENAME > > -------------------------------------------------------------------------------- > > --------------------------------------------------------------------- > 1313 |SQL080331145004520 > > This is the SQL we used to create this table: > > CREATE TABLE text_table (guidhigh BIGINT NOT NULL, > guid BIGINT NOT NULL, > data BLOB (1G) NOT NULL, > PRIMARY KEY (guidhigh, guid)) > > What is interesting is if I perform a simple query on text_table, I > get the following: > > ij> select count(*) from text_table; > ERROR XSDG2: Invalid checksum on Page Page(0,Container(0, 1313)), > expected=304,608,373, on-disk version=2,462,088,751, page dump > follows: Hex dump: > 00000000: 0076 0000 0001 0000 0000 0000 27ea 0000 .v.............. > 00000010: 0000 0006 0000 0000 0000 0000 0000 0000 ................ > 00000020: 0000 0000 0001 0000 0000 0000 0000 0000 ................ > .... > > Same as my application. I have also noticed the connection is > automatically closed, as is the case in my application if I try and > perform another operation. > > ij> select count(*) from text_table; > ERROR 08003: No current connection. > > Reconnecting to the database has the same result. > > So I guess it is not surprising now that conglomerate 1313 is always > returned since all worker processes create the same database on > startup. However the corruption is always happening on page 0. If we > were experiencing true disk problems, I'd expect the page number to be > random across the machines. > > Any ideas on what to try next? > Hi Dave - The good news is that it looks like you do not have to recover this database from backups because: 1) since you can boot the database using IJ the corruption is not in the transaction log (I assume you did not directly modify or delete any files in the database directory tree in order to bypass recovery - *yes, I have seen people do this*) 2) since the corruption is in an index you should be able to drop and recreate the index thus restoring the index without any data loss. I also have an idea that might get around it. Since the problem is with an index and during a data load (I am assuming the load is a one time database initialization streaming lots of data?) then I would try creating the index AFTER loading the data rather than declaring the index in the table Create statement. If the problem involves handling a large number of database pages and the index pages at the same time separating the activities will avoid it. Doing this also insures that index statistics are created (for more info on this see: http://wiki.apache.org/db-derby/CheckingForIndexStatistics) As for the cause of the problem I can't be sure but since this happens repeatedly and with different physical files it sounds like the problem is not with the physical disk surface (I think the RAID architecture helps protect against this type of failure anyway). I'd like to see this problem captured in an JIRA entry. You have provided a lot of good information here and the results of performing a load then creating the Primary Key will narrow it down more. My gut-level feel is that reproducing this will be hard without an environment similar to yours. With the multiple processors and the RAID array I imagine it is a pretty fast machine and this may be exposing a thread synchronization issue in Derby (or not). Having your information and stack traces may give the stores folks enough information to perform a code inspection for this or other issues. Would you file a JIRA describing your processing, the machine environment, the JVM and attach your derby log files? From derbyschneiderjacob@example.net Tue Apr 01 18:01:24 2008 Return-Path: <derbyschneiderjacob@example.net> Delivered-To: schneiderjacob@example.net Received: (qmail 31121 invoked from network); 1 Apr 2008 18:01:24 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (83.161.120.155) by minotaur.apache.org with SMTP; 1 Apr 2008 18:01:24 -0000 Received: (qmail 37095 invoked by uid 500); 1 Apr 2008 18:01:23 -0000 Delivered-To: schneiderjacob@example.net Received: (qmail 37080 invoked by uid 500); 1 Apr 2008 18:01:23 -0000 Mailing-List: contact schneiderjacob@example.net; run by ezmlm Precedence: bulk list-help: <mailto:schneiderjacob@example.net> list-unsubscribe: <mailto:schneiderjacob@example.net> List-Post: <mailto:schneiderjacob@example.net> List-Id: <derby-user.db.apache.org> Reply-To: "Derby Discussion" <schneiderjacob@example.net> Delivered-To: mailing list schneiderjacob@example.net Delivered-To: moderator for schneiderjacob@example.net Received: (qmail 31562 invoked by uid 99); 1 Apr 2008 06:33:15 -0000 X-ASF-Spam-Status: No, hits=2.0 required=10.0 tests=HTML_MESSAGE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of schneiderjacob@example.net designates 83.161.120.155 as permitted sender) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; bh=znWBUOQMiFuePG6V+8FoUpeQs1ELaP5Zyd2F8gkybMs=; b=Iy62nWgeeuEpy6rKt603uqZOznWBD+m8oVwh6XXa4wZF+lRl7rxZS00m0ut7LE8ybgTk/vJ6q7JchbQ3z9Ay4EqM23MhkNx8shgB4yDV39PVghRghe+TAIh3kLJsE6D+g2LHFiRBCFL1AehkMx+ejAAVbpSKPZ1o2l91Ayrh7FY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=KjytsTfjHzIu3sk7xUqCItWgEUB92dC9UJyRCP2isTvjE6g9hCmDoeRwEC2KroTCBtC9OAKCRDkxU4MyM1EMmMkm3PEtb8xQp++xTdYam9K/NVYUZMXmlruy1iw0UiaK3MLqtO0zLnTFK9978hq7Mn8DMyDCa1EX7/SnqH2VmWA= Message-ID: <schneiderjacob@example.net> Date: Tue, 1 Apr 2008 12:02:44 +0530 From: "UDAI GUPTA" <schneiderjacob@example.net> To: schneiderjacob@example.net Subject: Windows directory path in BUILDING.txt In-Reply-To: <schneiderjacob@example.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_14965_31868331.1207031564460" References: <schneiderjacob@example.net> X-Virus-Checked: Checked by ClamAV on apache.org ------=_Part_14965_31868331.1207031564460 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi, I was Building the Derby on windows machine for which referred BUILDING.txt from my ${derby.source}. there was an example in section 3.2(Creating ant.properties) showing how to set j14lib which was like.... j14lib=C:\j2sdk1.4.2_10\jre\lib (for windows machine) but if you do so....you will get error..... [setJdkProperties] java.vendor = Sun Microsystems Inc. [setJdkProperties] java.home = C:\java5\jdk1.5.0\jre [setJdkProperties] java.version = 1.5.0 [setJdkProperties] os.name = Windows XP *[setJdkProperties] j14lib = C:j2sdk1.4.2_10jrelib [setJdkProperties] j15lib = C:java5jre1.5.0lib* [setJdkProperties] [setJdkProperties] Please consult BUILDING.txt for instructions on how to set the jdk-library and compiler-classpath properties. [setJdkProperties] at org.apache.derbyPreBuild.PropertySetter.couldntSetProperty( PropertySetter.java:677) [setJdkProperties] at org.apache.derbyPreBuild.PropertySetter.setClasspathFromLib( PropertySetter.java:484) [setJdkProperties] at org.apache.derbyPreBuild.PropertySetter.execute( PropertySetter.java:207) [setJdkProperties] at org.apache.tools.ant.UnknownElement.execute( UnknownElement.java:288) [setJdkProperties] at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source) [setJdkProperties] at sun.reflect.DelegatingMethodAccessorImpl.invoke( DelegatingMethodAccessorImpl.java:25) [setJdkProperties] at java.lang.reflect.Method.invoke(Method.java:585) [setJdkProperties] at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:105) [setJdkProperties] at org.apache.tools.ant.Task.perform(Task.java:348) [setJdkProperties] at org.apache.tools.ant.Target.execute(Target.java :357) and if you do * j14lib=C:\\j2sdk1.4.2_10\\jre\\lib* (Double slash) you won't get this error...... I figured that out in minutes but may be this could consume time of some other newbie like me has anyone else noticed this in BUILDING.txt regards, udai ------=_Part_14965_31868331.1207031564460 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline <br><div class="gmail_quote">Hi,<br><br>&nbsp;&nbsp;&nbsp;&nbsp; I was Building the Derby on windows machine for which referred BUILDING.txt from my ${derby.source}. there was an example in section 3.2 (Creating ant.properties) showing how to set j14lib which was like....<br> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; j14lib=C:\j2sdk1.4.2_10\jre\lib (for windows machine)<br><br><br>but if you do so....you will get error.....<br><br><br>[setJdkProperties] java.vendor = Sun Microsystems Inc.<br>[setJdkProperties] java.home = C:\java5\jdk1.5.0\jre<br> [setJdkProperties] java.version = 1.5.0<br>[setJdkProperties] <a href="http://os.name" target="_blank">os.name</a> = Windows XP<br><b>[setJdkProperties] j14lib = C:j2sdk1.4.2_10jrelib<br>[setJdkProperties] j15lib = C:java5jre1.5.0lib</b><br> [setJdkProperties]<br>[setJdkProperties] Please consult BUILDING.txt for instructions on how to set the jdk-library and compiler-classpath properties.<br>[setJdkProperties]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.derbyPreBuild.PropertySetter.couldntSetProperty(PropertySetter.java:677)<br> [setJdkProperties]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.derbyPreBuild.PropertySetter.setClasspathFromLib(PropertySetter.java:484)<br>[setJdkProperties]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.derbyPreBuild.PropertySetter.execute(PropertySetter.java:207)<br>[setJdkProperties]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:288)<br> [setJdkProperties]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)<br>[setJdkProperties]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)<br>[setJdkProperties]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at java.lang.reflect.Method.invoke(Method.java:585)<br> [setJdkProperties]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:105)<br>[setJdkProperties]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.tools.ant.Task.perform(Task.java:348)<br>[setJdkProperties]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.tools.ant.Target.execute(Target.java:357)<br> <br><br><br>and if you do&nbsp;&nbsp;&nbsp;&nbsp;<b>&nbsp; j14lib=C:\\j2sdk1.4.2_10\\jre\\lib</b> (Double slash)<br>you won&#39;t get this error......<br>I figured that out in minutes but may be this could consume time of some other newbie like me<br> has anyone else noticed this in BUILDING.txt<br><br><br> regards,<br><font color="#888888">udai<br><br><br> </font></div><br> ------=_Part_14965_31868331.1207031564460-- From derbyschneiderjacob@example.net Tue Apr 01 19:14:29 2008 Return-Path: <derbyschneiderjacob@example.net> Delivered-To: schneiderjacob@example.net Received: (qmail 71576 invoked from network); 1 Apr 2008 19:14:29 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (83.161.120.155) by minotaur.apache.org with SMTP; 1 Apr 2008 19:14:29 -0000 Received: (qmail 72145 invoked by uid 500); 1 Apr 2008 19:14:28 -0000 Delivered-To: schneiderjacob@example.net Received: (qmail 71813 invoked by uid 500); 1 Apr 2008 19:14:27 -0000 Mailing-List: contact schneiderjacob@example.net; run by ezmlm Precedence: bulk list-help: <mailto:schneiderjacob@example.net> list-unsubscribe: <mailto:schneiderjacob@example.net> List-Post: <mailto:schneiderjacob@example.net> List-Id: <derby-user.db.apache.org> Reply-To: "Derby Discussion" <schneiderjacob@example.net> Delivered-To: mailing list schneiderjacob@example.net Received: (qmail 71802 invoked by uid 99); 1 Apr 2008 19:14:27 -0000 Received: from athena.apache.org (HELO athena.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 01 Apr 2008 12:14:27 -0700 X-ASF-Spam-Status: No, hits=-1.0 required=10.0 tests=RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: local policy) Received: from [83.161.120.155] (HELO gmp-eb-inf-2.sun.com) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 01 Apr 2008 19:13:44 +0000 Received: from fe-emea-10.sun.com (gmp-eb-lb-2-fe2.eu.sun.com [83.161.120.155]) by gmp-eb-inf-2.sun.com (8.13.7+Sun/8.12.9) with ESMTP id m31JDoBj000849 for <schneiderjacob@example.net>; Tue, 1 Apr 2008 19:13:54 GMT Received: from conversion-daemon.fe-emea-10.sun.com by fe-emea-10.sun.com (Sun Java System Messaging Server 6.2-8.04 (built Feb 28 2007)) id <schneiderjacob@example.net> (original mail from schneiderjacob@example.net) for schneiderjacob@example.net; Tue, 01 Apr 2008 20:13:50 +0100 (BST) Received: from [83.161.120.155] by fe-emea-10.sun.com (Sun Java System Messaging Server 6.2-8.04 (built Feb 28 2007)) with ESMTPSA id <schneiderjacob@example.net> for schneiderjacob@example.net; Tue, 01 Apr 2008 20:13:50 +0100 (BST) Date: Tue, 01 Apr 2008 21:13:49 +0200 From: Kristian Waagan <schneiderjacob@example.net> Subject: Re: derby.storage.pageCacheSize on multiple databases In-reply-to: <schneiderjacob@example.net> Sender: schneiderjacob@example.net To: Derby Discussion <schneiderjacob@example.net> Message-id: <schneiderjacob@example.net> Organization: Sun Microsystems Inc. MIME-version: 1.0 Content-type: text/plain; format=flowed; charset=ISO-8859-1 Content-transfer-encoding: 7BIT References: <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> User-Agent: Mozilla/5.0 (X11; U; SunOS i86pc; no-NO; rv:83.161.120.155) Gecko/20080228 Thunderbird/83.161.120.155 Mnenhy/83.161.120.155 X-Virus-Checked: Checked by ClamAV on apache.org Guillermo Averboch wrote: > Knut, > > Actually, it does not work. If you set derby.storage.pageCacheSize after > loading a database, the setting affects that database too. In the end, the last > setting affects all databases. Hello Guillermo, I tried the approach suggested by Knut Anders, and it seemed to work for me when I tested it on trunk. I added a trace print in the cache, and could see that it got initialized with different values. As the maximum size is kept in a final variable, the only way it can change is if the database is shutdown and rebooted. Now, this is only valid for the new cache implementation, but I would believe the approach should work for the old one too. So, a few question that might help to figure out what is going on: a) Are you sure the databases are not rebooted after the initial boot? b) Are you sure you set the system property in the VM process that boots the databases? c) Which driver are you using? I assume you have written a Java class that boots the databases for you and possibly starts the network server? regards, -- Kristian > > I learned this the hard way, after everybody started complaining about how > awfully slow the system was... > > Guillermo > > > From derbyschneiderjacob@example.net Wed Apr 02 05:19:20 2008 Return-Path: <derbyschneiderjacob@example.net> Delivered-To: schneiderjacob@example.net Received: (qmail 18206 invoked from network); 2 Apr 2008 05:19:19 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (83.161.120.155) by minotaur.apache.org with SMTP; 2 Apr 2008 05:19:19 -0000 Received: (qmail 21769 invoked by uid 500); 2 Apr 2008 05:19:18 -0000 Delivered-To: schneiderjacob@example.net Received: (qmail 21733 invoked by uid 500); 2 Apr 2008 05:19:18 -0000 Mailing-List: contact schneiderjacob@example.net; run by ezmlm Precedence: bulk list-help: <mailto:schneiderjacob@example.net> list-unsubscribe: <mailto:schneiderjacob@example.net> List-Post: <mailto:schneiderjacob@example.net> List-Id: <derby-user.db.apache.org> Reply-To: "Derby Discussion" <schneiderjacob@example.net> Delivered-To: mailing list schneiderjacob@example.net Received: (qmail 21722 invoked by uid 99); 2 Apr 2008 05:19:18 -0000 Received: from nike.apache.org (HELO nike.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 01 Apr 2008 22:19:18 -0700 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: local policy) Received: from [83.161.120.155] (HELO web1.nuix.com.au) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 02 Apr 2008 05:18:24 +0000 Received: from [83.161.120.155] (unknown [83.161.120.155]) by web1.nuix.com.au (Postfix) with ESMTP id EB0B21DF101 for <schneiderjacob@example.net>; Wed, 2 Apr 2008 15:17:56 +1000 (EST) Message-ID: <schneiderjacob@example.net> Date: Wed, 02 Apr 2008 16:19:02 +1100 From: David Sitsky <schneiderjacob@example.net> Organization: NUIX Pty Ltd User-Agent: Thunderbird 83.161.120.155 (Windows/20080213) MIME-Version: 1.0 To: Derby Discussion <schneiderjacob@example.net> Subject: Re: ERROR XSDG2: Invalid checksum on Page Page(0,Container(0, 1313)) References: <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> In-Reply-To: <schneiderjacob@example.net> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Checked: Checked by ClamAV on apache.org > Hi Dave - > The good news is that it looks like you do not have to recover this > database from backups because: > 1) since you can boot the database using IJ the corruption is not in > the transaction log (I assume you did not directly modify or delete any > files in the database directory tree in order to bypass recovery - *yes, > I have seen people do this*) Correct. > 2) since the corruption is in an index you should be able to drop and > recreate the index thus restoring the index without any data loss. > > I also have an idea that might get around it. Since the problem is with > an index and during a data load (I am assuming the load is a one time > database initialization streaming lots of data?) then I would try Yes. > creating the index AFTER loading the data rather than declaring the > index in the table Create statement. If the problem involves handling a > large number of database pages and the index pages at the same time > separating the activities will avoid it. Doing this also insures that > index statistics are created (for more info on this see: > http://wiki.apache.org/db-derby/CheckingForIndexStatistics) Ok - we are restructuring the application, so that now, we can get away with a primary key field of just an INTEGER, rather than the two BIGINT columns. Also, because of the restructure, we don't need to check for the existence of a duplicate record before insertion, so we can now create the primary key once all of the data has been loaded. I'm in the process of merging these changes in, and will try to do some more big loads to see if this has made the problem go away. I'll let you know how I go in the next few days. > As for the cause of the problem I can't be sure but since this happens > repeatedly and with different physical files it sounds like the problem > is not with the physical disk surface (I think the RAID architecture > helps protect against this type of failure anyway). For these tests - I am just running software "scary raid" (raid-0), but the OS doesn't report any disk failures. > I'd like to see this problem captured in an JIRA entry. You have > provided a lot of good information here and the results of performing a > load then creating the Primary Key will narrow it down more. My > gut-level feel is that reproducing this will be hard without an > environment similar to yours. With the multiple processors and the RAID > array I imagine it is a pretty fast machine and this may be exposing a > thread synchronization issue in Derby (or not). Having your information > and stack traces may give the stores folks enough information to perform > a code inspection for this or other issues. Would you file a JIRA > describing your processing, the machine environment, the JVM and attach > your derby log files? Will do - once I have more information from the load with the modified application, I'll create the JIRA entry with all the relevant information. Thanks so much for your advice. -- Cheers, David Nuix Pty Ltd Suite 79, 89 Jones St, Ultimo NSW 2007, Australia Ph: +1-325-387-2166 Web: http://www.nuix.com Fax: +1-325-387-2166 From derbyschneiderjacob@example.net Wed Apr 02 08:15:11 2008 Return-Path: <derbyschneiderjacob@example.net> Delivered-To: schneiderjacob@example.net Received: (qmail 7499 invoked from network); 2 Apr 2008 08:15:11 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (83.161.120.155) by minotaur.apache.org with SMTP; 2 Apr 2008 08:15:11 -0000 Received: (qmail 2658 invoked by uid 500); 2 Apr 2008 08:15:10 -0000 Delivered-To: schneiderjacob@example.net Received: (qmail 2626 invoked by uid 500); 2 Apr 2008 08:15:10 -0000 Mailing-List: contact schneiderjacob@example.net; run by ezmlm Precedence: bulk list-help: <mailto:schneiderjacob@example.net> list-unsubscribe: <mailto:schneiderjacob@example.net> List-Post: <mailto:schneiderjacob@example.net> List-Id: <derby-user.db.apache.org> Reply-To: "Derby Discussion" <schneiderjacob@example.net> Delivered-To: mailing list schneiderjacob@example.net Received: (qmail 2615 invoked by uid 99); 2 Apr 2008 08:15:10 -0000 Received: from nike.apache.org (HELO nike.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 02 Apr 2008 01:15:10 -0700 X-ASF-Spam-Status: No, hits=-1.0 required=10.0 tests=RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: local policy) Received: from [83.161.120.155] (HELO brmea-mail-1.sun.com) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 02 Apr 2008 08:14:18 +0000 Received: from dm-norway-02.uk.sun.com ([83.161.120.155]) by brmea-mail-1.sun.com (8.13.6+Sun/8.12.9) with ESMTP id m328EbYs016651 for <schneiderjacob@example.net>; Wed, 2 Apr 2008 08:14:37 GMT Received: from clustra.norway.sun.com (clustra.Norway.Sun.COM [83.161.120.155]) by dm-norway-02.uk.sun.com (8.13.6+Sun/8.13.6/ENSMAIL,v2.2) with SMTP id m328Ea8O006520 for <schneiderjacob@example.net>; Wed, 2 Apr 2008 09:14:37 +0100 (BST) Received: (qmail 5730 invoked from network); 2 Apr 2008 08:14:36 -0000 Received: from khepri35.norway.sun.com (83.161.120.155) by clustra.norway.sun.com with QMQP; 2 Apr 2008 08:14:36 -0000 Date: Wed, 2 Apr 2008 10:14:36 +0200 From: "Bernt M. Johnsen" <schneiderjacob@example.net> To: Derby Discussion <schneiderjacob@example.net> Subject: Re: Update Substring of a field Message-ID: <schneiderjacob@example.net> References: <schneiderjacob@example.net> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="9UV9rz0O2dU/yYYn" Content-Disposition: inline In-Reply-To: <schneiderjacob@example.net> Organization: Sun Microsystems User-Agent: Mutt/1.5.7i X-Virus-Checked: Checked by ClamAV on apache.org --9UV9rz0O2dU/yYYn Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable >>>>>>>>>>>> Reginald Johnson wrote (2008-03-30 00:48:33): > Is it possible to update the substring of a field? I'm trying to do > something like this: >=20 > UPDATE tblIntervals SET SessionMouseID =3D 'ggg', substr(SessionID, 0, 3= ) =3D > 'ggg' WHERE SessionMouseID =3D 'bbb' Neither Derby, nor the SQL standard supports a substring expression on the left side of the assignment. Your options is to eiher do it in java through JDBC, or modify your schema to store SessionMouseID and e.g. SessionIDRest and do a concatenation each time you need a SessionID. Don't know if the latter is feasible in your case, though. --=20 Bernt Marius Johnsen, Database Technology Group,=20 Staff Engineer, Derby/Java DB Sun Microsystems, Trondheim, Norway --9UV9rz0O2dU/yYYn Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.8 (SunOS) iD8DBQFH80BslFBD9TXBAPARAveLAKCUJeYr4eP4SopddgJpq7c/8p3LfQCdHzRt RVg2TeB7Pr/cgbJo9QCFwNs= =IFCa -----END PGP SIGNATURE----- --9UV9rz0O2dU/yYYn-- From derbyschneiderjacob@example.net Wed Apr 02 09:50:10 2008 Return-Path: <derbyschneiderjacob@example.net> Delivered-To: schneiderjacob@example.net Received: (qmail 54565 invoked from network); 2 Apr 2008 09:50:10 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (83.161.120.155) by minotaur.apache.org with SMTP; 2 Apr 2008 09:50:10 -0000 Received: (qmail 79317 invoked by uid 500); 2 Apr 2008 09:50:09 -0000 Delivered-To: schneiderjacob@example.net Received: (qmail 79294 invoked by uid 500); 2 Apr 2008 09:50:09 -0000 Mailing-List: contact schneiderjacob@example.net; run by ezmlm Precedence: bulk list-help: <mailto:schneiderjacob@example.net> list-unsubscribe: <mailto:schneiderjacob@example.net> List-Post: <mailto:schneiderjacob@example.net> List-Id: <derby-user.db.apache.org> Reply-To: "Derby Discussion" <schneiderjacob@example.net> Delivered-To: mailing list schneiderjacob@example.net Received: (qmail 79283 invoked by uid 99); 2 Apr 2008 09:50:09 -0000 Received: from nike.apache.org (HELO nike.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 02 Apr 2008 02:50:09 -0700 X-ASF-Spam-Status: No, hits=-1.0 required=10.0 tests=RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: local policy) Received: from [83.161.120.155] (HELO gmp-eb-inf-2.sun.com) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 02 Apr 2008 09:49:17 +0000 Received: from fe-emea-09.sun.com (gmp-eb-lb-2-fe2.eu.sun.com [83.161.120.155]) by gmp-eb-inf-2.sun.com (8.13.7+Sun/8.12.9) with ESMTP id m329nZRe009898 for <schneiderjacob@example.net>; Wed, 2 Apr 2008 09:49:35 GMT Received: from conversion-daemon.fe-emea-09.sun.com by fe-emea-09.sun.com (Sun Java System Messaging Server 6.2-8.04 (built Feb 28 2007)) id <schneiderjacob@example.net> (original mail from schneiderjacob@example.net) for schneiderjacob@example.net; Wed, 02 Apr 2008 10:49:35 +0100 (BST) Received: from localhost ([83.161.120.155]) by fe-emea-09.sun.com (Sun Java System Messaging Server 6.2-8.04 (built Feb 28 2007)) with ESMTPSA id <schneiderjacob@example.net> for schneiderjacob@example.net; Wed, 02 Apr 2008 10:49:33 +0100 (BST) Date: Wed, 02 Apr 2008 11:49:32 +0200 From: Knut Anders Hatlen <schneiderjacob@example.net> Subject: Re: Update Substring of a field In-reply-to: <schneiderjacob@example.net> Sender: schneiderjacob@example.net To: Derby Discussion <schneiderjacob@example.net> Message-id: <schneiderjacob@example.net> Organization: Sun Microsystems MIME-version: 1.0 Content-type: text/plain; charset=us-ascii Content-transfer-encoding: 7BIT References: <schneiderjacob@example.net> <schneiderjacob@example.net> User-Agent: Gnus/5.110007 (No Gnus v0.7) Emacs/22.1 (usg-unix-v) X-Virus-Checked: Checked by ClamAV on apache.org "Bernt M. Johnsen" <schneiderjacob@example.net> writes: >>>>>>>>>>>>> Reginald Johnson wrote (2008-03-30 00:48:33): >> Is it possible to update the substring of a field? I'm trying to do >> something like this: >> >> UPDATE tblIntervals SET SessionMouseID = 'ggg', substr(SessionID, 0, 3) = >> 'ggg' WHERE SessionMouseID = 'bbb' > > Neither Derby, nor the SQL standard supports a substring expression on > the left side of the assignment. > > Your options is to eiher do it in java through JDBC, or modify your > schema to store SessionMouseID and e.g. SessionIDRest and do a > concatenation each time you need a SessionID. Don't know if the latter > is feasible in your case, though. You can also keep the schema as it is and use a combination of SUBSTR and concatenation in the UPDATE statement. UPDATE tblIntervals SET SessionMouseID = 'ggg', SessionID = 'ggg' || SUBSTR(4, LENGTH(SessionId) - 3) WHERE SessionMouseId = 'bbb' -- Knut Anders From derbyschneiderjacob@example.net Thu Apr 03 05:40:19 2008 Return-Path: <derbyschneiderjacob@example.net> Delivered-To: schneiderjacob@example.net Received: (qmail 10609 invoked from network); 3 Apr 2008 05:40:19 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (83.161.120.155) by minotaur.apache.org with SMTP; 3 Apr 2008 05:40:19 -0000 Received: (qmail 77818 invoked by uid 500); 3 Apr 2008 05:40:18 -0000 Delivered-To: schneiderjacob@example.net Received: (qmail 77773 invoked by uid 500); 3 Apr 2008 05:40:17 -0000 Mailing-List: contact schneiderjacob@example.net; run by ezmlm Precedence: bulk list-help: <mailto:schneiderjacob@example.net> list-unsubscribe: <mailto:schneiderjacob@example.net> List-Post: <mailto:schneiderjacob@example.net> List-Id: <derby-user.db.apache.org> Reply-To: "Derby Discussion" <schneiderjacob@example.net> Delivered-To: mailing list schneiderjacob@example.net Received: (qmail 77762 invoked by uid 99); 3 Apr 2008 05:40:17 -0000 Received: from nike.apache.org (HELO nike.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 02 Apr 2008 22:40:17 -0700 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: local policy) Received: from [83.161.120.155] (HELO web1.nuix.com.au) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 03 Apr 2008 05:39:25 +0000 Received: from [83.161.120.155] (unknown [83.161.120.155]) by web1.nuix.com.au (Postfix) with ESMTP id E08B41DF101 for <schneiderjacob@example.net>; Thu, 3 Apr 2008 15:38:58 +1000 (EST) Message-ID: <schneiderjacob@example.net> Date: Thu, 03 Apr 2008 16:40:08 +1100 From: David Sitsky <schneiderjacob@example.net> Organization: NUIX Pty Ltd User-Agent: Thunderbird 83.161.120.155 (Windows/20080213) MIME-Version: 1.0 To: Derby Discussion <schneiderjacob@example.net> Subject: Re: ERROR XSDG2: Invalid checksum on Page Page(0,Container(0, 1313)) References: <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> <schneiderjacob@example.net> In-Reply-To: <schneiderjacob@example.net> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Checked: Checked by ClamAV on apache.org > Ok - we are restructuring the application, so that now, we can get away > with a primary key field of just an INTEGER, rather than the two BIGINT > columns. Also, because of the restructure, we don't need to check for > the existence of a duplicate record before insertion, so we can now > create the primary key once all of the data has been loaded. As expected, our loading doesn't exhibit the problem now with the above change. Incidentally, we had a customer being hit with this problem yesterday, on their own hardware, which was an AMD64 dual core 4200 with 4 GB of ram running 32 bit XP pro. I will create the JIRA entry in the next day or two - the fact this has now been seen on an external site on completely different hardware / OS shows we are probably dealing with a software issue. -- Cheers, David Nuix Pty Ltd Suite 79, 89 Jones St, Ultimo NSW 2007, Australia Ph: +1-325-387-2166 Web: http://www.nuix.com Fax: +1-325-387-2166 From derbyschneiderjacob@example.net Fri Apr 04 06:31:59 2008 Return-Path: <derbyschneiderjacob@example.net> Delivered-To: schneiderjacob@example.net Received: (qmail 76507 invoked from network); 4 Apr 2008 06:31:59 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (83.161.120.155) by minotaur.apache.org with SMTP; 4 Apr 2008 06:31:59 -0000 Received: (qmail 42971 invoked by uid 500); 4 Apr 2008 06:31:57 -0000 Delivered-To: schneiderjacob@example.net Received: (qmail 42943 invoked by uid 500); 4 Apr 2008 06:31:57 -0000 Mailing-List: contact schneiderjacob@example.net; run by ezmlm Precedence: bulk list-help: <mailto:schneiderjacob@example.net> list-unsubscribe: <mailto:schneiderjacob@example.net> List-Post: <mailto:schneiderjacob@example.net> List-Id: <derby-user.db.apache.org> Reply-To: "Derby Discussion" <schneiderjacob@example.net> Delivered-To: mailing list schneiderjacob@example.net Received: (qmail 42928 invoked by uid 99); 4 Apr 2008 06:31:57 -0000 Received: from nike.apache.org (HELO nike.apache.org) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 03 Apr 2008 23:31:57 -0700 X-ASF-Spam-Status: No, hits=3.7 required=10.0 tests=DNS_FROM_OPENWHOIS,FORGED_HOTMAIL_RCVD2,SPF_HELO_PASS,SPF_PASS,WHOIS_MYPRIVREG X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of schneiderjacob@example.net designates 83.161.120.155 as permitted sender) Received: from [83.161.120.155] (HELO kuber.nabble.com) (83.161.120.155) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 04 Apr 2008 06:31:05 +0000 Received: from isper.nabble.com ([83.161.120.155]) by kuber.nabble.com with esmtp (Exim 4.63) (envelope-from <schneiderjacob@example.net>) id 1JhfSP-0005Me-Fn for schneiderjacob@example.net; Thu, 03 Apr 2008 23:31:25 -0700 Message-ID: <schneiderjacob@example.net> Date: Thu, 3 Apr 2008 23:31:25 -0700 (PDT) From: rico16135 <schneiderjacob@example.net> To: schneiderjacob@example.net Subject: Stored Procedures (what is it really used for?) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Nabble-From: schneiderjacob@example.net X-Virus-Checked: Checked by ClamAV on apache.org So from a tutorial, I successfully created a stored procedure, stored it, and made code to use it. I think. If I run my code, it seems to work fine, regardless of whether or not I have entered that stored procedure into the database. Is my code suppose to work regardless? Is storing a procedure just meant to make my code, which repeats quite often, more efficient? Or are stored procedures created for different reasons? Are they there to let me run my java code from an sql query? I'm at my wits end trying to understand the significance of a stored procedure. I've posted my code, if it helps. public static void callSortTwo(Connection conn, CallList cl) { try { Timestamp ts = new Timestamp(System.currentTimeMillis() ); System.out.println("Executed at: " + ts); String insert_query = "INSERT INTO SWEATY_BALLSAK.CALLS (" + "beat," + "call_descr," + "priority," + "date," + "time," + "call_num," + "location," + "status," + "entry_id) " + "VALUES(?,?,?,?,?,?,?,?,?)"; PreparedStatement pstmt = conn.prepareStatement(insert_query); for (int counter = 0; counter < cl.getSize(); counter++) { String current_event[] = cl.getElementArray(counter); pstmt.setString(1,current_event[0]); pstmt.setString(2,current_event[1]); pstmt.setString(3,current_event[2]); pstmt.setString(4,current_event[3]); pstmt.setString(5,current_event[4]); pstmt.setString(6,current_event[5]); pstmt.setString(7,current_event[6]); pstmt.setString(8,current_event[7]); pstmt.setTimestamp(9, ts); pstmt.executeUpdate(); } } catch(SQLException sqle_callSort) { System.out.println("SQL Exception" + sqle_callSort.getMessage() ); } -- View this message in context: http://www.nabble.com/Stored-Procedures-%28what-is-it-really-used-for-%29-tp16485873p16485873.html Sent from the Apache Derby Users mailing list schneiderjacob@example.net. From derbyschneiderjacob@example.net Fri Apr 04 10:28:58 2008 Return-Path: <derbyschneiderjacob@example.net> Delivered-To: schneiderjacob@example.net Received: (qmail 55614 invoked from network); 4 Apr 2008 10:28:58 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (83.161.120.155) by minotaur.apache.org with SMTP; 4 Apr 2008 10:28:58 -0000 Received: (qmail 64918 invoked by uid 500); 4 Apr 2008 10:28:56 -0000 Delivered-To: schneiderjacob@example.net Received: (qmail 64877 invoked by uid 500); 4 Apr 2008 10:28:56 -0000 Mailing-List: contact schneiderjacob@example.net; run by ezmlm Precedence: bulk list-help: <mailto:schneiderjacob@example.net> list-unsubscribe: <mailto:schneiderjacob@example.net> List-Post: <mailto:schneiderjacob@example.net> List-Id: <derby-user.db.apache.org> Reply-To: "Derby Discussion" <schneiderjacob@example.net> Delivered-To: mailing list schneiderjacob@example.net Delivered-To: moderator for schneiderjacob@example.net Received: (qmail 70227 invoked by uid 99); 4 Apr 2008 06:57:56 -0000 X-ASF-Spam-Status: No, hits=2.8 required=10.0 tests=HTML_MESSAGE,SPF_SOFTFAIL,WHOIS_MYPRIVREG X-Spam-Check-By: apache.org Received-SPF: softfail (athena.apache.org: transitioning domain of schneiderjacob@example.net does not designate 83.161.120.155 as permitted sender) Message-ID: <schneiderjacob@example.net> Date: Fri, 4 Apr 2008 07:57:21 +0100 From: "Tiago Espinha" <schneiderjacob@example.net> To: "Derby Discussion" <schneiderjacob@example.net> Subject: Re: Stored Procedures (what is it really used for?) In-Reply-To: <schneiderjacob@example.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_4089_16968153.1207292242026" References: <schneiderjacob@example.net> X-Virus-Checked: Checked by ClamAV on apache.org ------=_Part_4089_16968153.1207292242026 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Well, to understand the relevance and the need for procedures you pretty much have to backtrace it to the why we encapsulate code in methods in the first place. For instance, in procedural programming, say in C, you could pretty much just write a big bunch of code in the main() function, but you don't, or you shouldn't. We don't because it's messy and because it's very hard to read. This is pretty much the same, if in Java you can, say, add a customer to the database by invoking a remote procedure that takes you one line of code, then that proves as an advantage over having like 10 lines of code required to do the same task, should you not have it in a procedure. In the end it may also decrease network overhead if that's of any concern to you, as if there's the need to work with big blocks of data, these won't be getting bounced between the client and the DB server, everything will happen server-side. On the other hand there's the issue of server-load. And perhaps that's the reason why procedures aren't a panacea for all your problems. Since the procedure will be running server-side, you are basically shifting some of the CPU load that would have belonged to the client otherwise. Now, as long as there's just one client, this isn't relevant, but if you have 10000 clients hammering the server by invoking a wide-range of cumbersome procedures, then you might have a problem. Hope this helped, Tiago On Fri, Apr 4, 2008 at 7:31 AM, rico16135 <schneiderjacob@example.net> wrote: > > So from a tutorial, I successfully created a stored procedure, stored it, > and > made code to use it. I think. > If I run my code, it seems to work fine, regardless of whether or not I > have > entered that stored procedure into the database. > > Is my code suppose to work regardless? Is storing a procedure just meant > to > make my code, which repeats quite often, more efficient? Or are stored > procedures created for different reasons? Are they there to let me run my > java code from an sql query? I'm at my wits end trying to understand the > significance of a stored procedure. I've posted my code, if it helps. > > > public static void callSortTwo(Connection conn, CallList cl) { > try { > Timestamp ts = new Timestamp(System.currentTimeMillis() ); > System.out.println("Executed at: " + ts); > String insert_query = "INSERT INTO SWEATY_BALLSAK.CALLS (" + > "beat," + > "call_descr," + > "priority," + > "date," + > "time," + > "call_num," + > "location," + > "status," + > "entry_id) " + > "VALUES(?,?,?,?,?,?,?,?,?)"; > > PreparedStatement pstmt = conn.prepareStatement(insert_query); > > for (int counter = 0; counter < cl.getSize(); counter++) { > String current_event[] = cl.getElementArray(counter); > > pstmt.setString(1,current_event[0]); > pstmt.setString(2,current_event[1]); > pstmt.setString(3,current_event[2]); > pstmt.setString(4,current_event[3]); > pstmt.setString(5,current_event[4]); > pstmt.setString(6,current_event[5]); > pstmt.setString(7,current_event[6]); > pstmt.setString(8,current_event[7]); > pstmt.setTimestamp(9, ts); > > pstmt.executeUpdate(); > } > } > > catch(SQLException sqle_callSort) { > System.out.println("SQL Exception" + sqle_callSort.getMessage() > ); > } > -- > View this message in context: > http://www.nabble.com/Stored-Procedures-%28what-is-it-really-used-for-%29-tp16485873p16485873.html > Sent from the Apache Derby Users mailing list schneiderjacob@example.net. > > ------=_Part_4089_16968153.1207292242026 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding:
{'source': 'Apache Software Foundation Public Mail Archives'}