Tag Archives: mysql

Solve ERROR 1136 (21S01) in Mysql: Column count doesn’t match value count at row 1

The problem is that when a new row is inserted, an error is returned.
type:

mysql> insert into students values(1,"goutou",18,"male",12121);

Returns:

ERROR 1136 (21S01): Column count doesn't match value count at row 1

check statement is ok. Considering the previous operation of deleting a column, two new columns are added, and the input is:

mysql> insert into students(id,name,age,gender,clc_id) values(0,"zhutou",12,"male",12121);
Query OK, 1 row affected (0.06 sec)

 

Mybatis Error: The server time zone value ‘����1532a0’ is unrecognized

The first time to install and use mybatis, problems encountered are as follows:

Question 1: Unkonwn database ‘test’

I created a new test database in my own database, and the corresponding table name, the problem was solved. However, what I want to understand is: how to specify which database and which table to use in the XML configuration file, otherwise, this problem will appear.

Question 2: The server time zone value ‘appears in MySQL й ��� ׼ʱ ‘is unrecognized record

Solution 1: modify the database time zone
set global time_ zone = ‘+8:00’; ## Modify the global time zone of Mysql to Beijing time, that is, the East 8 district where we are
in the database   set time_ zone = ‘+8:00’; ## Modify the time zone of the current session
0   flush privileges; # Effective immediately

Solution 2: modify the value of driver and URL.

<?xml version="1.0" encoding="UTF-8" ?>
<!-- Pay attention to this configuration header tag, don't make a mistake -->
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <environments default="bank">
        <environment id="bank">
            <!-- Using jdbc transaction management -->
            <transactionManager type="JDBC" />
            <!-- Database connection pooling -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver" />  
                <property name="url"
                          value="jdbc:mysql://localhost:3306/test?characterEncoding=utf-8" />
                <property name="username" value="root" />
                <property name="password" value="123456" />
            </dataSource>
        </environment>
    </environments>

    <!-- This place configures the entity class sql mapping file -->
    <mappers>
        <mapper resource="pufaSpring\otherMybatis\UserMapper.xml"/>

    </mappers>
</configuration>

org.hibernate.exception.SQLGrammarException: could not extract ResultSet, Resolve MySQL 5.7.5 or above GROUP_BY is not supported

Title: org.hibernate.exception.sqlgrammarexception: could not extract resultset, solve group above MySQL 5.7.5_ By is not supported

The reason for the problem:
MySQL 5.7.5 and above functions rely on the detection function. If only is enabled_ FULL_ GROUP_ By SQL mode (by default), MySQL will reject queries that select lists, having conditions, or order by lists, refer to non collection columns that are neither named nor functionally dependent on them in the group by clause( Before 5.7.5, MySQL did not detect any functional dependency, and only was not enabled by default_ FULL_ GROUP_ BY。 For a description of the behavior before 5.7.5, please refer to the MySQL 5.6 reference manual.)

Solution 1:

Open navcat,

Query with SQL:

select @@global.sql_ mode

The values are as follows:

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

Remove only_ FULL_ GROUP_ By, reset the value.

set @@global.sql_ mode
=’STRICT_ TRANS_ TABLES,NO_ ZERO_ IN_ DATE,NO_ ZERO_ DATE,ERROR_ FOR_ DIVISION_ BY_ ZERO,NO_ AUTO_ CREATE_ USER,NO_ ENGINE_ SUBSTITUTION’;

Solution 2:

Successful steps:

Item open

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

Scroll to the bottom of the file and copy and paste

[mysqld]
sql_ mode=STRICT_ TRANS_ TABLES,NO_ ZERO_ IN_ DATE,NO_ ZERO_ DATE,ERROR_ FOR_ DIVISION_ BY_ ZERO,NO_ AUTO_ CREATE_ USER,NO_ ENGINE_ SUBSTITUTION

Go to the bottom of the file

Save and exit input mode

sudo service mysql restart

Restart mysql
complete

Solve the problem of jdk8 after win7 is installed. Has value ‘1.8’, but ‘1.7’ is required

I win7 x64 Ultimate Edition, installed JDK7 and jdk8 at the same time, after uninstalling jdk8, the CMD command line input: Java – version, originally thought to display java version 1.7, the result of error: has value ‘1.7’, but ‘1.8’ is required

 
I look at Java_ Home, the environment variable, is found to be OK, pointing to C:// Java/jdk1.7.0

resolvent:

After JDK7 is installed, jdk8 is installed, because when JDK1.8 is installed, java.exe, javaw.exe and javaws.exe are automatically copied to the directory C: (Windows) system32, because the priority of this directory in the windows environment variable is higher than Java_ The priority of the environment variable set by home.

After understanding this, you will know the cause of the error. Although JDK1.8 has been uninstalled, the three executable files of java.exe, javaw.exe and javaws.exe in the directory of C: (Windows) system32 are still JDK1.8, so the Java version of jdk1.7 is_ Copy the three corresponding executable files under home/bin to the directory of C: Windows/system32. At this time, enter Java – version on the command line of CMD, and everything is normal.

 
My situation is just the opposite of his, so I replace several files of jdk8 and overlay them in the directory of C: (Windows) system32.

According to the above modification, enter Java – version in CMD to display version 1.8

PhpMyAdmin localhost only displays the codes of configuration

One day, when using phpMyAdmin, you can’t jump out of the login page. Instead, it’s the code in configuration (see below). Due to the Mac operating system, the methods given by the Internet for a long time are not available. Later, it seems that some of the previous settings have been overridden.

<?php
/* vim: set expandtab sw=4 ts=4 sts=4: */
/**
 * Main loader script
 *
 * @package PhpMyAdmin
 */

/**
 * Gets some core libraries and displays a top message if required
 */
require_once 'libraries/common.inc.php';

/**
 * display Git revision if requested
 */
require_once 'libraries/display_git_revision.lib.php';
require_once 'libraries/Template.class.php';

/**
 * pass variables to child pages
 */
$drops = array(
    'lang',
    'server',
    'collation_connection',
    'db',
    'table'
);
foreach ($drops as $each_drop) {
    if (array_key_exists($each_drop, $_GET)) {
        unset($_GET[$each_drop]);
    }
}
unset($drops, $each_drop);

The LoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoaLoamodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulmodulliblibliblibliblibliblibliblibliblibliblibliblibexe/ apaapache2/libliblibliblibliblibgaggaggaggaggaggaggaggaggaggaggaggaggaggaggaggaggaggaggaggaggaggaggaggaggaggaggag
sudo apachectl restart 256303;`

Get connection timeout retry: 1 MySQL errorcode 0, state 08s01 docker container accessing MySQL container is very slow and sometimes interrupted

Today, I encountered a problem: starting the Tomcat container and then accessing the MySQL container, the query speed is very slow, and sometimes it is the same as disconnecting
solution: when starting the Tomcat container, do not add the host name and MAC address, it is likely that there is a conflict

PHP message: PHP Fatal error: Uncaught PDOException: SQLSTATE[08004] [1040] Too many connections

preface

The OJ, which was built before, suddenly made an HTTP 500 error this evening. When I opened the log, I found that the MySQL database collapsed.

Text

Then open the MySQL configuration file. The path of the configuration file is as follows

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

Max the number of links in it_ Change the connections to 1000, and then restart mysql.

NettyAvroRpcClient RPC connection error

2014-12-19 01:05:42,141 (lifecycleSupervisor-1-1) [WARN – org.apache.flume.sink.AbstractRpcSink.start(AbstractRpcSink. java:294 )] Unable to create Rpc client using hostname: xxx.xxx.xxx.xxx, port: 41100
org.apache.flume.FlumeException: NettyAvroRpcClient { host: 121.41.49.51, port: 41100 }: RPC connection error

This problem occurs when flume uses Avro to accept data.

First, let’s see if the port of the connected server is monitored

If you want to send data to port 4383 of 192.168.1.1, you need a server listening to this window, otherwise RPC connection failure will occur

Docker mysql8 modify password

Docker mysql8 modify password

Set skip password login

docker exec -it mysql /bin/sh
# Note: The configuration file for editing is docker.cnf
vi /etc/mysql/conf.d/docker.cnf
exit

Restart MySQL container

docker restart mysql

Restart MySQL container

The old version of the command is invalid:
update user set password = password (‘a123456 ‘), where user =’root'</ s>
correct:

UPDATE mysql.user SET authentication_string='' WHERE user='root' and host='localhost';
-- Change the password for use without expiration
ALTER USER 'root'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; 
flush privileges;

Error handling

ERROR 2059 (HY000): Authentication plugin ‘caching_ sha2_ password’ cannot be loaded: /usr/lib64/mysql/plugin/caching_ sha2_ password.so: cannot open shared object file: No such file or directory

Maybe the password is not set and will never expire. Maybe the database client version under Linux is too low. I tried to connect to mysql8 of docker in server B on server a, but the connection was successful by using DataGrid in windows.

Creating users and authorizations

create user  'nacos'@'%' identified by '123';
grant all on mid_nacos.* to 'nacos'@'localhost' ;
flush privileges;

Here, all the permissions of the table are granted, and all the items that can be authorized are explained and referenced https://www.cnblogs.com/yinzhengjie/p/10263692.html

Solve the error of operation create user failed for ‘user’ @ ‘%

It may be that the user already exists, you can delete and recreate it to
check whether the user exists: select user from user
delete: drop user 'user' @ '%

Solving com.mysql.cj.exceptions.unabletoconnectexception: public key retrieval is not allowed

Add & amp; after the connection information; allowPublicKeyRetrieval=true

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

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

This prompt is a password error. If you forget your password, you can use this method to reset your password
* * 1. * * the first step to reset your password is to skip the password authentication process of MySQL
modify the MySQL configuration file
and find it in the directory of C:// program files/MySQL/MySQL server 5.7 my.cnf/my .ini/my- default.ini One of the three is modified
in [mysqld] skip-grant-tables

**2 * * restart MySQL service

	net start mysql

If this command reports an error and cannot start MySQL service, run it as an administrator cmd.exe ( cmd.exe In the directory of C: Windows: system32

**3. * * implementation again

	mysql -u root -p

You don’t need to enter a password. Press enter to enter mysql

**4 * * Change Password

	update user set Password=PASSWORD('888888') where User='root'; 
	FLUSH PRIVILEGES;

Use 888888 to login next time

5. If 4 reports error 1820 (HY000): you must set password before executing this statement, execute the code

 	SET PASSWORD = PASSWORD('123456');
	or SET PASSWORD = '123456';

Then prompt query OK, 0 rows affected (0.00 sec)
and you can log in with 123456 password

Error (1820) if the password is not changed in time after MySQL installation

The password of MySQL was not changed in time when logging in for the first time

When downloading MySQL for the first time, because the password was not changed in time, it was prompted to change the password first.

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

When changing the password according to the requirements, the following error occurs when executing SQL command:

mysql> alter user 'root'@'localhost' identified by '123456';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

The reason is that the password does not conform to the password rules, which is too simple.

Therefore, it is necessary to modify the password policy in time

set global validate_password_policy=0
set global validate_password_length=1;

validate_ password_ polic =
0 or low verify length only
1 or medium verify length, number, case and special characters
2 or strong verify length, number, case, special character and dictionary file

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)