會員註冊 / 登入  |  電腦版  |  Jump to bottom of page

User Forum » trouble connecting to mysql

發表人: philfrei
5 年 前
I'm having trouble connecting to MySQL. My goal is to make a local test version on my laptop (Windows 10, OpenJDK 11, MySQL 8) before running a more advanced test on a live website.

My login process (Windows 10, from CMD):

c:\>mysql -u philfrei -p password


From here a SHOW DATABASES; command demonstrates that "jgo" exists.


Here is the info from the JForum confirmation page.

1) General Configuration
Default board language: en_US
Database Type: mysql
2) Database Configuration
Database Server Hostname: localhost:3306
Database name: jgo
Database Username: philfrei
Database Password: password
Database Encoding: utf-8
Use Connection Pool: no
3) Board Configuration
Forum Link: http://localhost:8080/jforum-2.6.0-jetty/
Website Link: http://localhost:8080
Administrator Username: Admin
Administrator Password: admin

Here is the error message:

Error while trying to configure the database. Please check if parameters like username, password and host are correct and try again.

Error raised:
net.jforum.exceptions.DatabaseException: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.


Any suggestions on how to troubleshoot this?

Thanks!

發表人: udittmer
5 年 前
I was hoping to reproduce the issue, but was able to install and proceed smoothly. Only difference was that I used the "root" user with no password (on OS X, OpenJDK 8, MySQL 5.1).

What does "show grants;" display within MySQL? I vaguely recall MySQL being finicky about which user is allowed to connect via the DB protocol and which user is allowed to log into the DB via the CLI.

發表人: philfrei
5 年 前
When entering MySQL via user "philfrei", the grants are as follows:

mysql> SHOW GRANTS;

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for philfrei@% |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO `philfrei`@`%` WITH GRANT OPTION |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)



When entering as root:
c:\>mysql -u root -p password


mysql> SHOW GRANTS;

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION |
| GRANT APPLICATION_PASSWORD_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.13 sec)



I tried the following, all giving the same error:


Database Server Hostname Username Password
------------------------ ----------- -----------
localhost root password
localhost root (no password)
root@localhost root password
root@localhost root (no password)
localhost root@localhost password
localhost root@localhost (no password)
root@localhost root@localhost password
root@localhost root@localhost (no password)


I vaguely recall MySQL being finicky...


Is there something else that could cause this, like using UTF-8 when it is not appropriate? (How do I find out if I am encoding in UTF-8 or not?)

I checked the command prompt and there are no Java errors being displayed.

I'm wondering, is there code for logging into MySQL in JForum that I could inspect and isolate and test? I'm using Eclipse. Maybe I could write a simple GUI to attempt logging in, and see if that is successful or if I can generate more distinct error messages.

發表人: philfrei
5 年 前
Additional testing.

I am able to access the MySQL database in Java using the following:

Class.forName("com.mysql.jdbc.Driver");


String url = "jdbc:mysql://localhost:3306/jgo";
String user = "root";
String password = "password";

Connection con = DriverManager.getConnection(url,
user, password);

Statement statement = con.createStatement();
ResultSet rs = statement.executeQuery("SHOW DATABASES;");
while (rs.next())
{
System.out.println(rs.getString(1));
}


To me, this implies that the following should work (but doesn't):

Hostname: localhost
User: root
Password: password

However, I DID have to first install a driver (external lib jar, from MySQL "Connector/J", because when I first tried to execute this code,
I got a ClassNotFoundException for "com.mysql.jdbc.Driver".

Is the error possibly due to not having this driver available?
JForum's WEB-INF/lib does have an older driver, but this older driver (5.1..., not 8.0...) also works in my Java code.
So that doesn't seem to be the issue, either.

Again, thanks for any assistance!

發表人: udittmer
5 年 前
Both driver versions are fine; see https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-versions.html. Version 5.1 is shipped with JForum, so nothing needs to be installed. We'll switch to 8.x for the next JForum release, given that Java 8 is required anyway now.

發表人: philfrei
5 年 前
That both drivers are fine was my assumption as well. The 5.1... driver worked from the test program I wrote in my Java IDE.

Instead of persisting in trying to solve this problem, I installed PostgreSQL 11. I was able to get a working forum.

It occurs to me, the problem could have been something really simple, like having made a too-clever-by-half password for my root user.
It included chars "*" and ")" as well as numbers and caps. But I'm not ready to reinstall MySQL just to test this theory!

發表人: udittmer
5 年 前
 
It occurs to me, the problem could have been something really simple, like having made a too-clever-by-half password for my root user.
It included chars "*" and ")" as well as numbers and caps.

That is an interesting thought. I don't think that would cause problems, but I'll check to make sure.




會員註冊 / 登入  |  電腦版  |  Jump to top of page