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:

General Database Troubleshooting

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

8/6/2012