Tag Info

Hot answers tagged

5

DbVisualiser could fulfill your requirements as it supports quite a number of RDBMS, including JavaDB/Derby. You can see it in action below: The only question mark is if the free version has some limitations which are a stopper for you. You can check the matrix here.


4

Looks like we found the culprit. Following the recent app server upgrade, we inadvertently included both ojdbc14 and ojdbc6 jars into our deployment, and evidently, the jvm picked up ojdbc14 for its Oracle DB driver. Since we removed ojdbc14 manually, this problem hasn't come up again in the past 24 hours. I assume ojdbc14 is no longer officially supported, ...


4

You are attempting to pass in a blank password. Odds are the sa account has a password. You should create a separate account for the application to use instead of using the sa account. Using the sa account is a major security issue.


4

Use a subquery (as displayed) or CTE for that purpose: SELECT * FROM ( SELECT qid, gid FROM table1 ORDER BY date DESC LIMIT 10 OFFSET ? ) q JOIN table2 a USING (qid, gid) USING (qid, gid) is just a shortcut for ON q.qid = a.qid AND q.gid = a.gid with the side effect that the two columns are only included once in the result.


3

You can get most of those messages, but unfortunately not all. See my question on Stackoverflow regarding that. In general those messages (e.g. messages from a PRINT statement) are returned as warnings on the Statement object by the JDBC driver. To retrieve them use Statement.getWarnings() in a loop: Statement stmt = ...; stmt.execute("some sql"); ...


3

Given this is a Windows installation, @DTest still provided the initial proper direction. Apply the following formula: Most people use this: Maximum MySQL Memory Usage = innodb_buffer_pool_size + key_buffer_size + (read_buffer_size + sort_buffer_size) X max_connections I prefer this: Maximum MySQL Memory Usage = innodb_buffer_pool_size + ...


3

A quick way to determine how much memory MySQL thinks it could allocate is as follows: wget mysqltuner.pl perl mysqltuner.pl When you run this script, it will tell you what percentage of the installed RAM MySQL thinks it can safely allocate. If the answer given is over 100%, you definitely need to lower your buffer sizes. The main one to focus on are: ...


3

I would try lowering your buffer sizes. Making them as large as you have them is going to cause problems. How much memory do you have available to run these values: query_cache_size=1024M myisam_max_sort_file_size=100G myisam_sort_buffer_size=10G key_buffer_size=5000M bulk_insert_buffer_size = 4000M read_buffer_size=8000M read_rnd_buffer_size=8000M ...


3

Already answered at a parallel thread on serverfault: http://serverfault.com/questions/345253/oracle-11-updating-blob-field-db-file-sequential-read-inappropriately-slow/345588#345588 In Oracle, LOB (including BLOB) is stored as: in-the-table LOB - if the LOB is smaller than 3900 bytes it can be stored inside the table row; by default this is ...


3

No, it's still only 1GB of total memory. The buffer is allocated as shared memory and it only allocated once, but shared between all server processes. It's basically an "error" in the memory display of the top command, which simply reports the shared memory (that only exists once) for each process.


3

Deadlocks in Oracle with logically disjoint transactions usually involve unindexed foreign keys: There are two issues associated with unindexed foreign keys. The first is the fact that a table lock will result if you update the parent records primary key (very very unusual) or if you delete the parent record and the child's foreign key is not indexed. ...


3

From the Connection.setAutoCommit docs: NOTE: If this method is called during a transaction and the auto-commit mode is changed, the transaction is committed. If setAutoCommit is called and the auto-commit mode is not changed, the call is a no-op. But I don't think it's very readable/obvious in your code. You should probably simply commit before ...


3

If the session is active and and on an idle event it's probably on the CPU and not waiting. You can run a query like the following to see for sure select nvl(s.username,s.program) username, s.sid sid, s.serial# serial, s.sql_hash_value sql_hash_value, substr(decode(w.wait_time, 0, w.event, 'ON CPU'),1,15) event , ...


3

You should always ensure that the JDBC driver is at least as new as the back-end server. PostgreSQL's catalogs change between major versions, and other details like the bytea quoting method, string escaping features, etc have also changed over time. PgJDBC contains compatibility code to cope with this by querying the server version. If you don't use a ...


3

An ORM requires information about the first write (SCOPE_IDENTITY or such) to complete the second write. This means 2 (with an OUTPUT clause) or 3 database (with SELECT SCOPE_IDENTITY) calls in general in a client side transaction. No amount of tinkering with isolation levels will eliminate these 2 or 3 calls. If you want more performance, then the best ...


2

For Oracle, this seems like a good sneaky way of catching COMMITs: http://stackoverflow.com/a/6463800/790702 What he doesn't mention is that you should be able to catch the constraint violation in your code too, to stop the 2nd situation occurring.


2

Consider a parent/child table such as client/order. You can't delete a client that has an order. Say client 123 has an order A123. Fred does a delete for that order but does not commit. Then "Jane" tries to delete client 123. Since Fred's statement can potentially rollback, the client can't be deleted because it isn't allowed to the leave the order ...


2

Delete is a DML command and stores the data in redo log till the delete operation is committed. This means that if data to be removed by delete is slightly large[even though search time is less] it will take longer time as it will move data to redo log. So may be the instance when your operation took longer large no. of rows were being deleted to many ...


2

Thin client can also use alias defined in tnsnames.ora. In java call set -Doracle.net.tns_admin=... http://docs.oracle.com/cd/B19306_01/java.102/b14355/urls.htm


2

Your trigger is fired for each statement not for each row. In statement level triggers you can not access the new and old records. You need to change your create trigger to create a row-level trigger, rather than a statement level trigger: CREATE TRIGGER my_trigger AFTER INSERT ON table2 FOR EACH ROW -- this is the change EXECUTE PROCEDURE ...


2

I'm getting a bit nervous. First, the upside: I've used the MariaDB JDBC driver in an "always on" service in production with a database connection pool and it is running fine for a couple of months now. The service only uses basic JDBC functions with simple queries (e.g. no joins, no blobs). I'm about to release another "always on" service that uses the ...


2

The parentheses form a row-constructor, so your query returns a single column row literal, essentially an anonymous composite type. Compare: regress=> SELECT (1,2); row ------- (1,2) (1 row) regress=> SELECT 1, 2; ?column? | ?column? ----------+---------- 1 | 2 (1 row) You would've quickly realised this if you'd run the query ...


2

There are several ways to rewrite the query and even more because both subqueries use the same base table. Not sure why the error is thrown and who is to blame, the JDBC drivers, the Foxpro or something else, so here are a few alternatives: (1) using one query for both searches: SELECT COALESCE(SUM(CASE WHEN t.btyp = 5 THEN t.netto ...


2

Transaction isolation level is set up per transaction basis. Even one connection can have different isolation level on each transaction. Of course different connection can have different isolation level. Here's some testing : postgres=# begin; BEGIN postgres=# show transaction_isolation ; transaction_isolation ----------------------- read committed (1 ...


2

Yes it does support different transaction isolation levels per-connection. You can set the transaction isolation level (as well as the read-only and deferrable status for transactions) for a connection with SET SESSION CHARACTERISTICS: localhost:5432 postgres postgres # SHOW transaction_isolation; transaction_isolation ----------------------- read ...


2

The usual solution is to authenticate the user within the web app, then issue a SET ROLE or SET SESSION AUTHORIZATION to "become" the user on a JDBC session that's already authenticated with the database using a fixed username. In both cases the DISCARD ALL command that should be run by any connection pool when returning connections to the pool will ...


2

You could create a function that executes SET ROLE with dynamic SQL, using format to safely insert the role identifier (%I inserts an identifier, placing double quotes around it if necessary, and escaping double quotes in the string by doubling them up if necessary). Something along the lines of CREATE FUNCTION setrole(role text) RETURNS void AS $$ BEGIN ...


2

Based on a simple test case I just wrote: @Test public void test() throws SQLException { PreparedStatement ps = conn.prepareStatement("SET ROLE ?"); ps.setString(1, "someuser"); ps.executeUpdate(); } I think the error you refer to is probably: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1" Position: 10 at ...


2

The only isolation level that influences writes is SNAPSHOT (and the READ_COMMITTED_SNAPSHOT). Snapshot isolation requires row versioning and row versioning requires extra writes. Read Understanding Row Versioning-Based Isolation Levels. Now about the 'super-fast' part of the question: the 'super-fast' option for INSERT is the bulk insert path. This ...


1

I guess you have forgotten a ')'. try this: stmt=con.prepareStatement("insert into create_request values((select count(reqno) from create_request)+1),?,?,?,?,?");



Only top voted, non community-wiki answers of a minimum length are eligible