Resolving Amazon RDS for MariaDB Connection Issues in Spotfire Server

Description: Spotfire Server fails to connect to Amazon RDS for MariaDB databases, displaying "Unknown error" messages, even when MySQL ODBC connections work locally and basic connectivity tests are successful.

Solution:

When Spotfire Server cannot connect to Amazon RDS for MariaDB despite successful local connections and network connectivity, the issue is typically caused by missing or incompatible database drivers on the server.

Troubleshooting Steps:

1. Verify Driver Requirements:
   • Confirm that MariaDB ODBC BI connector is installed on the Spotfire server
   • Check that the driver version is compatible with your MariaDB version
   • Ensure both 32-bit and 64-bit drivers are installed if needed
2. Test Connection Methods:
   • Test the connection using Spotfire Analyst Client directly on the server machine
   • Verify ODBC System DSN connectivity from the server
   • Compare working local machine driver configurations with server setup
3. Install Missing Connectors:
   • Download and install the MariaDB ODBC BI connector on the Spotfire server
   • Restart Spotfire services after driver installation
   • Test connectivity through both Analyst Client and Web Player
4. Driver Maintenance:
   • Keep database drivers updated to match database server versions
   • Monitor for automatic updates that might remove or modify drivers
   • Document driver versions and configurations for consistency

Resolution Process:

1. Identify the Missing Component: Determine if MariaDB-specific drivers are present
2. Install MariaDB ODBC BI Connector: Download from official MariaDB sources
3. Configure Connection: Set up ODBC data source with proper credentials
4. Test Functionality: Verify connections work in both Analyst Client and Web Player
5. Enable Debug Logging: If issues persist, enable DEBUG logging to capture detailed error information

Prevention:

• Maintain an inventory of required database drivers for each environment
• Test connections after any system updates or patches
• Consider using connection pooling for better reliability
• Document working driver configurations for future reference

Additional Considerations:

• Amazon RDS for MariaDB may require specific driver versions
• Network security settings might affect driver functionality
• Consider AWS-specific connection requirements and best practices

Note: While MySQL ODBC drivers may work for basic MariaDB connections, using the dedicated MariaDB ODBC BI connector ensures optimal compatibility and performance with Amazon RDS for MariaDB services.