第二十一课mysql常见错误-converted

英MySQL常见错ERROR1040(HY000):Toomany该错误发生在有max_connections个客户连接了mysqld服务器，应该重启mysqld，用更大max_connections变量 通常不把这个权限给一般用户，有这个权限的管理员可以登录并且使用SHOWPROCESSLIST找出什么可比 f中配置 root@ip-172-31-18-152:/usr/local/mysql/bin#./mysql-uroot-root@ip-172-31-18-152:/usr/local/mysql/binmysqlucdqpEnterpassword:root@ip-172-31-18-152:/usr/local/mysql/binmysql-uroot-pEnterpassword:ERROR1040(HY000):Toomany 英MySQL常见错Packettoolargemysql根据max_allowed_packet参数来限制server接受的数据包大小当一个MySQL客户或mysqld服务器得到一个比max_allowed_packet个字节长的包，它发出一个Packettoolarge错可以使用 令行选项设置max_allowed_packet为一个更大的尺寸。例如，如果将一个全长的BLOB存入张表中，需要用max_allowed_packet=24M选项来启动mysqlmysql>setglobalmysql ##重新连接生mysql>insertintostudentsERROR1301(HY000):Resultofrepeat()waslargerthanmax_allowed_packet(1024)– mysql>createtabletemp3(namelongtextcharsetmysql>insertintotemp3ERROR1301(HY000):Resultofrepeat()waslargerthanmax_allowed_packet )-mysql>insertintotemp3ERROR1301(HY000):Resultofrepeat()waslargerthanmax_allowed_packet )-mysql>insertintotemp3 英MySQL常见错 用户的口令在 f中添加skip-grant-tables=1选项重启---P3307mysql>mysql>all*.*‘mysql2’;将skip-grant-tables=1 英MySQL常见错1062Duplicateentry'xxx'forUNIQUEKEY`idx_sname`(`sname`),KEY`dept_id`(`dept_id`),mysql>select*from |sid|sname|gender|dept_id 1| 1 1 1rowinset(0.00mysql>insertintostudentsERROR1062(23000):Duplicateentry'a'forkey 英MySQL常见错1060Duplicatecolumnnamemysql>altertablestudentsaddsnameERROR1060(42S21):Duplicatecolumnname1050Table'xxx'already tableifnotexists避免错误，或者用droptableifexists再执行createtable命令mysql>createtablestudents(idint);ERROR1050(42S01):Table'students'alreadyexistsmysql>createtableifnotexistsstudents(idint);QueryOK,0rowsaffected,1warning(0.00sec) 英MySQLroot@ip-172-31-18-152:/usr/local/mysql/bin#./mysql-uroot-pmysql>alterusercdq@localhostaccountlock;Enterpassword:ERROR3118(HY000):Accessdeniedforuser'cdq'@'localhost'.Accountismysql>selecthost,user,account_lockedfrommysql.userwhere | |user|account_locked |localhost|cdq| mysql>alterusercdq@localhostaccount 01英MySQL1044：当前用户没 数据库的权1045：不能连接数据库，用户名 错1141：当前用户无 数据1142：当前用户无 数据1143：当前用户无 数据表中的字mysql>grantselectoncourse.studentstocdq@'localhost'identifiedby'mysql';QueryOK,0rowsaffected,1warning(0.00sec)mysql>grantselect(id)oncourse.deptto[root@master~]#mysql-ucdq-p-DtestEnterpassword:ERROR1044(42000):Accessdeniedforuser‘cdq’@‘localhost’todatabasemysql>select*fromERROR1142(42000):SELECTcommanddeniedtouser'cdq'@'localhost'fortablemysql>selectdept_namefromERROR1143(42000):SELECTcommanddeniedtouser'cdq'@'localhost'for'dept_name'intable 01英MySQL1205Lockwaittimeout锁等待超mysql>showvariableslike | |Value |innodb_lock_wait_timeout| Sessionmysql> mysql>updatedeptsetSessionmysqlupdatedeptsetdept_name=‘bcd’;##等待50秒超ERROR1205(HY000):Lockwaittimeoutexceeded;tryrestarting 01英MySQL常见错2002Can'tconnecttolocalMySQLserverthroughsocketMySQLsocketroot@ip-172-31-18-152:/usr/local/mysql/bin#./mysql-uroot-pEnterpassword:ERROR2002(HY000):Can'tconnecttolocalMySQLserverthroughsocket'/tmp/mysql.sock'[root@mastertmp]#mvmysql.sock[root@mastertmp]#mysql-uroot-pEnterpassword:ERROR2002(HY000):Can'tconnecttolocalMySQLserverthroughsocket'/tmp/mysql.sock'[root@mastertmp]#mysql-uroot-p-h-P3308 英MySQL常见错Mysql有两种连接方式对mysql.sock来说，其作用是程序与mysqlserver处于同一台机器，发起本地连接时可用。例如你无须定义连接host的具体IP地址，只要为空或localhost就可以。在此种情况下，即使你改变mysql的外部port也是一样可能正常连接。因为你 f中改变端口后，mysql.sock是随每一次mysqlserver启动生成的。已经根据你在更 下面生成mysql.sock到时候指 =[root@mastertmp]#/etc/init.d/mysql.serverrestartShuttingdownMySQL..SUCCESS!StartingMySQL.[root@mastertmp]#falcon-plus ssh-1ZfXakGXqissh-jIj2QS6wEqssh-pRPRqdAtAussh-WbVxDrJldM mysql.sock.lockssh-7i3HcxV8SIssh-jxyBL3QosTssh-[root@mastertmp]#mysql-uroot-pEnterpassword: 英MySQL常见错启动错误[root@mastermysql]#/etc/init.d/mysql.serverstartStartingMySQL.ERROR!TheserverquitwithoutupdatingPIDfile(/usr/local/mysql/data/master.pid).查看日志文件：log-2017-07-18T14:06:21.937497Z0[Note]/usr/local/mysql/bin/mysqld(mysqld5.7.17-log)asprocess3924mysqld:File'./mysql-bin.index'notfound(Errcode:13-denied)2017-07-18T14:06:21.940638Z0[ERROR]是文件权限问drwxr-xr-x.14 40967 1822:06[root@mastermysql]#chownmysql:mysqldata-R[root@mastermysql]#/etc/init.d/mysql.serverstartStartingMySQL. 英MySQL常见错例如执行：mysqldump提示：-bash：commandnotfound是临时添加：export export保存退出后执行：source.bash_profile 英MySQL常见错1452：外键约束检查失败，更新子表记录失1451：外键约束检查失败，删除mysql>updatedeptsetid=5whereid=4;ERROR1451(23000):Cannotdeleteorupdateaparentrow:aforeignkeyconstraintfailsmysql>deletefromdeptwhereid=3;ERROR1451(23000):Cannotdeleteorupdateaparentrow:aforeignkeyconstraintfailsmysql>updatestudentssetdept_id=6wheresid=1;ERROR1452(23000):Cannotaddorupdateachildrow:aforeignkeyconstraintfails 英MySQL1052SQLSTATE:23000mysql>select*fromstudentsainnerjoinscoreb+-----+-------+--------+---------+------+---------- i|geder1ept_d |course_id| 1| 2|88|----a+----1--+-|------+---------+------+----------|----a-- 1 1|1 4|88+-----+-------+-|------+---------+------+----------- 4rowsinset(0.01mysql>selectsid,sname,scorefromstudentsainnerjoinscorebERROR1052(23000):Column'sid'infieldlistis 01英MySQL`gender`smallint(6)DEFAULTPRIMARYKEY(`sid`),KEY`dept_id`KEY`idx_3`CONSTRAINT`students_ibfk_1`FOREIGNKEY(`dept_id`)REFERENCES`dept`mysql>altertablestudentsaddprimaryERROR1068(42000):Multipleprimarykey 01英MySQL常见错SQL模式MySQL服务器可以以不同的SQL模式来操作，并且可以为不同客户端应用不同模式。这样每个应用程序以根据自己的需求来 务器的操作模式模式定义MySQLSQL使用MySQLMySQL。可以 f中配置sql_mode=“modes”选项启动mysqld来设置默认SQL模式。也可以在启动后SET[SESSION|GLOBAL]sql_mode='modes‘用逗号间隔开的一系列不同的模式。可以用SELECT@@sql_mode语句查询当前的模mysql>showvariableslike+--------------- |Variable_name| +--------------- | |STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION+--------------- 1rowinset(0.00mysql>select | |STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION“严格模式”，表示至少STRICT_TRANS_TABLES或STRICT_ALL_TABLES被启用的模 英MySQL常见错主要的SQL_Mode值包括ANSI更改语法和行为，使其更符合标准SQLTRADITIONAL使MySQL的行为象“传统”SQL数据库系统。该模式的简单描述是当在列中插入不正确出错不是警告”，等同STRICT_TRANS_TABLES、STRICT_ALL_TABLES、NO_ZERO_IN_DATENO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER在严格模式，在INSERT或UPDATE过程中，如果被零除(或MOD(X，0))，则产生错误(否则为警告)。如未给出该模式，被零除时MySQL返回如果需要 引擎被禁用或未编译，可以防止自动替 引不要让GROUPBY部分中的查询指向未选择的 英MySQL常见错为事 引擎启用严格模式，也可能为非事 引擎启用严格模式严格模式控制MySQL如何处 或丢失的输入值。有几种原因可以使一个值 ，不适合列，或超出范围。当新插入的行不包含某列的没有显示定义DEFAULT子句的值，则该值被丢失。对 `sid`int(11)NOTPRIMARYKEY(`sid`),) 英MySQL常见错mysql>select | |STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION 1rowinset(0.00mysql>selectsid,sname,count(*)fromstudentsgroupby+-----+------- |sid|sname|count(*)|----a+-----3- ||||ab||||3|c1mysqlsessi|nQuryOK,0ro1saffected(0.00myq>dselectsid,sname,count(*)fromstudentsgroupbyERROR1055(42000):Expression#1ofSELECTlistisnotinGROUPBYclauseandcontainsnoaggregatedcolumn'course.students.sid'whichisnotfunctionallydependentoncolumnsinGROUPBYclause;thisis patiblewithsql_mode=only_full_group_by 英MySQL常见错mysql>select | |STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTIONmysql>insertintostudentsERROR1366(HY000):Incorrectintegervalue:'a'forcolumn'gender'atrow1mysql>setsessionQueryOK,0rowsaffected(0.00mysql>insertintostudentsQueryOK,1rowaffected,1warningmysql>select*fromstudentswhere +-----+-------+-------- |sid|sname|gender|dept_id +-----+-------+-------- |6|ab 0 1

英MySQL常见错mysql>select | mysql>insertintostudentsQueryOK,1rowaffected(0.00mysql>select*fromstudentswheresid=7; +-----+-------+-------- |sid|sname|gender|dept_id +-----+-------+-------- |7|a|NULL 1mysql>setQueryOK,0rowsaffected,1warning(0.00mysql>select | |STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION 1rowinset(0.00mysql>insertintostudentsERROR1365(22012):Divisionby 英MySQL常见错mysql>select | |STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTIONmysql>insertintostudentsERROR1406(22001):Datatoolongforcolumn'sname'atrowmysql>setsessionQueryOK,0rowsaffected(0.00mysql>insertintostudentsQueryOK,1rowaffected,1warningmysql>select*fromstudentswhere +-----+-------+-------- |sid|sname|gender|dept_id +-----+-------+-------- |6|abcde|1 1

英MySQL常见错mysql>setsessionmysql>select +--------------------------------------------------------------------------- mysql>insertintostudentsERROR1366(HY000):Incorrectintegervalue:'a'forcolumn'gender'atrowmysql>insertintostudentsERROR1365(22012):Divisionby 英MySQL1242SQLSTATE:21000消息：子查询返回1行以上mysql>select*fromdeptwhereid=(selectdept_idfromERROR1242(21000):Subqueryreturnsmorethan1mysql>select*fromdeptwhereidin(selectdept_idfrom |id|dept_name |1| 1213SQLSTATE:40001消息：试图获取锁定session updatestudentssetsname='b'where

session updatestudentssetsname='cd'wherelock;tryrestarting

updatestudentssetsname='bc'whereupdatestudentssetsname='ac'whereERROR1213(40001):Deadlockfoundwhentryingto 01英MySQL常见错 updatetestset`cdq`.*TOidentifiedby‘cdq4’;Flushprivileges;

解决办法alterprocedureproc1securityCREATEPROCEDURESQLSECURITYINVOKERupdatetestname='abc'; PS:sqlsecuirty的值决定了调用存储过程的方式，取值：definer（默认）或者invoker ，若没有则报错invoker:在执 英MySQL常见错mysql>grantallprivilegeson*.*tocdq2@'localhost'identifieddelimiter//CREATEPROCEDUREDeclarenintdefaultwhilen<=10Insertintostudentsvalues(n,concat('zhangSetn=n+1;End//delimiter

delimiter//createafter

onstudentsforeachinsertintostudents_noindex//delimiter 英MySQL常见错[root@master~]#mysqldump-uroot-p-P3307-h--no-data--no-info-routine--trigger导入到另外的数据库实例slave1[root@slave1~]#mysql-uroot-mysql>usemysql>sourcemysql>callERROR1449(HY000):Theuserspecifiedasadefiner('cdq2'@'localhost')doesmysql>alterprocedureproc_studentssqlsecuritymysql>callERROR1449(HY000):Theuserspecifiedasadefiner('cdq2'@'localhost')doesnotDroptriggerdelimiter//createtriggerafteronstudentsforeachinsertintostudents_noindex//delimitermysql>call QueryOK,1rowaffected(0.02

MySQL常见错Error:1553SQLSTATE:HY000(ERDROPINDEXCREATETABLE`students``sid`int(11)NOT`sname`varchar(2000)DEFAULT`gender`int(11)DEFAULTPRIMARYKEY(`sid`),KEY`idx_sname`KEY`idx_gender`(`gender`),KEY`dept_id`(`dept_id`),CONSTRAINT`students_ibfk_1`FOREIGNKEY(`dept_id`)REFERENCES`dept`)mysql>dropindexdept_idonERROR1553(HY000):Cannotdropindex'dept_id':neededinaforeignkeymysql>altertablestudentsdropforeignkeystudents_ibfk_1;mysql>dropindexdept_idonstudents;QueryOK,0rowsaffected(0.01Records:0Duplicates:0Warnings: 英MySQL常见错ERRORmysql>createtabletemp1(idintauto_increment,nameERROR1075(42000):Incorrecttabledefinition;therecanbeonlyoneautocolumnanditmustbedefinedasakeymysql>createtabletemp1(idintauto_incrementprimarykey,nameQueryOK,0rowsaffected(0.01 英MySQL常见错ERRORmysql>createviewview_1asselect*temp1;mysql>showcreateview+--------+------------------------------------------------------------------| Create| |collation_connection+--------+------------------------------------------------------------------|view_1|CREATEALGORITHM=UNDEFINEDmysql>altertabletemp1dropcolumnmysql>select*fromERROR1356(HY000):View'course.view_1'referencesinvalidtable(s)orcolumn(s)orfunction(s)ordefiner/invokerofviewlackrightstousethem

英MySQL常见错MySQL提供了对每个用户的资源限制管理MAX_QUERIES_PER_HOUR：一个用户在一个小时内可以执行查询的次数（）MAX_UPDATES_PER_HOUR：一个用户在一个小时内可以执行修改的次数（仅包含修改数据库或表的语句）MAX_CONNECTIONS_PER_HOUR：一个用户在一个小时内可以连接MySQL的时间MAX_USER_CONNECTIONS：一个用户可以在同一时间连接MySQL实例的数量 英MySQL常见错通过执行createuser/alteruser设置/修改用户的资源限制mysql>CREATEUSER‘cdq3'@'localhost'IDENTIFIEDBY‘mysql' WITHMAX_QUERIES_PER_HOUR20MAX_UPDATES_PER_HOUR10MAX_CONNECTIONS_PER_HOURMAX_USER_CONNECTIONSmysql>ALTERUSER‘cdq3'@'localhost'WITHMAX_QUERIES_PER_HOUR100;取mysql>ALTERUSER‘cdq3'@'localhost'WITHMAX_CONNECTIONS_PER_HOUR 英MySQL常见错CREATEUSER'cdq3'@'localhost'IDENTIFIEDBYWITHMAX_QUERIES_PER_HOUR20MAX_UPDATES_PER_HOUR10MAX_CONNECTIONS_PER_HOURMAX_USER_CONNECTIONS[root@master~]#mysql-ucdq3-pEnterpassword:ERROR1226(42000):User'cdq3'hasexceededthe'max_user_connections'resource(currentvalue:2)[root@master~]#mysql-ucdq3-pEnterpassword:ERROR1226(42000):User'cdq3'hasexceededthe'max_connections_per_hour'(currentvalue: 英MySQL常见错数据误操作之后的恢复，通常是对某个表数据误删除或者误修改，取决于备份 来恢通过mysqldump恢通过replication中的延 恢通过xtrabackup的全量备份恢Mysqldump恢通常mysqldump的备份是全数据库的备份，需要比如之前的全量备份如下：mysqldump-mysql>deletefrom

root-p执行如下命令将students表单独提取出来形成一然后执行source命令导入数据：[root@master~]#mysql-root-pEnteretotheMySQLmonitor.Commandsendwith;or\g.mysql>usecourse;mysql>source 英MySQL常见错查看students表里的数据mysql>select*from+-----+-------+--------+-- |sid|sname|gender||1 1 1+----b+----1-|+-2-|----+-------c+ 2 1|4|d 2 2|5|a|NULL |7NUL 1|21|abab 1 1|100|ab 1|11|-----+-------+--------+-- 10rowsinset(0.00 英MySQL常见错表，所以需要从binlog中把全量备份之后的修改和插入操作提取出来再重新执行一遍比如之前的全量备份如下：mysqldump-uroot-p studentsvalues(101,'ab',1,1);mysql>deletefrom

insertintoawk'/^--Tablestructurefortable`students`/,/^--Tablestructurefortable`teacher`/{print}'course.sql>删除students.sql中drop/createtable语句部分，仅保留insert分然后执行source命令导入数据：[root@master~]#mysql-urootpEnterpassword:etotheMySQLmonitor.Commandsendwith;ormysql>usemysql>sourcemysqlbinlog-vmysql-bin.000016>a.logINSERTINTO######@1=100###@2='ab'###@3=1###@4=1###SET###@1=101######@3=1###@4=1 英MySQL常见错mysql>INSERTINTOaffected(0.01mysql>mysql>INSERTINTO`course`.`students`values(101,'ab',1,1);QueryOK,1rowaffected(0.01mysql>select*from+-----+-------+-------- i|nam1||gendera----b+----1-|+--|---- |3|c 2 1|4|d 2 2|5|a|NULL | 7 NUL 1|21|abab 1|100|ab 1 1|10