Tag Archives: mysql

The solution of 2003 can’t connect to MySQL server on ‘localhost’ (10061)

Front end developers must read! Starting from scratch, teach you to build a low code website platform in stages>>>

Problem description

Bloggers today use the database to find such a problem:

The Internet said that the database needs to be configured, but they didn’t come to the point. We need to configure the remote access port of the server

Answer to the question

Use the following command:

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

Then change the place in the figure to 0.0.0.0:

Don’t forget to restart MySQL service:

sudo service mysql restart

Then run again, you can connect

MySQLERROR 1558 (HY000): Column count of mysql.user is wrong.

Front end developers must read! Starting from scratch, teach you to build a low code website platform in stages>>>

MySQL error 1558 (HY000): column count of mysql.user is wrong

1. The following error was reported when creating database with MySQL today:

mysql> CREATE USER 'dev'@'%' IDENTIFIED BY '123456'; 
ERROR 1558 (HY000): Column count of mysql.user is wrong. Expected 43, found 42. Created with MySQL 50560, now running 50643. Please use mysql_upgrade to fix this error.

2. Solutions

The main reason for this error is that I upgraded the database and did not use it. Just use the following command:

[root@vm172-31-0-6 ~]# mysql_upgrade -u root -p
Enter password: 
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
OK

3. Create the database again

mysql> CREATE USER 'dev'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

Discovery successfully created

Centos7 Install MYSQL Error: Could NOT find Curses (missing CURSES_LIBRARY CURSES_INCLUDE_PATH)

Today, when installing mysql 5.7, I had the following problem when compiling.

[root@localhost software]# cd mysql-5.7.21

[root@localhost mysql-5.7.21]# cmake .

The following error message appears.

— Could NOT find Curses (missing: CURSES_LIBRARY CURSES_INCLUDE_PATH)
CMake Error at cmake/readline.cmake:82 (MESSAGE):
Curses library not found. Please install appropriate package,

remove CMakeCache.txt and rerun cmake.On Debian/Ubuntu, package name is libncurses5-dev, on Redhat and derivates it is ncurses-devel.
Call Stack (most recent call first):
cmake/readline.cmake:126 (FIND_CURSES)
cmake/readline.cmake:216 (MYSQL_USE_BUNDLED_LIBEDIT)
CMakeLists.txt:250 (MYSQL_CHECK_READLINE)

— Configuring incomplete, errors occurred!

 

Solution:

[root@xxxxxxx mysql-5.7.21]# rm CMakeCache.txt

[root@xxxxxxx mysql-5.7.21]# yum install ncurses-devel

[root@xxxxxxx mysql-5.7.21]# yum install bison

[[email protected]]# make && make install

Done!

Solution of data truncated for column ‘xxx’ in MySQL

Explain the function of static keyword and final keyword in Java in detail>>>

DATA truncated FOR COLUMN ‘description’ AT ROW 1

1. Error reappearance

Null field in table

At this point, modify a field in the table as the primary key

2. Solutions

Null fields are not allowed in the database

Null fields are not allowed in the database

Null fields are not allowed in the database

3. Cause of the problem

This error is actually caused by the illegal inserted data

For example: garbled code, over field length, illegal characters, etc., the data inserted here is caused by over field length

At that time, the field description (description) of the table in MySQL database was varchar (32), and the number of inserted data was more than 32, so it can be modified to varchar (300)

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 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

MySQL exclude error 1114 holmium HY000 41 holmium“

What are the eight life cycle hook functions of Vue>>>

Action: execute alter table aa_ table drop column hehe; Delete AA_ The hehe field in the table. Error 1114 (HY000): the table’s y_ transaction_ info’ is full。

To delete a field, the disk space needs to be larger than the space occupied by the table itself (for example, if the current table occupies 40 GB, the disk free space must be larger than 40 Gb)

According to the observation, it is inferred that the behavior of deleting fields is: create a new table, delete the field is no longer created, and then import the original table data into the new table

1. Check the disk usage (whether there is no disk space)

df -H

2. MySQL my.cnf configuration file, set TMP_ table_ Size is greater than max_ heap_ table_ size。

View MySQL installation path

show variables like “%char%”;

mysql> show variables like ‘%table_ size%’;

Modify my.cnf configuration file and restart mysql

-The configuration file of MySQL under Linux is my.cnf, which is usually placed in/etc/my.cnf,/etc/MySQL/my.cnf

Add or modify the configuration shown in the red box below

Restart MySQL

Modified successfully

Perform the operation again and you will be successful

Analysis of common error types in the use of MySQL database

>>>

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>2.304444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444441919919919981111191919191919191919191919191919191919191919191919191919191919191919191919191919191919191919191919191919191919191919191919191919191919191919191919202020202020202020202020202020202020209999191919191919191919191919191919191919191919191919191919191919191919191919191919191919191919191919191919191919191919191919191919191919191919191919191919191919191920202020202020202020202020202020p>

25253d;381699; 38169;- 35823;- 26512; 22791; 278808;

ERROR 1044 (42000):Access denied for user

35265;- 25143;- 25480;- 26435;- 19981;- 36275s;38470;- root25143;- 21464;- 24403;- 25143;- 23545;- 24212;- 26435;- 38480
ERROR 1045 (28000): Access denied for user ***(using password: YES) 25454;- 24211;- 25298;- 32477;24405;- 38169;- 35823;

ERROR 1054 (42S22): Unknown column *** in’where clause’

 26681; 25454; 38480; 26465; 21040; 210151;

ERROR 1064 (42000): You have an error in your SQL syntax;

 38169;
ERROR 1072 (42000): Key column’***’doesn’t exist in table

ERROR 1130: Host 10.0.0.1 is not allowed to connect to this MySQL server

 19981;”20801;” 35768;”36807;” ip10.0.1.35775;”3838382;” 25454;”242111;

24405;- 38169;- 35823d;root25143d;20462;- 259133d;-25143d;-244053d;
ERROR 1133 (42000): Can’t find any matching row in the user table UserLogin

flush privileges; “21047;” 36941; 20877;”35797;” 22914;”26524;” 36824;”19981;” 26680;”25143;” 30830;”233844;”

ERROR 1142 (42000): CREATE command denied to user’guest04′ 25454;- 24211;- 25298;- 32477;- 25191;- 21019;- 20196; guest04-30456;- 25480;- 26435
ERROR 1144 (42000): Illegal GRANT/REVOKE command;
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation 25454;- 24211;- 25298;- 32477;- 25191;- 27809;- 26377;- 21462;- 24471;- 30456;- 24212;- 20316;- 254806;- 26435; 27492;- 2278888;- 21019;- 25143d;
ERROR 1241 (21000): Operand should contain 1 column(s) 36827;”35810;” 20505; 1d21015;”20294;” 25321;”23545;” 35937;”19981;” 21807;”
ERROR 1248 (42000): Every derived table must have its own alias
ERROR 1396 (HY000): Operation CREATE USER failed for *** 21019;”25143;” 22833;”36133;

30475;- 25143; 3083035748;- 21019;- 25143;- 240503;- 23384;- 2291414;- 2652444;-21018;- 21018;- 210244;-385009493;- 39588;-2358111;- 35797;- 2091515153p>

drop ***
flush privileges;//

ERROR 1701 (42000): Cannot truncate a table referenced 22833;, 36133; 36824;, 26377;”38190;” 20917;”20917;” 24403;”254544;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements 31574;- 30053;- 24322;- 26684;- 19981;- 31526;- 2151212;- 24403;- 26684;- 277144;