Database Tips

General

• J2EE Transaction Management
• Nested Transactions
• JDBC Best Practices

PostgreSQL Tips

• PostgreSQL Wiki Article on Performance Optimization
• Optimizing PostgreSQL
• Practical PostgreSQL Query Analysis

Monitoring PostgreSQL Deadlocks

You can monitor any database locks using the Entity SQL Processor in Webtools -> Entity SQL Processor with the following query,

select pg_class.relname, pg_locks.mode, pg_locks.relation, pg_locks.transaction, pg_locks.pid
    from pg_class, pg_locks 
    where pg_class.relfilenode = pg_locks.relation 
    order by pid

This will show what kinds of locks are active on what entities. If there is an exclusive lock on a table followed by a bunch of pids that are waiting for it, then you have a deadlock.

Checking Open PostgreSQL Connections

Run this query:

 select datname, numbackends from pg_stat_database;

to see the number of open connections to each of your databases. See How to Know Number of Connections Made with Database - PostgreSQL

If you are running out of connections, edit the file framework/entity/config/entityengine.xml and increase the number of connections available.

MySQL Tips

Table Name Case Sensitivity

If you use Linux or Unix for your MySQL server, the table names may be case sensitive, so PRODUCT and product are not the same table. You can turn this off by configuring mysqld on startup to ignore table names with the lower-case-table-names flag, such as this example from /etc/init.d/mysql:

 $bindir/mysqld_safe --datadir=$datadir --lower-case-table-names=1 --pid-file=$server_pid_file $other_args >/dev/null 2>&1 &
     

See MySQL manual on identifier case sentivity

UTF-8 Support

By default, MySQL supports the Latin1 character set, which is intended for European languages such as English. If you wish to use MySQL for other language types, you may need to set up a database or UTF-8 character set encoding. To do this, you would need to create your database using UTF-8 first:

   mysql> create database opentaps default character set utf8 collate utf8_general_ci;

Then you would need to set your framework/entity/config/entityengine.xml file for the MySQL database to use the UTF-8 character set:

            character-set="utf8"
            collate="utf8_general_ci" 

Note that it is not clear that my SQL supports case sensitive UTF-8 coalition at this point, although you may be able to use UTF-8 binary collation.

Lock Timeout

You must change innodb_lock_wait_timeout parameter, else you might get error like these:

 Lock wait timeout exceeded; try restarting transaction

You can add this line in your mysql config (Windows: [MYSQL_HOME]\my.ini, Linux: /etc/my.cnf)

#set lock timeout 300s, default is 50s
innodb_lock_wait_timeout=300

Reset Root Password

See http://www.cyberciti.biz/tips/recover-mysql-root-password.html

DB2 Tips

DB2 Basics

You must configure DB2 to have tablespaces of 8K or more. This can be done when you create the database from the Control Center:

If you get an error message from DB2, you will get a SQLCODE like below:

DB2 SQL Error: SQLCODE=-270, SQLSTATE=42997, SQLERRMC=63, DRIVER=3.50.152

To figure out what it is, you have to run db2 from the command line:

$ db2 ? sql-270

Some of the more popular codes are:

• SQL-204: <name> not recognized. Most likely, you are referencing a table that doesn't exist.
• SQL-270: Function not supported. See the SQLERRMC for the message code. If you get sql-270 with sqlerrmc=63, it means that you are trying to select a CLOB/BLOB type with a scroll insensitive cursor.
• SQL-286: insufficient page size for CREATE TABLE
• SQL-530: foreign key violation
• SQL-803: operation violates a unique value constraint

Making DB2 Work

There are three issues with using DB2 and opentaps:

1. You must define a fieldtypedb2.xml file for your framework/entity/fieldtype/ directory. You can Start with the field type XML from another database, such as MySQL. Most of the valid DB2 field types are similar, but DB2 does not have a "NUMERIC" type. It is called "DECIMAL" instead of must be used for floating-point and currency field types.
2. On startup, the ofbiz entity engine does a check of the database against the entity model definitions. Part of the check is to verify that the primary keys of all the tables are correctly defined, but the entity engine attempts to obtain the primary key information for all the tables of the database at once, which is not supported by DB2. To make this feature work, you need to modify DatabaseUtil.java to have the entity engine check the primary keys one table at a time.
3. The biggest problem with DB2 is that it does not support SELECT operations which include CLOB/BLOB fields when the ResultSet is scroll insensitive (See [1].) The solution is not as simple as just changing the result set type, because DB2 also does not support (i) SELECT operations on views or with JOIN using scroll sensitive cursor or (ii) moving around with .absolute(i) or .relative(i) operations on a ResultSet of TYPE_FORWARD_ONLY. This means that the view entities which include CLOB/BLOB types cannot be SELECTED (because you cannot use a scroll insensitive ResultSet), or that the EntityListIterator.getPartialList method will not work (because you cannot use .absolute and .relative), so the ofbiz form widget's list form will not paginate correctly. There is no solution for this problem, but the following workarounds exist:
   1. Since the majority of the large object (LOB) types are CLOB for long character strings, you can redefine the field type for your blob and very-long to be the longest possible VARCHAR instead of using CLOB.
   2. You can avoid using the getPartialList feature and instead use findAll or findByAnd to return a Java list, and then use the sublist() method on it. These queries are done with TYPE_FORWARD_ONLY and return the entire list at once, but the drawback is that a Java list has a limited capacity of about 65,000 records.
   3. If neither of these workarounds are acceptable, you would have to rewrite certain features (like surveys with long text responses) to conform to DB2's restrictions.

In practice, most ERP-related uses of opentaps would not require CLOB's, so the first option should suffice. Only with content management features would such field types be required, and those features would need to be rewritten for DB2 compatibility.