preface

Hello, my friends, I am Amu! Recently, I am preparing to work on the cloud, and many scenarios need to be taken into account; For example, mongo, mysql, Redis, Splinx and other migration work, which involves version compatibility issues; Before the migration, Amu migrated mysql to another container and found that the version of mysql on the migration machine was 5.7 or higher. As a result, SQL statement compatibility problems occurred. So I took the opportunity to collate all kinds of mysql common problems encountered long ago to share with the gold mining friends. Small friends can be stored up oh, encounter common errors can be quickly queried to solve ~~~

Mysql > connect to localhost

Error code:
ERROR 2003(HY000) : Can 'tconnect to MySQL server on 'localhost' (10061)
Copy the code
Error cause:
(1) Localhost exists; Mysql > mysql > mysql > Mysql > Mysql > mysql > mysql > mysql > mysql > mysql > mysql > mysql > mysql > mysql > mysql It is common to look at processList to see how threads and connections are running:1,showProcesslist can only list the current100We can see all users connected to mysql> show processlist;
        +----+-----------------+-----------+------+---------+-------+------------------------+------------------+
        | Id | User            | Host      | db   | Command | Time  | State                  | Info             |
        +----+-----------------+-----------+------+---------+-------+------------------------+------------------+
        |  4 | event_scheduler | localhost | NULL | Daemon  | 30404 | Waiting on empty queue | NULL             |
        | 14 | root            | localhost | NULL | Query   |     0 | starting               | show processlist |
        +----+-----------------+-----------+------+---------+-------+------------------------+------------------+
        2 rows in set (0.00 sec)
       
       2Check all links in mysql> show full processlist;
        +----+-----------------+-----------+------+---------+-------+------------------------+-----------------------+
        | Id | User            | Host      | db   | Command | Time  | State                  | Info                  |
        +----+-----------------+-----------+------+---------+-------+------------------------+-----------------------+
        |  4 | event_scheduler | localhost | NULL | Daemon  | 30527 | Waiting on empty queue | NULL                  |
        | 14 | root            | localhost | NULL | Query   |     0 | starting               | show full processlist |
        +----+-----------------+-----------+------+---------+-------+------------------------+-----------------------+
        2 rows in set (0.00SEC) # select * from 'mysql' where 'Id' is assigned 'connection_id' when you log in to mysql> select connection_id();
    +-----------------+
    | connection_id() |
    +-----------------+
    |              14 |  -- The id assigned by the system is 14
    +-----------------+
    1 row in set (0.01The SEC) 2.UserMysql > select * from Host where Host is connected to mysql; The source port can be found, and the user whose statement is faulty can be traced. ④ Name of the database connected by the DB ⑤ Command Command executed in the current link; Query, sleep,connect(connection), daemon(daemon)TimeCurrent connection duration, in seconds. ⑦ State Displays current connection durationsqlStatement status ⑧ Info displaysqlStatements, pairs are used to judgesqlIt matters if the statement is wrongCopy the code
Problem solution:
Brew services start mysql mysql. Server start centos Systemctl start mysqld.service service mysql start Other: Find the root directory of the execution file and execute the startupCopy the code

2. Failed to connect to localhost/IP address

Error code:
➜  ~ mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
Copy the code
Error cause:
① Overview: User root is denied access to localhost/IP. ② The user name or password of the database is different from that of the mysql serverCopy the code
Problem solution:
1. Check the mysql configuration file. 2. Check the loading order of the mysql configuration fileCopy the code
Note the MAC version scheme:
Mysql > mysql > mysql > mysql > mysql > mysql
➜ ~ ps aux | grep mysql - check mysql running processes Can see the start address amu 15262 4899000 407680 0.0 4.9?? S 0:01 1:14 afternoon. 18 / usr/local/opt/mysql/bin/mysqld -- basedir = / usr/local/opt/mysql - datadir = / usr/local/var/mysql --plugin-dir=/usr/local/opt/mysql/lib/plugin --log-error=liyangyang.local.err --pid-file=liyangyang.local.pid --socket=/tmp/mysql.sockCopy the code
Check whether the mysql configuration file is loaded.
➜ ~ / usr/local/opt/mysql/bin/mysqld -- verbose -- help | grep - A 1 'Default options' Default options are read from the following files in the given order: /usr/local/etc/my.cnf ~/.my.cnf / / mysql-my.cnf; / / mysql-my.cnf; / / mysql-my.cnf; / / mysql-my.cnf; No configuration file is configured after mysql is installed. You can edit a copy called my.cnf; Modify the file owner and subordinate to the group and the executable permissions can be (1) the mkdir/usr/local/mysql/etc (2) the vim/usr/local/mysql/etc/my CNF - content can copy a configuration file (3) local chown -r Root, root/usr/local/mysql/etc/(4) chmod 755 / usr/local/mysql/etc/my CNFCopy the code
Mysql > select * from ‘mysql’ where ‘mysql’ = ‘mysql’;
# # to see whether or not to use the specified directory. My CNF ➜ ~ ps aux | grep mysql | grep '. My CNF '# # to check the mysql default read my. ➜ ~ CNF directory mysql -- help | grep'. My CNF ' order of preference, my.cnf, $MYSQL_TCP_PORT, /usr/local/etc/my.cnf; /usr/local/etc/my.cnf ~/.my.cnf; Equivalent to the above search statementCopy the code
④ Password-free login and password change
[mysqld] skip_grant_tables [mysqld] skip_grant_tables [mysqld] skip_grant_tables [mysqld] skip_grant_grant_tables Mysql -uroot -p ### mysql5.8 mysql -uroot -p ### mysql5.8 / usr/local/opt/mysql/bin/mysqld - the console - skip - grant - tables - Shared - memory (2) open a new window: Set password for root@localhost = 'new password '; mysqld -uroot -p =' new password '; Update user set authentication_string=' new password 'where user='root'; Alter user 'root'@'localhost' identified with mysql_native_password by 'new password '; ### refresh privileges on mysql; Mysql > update user set authentication_string='root' where user='root'; Query OK, 1 row affected (0.01sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> set password for root@localhost = 'root'; Query OK, 0 rows affected (0.02sec) mysql> alter user 'root'@'localhost' IDENTIFIED with mysql_native_password by 'root'; Query OK, 0 rows affected (0.01sec) mysql> flush privileges; Query OK, 0 rows affected (0.01sec) mysql> exit ByeCopy the code

3. Failed to connect to the mysql server

Error code:
➜  ~ mysql -uroot -p
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock'(2)Copy the code
Error cause:
The mysql server is enabled, but the socket file cannot be foundCopy the code
Connect to mysql:
### Socket (socket) connection: only if the mysql client and the database instance are on the same server (local connection); The connection to localhost is usually made through a Unix domain socket file, usually/TMP /mysql.sock; Mysql > show variables like 'socket'; mysql> show variables like 'socket'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | socket | / TMP/mysql. The sock | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.04 SEC) # # #, of course, we can also through the socket file login ➜ ~ mysql database -uroot -proot -s/TMP /mysql.sock -- user name + password + Socket file path [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. ............. Note: mysql.sock must be a file configured in mysql and must exist under/TMP; ### TCP/IP connection: ➜ ~ mysql -uroot -proot -h 127.0.0.1 -- user name + password + IP :port mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. ............. #### Description Overview If the TCP/IP address is used to connect to mysql; Mysql > use mysql; mysql> use mysql; mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select host, user from user; +-----------+------------------+ | host | user | +-----------+------------------+ | localhost | mysql.infoschema | | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +-----------+------------------+ 4 rows in Set (0.00 SEC) ① host indicates that the user can access the database only through the IP address of localhost ② host:% indicates that any IP address can connect to the mysql instanceCopy the code
Problem solution:
① Modify the configuration file to add the socket path ➜ ~ vim /usr/local/etc/my.cnf [mysqld] socket=/ TMP /mysql.sock ② Use soft connection to existing mysql. The sock soft chain to/TMP/mysql. The sock ln -s/usr/local/mysql/XXX. The sock/TMP/mysql. The sock (3) the most the most violent solution; Uninstall mysql, mysql related deletion; 😭 😭 😭 ➜ ~ brew remove mysql ➜ ~ brew cleanup 😭 ~ launchctl unload -w ~ / Library/LaunchAgents/homebrew. MXCL. Mysql. The plist ➜ ~ rm ~ / Library/LaunchAgents/homebrew MXCL. Mysql. The plist ➜ ~ sudo rm - rf / usr/local/var/mysql brew install mysql ➜ ➜ ~ ~ launchctl load ~ / Library/LaunchAgents/homebrew MXCL. Mysql. Plist - set the startup/Users / / Library/LaunchAgents/homebrew MXCL. Mysql. Plist: service already the loadedCopy the code

4. Cannot connect to the mysql server

Error code:
➜  ~ mysql -uroot -p
ERROR!The server quit without updating PID file (/usr/local/var/mysql/***MacBook-Pro.local.pid)
Copy the code
Error cause:
The mysql server is enabled, but the socket file cannot be foundCopy the code
Problem solution:
sudo chown -R mysql:mysql /usr/local/mysql/
Copy the code

5. The database cannot be connected due to the excessive number of connections

Error code:
➜  ~ mysql -uroot -p
ERROR 1040 (0000) : Too many connectionsCopy the code
Error cause:
The number of links in mysql has exceeded the default value. (2) The value of wait_timeout set by mysql may be too large. As a result, the idle wait for the connection is too long, and the current number of connections becomes largeCopy the code
How to solve the problem:
Mysql > show variables like "%connections"; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | max_connections | 151 The biggest connection | | - mysql support max_user_connections | | 0 - users can maximum number of connections in | mysqlx_max_connections | | 100 - the maximum number of concurrent client connections can be accepted Same as max_connections; Increased 8.0 + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 3 rows in the set (0.01 SEC) # # # look at maximum number of connections mysql > show status like 'Threads %'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_cached | 1 | -- Refers to the thread pool mysql management how much can be reuse resources | Threads_connected | | - refers to the number of connections to open; To show the processlist same result | Threads_created | | 10 - refers to the newly created thread | Threads_running | 2 | -- is refers to the number of connections in real operation; The current number of concurrent + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + 4 rows in the set (0.04 SEC) # # # to check the connection timeout configuration mysql > show variables like '% % timeout'; +-----------------------------------+----------+ | Variable_name | Value | +-----------------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | have_statement_timeout | YES | | innodb_flush_log_at_timeout | 1 | | innodb_lock_wait_timeout | 50 | | Innodb_rollback_on_timeout | OFF | | interactive_timeout | | - 28800 server is shut down an interactive connection before the number of seconds to wait activity | lock_wait_timeout | 31536000 | | mysqlx_connect_timeout | 30 | | mysqlx_idle_worker_thread_timeout | 60 | | mysqlx_interactive_timeout | 28800 | -- Server is shut down an interactive connection before the number of seconds to wait activity | mysqlx_port_open_timeout | 0 | | mysqlx_read_timeout 30 | | | mysqlx_wait_timeout | | - 28800 Waiting for the interactive client timeout seconds | mysqlx_write_timeout 60 | | | net_read_timeout 30 | | | net_write_timeout 60 | | | Rpl_stop_slave_timeout | 31536000 | | slave_net_timeout 60 | | | wait_timeout | | - 28800 server is shut down the interactive activities waiting for the number of seconds before connection + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + 20 rows in the set (0.00 SEC)Copy the code
Problem solution:
Mysql > set global max_connections =200; mysql> set max_connections =200; Query OK, 0 rows affected (0.00 SEC) ② mysql> set global max_user_connections =100; Query OK, 0 rows affected (0.00 SEC) ③ mysql> set global mysqlX_max_connections =100; Query OK, 0 rows affected (0.00 SEC) ### Mysql > set interactive_timeout =31536000; mysql> set interactive_timeout =31536000; Query OK, 0 rows affected (0.00 SEC) ② mysql> set wait_timeout =31536000; Query OK, 0 rows affected (0.00 SECCopy the code

Adjust the maximum number of connections: there are security risks, we cannot confirm that the database can withstand the maximum connection pressure; Just like you can do 3 needs a day, suddenly the leader gives you 10 needs, let you when to test; Well, that’s not good enough, you know, to be on the verge of collapse, temper tantrums, quit.

Services involved: (1) Release the current link after the write service operation is complete. ② The system initializes and creates a connection pool. When a user accesses the database, an established free connection object is retrieved from the connection pool. When used, instead of closing the connection, the connection is put back into the pool for the next request.

6. Only_full_group_by in mysql5.7 causes an error in the original SQL statement

Error code:
1055 - Expression #4 of SELECT list is not in GROUP BY clause and contains 
nonaggregated column 'table.starttime' which is not functionally dependent on columns 
in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Copy the code
Error cause:

After mysql5.7, only_full_group_by is true; Group BY: group by: group BY: group BY: group by: group by: group by: group by

Core principles: ① All non-aggregate columns following select, HAVING, or order BY must exist in group BY; Select * from order by; select * from order by

Problem solution:
### First solution: Delete the only_full_group_by rule ### Second solution: Modify the service SQL statements to comply with the only_full_group_by rule ### Third mysql version is reduced to a version later than 5.7Copy the code

The first option: recommended; The reason is relatively low risk and simple operation, as follows:

Mysql > show variables like '%sql_mode%'; Mysql > SELECT @@sql_mode; Mysql > SELECT @@global. sql_mode; "-- the third instruction +---------------+------------------------------------------------------------------------------------------------------- ----------------+ | 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_ENGINE_SUBSTITUTION | +---------------+------------------------------------------------------------------------------------------------------- ----------------+ 1 row in set (0.01sec sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; Sql_mode = 'XXX' set @@session. sql_mode= 'XXX' #### You need to add a secondary option to the mysql configuration file and restart the system to take effect.Copy the code

The second option: if it is recommended for a new project, the change is minor; If the mysql version is upgraded on the project, basically the project SQL does not follow the only_full_group_by rule. The modification cost is high, and the test needs to be involved in a comprehensive test. The labor cost is here. And there are potentially undetectable problems

The third option: not recommended; The degraded version does not know that other problems will occur, the risk is large, the security degree is not high enough

7. Coding error

Error code:
SQL Error: 1366: Incorrect string valueXBB \ x86..."for column "user_name" at row 1 
Copy the code
Error cause:

Insert into the insert data, a certain field with emoticon (wechat login); The normal UTF-8 encoding may have 2, 3, and 4 bytes, so the emoji or some special characters are 4 bytes, but the mysql UTF8 encoding is up to 3 bytes, so the insert fails. Many beginners should encounter this problem

Problem solution:
Mysql > show variables like 'character_set_%'; +--------------------------+------------------------------------------------------+ | 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 | utf8 | | Character_sets_dir | / usr/local/Cellar/mysql / 8.0.19 / share/mysql/charsets / | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 8 rows in the set (0.00 SEC) # # parameters: ① Character_set_client -- used to set the character set used by the client ② character_set_connection -- Used to set the character set used to connect to the database (3) Character_set_database (3) Character_set_database (3) Character_set_database (3) Character_set_database Set the character_set_FILESYSTEM format to the character_set_FILESYSTEM encoding format and convert the file names on the operating system to the character_set_filesystem. The default binary is ⑤ character_set_results -- the encoding that the database returns to the client. If not specified, Use the default coding format of the server. ⑥ Character_set_server -- the default coding format specified during the server installation. It is recommended that the system manage this variable. This value is always utf8, you don't need to set it, [mysql] default-character-set=utf8mb4 [mysqld] [mysql] default-character-set=utf8mb4 Character_set_server = utf8MB4 ### ALTER database CHARACTER SET UTf8MB4; After the database character set is modified, restart the mysql database. ALTER TABLE TABLE name DEFAULT CHARACTER SET UTF8MB4 COLLATE UTF8MB4_general_CI; This applies only to new rows in the table, not to previous tablesCopy the code

8, mysql database import SQL file report 1153

Error code:
Error Code: 1153 - Got a packet bigger than 'max_allowed_packet' bytes
Copy the code
Error cause:

If you are using navicat or other mysql clients to import SQL data, you may be able to do so in small files, but if the file is tens of megabytes or hundreds of megabytes, you will find errors due to the default size of the SQL file that mysql reads

Problem solution:
Mysql > show VARIABLES like '%max_allowed_packet%'; +---------------------------+------------+ | Variable_name | Value | +---------------------------+------------+ | Max_allowed_packet | | 67108864 - refers to the mysql server and the client in the process of a transmit packets maximum allowable packet size of 64 m | mysqlx_max_allowed_packet | 67108864 | | Slave_max_allowed_packet | 1073741824 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- + 3 rows in the set (0.00 SEC) # # # 1) Mysql > set global max_allowed_packet = 16*1024*1024; Query OK, 0 rows affected (0.01sec) -- Mysql > show VARIABLES like '%max_allowed_packet%'; +---------------------------+------------+ | Variable_name | Value | +---------------------------+------------+ | Max_allowed_packet | | 16777216-16 m + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- + 3 rows in the set (0.00 SEC) # # # (2) Modify the configuration file to my CNF (can't find the configuration file: mysql -- help | grep. My CNF)/mysqld max_allowed_packet = 20 mCopy the code

9, create mysql database or table display 1044 no permission to create

Error code:
mysql> use test;
ERROR 1044 (42000): Access denied for user 'root1'@'localhost' to database 'test'
Copy the code
Error cause:

The current root1 account does not have permission to create databases or tables.

Problem solution:
Mysql > show grants for root1@localhost; +-------------------------------------------+ | Grants for root1@localhost | +-------------------------------------------+ | GRANT USAGE ON *.* TO `root1`@`localhost` | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.01 SEC) # # # visualize root1 access mysql > select * from mysql. The user where user='root1' and host='localhost' \G; *************************** 1. row *************************** Host: localhost User: root1 Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N .................... 1 row in set (0.00 SEC) ### mysql> grant all privileges on *.* to 'mprivileges '@'localhost' with grant option; Query OK, 0 rows affected (0.02sec) mysql> flush privileges; Query OK, 0 rows affected (0.01sec) mysql> show grants for root1@localhost; +----------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ ----------------------+ | Grants for root1@localhost | +----------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ ----------------------+ | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root1`@`localhost` WITH GRANT OPTION | | GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,EN CRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLIC ATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,S ET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root1`@`localhost` WITH  GRANT OPTION | +----------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set (0.00 SEC) at this time to create a database or table operations are okCopy the code

10. An error is reported when revoking user rights

Error code:
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
Copy the code
Error cause:

Because the local mysql version is later than 8.0, when a new user is created in the root account, the SYSTEM_USER permission is granted to the new user. However, the user does not have the SYSTEM_USER permission. Therefore, an error is reported when the user is revoked in the root account.

Problem solution:
Mysql > grant system_user on *.* to root; Mysql > revoke all privileges on *.* from oot1; mysql> revoke all privileges on *. Mysql > revoke all privileges on *.* from root1; mysql> revoke all privileges on *. Query OK, 0 rows affected (0.01sec)Copy the code

11. Failed to insert duplicate data due to unique build

Error code:
ERROR 1062 (23000): Duplicate entry 'amu-1' for key 'test.uniq_flag'
Copy the code
Error cause:

Failed to insert duplicate data because the unique key was set in the data table. Procedure The process is as follows:

Mysql > CREATE TABLE 'test' (-> 'id' int(10) NOT NULL AUTO_INCREMENT COMMENT 'primary key ID', -> 'uniq_flag' VARCHar (64) NOT NULL DEFAULT "COMMENT", -> PRIMARY KEY (' id '), -> UNIQUE KEY 'uniq_flag' (' uniq_flag ') USING BTREE ->) ENGINE=InnoDB DEFAULT CHARSET= UTf8MB4 COMMENT=' 数 据 库 '; Query OK, 0 rows affected, 1 warning (0.04 SEC) mysql> insert into test(' uniq_flag ') values('amu-1'); Query OK, 1 row affected (0.02sec) mysql> select * from test; + - + -- -- -- -- -- -- -- -- -- -- -- + | | id uniq_flag | + - + -- -- -- -- -- -- -- -- -- -- -- + | 1 | | amu - 1 + - + -- -- -- -- -- -- -- -- -- -- - + 1 row in the set (0.00 SEC) mysql >  insert into test(`uniq_flag`) values('amu-1'); ERROR 1062 (23000): Duplicate entry 'amu-1' for key 'test.uniq_flag'Copy the code
Problem solution:
If the return status code is 1062, logs can be logged and no fatal error is reported. (Recommended) Insert ignore into mysql> insert ignore into test(' uniq_flag ') values('amu-1'); Query OK, 0 rows affected, 1 warning (0.01sec) Make sure your SQL statement is ok, otherwise ignore all errors (not recommended) The select... where not exist insert into test(`uniq_flag`) SELECT 'amu-1' FROM dual WHERE NOT EXISTS (SELECT uniq_flag FROM test WHERE uniq_flag = 'amu-1'); Select to detect whether to insert; If it exists, do not insert. Otherwise, insert data. Replace into mysql> replace into test(' uniq_flag ') values('amu-2'); Query OK, 2 rows affected (0.00 SEC) Query OK, 2 rows affected (0.00 SEC) Query OK, 2 rows affected (0.00 SEC) Query OK, 2 rows affected (0.00 SEC)Copy the code

12. Unknown field name(found in code review)

Error code:
mysql> select ids  from test;
ERROR 1054 (42S22): Unknown column 'name' in 'field list'
Copy the code
Error cause:

This error occurs when the SQL statement is written with a field name that is not in the specified table.

  • (1) Possible development of table fields in the test environment to add or modify, not synchronized to the line; This happens when the code goes online
  • ② If multiple clusters exist, the table structure is not modified in a timely mannerSynchronize to the slave library; Error reading the slave library
  • ③ Most likely is not careful enough development coding, this kind of low-level mistakes can not be made; All SQL statements go throughexplineAnd test environment validation; Failed codereviewwork

conclusion

The above is just a summary of part of the mysql common error problem case analysis solutions; Also will continue to summarize mysql error cases to share out. I hope you can correct my shortcomings.

All right, I’m Amu, a worker who doesn’t want to be eliminated at 30 ⛽️ ⛽️ ⛽. Creation is not easy to feel “Amu” wrote a bit of material: 👍 to pay attention to, 💖 to share, we will see you next time.