MySQL 错误笔记

发表于 2017-06-21 更新于 2022-05-28 分类于 mysql 阅读次数： Waline：本文字数： 12k 阅读时长 ≈ 11 分钟

(1)　-bash: mysql: command not found

原因:这是由于系统默认会查找/usr/bin下的命令，如果这个命令不在这个目录下，当然会找不到命令，我们需要做的就是映射一个链接到/usr/bin目录下，相当于建立一个链接文件。
[root@localhost bin]# ln -s /usr/local/mysql/bin/mysql /usr/bin

(2)　cannot connect to local MySQL Server through socket ‘/var/lib/mysql/mysql.sock’

配置 /etc/my.cnf
sock = /var/lib/mysql/mysql.sock

(3)　can’t connect to local mysql server through socket ‘/tmp/mysql.sock’

客户端连接时会默认去找/tmp路径下的mysql.sock 所以我们这里的第二个方案是看能不呢把 mysql.sock复制到 /tmp路径下

ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

error

(3)　MySQL server PID file could not be found

配置 /etc/my.cnf
sock = /var/lib/mysql/mysql.sock

(4)　Starting MySQL. ERROR! The server quit without updating PID file (/data00/usr/local/mysql/data/host-172-16-11-125.pid).

data目录最好放到 /usr/local/mysql/data
data目录所属用户不是mysql

首先看文件对应的用户

-rw-rw---- 1 root  root         4 Dec  5 18:10 mysqld_safe.pid

[root@host-172-16-11-125 data]# chown -R mysql /data00/usr/local/mysql/data/mysqld_safe.pid
[root@host-172-16-11-125 data]# chgrp -R mysql /data00/usr/local/mysql/data/mysqld_safe.pid

可能是刚修改my.ini，哪里配置的有问题

(5)　ln: failed to create symbolic link ‘/usr/bin/mysql’: File exists

[root@localhost bin]# ln -s /usr/local/mysql/bin/mysql  /usr/bin
ln: failed to create symbolic link ‘/usr/bin/mysql’: File exists
[root@localhost bin]# rm /usr/bin/mysql
rm: remove symbolic link ‘/usr/bin/mysql’? yes
[root@localhost bin]# pwd
/usr/local/mysql/bin
[root@localhost bin]# ln -s /usr/local/mysql/bin/mysql  /usr/bin

(6)　mysql启动不了

[root@localhost /]# /etc/init.d/mysqld restart
MySQL server PID file could not be found!                  [FAILED]
Starting MySQL...The server quit without updating PID file [FAILED]cal/mysql/data/localhost.localdomain.pid).
[root@localhost /]# service mysql start
Redirecting to /bin/systemctl start  mysql.service
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.
[root@localhost /]# service mysql start
Redirecting to /bin/systemctl start  mysql.service
[root@localhost /]# systemctl start mariadb.service
Failed to start mariadb.service: Unit mariadb.service failed to load: No such file or directory.
[root@localhost /]# systemctl start mysql

(7)　发生系统错误 1067

net start mysql
MySQL 服务正在启动 ...
MySQL 服务无法启动。

系统出错。

发生系统错误 1067。

进程意外终止。

一定是配置文件里的某些配置不对(注意MySQL各个版本间的配置可能不一样)

(8)　your password has expired. to log in you must change it using a client that supports expired passwords

在mysql bin目录下用./mysql -u root -p 登录
账户密码过期，修改密码

(9)　忘记MySQL密码

Linux下如果忘记MySQL的root密码，可以通过修改配置的方法，重置root密码
修改MySQL的配置文件（默认为/etc/my.cnf）,在[mysqld]下添加一行skip-grant-tables
保存配置文件后，重启MySQL服务 service mysqld restart
再次进入MySQL命令行 mysql -uroot -p,输入密码时直接回车，就会进入MySQL数据库了，这个时候按照常规流程修改root密码即可。

update user set password=password('newpassword') where user='root';  #MySQL-5.6修改密码命令
update user set authentication_string=password('root') where user='root' ;  #MySQL-5.7修改密码命令
flush privileges;


mysql> SET PASSWORD = PASSWORD('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

(10)　ERROR 1820 (HY000): You must reset your password

ERROR 1820 (HY000): You must reset your password using ALTER USER statement befo re executing this statement.

解决办法：

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
# 第一次登录会提示你修改密码，修改密码就可以了，修改完重新登录。
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.  
mysql> SET PASSWORD = PASSWORD('abc2017qwer');  # 修改密码
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> alter user 'root'@'localhost' password expire never;  # 设置密码永不过期
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;  # 把表里的修改同步到内存
Query OK, 0 rows affected (0.00 sec)

mysql> exit;
Bye

(11)　ERROR 1045 (28000): Access denied for user ‘root‘@’localhost’ (using password: YES)

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

可能是密码输错了
root用户没有localhost登录的权限，在mysql数据库的user表里插入或者更新root用户的权限

(12)　Establishing SSL connection without server’s identity verification is not recommended.

Wed Jun 14 10:47:21 CST 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

连接MySQL的时候url参数里加上&useSSL=false

url=jdbc:mysql://localhost:3306/text?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false

(13)　1215 cannot add foreign key constraint

可能原因：
（1）外键对应的字段数据类型不一致

（2）设置外键时“删除时”设置为“SET NULL”

（3）两张表的存储引擎不一致

解决办法：

show table status from db_name where name='table_name';
alter table table_name engine=innodb;

(14)　ERROR 2003 (HY000): Can’t connect to MySQL server on ‘172.16.1.23’ (113)

113是防火墙的原因
修改防火墙，允许3306端口访问，或者关闭防火墙

/sbin/iptables -I INPUT -p tcp --dport 3306 -j ACCEPT  


service iptables stop  #关闭防火墙

systemctl stop firewalld   #关闭防火墙

(15)　java.io.IOException: java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x8C\xA3’ for column ‘page_title’ at row 892

UTF-8编码有可能是两个、三个、四个字节。Emoji表情是4个字节，而Mysql的utf8编码最多3个字节，所以数据插不进去。
解决方案就是：将Mysql的编码从utf8转换成utf8mb4。

1. 修改my.cnf(windows为my.ini)

[client] 
default-character-set = utf8mb4 

[mysql]
default-character-set=utf8mb4

[mysqld]
character-set-server = utf8mb4 
#collation-server = utf8mb4_unicode_ci 
#character-set-client-handshake = FALSE 
#init_connect='SET NAMES utf8mb4'

2. 将已经建好的表也转换成utf8mb4

alter database DATABASE_NAME character set utf8mb4 collate utf8mb4_general_ci;  # 更改数据库编码 utf8mb4_unicode_ci
alter table TABLE_NAME convert to character set utf8mb4 collate utf8mb4_bin;  # 更改表的编码 utf8mb4_unicode_ci
alter table TABLE modify COL varchar(50) CHARACTER SET utf8mb4;  # 更改字段的编码

修改后重启Mysql

第三步可以省略
3. 以root身份登录Mysql，修改环境变量，将character_set_client,character_set_connection,character_set_database,character_set_results,character_set_server 都修改成utf8mb4

(16)　1071 - Specified key was too long; max key length is 767 bytes

[SQL]  alter table page convert to character set utf8mb4 collate utf8mb4_bin;
[Err] 1071 - Specified key was too long; max key length is 767 bytes

数据库中的某两个字段设置unique索引的时候，出现了Specified key was too long; max key length is 767 bytes错误
是Mysql的字段设置的太长了，于是我把这两个字段的长度改了一下就好了

(17)　java.io.IOException: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry ‘15649431’ for key ‘PRIMARY’

Exception in thread "main" java.io.IOException: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '15649431' for key 'PRIMARY'
Caused by: org.xml.sax.SAXException: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '15649431' for key 'PRIMARY'

主键重复了

(18)　Cannot truncate a table referenced in a foreign key constraint

[SQL] truncate table `program`;
[Err] 1701 - Cannot truncate a table referenced in a foreign key constraint (`m`.`task`, CONSTRAINT `FK_7kxuyirn7pko5dshnh5969xay` FOREIGN KEY (`pid`) REFERENCES `m`.`program` (`pid`))

sql : INSERT INTO page (page_id,page_namespace,page_title,page_restrictions,page_counter,page_is_redirect,page_is_new,page_random,page_touched,page_latest,page_len) VALUES (133506,0,'陳任','',0,0,0,RAND(),DATE_ADD('1970-01-01', INTERVAL UNIX_TIMESTAMP() SECOND)+0,38317407,3004) ...
java.io.IOException: java.sql.SQLException: Incorrect string value: '\xA2\xE3' for column 'page_title' at row 642

(19)　mysql error: Table “mysql”.“innodb_table_stats” not found

CREATE TABLE `innodb_index_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `stat_value` bigint(20) unsigned NOT NULL,
  `sample_size` bigint(20) unsigned DEFAULT NULL,
  `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

CREATE TABLE `innodb_table_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `n_rows` bigint(20) unsigned NOT NULL,
  `clustered_index_size` bigint(20) unsigned NOT NULL,
  `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

(20)　Data truncated for column

由数据类型的长度不一致导致的。
alter table user modify column sex enum(‘0’,’1’);

(21)　ERROR 1290 (HY000): –secure-file-priv option so it cannot execute this statement

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

On Ubuntu 14 and Mysql 5.5.53 this setting seems to be enabled by default. To disable it you need to add secure-file-priv = “” to your my.cnf file under the mysqld config group. eg:-

[mysqld]
secure-file-priv = ""

Stop the MySQL server service by going into services.msc
Go to C:\ProgramData\MySQL\MySQL Server 5.6 (ProgramData was a hidden folder in my case).
Open the my.ini file in Notepad.
Search for 'secure-file-priv'.
Comment the line out by adding '#' at the start of the line.
Save the file.
Start the MySQL server service by going into services.msc

(22)　Can’t create/write to file ‘/tmp/ML6MLk2I’ (Errcode: 28 - No space left on device)

java.sql.BatchUpdateException: Can't create/write to file '/tmp/ML6MLk2I' (Errcode: 28 - No space left on device)

(23)　mysql如何查看自己数据库文件所在的位置 2016.11.12

打开mysqml文件夹，显示的文件如下，然后找到my.ini文件,打开my.ini文件后，按ctrl+f搜索”datadir”就可以找到你数据库的物理路径。
如果你修改数据库文件的存储位置，可以直接在这里修改。如果想对数据库进行搬迁，只需要复制”data“这个文件夹，覆盖新环境下的”data“文件就可以了。

(24)　Got a packet bigger than ‘max_allowed_packet’ bytes

导入文件时提示文件太大
[Err] 1153 - Got a packet bigger than ‘max_allowed_packet’ bytes
解决方法：
在MySQL安装目录下找到文件my.ini，搜索[mysqld]，在其下面添加一句话
max_allowed_packet=1024M （大小根据自己的情况定）
重启MySQL服务
（windows下计算机右键管理服务和应用程序服务找到mysql服务，重启）
或者以管理员身份运行cmd net mysql stop net mysql start

(25)　MySQL ibdata1文件太大如何缩小

2016.3.1

MySql innodb如果是共享表空间，ibdata1文件越来越大，达到了30多个G，对一些没用的表进行清空：
truncate table xxx;
然后optimize table xxx; 没有效果
因为对共享表空间不起作用。
mysql ibdata1存放数据，索引等，是MYSQL的最主要的数据。

如果不把数据分开存放的话，这个文件的大小很容易就上了G，甚至几十G。对于某些应用来说，并不是太合适。因此要把此文件缩小。
无法自动收缩，必须数据导出，删除ibdata1，然后数据导入，比较麻烦，因此需要改为每个表单独的文件。

解决方法：数据文件单独存放(共享表空间如何改为每个表独立的表空间文件)。
步骤如下：

1）备份数据库
从命令行进入MySQL Server 5.0\bin
备份全部数据库，执行命令
D:\>mysqldump -q -u mysql -ppassword --add-drop-table --all-databases > c:/all.sql
做完此步后，停止数据库服务。

2）找到my.ini或my.cnf文件
linux下执行 ./mysqld --verbose --help | grep -A 1 'Default options'
会有类似显示：
Default options are read from the following files in the given order:
/etc/my.cnf ~/.my.cnf /usr/local/service/mysql3306/etc/my.cnf

windows环境下可以：
mysqld --verbose --help > mysqlhelp.txt
notepad mysqlhelp.txt
在里面查找Default options，可以看到查找my.ini的顺序，以找到真实目录

3）修改mysql配置文件
打开my.ini或my.cnf文件
[mysqld]下增加下面配置
innodb_file_per_table=1

验证配置是否生效，可以重启mysql后,执行
show variables like '%per_table%'
看看innodb_file_per_table变量是否为ON

4）删除原数据文件
删除原来的ibdata1文件及日志文件ib_logfile*，删除data目录下的应用数据库文件夹(mysql文件夹不要删)

5）还原数据库
启动数据库服务

从命令行进入MySQL Server 5.0\bin
还原全部数据库，执行命令mysql -uusername -pyourpassword < c:/all.sql

经过以上几步后，可以看到新的ibdata1文件就只有几十M了，数据及索引都变成了针对单个表的小ibd文件了，它们在相应数据库的文件夹下面。

(26)　同一局域网内一台电脑(windows)访问另一条电脑(windows)上的mysql

被连接的那台电脑

打开3306端口
允许远程用户访问(把mysql数据库user表里的host改成%)
关闭防火墙
备注：和telnet没关系

(27)　can not connet mysql 10060

mysqld_safe The file /usr/local/mysql/bin/mysqld
does not exist or is not executable. Please cd to the mysql installation
directory and restart this script from there as follows:
./bin/mysqld_safe&
See http://dev.mysql.com/doc/mysql/en/mysqld-safe.html for more information

mysqld_safe只认识/usr/local/mysql/bin/mysqld路径的mysqld

于是调整在当前目录创建了一个软链接，问题解决。

[root@edu local]# ln -s mysql5.7/ mysql

(28) ERROR 2013 (HY000): Lost connection to MySQL server at ‘reading initial communication packet’, system error: 110

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 110

mysql设置文件中“bind-address”值的问题；
访问权限限制问题；
防火墙、杀毒软件阻拦的问题（特别是Windows）；
负载过大、最大连接限制了访问（特别正式提供服务的mysql）
网络问题

Lost connection to MySQL server at ‘reading initial communication packet’, system error: 0

References

[1] MySQL-5.7 官方文档
[2] MySQL-5.6 创建用户
[3] 安装完绿色版MySQL修改密码：ERROR 1820 (HY000): You must reset your password using ALTER USER statement befo re executing this statement.
[4] alter user
[5] 让Mysql支持Emoji表情
[6] MySQL乱码问题以及utf8mb4字符集
[7] 清官谈mysql中utf8和utf8mb4区别
[8] mysql-error-table-mysql-innodb-table-stats-not-found
[9] Incorrect string value: ‘\xF0\x9F…’ for column ‘XXX’ at row
[10] charset-unicode-conversion
[11] mysql-utf8mb4
[12] MySQL server PID file could not be found!
[13] Mysql JDBC 连接串参数说明
[14] connector-j-reference-configuration-properties
[15] mysql导出导入文件问题整理
[16] how-should-i-tackle-secure-file-priv-in-mysql
[17] Mysql导出逗号分隔的csv文件
[18] mysql导出数据 null值被处理成\N
[19] mysql-writing-file-error-errcode-28
[20] mysql-cant-create-write-to-file-tmp-sql-3c6-0-myi-errcode-2-what-does
[21] Mysql删除数据后，磁盘空间未释放的解决办法

(1) -bash: mysql: command not found

(2) cannot connect to local MySQL Server through socket ‘/var/lib/mysql/mysql.sock’

(3) can’t connect to local mysql server through socket ‘/tmp/mysql.sock’

(3) MySQL server PID file could not be found

(4) Starting MySQL. ERROR! The server quit without updating PID file (/data00/usr/local/mysql/data/host-172-16-11-125.pid).

(5) ln: failed to create symbolic link ‘/usr/bin/mysql’: File exists

(6) mysql启动不了

(7) 发生系统错误 1067

(8) your password has expired. to log in you must change it using a client that supports expired passwords

(9) 忘记MySQL密码

(10) ERROR 1820 (HY000): You must reset your password

(11) ERROR 1045 (28000): Access denied for user ‘root‘@’localhost’ (using password: YES)

(12) Establishing SSL connection without server’s identity verification is not recommended.

(13) 1215 cannot add foreign key constraint

(14) ERROR 2003 (HY000): Can’t connect to MySQL server on ‘172.16.1.23’ (113)

(15) java.io.IOException: java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x8C\xA3’ for column ‘page_title’ at row 892

(16) 1071 - Specified key was too long; max key length is 767 bytes

(17) java.io.IOException: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry ‘15649431’ for key ‘PRIMARY’

(18) Cannot truncate a table referenced in a foreign key constraint

(19) mysql error: Table “mysql”.“innodb_table_stats” not found

(20) Data truncated for column

(21) ERROR 1290 (HY000): –secure-file-priv option so it cannot execute this statement

(22) Can’t create/write to file ‘/tmp/ML6MLk2I’ (Errcode: 28 - No space left on device)

(23) mysql如何查看自己数据库文件所在的位置 2016.11.12

(24) Got a packet bigger than ‘max_allowed_packet’ bytes

(25) MySQL ibdata1文件太大如何缩小

(26) 同一局域网内一台电脑(windows)访问另一条电脑(windows)上的mysql

(27) can not connet mysql 10060