JIRA Cannot Connect to MySQL with Named Pipes Enabled

JIRA can't connect to the database with Named Pipes enabled

I've tried a number of things, and it looks like named pipes is the problem. This is a problem with MySQL, not with JIRA. Essentially I've had to install MySQL with two key things:

    * Go through the Standard Installation route for MySQL, not the Detailed Installation route
    * Enable TCP/IP connections in the MySQL Config Wizard afterwards

After doing this, JIRA now appears to connect to the MySQL and can see the new database

Details of what I did to recover MySQL after installing it using named pipes:

MySQL Installation and Config:

• Install MySQL server 5.0 release 5.0.24a (see http://dev.mysql.com/doc/refman/5.0/en/windows-installation.html and http://dev.mysql.com/tech-resources/articles/securing_mysql_windows.html)
• Select typical install
• Configure MySQL with the Configuration Wizard
  o Detailed Configuration
  o Server Machine
  o Multifunctional Database
  o Chose C:\<Installation path> for the InnoDB tablespace
  o Decision Support DSS/OLAP
  o Disable TCP/IP networking for security and Enable Strict Mode
  o UTF-8 character set
  o Install as Windows Service
  o Include MySQL /bin directory on path - allows mysql* commands to be run directly
  o Choose root password: *********** Do not allow access from remote machines
• Execute configuration
  o Config OK - my.ini
  o Service started - mySQL
  o Security setting FAILED - error 2017. Can't open named pipe to host: .pipe:mysql(2)
• Create a my.cnf with (client) host=localhost in it, as per http://mysqld.active-venture.com/Windows_vs_Unix.html. Also edit my.ini to have the same line under (client). Don't know if this will work. Named pipes may be a problem.
• Hmm.. There's a suggestion that the Detailed Configuration method just doesn't work (http://forums.mysql.com/read.php?11,80814,93616). If I can't get JIRA to connect to MySQL it might be necessary to re-install the whole thing...
• Create MySQL database and user for JIRA to use. In a command shell run:
  o mysql -u root
  o CREATE DATABASE jiradb CHARACTER SET 'utf8';
  o show databases;
  o CREATE USER 'jirauser'@'localhost' IDENTIFIED BY '****'; (where **** is jirauser's password)
  o GRANT ALL PRIVILEGES ON jiradb.* TO 'jirauser'@'localhost';
  o quit;
  o mysql -u jirauser -p
  o <enter password>
  o show databases;
  o jiradb is listed as one of the databases
  o quit;

JIRA configuration to use MySQL:

• Download JDBC driver mysql-connector-java-3.1.12.zip
• Copy the mysql-connector-java-3.1.12-bin.jar file from this zip to C:\Jira\atlassian-jira-professional-3.6.5-standalone\common\lib
• Edit the conf/server.xml file
  o username and password for the jirauser account set up above
  o driverClassName="com.mysql.jdbc.Driver"
  o url="jdbc:mysql://localhost/jiradb?autoReconnect=true&useUnicode=true&characterEncoding=UTF8"
  o delete the minEvictableIdleTimeMillis and timeBetweenEvictionRunsMillis parameters
• Edit the entityengine.xml file and change the field-type-name to mysql

Re-start JIRA to use MySQL database

• run C:\jira\atlassian-jira-professional-3.6.5-standalone\bin\shutdown
  o Tomcat web-server shuts down
• run C:\jira\atlassian-jira-professional-3.6.5-standalone\bin\startup
  o Get error on connection: Unable to establish connection with the database. I suspect this is because the database wasn't set up correctly above and can't open named pipes. This is probably the issue with WinNT-based systems not being able to support named pipes (without modification).
• run C:\jira\atlassian-jira-professional-3.6.5-standalone\bin\shutdown

Re-installing MySQL without named pipes

• Go to Control Panel->Add/Remove Programs and remove MySQL
• Delete C:\Program Files\MySQL
• Reinstall as above (typical installation)
• Configure and select "Standard Installation"
  o Install as Windows Service
  o Add \bin to path
  o Choose root password ************
• Execute configuration.
  o Success!
• Run MySQL Config Wizard. Choose options as above.
• Execute configuration
  o Success!
• Set up MySQL database and jira user as above.
• run C:\jira\atlassian-jira-professional-3.6.5-standalone\bin\startup
  o FAILED! Tomcat starts and shuts down immediately. Looking at the logs, it seems that the jirauser account has a & in the password, which stuffs the XML.
• log back in to MySQL as root and run:
  o DROP USER 'jirauser'@'localhost';
  o CREATE USER 'jirauser'@'localhost' IDENTIFIED BY '********'; (makeing sure password has no & in it)
  o GRANT ALL PRIVILEGES ON jiradb.* TO 'jirauser'@'localhost';
  o quit;
• Edit conf/server.xml to the new password
• run C:\jira\atlassian-jira-professional-3.6.5-standalone\bin\startup
  o No good. Still won't connect.
• Try re-running MySQL config and this time enable TCP/IP connection over port 3306 (so that we don't have to use named pipes)
• run C:\jira\atlassian-jira-professional-3.6.5-standalone\bin\startup
  o Tomcat server starts! Hurray. We appear to connect in some way, although there are lots of exceptions. Maybe these are due to the first start?
• Point web browser at http://localhost:8080/
  o JIRA config screen appears - good
• Go through the JIRA setup and initial configuration steps as above

http://confluence.atlassian.com/pages/editpage.action?pageId=133186