0

We use java application client to transfer database table data to a third party server. With MySQL 5.7 it was working fine. After upgrade to MySQL 8, it started to show errors. First thing we did was to change the MySQL jar file to mysql-connector-java-8.0.30.jar. Even then we are not getting connected.

We are however able to connect via PHP and login via MySQL command line.

Server Details:

  • Ubuntu 18.04
  • RAM: 16 GB
  • mysql Ver 8.0.30 for Linux on x86_64 (MySQL Community Server - GPL)

The following are the errors shown:

Note: Example.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
Cannot connect to database server
Could not create connection to database server. Attempted reconnect 3 times. Giving up.
java.sql.SQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
        at com.mysql.cj.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:898)
        at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:823)
        at com.mysql.cj.jdbc.ConnectionImpl.(ConnectionImpl.java:448)
        at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:241)
        at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198)
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)
        at Example.main(Example.java:16)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:566)
        at jdk.compiler/com.sun.tools.javac.launcher.Main.execute(Main.java:404)
        at jdk.compiler/com.sun.tools.javac.launcher.Main.run(Main.java:179)
        at jdk.compiler/com.sun.tools.javac.launcher.Main.main(Main.java:119)
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
        at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
        at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
        at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)
        at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151)
        at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167)
        at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:89)
        at com.mysql.cj.NativeSession.connect(NativeSession.java:120)
        at com.mysql.cj.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:842)
        ... 14 more
Caused by: java.net.ConnectException: Connection refused (Connection refused)
        at java.base/java.net.PlainSocketImpl.socketConnect(Native Method)
        at java.base/java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:412)
        at java.base/java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:255)
        at java.base/java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:237)
        at java.base/java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
        at java.base/java.net.Socket.connect(Socket.java:609)
        at com.mysql.cj.protocol.StandardSocketFactory.connect(StandardSocketFactory.java:153)
        at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:63)
        ... 16 more

Please find the MySQL my.cnf settings.

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
skip-grant-tables


# Drupal Specific Settings
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
character_set_server = utf8mb4
default-storage-engine=InnoDB
skip-name-resolve=1
#default_authentication_plugin=mysql_native_password
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1
#
# * Fine Tuning
#
# Obsolete key_buffer option renamed to key_buffer_size by maintainer script
key_buffer_size         = 128M
max_allowed_packet      = 64M
thread_stack            = 192K
thread_cache_size       = 8

# Additional Fine Tuning Settings for Drupal and Performance
performance_schema
#read_rnd_buffer_size = 8M
#myisam_sort_buffer_size = 8M
max_user_connections = 100
join_buffer_size = 8M
read_buffer_size = 4M
sort_buffer_size = 4M
thread_cache_size = 4
interactive_timeout = 300
wait_timeout = 300
connect_timeout = 100
max_connect_errors = 10000
tmp-table-size = 64M
max-heap-table-size = 64M
open_files_limit = 40000
max_execution_time=240000 # in milliseconds. 4 minutes
skip-character-set-client-handshake ## Do not check for character set
#ssl=0

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
# Obsolete myisam-recover option renamed to myisam_recover_options by maintainer script
myisam_recover_options  = BACKUP
max_connections        = 200
table_open_cache        = 100000
table_definition_cache = 12000
#thread_concurrency     = 10
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries       = /var/log/mysql/mysql-slow.log
slow-query-log-file   = /var/log/mysql/mysql-slow.log
long_query_time = 5
#log-queries-not-using-indexes = 1
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id              = 1
#log_bin                        = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
#max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

[mysqldump]
quick
quote-names
max_allowed_packet      = 64M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
# Obsolete key_buffer option renamed to key_buffer_size by maintainer script
key_buffer_size         = 128M

[mysqld]
# InnoDB Settings
innodb_buffer_pool_size=6G
innodb_flush_log_at_trx_commit=2
#Set the log file size to about 25% of the buffer pool size
innodb_log_file_size=1G
innodb_log_buffer_size=64M
innodb_flush_method=O_DIRECT
innodb_file_per_table=1
innodb_thread_concurrency=64
#innodb_buffer_pool_instances=8
innodb_stats_on_metadata=0
innodb_lock_wait_timeout=100
#innodb_write_io_threads=8  #To stress the double write buffer
transaction-isolation=READ-COMMITTED

# Database 4 byte UTF-8 support
innodb_file_per_table=true

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

We have tried various suggestions provided in the internet for the MySQL error: Communications link failure

The following things we tried but could not get it working:

  • -Djava.net.preferIPv4Stack=true
  • tcpKeepalive=true
  • JDBC URL from localhost to 127.0.0.1: jdbc:mysql://127.0.0.1:3306/mydatabase?tcpKeepalive=true

Please find the MySQL user list:

mysql> SELECT Host,User,Plugin FROM mysql.user;
+-----------+------------------+-----------------------+
| Host      | User             | Plugin                |
+-----------+------------------+-----------------------+
| 127.0.0.1 | root             | mysql_native_password |
| ::1       | root             | mysql_native_password |
| li1003-25 | root             | mysql_native_password |
| localhost | debian-sys-maint | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | mysql_native_password |
| localhost | mysql.sys        | mysql_native_password |
| localhost | root             | mysql_native_password |
+-----------+------------------+-----------------------+

Please share what additional info can be provided to help identify the issues. Thanks.

1 Answer 1

0

The setting skip-grant-tables disables TCP connection in MySQl 8.0 I had to disable that first. This made MySQL listen on default port 3306 via TCP connection.

Additionally there were concerns on certificates when connecting via JDBC. Specifying the query string useSSL=false in JDBC connection string resolved the certificate problem.

These 2 setting changes resolved the issue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.