I was trying to install a Confluence instance in my hosting account. I had gone through the install and setup successfully locally on my Mac. But the setup on the host was failing:

2008-12-21 13:07:28,087 ERROR [TP-Processor8] [atlassian.config.bootstrap.DefaultAtlassianBootstrapManager] getTestDatasourceConnection Couldn't open a connection on Datasource (java:comp/env/jdbc/confluence): org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Server connection failure during transaction. Due to underlying exception: 'java.sql.SQLException: Access denied for user 'foo_user'@'dz64.dailyrazor.com' (using password: YES)'.

** BEGIN NESTED EXCEPTION **

java.sql.SQLException
MESSAGE: Access denied for user 'foo_user'@'dz64.dailyrazor.com' (using password: YES)

STACKTRACE:

java.sql.SQLException: Access denied for user 'foo_user'@'dz64.dailyrazor.com' (using password: YES)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1056)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3376)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3308)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:894)
	at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3808)
	at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1256)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2182)
	at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:729)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:298)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:283)
	at org.apache.tomcat.dbcp.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
	at org.apache.tomcat.dbcp.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:294)
	at org.apache.tomcat.dbcp.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1247)
	at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1221)
	at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
	at com.atlassian.config.bootstrap.DefaultAtlassianBootstrapManager.getTestDatasourceConnection(DefaultAtlassianBootstrapManager.java:403)
	at com.atlassian.confluence.setup.actions.SetupDatasourceAction.execute(SetupDatasourceAction.java:50)

I kept receiving some sort of access error: Access denied for user 'foo_user'@'dz64.dailyrazor.com' (using password: YES)

I tried establishing the database connection manually through DbVisualizer, which worked fine:

jdbc:mysql://domain.com:3306/db_name?autoReconnect=true

I looked more closely at the error and realized that something was appending the username with the individual server my account was being hosted on (i.e., dz64.dailyrazor.com). I had a hard time figuring out who was at fault - Confluence, Tomcat, DBCP, or MySQL.

The source of the problem appeared to be my Tomcat resource definition:

<context path="/wiki" docBase="/path/to/tomcat/confluence-2.10-std/confluence" debug="0" reloadable="true">
	<resource name="jdbc/confluence" auth="Container" type="javax.sql.DataSource"
			 username="foo_user"
			 password="password"
			 driverClassName="com.mysql.jdbc.Driver"
			 url="jdbc:mysql://domain.com:3306/db_name?autoReconnect=true"
			 maxActive="15"
			 maxIdle="7"
			 validationQuery="Select 1" />
</context>

Googling around didn't provide me any answers. I tried replacing "domain.com" in the "url" attribute with "dz64.dailyrazor.com", but this didn't achieve anything. In the end, I figured out that specifying "localhost" was the answer:

url="jdbc:mysql://localhost:3306/db_name?autoReconnect=true"

This is what I was doing on my Mac, but it took me a while to figure out that the same had to be done on my host.