Category Archives: MYSQL

MySQL error message: subquery returns more than 1 row and its solution

When practicing MySQL join table query, we encountered such a problem

– question: query the relevant information of all students whose “biology course” scores are higher than “physics course”

– error instruction:

1 SELECT  student.sid AS 'number', student.sname AS 'name', course.cname AS 'course', score.num AS 'performance' 
2 FROM student INNER JOIN course INNER JOIN score  
3 ON student.sid=score.student_id AND course.cid=score.course_id AND course.cid=2 
4 WHERE score.num < (SELECT score.num FROM course INNER JOIN score ON course.cid=score.course_id AND course.cid=1);

Problematic instructions

– error information:

ERROR 1242 (21000): Subquery returns more than 1 row

– error information translation:

subquery returns more than 1 line

– Analysis and solutions:

1. This problem can be solved by removing the duplicate data

– prevent data from being written repeatedly by adding logic judgment or foreign key during writing

2. Use in, some, any and all keywords to restrict

– the error information comes from the subquery, so it is necessary to modify the conditions of the instructions involved in the subquery

– final resolution instruction:

1 SELECT  student.sid AS 'student.name AS 'name', course.cname AS 'course', score.num AS 'grade'
2 FROM student INNER JOIN course INNER JOIN score
3 ON student.sid=score.student_id AND course.cid=score.course_id AND course.cid=2 
4 WHERE score.num < ANY(SELECT score.num FROM course INNER JOIN score ON course.cid=score.course_id AND course.cid=1);

Final resolution instruction

supplement

Subquery refers to nesting another select statement in a select statement.
in, some, any and all are keywords involved in subquery

– any and = (& gt>=, & lt;, & lt;=, & lt;>) In combination, it respectively represents any data equal to (greater than, greater than or equal to, less than, less than or equal to, not equal to)

The — any keyword must be used with a comparison operator

— any keyword can be understood as “for any value in the column returned by the subquery, if the comparison result is true, return true”

– all can be associated with = (& gt>=, & lt;, & lt;=, & lt;>) In combination, it represents all data equal to (greater than, greater than or equal to, less than, less than or equal to, not equal to) respectively

The — all keyword must be used with a comparison operator

— all keyword can be understood as “for all values in the column returned by the subquery, if the comparison result is true, return true”

– the keyword in has the same effect as the keyword combination “= any”

1 SELECT s1 
2 FROM t1 
3 WHERE s1 =ANY(SELECT s1 FROM t2);
4 -- Effectiveness Equivalent
5 SELECT s1 
6 FROM t1 
7 WHERE s1 IN(SELECT s1 FROM t2);

Examples of in and = any

– notin and “& lt> “All” has the same usage and function

1 SELECT s1 
2 FROM t1 
3 WHERE s1 <>ANY(SELECT s1 FROM t2);
4 -- Effectiveness Equivalent
5 SELECT s1 
6 FROM t1 
7 WHERE s1 NOT IN(SELECT s1 FROM t2);

<> Examples of any and not in

– some is the alias of any

1 SELECT s1 FROM t1 WHERE s1 <> ANY(SELECT s1 FROM t2);
2 -- Effectiveness Equivalent
3 SELECT s1 FROM t1 WHERE s1 <> SOME(SELECT s1 FROM t2);

Examples of any and some

— in terms of understanding, some are easier to explain than any. In the above example, the instruction involving the keyword “some S1 in table t1 are not equal to S1 in table t2”, and the instruction involving the keyword “any” is interpreted as “all S1 in table t1 are not equal to S1 in table t2”

[Solved] MySQL ERROR 1133 (42000): Can’t find any matching row in the user table

ERROR 1133 (42000): Can’t find any matching row in the user table

Today, after executing grant all privileges on cache_cloud.* to ‘cachecloud’@’%’; an error message appears: ERROR 1133 (42000): Can’t find any matching row in the user table.

As follows:

Solution.

Execute the statement.flush privileges;

Execute the statement again: grant all privileges on cache_cloud.* to ‘cachecloud’@’%’; Success.

The overall execution statement is as follows.

[Solved] Mysql 5.7 Change Passwords ERROR 1054 (42S22): Unknown column ‘password’ in ‘field list’

1. Environment

Reinstalled the environment on the new server, which was 5.6, and upgraded to version 5.7.

2. Problems

A new installation of MySQL 5.7 prompted a password error when logging in. I didn't change the password when I installed it, but later changed it by logging in password-free.

Enter.

update mysql.user set password=password('root') where user='root'

Error message: ERROR 1054 (42S22): Unknown column 'password' in 'field list'

3. Solutions

There is no password field in MySQL 5.7 + database. The password field is changed to authentication_ string.

#Just change the change statement to the following

update mysql.user set authentication_ string=password(‘root’) where user=’root’ ;

#Refresh permissions

flush privileges;

[Solved] MySQL ERROR 1054 (42S22): Unknown column ‘i2goods.t_ebook_data.fbookid’ in ‘field list’

1.1.1 Phenomenon

When importing data, the following error occurs.

ERROR 1054 (42S22) at line 734: Unknown column ‘i2goods.t_ebook_data.fbookid’ in ‘field list’

1.1.2 Reason

The internet usually says that there is indeed a column not found, and after checking the imported SQL file, the error is a statement to create a view, the SQL statement is as follows.

VIEW `v_ebook_data` AS

select

`t_ebook_data`.`fbookid` AS `fbookid`,

`t_ebook_data`.`fperiodid` AS `fperiodid`,

1 AS `fdevicetype`,

`t_ebook_data`.`ftitlephoto` AS `ftitlephoto`,

`t_ebook_data`.`fformat` AS `fformat`,

`t_ebook_data`.`ffilepath` AS `ffilepath`,

`t_ebook_data`.`ffilesize` AS `ffilesize`,

`t_ebook_data`.`fdataversion` AS `fdataversion`,

`t_ebook_data`.`fdataid` AS `fdataid`

from `t_ebook_data`

where (`t_ebook_data`.`fdevicetype` = 1) union select `t1`.`fbookid` AS `fbookid`,`t1`.`fperiodid` AS `fperiodid`,1 AS `fdevicetype`,`t1`.`ftitlephoto` AS `ftitlephoto`,`t1`.`fformat` AS `fformat`,`t1`.`ffilepath` AS `ffilepath`,`t1`.`ffilesize` AS `ffilesize`,`t1`.`fdataversion` AS `fdataversion`,`t1`.`fdataid` AS `fdataid` from `t_ebook_data` `t1` where ((`t1`.`fdevicetype` = 0) and (not(exists(select 1 from `t_ebook_data` `t2` where ((`t2`.`fdevicetype` = 1) and (`t1`.`fbookid` = `t2`.`fbookid`) and (`t1`.`fperiodid` = `t2`.`fperiodid`)))))) union select `t_ebook_data`.`fbookid` AS `fbookid`,`t_ebook_data`.`fperiodid` AS `fperiodid`,2 AS `fdevicetype`,`t_ebook_data`.`ftitlephoto` AS `ftitlephoto`,`t_ebook_data`.`fformat` AS `fformat`,`t_ebook_data`.`ffilepath` AS `ffilepath`,`t_ebook_data`.`ffilesize` AS `ffilesize`,`t_ebook_data`.`fdataversion` AS `fdataversion`,`t_ebook_data`.`fdataid` AS `fdataid` from `t_ebook_data` where (`t_ebook_data`.`fdevicetype` = 2) union select `t1`.`fbookid` AS `fbookid`,`t1`.`fperiodid` AS `fperiodid`,2 AS `fdevicetype`,`t1`.`ftitlephoto` AS `ftitlephoto`,`t1`.`fformat` AS `fformat`,`t1`.`ffilepath` AS `ffilepath`,`t1`.`ffilesize` AS `ffilesize`,`t1`.`fdataversion` AS `fdataversion`,`t1`.`fdataid` AS `fdataid` from `t_ebook_data` `t1` where ((`t1`.`fdevicetype` = 0) and (not(exists(select 1 from `t_ebook_data` `t2` where ((`t2`.`fdevicetype` = 2) and (`t1`.`fbookid` = `t2`.`fbookid`) and (`t1`.`fperiodid` = `t2`.`fperiodid`)))))) union select `t_ebook_data`.`fbookid` AS `fbookid`,`t_ebook_data`.`fperiodid` AS `fperiodid`,3 AS `fdevicetype`,`t_ebook_data`.`ftitlephoto` AS `ftitlephoto`,`t_ebook_data`.`fformat` AS `fformat`,`t_ebook_data`.`ffilepath` AS `ffilepath`,`t_ebook_data`.`ffilesize` AS `ffilesize`,`t_ebook_data`.`fdataversion` AS `fdataversion`,`t_ebook_data`.`fdataid` AS `fdataid` from `t_ebook_data` where (`t_ebook_data`.`fdevicetype` = 3) union select `t1`.`fbookid` AS `fbookid`,`t1`.`fperiodid` AS `fperiodid`,3 AS `fdevicetype`,`t1`.`ftitlephoto` AS `ftitlephoto`,`t1`.`fformat` AS `fformat`,`t1`.`ffilepath` AS `ffilepath`,`t1`.`ffilesize` AS `ffilesize`,`t1`.`fdataversion` AS `fdataversion`,`t1`.`fdataid` AS `fdataid` from `t_ebook_data` `t1` where ((`t1`.`fdevicetype` = 0) and (not(exists(select 1 from `t_ebook_data` `t2` where ((`t2`.`fdevicetype` = 3) and (`t1`.`fbookid` = `t2`.`fbookid`) and (`t1`.`fperiodid` = `t2`.`fperiodid`))))))

 

When I opened the database with the tool and checked the exported table t_ebook_data, there was indeed the field fbookid, so the error message felt baffling.

Later, after careful analysis, I found that there is a prefix in front of the table name: ‘i2goods’, which is actually the name of the database, so I suspected that this is not the cause, and deleted all the ‘i2goods’ prefixes in the statement on the table, and the result was fine. The result was fine.

1.1.3 Solution

Remove all the database name prefixes from the create view statement, then perform the import operation, and it works.

In addition, to solve the following Got error: 1449 problem, after raising the privileges of the root user, you don’t need to remove the database name prefix in the above way, because when you look at the exported SQL file, all SQL statements in it have no database name prefix, so the import is fine.

[Solved] MYSQL ERROR 1044 (42000): Access denied for user ”@’localhost’ to database ‘mys…

After MySQL is installed, MySQL directly enters the database

After setting the remote connection, the following error will be prompted:

Prompt: error 1044 (42000): access denied for user ‘@’localhost’ to database ‘MySQL’. The reason is that in the user table of MySQL database, there is an account whose user name is empty, that is, an anonymous account. In fact, it logs in anonymously. It can be seen from the “@ ‘localhost” in the error prompt. Therefore, the solution is shown in method 2

method 1: (applicable to those with incorrect password)

0. Ideas:

By shielding the login password of MySQL, first enter into mysql, and then update the password through the update command

1. Close mysql

Service mysqld stop/Linux

Net stop MySQL// window

2. Shielding permissions
mysqld_ Safe — skip grant table// in Linux

Mysqld — skip grant table// window

Or use the following command

mysqld_ safe –user=mysql –skip-grant-tables –skip-networking & // Using Linux

the screen appears: starting demo from

3. Open a new terminal and input
?MySQL – U root MySQL
MySQL > UPDATE user SET Password=PASSWORD(‘newpassword’) where USER=’root’;
mysql> FLUSH PRIVILEGES; // Remember this sentence in the update command, otherwise, if you close the previous terminal, the original error will appear again
MySQL & gt\ q

method 2: (applicable to the presence of empty password)

0. Ideas

Sometimes, although MySQL has an account and a corresponding password. However, due to the existence of a blank password, it will log in to the blank password by default

If you need to delete the account with empty password, you can view all the accounts through the following command

select host,user,password from user;

1. Close MySQL
?Service mysqld stop

2. Shield permissions
?Mysqld_ Safe — skip grant table
the screen appears: starting demo from

3. Start a new terminal to input
# MySQL – U root MySQL
MySQL > delete from user where USER=”; // Delete empty password
MySQL > FLUSH PRIVILEGES;// Remember this sentence, otherwise, if you close the previous terminal, the original error will appear again
MySQL > exit

4. Finally solve the startup error

Check the MySQL startup log and find that port 3306 is occupied. Kill the MySQL process and restart the MySQL service

[Solved] MYSQL ERROR 1044 (42000): Access denied for user ”@’localhost’ to database ‘mysql’

mysql> use mysql
ERROR 1044 (42000): Access denied for user ”@’localhost’ to database ‘mysql’

Solution:

# mysqld_safe –skip-grant-table
161104 10:36:00 mysqld_safe Logging to ‘/var/log/mysqld.log’.
161104 10:36:00 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

At this point the terminal is in a waiting state, open another terminal to change the root password of mysql

# mysql -u root

mysql> update user set password=PASSWORD(”) where user=’root’; # Here I set the root password to null
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3 Changed: 0 Warnings: 0

mysql> delete from user where user=”; # Delete this ”@’localhost” user, otherwise it will still default to this user after login

Query OK, 2 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit

After changing the root password of mysql, stop the mysqld service, at this point, the above terminal will stop waiting.

# /etc/init.d/mysqld restop
Stopping mysqld: [ OK ]

Starting mysqld: [ OK ]

The problem is solved by logging into mysql again.

[Solved] MYSQL Error 1040 (HY000): too many connections

Error 1040 (HY000): too many connections

the first processing method:

./mysql -u root -p

After successful login, execute the following statement to query the current maximum number of connections:

select VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES where VARIABLE_NAME='MAX_CONNECTIONS';

Execute the following statement to modify the maximum number of connections:

set global max_connections = 10000;

You can view it through the following statement:

show variables like '%max_connections%';

The second way to handle it:

vim /etc/my.cnf

Under the paragraph [mysqld], add

max_connections=10000

It needs to be restarted to take effect

show full processlist; You can view some status

The first way is to take effect immediately in real time, but it will be restored to the original configuration after restart. The second way is to read the configuration file. The parameters here will be used when starting mysql, which is equivalent to permanent effect

[Solved] MySQL ERROR 1062 (23000): Duplicate entry ‘0’ for key ‘PRIMARY’

1.1 phenomenon

when executing database operation, the following error message appears:

ERROR 1062 (23000): Duplicate entry ‘0’ for key ‘PRIMARY’

1.1.2 reasons

before the problem table, the primary key was not set as an automatic growth column. Later, the program needs to modify it as an automatic growth column. As a result, the first data is inserted. The primary key is 0 , and if the data is added later, the above error will appear. The information is that every time the data is added in the future, the primary key is 0 , So it leads to this error

it is estimated that when the table was created, the primary key was not an automatic growth column, but it was later modified to an automatic growth column. However, there was a problem with the MySQL engine recognition

1.1.3 solve

drop the table drop , and then create it again with correct statements (including the primary key is the auto growth column)

SQL Error: 1064, SQLState: 42000 [Three Methods to Solve]

If you accidentally use the keywords of the database, you will report the error “SQL error: 1064, sqlstate: 42000”

There are three solutions

1.Enclose the table name or field name in square brackets ([])

XML configuration:

<property name="desc" type="string" >   
<column name="[DESC]" length="255" not-null="true" />   
</property>

Note:

@Column(name = "[DESC]", nullable = false)   
public String getDesc() { return this.desc; }

2. Enclose the table name or field name with two accents (‘). The accented key is the key to the left of the “1” key and the key to the top of the “tab” key on the keyboard. This symbol is also known as “inverted quotation marks.”

XML configuration:

<property name="desc" type="string" >   
<column name="`DESC`" length="255" not-null="true" />   
</property>

Note:

@Column(name = "`DESC`", nullable = false)   
public String getDesc() { return this.desc; }

3. Use double quotation marks (“) to enclose the table name or field name

<property name="desc" type="string" >   
<column name='"DESC"' length="255" not-null="true" />  
 </property>

Note:

@Column(name = "\"DESC\"", nullable = false)   
public String getDesc() { return this.desc; }