/geoportal-server

Database problems

ZhongPing Guo edited this page Jul 2, 2013 · 3 revisions
Clone this wiki locally

Below are steps for general troubleshooting for database issues. Specific information about the database tables and columns within them can be found in the Database Tables topic. 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).

Table of Contents

General Database Troubleshooting

"Cannot create PoolableConnectionFactory..." or "Cannot load JDBC driver class..."

Things to check:

  • If using Tomcat, verify that settings are correct in the \\TOMCAT\conf\Catalina\localhost\geoportal.xml file. Doublecheck that the correct driverClassName, jdbc url, username and password are correct and have been entered without extra spaces, characters, or missing quotation marks.
  • Verify that the correct jdbc driver for your database has been copied to the correct location in your servlet container. See the jdbc database driver table in the Geoportal Installation Guide (Installation topic) for which drivers to use with each database and guidance where to copy the driver. If you're using GlassFish or WebLogic, look at the respective geoportal installation guides for those environments.
  • 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 Tomcat), 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.

SQL Server 2005 and 2008

  • Verify that TCP/IP is turned on as a protocol.
    • Launch SQL Server Configuration Manager from Start menu > Microsoft SQL Server 200x > Configuration Tools > SQL Server Configuration Manager.
    • 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.
    • Follow same steps above to select TCP/IP from the SQL Server Configuration Manager.
    • Right-click on TCP/IP, and select Properties.
    • 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.
    • 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 authentication. If your server only allows Windows authentication, then the user that the geoportal database 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%28v=SQL.100%29.aspx), do the following:
    • Login to SQL Server Management Studio.
    • In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
    • 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.
    • 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

  • 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. For example, 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 PostgreSQL Client Authentication documentation.
Back to Common problems and solutions