Tag Archives: database

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.

Nginx reverse proxy MySQL

1. Scene

Mysql database in the pure Intranet environment, no public IP, no VPN.

2. Programme

Install nginx on a server with public IP and in the same Intranet environment with MySQL service to realize the routing and forwarding of MySQL access.

3. Nginx installation

Nginx version needs 1.9 or above. Nginx not only implements HTTP reverse proxy, but also supports TCP reverse proxy.

1) When compiling nginx, you need to add the parameter — with stream to load NGX_ stream_ core_ Module

Examples

./configure –prefix=/opt/software/nginx –with-http_ stub_ status_ module –with-http_ ssl_ module –with-stream –with-stream_ ssl_ module –with-pcre=/usr/local/src/pcre-8.35

4. Nginx configuration file nginx.conf

Monitor port 3307 with public IP server, and jump to port 3306 of 172.31.88.27.

Special note: stream should be in the same level directory as HTTP

stream {
    upstream mysql3306 {
        hash $remote_addr consistent;
        server 172.31.88.27:3306 weight=5 max_fails=3 fail_timeout=30s;
    }
	
	 server {
        listen 3307;
        proxy_connect_timeout 10s;
        proxy_timeout 200s;
        proxy_pass mysql3306;
    }
}

 

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.

  

Solution to syntaxerror: invalid syntax in PIP install XXX

Solution to syntaxerror: invalid syntax in PIP install XXX

Statement: 1. PIP install requests are taken as an example; 2. Windows system;
first of all, check whether you have run PIP in the python environment. If so, please open the start menu, enter CMD, find the command prompt and open it.
Enter PIP install requests in CMD, If “not an internal or external command, nor a runnable program or batch file” appears, the specific solutions are as follows:
1. Find the folder where Python is located, find the scripts file, and use Ctrl + C to copy the file path. Take an individual as an example: C: 2 2. Return to the desktop, right-click “my computer” and click “properties”
3. In the pop-up page, select “advanced system settings – variable environment – path – Edit – add the file path copied in step 1 and click OK
4. Restart the computer

After restarting the computer, open CMD again, and then enter the command PIP install requests. If
appears, the installation is successful and the problem is solved.

New version of grafana add data source error!

Premise:

There are no errors in the data source URL configured by grafana.

 

Phenomenon:

1) After upgrading grafana, it is found that the original configured open face data source is invalid, and HTTP error not found is always prompted.

2) After installing the new version of grafana, we found that ZABBIX data source configuration always reported an error, could not connect to given URL.

 

handle:

1) Re install the version under grafana 5.4.

2) Check the configuration of ZABBIX user name and password.

 

The specific reasons and how to configure them above grafana 5.4 have not been studied yet.

Reproduced in: https://www.cnblogs.com/whych/p/10793709.html

Initialization error of PLSQL connection to Oracle

Download as many as 64 = 32-bit database

Oracle is 32-bit and your PLSQL must be 32-bit; Oracle is 64 bit and your PLSQL must be 64 bit;

After entering the account password, this box will pop up

Solution: see how many bits your Oracle is, and then you can download how many bits of your PLSQL

This interface is usually installed correctly

Error in ODBC connection of Dameng database, [ISQL] error: could not SQLConnect

In the process of learning Dameng database, install ODBC driver process record.
Operating system environment: Qilin 6.0 server

1. UNIX ODBC installation
tar – xzvf UNIX odbc-2.3.0 tar.gz
CD unixodbc-2.3.0
./configure — enable GUI = no
make
make install
all the way down OK, there are two configuration files for ODBC configuration ODBC.ini and odbcinst.ini By default, you can log in and edit in/usr/local/etc
root user, as shown in the figure:


3. Test the ODBC connection
Su – dmdba
execute ISQL Dm7
report an error, [ISQL] error: could not SQLConnect

in order to see the cause of the error, execute ISQL – V Dm7, prompt:

according to the cause of the error, guess that there is an error in the configuration file. After careful inspection, it is found that:

after the problem is solved, execute ISQL Dm7, the correct result comes out:

Mongodb uses Mongo to report error: could’t connect to server 127.0.0.1:27017, connection attempt failed: socket

Mongodb uses Mongo to report an error:

connecting to: mongodb://127.0.0.1 :27017/?compressors=disabled&gssapiServiceName=mongodb
2019-07-18T15:02:35.529+0800 E QUERY [js] Error: couldn't connect to server 127.0.0.1:27017, connection attempt failed: SocketException: Error connecting to 127.0.0.1:27017 :: caused by :: ����Ŀ�����������ܾ����޷����ӡ� :
connect@src/mongo/shell/mongo.js : 342:17
there is a connection error when you enter Mongo on the command line

Solution:

Enter in the command line mongod.exe --dbpath E:\MongoDB\data\db

Among them, e:
do not close the CMD window, open a new CMD window again, and enter the Mongo command:

to open mongodb

Using Navicat + premium + 11.1 to connect to PG12 version, error: column c.relhasoids does not exist

Phenomenon:
when using Navicat + premium + 11.1 client to connect to PG12, an error was reported:

reason:
the current database version: postgres12 is inconsistent with the client connection tool Navicat + premium + 11.1

Solutions:
1. Upgrade the client version to Navicat premium 12
2. Reduce postgres12 to 11

After solution:

Solution to error 2002 (HY000) in MySQL login startup

Error information

Login database error:`

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/ mysql.sock ‘ (2 “No such file or directory”)`

Service restart error: job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service " and "journalctl -xe" for details.

View service status information: ` systemctl status mariadb.service

● mariadb.service – MariaDB 10.1 database server
Loaded: loaded (/usr/lib/systemd/system/ mariadb.service ; disabled; vendor preset: disabled)
Active: failed (Result: exit-code) since Mon 2019-04-15 17:16:38 UTC; 4s ago
Process: 2864 ExecStartPre=/usr/libexec/mysql-prepare-db-dir %n (code=exited, status=1/FAILURE)
Process: 2841 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS)

Apr 15 17:16:37 xiandian systemd[1]: Starting MariaDB 10.1 database server…
Apr 15 17:16:38 xiandian mysql-prepare-db-dir[2864]: Database MariaDB is not initialized, but the directory /var/lib/mysql is not empty…e done.
Apr 15 17:16:38 xiandian mysql-prepare-db-dir[2864]: Make sure the /var/lib/mysql is empty before running mysql-prepare-db-dir.
Apr 15 17:16:38 xiandian systemd[1]: mariadb.service : control process exited, code=exited status=1
Apr 15 17:16:38 xiandian systemd[1]: Failed to start MariaDB 10.1 database server.
Apr 15 17:16:38 xiandian systemd[1]: Unit mariadb.service entered failed state.
Apr 15 17:16:38 xiandian systemd[1]: mariadb.service failed.
Hint: Some lines were ellipsized, use -l to show in full.`

The error message of restart service log: ` APR 15 17:16:21 Xiandian MySQL prepare DB dir: database MariaDB is not initialized, but the directory/var/lib/MySQL is not empty, so initialization cannot be done

Apr 15 17:16:21 mysql-prepare-db-dir: Make sure the /var/lib/mysql is empty before running mysql-prepare-db-dir.
Apr 15 17:16:21 systemd: mariadb.service : control process exited, code=exited status=1
Apr 15 17:16:21 systemd: Failed to start MariaDB 10.1 database server.
Apr 15 17:16:21 systemd: Unit mariadb.service entered failed state.
Apr 15 17:16:21 systemd: mariadb.service failed.`

You can see that the error message indicates that the database is not initialized, but/var/lib/MySQL is not empty, so it cannot be initialized.

Take a look at the directory permissions

 ll /var/lib/
 d---------. 11 mysql      mysql      4096 May  4  2018 mysql

The MySQL permission is 0, which causes the startup file sock file cannot be written in
Restart, give the directory permission 755
Chmod 755/var/lib/MySQL/
and then restart the MySQL service
systemctl restart mariadb.service
PS - e | grep - I MySQL
3191?00:00:02 mysqld
starts normally

Reproduced in: https://blog.51cto.com/9103824/2378808

Failed to connect to ourself

Today, when the management node of MySQL Cluster was restarted, it could not be started. After several attempts, there was still a problem. Check the log and the following error was reported.

2011-04-01 23:49:03 [MgmtSrvr] INFO     — Got initial configuration from ‘/usr/local/mysql/mysql-cluster/ config.ini ‘, will try to set it when all ndb_ mgmd(s

) started

2011-04-01 23:49:03 [MgmtSrvr] INFO     — Mgmt server state: nodeid 1 reserved for ip 192.168.100.32, m_ reserved_ nodes 1.

2011-04-01 23:49:03 [MgmtSrvr] INFO     — Node 1: Node 1 Connected

2011-04-01 23:49:03 [MgmtSrvr] INFO     — Id: 1, Command port: *:1186

2011-04-01 23:49:03 [MgmtSrvr] WARNING  — 1011 Unable to connect with connect string: nodeid=0, localhost:1186

2011-04-01 23:49:03 [MgmtSrvr] ERROR    — Failed to connect to ourself!

It’s strange that the server has been in normal use for several days. As soon as it was restarted today, there was a problem. I asked my colleagues what changes the server had made in the past two days. The answer is:

Changed the hosts file. Go up and see: found changed to:

127.0.0.1               localhost.localdomain openldap

So change to the default value

127.0.0.1               localhost.localdomain localhost

Restart, OK. business as usual!

Reproduced in: https://www.cnblogs.com/feihongwuhen/archive/2011/04/01/7170052.html

MySQL error: 1005 can’t create table (error: 150)

Cause of error:

1. The type or size of the two fields do not strictly match. For example, if one of them is int (10), then the foreign key must also be set to int (10), not int (11), and it cannot be tinyint. You can use the show command to view the size of the field, because some query browsers sometimes display int (10) and int (11) as integer. In addition, it is necessary to confirm whether both fields are signed or not, and the two fields must be strictly matched all the time.

2. When an attempt is made to refer to one of the foreign keys, which is not indexed or is not primary key, an index must be created for this foreign key.

3. The name of a foreign key is an existing key value. You should make sure that the foreign key name is unique, or add several characters randomly after the key name to test whether this is the reason.

4. If you want to use foreign key constraints, one or two of the tables representing the MyISAM engine must be InnoDB engine (if both tables are MyISAM engines, this error will not occur at all, but foreign keys will not be generated).

5. It is possible that on delete set null is set, but the related key field is set to not null. You can fix this bug by modifying the property value of cascade or setting the field property to allow null.

6. Make sure your charset and collate options are consistent at the table and field levels.

7. A default value may be set for the foreign key, such as default = 0

8. In this relationship, one of the fields is one of the mixed key values. It does not have its own independent index, so it is necessary to create an independent index for it.

9. Error in alert declaration.

10. The two tables to be joined have different encoding formats.

Refer to Baidu document: https://wenku.baidu.com/view/7a0a1f1b10a6f524ccbf85e6.html