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.
