Re: Bug - Endless loop in get sequece value
2013-10-11 07:33:05,767 [main ] DEBUG factory.support.DefaultListableBeanFactoryReturning cached instance of singleton bean 'org.springframework.transaction.interceptor.TransactionInterceptor#0' 2013-10-11 07:33:05,907 [main ] DEBUG org.hibernate.SQLvalues next value for SEQ_USER 2013-10-11 07:33:06,204 [main ] DEBUG hibernate.id.SequenceGeneratorSequence identifier generated: BasicHolder[java.lang.Long[-2147483648]] Try defining derby.language.logStatementText=true http://db.apache.org/derby/docs/10.10/ref/rrefproper43517.html Then look in your derby.log, to see what sort of SQL statements are being generated and executed by Hibernate. Then you will have more information to ask the Hibernate community about why it is doing that. thanks, bryan
Re: Thread-safe shutdown while other threads might try to reopen
I know I can just slap a synchronized block around these two methods to make it bulletproof, but there are two problems with this: (1) synchronized is slow and Derby's shutdown is not fast at all... (2) I don't know what other apps might be open in the same JVM at the same. I'm not sure you can avoid the performance cost, given the needs of your application. I'm always more comfortable getting the correct behavior first, then dealing with the implications once it's reliably running. I think you may have to do slightly more than just make two methods synchronized; it isn't exactly clear to me which two methods you mean. I might try: - make the Database class have two public methods Open and Close. - in Open, call DriverManager.getConnection and increment your in-use count - in Close, close the connection, decrement your in-use count, and shutdown the database when it goes to zero - make Open and Close synchronized Regarding other apps in the same JVM, is that truly a concern? I'm not sure how your app is packaged, but it seems unlikely to me that some other arbitrary body of Java code is going to try to open one of your own databases. How would that code even know what connection URL to use? thanks, bryan
Re: database connectivity error
C:\Users\HeartBeat\Desktopjava org.apache.derby.tools.sysinfo - Derby Information [C:\Users\HeartBeat\Desktop\Derby\lib\derby.jar] 10.10.1.1 - (1458268) [C:\Users\HeartBeat\Desktop\Derby\lib\derbytools.jar] 10.10.1.1 - (1458268) -- Good. but still when i run SimpleApp.java it gives error SimpleApp starting in embedded mode Unable to load the JDBC driver org.apache.derby.jdbc.EmbeddedDriver Please check your CLASSPATH. java.lang.ClassNotFoundException: org.apache.derby.jdbc.EmbeddedDriver What command did you use to run SimpleApp? And did you run that command immediately after you ran java org.apache.derby.tools.sysinfo from the very same CMD.EXE window? bryan
Re: Problem with Class.forName
CLASSPATH: .;C:\Program Files (x86)\Java\jre7\lib\ext\QTJava.zip;C:\Program Files\Java\jdk1.7.0_25\db\lib;C:\Program Files\Java\jdk1.7.0_25\bin;C:\Program Files\Java\jre7\bin Don't name the *directories* here, name the actual *jar files*. As in: set CLASSPATH=C:\Program Files (x86)\Java\jre7\lib\ext\QTJava.zip;C:\Program Files\Java\jdk1.7.0_25\db\lib\derbyclient.jar You can either list derbyclient.jar, or you can list derbyrun.jar. thanks, bryan
Re: Problem with Class.forName
C:\Users\Docecho %CLASSPATH% .;C:\Program Files (x86)\Java\jre7\lib\ext\QTJava.zip;C:\Program Files\Java\jdk1.7.0_25\db\lib\derbyrun.jar;C:\Program Files\Java\jdk1.7.0_25\bin;C:\Program Files\Java\jre7\bin;C:\Program Files\Java\jdk1.7.0_25\db\lib\derbyclient.jar After making the changes and verifying the changes to CLASSPATH, I ran the program. With that same CLASSPATH, try running java org.apache.derby.tools.sysinfo and paste the output into email for us. thanks, bryan
Re: Problem with Class.forName
C:\Users\Docjava org.apache.derby.tools.sysinfo - Derby Information JRE - JDBC: Java SE 7 - JDBC 4.0 [C:\Program Files\Java\jdk1.7.0_25\db\lib\derby.jar] 10.8.2.2 - (1181258) [C:\Program Files\Java\jdk1.7.0_25\db\lib\derbytools.jar] 10.8.2.2 - (1181258) [C:\Program Files\Java\jdk1.7.0_25\db\lib\derbynet.jar] 10.8.2.2 - (1181258) [C:\Program Files\Java\jdk1.7.0_25\db\lib\derbyclient.jar] 10.8.2.2 - (1181258) This looks pretty good. And I assume that, with this classpath, you can run java org.apache.derby.tools.ij without problems as well? (If you get the ij prompt, just type quit;) If you're getting this far, this means that your CLASSPATH is fine, as far as Derby is concerned. What exact exception are you getting? In your catch block, add e.printStackTrace(); and paste the output back to us. thanks, bryan
Re: Problem with Class.forName
Here's the output: java.lang.ClassNotFoundException: org.apache.derby.jdbc.ClientDriver Hmmm... It sure seems like a classpath problem, but you can find the class when you run simple utilities like ij or sysinfo. Perhaps the classpath that your application is running with is somehow different, or perhaps something else is interfering with loading the class. You could try something like this: http://www.mkyong.com/java/how-to-print-out-the-current-project-classpath/ to have your application dump the classpath to see if it's what you expect it to be. Two more things to try: 1) Run 'java org.apache.derby.tools.ij', then do connect 'jdbc:derby://localhost/C:/Users/Doc/Documents/JavaDB/Learning/Users'; That will at least prove 100% that you can connect to your database using the ClientDriver, from ij. 2) Run your test program with 'java -verbose:class', which might give you some clues about where it's looking for classes. Here's a web page with other things you could try: http://myarch.com/classnotfound/ One other thing occurs to me: I know that sometimes there can be multiple copies of Derby installed on the system, and if the multiple copies are of multiple different versions, that can disrupt the class loading. So if you have more than one copy of Derby on your system, make sure that your CLASSPATH is as narrow as possible and only includes the Derby that you intend to run. bryan
Re: Performance Improvement on Aggregate Functions using histogram stats
On 8/22/2013 1:31 AM, Ayesha Dissanayaka wrote: I am a newbie to derby and I would like to try out improve performances of aggregate functions Hi, and welcome to Derby! In the area of improvements to aggregate functions in Derby, you could consider working on the implementation of window functions in Derby. Window functions are part of modern standard SQL, are well-defined by the SQL standard, and there are other open source implementations of window functions that you can study and learn from, such as: https://www.pgcon.org/2009/schedule/attachments/98_Windowing%20Functions.pdf thanks, bryan
Re: Create new schema with tables of existing schema.
I need script to create new schema app2 with the exact replica of app1 schema Have you investigated dblook? http://db.apache.org/derby/docs/10.10/tools/ctoolsdblook.html thanks, bryan
Re: Is there some way to shut down a Derby database faster?
Exiting the VM isn't really an option for me anyway, this is just when someone is closing the database, potentially planning to open another one. Another question along the same lines then - if I kick off the shutdown in another thread, what happens if the same database is then reopened while the shutdown is still occurring? It sounds like users are routinely arriving, doing work, and disconnecting, causing the database to be opened and closed. Have you considered using a connection pool in between your application layer and the database, so that the connections are retained and re-used, rather than being fully reclaimed and fully reopened? thanks, bryan
Re: record is not inserting in the Derby DB.
i create one table in the already existing Derby by but table is creating successfully but unable to insert the record. What happens when you try to insert the record? Do you get an exception? What does the exception say? Here's how to read the exception information: http://wiki.apache.org/db-derby/UnwindExceptionChain thanks, bryan
Re: Peculiar sorting behaviour?
Hi John, Here's my perspective on what you posted: 1) Anytime you issue a SELECT statement with an ORDER BY, and the rows don't come back in that order, that's a bug. As you point out, it would be best if you could narrow this down to a simple reproducible case when you report it. However, even if you can't, it's worth reporting the problem, together with whatever information you have. For instance, the query plan information that you posted is very valuable and may give enough clues to allow somebody else to reproduce the problem. When you do report the problem, it would be nice if you could clearly indicate which query plan is for the run which gets the results in the wrong order, and which is for the run with the right order. For example, upload the query plans as separate attachments with names that clearly indicate which is which. 2) I agree with Knut Anders's hunch that this seems like a sort avoidance bug. Normally, the query plan for a query with an ORDER BY clause has to include an explicit sort of the results to get them in the right order. However, sometimes the optimizer can determine that an earlier node in the query plan has guaranteed that it already has the results in the correct order, in which case the sort is unnecessary and will not be performed. Such a node would be a INDEX SCAN node, and as Mike observed there do appear to be such INDEX SCAN nodes in the query plan. It looks like there are index scans on both the ITEM_USAGE and TESTS tables, but since the ORDER BY clause specifies tests.item,table the index scan for ITEM_USAGE is presumably not relevant. 3) I see that your query plan output includes: Scroll Insensitive ResultSet: Number of opens = 1 Rows seen = 30 Number of reads from hash table = 30 Number of writes to hash table = 30 This is interesting, and makes me think that maybe you have some special connection settings which are causing the query plan to take all of your results (which might have been correctly ordered), and throw them into an in-memory hash table, which then causes the rows to become randomly ordered. Do you by any chance specify ResultSet.TYPE_SCROLL_INSENSITIVE, when you create the statement object in your JDBC code? If so, does the behavior change if you change this setting? Hope this helps, bryan
Re: I failed to connect client and server Derby.
C:\Users\pocky\derby\db-derby-10.9.1.0-bin\DERBYTUTORjava -jar %DERBY_HOME%\lib\derbyrun.jar server start C:\Users\pocky\derby\db-derby-10.9.1.0-bin\DERBYTUTOR It is odd that you didn't see any output from the command: java server start Normally when I run that command there is output printed to my console. Also, it is odd that you got a command prompt back. Normally when I run that command, my terminal session is blocked, and I have to open a new command window to enter additional commands, as the first command window is busy running the server code. This makes me think that the basic problem here is that the server failed to start. But I don't know why it wouldn't emit any error messages in that case. Was there a derby.log file created in the directory where you started the server? thanks, bryan
Re: DERBY EMBEDDED IN NETBEANS PROGRAM
I am using netbeans. When I use embedded driver in netbeans i get errors, even though the derbyclient is in the library. If i add derbyclient.jar in the client it runs perfectly, even though the driver is: Class.forName(org.apache.derby.jdbc.EmbeddedDriver).newInstance(); The choice of the driver is also dependent on the syntax you provide in your connection URL. For an embedded Derby instance, use the EmbeddedDriver, include derby.jar in your classpath, and specify jdbc:derby:dbname as your connection URL. For a client-server Derby applicaty, use the ClientDriver, include derbyclient.jar in your classpath, and specify jdbc:derby://host:port/dbname as your connection URL. thanks, bryan
Re: DriverManager.getConnection error with JDK 7
I caught SQLException and he said that : No suitable driver found for jdbc:derby:MoneyBack1;create=true;user=miltone;password=password So your classloader can't find the derby driver. Have you checked that derby.jar is on your classpath? The sysinfo tool is useful for diagnosing classpath problems like these: http://db.apache.org/derby/docs/10.9/getstart/tgsrunningsysinfo.html thanks, bryan
Re: Derby 10.8.2.2, JTA on GlassFish 3.1.2.2 and very strange behavior
On 02/18/2013 07:40 AM, Wujek Srujek wrote: Hi. But why is there any local transaction? I haven't started any, I just set autoCommit to false There is always a transaction; Derby won't let you ever access the database without one. What auto-commit does is to automatically commit the transaction after each statement is executed. But any time you issue a statement against the database, if there isn't already a transaction begun, one is started for you. thanks, bryan
Re: Derby 10.8.2.2, JTA on GlassFish 3.1.2.2 and very strange behavior
One more question, though: the uncommitted insert comes up in the subsequent select - is this data coming from the server, from the active tx, or does the jdbc driver cache the data somehow? The results are coming from the server. The server shows you your own uncommitted updates, but won't show them to any other transaction until you commit or abort your transaction. To be precise, some of these behaviors are controllable: see the discussion of isolation in the docs: http://db.apache.org/derby/docs/10.9/devguide/cdevconcepts30291.html thanks, bryan
Re: Create embedded, run via network server: what's databaseName?
What I can't figure out is the name of the database the network client should connect to, aka the last element of the JDBC URL. The last element is basically the same: it is the path to the database, starting from the network server's derby.system.home location. So if you do: jdbc:derby:my/database/directory/dbname in embedded mode, then you should do: jdbc://my.host.name:/my/database/directory/dbname Here's a few more examples: http://db.apache.org/derby/docs/10.9/adminguide/radminappsclientxmp.html thanks, bryan
Re: Ideas for optimisation needed
The change in timing is spectacular: it now takes about 0.4 seconds Great news! I think it would be cool if you could write up a short summary of your findings and put it on the hints and tips section of the Derby community wiki. thanks, bryan
Re: Some questions about the Derby
Forwarding to the list. On 12/19/2012 04:10 AM, 王旭 wrote: *Hello Pendleton* ** *I am a loyal user of derby database from China. I found some problems in the course of use, so I need your help urgently.* ** *Derby Version**：**10.4* *Question one**：*** Firstly, I created a table. CREATE TABLE CV ( AGE INTEGER NOT NULL, RT DECIMAL(20,10), PRIMARY KEY (AGE) ) Then innserted data insert into CV VALUES (80，10729.50) Finally, an error occured when the SQL statement select?(95000.0*1000/1000)/1000.0?*?RT?from?CV?where?Age=80? was executed, it displayed the generated value has gone beyond the data type of DECIMAL / NUMERIC (31,25). Could you tell me how Derby do the precision control and operation on the data type of DECIMAL when it performs a variety of arithmetic operations including add, subtract, multiply and divide? *Question two**：*** Firstly, I created a table. CREATE TABLE CV ( AGE SMALLINT, RT DOUBLE, PRIMARY KEY (AGE) ) Then innserted data insert into CV VALUES (20，766.3) Finally, I got results of 268204.994 after executing the SQL statement select 35.0/1000 * RT from CV the where age = 20?, but the actual result should be 268205.0. Could you tell me whether the cause of problem is inaccurated data from a floating-point type double. *Let me know whether the above problems are the Derby Bug? Are there any other similar problems on the version in use? And how should we deal with?* *Look forward to your reply, thank you!* From Apollo.Wong
Re: Boolean in version 10.9
I've checked my computer and couldn't find another version of derby installed anywhere. The 'sysinfo' tool can be useful for figuring out which copy of Derby is getting run, and from what location: http://db.apache.org/derby/docs/10.9/tools/rtoolssysinfo41288.html thanks, bryan
Re: Vetting Derby. Technical documents?
My company is evaluating whether to use Derby for a desktop/client-server application where security and 21-CFR 11 compliance is important. Although Derby can be used perfectly well as a standalone database, it is also designed to be embedded into a containing application. The Derby libraries are small and stable, the memory footprint is predictable, and many other aspects of Derby favor this embedded usage. If you thus embed Derby into your application, you can arrange for little or none of the Derby APIs to be part of the exposed surface of your application, and therefore you can minimize your security exposure. For example, you say you are designing a client-server application; presumably Derby is to be part of the server component. But you do NOT need to make Derby be the *entire* server component. As an alternative design, you could implement your own completely locked-down web service, which provides just the APIs that your application client requires, in a highly-secure fashion, and embed Derby inside your secure web service. Hope these ideas give you some new perspectives to consider. thanks, bryan
Re: Introducing the derby-maven-plugin
while ago I knocked up a Maven plugin which can start Derby for you during the build (in the same VM) and be used by integration tests (which are not forked. of course). Hi Martin, You might want to add some information about your tool to the Uses of Derby section of the Derby wiki at http://wiki.apache.org/db-derby thanks, bryan
Re: derby (dead)lock exception
. What does U and X mean after the trans. Ids? The query is: DELETE FROM TRIP_TIMETABLE WHERE EXISTS( SELECT ID FROM TRIP WHERE (ID IN (?)) AND ID = TRIP_TIMETABLE.trips_ID ) What can be wrong with this query? I'm not exactly sure what's wrong, but I agree that they both appear to have gone after the same set of rows, but in different orders. I think that the '1,19' and '1,20' are row identifiers; the 19th row in table 1 and the 20th row in table 1, or something like that. Apparently there were two timetables for a certain trip, and one transaction was trying to delete timetable 19, then timetable 20, while the other was trying to delete timetable 20, then timetable 19. U and X are lock modes. U means: I'm reading this row and intend to update it, while X means: I'm updating this row. Updating can be any of: insert, update, delete; in your case it is delete. One way that I've used to get around problems like this in the past is a bit of a sledgehammer: immediately prior to the DELETE statement, but within the same transaction, you can do: LOCK TABLE TRIP IN EXCLUSIVE MODE LOCK TABLE TRIP_TIMETABLE IN EXCLUSIVE MODE. This technique works best if you adjust your code so that these three statements (the 2 LOCK TABLE statements, and the DELETE statement) are the only three statements in the transaction; that is, commit immediately before and immediately after this. Hope this gives you some clues. bryan
Re: derby is hanging in org.apache.derby.exe....g2
DELETE FROM ConnectionEntity conn WHERE conn.stopOrPass.id IN + (SELECT sop.id FROM StopOrPassEntity sop WHERE sop.partialTrip.id IN + (SELECT prtTrip.id FROM PartialTripEntity prtTrip WHERE prtTrip.trip.id IN : Ids)) There have been performance problems in the past with IN (...) queries. Is this query under your control? Or is it generated by some tool?
Re: Is it expected that each class loader context gets its own in memory database
And that would lead me to expect that each engine (and therefore class loader) would get its own, separate namespace of in-memory databases. That seems like correct behavior to me. thanks, bryan
Re: unable to take backup after derby upgrade
ij CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE('/usr/local/derby10/backups');
...
ERROR 38000: The exception 'java.security.AccessControlException: Access denied
(java.io.FilePermission
/usr/local/derby10/backups/PRAT write)' was thrown while evaluating an
expression.ERROR XJ001: Java exception: 'Access denied
(java.io.FilePermission /usr/local/derby10/backups/PRAT write):
java.security.AccessControlException'.
Do you have a security policy in place?
Have a look at
http://db.apache.org/derby/docs/dev/adminguide/tadminnetservbasic.html
and see if you can find your security policy, and see what file permissions it
is granting.
thanks,
bryan
Re: Error 42x71:Type de donnÚes 'BOOLEAN' non valide: when doing a Create Table [table] as Select * from [otherTable] with no data
So the 'boolean' word definately works, just not in the instance of a create table as statement. Yes, that sounds like a bug. Boolean has recently been added to the DDL language, and this might have been missed. Did you try searching JIRA to see if it's a known bug? If not, please log a bug with your reproduction script. What else can I now do? Did you try doing CREATE TABLE first, followed by INSERT INTO newtable SELECT FROM oldtable? If INSERT ... SELECT has the same bug, then you'll probably have to write a short program to do the processing yourself. thanks, bryan
Re: problem i connection
i need to connect my java code with ma database..hou should i write the database connection code in windows..i need hostname,name of the db,type of driver,tcp/ip port no of db,username,password. http://db.apache.org/derby/docs/10.9/getstart/ bryan
Re: Reconstruct DB from seg and log folders
I was given some data for academic research purposes all zipped up. Once I opened them I found out I had part of a derby database! (two folders were sent; seg and log). I'm now trying to reconstruct a derby database with these folders but not having any luck. I essentially created a new db with the ij tool, then disconnected and replaced the two folders and tried reconnecting to it, but Are you using the latest version of the Derby code when you try these experiments? Have you experimented with different versions of the Derby jars? I would anticipate that a newer Derby version could open an existing database in an older format, but not vice versa. bryan
Re: Problems with Online Backup SYSCS_BACKUP_DATABASE
On 08/17/2012 04:05 AM, Stefan R. wrote: database, got an I/O Exception while writing to the backup container file /mnt/backup/2012-08-13-00-00-00/bd/seg0/c9b1.dat.#012#011at org.apache.derby.client.am.Statement.completeExecute(Unknown ... org.apache.derby.client.am.SqlException: Java exception: 'No space left on device: java.io.IOException'.#012#011... 29 more The available space on the target device is more than sufficient. What sort of operating system and file system are being used? Perhaps you have disk quotas enabled? Some operating systems reserve a certain portion of the space on a filesystem, and won't let non-privileged processes write 100% of the space on the filesystem. Some filesystems have file size limits. Perhaps one of the tables is 4GB in size, and you're trying to write to a external thumb drive which is formatted FAT32 and won't allow larger files to be written. The easiest thing would seem to be to get a larger backup device. thanks, bryan
Re: Corrupted database - missing system files
After adding these in, I get assertion failures when trying to run some queries, which is perhaps not surprising. Seemingly, it seems all the user conglomerates are present, looking at the list of filenames present. Is there any way I can get this database into a useable state? I can't think of any straightforward way. It seems like, if you could create an empty database with an *identical* schema, you might stand some chance of copying the user conglomerates from the damaged database to the empty database and running SELECT commands to extract the data. Even the smallest differences in the two schemas would cause internal assertions to fire, but with some effort you could probably diagnose where the schema mismatch was, from the information in the assertion, and from your knowledge about the expected schema. But it's going to be a lot of work, sorry. bryan
Re: Cannot read table while writing transaction in another connection
I would have expected that around line 175 I would get the row count as it was before the uncommitted transaction started and that no lock would be needed to just read. Unfortunately, Derby doesn't currently implement these snapshot isolation types of semantics. Is this the way it is supposed to work? If so, is there some configuration parameters that I can set up to get the behavior I expected? You can try using a lower isolation level: http://db.apache.org/derby/docs/10.9/devguide/cdevconcepts15366.html But that's not exactly the same thing (e.g., at a lower isolation level, it might be that the count returns the instantaneous count of rows in the table, including some rows from uncommitted transactions). thanks, bryan
Re: Java DB (Derby Database) queries
1) Does Java DB support database level audit trail ? Derby does not provide any support for tracking security-related operations. If you only need to audit update statements, you may be able to define triggers that do what you need. Typically this is done by having your triggers append auditing records to a separate audit table in your database. Then you can query the audit table later to see what work has been occurring. thanks, bryan
Re: Derby on NAS = corruption ?
These Derby dbs (version 10.5.1.1) are stored on a NAS (cluster Isilon NL series) shared between webservers. The derby db is directly acceded on NAS and it is thread safe. When you say the Derby dbs are stored on a device shared between webservers, do you mean that there are Derby applications on each webserver directly accessing the Derby db? Or do you have some sort of Derby Network Server in place? Derby attempts to prevent multiple JVMs concurrently opening the same database with the embedded driver, but if your shared-file-system configuration circumvents these protections, the database would quickly become corrupt. thanks, bryan
Re: DerbyUI plugin.xml error
I am attempting to configure Derby with Eclipse. I have downloaded the DerbyUI svn source and after importing the source into Eclipse per the instructions I have an error in the plugin.xml file indicating org.apache.derby.core cannot be resolved. Has anyone else encountered this and know what needs to be done to resolve the error? Eclipse IDE for Java EE Developers 4.2 JDK 7 Derby 10.9.1.0 Hmmm... Not sure what you're asking. Firstly, there are two separate plugins: the core plugin and the ui plugin. It sounds like you have the ui plugin but not the core plugin. Secondly, the Derby Eclipse plugins are not actively built as part of the Derby release process; you have to build them yourself. See: https://issues.apache.org/jira/browse/DERBY-5272 thanks, bryan
Re: transaction problem???
Caused by: java.io.FileNotFoundException: D:\repos\TOPIK\workspace\log\db_20120614_114744\loggingDB20120614_114744\log\log2846.dat (The process cannot access the file because it is being used by another process) Check to see if you have an active Anti-Virus scanning program which is monitoring file activity. A/V software often interferes with programs in ways like this. If you have A/V software on your machine, see if you can configure it so that it doesn't scan D:\repos and see if that helps. thanks, bryan
Re: Error when dropping a table
Does anyone have explanation on the error? Is there anything we can do to drop the table successfully? There's some sort of a bug here. If you can file it in JIRA with whatever supporting information you can provide (ideally, a reproduction case or perhaps a backup of the database with this problem), that would help the developers tremendously. If you can't drop the table, can you rename it? That at least would move it out of the way so you can get on with your work. thanks, bryan
Re: Speeding up hideous insert
On 07/03/2012 03:20 PM, TXVanguard wrote: Don't worry too much about the details: just look at the SELECT DISTINCT, the WHERE, the GROUP BY, etc. What are some general strategies for speeding up this kind of statement? What the community knows about this sort of thing is mostly collected here: http://db.apache.org/derby/docs/10.9/tuning/ A good place to start is to (a) break down your big query into smaller queries, so you can analyze each part separately, and (b) learn how to read Derby's query execution plan output, which you can read about here: http://db.apache.org/derby/docs/10.9/tuning/ctundepth853133.html thanks, bryan
Re: Problem upgrading a derby database, ArrayIndexOutOfBoundsException on getIndexInfo()
On 06/30/2012 02:24 PM, fed wrote: I have a derby database 10.8.x and i use it with jdo/datanuclues. I am trying to update it to 10.9 but after updating it the database becomes unusable, it gives me an ArrayIndexOutOfBoundsException on conn.getMetaData().getIndexInfo(... ). From time to time, upgrade bugs such as this creep into Derby. It sounds like you have made good progress on narrowing down a reproduction scenario for this problem. Getting the *exact* details for what reproduces the problem is critical with these upgrade bugs. Derby has a very extensive upgrade test suite, but there are an astonishing number of combinations and sometimes something slips through. It would be great if you can enter a JIRA issue describing the upgrade problem that you are seeing, and include as much information as possible, including all the information that you have in your email. If you can describe the problem accurately enough that it can be reproduced by somebody else, the odds of resolving it are very high. thanks, bryan
Re: Guidance/Help/Book/References?
Derby is used heavily in my project and its tables are frequently accessed concurrently by multiple threads. Some threads update one or several tables, while other threads perform run select statements against those. I’ve written to this group several times whenever errors occurred, but some of those message have either been ignored or contained references to fairly short explanations on the website. So, the problems continue and it is difficult to find the proper solution. ... I must resolve all these errors ASAP and I would like to ask some guidance as to how to do it most properly. Avoiding deadlocks can be very challenging. I have often found that a very crude mechanism can be used. In my application, I modified my code to use Derby's LOCK TABLE statement. For each transaction, I identified which table(s) it was accessing. At the start of the transaction, my code issued a series of LOCK TABLE statements: LOCK TABLE t1 IN EXCLUSIVE MODE; LOCK TABLE t2 IN EXCLUSIVE MODE; I did this even for read-only transactions (and used EXCLUSIVE mode for them, although I certainly could have used SHARE mode). I thus single-threaded all my Derby accesses, and avoided all deadlocks. I encapsulated this logic into a common subroutine to avoid cluttering up the rest of the code; I just have to pass that subroutine the correct set of tables for this particular transaction, and the JDBC connection to use. I ensured that I always locked the tables in the same order (to avoid deadlocks). Although this meant that my transaction was somewhat less concurrent than it might have been: a) I never had to deal with unexpected deadlocks in the middle of a transaction b) I found that performance was just fine for my application If deadlocks are an ongoing persistent problem, you might consider this technique. thanks, bryan
Re: Merge and combine different data from databases
Currently I have a DB named myDB and myDB consists of a few tables in it. Lets say I want to write to myDB from different computers concurrently(each computer will have its own myDB), how do I merge and combine the DB from each computer into 1 central DB at the end of the day? One technique is to use a single Network Server instance, and a single physical DB, but multiple database schemas, one for each computer. Each computer connects to the server and reads and writes data from its own set of tables in its separate schema. A separate application can then connect and use UNION VIEW syntax to view the various separate tables as unified larger tables containing all the data, union-ed together. thanks, bryan
Re: how to limit the derby db file size avoid eating up disk space?
How to limit thesize of db file afterlarge amounts of insert and delete operation? One common technique for handling this pattern of activity, is to use a collection of tables, rather than a single table, and to drop entire tables rather than deleting rows from an existing table. For example, create a new table to hold each week's worth of data. Keep the 10 most recent weeks of data online, by keeping the 10 most recently created tables. Each week, drop the oldest table, and create a new table for the new week. Use UNION selects to process data from multiple tables. thanks, bryan
Re: hello
C:\java org.apache.derby.tools.sysinfo Error: Could not find or load main class org.apache.derby.tools.sysinfo C:\echo %CLASSPATH% C:\Program Files\Apache\db-derby-10.8.1.2-bin\lib\derby.jar;C:\Program Files \Apache\db-derby-10.8.1.2-bin\LIB\derbytools.jar; Sometimes it is hard to get the quotation marks correct when you have spaces in file names. Why don't you try putting the Derby installation into a different directory, without spaces in the names, for example c:\derby10.8, and see if setting your classpath to point to that directory makes things easier. thanks, bryan
Re: a few questions to Apache Derby Database
*_java.lang.OutOfMemoryError: Java heap space_* Is it because of the size of our database (3,1 GB!)? and what can I do to improve the performance of the Derby Database, and to resolve the error? The Derby Tuning Guide provides a lot of useful general advice about how to tune the performance of a Derby-powered application: http://db.apache.org/derby/docs/10.8/tuning/ This derby-user mailing list is the right place to pose more specific questions about performance problems which you are seeing. Most of Derby-allocated memory belongs to the Derby page cache. You can trim the size of the Derby page cache by adjusting the derby.storage.pageCacheSize property, as discussed in the Derby Reference Manual: http://db.apache.org/derby/docs/10.8/ref/ It wasn't obvious from the original question whether you are running Derby embedded, or in Client-Server mode. If you are running embedded, the single JVM has to satisfy the memory needs of Derby *and* those of your application, so memory issues in your application can present symptoms in Derby. In addition to reducing Derby's usage of memory by setting various properties to limit its usage, you can also grant the overall JVM more space by setting larger values for the -Xmx flags, etc. thanks, bryan
Re: Activity 3: Run a JDBC program using the embedded driver
and echo $CLASSPATH /home/kb9agt/jdk1.7/db/lib/derby.jar:. I see A : separated list of directories I think I need java -cp $CLASSPATH WwdEmbedded Yep. Did the trick. Please update the tutorial as soon as you can. I'm glad you got it figured out, but I'm not sure what's wrong. The whole point of setting your CLASSPATH variable is that you do *not* need to say '-cp $CLASSPATH', since the 'java' executable will use the CLASSPATH environment variable when no '-cp' argument is present on the command line. I'm having trouble figuring out things you might have done where '-cp $CLASSPATH' is different from omitting the -cp argument entirely. Is it possible that you did set CLASSPATH=... instead of doing export CLASSPATH=... thanks, bryan
Re: Can someone explain the use of logged archive backup?
confused here on the archived logs and the active logs. In general, there can be multiple logs covering the time between one backup and the next backup, and those logs must be applied, serially, in the correct order, to recover the database fully. Once you take that next backup, you no longer need the previous backup/logs, though it's probably wise to establish a reasonable holding period depending on your resources (e.g., backup weekly, keep 3 months of backups and logs, destroy the oldest set when you complete the most recent backup, etc.) I need to protect against a media crash and it is not so important to go back to specific periods of time for the database. Perhaps you can entirely use lower-level mechanisms, then, such as RAID or other redundant storage hardware, or a modern filesystem which automatically replicates the underlying data against the failure of the storage, such as ZFS (http://en.wikipedia.org/wiki/ZFS) I think it's still wise to have an application-level backup strategy, because sometimes logical recovery is necessary (e.g., to recover from an application bug or an administrative mistake), so I think that the exercise you're going through about documenting your backup and recovery strategies is an excellent one. And don't forget to test those backup/restore practices, since an untested restore is no better than no restore at all. I've found that one useful technique is to provision a secondary machine, which can be MUCH smaller in terms of CPU, memory, networking, etc., and just has to have enough disk space, and automate things so that every time I take a backup, my scripts automatically copy the backup to this spare machine, restore the backup and apply all the logs, and then run a few queries to satisfy myself that the database is correctly recovered. thanks, bryan
Re: Can't remove derby from memory
room. I have noticed that no matter what I do, the ~10MB of memory that is taken when the database connect is initiated is held no matter what commands Certainly sounds like the database isn't getting fully shut down. dynamDS.setShutdownDatabase(shutdown); It's not clear to me that this does anything by itself. The docs say: If set to the string shutdown, this will cause the database to shutdown when a java.sql.Connection object is obtained from the data source. E.g., If the data source is an XADataSource, a getXAConnection().getConnection() is necessary to cause the database to shutdown. This sounds like you have to get a final connection (and then close it) after setting ShutdownDatabase. Did you try getting a connection after calling setShutdownDatabase? thanks, bryan
Re: Who is connected to Derby Network Server?
On 02/26/2012 08:43 AM, Libor Jelinek wrote: Hello everbody! I would like to ask the community how to see a list of connected clients to Derby Network Server? Have you tried runtimeinfo: http://db.apache.org/derby/docs/10.8/adminguide/tadminappsruntimeinfo.html thanks, bryan
Re: CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE
Why i aways got an StackOverflowError exception when i try to run CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE() ? ... Caused by: java.lang.StackOverflowError at java.lang.ThreadLocal.get(ThreadLocal.java:125) at java.lang.StringCoding.deref(StringCoding.java:46) at java.lang.StringCoding.encode(StringCoding.java:258) at java.lang.String.getBytes(String.java:946) at java.io.UnixFileSystem.getBooleanAttributes0(Native Method) at java.io.UnixFileSystem.getBooleanAttributes(UnixFileSystem.java:228) at java.io.File.exists(File.java:733) at org.apache.derby.impl.store.raw.data.StreamFileContainer.run(Unknown Source) at java.security.AccessController.doPrivileged(Native Method) at org.apache.derby.impl.store.raw.data.StreamFileContainer.privExists(Unknown Source) at org.apache.derby.impl.store.raw.data.StreamFileContainer.open(Unknown Source) at org.apache.derby.impl.store.raw.data.BaseDataFileFactory.openStreamContainer(Unknown Source) at org.apache.derby.impl.store.raw.xact.Xact.openStreamContainer(Unknown Source) at org.apache.derby.impl.store.raw.data.BaseDataFileFactory.dropStreamContainer(Unknown Source) at org.apache.derby.impl.store.raw.xact.Xact.dropStreamContainer(Unknown Source) at org.apache.derby.impl.store.raw.data.DropOnCommit.update(Unknown Source) at java.util.Observable.notifyObservers(Observable.java:142) at org.apache.derby.iapi.store.raw.xact.RawTransaction.notifyObservers(Unknown Source) at org.apache.derby.impl.store.raw.data.DropOnCommit.update(Unknown Source) at java.util.Observable.notifyObservers(Observable.java:142) at org.apache.derby.iapi.store.raw.xact.RawTransaction.notifyObservers(Unknown Source) at org.apache.derby.impl.store.raw.data.DropOnCommit.update(Unknown Source) at java.util.Observable.notifyObservers(Observable.java:142) ... That's clearly a bug in Derby, unfortunately. Can you report it, gathering as much information as possible? http://db.apache.org/derby/DerbyBugGuidelines.html Can you narrow the problem down to a repeatable case that you can include with the bug report? thanks, bryan
Re: Derby Embedded losing connection ?
get reports (and also felt it our self) that our app simply is losing its connection to the DB. What are the symptoms, exactly? That is, what is it that makes you think you are losing your connection? One thing that occurs to me is a bit of a long-shot: are you using a connection pooling framework of some sort? If so, is it possible that the connection pooling framework is making a mistake and closing a connection inappropriately? thanks, bryan
Re: Derby database started in READ ONLY mode
=== java.io.IOException: No space left on device at sun.nio.ch.FileDispatcher.pwrite0(Native Method) at Also check if your database is located on a FAT-32 filesystem or similar, where database files are limited by the filesystem to a max 2GB size. Similar things can happen with operating system quotas; the operating system can refuse you from writing any more data to the filesystem even though the overall filesystem itself may not be full, if you have reached your quota. thanks, bryan
Re: Stall during NetworkServerControl.getRuntimeInfo()
We have one particular user reporting an issue where getRuntimeInfo() stalls while trying to read the data back from the server: SwingWorker-pool-1-thread-3 Id=48 RUNNABLE at java.net.PlainSocketImpl.isConnectionReset(PlainSocketImpl.java:623) - locked java.lang.Object@48183ac5 at java.net.SocketInputStream.read(SocketInputStream.java:112) at org.apache.derby.impl.drda.NetworkServerControlImpl.ensureDataInBuffer(NetworkServerControlImpl.java:2853) at org.apache.derby.impl.drda.NetworkServerControlImpl.readLDString(NetworkServerControlImpl.java:2962) at org.apache.derby.impl.drda.NetworkServerControlImpl.readStringReply(NetworkServerControlImpl.java:2943) at org.apache.derby.impl.drda.NetworkServerControlImpl.runtimeInfo(NetworkServerControlImpl.java:1385) at org.apache.derby.drda.NetworkServerControl.getRuntimeInfo(NetworkServerControl.java:482) The same server is doing it fairly reproducibly (if getRuntimeInfo() is called often enough, it eventually will stall, but it might take a few tries each time) but it doesn't happen at all for anyone else. (1) What could cause this sort of thing? I've never seen this. It certainly sounds like a bug. Can you isolate this into a standalone test program which does only this and nothing else, and reproduce it? Also, what is in your derby.log? Are there any clues there about what might be going wrong? thanks, bryan
Re: Porting to standard SQL
On 02/02/2012 02:53 PM, TXVanguard wrote: UPDATE T1 INNER JOIN T2 ON (T1.A= T2.A) SET T2.B = T1.B Perhaps something like: update t2 set b = (select b from t1 where t1.a = t2.a) thanks, bryan
Re: Can't seem to force table level locking
Yes, autocommit is turned off. Any other thoughts? Well, I never had any trouble getting the LOCK TABLE feature to work, so I'm not sure what's wrong. One possibility is that the query plan output is misleading you. That is, although the query plan output might indicate that the optimizer is choosing row-level locking, and it might even be requesting row-level locks when it runs, the locking system may be quietly processing those locks as table-level locks and so everything might be working fine, just confusing output from the optimizer dumps. Do you have a reason to believe that your table is not being locked at table level? For example, you see unexpected concurrent accesses to the table? Another thing you might try is to access some of the diagnostic locking tools from within your transaction. For example, plant a call to the LOCK_TABLE function and print out the results, and see what locks it shows that you are *actually* holding. thanks, bryan
Re: Can't seem to force table level locking
I have tried to force table level locking by;
1. SQL - lock table wayNodes6 in share mode
2. st.execute(call
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.locks.escalationThreshold','1'));
But the query execution plan always states it has chosen row level locking
Do you have autocommit turned off?
Otherwise Derby is committing (and releasing your table lock) after step (1).
thanks,
bryan
Re: where are my db files?
well i use jdbc.EmbeddedDriver for my db connectivity and i connect to mydb with this statement jdbc:derby:C:/Users/user1/firstdb i get in the firstdb folder but i do not see the tables i have created.so where are they? Make sure you say ;create=true at the end of your connect statement, otherwise Derby won't create the database. You should see files named 'dbex.lck', 'db.lck', and 'service.properties', and you should see subdirectories named log and seg0. Inside seg0 you should see files like c10.dat, c20.dat, c324.dat, c1a1.dat, etc. These are conglomerates, and to figure out which conglomerate corresponds to which table in your database, you can use the SPACE_TABLE procedure: http://docs.oracle.com/javadb/10.8.1.2/ref/rrefsyscsdiagtables.html thanks, bryan
Re: List columns that make up an index
What root canal Just to get column names of an index I always just use ij's show indexes command. http://db.apache.org/derby/docs/10.8/tools/rtoolsijcomrefshow.html thanks, bryan
Re: German Sharp S and UCASE
ResultSet rs = s.executeQuery(values upper('Straße'));
So it seems the value is returned correctly, but the meta-data is wrong
(STRASSE is 7 characters long, not 6). ij uses the meta-data to
determine how much space each column should have.
6 *characters* long, but 7 *bytes* long?
Do we have a way to represent such a difference?
Certainly sounds like a bug, and with a nice small test case!
bryan
Re: Error on CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE
Exception in thread main java.sql.SQLException: Import error on line 1 of The error message is telling you that the error is on the very first line of the data file. Sometimes spreadsheet exports contain a special column headers row at the very start. Usually your spreadsheet tool has a checkbox where you can tell it, when exporting the CSV file, to skip the column headers. thanks, bryan
Re: AW: Does derby ned allways a rollback or commt?
On 11/22/2011 04:01 AM, Peter Ondruška wrote: I would extend your question: is there any difference in commit or rollback after single select statement? Nothing much that I ever found. I always use commit to complete my selects, because it feels cleaner, at the application level, to use rollback only when I am intending to undo an operation. thanks, bryan
Re: Unique Column with null values
is there a way to mix an unique column with null values. So that i have an column where only unique values are allowed with the exception of multiple null values. Yes. Since Derby 10.4, that is the way that Derby's table-level UNIQUE constraints have worked. See: http://db.apache.org/derby/docs/10.8/ref/rrefsqlj13590.html thanks, bryan
Re: SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE question
On 10/27/2011 09:26 PM, Sundar Narayanaswamy wrote: I insert 1 rows into the table, then delete all that rows. I then call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE with just the option to purge rows so that the space left behind by deleted rows can be reused for future inserts. I have the inserts, deletes and compress in a loop. After running through the loop a few times, I am noticing that the number of allocated and unfilled pages for the primary key keeps growing (causing database size to grow over time). The longer I run the loop, larger these numbers are. That does not happen for the table or the index files though.. This seems like incorrect behavior to me; I think all the space should be reclaimed. I think you should open a job in JIRA and provide your test program so the developers can have a look at the behavior and try to understand what is doing on. http://db.apache.org/derby/DerbyBugGuidelines.html thanks, bryan
Re: Space requirements on disk
Is it possible to give rules of the following kind: Every field of type INTEGER will take N bytes (N=4?), every field of type VARCHAR(X) will take I*X+J bytes (I, J =?), every field of type VARCHAR with NULL value will take K bytes, every row will take the sum of all field-widths plus L bytes, I think it *is* possible, but I'm not aware of anyone who has done this. When I was faced with practical problems of this sort (how much space will my database take up, if I design the tables like this?), my approach was to prototype the tables, load up a reasonable amount of data (at least a few hundred thousand rows, maybe a million rows), and look to see how much actual space got used. The Derby SPACE_TABLE tool is quite useful for running experiments like this. http://db.apache.org/derby/docs/10.8/ref/rrefsyscsdiagtables.html thanks, bryan
Re: ClientDriver class not found in derby.jar
I'm using Derby-10.8.1.2 bin installation for use in my program, I got the following error stack : java.lang.ClassNotFoundException: org.apache.derby.jdbc.ClientDriver And is derbyclient.jar in your CLASSPATH? thanks, bryan
Re: too size for derby db
I'm newbie of derby but there is something not clear for me. There are soo few data that 12MB seems to excessive...could you please let me know? SYSCS_DIAG.SPACE_TABLE can help you figure out where your space goes: http://db.apache.org/derby/docs/10.8/ref/rrefsyscsdiagtables.html thanks, bryan
Re: Can't Load Embedded Driver
the call jdbc:derby:EMDatabase;create=trueis not a valid call to embedded driver. It is a call to a derby server correct url should be ( as far as I know) jdbc:derby:path to database;create=true No, the server-style URL always has the double slash after the derby:, as in: jdbc:derby://my.host:/dbname The URL jdbc:derby:EMDatabase;create=true is a valid call to the embedded driver to reference a database named EMDatabase which is located *relative* to derby.system.home. If the path to database portion starts with a leading (single) slash, it is an absolute path name in the filesystem; if it does NOT start with a leading slash, it is a relative path name, interpreted relative to derby.system.home. http://db.apache.org/derby/docs/10.8/devguide/rdevdvlp22102.html#rdevdvlp22102 thanks, bryan
Re: Can't Load Embedded Driver
C:\WINDOWS\SYSTEM32\java.exe -splash:EMsplash.jpg -jar C:\Program Files\ElectionManager\EMServer.jar Not sure if this is the problem, but I believe that if you use '-jar' on your command line, then CLASSPATH is ignored, and ALL the classes have to come from the jar, right? thanks, bryan
Re: How to run programs with Derby as a database
I am getting error that database 'wombat' not found. You can specify ;create=true at the end of your connection URL and then Derby will automatically create the database for you. thanks, bryan
Re: nulls in prepared statement
pstmt.setString(1,cobj.getPartNo()); where the getPartNo() method returns null. When this happens I get a null pointer exception. It's not an exact match, but your description sounds VERY close to https://issues.apache.org/jira/browse/DERBY-1938 Can you post a full stack trace of your NullPointerException? What version of Derby are you using? If you are in fact encountering DERBY-1938, can you upgrade to 10.8 and see if your results are better? thanks, bryan
Re: How to unlock a table in derby
On 07/18/2011 07:16 AM, Lahiru Gunathilake wrote: Hi Byan, I am creating the connection with autoCommit=true parameter, does this work with derby or should I explicitly commit the transaction? Lahiru I believe if you do this, the system will automatically insert a 'commit' immediately after your 'lock table' statement, thus releasing the lock immediately and making the lock not very useful. When I use 'lock table', I always do it with autocommit=false. thanks, bryan
Re: How to unlock a table in derby
I execute query lock table table name in share mode but I cannot see any documentation on how to unlock a derby table. Commit. thanks, bryan
Re: Long compilation time for a Prepared Statement
1. Does the compiler take the amount of records in consideration when compiling the query? Yes. The optimizer has statistical information about the size of the various tables, and about their keying structures, etc. 2. Am I right to assume the cause of the delay is in the excessive amount of joins, that is causing the compiler (and optimizer) to have a hard time figuring out the best query plan? Possibly. There are extreme combinatorics here, for sure; some of the search space exploration is factorial in complexity. I had some similar problems, and opened a job with my findings: https://issues.apache.org/jira/browse/DERBY-2130 In the details of that job are references to other related jobs. 3. Is there any parameter that controls the timeout for that optimization? Since the view does not actually have many records to begin with, I think it's acceptable to go with a non-optimal query plan. As you'll see in my notes on DERBY-2130, I followed a similar line of reasoning, and found that by forcing an early termination of the search, I got a reasonable query plan. 4. Is there any other information I can provide to help debug? There are no stack traces in the log, but I'm not seeing that much info on it anyway: I see a line where compilation begins and the next one, two minutes later, is when the compilation ends. I have also attached my derby.properties below [4]. This is hard stuff. I'm not sure what to suggest. Although Derby has powerful algorithms in this area, there are a few weak spots, and it's possible you've hit one of them. Since it's open source, we as a community can all work together to try to improve Derby's implementation in this area. thanks, bryan
Re: hi
On 07/05/2011 11:12 PM, dinesh nautiyal wrote: Hi, I want to use derby database with Eclipse,Plz suggest me how to pulg in Derby with eclipse. Thanks and Regards Dinesh. Perhaps you are looking for a resource like this: http://www.eclipse.org/articles/article.php?file=Article-EclipseDbWebapps/index.html thanks, bryan
Re: Embedded - can it be multi-user?
Now, I was using the SQuirreL SQL Client to look at my databases as they were being created, but I couldn't use ij with the database *_and_* SQuirreL at the same time. Correct. Two separate JVMs cannot both access the same database using the embedded driver concurrently. What I'm wondering is, if there's a single app connecting to the database multiple times, is it up to the app to manage the connections so that only one connection is active at any one time, or how exactly does that work? Say in a context where an App Server is connecting to an embedded Derby database - i.e. no server running - does the App Server have to manage requests to the database in a queue or how, exactly does the system work? Java is multi-threaded, and Derby's JDBC apis (the java.sql.* interfaces) are thread safe. Multiple connections can be opened and used concurrently. So a well-written application which processes work on behalf of multiple users can certainly open multiple separate connections to the database, and each connection (in a separate thread) can be performing work concurrently using the same embedded driver, so long as this is all a single JVM. (More precisely, it must be a single class-loader in a single JVM; separate class loaders behave like separate JVMs w.r.t embedded use.) Derby's NetworkServer is an example of such a well-written application, but it is not the only one possible. Still, many people find it convenient to use Derby's NetworkServer rather than writing their own. The NetworkServer even has some simple support for being managed by an application server: http://db.apache.org/derby/docs/10.8/adminguide/cadminservlet98430.html thanks, bryan
Re: Bug with http sub protocol?
Yes, I think you should file a JIRA. For what it's worth, it seems to work with a URL like this: jdbc:derby:https:http://localhost:8080/ipinfo Wow! Is this in the Derby docs somewhere? thanks, bryan
Re: Derby can be used for enterprise application?
can it be used for big enterprise applications? Yes! does data access and write operations gets slow down as volume of data increases over time? Somewhat, but you can get tens of millions of rows into a well-designed Derby database without any noticable slowdown. is there any limitations of derby...? Yes. However, every big enterprise application I know of encounters database system limitations, and deals with them using the standard techniques of big enterprise applications: partition and replicate your data, update it asynchronously, distribute it over multiple machines, etc. As with all database applications, step 1 is your database design, and the basic principles are both database-independent and well-established over decades of experience. So long as you follow those, Derby works well to surprisingly large scales. bryan
Re: Bug with http sub protocol?
According to the JavaDoc, there exists a org.apache.derby.impl.io.URLStorageFactory, which is addressed by using the http sub protocol. However, when I attempt to access the following URL, Derby throws an exception: Connection _conn_ = DriverManager.getConnection(jdbc:derby:http://localhost:8080/ipinfo;); I don't think this works; I've never seen any discussion of such an URL syntax for Derby. It's an interesting idea, but I don't think you're going to get this to work with the 10.8 version of Derby. Here's some good docs about the current Derby support for Connection URL syntax: http://db.apache.org/derby/docs/10.8/devguide/cdevdvlp17453.html http://db.apache.org/derby/docs/10.8/devguide/rdevdvlp22102.html#rdevdvlp22102 thanks, bryan
Re: error executing multiple insert statements
On 06/16/2011 11:38 AM, Lothar Krenzien wrote: But shouldn't it works with JDBC too ? Perhaps you are looking for the batch facility of JDBC, as in the addBatch/executeBatch methods on java.sql.Statement: http://download.oracle.com/javase/1.4.2/docs/api/java/sql/Statement.html#executeBatch() thanks, bryan
Re: SYSCS_UTIL.SYSCS_BACKUP_DATABASE failing-urgent
On 06/07/2011 12:38 AM, Vijender Devakari wrote: Hi, Can you respond to this as this is very urgent. Mike replied: http://mail-archives.apache.org/mod_mbox/db-derby-user/201106.mbox/%3c4de90f37.2040...@sbcglobal.net%3E
Re: Disappearing service.properties file
problems above. I suspect derby has been terminated or interrupted while updating the service.properties file and left the file system in an inconsistent state. Although I imagine the window for this to happen would be fairly narrow. As far as I can tell the call to SYSCS_DISABLE_LOG_ARCHIVE_MODE(1), which is called during our nightly maintenance task, is the only place in our application which updates service.properties. Fortunately this call is no longer needed so we have removed it. Yikes! I've never encountered this service.propertiesold problem. I've also never called the disable-log-archive-mode procedure. If the two events are indeed related, that sure sounds like a bug in Derby, so if you have a chance to research this some more and see if you can verify that there is a real correlation here, that would be worth reporting to the developers, I think. thanks, bryan
Re: IJ-tool getting cursor-keys work on Solaris (bash)
I am using *ij-tool* just for look up. It works great on windows. When I use it on a bash-shell (Solaris) the *cursor-keys* do not work, but I get some strange letters typed like this: *ij select ^[[A^[[B^[[C^[[D* It seems that those keys are not defined in some way. They work with in the bash shell itself though. What has to be done, so it works, including history of the commands used within ij-session? I don't know if anyone has ever solved this. I have heard some discussions in the past about trying to use JLine (http://jline.sourceforge.net/), but I don't think I ever heard whether those attempts were successful or not. I think it would be great to get something to work, but I don't know of anything built-in to the Derby ij tool itself. For my own purposes, I tend to use a tool like SQL Squirrel, which has a nice SQL editing window built-in. thanks, bryan
Re: Derby network server
In the webapp: server = new NetworkServerControl(); server.start(null); Perhaps your webapp is trying to run these lines multiple times? It can be very tricky to ensure that your webapp starts the Network Server once and only once. You may be starting it a second time, and the second instance is not succesfully accessing the database because the first instance already has the database open? A possibly simpler architecture for your application is to have an entirely separate piece of software responsible for starting/stopping the Network Server, and then it can concentrate on doing that once-and-only-once. So: 1) Have a Network Server webapp 2) Have your normal application webapp(s), which access the database as clients 3) Have your external command-line tool(s), which access the database as clients You can have as many (2) and (3) instances as you want; you just have to ensure you have exactly one of your (1) instance. thanks, bryan
Re: packaging derby.jar in application jar file
On 04/01/2011 07:40 PM, Kris Hayden wrote: hello. i have been coding an application by hand and have been having a hard time getting the derby.jar file to be loaded from inside my jar Do you literally mean a jar-within-a-jar, not a jar-within-a-war and not a jar-within-a-ear? I don't think that the basic JDK jar loading utilities are capable of resolving class references to a jar within a jar; that sort of fancy class loading is generally done by the application class loaders found in application servers. The mechanism of having your manifest refer to a separate jar file is the only way I know of for the basic JDK jar loader to affect the class path. If you want to package everything as a single jar, you could try un-jarring all the Derby code into simple .class files and then re-packaging it into a single jar with no internal structure, just all .class files within the jar. But really, this is a lot of work; I think it would be easier to keep the Derby jars separate, in their existing jar files, and re-design your application strategy to allow for multiple jar files in the overall packaging. For one thing, the Derby security policy implementation assumes that the Derby jars are left as is, not re-packaged. Can you describe some of the reasons that you want to repackage Derby? thanks, bryan
Re: packaging derby.jar in application jar file
On 04/02/2011 08:32 AM, Matt Pouttu-Clarke wrote: The JDK loads the jars correctly as long as the manifest is updated correctly. Use it all the time with Derby and it works great. Thanks Matt! That's good to know; I was unaware of that capability. If you have time to put a few pointers to those techniques on http://wiki.apache.org/db-derby/HintsAndTips I'm sure the next person who hits this problem would be grateful! bryan
Re: Indexing and searching complex data types
Perhaps the Derby generated columns feature would be of help. You could write some code that would generate the search values by processing your blob of data, then build an index on the generated search terms. Knut Anders has a nice writeup of generated columns here: http://blogs.sun.com/kah/entry/derby_10_5_preview_generated And you can find more reference material about generated columns in the Derby docs at http://db.apache.org/derby/docs/10.7/ (which seem to be down at the moment, otherwise I'd give you a better link as the reference, but look in the reference guide for the CREATE TABLE column definition pages. thanks, bryan
Re: importing file with header
Is it possible to import data via SYSCS_UTIL.SYSCS_IMPORT_DATA when the first row of data is a header that contains column names? For example the data are orgnized like this: name,age,weight bob,17,150 sue,18,120 If not, does anyone know a workaround? I've always just stripped off that first line prior to the import. Is it hard to do that in your case? thanks, bryan
Re: establishment of the connection
On 03/14/2011 02:42 AM, bilal haider wrote: contents of startserver.bat set CLASSPATH=lib\derby.jar;lib\derbynet.jar java -cp %CLASSPATH% org.apache.derby.drda.NetworkServerControl start -h localhost -p 1527 pause and the posdb and startserver.bat are in same location. http://old.nabble.com/file/p31142301/screenshot.jpeg In the directory where you start the server (D:\New WS\floreantpos), do a dir command before you start the server. Do you see a subdirectory named posdb? If so, what is inside that dir? Can you do: dir /s D:\New WS\floreantpos Look around on your computer. Where is the directory posdb located? thanks, bryan
Re: AW: Performance question, Closing Prepared statements
any derby rule or good practice how many commits are to be done in respect of how much memory is assigned to the JVM derby resides in? In general, a commit should be performed at the completion of a natural unit of work in your application, to tell the database that you are finished making your changes, and desire them to be permanent and made available to other concurrent users. Sometimes, an application may wish to commit at intermediate points, for example if the application can thus be made re-startable upon a system crash or other failure. If I had a program that needed to run for (say) 3 hours each night, but I could make it commit its work periodically, and then be able to restart it from the point of last commit if it should fail, that might be nice, but that's a lot of extra complexity in the application design and I usually don't bother. The only other reason to issue a commit prior to the completion of your task is if the database can't handle a transaction that large. I'm not sure what internal Derby limits you might hit, but if your task is to load a bunch of data into a Derby table, I'd try to do it as a single transaction, and I'd only add additional commits if I got a transaction too large error out of Derby when I ran the program during testing. thanks, bryan
Re: Performance question, Closing Prepared statements
On 03/08/2011 10:01 AM, malte.kem...@de.equens.com wrote: close prepared statement after each operation, because there might be a pooling within derby or derby driver for those statements, or should I rather leave those three kinds of prepared statements (insert, update delete) open till my manipulation is full done or breaks down for some error? It is fine to have a number of prepared statements, and to leave them open for a while. It is also fine to close them and re-prepare them; if the literal SQL text string is identical, Derby has a cache which should re-prepare the statement very quickly. Sometimes it is *necessary* to close and re-prepare a prepared statement, and *not* use the one that Derby has cached, because the table structure has changed so much in the meantime that a new query plan needs to be chosen. In that case, you can tweak the statement slightly (I sometimes add some extra whitespace, or change the upper/lower case of a keyword) to force Derby to recompile. Regarding the overall performance, if your program is single-threaded and is having direct access to the database, I would advise: 1) Give Derby as much memory as possible. Use -Xmx and specify as much memory as you can without making your system start to page. 2) Use the embedded driver rather than the network server driver, to shorten the overall data path and reduce the amount of data movement. 3) Try to break your work into as few transactions as possible. The thing that limits your overall throughput is probably the number of commit's that you have to do, so do as few as possible. Hope this helps; let us know how your work goes and I'm sure the list would be glad to offer more ideas. thanks, bryan
Re: Schema does not exist
On 03/07/2011 04:35 AM, Prakash Jaya wrote: After creating the database with user name and password in eclipse , then after restarting the server , when trying to access the data it is saying schema does not exist. but before restarting the server i am able to access The most common cause of this is that you have used a relative, rather than an absolute, database name in your JDBC Connection URL, and the server is using a different current-working-directory when you ran it the second time, hence it is not finding the database that you created and used the first time. thanks, bryan
Re: Access to embedded derby db
On 02/25/2011 11:43 PM, linux86 wrote: Can I ignore the lock on db or access to db in read only mode? No, those techniques are likely to bring crashes and database corruption, I'm afraid. In general, the only way to have multiple independent Java executables accessing the same Derby database is to channel them all through a NetworkServer instance. Can you control the JDBC URL used by the other program? If so, you could point it to a Network Server URL of a server that you controlled. Alternatively, if you can invoke the other program from within your own Java program (that is, if the other program is a simple Java executable), you could start the network server from within your own program prior to starting the other program, and then your own super-program would be able to serve concurrent requests from other tools as well as allowing the other program's direct access to the DB from within your super-program. thanks, bryan
Re: Explanation of Compile Time metric in Runtime Statistics
The runtime statistics yields the following output for statement 1 (first prepared statement compile) and statement 2 (same prepared statement executed again with different parameter): Begin Compilation Timestamp : 2011-02-24 13:09:50.816 End Compilation Timestamp : 2011-02-24 13:09:50.901 Begin Compilation Timestamp : 2011-02-24 13:09:50.816 End Compilation Timestamp : 2011-02-24 13:09:50.901 I'm a little confused. These timestamps are also identical. Were you somehow compiling the two statements simultaneously on two different threads? Or is it possible that this is actually just duplicate output, not output from two separate statement invocations? thanks, bryan
Re: SQLException - Column 'COLUMN2' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification .....
18 Feb 2011 18:17:35,218- Thread: 15 SEVERE [com.vontu.lookup.csv.CsvLookup] Failed to initialize Csv lookup. Cause: com.vontu.lookup.common.InitializationException: The exception 'java.sql.SQLException: Column 'COLUMN2' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'COLUMN2' is not a column in the target table.' was thrown while evaluating an expression. I don't know much about com.vontu.lookup.common, nor have I seen the message Failed to initialize Csv lookup before, but since that's not Derby code, that's not surprising. Probably the vontu.com library is generating and issuing an invalid query. Can you post the full stacktrace from your derby.log file? thanks, bryan
Re: Upgrade from 10.6 to 10.7 failed
but I run into following error on startup of the database (no matter what application (my app, SQuirrel, OpenOffice base) I use, and with all databases I could find): Restore of a serializable or SQLData object of class , attempted to read more data than was originally stored I'm not sure what's going on here; can you post the full stacktrace from your derby.log file? Did you do a soft upgrade or a hard upgrade? Does your database contain BLOB columns? The last several times I've seen this message, it has been due either to bugs in the Derby code or due to a corrupt database. Either way, it would be a good idea at this point to open a Derby issue in JIRA and start collecting as much information as you can in order to work with the Derby developers to analyze this in more detail. http://db.apache.org/derby/DerbyBugGuidelines.html thanks, bryan
Re: can't accesso to derby db folder...
C:\Program Files (x86)\NetBeans 6.9.1\javafx\javafx-sdk\binjavafx -classpath c:\application\application.jar application.Main and application starts correctly. When I click on button to interact with db an exception occurrs, saying it can't find db The database name typically is written in your JDBC URL as: jdbc:derby:applicationdb This is a relative name, meaning that the folder 'applicationdb' must be found relative to your program's current directory. So try one of the following: 1) In your shell, do this command first: cd c:\application so that your process's current directory is the correct one prior to running the application 2) Change your JDBC URL to something like: jdbc:derby:/application/applicationdb so that the application is accessed via an absolute path name, not a relative path name. thanks, bryan
Re: Arithmetic operations and PreparedStatments
update T_Professor set weight_In_B_D = weight_In_B_D + ?) - ?) * ?) / ?) where (id = ?) I'm not sure why the arithmetic is carried out using different intermediate scale and precision when you use dynamically substituted values for the constants in your expressions. Did you try using the SQL CAST() operator? Does that help at all? There might be a way to use Derby stored procedures to enable you to implement the numeric processing in your own Java code, rather than in SQL, which could be a workaround. The behavior you are seeing has the feel of a bug, though it's certainly possible it could be defined SQL language behavior. But since you have such a nicely-constructed test program, I think you should file your issue in the Derby bug-tracking system so that the developers can analyze it in more detail. http://db.apache.org/derby/DerbyBugGuidelines.html thanks, bryan
Re: ERROR XSLA0: Cannot flush the log file to disk
I'm using linux. DB size is 350MB only. The problem is solved once I restart derby. Derby version is 10.6. Hmmm... Interesting. I haven't seen this behavior before. Possibly you had an active transaction, which had performed some updates but had not yet committed, and the database was attempting to perform a checkpoint, and could not make room. By restarting Derby, you then allowed Derby to abort that old transaction. Just a guess, I'm afraid. I'm glad you've resolved your problem for now, hope you continue to find success with Derby. thanks, bryan
Re: ERROR XSLA0: Cannot flush the log file to disk
I'm new to derby, when I start writing data into db i'm getting the follwoing exception from derby.log can any one help me to fix it. = begin nested exception, level (1) === java.io.IOException: No space left on device Welcome to Derby. You have filled up your disk. Either write less data, or get a bigger disk. thanks, bryan
Re: ERROR XSLA0: Cannot flush the log file to disk
Thanks. I've verified disk space. It has enough space. FilesystemSize Used Avail Use% Mounted on /dev/xvdf 99G 18G 77G 19% /mnt/rw-ti ERROR XSLA0: Cannot flush the log file to disk /mnt/rw-ti/rw/DerbyDatabase/TICYCLESMFR/log/log1460.dat. There are other possible limits. For example, FAT-32 file systems limit the size of an individual file to 2GB or 4GB I believe. Have a close look at the file in the message above; what is the size of that file at this time? Also, are there possibly user quotas or other filesystem restrictions on file sizes or disk space usage? thanks, bryan
