Qt MySQL Connection Issues Between Windows and Linux

Basic Database Connection Code

The following code demonstrates a basic database connection implementation:

#include "sqlinit.h"
#include <QMessageBox>
#include <QStringList>

SQLinit::SQLinit()
{
    // Add a database connection
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");    // Specify database type
    
    db.setHostName("192.168.xxx.xxx"); // Set database host IP
    db.setPort(3306);
    
    db.setUserName("root");
    db.setPassword("123456");    // MySQL password set during installation
    
    db.setDatabaseName("database");
    
    db.open();
    // Uncomment to display error messages:
    // QMessageBox::warning(this, "warning", db.lastError().text());
    // qDebug() << "Connection failed, error: " << db.lastError().text();
}

Issue 1: Qt Driver Problems

Error message:

QSqlDatabase: QMYSQL driver not loaded
QSqlDatabase: available drivers: QSQLITE QODBC QODBC3 QPSQL QPSQL7
"Driver not loaded"

Cause Analysis:

The Qt MySQL driver is missing. You need to compile two DLL files using Qt environment and MySQL header/library files. This applies when Qt runs on Windows regardless of whether MySQL is on Windows or Linux. If Qt is on Linux, you would need to compile SO files instead.

You need MySQL installed on Windows (or at least the library files) and MySQL installed on Linux.

Solution Steps:

1. Open the mysql.pro file in your Qt installation directory (requires source code; if unavailable, reinstall Qt quickly)
2. Comment out and add the following:

mysql.pro code:

TARGET = qsqlmysql

HEADERS += $$PWD/qsql_mysql_p.h
SOURCES += $$PWD/qsql_mysql.cpp $$PWD/main.cpp

#QMAKE_USE += mysql

OTHER_FILES += mysql.

SQL_P = sql
PLUGIN_CLASS_NAME = QMYSQLDriverPlugin
include(../qsqldriverbase.pri)

INCLUDEPATH += "D:/mysql/include"
LIBS += "D:/mysql/lib/libmysql.lib"
DESTDIR = "D:/mysql"

3. Open qsqldriverbase.pri and add comments:

qsqldriverbase.pri source code:

QT += core core-private sql-private

# For QMAKE_USE in the parent projects.
#include($$shadowed($$PWD)/qtsqldrivers-config.pri)
include(./configure.pri)

PLUGIN_TYPE = sqldrivers
load(qt_plugin)

DEFINES += QT_NO_CAST_TO_ASCII QT_NO_CAST_FROM_ASCII

4. Compile the project
5. Navigate to the driver output directory and copy qsqlmysql.dll and qsqlmysql.dll.debug to the Qt installation's sqldrivers folder
6. Copy libmysql.lib from MySQL to Qt's bin directory

These steps should resolve Qt-level driver issues. If problems persist, restart your computer to ensure Qt detects the MySQL driver.

Issue 2: MySQL Server Problems

Common error messages:

telnet: Unable to connect to remote host: Connection refused
or
MySQL 2003 - Can't connect to MySQL server on 'localhost' (10038)

Solution Steps:

1. Ensure user connection permissions allow connection from any IP. For root user:

use mysql;
select host, user from user;
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; -- Password change optional
update user set host='%' where user='root';
select host, user from user;
flush privileges;
quit;

2. Modify MySQL's mysql/my.cnf file, setting bind-address = 0.0.0.0:

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

3. Restart MySQL:

sudo systemctl restart mysql

4. Check if port 3306 is open by running:

iptables -vnL | grep 3306

If no data appears, port 3306 is not open. If it shows "drop" status, you need to modify the firewall settings:

sudo /sbin/iptables -I INPUT -p tcp --dport 3306 -j ACCEPT

Verify the port status again:

sudo iptables -vnL | grep 3306