mysql参考手册|目录

附录A:问题和常见错误

目录

A.1. 如何确定导致问题的原因
A.2. 使用MySQL程序时的常见错误
A.2.1. 拒绝访问
A.2.2. 无法连接到[local] MySQL服务器
A.2.3. 客户端不支持鉴定协议
A.2.4. 输入密码时出现密码错误
A.2.5. 主机的host_name被屏蔽
A.2.6. 连接数过多
A.2.7. 内存溢出
A.2.8. MySQL服务器不可用
A.2.9. 信息包过大
A.2.10. 通信错误和失效连接
A.2.11. 表已满
A.2.12. 无法创建文件/写入文件
A.2.13. 命令不同步
A.2.14. 忽略用户
A.2.15. 表tbl_name不存在
A.2.16. 无法初始化字符集
A.2.17. 文件未找到
A.3. 与安装有关的事宜
A.3.1. 与MySQL客户端库的链接问题
A.3.2. 如何以普通用户身份运行MySQL
A.3.3. 与文件许可有关的问题
A.4. 与管理有关的事宜
A.4.1. 如何复位根用户密码
A.4.2. 如果MySQL依然崩溃,应作些什么
A.4.3. MySQL处理磁盘满的方式
A.4.4. MySQL将临时文件储存在哪里
A.4.5. 如何保护或更改MySQL套接字文件/tmp/mysql.sock
A.4.6. 时区问题
A.5. 与查询有关的事宜
A.5.1. 搜索中的大小写敏感性
A.5.2. 使用DATE列方面的问题
A.5.3. 与NULL值有关的问题
A.5.4. 与列别名有关的问题
A.5.5. 非事务表回滚失败
A.5.6. 从相关表删除行
A.5.7. 解决与不匹配行有关的问题
A.5.8. 与浮点比较有关的问题
A.6. 与优化器有关的事宜
A.7. 与表定义有关的事宜
A.7.1. 与ALTER TABLE有关的问题
A.7.2. 如何更改表中的列顺序
A.7.3. TEMPORARY TABLE问题
A.8. MySQL中的已知事宜
A.8.1. MySQL中的打开事宜
在本附录中,列出了一些你可能会遇到的常见问题和错误消息。并介绍了确定故障原因的方法,以及为解决问题所应采取的措施。

A.1. 如何确定导致问题的原因

当你遇到问题时,首先要做的是找出导致问题的程序和设备部件:

·         如果遇到下述征兆之一,或许是因为硬件问题(如内存、主板、CPU或硬盘)或内核问题:

1.    键盘不工作。正常情况下可通过按Caps Lock建进行检查。如果Caps Lock的点亮状态未改变,就需要更换键盘(在此之前,应尝试重启计算机,并检查与键盘相连的所有电缆)。

2.    鼠标指针不移动。

3.    机器未对远程机器的Ping命令做出应答。

4.    MySQL无关的其他程序工作不正常。

5.    系统意外重启(有问题的用户级程序应不能使系统出现严重问题)。

在该情况下,应检查所有的电缆并运行某些诊断工具,对你的硬件进行检查!此外,还应检查是否有能够解决问题的、适用于你的操作系统的补丁、更新或服务包。请检查所有的库(如glibc)是否是最新的。

使用配备ECC内存的机器以便尽早发现问题总是个好主意。

·         如果键盘已锁定,可从另一台机器登录到你的机器,并执行kbd_mode –a,或许能恢复。

·         请检查系统的日志文件(/var/log/messages或类似物)以找出问题的原因。如果你认为问题出在MySQL中,应检查MySQL的日志文件。请参见5.11节,“MySQL日志文件”

·         如果你不认为存在硬件问题,应尝试找出导致问题的原因。请使用topps、任务管理器或类似程序,以检查哪个程序占用了所有CPU时间或锁定了机器。

·         使用topdf或类似程序检查是否内存不够、磁盘空间不足、文件描述符缺乏、或其他关键资源缺少。

·         如果问题是失去控制的进程,应尝试杀死它。如果杀不死进程,或许是因为操作系统中存在缺陷。

如果在检查了所有其他可能性之后,并得出结论问题是由MySQL服务器或MySQL客户端导致的,应创建提供给我方的邮件列表或支持团队的缺陷报告。在缺陷报告中,请详细描述系统的行为,以及你认为发生了什么情况。+3.

还应阐明为什么你认为是MySQL导致了问题。请考虑本章介绍的所有情况。准确阐明当你检查系统时问题是如何出现的。对于程序和日志文件的任何输出和错误消息,请使用“复制和粘贴”方法。

尽量详细描述不工作的程序,以及你所见到的所有征兆。我们过去收到过很多仅说明“系统不工作”的缺陷报告。这不会为我们提供有助于解决问题的信息。

如果程序失败,了解下述信息总是有用的:

·         有嫌疑的程序是否出现了分段故障(是否转储内核)?

·         程序是否占用了所有可用的CPU时间?用top.进行检查。让程序运行一段时间,或许能简单地评估某些事是否是计算密集性的。

·         如果问题是因mysqld服务器导致的,使用mysqladmin -u root pingmysqladmin -u root processlist是否能获得服务器的响应?

·         当你尝试连接到MySQL服务器(例如,mysql)时,客户端程序给出的信息是什么?客户端是否堵塞?是否获得了来自程序的任何输出?

发送缺陷报告时,请遵循1.7.1.2节,“请教问题或通报缺陷”中给出的说明。

A.2. 使用MySQL程序时的常见错误

本节列出了用户运行MySQL服务器时常会遇到的一些错误。尽管问题是在你尝试运行客户端时出现的,但对很多问题的解决方案来说,需要更改MySQL服务器的配置。

A.2.1. 拒绝访问

导致拒绝访问错误的原因很多。该错误常与连接时服务器允许客户端使用的MySQL账户有关。请参见5.7.8节,“拒绝访问错误的原因。请参见5.7.2节,“权限系统工作原理”

A.2.2. 无法连接到[local] MySQL服务器

Unix平台上的MySQL客户端能够以两种不同的方式连接到mysqld服务器:通过文件系统中的文件(默认为/tmp/mysql.sock)使用Unix套接字进行连接,或通过端口号使用TCP/IP进行连接。Unix套接字文件的连接速度比TCP/IP快,但仅能在与相同计算机上的服务器相连时使用。如果未指定指定主机名或指定了特殊的主机名localhost,将使用Unix套接字。

如果MySQL服务器运行在Windows 9xMe上,仅能通过TCP/IP进行连接。如服务器运行在Windows NT2000XP2003上,而且使用--enable-named-pipe选项启动,如果在运行服务器的机器上运行客户端,也能使用命名管道进行连接。默认情况下,命名管道的名称为MySQL。如果在连接到mysqld时未给定主机名,MySQL客户端首先会尝试连接到命名管道。如果不能工作,将连接到TCP/IP端口。使用“.”作为主机名,可在Windows平台上强制使用命名管道。

错误(2002)“无法连接到…”通常意味着在系统没有运行的MySQL服务器,或在连接到服务器时使用了不正确的Unix套接字文件名或TCP/IP端口号。

首先检查服务器主机上是否有名为mysqld的进程(在Unix平台上使用ps xa | grep mysqld,或在Windows平台上使用任务管理器)。如果没有这类进程,应启动服务器。请参见2.9.2.3节,“启动MySQL服务器以及其故障诊断和排除”

如果mysqld进程正在运行,可使用下述命令检查。在你的具体设置中,端口号或Unix套接字文件名可能会有所不同。host_ip代表运行服务器的机器的IP编号。

shell> mysqladmin version
shell> mysqladmin variables
shell> mysqladmin -h `hostname` version variables
shell> mysqladmin -h `hostname` --port=3306 version
shell> mysqladmin -h host_ip version
shell> mysqladmin --protocol=socket --socket=/tmp/mysql.sock version

注意,应与主机名命令一起使用“`”而不是“’”,这会使主机名输出(当前主机名)被代入mysqladmin命令。如果没有主机名命令或正运行在Windows平台上,应以手动方式输入机器的主机名(无“`”符号),后跟-h选项。也可以使用TCP/IP协议用-h 127.0.0.1连接到本地主机。

下面给出了一些“无法连接到本地MySQL服务器”错误的可能原因:

1.    Mysqld未运行。请检查操作系统的进程列表以确保mysqld进程正在运行。

2.    你正在具有很多TCP/IP连接的Windows平台上运行MySQL服务器。如果你的客户端经常出现错误,请参见A.2.2.1节,“在Windows上与MySQL服务器的连接失败”,以找出规避方法。

3.    你正在使用MIT-pthreads的系统上运行。如果你正在运行不具有固有线程的操作系统,mysqld将使用MIT-pthreads软件包。请参见2.1.1节,“MySQL支持的操作系统”。但是,并非所有的MIT-pthreads版本均支持Unix套接字文件。在不支持套接字文件的系统上,连接到服务器时,必须明确指定主机名。请使用下述命令来检查是否连接到了服务器:

4.           shell> mysqladmin -h `hostname` version

5.    某人移动了mysqld使用的Unix套接字文件(默认为/tmp/mysql.sock)。例如,你可能执行了将旧文件从/tmp目录删除的cron任务。你总能执行mysqladmin version来检查mysqladmin试图使用的Unix套接字文件是否的确存在。在该情况下,更正方式是更改cron任务,不删除mysql.sock文件,或将套接字文件置于其他地方。请参见A.4.5节,“如何保护或更改MySQL套接字文件/tmp/mysql.sock

6.    你使用--socket=/path/to/socket选项启动了mysqld服务器,当忘记将套接字文件的新名称通知客户端程序。如果更改了关于服务器的套接字路径,也必须通知MySQL客户端。可在运行客户端程序时使用相同的—socket选项来完成该任务。此外,你还应确保客户端具有访问文件mysql.sock的权限。要想找出套接字文件的位置,可使用:

7.           shell> netstat -ln | grep mysql

请参见A.4.5节,“如何保护或更改MySQL套接字文件/tmp/mysql.sock

8.    你正在使用Linux而且1个服务器线程已死亡(内核已清除)。在此情况下,在重启MySQL服务器之前,必须杀死其他mysqld线程(例如,使用killmysql_zap脚本)。请参见A.4.2节,“如果MySQL依然崩溃,应作些什么”

9.    服务器或客户端程序不具有访问包含Unix套接字文件的目录或套接字文件本身的恰当权限。在该情况下,必须更改目录或套接字文件的访问权限,以便服务器或客户端程序能够访问它们,或用–socket选项重启mysqld,在该选项中指定服务器能创建、而且客户端可访问的目录下的套接字文件名。

如果遇到错误消息“无法连接到some_host上的MySQL服务器”,可尝试采取下述步骤以找出问题所在:

·         执行“telnet some_host 3306并按两次回车键,检查服务器是否运行在该主机上(3306是默认的MySQL端口号。如果你的服务器正在监听不同的端口,请更改该值)。如果有1MySQL服务器正在运行并监听该端口,你应收到包含服务器版本号的回应。如果遇到错误,如“telnet:无法连接到远程主机:拒绝连接,表示在该定端口上没有运行的服务器。

·         如果服务器正运行在本地主机上,请使用Unix套接字文件,并使用mysqladmin -h localhost variables进行连接。验证服务器监听的TCP/IP端口号(它是port变量的值)。

·         确保你的mysqld服务器未用--skip-networking选项启动。如果使用了该选项,将无法使用TCP/IP连接到它。

·         检查并确认不存在屏蔽了对MySQL访问的防火墙。需要配置诸如ZoneAlarmWindows XP个人防火墙等应用程序,以允许对MySQL服务器的外部访问。

A.2.2.1. 在Windows上与MySQL服务器的连接失败

当你在具有很多TCP/IP连接的Windows上运行MySQL服务器,并经常在客户端上遇到“无法连接到MySQL服务器”错误时,可能是因为Windows不允许足够的临时(短命)端口用于这类连接。

默认情况下,Windows允许用于使用5000个临时(短命)TCP端口。任何端口关闭后,它将在TIME_WAIT状态保持120秒。与重新初始化全新的连接相比,该状态允许以更低的开销重新使用连接。但是,在该时间逝去前,无法再次使用该端口。

对于小的可用TCP端口堆栈(5000),以及具有TIME_WAIT状态的大量在短时间内打开和关闭的TCP端口,你很可能遇到端口耗尽问题。处理该问题的方法有两种:

·         通过调查连接池以及可能的持久连接,减少快速消耗的TCP端口数。

·         调整Windows注册表中的某些设置(请参见下面)。

要点:下述步骤涉及更改Windows 注册表。更改注册表之前,请备份注册表,并确认你已掌握在出现问题时恢复注册表的方法。关于备份年、恢复和编辑注册表的更多信息,请请参见Microsoft知识库中的下述文献:http://support.microsoft.com/kb/256986/EN-US/

·         启动注册表编辑器(Regedt32.exe)。

·         在注册表中确定下述键值的位置:

·                HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters

·         在“编辑”菜单上点击“添加值”,然后增加下述注册值:

·                Value Name: MaxUserPort
·                Data Type: REG_DWORD
·                Value: 65534

它用于设置为任何用户提供的临时端口数。有效范围介于500065534之间(十进制)。默认值为0x13885000,十进制)。

·         在“编辑”菜单上点击“添加值”,然后增加下述注册值:

·                Value Name: TcpTimedWaitDelay
·                Data Type: REG_DWORD
·                Value: 30

它用于设置关闭之前将TCP端口连接保持在TIME_WAIT状态的秒数。有效范围介于0秒和300秒之间。默认值为0x78120秒)。

·         退出注册表编辑器。

·         重新引导机器。

注释:撤销上述设置十分简单,就像删除你创建的注册表一样。

A.2.3. 客户端不支持鉴定协议

MySQL 5.1采用了基于密码混编算法的鉴定协议,它与早期客户端(4.1之前)使用的协议不兼容。如果你将服务器升级到4.1之上,用早期的客户端进行连接可能失败,并给出下述消息:

shell> mysql
客户端不支持服务器请求的鉴定协议:请考虑升级MySQL客户端。

要想解决该问题,应使用下述方法之一:

·         升级所有的客户端程序,以使用4.1.1或更新的客户端库。

·         4.1版之前的客户端连接到服务器时,请使用仍具有4.1版之前风格密码的账户。

·         对于需要使用4.1版之前的客户端的每位用户,将密码恢复为4.1版之前的风格。可以使用SET PASSWORD语句和OLD_PASSWORD()函数完成该任务:

·                mysql> SET PASSWORD FOR
·                    -> 'some_user'@'some_host' = OLD_PASSWORD('newpwd');

也可以使用UPDATEFLUSH PRIVILEGES

mysql> UPDATE mysql.user SET Password = OLD_PASSWORD('newpwd')
    -> WHERE Host = 'some_host' AND User = 'some_user';
mysql> FLUSH PRIVILEGES;

用你打算使用的密码替换前例中的“newpwd”。MySQL不能告诉你原来的密码是什么,因此,你需要选择新的密码。

·         通知服务器使用旧的密码混编算法:

1.    使用“--old-passwords”选项启动mysqld

2.    对于已将密码更新为较长4.1格式的每个账户,为其指定具有旧格式的密码。可以使用下述查询确定这些账户:

3.                 mysql> SELECT Host, User, Password FROM mysql.user
4.                     -> WHERE LENGTH(Password) > 16;

对于查询显示的每个账户记录,请使用HostUser值,并使用OLD_PASSWORD()函数以及SET PASSWORDUPDATE之一指定密码,如前面所介绍的那样。

注释:在早期的PHP版本中,mysql扩展不支持MySQL 4.1.1和更高版中的鉴定协议。无论使用的PHP版本是什么,它均是正确的。如果你打算与MySQL 4.1或更高版本一起使用mysql扩展,需要使用前面介绍的选项之一,配置MySQL,以便与较早的客户端一起使用。mysqli扩展(支持改进的MySQL”,在PHP 5中增加)与MySQL 4.1和更高版本中使用的改进的密码混编算法兼容,不需要对MySQL进行特殊配置就能使用该MySQL客户端库。关于mysqli扩展的更多信息,请参见http://php.net/mysqli

关于密码混编和鉴定功能的额外背景知识,请参见5.7.9节,“MySQL 4.1中的密码哈希处理”

A.2.4. 输入密码时出现密码错误

使用无下述密码值的“—password”-p”选项调用时,MySQL客户端程序将提示输入密码:
shell> mysql -u user_name -p
Enter password:

在某些系统上,当你在选项文件或命令行上指定时,你可能会发现密码能够工作,但是当你在“Enter password:”提示下以交互方式输入密码时,你可能会发现输入的密码不工作。当系统所提供的用于读取密码的库将密码值限定在少数字符时(典型情况下为8个),就会出现该问题。这是与系统库有关的问题,与MySQL无关。要想处理该问题,可将MySQL密码更改为由8个字符或更少字符构成的值,或将密码置于选项文件中。

A.2.5. 主机的host_name被屏蔽

如果遇到下述错误,表示mysqld已收到来来自主机“host_name”的很多连接请求,但该主机却在中途中断。

由于出现很多连接错误,主机'host_name'被屏蔽。
可使用'mysqladmin flush-hosts'解除屏蔽。

允许的中断连接请求的数目由max_connect_errors系统变量的值决定。当超出max_connect_errors规定的连接请求时,mysqld将认为某处出错(例如,某人正试图插入),并屏蔽主机的进一步连接请求,直至执行了mysqladmin flush-hosts命令,或发出了FLUSH HOSTS语句为止。请参见5.3.3节,“服务器系统变量”

在默认情况下,mysqld会在10次连接错误后屏蔽主机。你可以通过下述方式启动服务器来调整该值:

shell> mysqld_safe --max_connect_errors=10000 &

如果在给定主机上遇到该错误,首先应核实该主机的TCP/IP连接是否正确。如果存在网络问题,增加max_connect_errors变量的值不会有任何好处。

A.2.6. 连接数过多

当你试图连接到mysqld服务器时遇到“过多连接”错误,这表示所有可用的连接均已被其他客户端使用。

允许的连接数由max_connections系统变量控制。默认值为100。如果需要支持更多的连接,应使用该变量的较大值重启mysqld

mysqld实际上允许max_connections+1个客户端进行连接。额外的连接保留给具有SUPER权限的账户。通过为系统管理员而不是普通用户授予SUPER权限(普通用户不应具有该权限),系统管理员能够连接到服务器,并使用SHOW PROCESSLIST来诊断问题,即使已连接的无特权客户端数已达到最大值也同样。请参见13.5.4.16节,“SHOW PROCESSLIST语法”

MySQL能支持的最大连接数取决于给定平台上线程库的质量。LinuxSolaris应能支持500-1000个并发连接,具体情况取决于RAM容量,以及客户端正在作什么。MySQL AB提供的静态Linux库能支持高达4000个连接。

A.2.7. 内存溢出

如果使用mysql客户端程序发出了查询,并收到下述错误之一,则表示mysql没有足够内存来保存全部查询结果:

mysql: Out of memory at line 42, 'malloc.c'
mysql: needed 8136 byte (8k), memory in use: 12481367 bytes (12189k)
错误2008: MySQL client ran out of memory

要想更正该问题,首先应检查查询是否正确。返回这么多行是否合理?如果不合理,更正查询并再次尝试。否则,应使用“--quick”选项调用mysql。这样,将使用mysql_use_result() C API函数来检索结果集,这类函数能够降低客户端上的负载(但会加重服务器上的负载)。

A.2.8. MySQL服务器不可用

在本节中,还介绍了出现查询错误期间,与丢失了服务器连接有关的事宜。

MySQL服务器不可用错误的最常见原因是服务器超时以及连接已关闭。在该情况下,通常能见到下述错误代码之一(具体的错误代码与操作系统有关):

错误代码

描述

CR_SERVER_GONE_ERROR

客户端无法将问题发送至服务器。

CR_SERVER_LOST

写入服务器时客户端未收到错误,但也未获得问题的完整答案(或任何答案)。

在默认情况下,如果未发生任何事,8小时后服务器将关闭连接。也可以在启动mysqld时,通过设置wait_timeout变量更改时间限制。请参见5.3.3节,“服务器系统变量”.

如果有1个脚本,你仅需要再次发出查询,让客户端再次进行自动连接即可。其中,假定在客户端中启用了自动再连接功能(对于mysql命令行客户端,这是默认设置)。

MySQL服务器不可用错误的一些其他常见原因如下:

·         你(或db系统管理员)使用KILL语句或mysqladmin kill命令杀死了正在运行的线程。

·         你试图在关闭了与服务器的连接后运行查询。这表明应更正应用程序中的逻辑错误。

·         你在客户端一侧遇到TCP/IP连接超时错误。如果你使用了命令:mysql_options(..., MYSQL_OPT_READ_TIMEOUT,...)mysql_options(..., MYSQL_OPT_WRITE_TIMEOUT,...),就可能出现该问题。在该情况下,增加超时值可能有助于问题的解决。

·         你在服务器端遇到超时错误,而且禁止了客户端中的自动再连接功能(MYSQL结构中的再连接标志等于0)。

·         你正在使用Windows客户端,而且在发出命令之前服务器撤销了连接(或许是因为已超过wait_timeout

Windows平台上出现问题的原因,在某些情况下,将TCP/IP连接写入服务器时,MySQL未收到来自操作系统的错误,但当试图从连接读取答案时出现错误。

在该情况下,即使MYSQL结构中的再连接标志等于1MySQL也不会执行自动再连接并再次发出查询,这是因为它不知道服务器是否收到原始查询。

对此的解决方式是:如果自上一次查询以来经过了较长时间,在连接上执行mysql_ping(正是MyODBC所作的);或在mysqld服务器上将wait_timeout设置得很高,使之实际上不存在超时。

·         如果你向服务器发出了不正确或过大的查询,也会遇到这类问题。如果mysqld收到过大或无序的信息包,它会认为客户端出错,并关闭连接。如果需要执行较大的查询(例如,正在处理大的BLOB列),可通过设置服务器的max_allowed_packet变量,增加查询限制值,该变量的默认值为1MB。或许,你还需增加客户端上的最大信息包大小。关于设置信息包大小的更多信息,请参见A.2.9节,“信息包过大”

·         如果你的客户端低于4.0.8而且你的服务器高于4.0.8,当你接收16MB或更大的信息包时,可能会丢失连接。

·         如果MySQL是用“--skip-networking”选项启动的,也会见到MySQL服务器不可用错误。

·         你遇到了执行查询时服务器宕机的缺陷。

通过执行mysqladmin version并检查服务器的正常工作时间,可检查服务器是否宕机并重启。如果客户端连接是因mysqld崩溃和重启而断开的,应将重点放在查找崩溃你方面。首先应再次检查发出的查询是否再次杀死了服务器。请参见A.4.2节,“如果MySQL依然崩溃,应作些什么”

用“--log-warnings=2选项启动mysqld,可获得关于连接的更多信息。这样,就能将某些断开连接错误记录到hostname.err文件中。请参见5.11.1节,“错误日志”

如果你打算创建与该问题有关的缺陷报告,务必包含下述信息:

1.    指明MySQL服务器是否宕机。通过服务器错误日志可发现这方面的信息。请参见A.4.2节,“如果MySQL依然崩溃,应作些什么”

2.    如果特定查询杀死了mysqld,而且在运行查询前用CHECK TABLE检查了涉及的表,你是否能提供可重复的测试范例?请参见E.1.6节,“如果出现表崩溃,请生成测试案例”

3.    MySQL服务器中,系统变量wait_timeout的值是什么?mysqladmin variables给出了该变量的值)。

4.    你是否尝试使用“--log”选项来运行mysqld,以确定是否在日志中出现问题?

另请参见A.2.10节,“通信错误和失效连接”

请参见1.7.1.2节,“请教问题或通报缺陷”

A.2.9. 信息包过大

通信信息包是发送至MySQL服务器的单个SQL语句,或发送至客户端的单一行。

MySQL 5.1服务器和客户端之间最大能发送的可能信息包为1GB

MySQL客户端或mysqld服务器收到大于max_allowed_packet字节的信息包时,将发出信息包过大错误,并关闭连接。对于某些客户端,如果通信信息包过大,在执行查询期间,了能回遇到丢失与MySQL服务器的连接错误。

客户端和服务器均有自己的max_allowed_packet变量,因此,如你打算处理大的信息包,必须增加客户端和服务器上的该变量。

如果你正在使用mysql客户端程序,其max_allowed_packet变量的默认值为16MB。要想设置较大的值,可用下述方式启动mysql

mysql> mysql --max_allowed_packet=32M

它将信息包的大小设置为32MB

服务器的默认max_allowed_packet值为1MB。如果服务器需要处理大的查询,可增加该值(例如,如果准备处理大的BLOB列)。例如,要想将该设置为16MB,可采用下述方式启动服务器:

mysql> mysqld --max_allowed_packet=16M

也能使用选项文件来设置max_allowed_packet要想将服务器的该变量设置为16MB,可在选项文件中增加下行内容:

[mysqld]
max_allowed_packet=16M

增加该变量的值十分安全,这是因为仅当需要时才会分配额外内存。例如,仅当你发出长查询或mysqld必须返回大的结果行时mysqld才会分配更多内存。该变量之所以取较小默认值是一种预防措施,以捕获客户端和服务器之间的错误信息包,并确保不会因偶然使用大的信息包而导致内存溢出。

如果你正是用大的BLOB值,而且未为mysqld授予为处理查询而访问足够内存的权限,也会遇到与大信息包有关的奇怪问题。如果怀疑出现了该情况,请尝试在mysqld_safe脚本开始增加ulimit -d 256000,并重启mysqld

A.2.10. 通信错误和失效连接

对于连接问题,服务器错误日志是有用的信息源。请参见5.11.1节,“错误日志”。如果服务器是用“--log-warnings”选项启动的,在错误日志中可能会发现下述消息:

010301 14:38:23  Aborted connection 854 to db: 'users' user: 'josh'

如果“Aborted connections”(放弃连接)消息出现在错误日志中,可能的原因是:

1.    客户端程序在退出之前未调用mysql_close()

2.    客户端的空闲时间超过wait_timeoutinteractive_timeout秒,未向服务器发出任何请求。请参见5.3.3节,“服务器系统变量”

3.    客户端在数据传输中途突然结束。

出现这类情况时,服务器将增加“Aborted_clients”(放弃客户端)状态变量。

出现下述情况时,服务器将增加“Aborted_clients”(放弃客户端)状态变量。

·         客户端不具有连接至数据库的权限。

·         客户端采用了不正确的密码。

·         连接信息包不含正确信息。

·         获取连接信息包的时间超过connect_timeout秒。请参见5.3.3节,“服务器系统变量”

如果出现这类情况,可能表明某人正试图侵入你的服务器!

对于放弃客户端或放弃连接问题,其他可能的源应包括:

·         Linux一起使用以太网协议,半双工或全双工。很多Linux以太网驱动均存在该缺陷。应通过FTP在客户端和服务器机器之间传输大文件来测试该缺陷。如果传输处于burst-pause-burst-pause(爆发-暂停-爆发-暂停)模式,表明你遇到了Linux双工故障。唯一的解决方法是,将网卡和Hub/交换器的双工模式切换为全双工或半双工,并对结果进行测试以确定最佳设置。

·         与线程库有关的某些问题导致读取中断。

·         配置不良的TCP/IP

·         有问题的以太网、Hub、交换器、电缆等。仅能通过更换硬件才能恰当诊断。

·         变量max_allowed_packet过小或查询要求的内存超过为mysqld分配的内存。请参见A.2.9节,“信息包过大”

另请参见A.2.8节,“MySQL服务器不可用”

A.2.11. 表已满

表已满错误出现的方式有数种:

·         你正在使用低于3.23版的MySQL服务器,而且“内存中”临时表超过了tmp_table_size字节。要想避免该问题,可使用“-O tmp_table_size=val”选项以便mysqld增加临时表的大小,或在发出有问题的查询之前,使用SQL选项SQL_BIG_TABLES请参见13.5.3节,“SET语法”

也可以使用“--big-tables”选项启动mysqld。它与使用针对所有查询的SQL_BIG_TABLES完全相同。

MySQL 3.23起,该问题应不再出现。如果“内存中”临时表超过tmp_table_size,服务器会自动将其转换为基于磁盘的MyISAM表。

·         你正在使用InnoDB表,并超出了InnoDB表空间。在该情况下,解决方法是增加InnoDB表空间。请参见15.2.7节,“添加和删除InnoDB数据和日志文件

·         你正在仅支持2GB文件的操作系统上使用ISAMMyISAM表,数据文件或索引文件达到了该限制值。

·         你正在使用MyISAM表,而且表所需的空间超过内部指针允许的大小。如果在创建表时未指定MAX_ROWS表,MySQL将使用myisam_data_pointer_size系统变量。默认值为6字节,它足以容纳65536TB数据。请参见5.3.3节,“服务器系统变量”

使用该语句,可检查最大数据/索引大小:

SHOW TABLE STATUS FROM database LIKE 'tbl_name';

也可以使用myisamchk -dv /path/to/table-index-file

如果指针大小过小,可使用ALTER TABLE更正该问题:

ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;

仅应为具有BLOBTEXT列的表指定AVG_ROW_LENGTH在该情况下,MySQL不能仅根据行数优化所需的空间。

A.2.12. 无法创建文件/写入文件

如果对某些查询遇到下述类型的错误,它意味着MySQL不能为临时目录下的结果集创建临时文件:

无法创建/写入文件'\\sqla3fe_0.ism'

前述错误是Windows平台上的典型消息,Unix平台上的消息与之类似。

一种更正方式是使用“--tmpdir”选项启动mysqld,或在选项文件的[mysqld]部分增加该选项。例如,要想指定目录C:\temp,可使用:

[mysqld]
tmpdir=C:/temp

目录C:\temp必须存在,并有足够的空间允许MySQL写入它。请参见4.3.2节,“使用选项文件”

该错误的另一个原因可能是许可事宜。请确认MySQL服务器能够写入tmpdir目录。

此外,还用使用perror检查错误代码。服务器无法写入表的一个原因是文件系统已满。

shell> perror 28
错误代码28:磁盘上无剩余空间。

A.2.13. 命令不同步

如果遇到“命令不同步”错误,将无法在你的客户端代码中运行该命令,你正在以错误顺序调用客户端函数。

例如,如果你正使用mysql_use_result(),并打算在调用mysql_free_result()之前执行新查询,就会出现该问题。如果你试图执行两次查询,但并未在两次查询之间调用mysql_use_result()mysql_store_result(),也会出现该问题。

A.2.14. 忽略用户

如果遇到下述错误,表示当启动mysqld时或重新加载授权表时,在用户表中发现具有非法密码的账户。

发现用户'some_user'@'some_host'密码错误:忽略用户。

作为其结果,许可系统将简单忽略账户。

在下面的介绍中,指明了可能的原因和问题的更正措施:

1.    或许,你正打算用旧的用户表运行新版本的mysqld。执行mysqlshow mysql user检查Password(密码)列是否短于16个字符,通过该方式可检查该问题。如果结果是肯定的,可运行脚本/add_long_password脚本更正该问题。

2.    账户具有旧的密码(8字符长),而且未使用“--old-protocol”选项启动mysqld。更新用户表中的账户,使之具有新的密码,或使用“--old-protocol”选项重启mysqld

3.    在用户表中未使用PASSWORD()函数指定了密码。使用mysql用新密码更新用户表中的账户,务必使用PASSWORD()函数:

4.           mysql> UPDATE user SET Password=PASSWORD('newpwd')
5.               -> WHERE User='some_user' AND Host='some_host';

A.2.15. 表tbl_name不存在

如果遇到下述错误之一,通常意味着当前数据库中不存在具有给定名称的表:

'tbl_name'不存在
无法找到文件:'tbl_name' (errno: 2)

在某些情况下,表或许存在,但未正确引用它:

·         由于MySQL使用目录和文件来保存数据库和表,如果它们位于区分文件名大小写的文件系统上,数据库和表名也区分文件大小写。

·         即使对于不区分大小写的文件系统,如Windows,在查询内对给定表的所有引用必须使用相同的大小写。

可以使用SHOW TABLES检查位于当前数据库中的表。请参见13.5.4节,“SHOW语法”

A.2.16. 无法初始化字符集

如果存在字符集问题,可能会遇到下述错误:

MySQL连接失败:无法初始化字符集charset_name

导致该错误的原因:

·         字符集为多字节字符集,但客户端不支持该字符集。在该情况下,需要使用“--with-charset=charset_name”或“--with-extra-charsets=charset_name”选项运行configure以重新编译客户端。请参见2.8.2节,“典型配置选项

所有的标准MySQL二进制文件均是采用“--with-extra-character-sets=complex”编译的,能够支持所有的多字节字符集。请参见5.10.1节,“数据和排序用字符集”

·         字符集是未编译到mysqld中的简单字符集,而且字符集定义文件不在客户端预期的位置。

在该情况下,需要采取下述方法之一解决问题:

1.    重新编译客户端,使之支持字符集。请参见2.8.2节,“典型配置选项

2.    为客户端指定字符集定义文件所在的目录。对于很多客户端,可使用“--character-sets-dir”选项完成该任务。

3.    将字符集定义文件复制到客户端预期的位置。

A.2.17. 文件未找到

如果遇到“ERROR '...'未发现(errno: 23)”无法打开文件:... (errno: 24)”,或来自MySQL的具有errno 23errno 24的其它错误,它表示未为MySQL服务器分配足够的文件描述符。你可以使用perror实用工具来了解错误编号的含义:

shell> perror 23
错误代码23:文件表溢出
shell> perror 24
错误代码24:打开文件过多
shell> perror 11
错误代码11:资源暂时不可用

这里的问题是,mysqld正试图同时打开过多的文件。你可以通知mysqld不要一次打开过多文件,或增加mysqld可用文件描述符的数目。

要想通知mysqld将一次打开的文件控制在较小的数目上,可降低table_cache系统变量的值(),从而减少表高速缓冲(默认值为64)。降低max_connections的值也能降低打开文件的数目(默认值为100)。

要想更改mysqld可用的文件描述符的数目,可在mysqld_safe上使用“--open-files-limit”选项或设置(自MySQL 3.23.30开始)open_files_limit系统变量。请参见5.3.3节,“服务器系统变量”。设置这些值的最简单方式是在选项文件中增加1个选项。请参见4.3.2节,“使用选项文件”。如果mysqld的版本较低,不支持设置打开文件的数目,可编辑mysqld_safe脚本。在脚本中有1个注释掉的行ulimit -n 256。你可以删除#’字符取消对该行的注释,更改数值256,以设置mysqld可用的文件描述符数目。

“--open-files-limit”ulimit能够增加文件描述符的数目,但最高不能超过操作系统限制的数目。此外还有1个“硬”限制,仅当以根用户身份启动mysqld_safemysqld时才能覆盖它(请记住,在该情况下,还需使用“--user”选项启动服务器,以便在启动后不再以根用户身份继续运行)。如果需要增加操作系统限制的对各进程可用文件描述符的数目,请参阅系统文档。

注释:如果运行tcsh shellulimit不工作!请求当前限制值时,tcsh还能通报不正确的值。在该情况下,应使用sh启动mysqld_safe

A.3. 与安装有关的事宜

A.3.1. 与MySQL客户端库的链接问题

当你链接到应用程序以使用MySQL客户端库时,可能会遇到以mysql_开始的未定义引用错误,如下所示:

/tmp/ccFKsdPa.o: 在函数`main':
/tmp/ccFKsdPa.o(.text+0xb): `mysql_init'的未定义引用。
/tmp/ccFKsdPa.o(.text+0x31): `mysql_real_connect'的未定义引用。
/tmp/ccFKsdPa.o(.text+0x57): `mysql_real_connect'的未定义引用。
/tmp/ccFKsdPa.o(.text+0x69): `mysql_error'的未定义引用。
/tmp/ccFKsdPa.o(.text+0x9a): `mysql_close'的未定义引用。

通过在链接命令后增加“-Ldir_path -lmysqlclient”选项,应能解决该问题,其中,dir_path代表客户端库所在目录的路径名。要想确定正确的目录,可尝试下述命令:

shell> mysql_config --libs

来自mysql_config的输出可能会指明应在链接命令上指定的其他库。

对于非压缩或压缩函数,如果遇到未定义引用错误,可在链接命令后添加-lz,并再次尝试。

对于应在系统上存在的函数(如connect),如果遇到未定义引用错误,请检查相关函数的手册页,以便确定应在链接命令上增加哪些库。

对于系统上不存在的函数,可能会遇到未定义引用错误,如下所示:

mf_format.o(.text+0x201): `__lxstat'的未定义引用。

它通常意味着你的MySQL客户端库是在与你的系统不100%兼容的系统上编译的。在该情况下,应下载最新的MySQL源码分发版,并自己编译MySQL。请参见2.8节,“使用源码分发版安装MySQL”

当你试图执行MySQL程序时,可能会遇到运行时未定义引用错误。如果这类错误指明了以mysql_开始的符号,或指明未发现mysqlclient,这意味着你的系统无法找到共享的libmysqlclient.so库。对其的更正方式是,通知系统在库所在位置搜索共享库。请使用与系统相适应的下述方法:

·         libmysqlclient.so所在目录的路径添加到LD_LIBRARY_PATH环境变量中。

·         libmysqlclient.so所在目录的路径添加到LD_LIBRARY环境变量中。

·         libmysqlclient.so拷贝到可被系统搜索的目录下,如/lib,然后通过执行ldconfig更新共享库信息。

解决该问题的另一种方法是,以静态方式将你的程序与“-static”选项链接在一起,或在链接代码之前删除动态MySQL库。使用第2种方法之前,应确保没有使用动态库的其它程序。

A.3.2. 如何以普通用户身份运行MySQL

Windows平台上,能够使用普通用户账户以Windows服务方式运行服务器。

Unix平台上,不是任何用户都能启动并运行MySQL服务器mysqld。但是,处于安全方面的原因,应避免以Unix根用户身份运行服务器。要想更改mysqld,使之能以正常的无特权Unix用户user_name身份运行,必须采取下述步骤:

如果服务器正在运行,停止它(使用mysqladmin shutdown)。

更改数据库目录和文件,允许user_name读写其中的文件(可能需要以Unix根用户身份完成这类设置):

shell> chown -R user_name /path/to/mysql/datadir

如果未这样做,当以user_name身份运行时,服务器无法访问数据库或表。

如果MySQL数据目录下的目录或文件采用的是符号链接,还需跟踪这些链接,并更改它们指向的目录和文件。chown –R可帮助你跟踪符号链接。

user_name身份启动服务器。如果你正在使用MySQL 3.22或更高版本,另一种可选方式是,以Unix根用户身份启动mysqld,并使用--user=user_name选项。mysqld启动,然后在接受任何连接前,切换至Unix用户user_name并以该用户身份运行。

要想在系统启动时自动以给定的用户身份启动服务器,可在服务器数据目录下的/etc/my.cnf选项文件或my.cnf选项文件的[mysqld]组中,通过增加用户选项来指定用户名。。例如:

[mysqld]
user=user_name

如果你的Unix机器本身并不安全,应在授权表中为MySQL根账户指定密码。如不然,任何在该机器上具有登录账户的用户都能使用“--user=root”选项运行mysql客户端,并执行任何操作。在任何情况下均应为MySQL账户指定密码,这是个好主意,尤其是在服务器主机上存在其他登录账户时,更是如此。请参见2.9节,“安装后的设置和测试”

A.3.3. 与文件许可有关的问题

如果遇到与文件许可有关的问题,可能数启动mysqldUMASK环境变量设置得不正确。例如,当你创建表时,MySQL可能会发出下述错误消息:

ERROR: 无法找到文件:'path/with/filename.frm' (Errcode: 13)

UMASK的默认值是0660。通过下述方式启动mysqld_safe,可改变该情况:

shell> UMASK=384  # = 600 in octal
shell> export UMASK
shell> mysqld_safe &

在默认情况下,MySQL0700的许可创建数据库和RAID目录。你可以通过设置UMASK_DIR变量更改该行为。如果你设置了它的值,将使用组合的UMASKUMASK_DIR值创建新目录。例如,如果你打算为所有新的目录授予组访问权限,可:

shell> UMASK_DIR=504  # = 770 in octal
shell> export UMASK_DIR
shell> mysqld_safe &

MySQL 3.23.25和更高版本中,如果是以0开始的,MySQL将认为UMASKUMASK_DIR的值均采用八进制形式。

请参见附录F:环境变量

A.4. 与管理有关的事宜

A.4.1. 如何复位根用户密码

如果你从未为MySQL设置根用户密码,服务器在以根用户身份进行连接时不需要密码。但是,建议你为每个账户设置密码。请参见5.6.1节,“通用安全指南”

如果你以前设置了根用户密码,但却忘记了该密码,可设置新的密码。下述步骤是针对Windows平台的。在本节后面的内容中,介绍了针对Unix平台的步骤。

Windows平台下,该步骤是:

以系统管理员身份登录到系统。

如果MySQL服务器正在运行,停止它。对于作为Windows服务运行的服务器,进入服务管理器:

开始菜单->控制面板->管理工具->服务

然后在列表中找出MySQL服务器,并停止它。

如果服务器不是作为服务而运行的,可能需要使用任务管理器来强制停止它。

创建1个文本文件,并将下述命令置于单一行中:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');

用任意名称保存该文件。在本例中,该文件为C:\mysql-init.txt

打开控制台窗口,进入DOS命令提示:

开始菜单->运行-> cmd

假定你已将MySQL安装到C:\mysql。如果你将MySQL安装到了另一位置,请对下述命令进行相应的调整。

DOS命令提示符下,执行命令:

C:\> C:\mysql\bin\mysqld-nt --init-file=C:\mysql-init.txt

在服务器启动时,执行由“--init-file”选项命名的文件的内容,更改根用户密码。当服务器成功启动后,应删除C:\mysql-init.txt

如果你使用MySQL安装向导安装了MySQL,或许需要指定“--defaults-file”选项:

C:\> C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld-nt.exe
         --defaults-file="C:\Program Files\MySQL\MySQL Server 5.1\my.ini"
         --init-file=C:\mysql-init.txt

使用服务管理器,可找到恰当的“--defaults-file”设置:

开始菜单->控制面板->管理工具->服务

在列表中找出MySQL服务,右击,并选择“属性”选项。在可执行字段的Path(路径)中包含“--defaults-file”设置。

停止MySQL服务器,然后在正常模式下重启它。如果以服务方式运行服务器,应从Windows服务窗口启动它。如果以手动方式启动了服务器,能够像正常情形下一样使用命令。

应能使用新密码进行连接。

Unix环境下,重置根用户密码的步骤如下:

Unix根用户身份、或以运行mysqld服务器的相同身份登录到系统。

找到包含服务器进程ID.pid文件。该文件的准确位置和名称取决于你的分发版、主机名和配置。常见位置是/var/lib/mysql//var/run/mysqld//usr/local/mysql/data/一般情况下,文件名的扩展名为.pid,并以mysqld或系统的主机名开始。

在下述命令中使用.pid文件的路径名,向mysqld进程发出正常的kill(而不是kill -9),可停止MySQL服务器:

shell> kill `cat /mysql-data-directory/host_name.pid`

注意,cat命令使用符号“`”而不是“’”:这会使cat的输出代入到kill命令中。

创建文本文件,并将下述命令放在文件内的1行上:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');

用任意名称保存文件。对于本例,文件为~/mysql-init

用特殊的“--init-file=~/mysql-init”选项重启MySQL服务器:

shell> mysqld_safe --init-file=~/mysql-init &

文件init-file的内容在服务器启动时执行,更改根用户密码。服务器成功启动后,应删除~/mysql-init

应能使用新密码进行连接。

作为可选方式,在任何平台上,可使用mysql客户端设置新密码(但该方法不够安全):

停止mysqld,并用“--skip-grant-tables --user=root”选项重启它Windows用户可省略--user=root部分)。

使用下述命令连接到mysqld服务器:

shell> mysql -u root

mysql客户端发出下述语句:

mysql> UPDATE mysql.user SET Password=PASSWORD('newpwd')
    ->                   WHERE User='root';
mysql> FLUSH PRIVILEGES;

用打算使用的实际根用户密码替换“newpwd”。

应能使用新密码进行连接。

A.4.2. 如果MySQL依然崩溃,应作些什么

正式发布之前,每个MySQL版本均在很多平台上进行了测试。这不表示MySQL中不存在缺陷,但是,如果存在缺陷,它们应很少,而且很难发现。如果你遇到问题,如果你尝试找出导致系统崩溃的准确原因,这始终很有帮助,这是因为,如果这样的话,快速解决问题的机会很大。

首先,应尝试找出问题是否与mysqld服务器有关,或是否与客户端有关。通过执行mysqladmin version,可检查mysqld服务器运行了多长时间。如果mysqld宕机并重启,应查看服务器的错误日志以找出原因。请参见5.11.1节,“错误日志”

在某些系统上,在错误日志中,可发现mysqld宕机的堆栈跟踪,可使用resolve_stack_dump程序解决它。请参见E.1.4节,“使用堆栈跟踪”。注意,错误日志中的变量值并非始终是100%正确的。

很多服务器崩溃是因损坏的数据文件或索引文件而导致的,每次执行完SQL语句之后并在向客户端通告结果之前,MySQL将使用write()系统调用更新磁盘上的文件(如果你使用了“--delay-key-write”选项,情况并非如此,此时将写入数据文件而不是索引文件)。这意味着,即使mysqld崩溃,数据文件的内容也是安全的,这是因为操作系统能保证将未刷新的数据写入磁盘。使用“--flush”选项启动mysqld,这样,每次执行完SQL语句后,可强制MySQL将所有内容写入磁盘。

前述介绍表明,在正常情况下不会出现损坏的表,除非出现了下述情况之一:

在更新过程中,MySQL服务器或服务器主机被停止。

你发现了mysqld中存在的1个缺陷,该缺陷导致mysqld在更新中途中止。

mysqld操作的同时,某些外部程序正在操控数据文件或索引文件,未恰当锁定表。

你正使用系统上的相同数据目录运行很多mysqld服务器,该系统不支持良好的文件系统锁定(通常是由lockd锁定管理器负责的),或使用“--skip-external-locking”选项运行了多个服务器。

崩溃的数据文件或索引文件,其中包含导致mysqld混乱的损坏很严重的数据。

在数据存储节点发现缺陷。这种可能性不大,但至少是可能的。在该情况下,可在修复的表副本上,通过使用ALTER TABLE尝试将表类型更改为另一种存储引擎。

由于很难得知为什么某事会出现崩溃,首先请检查用于其他方面的事项是否崩溃。请尝试采取下述措施:

mysqladmin shutdown停止mysqld服务器,从数据目录运行myisamchk --silent --force */*.MYI,检查所有的MyISAM表,并重启mysqld。这样,就能确保从干净的状态运行服务器。请参见第5章:数据库管理

使用“--log”选项启动mysqld,并根据写入日志的信息确定是否某些特殊的查询杀死了服务器。约95%的缺陷与特定的查询有关。正常情况下,这是服务器重启前日志文件中最够数个查询中的1个。请参见5.11.2节,“通用查询日志”。如果能够用特殊查询重复杀死MySQL,即使在发出查询前检查了所有表的情况下也同样,那么你就应能确定缺陷,并应提交关于该缺陷的缺陷报告。请参见1.7.1.3节,“如何通报缺陷和问题”

尝试提供一个测试范例,我们应能利用该范例重复问题。请参见E.1.6节,“如果出现表崩溃,请生成测试案例”

请在mysql-test目录下并根据MySQL基准进行测试。请参见27.1.2节,“MySQL测试套件”。它们能相当良好地测试MySQL。你也可以为基准测试增加代码,以模拟你的应用程序。基准测试可在源码分发版的sql-bench目录下找到,对于二进制分发版,可在MySQL安装目录下的sql-bench目录下找到。

尝试使用fork_big.pl脚本(它位于源码分发版的测试目录下)。

如果你将MySQL配置为调试模式,如果某事出错,可更为容易地搜集关于可能错误的信息。如果将MySQL配置为调试模式,可生成1个安全的内存分配程序,可使用它发现某些错误。此外,它还提供了很多输出,这类输出与出现的问题相关。在configure上使用“--with-debug”或“--with-debug=full”选项重新配置MySQL,然后再编译它。请参见E.1节,“调试MySQL服务器”

确保为你的操作系统应用了最新的补丁。

mysqld使用“--skip-external-locking”选项。在某些系统上,lockd锁定管理器不能正确工作,--skip-external-locking”选项通知mysqld不使用外部锁定。(这意味着,你不能在相同的数据目录上运行2mysqld服务器,如果使用myisamchk,必须谨慎。然而,尝试将该选项用作测试也是有益的)。

mysqld看上去正在运行但并未响应时,是否运行了mysqladmin -u root processlist?某些时候,即使你认为mysqld处于闲置状态时,实际情况并非如此。问题可能是因为所有连接均已使用,或存在某些内部锁定问题。即使在该情况下,mysqladmin -u root processlist通常能够进行连接,并能提供关于当前连接数以及其状态的有用信息。

在运行其他查询的同时,在单独的窗口中运行命令mysqladmin -i 5 statusmysqladmin -i 5 -r status,以生成统计信息。

尝试采用下述方法:

gdb(或另一个调试器)启动mysqld。请参见E.1.3节,“在gdb环境下调试mysqld

运行测试脚本。

3个较低层面上输出backtrace(向后跟踪)和局部变量。在gdb中,当mysqldgdb内崩溃时,可使用下述命令完成该任务:

backtrace
info local
up
info local
up
info local

使用gdb,你还能检查与info线程共存的线程,并切换至特定的线程N,其中,N是线程ID

尝试用Perl脚本模拟你的应用程序,强制MySQL崩溃或行为异常。

发送正常的缺陷报告。请参见1.7.1.3节,“如何通报缺陷和问题”。应比通常的报告更详细。由于MySQL是为很多人提供服务的,它可能因仅存在于你的计算机上的某事崩溃(例如,与你的特定系统库有关的错误)。

如果你遇到与包含动态长度行的表有关的问题,而且你仅使用VARCHAR(而不是BLOBTEXT列),可尝试用ALTER TABLE将所有VARCHAR列更改为CHAR列。这样,就会强制MySQL使用固定大小的行。固定大小的行占用的空间略多,但对损坏的容忍度更高。

目前的动态行代码在MySQL AB已使用多年,很少遇到问题,但从本质上看,动态长度行更倾向于出现错误,因此,不妨尝试采用该策略以查看它是否有帮助,这不失为一个好主意。

诊断问题时不要将你的服务器硬件排除在外。有缺陷的硬件能够导致数据损坏。对硬件进行故障诊断与排除操作时,尤其应注意RAM和硬盘驱动器。

A.4.3. MySQL处理磁盘满的方式

在本节中,介绍了MySQL响应磁盘满错误的方式(如“设备上无剩余空间”),以及响应超配额错误的方式(如“写入失败”或“达到了用户屏蔽限制”)。

本节介绍的内容与写入MyISAM表有关。它也适用于写入二进制日志文件和二进制索引文件,但对row”和“record”的应用应被视为“event”。

出现磁盘满状况时,MySQL将:

每分钟检查一次,查看是否有足够空间写入当前行。如果有足够空间,将继续,就像什么也未发生一样。

10分钟将1个条目写入日志文件,提醒磁盘满状况。

为了减轻问题,可采取下述措施:

要想继续,仅需有足够的磁盘空间以插入所有记录。

要想放弃线程,必须使用mysqladmin kill。下次检查磁盘时将放弃线程(1分钟)。

其他线程可能会正在等待导致磁盘满状况的表。如果有数个“已锁定”的线程,杀死正在磁盘满状况下等待的某一线程,以便允许其他线程继续。

对前述行为的例外是,当你使用REPAIR TABLEOPTIMIZE TABLE时,或当索引是在LOAD DATA INFILEALTER TABLE语句后、在批操作中创建的。所有这些语句能创建大的临时文件,如果保留这些文件,会导致系统其他部分出现大问题。如果在MySQL执行这类操作的同时磁盘已满,它将删除大的临时文件,并将表标注为崩溃。但对于ALTER TABLE例外,旧表保持不变。

A.4.4. MySQL将临时文件储存在哪里

MySQL使用环境变量TMPDIR的值作为保存临时文件的目录的路径名。如果未设置TMPDIRMySQL将使用系统的默认值,通常为/tmp/var/tmp/usr/tmp。如果包含临时文件目录的文件系统过小,可对mysqld使用“—tmpdir”选项,在具有足够空间的文件系统内指定1个目录。

MySQL 5.1中,“—tmpdir”选项可被设置为数个路径的列表,以循环方式使用。在Unix平台上,路径用冒号字符“:”隔开,在WindowsNetWareOS/2平台上,路径用分号字符“;”隔开。注意,为了有效分布负载,这些路径应位于不同的物理磁盘上,而不是位于相同磁盘的不同分区中。

如果MySQL服务器正作为复制从服务器使用,不应将“--tmpdir”设置为指向基于内存的文件系统的目录,或当服务器主机重启时将清空的目录。对于复制从服务器,需要在机器重启时仍保留一些临时文件,以便能够复制临时表或执行LOAD DATA INFILE操作。如果在服务器重启时丢失了临时文件目录下的文件,复制将失败。

MySQL会以隐含方式创建所有的临时文件。这样,就能确保中止mysqld时会删除所有临时文件。使用隐含文件的缺点在于,在临时文件目录所在的位置中,看不到占用了文件系统的大临时文件。

进行排序时(ORDER BYGROUP BY),MySQL通常会使用1个或多个临时文件。所需的最大磁盘空间由下述表达式决定:

(length of what is sorted + sizeof(row pointer))
* number of matched rows
* 2

row pointer”(行指针)的大小通常是4字节,但在以后,对于大的表,该值可能会增加。

对于某些SELECT查询,MySQL还会创建临时SQL表。它们不是隐含表,并具有SQL_*形式的名称。

ALTER TABLE会在与原始表目录相同的目录下创建临时表。

A.4.5. 如何保护或更改MySQL套接字文件/tmp/mysql.sock

对于服务器用来与本地客户端进行通信的Unix套接字文件,其默认位置是/tmp/mysql.sock。这有可能导致问题,原因在于,在某些版本的Unix上,任何人都能删除/tmp目录下的文件。

在大多数Unix版本中,可对/tmp目录进行保护,使得文件只能被其所有这或超级用户(根用户)删除。为此,以根用户身份登录,并使用下述命令在/tmp目录上设置粘着位:

shell> chmod +t /tmp

通过执行ls -ld /tmp,可检查是否设置了粘着位。如果最后一个许可字符是“t”,表明设置了粘着位。

另一种方法是改变服务器创建Unix套接字文件的位置。如果进行了这类操作,还应让客户端程序知道文件的位置。能够以多种不同方式指定文件位置:

在全局或局部选项文件中指定路径。例如,将下述行置于文件/etc/my.cnf中:

[mysqld]
socket=/path/to/socket
 
[client]
socket=/path/to/socket

请参见4.3.2节,“使用选项文件”

在运行客户端程序时,在命令行上为mysqld_safe指定--socket”选项。

MYSQL_UNIX_PORT环境变量设置为Unix套接字文件的路径。

重新从源码编译MySQL,以使用不同的默认Unix套接字文件位置。运行configure时,用“--with-unix-socket-path”选项定义文件路径。请参见2.8.2节,“典型配置选项

用下述命令连接服务器,能够测试新的套接字位置是否工作:

shell> mysqladmin --socket=/path/to/socket version

A.4.6. 时区问题

如果遇到与SELECT NOW()有关的问题,它返回GMT值而不是当地时间,就应通知服务器你的当前失去。如果UNIX_TIMESTAMP()返回错误值,上述方式同样适用。应为服务器所运行的环境进行这类设置,例如,在mysqld_safemysql.server中。请参见附录F:环境变量

也可以对mysqld_safe使用“--timezone=timezone_name”选项,为服务器设置失去。也可以在启动mysqld之前,通过设置TZ环境变量完成该设置。

--timezone”或TZ的允许值与系统有关。关于可接受的值,请参见操作系统文档。

A.5. 与查询有关的事宜

A.5.1. 搜索中的大小写敏感性

在默认情况下,MySQL搜索不区分大小写(但某些字符集始终区分大小写,如czech)。这意味着,如果你使用col_name LIKE 'a%'进行搜索,你将获得以Aa开始的所有列。如果打算使搜索区分大小写,请确保操作数之一具有区分大小写的或二进制校对。例如,如果你正在比较均适用latin1字符集的列和字符串,可使用COLLATE操作符,使1个操作数具有latin1_general_cslatin1_bin校对特性。例如:

col_name COLLATE latin1_general_cs LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_general_cs
col_name COLLATE latin1_bin LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_bin

如果希望总是以区分大小写的方式处理列,可使用区分大小写的或二进制校对声明它。请参见13.1.5节,“CREATE TABLE语法”

简单的比较操作(>=, >, =, <, <=, 排序和分组)基于每个字符的“排序值”。具有相同排序值的字符(如‘E, e,和‘Ac’)将被当作相同的写字符。

A.5.2. 使用DATE列方面的问题

DATE值的格式是'YYYY-MM-DD'按照标准的SQL,不允许其他格式。在UPDATE表达式以及SELECT语句的WHERE子句中应使用该格式。例如:

mysql> SELECT * FROM tbl_name WHERE date >= '2003-05-05';

为了方便,如果日期是在数值环境下使用的,MySQL会自动将日期转换为数值(反之亦然)。它还具有相当的智能,在更新时或在与TIMESTAMPDATEDATETIME列比较日期的WHERE子句中,允许宽松的字符串形式(宽松形式表示,任何标点字符均能用作各部分之间的分隔符。例如,'2004-08-15''2004#08#15'是等同的)。MySQL还能转换不含任何分隔符的字符串(如'20040815'),前体是它必须是有意义的日期。

使用<<==>=>、或BETWEEN操作符将DATETIMEDATETIMETIMESTAMP与常量字符串进行比较时,MySQL通常会将字符串转换为内部长整数,以便进行快速比较(以及略为“宽松”的字符串检查)。但是,该转换具有下述例外:

比较两列时

DATETIMEDATETIMETIMESTAMP列与表达式进行比较时

使用其他比较方法时,如INSTRCMP()

对于这些例外情形,会将对象转换为字符串并执行字符串比较,采用该方式进行比较。

为了保持安全,假定按字符串比较字符串,如果你打算比较临时值和字符串,将使用恰当的字符串函数。

对于特殊日期'0000-00-00',能够以'0000-00-00'形式保存和检索。在MyODBC中使用'0000-00-00'日期时,对于MyODBC 2.50.12或更高版本,该日期将被自动转换为NULL,这是因为ODBC不能处理这类日期。

由于MySQL能够执行前面所介绍的转换,下述语句均能正常工作:

mysql> INSERT INTO tbl_name (idate) VALUES (19970505);
mysql> INSERT INTO tbl_name (idate) VALUES ('19970505');
mysql> INSERT INTO tbl_name (idate) VALUES ('97-05-05');
mysql> INSERT INTO tbl_name (idate) VALUES ('1997.05.05');
mysql> INSERT INTO tbl_name (idate) VALUES ('1997 05 05');
mysql> INSERT INTO tbl_name (idate) VALUES ('0000-00-00');
 
mysql> SELECT idate FROM tbl_name WHERE idate >= '1997-05-05';
mysql> SELECT idate FROM tbl_name WHERE idate >= 19970505;
mysql> SELECT MOD(idate,100) FROM tbl_name WHERE idate >= 19970505;
mysql> SELECT idate FROM tbl_name WHERE idate >= '19970505';

但是,下述语句不能正常工作:

mysql> SELECT idate FROM tbl_name WHERE STRCMP(idate,'20030505')=0;

STRCMP()是一种字符串函数,它能将idate转换为'YYYY-MM-DD'格式的字符串,并执行字符串比较。它不能将'20030505'转换为日期'2003-05-05'并进行日期比较。

如果你正在使用ALLOW_INVALID_DATES SQL模式,MySQL允许以仅执行给定的有限检查方式保存日期:MySQL仅保证天位于131的范围内,月位于112的范围内。

这样就使得MySQL很适合于Web应用程序,其中,你能获得三个不同字段中的年、月、日值,也能准确保存用户插入的值(无日期验证)。

如果未使用NO_ZERO_IN_DATE SQL模式,“天”和“月”部分可能为0。如果你打算将生日保存在DATE列而且仅知道部分日期,它十分方便。

如果未使用NO_ZERO_DATE SQL模式,MySQL也允许你将'0000-00-00'保存为“伪日期”。在某些情况下,它比使用NULL值更方便。

如果无法将日期转换为任何合理值,“0”将保存在DATE列中,并被检索为'0000-00-00'。这是兼顾速度和便利性的事宜。我们认为,数据库服务器的职责是检索与你保存的日期相同的日期(即使在任何情况下,数据在逻辑上不正确也同样)。我们认为,对日期的检查应由应用程序而不是服务器负责。

如果你希望MySQL检查所有日期并仅接受合法日期(除非由IGNORE覆盖),应将sql_mode设置为"NO_ZERO_IN_DATE,NO_ZERO_DATE"

A.5.3. 与NULL值有关的问题

对于SQL的新手,NULL值的概念常常会造成混淆,他们常认为NULL是与空字符串''相同的事。情况并非如此。例如,下述语句是完全不同的:

mysql> INSERT INTO my_table (phone) VALUES (NULL);
mysql> INSERT INTO my_table (phone) VALUES ('');

这两条语句均会将值插入phone(电话)列,但第1条语句插入的是NULL值,第2条语句插入的是空字符串。第1种情况的含义可被解释为“电话号码未知”,而第2种情况的含义可被解释为“该人员没有电话,因此没有电话号码”。

为了进行NULL处理,可使用IS NULLIS NOT NULL操作符以及IFNULL()函数。

SQL中,NULL与任何其它值的比较(即使是NULL)永远不会为“真”。包含NULL的表达式总是会导出NULL值,除非在关于操作符的文档中以及表达式的函数中作了其他规定。下述示例中的所有列均返回NULL

mysql> SELECT NULL, 1+NULL, CONCAT('Invisible',NULL);

如果打算搜索列值为NULL的列,不能使用expr = NULL测试。下述语句不返回任何行,这是因为,对于任何表达式,expr = NULL永远不为

mysql> SELECT * FROM my_table WHERE phone = NULL;

要想查找NULL值,必须使用IS NULL测试。在下面的语句中,介绍了查找NULL电话号码和空电话号码的方式:

mysql> SELECT * FROM my_table WHERE phone IS NULL;
mysql> SELECT * FROM my_table WHERE phone = '';

更多信息和示例,请参见3.3.4.6节,“使用NULL值”

如果你正在使用MyISAMInnoDBBDB、或MEMORY存储引擎,能够在可能具有NULL值的列上增加1条索引。如不然,必须声明索引列为NOT NULL,而且不能将NULL插入到列中。

LOAD DATA INFILE读取数据时,对于空的或丢失的列,将用''更新它们。如果希望在列中具有NULL值,应在数据文件中使用\N。在某些情况下,也可以使用文字性单词“NULL”。请参见13.2.5 LOAD DATA INFILE语法”

使用DISTINCTGROUP BYORDER BY时,所有NULL值将被视为等同的。

使用ORDER BY时,首先将显示NULL值,如果指定了DESC按降序排列,NULL值将最后显示。

对于聚合(累计)函数,如COUNT()MIN()SUM(),将忽略NULL值。对此的例外是COUNT(*),它将计数行而不是单独的列值。例如,下述语句产生两个计数。首先计数表中的行数,其次计数age列中的非NULL值数目:

mysql> SELECT COUNT(*), COUNT(age) FROM person;

对于某些列类型,MySQL将对NULL值进行特殊处理。如果将NULL插入TIMESTAMP列,将插入当前日期和时间。如果将NULL插入具有AUTO_INCREMENT属性的整数列,将插入序列中的下一个编号。

A.5.4. 与列别名有关的问题

可以使用别名来引用GROUP BYORDER BYHAVING子句中的列。别名也能用于为列提供更好的名称:
SELECT SQRT(a*b) AS root FROM tbl_name GROUP BY root HAVING root > 0;
SELECT id, COUNT(*) AS cnt FROM tbl_name GROUP BY id HAVING cnt > 0;
SELECT id AS 'Customer identity' FROM tbl_name;

标准SQL不允许在WHERE子句中已用列别名。这是因为,执行WHERE代码时,可能尚未确定列值。例如,下述查询是非法的:

SELECT id, COUNT(*) AS cnt FROM tbl_name WHERE cnt > 0 GROUP BY id;

执行WHERE语句以确定哪些行应被包含在GROUP BY部分中,而HAVING用于确定应使用结果集中的哪些行。

A.5.5. 非事务表回滚失败

执行ROLLBACK(回滚)时,如果收到下述消息,表示事务中使用的1个或多个表不支持事务:

警告:某些更改的非事务性表不能被回滚。

这些非事务性表不受ROLLBACK语句的影响。

如果在事务中意外地混合了事务性表和非事务性表,导致该消息的最可能原因是,你认为本应是事务性的表实际上不是。如你试图使用mysqld服务器不支持的事务性存储引擎(或用启动选项禁止了它)创建表,就可能出现该情况。如果mysqld不支持存储引擎,它将以MyISAM表创建表,这是非事务性表。

可使用下述语句之一检查表的标类型:

SHOW TABLE STATUS LIKE 'tbl_name';
SHOW CREATE TABLE tbl_name;

请参见13.5.4.18节,“SHOW TABLE STATUS语法以及13.5.4.5节,“SHOW CREATE TABLE语法”

使用下述语句,可检查mysqld服务器支持的存储引擎:

SHOW ENGINES;

也可以使用下述语句,检查与你感兴趣的存储引擎有关的变量值:

SHOW VARIABLES LIKE 'have_%';

例如,要想确定InnoDB存储引擎是否可用,可检查have_innodb变量的值。

请参见13.5.4.8节,“SHOW ENGINES语法”13.5.4.21节,“SHOW VARIABLES语法”

A.5.6. 从相关表删除行

如果针对related_tableDELETE语句的总长度超过1MB(系统变量max_allowed_packet的默认值),应将其分为较小的部分,并执行多个DELETE语句。如果related_column是索引列,为每条语句指定1001000related_column值,或许能获得更快的DELETE速度。如果related_column不是索引列,速度与IN子句中的参量数目无关。

A.5.7. 解决与不匹配行有关的问题

如果有使用了很多表的复杂查询,但未返回任何行,应采用下述步骤找出什么出错:

EXPLAIN测试查询,以检查是否发现某事显然出错。请参见7.2.1节,“EXPLAIN语法(获取关于SELECT的信息)

仅选择在WHERE子句中使用的列。

从查询中1次删除1个表,直至返回了某些行为止。如果表很大,较好的主意是在查询中使用LIMIT 10

对于具有与上次从查询中删除的表匹配的行的列,发出SELECT查询。

如果将FLOATDOUBLE列与具有数值类型的数值进行比较,不能使用等式(=)比较。在大多数计算机语言中,该问题很常见,这是因为,并非所有的浮点值均能以准确的精度保存。在某些情况下,将FLOAT更改为DOUBLE可更正该问题。请参见A.5.8节,“与浮点比较有关的问题”

如果仍不能找出问题之所在,请创建能与显示问题的“mysql test < query.sql”一起运行的最小测试。通过使用mysqldump --quick db_name tbl_name_1 ... tbl_name_n > query.sql转储表,可创建测试文件。在编辑器中打开文件,删除某些插入的行(如果有超出演示问题所需的行),并在文件末尾添加SELECT语句。

通过执行下述命令,验证测试文件能演示问题:

shell> mysqladmin create test2
shell> mysql test2 < query.sql

使用mysqlbug将测试文件张贴到哟娜通用MySQL邮件列表。请参见1.7.1.1节,“The MySQL邮件列表”

A.5.8. 与浮点比较有关的问题

注意,下述部分主要与DOUBLEFLOAT列相关,原因在于浮点数的不准确本质。MySQL使用64位十进制数值的精度执行DECIMAL操作,当处理DECIMAL列时,应能解决大多数常见的不准确问题。

浮点数有时会导致混淆,这是因为它们无法以准确值保存在计算机体系结构中。你在屏幕上所看到的值通常不是数值的准确值。对于FLOATDOUBLE列类型,情况就是如此。DECIMAL列能保存具有准确精度的值,这是因为它们是由字符串表示的。

在下面的示例中,介绍了使用DOUBLE时的问题:

mysql> CREATE TABLE t1 (i INT, d1 DOUBLE, d2 DOUBLE);
mysql> INSERT INTO t1 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00),
    -> (2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40),
    -> (2, 30.40, 30.40), (3, 37.00, 7.40), (3, -29.60, 0.00),
    -> (4, 60.00, 15.40), (4, -10.60, 0.00), (4, -34.00, 0.00),
    -> (5, 33.00, 0.00), (5, -25.80, 0.00), (5, 0.00, 7.20),
    -> (6, 0.00, 0.00), (6, -51.40, 0.00);
 
mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b
    -> FROM t1 GROUP BY i HAVING a <> b;
 
+------+-------+------+
| i    | a     | b    |
+------+-------+------+
|    1 |  21.4 | 21.4 |
|    2 |  76.8 | 76.8 |
|    3 |   7.4 |  7.4 |
|    4 |  15.4 | 15.4 |
|    5 |   7.2 |  7.2 |
|    6 | -51.4 |    0 |
+------+-------+------+

结果是正确的。尽管前5个记录看上去不应能进行比较测试(ab的值看上去没有什么不同),但它们能进行比较,这是因为显示的数值间的差异在十分位左右,具体情况取决于计算机的体系结构。

如果列d1d2定义为DECIMAL而不是DOUBLESELECT查询的结果仅包含1行,即上面显示的最后1行。

A.6. 与优化器有关的事宜

MySQL采用了基于开销的优化器,以确定处理查询的最解方式。在很多情况下,MySQL能够计算最佳的可能查询计划,但在某些情况下,MySQL没有关于数据的足够信息,不得不就数据进行“有教养”的估测。

MySQL未能做“正确的”事时,可使用下述工具来帮助MySQL

使用EXPLAIN语句获取关于MySQL如何处理查询的信息。要想使用它,可在SELECT语句前添加关键字EXPLAIN

mysql> EXPLAIN SELECT * FROM t1, t2 WHERE t1.i = t2.i;

关于EXPLAIN的详细讨论,请参见7.2.1节,“EXPLAIN语法(获取关于SELECT的信息)

使用ANALYZE TABLE tbl_name,为已扫描的表更新键分配。请参见13.5.2.1节,“ANALYZE TABLE语法”

为已扫描的表使用FORCE INDEX,通知MySQL:与使用给定的索引相比,表扫描开销昂贵。请参见13.2.7节,“SELECT语法”

SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
WHERE t1.col_name=t2.col_name;

USE INDEXIGNORE INDEX也有一定的帮助。

关于全局和表级别的STRAIGHT_JOIN。请参见13.2.7节,“SELECT语法”

你可以调节全局或线程类系统变量。例如,用“--max-seeks-for-key=1000选项启动mysqld,或使用“SET max_seeks_for_key=1000”来通知优化器:假定任何表扫描均不会导致1000个以上的键搜索。请参见5.3.3节,“服务器系统变量”

A.7. 与表定义有关的事宜

A.7.1. 与ALTER TABLE有关的问题

ALTER TABLE将表更改为当前字符集。如果在执行ALTER TABLE操作期间遇到重复键错误,原因在于新的字符集将2个键映射到了相同值,或是表已损坏。在后一种情况下,应在表上运行REPAIR TABLE

如果ALTER TABLE失败并给出下述错误,问题可能是因为在ALTER TABLE操作的早期阶段出现MySQL崩溃,没有名为A-xxxB-xxx的旧表:

Error on rename of './database/name.frm'
to './database/B-xxx.frm' (Errcode: 17)

在该情况下,进入MySQL数据目录,并删除其名称为以A-B-开始的所有文件(或许你希望将它们移动到其他地方而不是删除它们)。

ALTER TABLE的工作方式如下:

  • 用请求的结构变化创建名为A-xxx的新表。
  • 将所有行从原始表拷贝到A-xxx
  • 将原始表重命名为B-xxx
  • A-xxx重命名为原始表的名称。
  • 删除B-xxx

如果在重命名操作中出错,MySQL将尝试撤销更改。如果错误很严重(尽管这不应出现),MySQL会将旧表保留为B-xxx。简单地在系统级别上重命名表文件,应能使数据复原。

如果在事务性表上使用ALTER TABLE,或正在使用WindowsOS/2操作系统,如果已在表上执行了LOCK TABLE操作,ALTER TABLE将对表执行解锁操作。这是因为InnoDB和这类操作系统不能撤销正在使用的表。

A.7.2. 如何更改表中的列顺序

首先,请考虑是否的确需要更改表中的列顺序。SQL的核心要点是从数据存储格式获取应用。总应指定检索数据的顺序。在下面的第1条语句中,以col_name1col_name2col_name3顺序返回列;在第2条语句中,以col_name1col_name3col_name2顺序返回列:

mysql> SELECT col_name1, col_name2, col_name3 FROM tbl_name;
mysql> SELECT col_name1, col_name3, col_name2 FROM tbl_name;

如果决定更改表列的顺序,可执行下述操作:

  1. 用具有新顺序的列创建新表。
  2. 执行该语句:
mysql> INSERT INTO new_table
    -> SELECT columns-in-new-order FROM old_table;
  1. 撤销或重命名old_table
  2. 将新表重命名为原始名称:
mysql> ALTER TABLE new_table RENAME old_table;

SELECT *十分适合于测试查询。但是,在应用程序中,永远不要依赖SELECT *的使用,不要依赖根据其位置检索列。如果添加、移动或删除了列,所返回的列的顺序和位置不会保持相同。对表结构的简单更改也会导致应用程序失败。

A.7.3. TEMPORARY TABLE问题

下面介绍了对使用TEMPORARY表的限制:

  • TEMPORARY表只能是HEAPISAMMyISAMMERGE、或InnoDB类型。
  • 在相同的查询中,不能引用TEMPORARY1次以上。例如,下例不能正常工作:
mysql> SELECT * FROM temp_table, temp_table AS t2;
错误1137:不能再次打开表:'temp_table'
  • SHOW TABLES语句不会列出TEMPORARY表。
  • 不能使用RENAME重命名TEMPORARY表。但能使用ALTER TABLE取而代之:
mysql> ALTER TABLE orig_name RENAME new_name;

A.8. MySQL中的已知事宜

在本节中,列出了当前MySQL版本中的已知事宜。

关于平台相关事宜的更多信息,请参见2.12节,“具体操作系统相关的注意事项”附录E:移植到其他系统中的安装和移植说明。

A.8.1. MySQL中的打开事宜

下面列出了已知问题,更正它们具有较高的优先级:

  • 如果将NULL值与使用ALL/ANY/SOME的子查询进行比较,而且子查询返回空的结果,比较操作会评估NULL的非标准结果而不是TRUEFALSE。在MySQL 5.1中将更正该问题。
  • 对于IN的线子查询优化不像“=”那样有效。
  • 即使使用了lower_case_table_names=2(允许MySQL记住数据库名和表名使用的大小写),对于函数DATABASE()或在各种日志内(在不区分大小写的系统上),MySQL也不会记住数据库名使用的大小写情况。
  • 在复制操作中,撤销FOREIGN KEY约束不工作,这是因为约束可能在从服务器上有另一个名称。
  • REPLACE(以及具有REPLACE选项的LOAD DATA)不会触发ON DELETE CASCADE
  • 如果未使用所有列而且仅使用DISTINCT列表中的列,在GROUP_CONCAT()中,DISTINCT不能与ORDER BY一起工作。
  • 如果1位用户拥有长时间运行的事务,而且另1位用户撤销了在事务中更新的某1表,那么在表用于事务本身之前,存在较小的机会,会在二进制日志中包含DROP TABLE命令。我们计划更正该问题,方法是让DROP TABLE命令等待,直至表未在任何事务中使用为止。
  • 将大的整数值(介于2632641之间)插入数值或字符串列时,它将作为负值插入,这是因为该数值是在有符号整数环境下评估的。
  • 如果服务器运行在不具备二进制日志功能的条件下,FLUSH TABLES WITH READ LOCK不能屏蔽COMMIT,执行完整备份时这可能会导致问题(表间的一致性问题)。
  • 在某些情况下,作用在BDB表上的ANALYZE TABLE会导致表不可用,直至重启mysqld为止。如果出现该情况,请在MySQL错误文件中查找下述形式的错误:
001207 22:07:56  bdb:  log_flush: LSN past current end-of-log
  • 在所有事务完成之前,不要在BDB表(正在其上运行多语句事务)上执行ALTER TABLE(可能会忽略事务)。
  • 对于正在使用INSERT DELAYED的表,在其上执行ANALYZE TABLEOPTIMIZE TABLEREPAIR TABLE时,可能会导致问题。
  • 在表上执行LOCK TABLE ...FLUSH TABLES ...时,不保证没有完成一半的事务。
  • BDB打开的速度相对较慢。如果你在数据库上有很多BDB表,如果未使用“-A”选项或正使用再混编功能,要想在数据库上使用mysql客户端,需要花费较长的时间。当你有大的表高速缓冲时,这点尤其明显。
  • 复制功能采用了查询级日志功能:主服务器将已执行的查询写入二进制日志。这是一种速度很快、简洁和有效的记录方法,在大多数情况下工作良好。

如果以特定的方式设计查询,使得数据更改是非决定性(通常不推荐,即使在复制之外也同样),主服务器和从服务器上的数据将变得不同。

例如:

  • 将0或NULL值插入AUTO_INCREMENT列中的CREATE ... SELECT或INSERT ... SELECT语句。
  • DELETE,如果从具有ON DELETE CASCADE属性的外键的表中删除行。
  • REPLACE ... SELECT、INSERT IGNORE ... SELECT,如果在插入的数据中具有重复键。

当且仅当前述查询没有保证决定行顺序的ORDER BY子句时。

例如,对于不具有ORDER BYINSERT ... SELECTSELECT可能会以不同的顺序返回行(它会导致具有不同等级的行,从而导致AUTO_INCREMENT列中的不同数值),具体情况取决于优化器在主服务器和从服务器上所作的选择。

在主服务器和从服务器上,查询将进行不同的优化,仅当:

  • 使用不同的存储引擎在主服务器上而不是从服务器上保存表。(能够在主服务器和从服务器上使用不同的存储引擎。例如,如果从服务器具有较少的可用磁盘空间,可以在主服务器上使用InnoDB,但在 从服务器桑使用MyISAM)。
  • 在主服务器和从服务器上,MySQL缓冲区大小是不同的(key_buffer_size等)。
  • 在主服务器和从服务器上运行不同的MySQL版本,版本间的优化器代码也不同。

该问题也会影响使用mysqlbinlog|mysql的数据库恢复。

避免该问题的最简单方法是,为前述的非决定性查询增加ORDER BY子句,以确保总是以相同的顺序保存或更改行。

在将来的MySQL版本中,需要时,我们将自动增加ORDER BY子句。

下面列出了已知的事宜,这些事宜将在恰当的时候更正:

  • 日志文件名基于服务器主机名(如果未使用启动选项指定文件名的话)。如果更改了主机名,你将不得不使用诸如“--log-bin=old_host_name-bin”等选下美国。另一种选择是重命名旧文件,以反映主机名变更情况(如果是二进制日志,需要编辑二进制日志索引文件,并更正binlog名称)。请参见5.3.1节,“mysqld命令行选项”
  • Mysqlbinlog不删除执行LOAD DATA INFILE命令后遗留的临时文件。请参见8.6节,“mysqlbinlog:用于处理二进制日志文件的实用工具”
  • RENAME不能与TEMPORARY表一起工作,也不能与MERGE表中使用的表一起工作。
  • 由于表定义文件的保存方式,不能在表名、列名或枚举中使用字符255CHAR(255))。按照安排,当我们实施了新的表定义格式文件时,将在5.1版中更正该问题。
  • 使用SET CHARACTER SET时,不能在数据库、表和列名中使用转换的字符。
  • 不能在LIKE ... ESCAPE中与ESCAPE一起使用_’或‘%’。
  • 如果你有1DECIMAL列,其中,相同的数值以不同的格式保存(例如,+01.001.0001.00),GROUP BY可能会将每个值当作不同的值。
  • 使用MIT-pthreads时,不能在另一个目录下创建服务器。这是因为它需要更改MIT-pthreads,我们不太会更正该问题。请参见2.8.5 MIT-pthreads注意事项”
  • GROUP BYORDER BYDISTINCT中,不能可靠地使用BLOBTEXT值。在这类情况下,与BLOB值进行比较时,仅使用最前的max_sort_length字节。max_sort_length的默认值是1024,可在服务器启动时或运行时更改它。
  • 数值计算是使用BIGINTDOUBLE(正常情况下均为64位长)进行的。你所能获得的精度取决于函数。通用规则是位函数是按BIGINT精度执行的,IFELT()是按BIGINTDOUBLE精度执行的,其余的函数是按DOUBLE精度执行的。对于除位字段外的其他数,如果大于63位(9223372036854775807),应避免使用无符号长long值。
  • 1个表中,最多能有255ENUMSET列。
  • MIN()MAX()以及其他聚合函数中,MySQL目前会根据其字符串值比较ENUMSET列,而不是根据字符串在集合中的相对位置。
  • mysqld_safe会将来自mysqld的所有消息再定向到mysqld日志。与之相关的1个问题是,如果你执行mysqladmin refresh关闭并再次打开日志,stdoutstderr仍会被重定向到旧的日志。如果你以广义方式使用“--log”应编辑mysqld_safe以记录到host_name.err而不是host_name.log,以便通过删除它并执行mysqladmin refresh,方便地收回为旧日志分配的空间。
  • UPDATE语句中,列从左向右更新。如果引用了已更新的列,你将得到更新值而不是原始值。例如,下述语句会将KEY增加2,而不是1
mysql> UPDATE tbl_name SET KEY=KEY+1,KEY=KEY+1;
  • 你可以在相同查询中引用多个临时表,但不能引用任何给定的临时表1次以上。例如,下述语句不能正常工作:
mysql> SELECT * FROM temp_table, temp_table AS t2;
错误1137:不能再次打开表:'temp_table'
  • 当你在联合操作中使用“隐含”列时,与未使用隐含列相比,优化器将以不同的方式处理DISTINCT。在联合操作中,隐含列将作为结果的组成部份计数(即使未显示),但在正常查询中,隐含列不参与DISTINCT比较。在以后,我们可能会更改该情况,在执行DISTINCT时不比较隐含列。

例如:

SELECT DISTINCT mp3id FROM band_downloads
       WHERE userid = 9 ORDER BY id DESC;

以及

SELECT DISTINCT band_downloads.mp3id
       FROM band_downloads,band_mp3
       WHERE band_downloads.userid = 9
       AND band_mp3.id = band_downloads.mp3id
       ORDER BY band_downloads.id DESC;

在第2种情况下,使用MySQL服务器3.23.x,可在结果集中获得2个等同行(这是因为,隐藏ID列中的值可能不同)。

注意,在结果集中,仅对不含ORDER BY列的查询才会出现该情况。

  • 如果在返回空集的查询上执行PROCEDURE,在某些情况下,PROCEDURE不转换列。
  • 创建具有MERGE类型的表时,不检查基本表是否具有兼容的类型。
  • 如果使用ALTER TABLEMERGE表中使用的表增加了UNIQUE索引,然后在MERGE表上增加了正常索引,如果在表中存在旧的、非UNIQUE键,对于这些表,键顺序是不同的。这是因为,ALTER TABLE会将UNIQUE索引放在正常索引之前,以便能尽早检测到重复的键。
 

这是MySQL参考手册的翻译版本,关于MySQL参考手册,请访问dev.mysql.com。原始参考手册为英文版,与英文版参考手册相比,本翻译版可能不是最新的。

站长教学网 免责声明