Category Archives: MySQL

MySQL character set error resolution: err=Error 3988: Conversion from collation utf8mb4_unicode_ci into utf8_general_ci impo

The content of a field contains expressions. When inserting mysql, you will be prompted with an error:

err=Error 3988: Conversion from collation utf8mb4_unicode_ci into utf8_general_ci impossible for parameter

solve:

1. Modify MySQL character set

I am using docker

  1. Modify mysql.cnfmaps to docker where /etc/mysql/mysql.cnf:
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
      1. start command parameter addition:
 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

After restarting the container, enter Mysql to view:

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
| collation_connection     | utf8mb4_unicode_ci             |
| collation_database       | utf8mb4_unicode_ci             |
| collation_server         | utf8mb4_unicode_ci             |
+--------------------------+--------------------------------+
11 rows in set (0.17 sec)

2. Modify the character set of table

        1. modify the character set of a specific table:
DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
          1. modify the character set of a specific field:
CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci

[Solved] MybatisPlusException: Error: Method queryTotal execution error of sql

Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Error: Method queryTotal execution error of sql :

Error reason:
the user-defined SQL is written in mapper, where is added too much, and the user-defined SQL is transferred into querywrapper, where cannot be added
error code:

String customSql="select * from ("+queryAll+") as q where ${ew.customSqlSegment}";
    @Select(customSql)
    IPage<BranchBasic> baseQuery(Page<Object> objectPage, @Param(Constants.WRAPPER)QueryWrapper queryWrapper);

Correct code:
as both case and upper case are OK

String customSql="select * from ("+queryAll+") as q ${ew.customSqlSegment}";
    @Select(customSql)
    IPage<BranchBasic> baseQuery(Page<Object> objectPage, @Param(Constants.WRAPPER)QueryWrapper queryWrapper);

MySQL Install Error: MySQL error 1042: Unable to connect to any of the specified MySQL hosts

The following error occurred in the last step of installing MySQL:
MySQL error 1042: unable to connect to any of the specified MySQL hosts

There are two solutions

First, treat the symptoms but not the root cause. Open Win + R and run it. Enter services.msc to open the service. Find the service name added when installing mysql. Generally, MySQL version 8.0 is MySQL 80 by default. Double click to open it. Under the login tab, change the selected account to the local system account.

It’s OK to execute after the application is confirmed, but this method needs to be operated once every time you reload mysql.

Another way is to configure group policy, which is a problem caused by the permission management of windows. The steps are as follows:
right click this computer – > Management – > Local users and groups – > Group – > Double click administrators – > Add – > Advanced
add network service to the administrators group

[Solved] Mongo Error: cant post the change to mongodb there is transaction error

Mongo database
Edit, add data error:
can post the change to mongodb there is transaction error

submit transaction error:

add set error in query:

prompt: not master

Reason:
the slave node used in Mongo master-slave only has read permission by default, and the slave node connected by itself
solution:
setting in Mongo allows the slave node to be writable, or directly connects to the IP address of the master node

MySQL Build table error: You have an error in your SQL syntax； check the manual that corresponds to your MySQL ser

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ……

syntax error:

Because varchar needs to specify the length, varchar (32) checks whether the corresponding types of all fields need to add length or precision, because MySQL keywords are used. For example, using ID as the field name needs to be enclosed in quotation marks

[Solved] ORA-04063: package body “SYS.DBMS_DATAPUMP“ has errors

Due to the manual modification of the default parameter value of cluster_ok in start_job of sys.DBMS_DATAPUMP, 1 is 0, which leads to export error after successful compilation.
ORA-04063: package body “SYS.DBMS_DATAPUMP” has errors
SQL> CREATE TABLE “test”
2  (       ”   ” VARCHAR2(255), “YPID” VARCHAR2(255));
Table created.
SQL> insert into “test”  values(‘aa’,’bb’);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;

——————————————————————————–
YPID
——————————————————————————–
aa
bb

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0

[oracle@oem ~]$ expdp jyc/jyc@jyc dumpfile=t.dmp logfile=t.log directory=dmp tables=jyc.test
Export: Release 19.0.0.0.0 – Production on Tue Jun 29 16:54:57 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
UDE-04063: operation generated ORACLE error 4063
ORA-04063: package body “SYS.DBMS_DATAPUMP” has errors
ORA-06508: PL/SQL: could not find program unit being called: “SYS.DBMS_DATAPUMP”
ORA-06512: at line 1
[oracle@oem ~]$ sqlplus jyc/jyc@jyc
SQL*Plus: Release 19.0.0.0.0 – Production on Tue Jun 29 16:57:00 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Last Successful login time: Tue Jun 29 2021 16:54:57 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
SQL> select * from dba_errors where OWNER=’SYS’ and NAME=’DBMS_DATAPUMP’;
OWNER
——————————————————————————–
NAME
——————————————————————————–
TYPE                                     SEQUENCE       LINE   POSITION
————————————– ———- ———- ———-
TEXT
——————————————————————————–
ATTRIBUTE          MESSAGE_NUMBER
—————— ————–
SYS
DBMS_DATAPUMP
PACKAGE BODY                                    1       7294         17
OWNER
——————————————————————————–
NAME
——————————————————————————–
TYPE                                     SEQUENCE       LINE   POSITION
————————————– ———- ———- ———-
TEXT
——————————————————————————–
ATTRIBUTE          MESSAGE_NUMBER
—————— ————–
PLS-00593: default value of parameter “CLUSTER_OK” in body must match that of sp
ec
ERROR                         593
OWNER
——————————————————————————–
NAME
——————————————————————————–
TYPE                                     SEQUENCE       LINE   POSITION
————————————– ———- ———- ———-
TEXT
——————————————————————————–
ATTRIBUTE          MESSAGE_NUMBER
—————— ————–

SQL>
select * from dba_objects where status = ‘INVALID’
select owner, name, text, message_number from all_errors where owner=’SYS’;

Solution:
Execute rebuild dbms_datapump under cdb
cd $ORACLE_HOME/rdbms/admin
sqlplus/as sysdba
@dbmsdp.sql;
@utlrp.sql;
exit
Redirection is normal.
[oracle@oem admin]$ expdp jyc/jyc@jyc dumpfile=t.dmp logfile=t.log directory=dmp tables=jyc.test
Export: Release 19.0.0.0.0 – Production on Thu Jul 1 10:29:24 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
FLASHBACK automatically enabled to preserve database integrity.
Starting “JYC”.”SYS_EXPORT_TABLE_01″:  jyc/********@jyc dumpfile=t.dmp logfile=t.log directory=dmp tables=jyc.test
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “JYC”.”TEST”                                    0 KB       0 rows
Master table “JYC”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for JYC.SYS_EXPORT_TABLE_01 is:
/home/oracle/dmp/t.dmp
Job “JYC”.”SYS_EXPORT_TABLE_01″ successfully completed at Thu Jul 1 10:29:32 2021 elapsed 0 00:00:08

[Solved] Description: Failed to configure a DataSource: ‘url‘ attribute is not specified and no embedded

Description:
Failed to configure a DataSource: ‘url’ attribute is not specified and no embedded datasource could be configured.
Reason: Failed to determine a suitable driver class
Action:
Consider the following:
If you want an embedded database (H2, HSQL or Derby), please put it on the classpath.
If you have database settings to be loaded from a particular profile you may need to activate it (no profiles are currently active).
My database configuration file is application.properties. after reading it seems to be unreadable, I recreate the application.yml file again, just copy the configuration file to this side yml. restart it and it’s fine.

server:
  port: 8080
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/springcloud?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false&serverTimezone=UTC
    username: root
    password: 123456

I don’t know why.

[Solved] MySQL5.6.44 [Err] 1067 – Invalid default value for create_date settlement programme

Scenario
[Err] 1067 – Invalid default value for ‘create_date’, for the create table species statement as follows.
`create_date` timestamp(0) NOT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT ‘creation time’ SOLVED
MySQL5.6.44 and MySQL5.7.27 timestamp set default rule changed, not “0000 00-00 00:00:00″
Solution:
Check sql_mode:
mysql> show session variables like ‘%sql_mode%’;
+—————+——————————————————————————————————————————————-+
| Variable_name | Value                                                                                                                                     |
+—————+——————————————————————————————————————————————-+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+—————+——————————————————————————————————————————————-+
1 row in set (0.01 sec)
change sql_mode, remove NO_ZERO_IN_DATE,NO_ZERO_DATE:
mysql> set sql_mode=”ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”;

[Solved]Error 1054 (42s22): unknown column ‘password’ in ‘field list’ how to modify the password

1. Update list apt get

sudo apt-get update

2. Install MySQL server and client

sudo apt-get install mysql-server mysql-client

3. When installing the server, if there is no default password, there is no password. Enter the following command to enter mysql

mysql -u root -p

4. Modify the root password. Note that the password field does not exist and needs to be modified to authentication_ String to modify

use mysql;
update mysql.user set authentication_string=password('root') where user='root' and Host ='localhost';
update user set plugin="mysql_native_password"; 
flush privileges;
quit;

5. Set the encoding, modify the/etc/MySQL/my.cnf file, and add the following contents:

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
character-set-server=utf8

6. Enter Mysql to view the status (whether the code is utf8)

status or \s

7. Start and close MySQL service

open:service mysql start
close:service mysql stop
restart:service mysql restart

MYSQL Index Key Length 1071 – Specified key was too long； max key length is 3072 bytes

MySQL index key length

In the development, two varchar 1000 fields are associated. As a MySQL index white, I directly add a normal index to the table. This will report an error, saying that the length of my index is too long. Search on the Internet is to change the MySQL configuration file. By chance, I saw an article sent by a big man, and I knew that the index length of MySQL was such a usage.

Error reported by MySQL: 1071 – specified key was too long; max key length is 3072 bytes

SELECT
 ROUND(SUM(LENGTH(`school_unit_title`)<10)*100/COUNT(`school_unit_title`),2) AS pct_length_10,
 ROUND(SUM(LENGTH(`school_unit_title`)<20)*100/COUNT(`school_unit_title`),2) AS pct_length_20,
 ROUND(SUM(LENGTH(`school_unit_title`)<50)*100/COUNT(`school_unit_title`),2) AS pct_length_50,
 ROUND(SUM(LENGTH(`school_unit_title`)<80)*100/COUNT(`school_unit_title`),2) AS pct_length_80,
 ROUND(SUM(LENGTH(`school_unit_title`)<100)*100/COUNT(`school_unit_title`),2) AS pct_length_100
FROM `z_school_unit`;

host ‘‘ is not allowed to connect to this mysql server Connect MYSQL Error

1. When you plan to open your database with MySQL – H (IP address of host) – U (user name) – P, the error host ‘is not allowed to connect to this MySQL server is reported
reason: remote login is not allowed in MySQL Step 1: MySQL – U (user name) – P
Step 2: enter use MySQL
Step 3: enter update user set host = ‘%’ where user =’root ‘

4. After connecting, test example:

import pymysql

#Establish a connection
conn = pymysql. Connect (
0   Host =
your IP address   user=”root”,  # User name
Passwd = “root”, # user password
0   DB = (test) # database name

#To create a cursor, the default is meta group type
cursor = conn.cursor()
sql = “select * from student” # the name of the table in the database

cursor.execute(sql)
res = cursor.fetchall()
print(res)

cursor.close()
conn.close()

[Solved] The number of rows returned by the mybatis UPDATE statement is always 1 or useaffectedrows = true

The number of rows returned by the mybatis UPDATE statement is always 1 or useaffectedrows = true

Problem solving explanation useaffectedrows = true

solve the problem

     This is because the database url connection is missing useAffectedRows=true


 The problem is solved here, but I'll explain it later useAffectedRows=true.

Explain useaffectedrows = true

 useAffectedRows defaults to false, and the value returned is the number of rows matched.
    useAffectedRows = true, the value returned is the number of rows affected.