Tag Archives: mysql

MySQL driver compiling method of QT under windows and solutions to abnormal errors

2015-11-20

Recently, in the process of compiling QT’s MySQL driver with MinGW under windows, it was compiled through several twists and turns. In the process of compiling, there are many problems. In order to avoid similar errors in the subsequent driver compilation and facilitate the rapid completion of this driver compilation, the compilation methods are sorted out.

This method is illustrated by a case of my own compiling environment.

1、 Compiling environment

Operating system: win7 64 bit

MySQL service version: mysql-5.5.46-win32

QT version: 4.8.6

Compiling environment: MinGW 64 bit

2、 Compilation steps

（1） Generate libmysql. A file

Because MinGW compiler needs to use “. A” suffix static library file to connect with static library, but according to MySQL database, there is only “. Lib” format static library, so it needs to be converted. Two tools are needed for conversion reimp.exe and dlltool.exe The paths of these two tools need to be configured in the path environment variable. The conversion command is as follows:

First of all, open the CMD command input box and enter the Lib path under the MySQL installation path. For example, my path is: “C:// program files (x86)  MySQL  MySQL server 5.5  lib”.

Next, enter the following commands in turn:

reimp -d  libmysql.lib

dlltool -k -d  libmysql.def  -l libmysql.a

After successful execution, you can view the library file of libmysql. A in this path.

（2） Editor mysql.pro Engineering documents

Find it in the installation path of QT mysql.pro The path of my pro file is: “C:// Qt/4.8.6/SRC/plugins/sqldrivers/MySQL”.

open mysql.pro File, add the following configuration below:

INCLUDEPATH+=”C:/ProgramFiles(x86)/MySQL/MySQL Server 5.5/include/”

LIBS+= -L”C:/ProgramFiles(x86)/MySQL/MySQL Server 5.5/lib/” –llibmysql

The second line can also be written as follows:

LIBS+= “C:/ProgramFiles(x86)/MySQL/MySQL Server 5.5/lib/libmysql.a”

Makefile Makefile.Debug 、 Makefile.Release Three makefile files and others. As shown in the figure below:

（3） Edit makefile file

Take the debug version as an example. open Makefile.Debug File, find LIBS = “XXXX” line and modify it. Because there is a problem with this configuration generated.

The original configuration is as follows:

LIBS = -L”c:\Qt\4.8.6\lib”debug\qsqlmysqld_ resource_ res.o -llibmysql “-LC:/ProgramFiles(x86)/MySQL/MySQL Server 5.5/lib/” -llibmysql -lQtSqld4 -lQtCored4

According to our intention, according to makefile’s syntax rules, we can clearly find the problem. First of all, “L” must be placed outside the path of the configuration library. Second, the – llibmysql command has repetition.

It is revised as follows:

LIBS = -L”c:\Qt\4.8.6\lib”debug\qsqlmysqld_ resource_ res.o -L “C:/Program Files(x86)/MySQL/MySQLServer 5.5/lib/” -llibmysql -lQtSqld4 -lQtCored4

Or it can be modified as follows:

LIBS = -L”c:\Qt\4.8.6\lib”debug\qsqlmysqld_ resource_ res.o “C:/Program Files(x86)/MySQL/MySQL Server5.5/lib/libmysql.a” -lQtSqld4 -lQtCored4

The release version of makefile is modified in the same way.

（4） Execute the make command

Use the mingw32 make command to execute makefile. If no parameter is added, it will be executed by default Makefile.Debug . You can write debug or release or both after mingw32 make command to execute the corresponding version of makefile.

For example, execute debug and release makefile at the same time. The command is as follows:

mingw32-make debug release

After successful execution, you can see qsqlmysqld. A qsqlmysqld.dll Two library files, in the release folder, see qsqlmysql. A qsqlmysql.dll Two library files.

These four library files are the static library and dynamic library driven by MySQL of debug and release versions.

（5） Copy the driver file to the specified directory

Copy the four driver files generated in the previous step to the database driver directory of QT, that is, under the path of “C:: (QT) ﹣ 4.8.6 ﹣ plugins ﹣ sqldrivers”.

（6） Copy libmysql.dll File to specified directory

Install the libmysql.dll Copy the dynamic library file to the bin directory of QT, i.e. “C:// Qt/4.8.6/plugins/sqldrivers”. At this point, use QT to write a program to connect to MySQL database.

3、 Test whether the driver is available

Write demo program to test QT driver. The main codes are as follows:

#include <QtCore/QCoreApplication>  

#include <QDebug>  

#include <QStringList>  

#include <QString>  

#include <QSqlDatabase>  

int main(int argc, char *argv[])  

{  

    QCoreApplication a(argc, argv);  

    qDebug()<<“Available drivers:”;  

    QStringList drivers = QSqlDatabase::drivers();  

    foreach(QString driver,drivers)  

    qDebug()<<“\t”<<driver;  

    return a.exec();  

}  

Add in project file

QT       +=sql

Running this demo program, you can see “qmysql3” and “qmmysql” in the list of available drivers. As shown in the figure below:

4、 Common problems and Solutions

（1） Cannot find – llibmysql

“- llibmysql” cannot be found because the configuration in makefile is incorrect. After modifying Makefile, compile it again, and the compilation passes.

The solution to this problem is: check the configuration in Makefile, modify it to conform to the rules of makefile syntax, and then try again.

（2） Undefined reference to ‘MySQL’_ character_ set_ name@4 ’

This situation, as well as a series of undefined references related to MySQL, is due to a problem loading the MySQL library. After online query of relevant information, we know that it is the version problem of MySQL database. When using the link library in 64 bit MySQL database, we will report this error.

The solution is to install a 32-bit MySQL database and configure it mysql.pro File, compile the driver again, and the error will not appear again.

（3） Unrecognized command line option \ “- fno keep inline dllexport \”

The reason for this error is that the version of the compiler is relatively low. To query the version of the currently used gcc compiler is GCC – 4.4.0. The explanation of the online information is as follows:

this is because\”-fno-keep-inline-dllexport\” is the option of mingw 4.6.1, but i’musing 4.5. So I change it by installingcodelite-3.5.5377-mingw4.6.1-wx2.9.2.exe

The translation is as follows:

This is because the option “- fno keep inline dllexport” is a function of mingw4.6.1. The current version is too low, so the gcc compiler of version 4.6.1 or above is needed.

Later, the MinGW compiler environment of 4.8.1 version of gcc compiler was installed. When compiling this driver, the above error disappeared.

Therefore, the solution to this problem is to install the gcc compiler above 4.6.1.

How to Solve SQL state [HY000]: general error 2503

SQLSTATE[HY000]: General error 2503

This error is caused by $this – & gt; query. In version 3.2.3, the query and execute methods should be called separately, otherwise an error will be reported in debug mode

After 3.2.3, the TP framework requires different method calls for read and write operations. If it is used incorrectly, an error will be reported in non debugging mode.

Read data using $this->query
Write data using $this->execute

After TP5, it is written as follows:

Read data using Db::query
Write data using Db::execute

MySQL forgot the root password

MySQL forgot the root password

1. Start the task manager and end the MySQL process

2. Enter the command line, enter the bin directory of MySQL (& lt; environment change & gt; path, you can find the bin directory) and start the MySQL service

mysqld -nt –skip-grant-tables  

(start without checking permissions);

(it may appear: 120705 17:41:14 [warning] option ‘new’: Boolean value’t ‘wasn’t recognized. Set to off. )

3. Reopen a command prompt window

 

  

Input: MySQL – uroot

4. Change the root password:

 

  

update mysql.user set password=PASSWORD(‘newpass’) where User=’root’;

5, flush privileges;

6, quit

Reconnecting MySQL

Reprint address:

http://blog.sina.com.cn/s/blog_ a3695da601010 mrs.html

MySQL modify character set

First of all, there are two main concepts about MySQL character set, one is character sets, the other is collations, the former is character content
and encoding, and the latter is some rules for comparing the former. These two parameter sets can be specified at four levels: database instance, single database, table and column.

For users, utf8 encoding is generally recommended to store data. To solve the problem of garbled code, it is not only the storage of MySQL data, but also related to the coding mode of user program files and the connection mode between user program and MySQL database.

first of all, MySQL has a default character set, which is determined during installation. When compiling mysql, you can use default_ Charset =
utf8 and default_ COLLATION=utf8_ general_ CI (MySQL version 5.5, version 5.1 uses — with charset =
utf8 — with collation = utf8)_ general_ The default character set is utf8, which is also the most once and for all method. After this is specified,
the coding mode of client connecting to database is utf8 by default, and the application does not need any processing.

unfortunately, many people don’t specify these two parameters when compiling and installing mysql, and most people install it through binary programs.
at this time, the default character set of MySQL is Latin1. At this time, we can still specify the default character set of MySQL by my.cnf Add two parameters to the file:
1. Add
default character set = utf8 under [mysqld]
2. Add
default character set = utf8 under [Client]
2 In this way, we don’t need to specify utf8 character set when we build database and table. This writing method in the configuration file solves the problem of data storage and comparison
, but it has no effect on the connection of the client. At this time, the client usually needs to specify utf8 connection to avoid garbled code. This is the general set
Names command. In fact, the set names utf8 command corresponds to the following server-side commands:
set character_ set_ client = utf8;
SET character_ set_ results = utf8;
SET character_ set_ Connection = xutf8;
but these three parameters cannot be written in the configuration file my.cnf It’s in the library. It can only be modified dynamically by the set command. What we need is to write a way to
Yongyi in the configuration file. At this time, is there a way to solve the problem on the server?The feasible idea is in init_ Set in connect. This command will be triggered every time an ordinary user connects. You can add the following line in the [mysqld] section to set the connection character set:
Add under [mysqld]:
init_ Connect =’set names utf8 ‘
summary:
1. It is preferred to specify two parameters to use utf8 encoding when compiling and installing mysql.
2. Select in the configuration file my.cnf or my.ini Set two parameters and init at the same time_ Connect parameter.
3. The third is in the configuration file my.cnf or my.ini Set two parameters, and specify the set names command for the client connection.
4. In the configuration file my.cnf The default character set parameter is added to the client and server in to facilitate management.

Could not create connection to database server. Attempted reconnect 3 times. Giving

1. Questions

The program connection to the database is abnormal, as follows:

Could not create connection to database server. Attempted reconnect 3 times. Giving 

2. Solutions

I tried a variety of solutions, and finally realized: MySQL connector- java.jar Inconsistent with the latest database version. The database is the latest version of 8

0.19，mysql-connector- java.jar It’s 5.0.6.

Solution: MySQL connector- java.jar Keep the same version number as the database.

The POM file is as follows, please check and modify the version by yourself; some versions are compatible, but it is better to keep them unified.

<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>8.0.19</version>
</dependency>

 

Mysql 8.0.13 Enabling remote access (ERROR 1064 (42000): You have an error in your SQL syntax; check the manual th)

preface

When opening remote access permission on windows, we searched a lot of information. Most of the commands explaining how to open remote MySQL remote service are as follows:

grant all privileges on *.* to 'root'@'%' identified by 'Your passwords' with grant option

This method is not applicable to the version after MySQL 8.0.

you need to use the following command to start the remote service.

CREATE USER 'root'@'%' IDENTIFIED BY 'Your passwords'; 
GRANT ALL ON *.* TO 'root'@'%'; 
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'Your passwords';

After the three commands are executed in sequence, refresh the permissions:

FLUSH PRIVILEGES;

Actual effect:

Error in activation of Navicat premium register

Error in activating Navicat with registration machine

Version: Navicat premium 15

Error on decrypt rrequestf code

Processing: disconnect the network and reload Navicat (delete the registry according to the situation)

It is installed on Disk C by default (installation on disk D may not succeed, when the activation code is returned)

Results: it was successful

Duplicate entry ‘787192513’ for key ‘primary’

When the primary key is imported, a large number of errors will be reported.

   Query:

INSERT INTO `FNDCN_ MNG ` values (787390578, 1, ‘Yang Ming’, 95585, ‘2015-06-08′,’prospectus’, 80100, null, ‘1’, null, ‘3’, 2015-06-29 ‘, null,’ 1 ‘,’yang Ming, master of Central University of Finance and economics, 14 years of banking and fund experience. He worked as a credit officer, trader and risk manager in Bank of Shanghai. In October 2004, he joined Huaan Fund Management Co., Ltd. as a researcher of research and development department. Since June 2013, he has been the fund manager of Huaan strategic preferred equity fund. He has been the senior director of investment research since June 2014. ‘, ‘2015-11-09 12:33:58’, ‘2016-01-06 21:34:41’, ‘2015-12-31 12:14:18’, ‘JY’, ‘488969752640’, NULL, NULL, NULL, NULL)

Error occured at:2016-02-16 15:23:41

Line no.:220

Error Code: 1062 – Duplicate entry ‘95585-80100-2015-06-29 00:00:00-1–1’ for key ‘IDX_ FNDCN_ MNG’

Solution 1:

Set the primary key to grow automatically. You can manually set the related table ID to self growing in the database.

As shown in the figure:

  

Continue to import normal.

Solution 2:

Problem Description: if the SQL is imported separately, the following error will be reported:

1 queries executed, 0 success, 1 errors, 0 warnings

Query: insert into ` fndcn_ MNG ` values (787192513, 1, ‘Dingjin’, 150150, ‘2015-12-28’, ‘prospectus’, 80100, null,’ 1 ‘, null,’ 3 ‘, 20

Error code: 1062

Duplicate entry ‘787192513’ for key ‘PRIMARY’

After checking, the primary key generation strategy is: @ generatedvalue (strategy)= GenerationType.IDENTITY ）No problem.

Delete the table with import error;

Create a new table to import data;

My problem is in the database: in the database, the primary key is not set to grow automatically. Set the primary key of the existing table to grow automatically. The SQL statement is as follows:

alter table course change course_ id   course_ id   int(10)   not null   auto_ increment ; 

Then use SQL script to import the required data, which can also easily locate errors.

  

mysql error 1062

Duplicate entry ‘1’ for key ‘PRIMARY’

Error reason: primary key conflict, primary key value is unique
solution: the value of ‘primary’ primary key cannot be the same

Example:

1. View the table structure

2. View data

3. Add data, add id = 1, and an error will be reported, because ID is the primary key with uniqueness

Solution: if the value of ID is modified, the addition is successful

Error 1045 (28000): access denied for user ‘root’ @’localhost ‘(using password: no)

Error 1045 (28000): access denied for user ‘root’ @’localhost ‘(using password: no)

The solution steps are as follows:

1. First stop the database service: systemctl stop mariadb.service

2. Use mysqld_ Safe to start mysqld server: mysqld_ safe –user=mysql –skip-grant-tables –skip-networking &

Display: exit Ctrl + C

3. Login: MySQL – U root mysql

4. Modify the password of the root user: update user set password = password (‘newpassword ‘) where user’s root’;

5. Use the modified root user and new password to log in to the database: MySQL – uroot – P

 

The MariaDB server is running with the –skip-grant-tables option so it cannot execute this statement

An error occurred when creating a user. The execution statement is:

GRANT ALL PRIVILEGES ON *.* TO helei IDENTIFIED BY ‘MANAGER’ WITH GRANT OPTION;

Solution: MySQL & gt; flush privileges;

Reference: MariaDB initial login error 1045 (28000) solution

https://blog.csdn.net/carry9148/article/details/52252780

Mysql start slave error 1201 (HY000)

After MySQL replication is configured, an error is reported when starting slave as follows:

mysql> start slave;
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log
mysql>

Looking for it on the Internet, it turns out that there are two files to delete. It is estimated that it was generated yesterday when other people failed in the test.

/var/lib/mysql/ master.info
/var/lib/mysql/relay- log.info

Just delete it.

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql>

Refer to the following documents:

http://blog.sina.com.cn/s/blog_ 51d3553f0100j5m2.html