Tag Archives: mysql

Solve MySQL error 1698 (28000): access denied for user ‘root’ @’localhost ‘

I believe that many users who just installed MySQL on Linux will encounter this problem, how to solve it?I found the answer on StackOverflow (I used method 1 and it worked), carried it over and translated it into Chinese.
The original address: https://stackoverflow.com/questions/39281594/error-1698-28000-access-denied-for-user-rootlocalhost

Problem description: When I log in to the MySQL database via root, I get an ERROR “ERROR 1698 (28000): Access deniedfor user ‘root’ @’ localhost ‘”.

Answer:
This is because MySQL USES the UNIX Auth_Socket Plugin by default in recent Ubuntu installations (and possibly others).
In simple terms, this means that when DB_users use the database, they will be authenticated through the system user authentication table. You can see if your root user is set to this by using the following command:

$ sudo mysql -u root # I had to use "sudo" since is new installation

mysql> USE mysql;
mysql> SELECT User, Host, plugin FROM mysql.user;

+------------------+-----------------------+
| User             | plugin                |
+------------------+-----------------------+
| root             | auth_socket           |
| mysql.sys        | mysql_native_password |
| debian-sys-maint | mysql_native_password |
+------------------+-----------------------+

As you can see from the query, root is using the AUTH_socket plug-in. There are two ways to solve this problem:
1. You can set your root user to use mysql_native_password plug-in 2. You can create a new database user that is consistent with your system user (recommended)
(Note: Method 2 meets the requirements of auth_Socket plug-in)
Option 1:

$ sudo mysql -u root # I had to use "sudo" since is new installation

mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;

$ service mysql restart

Option 2 (substitute your operating system username for YOUR_SYSTEM_USER) :

$ sudo mysql -u root # I had to use "sudo" since is new installation

mysql> USE mysql;
mysql> CREATE USER 'YOUR_SYSTEM_USER'@'localhost' IDENTIFIED BY '';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'YOUR_SYSTEM_USER'@'localhost';
mysql> UPDATE user SET plugin='auth_socket' WHERE User='YOUR_SYSTEM_USER';
mysql> FLUSH PRIVILEGES;
mysql> exit;

$ service mysql restart

Remember that if you choose to use Method 2, you should connect to MySQL by using your operating system user name (mysql-u YOUR_SYSTEM_USER).
Note: In some operating systems (such as Debian), the ‘auth_Socket’ plugin is called ‘unix_socket’, so the corresponding SQL command statement should be UPDATE User SET plugin= ‘unix_socket’ WHERE user = ‘YOUR_SYSTEM_USER’.

MySQL error code 1217 (ER_ROW_IS_REFERENCED): Cannot delete or update a parent row: a foreign key co

There was an error copying the development environment from the library with the following error message:
mysql> show slave status\G;
Last_Errno: 1217
Last_Error: Error ‘Cannot delete or update a parent row: a foreign key constraint fails’ on query.
# perror 1217
MySQL error code 1217 (ER_ROW_IS_REFERENCED): Cannot delete or update a parent row: a foreign key constraint fails
mysql> show variables like ‘foreign_key_checks’;
+ — — — — — — — — — — — — — — — — — — — – + — — — — — — — +
| Variable_name Value | |
+ — — — — — — — — — — — — — — — — — — — – + — — — — — — — +
| foreign_key_checks | ON |
+ — — — — — — — — — — — — — — — — — — — – + — — — — — — — +
1 row in the set (0.00) sec)

Solution:
1. First stop slave, forbid foreign key constraint detection, start slave;
stop slave; set global foreign_key_checks =off; start slave;

2. Observe the normal synchronous replication by show Slave Status \G, start the foreign key constraint detection, and restart the replication.

set global foreign_key_checks =off; stop slave; start slave;
This operation is limited to the development environment and should be used with caution in the production environment.

MySQL error: column ‘ID’ in field list is ambiguous

1. Error message

ERROR 1052 (23000): Column 'id' in field list is ambiguous

2. Cause analysis
Column ‘ID’ is repeated in the field list. In fact, two tables have the same field, but the name of the table field is not preceded by the name of the table, so the reference is unknown. The prefix student is no problem.
3. Solutions

SELECT student.name, student.student_id, score.score FROM student INNER JOIN score ONstudent.student_id = score.student_id WHERE student.name='mio';

+------+------------+-------+

| name | student_id | score |

+------+------------+-------+

| mio  |          1 |    99 |

| mio  |          1 |    77 |

| mio  |          1 |    88 |

| mio  |          1 |    99 |

+------+------------+-------+

4 rows in set (0.00 sec)

Follow my technical official account “Ramble on ARTIFICIAL Intelligence”, and push high-quality articles every day

MySQL reports error 1292 (22007) failure

Error in editing MySql to change the date, the code is as follows:
mysql> Insert into date values (‘ Zhangsan ‘,92-01-02,99-03-04);
ERROR 1292 (22007): pendate value: ’89’ for column ‘birth’ at row 1
changed to
mysql> Insert into date values (‘zhangsan’,19920102,19990304);
Query OK, 1 row affected (0.13 SEC)
succeeds.
 
It should be the length of the date input.

ER_ACCESS_DENIED_ERROR: Access denied for user ‘root‘@‘localhost‘ (using password: NO)

ER_ACCESS_DENIED_ERROR: Access Denied for user ‘root’@’localhost’ (using password: NO) How to handle this?
Recently, redo node. Js connect mysql, this problem has been appear, can start the project, is has been submitted to the error and write the information also can not add in mysql, then baidu search, most above all, what password is modified, said of the fog, I didn’t do this, then in a pet write before all deleted, I knocked again and again finally found an error or accidentally got the wrong number, and then see the reason for the error.
In Vscode, it’s the red box, it was password, it’s PWD, so let’s just change PWD to password.

After the two hosts set up the master-slave replication of MySQL, the show slave status displays: last_ IO_ Error: error connecting to master ……

Last_IO_Error: error connecting to master ‘Replication @vMS00782 :3306’…… Results: Last_IO_Error: error connecting to master’ Replication @vms00782 :3306’……

First, check the error log file of B and find the following errors:
ERROR: Slave I/O: ERROR connecting to master ‘replication @vms00782 :3306’ – retry-time: 60 retry: 2, Error_code: 1045

Then look at the error code from the previous section with Perror:
perror 1045
MySQL error code 1045 (ER_ACCESS_DENIED_ERROR): Access denied for user ‘%-.48s’@’%-.64s’ (using password: %s)

Is there a problem with the account used for copying?First verify on A that the replication user account exists and that the correct permissions have been granted
Mysql> show grants for ‘usvr_replication’@’%’;
+ — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — +
| Grants for usvr_replication @ %
| + — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — +
| GRANT REPLICATION SLAVE ON *.* TO ‘usvr_replication’@’%’ IDENTIFIED BY PASSWORD ‘*F4039654D0AFD80BB0A7775938EFD47ACB809529’ |
+ — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — +
1 row in the set (0.00 SEC)
No problem found!

Then, try connecting from B to A using this account:
Mysql-uusvr_replication – H 192.168.83.35-P-p55944
Enter the password you set and press enter, not connected!! Enter again, not connected yet!!
It seems that the problem is here, carefully check, the original password was a mistake!

Try to recreate the replication relationship with the correct password:
Execute on B:
mysql> stop slave;
mysql> researt slave;
mysql> change master to master_host = ‘VMS00782’,
master_user = ‘replication’,
master_password = ‘ReplPass@123456’,
master_port = 3306,
master_log_file = ‘VMS00782-bin.000001’,
master_log_pos = 120;
mysql> start slave;
mysql> show slave status;
Everything is ok!!

A few points to note:
Use the original password instead of the hashed password in the master_password section of the change master to statement.
Be aware of looking at the error log file in the first place, using the Perror tool to see specific errors based on the error code.

Other FREQUENTLY asked questions:
Mysql fails to start: first look at the error prompted in the error log file, and find the cause according to the error; Check whether the data directory and other configurations in the configuration file are correct; Check whether the owner and group of MySQL related directories are correct; Check to see if any mySQld processes are still running that were not shut down properly.
Mysql cannot connect: first check if the mySQld process is started correctly; See if the provided link string is correct.

SQL Error: 0, SQLState: 08S01 & Communications link failure

Error:

WARN : org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: 08S01
ERROR: org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Communications link failure

The data source configuration used initially is as follows

<bean id="dataSource"
          class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url"
value="jdbc:mysql://localhost:3306/db_heybar01?useUnicode=true&amp;characterEncoding=UTF-8"/>
<property name="username" value="test"/>
<property name="password" value="test"/>

</bean>

Through the error message and the preliminary judgment of searching is that the number of database connections is not enough, try to change the connection pool:
modified data source configuration:

<bean id="dataSource"
          class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass">
        <value>com.mysql.jdbc.Driver</value>
    </property>
    <property name="jdbcUrl">
        <value>jdbc:mysql://101.201.73.167:3306/db_heybar01?useUnicode=true&amp;characterEncoding=UTF-8</value>
    </property>
    <property name="properties">
        <props>
            <prop key="c3p0.minPoolSize">2</prop>
            <prop key="c3p0.maxPoolSize">50</prop>
            <prop key="c3p0.timeout">5000</prop>
            <prop key="c3p0.max_statement">100</prop>
            <prop key="c3p0.testConnectionOnCheckout">true</prop>
            <prop key="user">nupa01</prop>
            <prop key="password">NUPA_mysql_2017</prop>
        </props>
    </property>
</bean>

Pit encountered by entity class data type BigDecimal

Scene:
The price data type in the database is Decimal, and the price data type in entity class A is BigDecimal. When it is necessary to use equals method to compare the A1 object passed from the front end with the A2 object quashed from the database, the result is false even if the object attributes are the same.
why
Debug found that the price value of the object A1 passed from the front end was 0.1 after JSON transformation, while the price value of the a2 object query from the database was 0.1000. Alt + INSERT equals method was used to call the EQUALS method of BigDecimal.

        BigDecimal a = new BigDecimal("1.0");
        BigDecimal b = new BigDecimal("1.00");
        System.out.println(a.compareTo(b) == 0 );  // true
        System.out.println(a.equals(b));  // false

The equals method of BigDecimal compares precision and values, while the compareTo method only compares values.
The solution

 @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        ProProductSkuEntity that = (ProProductSkuEntity) o;
        return Objects.equals(id, that.id) &&
                Objects.equals(productId, that.productId) &&
                compareBigDecimal(price, that.price);
    }

    public boolean compareBigDecimal(BigDecimal a, BigDecimal b) {
        return (a == b) || (a != null && a.compareTo(b) == 0);
    }

Modify the equals method overwritten in the entity class, customize the compareBigDecimal method, use compareTo to compare values of the BigDecimal type, and you can also put this method in the utility class.

Solve the problem that node. js USES MySQL to connect econnunion 127.0.0.0.1:3306 

const sequelize = new Sequelize('mysql', 'account', 'password', {
    host: 'localhost',
    // host: '118.25.35.81',
    dialect: 'mysql',
    operatorsAliases: false,
    dialectOptions: {
        charset: "utf8mb4",
        collate: "utf8mb4_unicode_ci",
        supportBigNumbers: true,
        bigNumberStrings: true
    },
    socketPath: '/tmp/mysql.sock',
    pool: {
        max: 5,
        min: 0,
        acquire: 30000,
        idle: 10000
    },
    timezone: '+08:00' 
});

solution :

socketPath: '/tmp/mysql.sock',

MYSQL ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

This may be because MySQL has set foreign Key association in InnoDB, which makes it impossible to update or delete data. You can avoid this situation by setting the FOREIGN_KEY_CHECKS variable.
SET FOREIGN_KEY_CHECKS = 0;
SET
SET FOREIGN_KEY_CHECKS = 1;

SET FOREIGN_KEY_CHECKS = 0;
SET
SET FOREIGN_KEY_CHECKS = 1;
ERROR 1452 (23000): Cannot add or update a Child row: a foreign key constraint fails…… (foreign key restrictions cannot be inserted)
can also use (SET FOREIGN_KEY_CHECKS = 0; ) insert