Mysql问题整理

PHPer 2018-01-18 605次浏览 0条评论 0 0 0
这里整理下Mysql使用中遇到的各种问题

这里整理下Mysql使用中遇到的各种问题...

登录 | 立即注册

更新于:2018-01-18 13:32:27

Yii2数据库报错-SQLSTATE[HY093]: Invalid parameter number: no parameters were bound

在提交表单时遇到了报错,在本地运行没问题,但是线上的项目只要提交内容稍多就会报错。PHP代码是一样的,于是想到是服务器配置那里和本地的不一样导致的,但是提示是没绑定,于是就像办法换了几种Yii2数据库操作的方式问题还在,最后在网上查,下面的分析基本是ok的,但是解决办法给的不好。尝试了后还是会报错。最后找到了解决的办法,我会在最后说。


网上的一篇分析:

地址:http://blog.csdn.net/cor_twi/article/details/53098...

并不是参数没有绑定,查看findAll()的源码

public function findAll($condition='',$params=array())
{
    Yii::trace(get_class($this).'.findAll()','system.db.ar.CActiveRecord');
    $criteria=$this->getCommandBuilder()->createCriteria($condition,$params);
    return $this->query($criteria,true);
}

query实际上执行的是:

$command=$this->getCommandBuilder()->createFindCommand($this->getTableSchema(),$criteria);
$command->queryAll();

而createFindCommand()
调用bindValue(),里面的代码如下:

$this->_statement->bindValue($name,$value,$this->_connection->getPdoType(gettype($value)));

连接已经timeout,失效了。bindValue无用。

queryAll()  --> queryInternal('fetchAll',PDO::FETCH_ASSOC,[]);
-->三次调用 queryInternalAll('fetchAll',PDO::FETCH_ASSOC,[])

简化的queryInternalAll如下:

private function queryInternalAll($method,$mode,$params=array())
{
    $params=array_merge($this->params,$params);   
    try
    {
        $this->prepare();
        @$this->_statement->execute();
        {
            $mode=(array)$mode;
            call_user_func_array(array($this->_statement, 'setFetchMode'), $mode);
            $result=$this->_statement->$method();
            $this->_statement->closeCursor();
        }
        return $result;
    }
    catch(Exception $e)
    {
        $errorInfo=$e instanceof PDOException ? $e->errorInfo : null;
        $message=$e->getMessage();

        if(YII_DEBUG)
            $message.='. The SQL statement executed was: '.$this->getText().$par;

        if(!empty($errorInfo) && (2006 == $errorInfo[1] || 2013 == $errorInfo[1])) {
            $this->_connection->setActive(false);
            $this->cancel();
        }
        throw new CDbException(Yii::t('yii','CDbCommand failed to execute the SQL statement: {error}',
            array('{error}'=>$message)),(int)$e->getCode(),$errorInfo);
    }
}

这样就看到了...

Mysql server has gone away 报错原因分析及解决办法

General error: 2006 MySQL server has gone away

Variable_nameValue
max_allowed_packet1024

看到这个1024,就是1KB,是真没想到,因为我配置文件里是设置成了256M,不知道怎么回事什么时候这个重置了,变成了1024,没有调用配置文件里的信息!下面是网上找到的一个不错的内容。分析的挺全面,这里引用下。因为自己就被网上的各种各样的内容坑了,里面多是说了一种情况,分析的不全面。

1. MySQL 服务宕了, 2.链接超时, 3.进程在server端被主动kill,4.Your SQL statement was too large.(SQL语句太大)



MySQL 服务宕了

判断是否属于这个原因的方法很简单,执行以下命令,查看mysql的运行时长

1
2
3
4
5
6
7
$ mysql -uroot -p -e "show global status like 'uptime';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime | 68928 |
+---------------+-------+
1 row in set (0.04 sec)


或者查看MySQL的报错日志,看看有没有重启的信息

$ tail /var/log/mysql/error.log
130101 22:22:30 InnoDB: Initializing buffer pool, size = 256.0M
130101 22:22:30 InnoDB: Completed initialization of buffer pool
130101 22:22:30 InnoDB: highest supported file format is Barracuda.
130101 22:22:30 InnoDB: 1.1.8 started; log sequence number 63444325509
130101 22:22:30 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
130101 22:22:30 [Note] - '127.0.0.1' resolves to '127.0.0.1';
130101 22:22:30 [Note] Server socket created on IP: '127.0.0.1'.
130101 22:22:30 [Note] Event Scheduler: Loaded 0 events
130101 22:22:30 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.28-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
如果uptime数值很大,表明mysql服务运行了很久了。说明最近服务没有重启过。

如果日志没有相关信息,也表名mysql服务最近没有重启过,可以继续检查下面几项内容。

2. 连接超时

如果程序使用的是长连接,则这种情况的可能性会比较大。

即,某个长连接很久没有新的请求发起,达到了server端的timeout,被server强行关闭。

此后再通过这个connection发起查询的时候,就会报错server has gone away

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
$ mysql -uroot -p -e "show global variables like '%timeout';"
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| connect_timeout | 30 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| wait_timeout | 28800 |
+----------------------------+----------+
mysql> SET SESSION wait_timeout=5;
## Wait 10 seconds
mysql> SELECT NOW();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 132361
Current database: *** NONE ***
+---------------------+
| NOW() |
+---------------------+
| 2013-01-02 11:31:15 |
+---------------------+
1 row in set (0.00 sec)



3. 进程在server端被主动kill

这种情况和情况2相似,只是发起者是DBA或者其他job。发现有长时间的慢查询执行kill xxx导致。
...

mysql 警告 could not be resolved: Name or service not known

问题: mysql DNS反解:skip-name-resolve


错误日志有类似警告:


1.120119 16:26:04 [Warning] IP address '192.168.1.10' could not be resolved: Name or service not known

2.120119 16:26:04 [Warning] IP address '192.168.1.14' could not be resolved: Name or service not known

3.120119 16:26:04 [Warning] IP address '192.168.1.17' could not be resolved: Name or service not known

通过show processlist发现大量类似如下的连接:

1.|592|unauthenticated user|192.168.1.10:35320|NULL|Connect| |login|NULL|

2.|593|unauthenticated user|192.168.1.14:35321|NULL|Connect| |login|NULL|

3.|594|unauthenticated user|192.168.1.17:35322|NULL|Connect| |login|NULL|

skip-name-resolve 参数的作用:不再进行反解析(ip不反解成域名),这样可以加快数据库的反应时间。

修改配置文件添加并需要重启:

代码如下:

[mysqld]
skip-name-resolve

其实就是在[mysqld]下面一行加入skip-name-resolve重启mysql服务就可以了。

下面是更加详细的解释:

现象:

程序连接mysql时,mysql的error.log里面提示:

[Warning] IP address '10.0.0.220' could not be resolved: Name or service not known

原因:

Mysql数据库服务器没有配置 /etc/hosts,也没有DNS服务,导致mysqld线程解析IP对应的主机名时,解析失败。

参考资料:

Mysql域名解析:

当一个新的客户端尝试跟mysqld创建连接时,mysqld产生一个新线程来处理这个请求。新线程会先检查请求建立连接的主机名是否在Mysql的主机名缓冲中,如果不在,线程会尝试去解析请求连接的主机名。

解析的逻辑如下:

a. Mysql线程通过gethostbyaddr()把获取的IP地址解析成主机名,然后通过gethostbyname()把获取的主机名解析成IP地址,保障主机名和IP地址对应关系的准确;

b. 如果操作系统支持使用安全进程的gethostbyaddr_r()和gethostbyname_r() 调用,Mysqld线程可以用它俩来优化主机名解析;

c. 如果操作系统不支持安全线程调用,Mysqld进程先做一个互斥锁,然后调用gethostbyaddr()和gethostbyname()解析主机名。此时,在第一个进程释放掉主机名缓冲池的主机名之前,其它进程无法再次解析这个主机名; <-------MySQL手册里面在此处说的host name ,意思应该是指同一个IP地址和对应的第一个主机名关系。

在启动mysqld进程是,可以使用 --skip-name-resolve 参数禁用DNS的主机名解析功能,禁用该功能后,在MySQL授权表里面,你只能使用IP地址。 ...

Mysql用特殊字符设置密码遇到的问题

Linux下Mysql设置密码的一些问题,

当设置密码中含义特殊字符,比如~!@#¥ 这样的,在你登陆mysql,提示输入密码时,这些密码前必须加入转义字符,好像是反斜杠,否则mysql是识别不到这些密码的,或者你在命令行里,直接连带密码一起写入。当然这样明文显示不安全。如果你要远程用工具登陆Mysql,比如phpmyadmin,那你就要在特殊字符前加转义字符。...

Mysql的函数substring使用注意事项

3、截取字符串
substring(str, pos)
substring(str, pos, length)
说明:substring(被截取字段,从第几位开始截取)
substring(被截取字段,从第几位开始截取,截取长度)
例:select substring(content,5) as abstract from my_content_t
select substring(content,5,200) as abstract from my_content_t

(注:如果位数是负数 如-5 则是从后倒数位数,到字符串结束或截取的长度) 这个函数...

MySQL 5.7内存使用分析

MySQL如何使用内存?

首先,介绍MySQL使用内存的一些方法:

1. 会话级别的内存消耗(连接私有内存):如sort_buffer_size等,每个会话都会开辟一个sort_buffer_size来进行排序操作。

2. 全局的内存消耗(共享内存):例如:innodb_buffer_pool_size等,全局共享的内存段。

MySQL内存计算器:http://www.mysqlcalculator.com

1ba04b1f93_normal.png

全局内存消耗(共享内存)相关参数

1)innodb_buffer_pool_size

使用过Innodb的同学都知道,这块内存是Innodb存储引擎最重要的内存,直接关系到MySQL的读写性能。与MyISAM表只缓存索引,数据寄望于OS系统缓存不同。Innodb一般都会关闭OS的缓存,所有读到数据页和索引都直接存在数据库层的innodb_buffer_pool中的。

InnoDB缓冲池缓存着InnoDB表,索引,及其它辅助缓冲器中的数据。为了实现大容量读取操作的效率,缓冲池被分成可以容纳多行的页。为了缓存管理的效率,缓冲池被实现为页面的链接列表,很少使用的数据使用LRU算法的变体进行页面替换。

缓冲池的大小对于系统性能很重要:

  • InnoDB使用malloc()方法在服务器启动时为整个缓冲池分配内存,通常,推荐innodb_buffer_pool_size值为系统内存的50%至75%。innodb_buffer_pool_size可以在服务器运行时动态配置。
  • 在具有大量内存的系统上,你可以通过将缓冲池划分为多个缓冲池实例来提高并发性,其innodb_buffer_pool_instances系统变量用来定义缓冲池实例的数量。
  • 缓冲池太小可能会导致过多的交换,因为页面从缓冲池中刷新后仅在短时间内可能再次需要。
  • 缓冲池太大可能会因为内存竞争而导致交换。

2)innodb_additional_mem_pool_size

主要用于存放MySQL内部的数据结构和Innodb的数据字典,所以大小主要与表的数量有关,表越多值越大。庆幸的是这个值是可变的,如果不够用的话,MySQL会向操作系统申请的。该值默认8M,AWS所有规格都是统一的2M,由于这个值可以动态申请,所以我觉得2M应该是满足需求的。

3)innodb_log_buffer_size

这个是redolog的缓冲区,为了提高性能,MySQL每次写日志都将日志先写到一个内存Buffer中,然后将Buffer按照innodb_flush_log_at_trx_commit的配置刷到disk上。目前,我们所有实例的innodb_flush_log_at_trx_commit设置为了1,即每次事务提交都会刷新Buffer到磁盘,保证已经提交的事务,redo是不会丢的。AWS该值也设的是1(为了保证不丢数据),这个值的大小主要影响到刷磁盘的次数,设置的过小,Buffer容易满,就会增加fsync的次数,设置过大,占用内存。该值默认是8M,AWS所有规格统一128M,我觉得目前每次提交都会刷buffer,所以除非有大事务的情况,一般buffer不太可能被占满,所以没必要开的很大, 8M应该是满足需求的。

4)key_buffer_size

MyISAM表的key缓存,这个只对MyISAM存储引擎有效,所以对于我们绝大多数使用Innodb的应用,无需关心。

5)query_cache_size

MySQL对于查询的结果会进行缓存来节省解析SQL、执行SQL的花销,query_cache是按照SQL语句的Hash值进行缓存的,同时SQL语句涉及的表发生更新,该缓存就会失效,所以这个缓存对于特定的读多更新少的库比较有用,对于绝大多数更新较多的库可能不是很适用,比较受限于应用场景,所以AWS也把这个缓存给关了。我觉得这个值默认应该关闭,根据需求调整。

会话级别的内存消耗(连接私有内存)

上面这些就是MySQL主要的共享内存空间,这些空间是在MySQL启动时就分配的,但是并不是立即使用的。MySQL还有一部分内存是在用户连接请求到达时动态分配的,即每个MySQL连接都单独一个缓存,这部分缓存主要包括:

1)read_buffer_size

每个线程连续扫描时为扫描的每个表分配的缓存区的大小(字节)。如果进行多次连续扫描,可能还需要增加该值。默认值为1311072,只有当查询需要的时候,才分配read_buffer_size指定的全部内存。

2)read_rnd_buffer_size

当以任意顺序读取行时,可以分配随机读取缓冲区,通过该缓冲区读取行,以避免磁盘寻找。read_rnd_buffer_size系统变量决定缓冲器大小。

3)sort_buffer_size

每一个要做排序的请求,都会分到一个sort_buffer_size大的缓存,用于做order by和group by的排序,如果设置的缓存大小无法满足需要,MySQL会将数据写入磁盘来完成排序。因为磁盘操作和内存操作不在一个数量级,所以sort_buffer_size对排序的性能影响很大。由于这部分缓存是即使不用这么大,也会全部分配的,所以对系统内存分配开销是比较大的,如果是希望扩大的话,建议在会话层设置,默认值2M,AWS也是2M。...

mysql 命令整理

更改密码

mysqladmin -uroot -p原密码 password 新密码...

【mysql】主键、普通索引、唯一索引和全文索引的比较

不过 FULLTEXT 用于搜索很长一篇文章的时候,效果最好。(待验证 20200404)

全文索引(适合在进行模糊查询的时候使用)MySQL从3.23.23版开始支持全文索引和全文检索。在MySQL中,全文索引的索引类型为FULLTEXT。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。它可以通过CREATE TABLE命令创建,也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集,通过ALTER TABLE(或者CREATE INDEX)命令创建全文索引要比把记录插入带有全文索引的空表更快。


MYSQL索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存。如果没有索引,执行查询时MySQL必须从第一个记录 开始扫描整个表的所有记录,直至找到符合要求的记录。表里面的记录数量越多,这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引,MySQL无 需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录,通过索引查找记录至少要比顺序扫描记录快100倍。


mysql在使用like查询中,能不能用到索引?在什么地方使用索引呢?

2

3

在使用like的时候,如果使用‘%%’,会不会用到索引呢?

EXPLAIN SELECT * FROM `user` WHERE username LIKE'%ptd_%';

  53d3c16347_normal.png

上面的结果是全表扫描,并没有使用到索引。


EXPLAIN SELECT * FROM `user` WHERE username LIKE'ptd_%';

0337fba731_normal.png

这个使用到了索引。


只使用左边一个%的查询:

EXPLAIN SELECT * FROM `user` WHERE username LIKE'%ptd_';

8cb75dc07f_normal.png  

在左边使用%时也是全表扫描,没有使用到索引。


综上,mysql在使用like查询的时候只有使用后面的%时,才会使用到索引。


总体分析

PRIMARY, INDEX, UNIQUE 这3种是一类PRIMARY 主键。 就是 唯一 且 不能为空。INDEX 索引,普通的UNIQUE 唯一索引。 不允许有重复。FULLTEXT 是全文索引,用于在一篇文章中,检索文本信息的。

MYSQL的索引类型:PRIMARY, INDEX,UNIQUE,FULLTEXT,SPAIAL 有什么区别?各适用于什么场合?举个例子来说,比如你在为某商场做一个会员卡的系统。这个系统有一个会员表有下列字段:会员编号 INT会员姓名 VARCHAR(10)会员身份证号码 VARCHAR(18)会员电话 VARCHAR(10)会员住址 VARCHAR(50)会员备注信息 TEXT那么这个 会员编号,作为主键,使用 PRIMARY会员姓名 如果要建索引的话,那么就是普通的 INDEX会员身份证号码 如果要建索引的话,那么可以选择 UNIQUE (唯一的,不允许重复)会员备注信息 , 如果需要建索引的话,可以选择 FULLTEXT,全文搜索。不过 FULLTEXT 用于搜索很长一篇文章的时候,效果最好。用在比较短的文本,如果就一两行字的,普通的 INDEX 也可以。...

没有接收到要导入的数据。可能是文件名没有提交,也可能是文件大小超出 PHP 限制。

网上推荐的方法是:

修改PHP上传文件大小限制的方法

1. 一般的文件上传,除非文件很小.就像一个5M的文件,很可能要超过一分钟才能上传完.

但在php中,默认的该页最久执行时间为 30 秒.就是说超过30秒,该脚本就停止执行.

这就导致出现 无法打开网页的情况.这时我们可以修改 max_execution_time

在php.ini里查找

max_execution_time

默认是30秒.改为

max_execution_time = 0

0表示没有限制

2. 修改 post_max_size 设定 POST 数据所允许的最大大小。此设定也影响到文件上传。

php默认的post_max_size 为2M.如果 POST 数据尺寸大于 post_max_size $_POST 和 $_FILES superglobals 便会为空.

查找 post_max_size .改为...

    您需要登录后才可以评论。 登录 | 立即注册
    相关内容

    使用Yii2遇到的问题整理

    Yii的东西很多,学习和使用的时候遇到了各种各样的问题,这里记录整理下,方便大家分享。composer安...

    Yii2用composer更新时遇到的错误

    Yii2 用composer update 时提示'git' 不是内部或外部命令,也

    Yii2​用composer安装kartik-v/yii2-mpdf时报错,成功解决后,

    使用Yii2的setFlash和bootstrap.min.js遇到的问题,bootstrap.min.js的bug?

    Yii2的action不支持大小写吗?其实是支持的

    composer install 使用tips-网上找的composer install的使用技巧方法

    这里专门开个帖子用来整理采集遇到的问题

    由于老哥采集遇到了很多问题,这里做个整理 2020406

    Linux中使用curl命令访问https站点4种常见错误和解决方法

    使用 curl 进行 ssl 认证 -文章是百度搜curl.cainfo找到的

    网上之前找的封装php curl的类,小巧且实用,用了挺久

    采集的时候把目标网页的内容输出到页面调试的问题

    vps相关问题

    vps相关问题,这里记录下

    国外VPS性能比较

    国外VPS网络状况比较

    不好用的VPS

    推荐内容

    怎样使用V2Ray代理和SSTap玩如魔兽世界/绝地求生/LOL台...

    在网上找的ss+SSTap的方式都不能通过SSTap的链接测试。最后找到了v2ray+SSTap的方式。 注意事项,首先单独有v2ray看能不能正常上网。另外加速时要v2ray和SST...

    使用V2Ray的mKCP协议加速游戏

    当前脚本已发布新版本,地址: https://github.com/kuoruan/shell-scripts/raw/master/kcptun/kcptun.sh 旧仓库已废...

    v2rayN已停止工作

    要安装.NET Framework 4.6 或者更高版本

    超省心游戏加速:Wireguard+udp加速(CentOS版)--(

    Wireguard+udpspeeder+udp2raw游戏加速方案 ---------------------------------------错误报告及解决-----------...

    wireguard+udpspeeder+udp2raw多用户配置

    Wireguard+udpspeeder+udp2raw游戏加速方案改进版-实测有效

    解决'nmake' 不是内部或外部命令,也不是可运行的程序

    在用gifsicle时,需要在其src目录下使用 nmake -f Makefile.w32 命令,报错 'nmake' 不是内部或外部命令,也不是可运行的程序 或批处理文件。 于是网...