Troubleshooting the Database

Troubleshooting the Database

Below are steps for general troubleshooting for database issues. Specific information about the database tables and columns within them can be found in the Geoportal Database Tables section. If the general items below do not help solve your problem, then look at the specific items to check for your type of database system software:

• SQL Server 2005 and 2008
• PostgreSQL 8.3

• Error Messages: "Cannot create PoolableConnectionFactory..." or "Cannot load JDBC driver class..."
  
  
  
  Things to check:
  1. Settings are correct in the geoportal.xml file (<TOMCAT>\conf\Catalina\localhost folder). Doublecheck that the correct driverClassName, jdbc url, and username/password are correct and have been entered without extra spaces, characters, or missing quotation marks.
  2. Verify that the correct jdbc driver for your database from the <TOMCAT>\webapps\geoportal\WEB-INF\lib folder has been copied to the common Tomcat lib folder, and unused drivers for other databases have been deleted from the <TOMCAT>\webapps\geoportal\WEB-INF\lib folder. Table below maps drivers to RDBMS:

     RDBMS	jdbc driver
     Oracle	ojdbc5.jar
     SQL Server 2005 or 2008	sqljdbc.jar (JRE 5) or sqljdbc4.jar (JRE 6), depending on JRE version
     PostgreSQL	postgresql-8.3-603.jdbc3.jar

  3. Verify the port that your database is using. If the port is different from the port stated in your geoportal.xml file, or if your database is dynamically assigning the port number, then change dynamically assigned ports to a static setting and edit geoportal.xml to reflect the port that is assigned.
     
     
     
     A useful tool for reporting port status is a command-line utility called PortQry.

SQL Server 2005 and 2008

• Verify that TCP/IP is turned on as a protocol
  1. Launch SQL Server Configuration Manager (Start menu > Microsoft SQL Server 200x > Configuration Tools > SQL Server Configuration Manager.
  2. Enable TCP/IP protocol by choosing from the tree SQL Server Network Configuration, then Protocols for MSSQLSERVER. Right-click on TCP/IP and select "Enable".
• Verify that the correct Port is assigned for TCP/IP
  1. Follow same steps above to select TCP/IP from the SQL Server Configuration Manager.
  2. Right-click on TCP/IP, and select "Properties"
  3. Click on the "IP Addresses" tab. Scroll through the listed IP's, and verify the port number assignment for the database host machine and the TCP Port. This port should be used in the geoportal.xml file for the jdbc url. IMPORTANT: If the port is dynamically assigned, then change the setting so it is not dynamically assigned.
• Verify that your server allows both Windows authentication and SQL Server authentication
  
  If your server only allows Windows authentication, then the user that the script creates will not be able to logon to create the tables. The error in the build_schema.log file will read 'Login failed for user'. To check and possibly change the security authentication mode (as per Microsoft, article http://msdn.microsoft.com/en-us/library/ms188670.aspx ), do the following:
  1. Login to SQL Server Management Studio.
  2. In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
  3. On the Security page, under Server authentication, select the "SQL Server and Windows Authentication Mode" radial if it is not already selected, and then click OK.
  4. In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.
• Verify that SQL Server is not running on a compressed drive
  
  According to the Microsoft Developer Network support site, it is no longer possible to create a read/write database on a compressed file system as of the SQL Server 2005 release. If the Geoportal extension database scripts are run in a compressed file environment, errors will result.

PostgreSQL 8.3

• TCP/IP Information has not been declared
  
  
  
  Verify that the TCP/IP is specified as a parameter in the pg_hba.conf file. You will need to enter a new parameter at the end of the file.
  
  
  
  The syntax is as follows:

  # TYPE	DATABASE	USER	CIDR-ADDRESS	METHOD
  host	all	all	<database server IP Address>/32	trust

• Listen_addresses have not been changed from the default 'localhost'
  
  In the postgresql.conf file you will have to update the listen_addresses section under the connection settings section to enable what IP addresses to listen on.
  
  
  
  e.g. listen_addresses = "*" (* indicates all)
  
  
  
  Changes to the pg_hba.conf and the postgresql.conf files will require a restart of the postgres service and the servlet engine (Tomcat, Glassfish or Weblogic).
  
  
  
  For more information on the pg_hba.conf or the postgresql.conf files please see the Client Authentication section in the PostgreSQL documentation.