Tag Archives: database

[Solved] MariaDB Add Datas Error: SQL error [1366] [22007]: (cIncorrect string value: ‘\xE5\xBC\xA0\xE4\xB8\x89’ for column

MariaDB add datas error:

SQL error [1366] [22007]: (conn=17) Incorrect string value: ‘\xE5\xBC\xA0\xE4\xB8\x89’ for column `SAOS`.`user`.`userName` at row 1

SQL:

INSERT into `user` values(1, "ZHANGSAN","123456");

The reason for this is the encoding problem. We can check the database character set encoding:

SHOW VARIABLES LIKE 'character%';

Variable_name                           |Value                       |
————————                +—————————-+
character_set_client                    |utf8mb4                     |
character_set_connection                |utf8mb4                     |
character_set_database                  |latin1                      |
character_set_filesystem                |binary                      |
character_set_results                   |utf8mb4                     |
character_set_server                    |latin1                      |
character_set_system                    |utf8                        |
character_sets_dir                      |/usr/share/mariadb/charsets/|

Above is the result displayed. You can see that there are two latin1 encodings. So to change the encoding, find the my.ini file at

whereis my.ini

The results are as follows:

my: /etc/my. cnf

That means this file replaces my Ini, we modify this file.

Add two lines of configuration:

default-character-set=utf8
character-set-server=utf8

Note that this file will tell you that it is read-only when it is modified. You should empower it:

sudo chmod 777  /etc/my.cnf

Then restart MariaDB.

systemctl restart mariadb.service

You need identity authentication. We can just enter the password.

Then view the database character set encoding:

In this way, insert the data just now, and the result is still an error. It seems that you need to continue to modify the configuration file.

[Solved] MySQL workbench Error: SSL connection error: SSL is required but the server doesn‘t support it.

Question:

MySQL workbench 8.0. The 27 version connects to the local MySQL database and reports SSL connection error: SSL is required but the server doesn’t support it

Solution:

If SSL is not enabled, enter the parameter: usessl = 0 in the others box on the advance tab page, and then you can connect.

db2 rollforward bufferpool error [How to Solve]

1. Problem: After transferring the online db2 backup file and logs from server A to server B for restoration, no error was reported during the restore process, but an error was reported during the rollforward process of the database, reporting sql1218n there are no pages currently available in bufferpool “”. sqlstate=57011 error, check db2diag report no available free pages, etc.

2. Analysis of the reasons: A server on the library bufferpool set relatively large, in B server is not so large memory, so it will report an error, in forward with topas or nmon for memory observation can be analyzed.

3. Solution: db2 has a global variable db2_override_bpf, you can set this variable, how much to set, according to the memory of the B server for planning, you need to restart db2 after setting to make it effective. Now, according to a backup file, it takes more than three months to rollforward the data, but now it takes one day to roll forward the data of less than 5 days.

4. Then observe the progress of rollforward.

Error in invoking target [How to Solve]

When Oracle 11g is installed on Linux 7, an error in invoking target is reported when the installation process reaches 86%. The screenshot is as follows

solution:

[oracle@emrtest ~]$ cd $ORACLE_HOME/sysman/lib/
[oracle@emrtest lib]$ vi ins_emagent.mk

Find:
$(SYSMANBIN)emdctl:
        $(MK_EMAGENT_NMECTL)

Modify to:
$(SYSMANBIN)emdctl:
        $(MK_EMAGENT_NMECTL) -lnnz11

Click Retry after the change

[Solved] ERROR #42601 syntax error at or near “)“

 

Background

orm：go-pg

Execute whereIn error: Error #42601} syntax} error at} or “near”)

As shown in the figure

The query is as follows:

err = db.Model(&model.Abc{}).WhereIn(“id in (?)”, ids).Column(“id”).Column(“name”).Select(&records)

If IDS is an empty slice, this error will be reported at this time

Solution:

The slices passed in should not be empty. From the business point of view, the length of the list of condition ids to be checked is 0 then the results must not be checked, so you can make a judgment in the outer layer, ids length is 0 then do not take the sql query can be.

How to Solve mysqld — console command error

My error report is like the following picture

The prompt is as follows: an error is reported

 2021-12-26T12:28:24.321789Z 0 [System] [MY-010116] [Server] C:\mysql-8.0.26-winx64\bin\mysqld.exe (mysqld 8.0.26) starting as process 8168
2021-12-26T12:28:24.323688Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2021-12-26T12:28:24.335659Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-12-26T12:28:24.337392Z 1 [ERROR] [MY-012271] [InnoDB] The innodb_system data file 'ibdata1' must be writable
2021-12-26T12:28:24.337508Z 1 [ERROR] [MY-012278] [InnoDB] The innodb_system data file 'ibdata1' must be writable
2021-12-26T12:28:24.337618Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2021-12-26T12:28:24.337878Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2021-12-26T12:28:24.337978Z 0 [ERROR] [MY-010119] [Server] Aborting
2021-12-26T12:28:24.338395Z 0 [System] [MY-010910] [Server] C:\mysql-8.0.26-winx64\bin\mysqld.exe: Shutdown complete (mysqld 8.0.26)  MySQL Community Server - GPL.

Reason guess:

The MySQL service is always turned on (it starts automatically after startup), so when it is turned on, an error will be reported when the command line is started

Solution:

Enter the command prompt in administrator mode and enter the following command to close the MySQL service

net stop mysql

So far, the problem has been solved. Entering mysqld — console again will open the MySQL service normally

CMake Error :MYSQL_INCLUDE_DIR [How to Solve]

CMake Error: The following variables are used in this project, but they are set to NOTFOUND.
Please set them or make sure they are set and tested correctly in the CMake files:
xxxx/MYSQL_INCLUDE_DIR

Solution: yum install mysql-devel

Other missing boost/cmake etc. can be installed by compiling the source code.

cmake-3.8.2.tar.gz
boost_1_78_0.tar.gz

[Solved] ElasticSearch Startup Error: error updating geoip database

Problem Description:

Error when launching elasticsearch:
error updating geoip database

[2021-12-22T21:07:11,944][INFO ][o.e.i.g.GeoIpDownloader  ] [DESKTOP-5JGQJR8] updating geoip database [GeoLite2-ASN.mmdb]
[2021-12-22T21:07:13,022][ERROR][o.e.i.g.GeoIpDownloader  ] [DESKTOP-5JGQJR8] error updating geoip database [GeoLite2-ASN.mmdb]
org.elasticsearch.cluster.block.ClusterBlockException: index [.geoip_databases] blocked by: [TOO_MANY_REQUESTS/12/disk usage exceeded flood-stage watermark, index has read-only-allow-delete block];

Cause analysis:

This is a bug

Solution:

Add the following configuration to elasticsearch.yml:

ingest.geoip.downloader.enabled: false

Turn off geoip database updates

[Solved] Error while sending STMT_PREPARE packet. PID=29294

In the error reporting scenario, the command command of tp6 + nohup of Linux is used to execute the continuous script
error reason: the connection to the database will be disconnected for a long time

Detailed error information is as follows

[think\exception\ErrorException]                    
Error while sending STMT_PREPARE packet. PID=29294  
                                                      


PHP Fatal error:  Uncaught think\exception\ErrorException: Error while sending STMT_CLOSE packet. PID=29294 in /....../vendor/topthink/think-orm/src/db/PDOConnection.php:603
Stack trace:
#0 /....../vendor/topthink/think-orm/src/db/PDOConnection.php(603): think\initializer\Error->appError(2, 'Error while sen...', '/...', 603, Array)
#1 /....../topthink/think-orm/src/db/PDOConnection.php(1576): think\db\PDOConnection->free()
#2 /....../vendor/topthink/think-orm/src/db/Connection.php(345): think\db\PDOConnection->close()
#3 [internal function]: think\db\Connection->__destruct()
#4 {main}
thrown in /....../vendor/topthink/think-orm/src/db/PDOConnection.php on line 603

Solution:

Modify the database configuration file database.php and set it to true to enable disconnect and reconnect
 // whether to disconnect and reconnect
'break_reconnect' => true,

[Solved] dbeaver Connect clickhouse Error: Unexpected driver error occurred while connecting to the database

Unexpected driver error occurred while connecting to the database when dbeaver is used to connect to the Clickhouse;

First:

The probability is because the Clickhouse configuration file/preprocessed_configs/config.xml file

<listen_host>::</listen_host>

In the annotation state, unlock the annotation.

Second:

In my personal case, I started the clikehouse image with the docker container

Before starting the container:

docker run -d --name ck-server --network=ck_net --ulimit nofile=262144:262144 --volume=$HOME/some_clickhouse_database:/var/lib/clickhouse yandex/clickhouse-server

Replace with:

docker run -d --name ck-server --ulimit nofile=262144:262144  -p 8123:8123 -p 9000:9000 -p 9009:9009 --volume=$HOME/Documents/ck2_database:/var/lib/clickhouse yandex/clickhouse-server

The test connection is successful.

How to Solve Clickhouse restart error: Cannot obtain value of path from config file…

1. Clickhouse service restart

sudo service clickhouse-server start

2. Error message

Start clickhouse-server service: Poco::Exception. Code: 1000, e.code() = 0, 
e.displayText() = Exception: Failed to merge config with '/etc/clickhouse-server/config.d/metric_log.xml': 
Exception: Root element doesn't have the corresponding root element as the config file. 
It must be <yandex> (version 21.3.4.25 (official build))
Cannot obtain value of path from config file: /etc/clickhouse-server/config.xml

3. Problem analysis

Exception starting Clickhouse server service. Unable to merge configuration with ‘/ etc/Clickhouse server/config’. The root element does not have a corresponding root element as a configuration file. It must be (version 21.3.4.25 (official version)). The path value cannot be obtained from the configuration file:/etc/Clickhouse server/config.xml

translated as/etc/Clickhouse server/config d/metric_log. The content in the XML configuration file, XML, cannot be parsed correctly. The official version is 21.3 4.25 it is required that the content of the configuration file should be in the label

4. Solutions

# open files /etc/clickhouse-server/config.d/metric_log.xml
vi /etc/clickhouse-server/config.d/metric_log.xml

# Mofdify the content of metric_log.xml file
<clickhouse>
    <metric_log>
        <database>system</database>
        <table>metric_log</table>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
        <collect_interval_milliseconds>1000</collect_interval_milliseconds>
    </metric_log>
</clickhouse>
# Modify the file content and put it in <yandex> tag
# Contents of the modified metric_log.xml file
<yandex>
	<clickhouse>
	    <metric_log>
	        <database>system</database>
	        <table>metric_log</table>
	        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
	        <collect_interval_milliseconds>1000</collect_interval_milliseconds>
	    </metric_log>
	</clickhouse>
</yandex>

# save and restart
sudo service clickhouse-server start
# Prompt after execution
Start clickhouse-server service: Path to data directory in /etc/clickhouse-server/config.xml: /data/clickhouse/
DONE

Python Connect database error: command listdatabases requires authentication

Python reports an error when connecting to the database. Command listdatabases requires authentication, full error: {OK ‘: 0.0,’ errmsg ‘:’ command listdatabases requires authentication ‘,’ code ‘: 13,’ codename ‘:’ unauthorized ‘}

The reason for the error is that authentication is required, indicating that user name and password authentication are required to connect to mongodb database.

Connect mongodb without password. The code is as follows:

from pymongo import MongoClient
class MongoDBConn:
    def __init__(self, host, port, db_name, user, password):
        """
        Establishing database connections
        """
        self.conn = MongoClient(host, port)
        self.mydb = self.conn[db_name]

With password authentication, connect to Mongo database, and the code is as follows:

from pymongo import MongoClient

class MongoDBConn:

    def __init__(self, host, port, db_name, user, password):
        """
        Establishing database connections
        """
        self.conn = MongoClient(host, port)
        self.db = self.conn.admin 
        self.db.authenticate(user, password)
        self.mydb = self.conn[db_name]

Pit record

In fact, an error was reported in the middle:

Authentication failed., full error: {‘ok’: 0.0, ‘errmsg’: ‘Authentication failed.’, ‘code’: 18, ‘codeName’: ‘AuthenticationFailed’}

Originally used directly in the code:

self.db = self.conn[db_name]
self.db.authenticate(user, password)

If you directly use the target database for link authentication, you will report the above error. Instead, first connect to the system default database admin, use admin for authentication, and you will succeed, and then do the corresponding operation for the target database.