Troubleshooting the Database

Troubleshooting Database Issues

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

General Database Troubleshooting

• Error: "Cannot create PoolableConnectionFactory..." or "Cannot load JDBC driver class...": Things to check:
  1. If using Tomcat, verify that settings are correct in the geoportal.xml file found in the \\TOMCAT\conf\Catalina\localhost folder. Doublecheck that the correct driverClassName, jdbc url, username and 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 has been copied to the correct location in your servlet container. If using Tomcat, see the jdbc database driver table in Section 8 of the Geoportal Installation Guide for which drivers to use with each database and guidance where to copy the driver. For other servlet application software, see the respective installation guides for those environments, found in your <Geoportal distribution directory>\Documentation\Installation folder.
  3. Verify the port that your database is using. If the port is different from the port stated in jdbc connection information (e.g., the geoportal.xml file for Tomcat0, 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 from 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.
  4. If your port is dynamically assigned, then you need to change this setting in your database software such that it is not dynamically assigned, and then use the designated port for your jdbc connection information.

• Verify that your server allows both Windows authentication and SQL Server authenticationIf 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, see 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 database scripts are run in a compressed file environment, errors will result.

PostgreSQL

• 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.