Troubleshooting Database Connector | MuleSoft Documentation
# Troubleshooting Database Connector
To troubleshoot Anypoint Connector for Database (Database Connector), become familiar with the information about enabling verbose logging, troubleshooting pooling profile connections, troubleshooting Snowflake JDBC driver, and interpreting commonly thrown messages.
Database Connector follows the standards and guidelines detailed by the JDBC library (working with Java SE JDK 1.8).
## Enable Verbose Logging
To get a better understanding of why an application’s interaction with the database is failing, temporarily enable verbose logging for Database Connector. Choose one of two options to enable verbose logging: manually change the `log4j2.xml` file or via Anypoint Runtime Manager if you are using CloudHub APIs. Remember to always disable the enhanced verbosity after troubleshooting because it affects your Mule application performance.
To enable verbose logging in the configuration file:
  1. Access Anypoint Studio and navigate to the **Package Explorer** view.
  2. Open your application’s project name.
  3. Open the `src/main/resources` path folder.
  4. Open the `log4j2.xml` file inside the folder.
  5. Add an `<AsyncLogger>` tag inside the `<Loggers>` tag:
```
			<Loggers>
				...
				<AsyncLogger name="org.mule.extension.db" level="DEBUG"/>
				<AsyncLogger name="org.mule.db.commons" level="DEBUG"/>
				...
			</Loggers>
```

  6. Save your application changes.
  7. Click the project name in **Package Explorer** and then click **Run** > **Run As** > **Mule Application**.


To enable verbose logging in Anypoint Runtime Manager:
  1. Access Anypoint Runtime Manager.
  2. Navigate to the API configuration.
  3. In the **Logging** subtab, add the `org.mule.extension.db` and `org.mule.db.commons` packages at DEBUG level.
  4. Apply the changes.


## Troubleshoot Pooling Profiles Connections Making the Flow Hang
Using a pooling profile that provides fewer connections than the flow requires can make the entire flow hang.
To resolve this issue:
One solution is to increase the size of the pool. When that’s not possible, use Anypoint Connector for Virtual Machine (VM Connector) to shorten the life of the connection to match the life of a VM message. This way, every call to the **Stored procedure** operation is executed, its result is consumed, and the connection associated with the operation is closed and returned to the pool.
The following example shows how to use pooling profiles with the **Stored procedure** operation and VM Connector. In this example, When the `foreach` element reaches the eleventh iteration, the **Stored procedure** operation doesn’t hang, because the pool is able to provide a connection:
```
	<db:config name="Database_Config">
		<db:generic-connection url="someUrl" user="someUser" password="somePassword" driverClassName="someDriver">
			<db:pooling-profile maxPoolSize="10">
			</db:pooling-profile>
		</db:generic-connection>
	</db:config>
	<vm:config name="VM_Config">
		<vm:queues >
			<vm:queue queueName="testQueue" />
		</vm:queues>
	</vm:config>
	<flow name="processingFlow">
		<foreach collection="#[1 to 11]">
			<logger level="INFO" message="#['Starting Iteration ' ++ payload as String]" />
			<vm:publish config-ref="VM_Config" queueName="testQueue"/>
			<logger level="INFO" message="#['Finishing Iteration ' ++ payload as String]"/>
		</foreach>
	</flow>
	<flow name="coolFlow">
		<vm:listener config-ref="VM_Config" queueName="testQueue" numberOfConsumers="1"/>
		<db:stored-procedure config-ref="Database_Config">
				<db:sql><![CDATA[{call testProc2()}]]></db:sql>
			</db:stored-procedure>
		<logger level="INFO" message="#[output application/json --- payload.resultSet1]" />
	</flow>
```

## Understand Select and Stored Procedure Operations Warning Exceptions
When the Database Connector **Select** and **Stored procedure** operations execute correctly but do not return results, Mule runtime engine (Mule) returns a `WARN` exception message instead of throwing the exception.
For the **Select** operation, the behavior occurs when executing an invalid SQL statement, and for the **Store procedure** operation, when there is an error within a set of statements that can collapse the procedure. In fact, JDBC returns a series of `ResultSets`, `UpdateCounts`, and `Exceptions`.
Mule does not throw an exception for the flow to catch it because the root cause is in the driver’s behavior. Some drivers comply strictly with JDBC and properly throw an exception at execution time, but other drivers do not. Therefore, adding in the connector code the behavior of throwing an exception would break backward compatibility for Mule apps that do not expect an exception.
For the **Select** operation, Mule logs a `WARN` exception when a subquery returns more than one value and that result is used for filtering or as an expression. Mule returns the warning as soon as the operation’s result is paginated and not when it’s executed.
For example, for the following SQL statement:
```
		<db:select doc:name="Select" config-ref="Database_Config">
			<db:sql ><![CDATA[
						SELECT *
						FROM SomeTable
						WHERE SomeColumn = (SELECT DISTINCT SomeColumn2 from SomeTable2)
					 ]]>
			</db:sql>
		</db:select>
```

Mule logs the following `WARN` exception:
```
	WARN 2021-06-01 10:04:33,323 [[MuleRuntime].io.10: [some-mule-app].some-mule-app.BLOCKING @...] [event: ...] org.mule.extension.db.internal.result.resultset.ResultSetIterator: Unable to determine if there are more records
	com.microsoft.sqlserver.jdbc.SQLServerException: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
```

For the **Store procedure** operation, Mule logs a `WARN` exception when a stored procedure contains multiple statements and one or more of them fail. Mule returns the warning as soon as the operation’s result is consumed and not when it’s executed. For example, for the following SQL Stored Procedure:
```
CREATE PROCEDURE dbo.SomeProcedure
AS
BEGIN
	BEGIN TRY
	select 1/0;
	END TRY
	BEGIN CATCH
	THROW 50000, 'Divide by zero error encountered.', 255;
	END CATCH
END
```

Mule logs the following `WARN` exception:
```
	WARN 2021-06-02 09:38:14,624 [[MuleRuntime].io.10: [some-mule-app].some-mule-app.BLOCKING @...] [event: ...] org.mule.extension.db.internal.result.statement.StatementResultIterator: Unable to determine if there are more statement results
	com.microsoft.sqlserver.jdbc.SQLServerException: 'The record does not exist.'
```

## Troubleshoot Snowflake JDBC Driver That Throws an Exception for a Stored Procedure
Some drivers have their own specifications when calling stored procedures. Running the application when you set the callable statement with curly brackets (`{` and `}`), for example, `{call doubleMyInt(:myInt)}`, causes the Snowflake JDBC driver to throw the following exception:
`net.snowflake.client.jdbc.SnowflakeSQLException: Unsupported feature 'call'`
To resolve this issue:
For Snowflake JDBC driver, set the callable statement without the curly brackets `{` and `}`, for example:
`call doubleMyInt(:myInt)`
## Troubleshoot CloudHub with Static IP Failure on Deployment
Deploying a CloudHub application with a static IP throws a database test connection error making the process fail. This occurs because the test is performed before the static IP gets assigned.
The following stack trace shows the test connection error `Could not obtain connection from data source`:
```
[2021-03-05 00:26:11.262] INFO  org.mule.runtime.module.extension.internal.runtime.config.LifecycleAwareConfigurationInstance [[MuleRuntime].uber.02: [iagsatellite-snowflake-sys-api-v1-dev].uber@org.mule.runtime.module.extension.internal.runtime.config.LifecycleAwareConfigurationInstance.testConnectivity:179 @10f9d7dc]: Connectivity test failed for config 'Database_Config'. Application deployment will continue. Error was: Could not obtain connection from data source
org.mule.runtime.api.connection.ConnectionException: Could not obtain connection from data source
```
To resolve this issue:
Set the following system property to the deployment `-DdoTestConnectivity=false`.
## Troubleshoot Oracle’s XML Extensions using System Properties
To use Oracle XML extensions, you must add Oracle jar files as dependencies. One of these jar files is `xmlparserv2.jar`, which exports XML parser capabilities via the Java SPI (Service Provider Interface). When internal parts of Mule use this parser, it can break some Mule components' expectations and cause XML parsing errors, such as the following:
```
	<Line 43, Column 57>: XML-24509: (Error) Duplicated definition for: 'identifiedType' <Line 60, Column 28>: XML-24509: (Error) Duplicated definition for: 'beans' <Line 140, Column 34>: XML-24509: (Error) Duplicated definition for: 'description'
```

To deal with this issue, do either of the following:
  * Add the `xmlparserv2.jar` file as an additional plugin dependency in the Database Connector, so the dependency is visible only from within the connector. Though, that won’t work if you need to share an Oracle connection among modules, which is the case if you want to provide a DataSource as a Spring configured bean.
  * Disable the XML parser SPI lookup by setting the following system properties, so Mule internal components always get the XML parser they expect:


System Properties:
```
-Djavax.xml.parsers.SAXParserFactory=com.sun.org.apache.xerces.internal.jaxp.SAXParserFactoryImpl
-Djavax.xml.parsers.DocumentBuilderFactory=com.sun.org.apache.xerces.internal.jaxp.DocumentBuilderFactoryImpl
```

These properties avoid any trouble if, for instance, you need to set up Oracle .jars as shared libraries.
For more information about how to set system properties, refer to the system properties documentation.
## Troubleshoot TCP Keep Alive for Oracle Driver
If a query or stored procedure takes more than 15–30 minutes to execute, the firewall or network device might close the connection.
To prevent this, set the TCP Keep Alive for the Oracle provider by adding this flag as a JVM parameter:
```
-Doracle.net.keepAlive=true
```

This flag ensures the connection remains active.
## Understand Common Throws
Here is a list of common throws messages and how to interpret them.
  * DB:BAD_SQL_SYNTAX
```
The provided SQL query contains invalid syntax.
```

  * DB:CANNOT_LOAD_DRIVER
```
Database Connector cannot load the JDBC driver.
```

  * DB:CANNOT_REACH
```
Database Connector cannot establish a connection with the RDBMS.
```

  * DB:CONNECTIVITY
```
Any of several possible connectivity issues, including poorly configured connections, lack of a database response while waiting for results or fetching rows, loss of database connection, and so on.
```

  * DB:INVALID_DATABASE
```
The connection to the RDBMS is established but the database doesn't exist.
```

  * DB:INVALID_CREDENTIALS
```
The database rejects the provided credentials.
```

  * DB:QUERY_EXECUTION
```
The query execution failed.
```

  * DB:RETRY_EXHAUSTED
```
All reconnection attempts failed.
```



## See Also
  * MuleSoft Help Center
  *