InnoDB: Error: Table "mysql"."innodb_table_stats" not found.

采菊篱下 回复了问题 • 2 人关注 • 1 个回复 • 63 次浏览 • 2017-07-18 22:02 • 来自相关话题

MySQL之BLGC介绍

Rock 发表了文章 • 0 个评论 • 58 次浏览 • 2017-07-08 23:51 • 来自相关话题

一、组提交介绍

1.1 什么是组提交
Binary Log Group Commit 即二进制日志组提交。这是 MySQL5.6 版本中引进的一个新的特性。为什么需要引进这个特性呢?我们知道当我们把 MySQL 的 binlog 开启的时候,MySQL 会将每个事务的操作都记录到 binlog 中,方便我们使用 binlog 来完成复制或者恢复操作。可是需要调用 fsync() 才能将缓存中被更改的 binlog 真正的写到磁盘上,保证数据的持久化。但是这是一个从内存写到磁盘的过程,I/O 比较慢。如果每次事务提交都执行一遍 fsync() 将 binlog 持久化落盘到磁盘的话,效率很低。于是就想,能不能等几个事务的 binlog 一起调用一次 fsync(),一次性落盘。减少 fsync() 的次数,从而提高效率。这就是二进制日志组提交的概念。
 

二、两阶段提交

2.1 为什么需要二阶段提交
我们知道在 MySQL 中不仅仅有 binlog,还有 redo log 和 undo log 。binlog 用来记录每个事务的操作信息,redo 是在数据库宕机恢复时使用,用来恢复数据库数据,undo 用来回滚还未被提交的数据。binlog 是在数据库 Server 层产生的,即它会记录所有存储引擎中事务的操作,而 redo 是 InnoDB 存储引擎特有的日志。

在事务提交的时候,我们需要先写入二进制日志,再写 InnoDB 存储引擎的 redo。并且要求二进制日志和 redo 要么都写,要么都不写。不然可能会出现这样的情况:在主从复制的环境下,master 提交了一个事务,先写了二进制日志,但是在要写 InnoDB 存储引擎的时候,数据库发生了宕机,此时 binlog 又已经被 slave 接收到了,slave 会执行这个事务,但是实际 master 上并没有这个事务。这就会导致主从数据的不一致。所以我们引入了二阶段提交来解决这个问题,即将写 binlog 操作个 InnoDB 提交操作通过事务变成原子的。
 
2.2 什么是二阶段提交
所谓的二阶段提交就是,我在事务提交的时候,确保先将 binlog 写入,然后再到数据引擎层提交,并且这两个操作是原子的。在 MySQL 中用内部的 XA 事务来完成,即将这两个操作包装成一个事务的概念。




上图表示了二阶段提交的过程。当一个会话中的某一事务 COMMIT 的时候,进去二阶段提交的过程。首先数据库先去协调 Server 层和 Engine,询问是否都可以开始写日志,这个过程就是图中的的 prepare 阶段。协调好两层之间的关系,Server 层和 Engine 层都表示可以写日志,这时候进入下一个过程。

第二个过程就是写 binlog 的过程,先把 binlog 写到内存中,然后调用 fsync() 将 binlog 从内存写到磁盘上。

第三个过程就是在存储引擎层提交的过程,将真实修改的数据提交到数据库中。当这一步完成才最终返回给会话一个 COMMIT 成功的信号。

这整个过程就是二阶段提交的过程,如果在 fsync() 之前数据库 crash 了,重启之后数据将会被回滚,若在 fsync() 之后 crash,则会进行重做操作。通过二阶段提交的方式就保证了存储引擎与二进制日志保持一致。
 

三、三阶段提交

3.1 为什么需要三阶段提交
上面的二阶段提交是针对单一事务提交时候的操作顺序,下面我们来看看当多个事务并发的时候会是什么样的一个情况。




现在有T1、T2、T3 三个事务需要执行,从图中可以看到数据在 fsync() 之前,三个事务已经写入到了 binlog 中,通过 fsync() 操作将 binlog 刷到磁盘。之后先是 T2 COMMIT,将数据更改更新到存储引擎层,接着是 T3 COMMIT,将数据更新到存储引擎层。这时候我们做了一个热备份的操作,有多种方式进行数据库的热备份,比如:XtraBackup等。这时候就会发生错误。会发生什么错误,我们需要先了解一下 XtraBackup 等热备工具的备份原理。

XtraBackup备份原理:直接拷贝数据库文件,并且记录下当前二进制日志中已经提交的最后一个事务标记。在新的数据库实例上完成 recovery 操作。

了解完备份原理之后,我们就可以想到上述情况下做热备会出现什么情况。因为 T2、T3 已经提交,所以备份的时候会记录下 T3 是最后一个提交的事务,会认为 T3 之前的事务都是已经提交的,由于是直接拷贝数据库文件,可以看到 T1 事务的数据还没有提交到存储引擎层,所以备份数据中还并没有 T1 的数据。如果新的数据库是用来做主从复制的话,change master to 会指向二进制日志中 T3 的位置,从 T3 事务开始往后进行复制,这样一来 T1 事务的数据就这样没了。产生这个问题的主要原因就是:事务写入二进制日志的顺序与事务在存储引擎层提交的顺序不一致。

为了解决这个问题,MySQL 引入了 prepare_commit_mutext 的机制,当事务提交的时候,需要先获得 prepare_commit_mutext 这个锁。有了这个锁就可以保证事务写入二进制日志的顺序与事务在存储引擎层提交的顺序一致。




但是这样一来,从图中我们也可以看到,原先是并发的事务,又变成了串行的,效率又变低了。只要是问题,必然存在解决方法。于是三阶段提交就出现了。
 
 
3.2 什么是三阶段提交
三阶段提交,顾名思义有三个阶段: Flush 阶段、sync 阶段、commit 阶段。分别对应的就是二进制日志写内存的阶段、二进制日志刷盘的阶段、事务提交到存储引擎层的阶段。
 




每个阶段都有 leader、follower 两种角色。当一个事务进入三个阶段中的某一个阶段,如果发现这个阶段中的队列为空,那么这个事务就会成为 leader 的角色,之后进入同一阶段的事务,发现这个阶段的队列中已经有事务存在了,那就变成 follower 角色。leader 角色的任务是安排当前阶段队列中的事务按顺序执行,并且带领队列中所有的事务进入下一个阶段。当 leader 带领队列中的事务进入下一阶段的时候,如果发现下一阶段中已经有事务存在(即下一阶段已有 leader 存在),新来的 leader 自动变成 follower 角色。

三阶段提交在每个阶段都控制了事务的顺序,从而也就控制了事务执行的整体顺序。解决了 prepare_commit_mutex 锁导致的问题,事务可以并发的执行。

参考:http://blog.itpub.net/28218939/viewspace-1975809/
           http://blog.itpub.net/28218939/viewspace-1975822/
http://mysqlmusings.blogspot.jp/2012/06/binary-log-group-commit-in-mysql-56.html   查看全部


一、组提交介绍


1.1 什么是组提交
Binary Log Group Commit 即二进制日志组提交。这是 MySQL5.6 版本中引进的一个新的特性。为什么需要引进这个特性呢?我们知道当我们把 MySQL 的 binlog 开启的时候,MySQL 会将每个事务的操作都记录到 binlog 中,方便我们使用 binlog 来完成复制或者恢复操作。可是需要调用 fsync() 才能将缓存中被更改的 binlog 真正的写到磁盘上,保证数据的持久化。但是这是一个从内存写到磁盘的过程,I/O 比较慢。如果每次事务提交都执行一遍 fsync() 将 binlog 持久化落盘到磁盘的话,效率很低。于是就想,能不能等几个事务的 binlog 一起调用一次 fsync(),一次性落盘。减少 fsync() 的次数,从而提高效率。这就是二进制日志组提交的概念。
 


二、两阶段提交


2.1 为什么需要二阶段提交
我们知道在 MySQL 中不仅仅有 binlog,还有 redo log 和 undo log 。binlog 用来记录每个事务的操作信息,redo 是在数据库宕机恢复时使用,用来恢复数据库数据,undo 用来回滚还未被提交的数据。binlog 是在数据库 Server 层产生的,即它会记录所有存储引擎中事务的操作,而 redo 是 InnoDB 存储引擎特有的日志。

在事务提交的时候,我们需要先写入二进制日志,再写 InnoDB 存储引擎的 redo。并且要求二进制日志和 redo 要么都写,要么都不写。不然可能会出现这样的情况:在主从复制的环境下,master 提交了一个事务,先写了二进制日志,但是在要写 InnoDB 存储引擎的时候,数据库发生了宕机,此时 binlog 又已经被 slave 接收到了,slave 会执行这个事务,但是实际 master 上并没有这个事务。这就会导致主从数据的不一致。所以我们引入了二阶段提交来解决这个问题,即将写 binlog 操作个 InnoDB 提交操作通过事务变成原子的。
 
2.2 什么是二阶段提交
所谓的二阶段提交就是,我在事务提交的时候,确保先将 binlog 写入,然后再到数据引擎层提交,并且这两个操作是原子的。在 MySQL 中用内部的 XA 事务来完成,即将这两个操作包装成一个事务的概念。
twocommit.png

上图表示了二阶段提交的过程。当一个会话中的某一事务 COMMIT 的时候,进去二阶段提交的过程。首先数据库先去协调 Server 层和 Engine,询问是否都可以开始写日志,这个过程就是图中的的 prepare 阶段。协调好两层之间的关系,Server 层和 Engine 层都表示可以写日志,这时候进入下一个过程。

第二个过程就是写 binlog 的过程,先把 binlog 写到内存中,然后调用 fsync() 将 binlog 从内存写到磁盘上。

第三个过程就是在存储引擎层提交的过程,将真实修改的数据提交到数据库中。当这一步完成才最终返回给会话一个 COMMIT 成功的信号。

这整个过程就是二阶段提交的过程,如果在 fsync() 之前数据库 crash 了,重启之后数据将会被回滚,若在 fsync() 之后 crash,则会进行重做操作。通过二阶段提交的方式就保证了存储引擎与二进制日志保持一致
 


三、三阶段提交


3.1 为什么需要三阶段提交
上面的二阶段提交是针对单一事务提交时候的操作顺序,下面我们来看看当多个事务并发的时候会是什么样的一个情况。
t3commit.png

现在有T1、T2、T3 三个事务需要执行,从图中可以看到数据在 fsync() 之前,三个事务已经写入到了 binlog 中,通过 fsync() 操作将 binlog 刷到磁盘。之后先是 T2 COMMIT,将数据更改更新到存储引擎层,接着是 T3 COMMIT,将数据更新到存储引擎层。这时候我们做了一个热备份的操作,有多种方式进行数据库的热备份,比如:XtraBackup等。这时候就会发生错误。会发生什么错误,我们需要先了解一下 XtraBackup 等热备工具的备份原理。

XtraBackup备份原理:直接拷贝数据库文件,并且记录下当前二进制日志中已经提交的最后一个事务标记。在新的数据库实例上完成 recovery 操作。

了解完备份原理之后,我们就可以想到上述情况下做热备会出现什么情况。因为 T2、T3 已经提交,所以备份的时候会记录下 T3 是最后一个提交的事务,会认为 T3 之前的事务都是已经提交的,由于是直接拷贝数据库文件,可以看到 T1 事务的数据还没有提交到存储引擎层,所以备份数据中还并没有 T1 的数据。如果新的数据库是用来做主从复制的话,change master to 会指向二进制日志中 T3 的位置,从 T3 事务开始往后进行复制,这样一来 T1 事务的数据就这样没了。产生这个问题的主要原因就是:事务写入二进制日志的顺序与事务在存储引擎层提交的顺序不一致。

为了解决这个问题,MySQL 引入了 prepare_commit_mutext 的机制,当事务提交的时候,需要先获得 prepare_commit_mutext 这个锁。有了这个锁就可以保证事务写入二进制日志的顺序与事务在存储引擎层提交的顺序一致。
3tcommit.png

但是这样一来,从图中我们也可以看到,原先是并发的事务,又变成了串行的,效率又变低了。只要是问题,必然存在解决方法。于是三阶段提交就出现了。
 
 
3.2 什么是三阶段提交
三阶段提交,顾名思义有三个阶段: Flush 阶段、sync 阶段、commit 阶段。分别对应的就是二进制日志写内存的阶段、二进制日志刷盘的阶段、事务提交到存储引擎层的阶段。
 
3commit.png

每个阶段都有 leader、follower 两种角色。当一个事务进入三个阶段中的某一个阶段,如果发现这个阶段中的队列为空,那么这个事务就会成为 leader 的角色,之后进入同一阶段的事务,发现这个阶段的队列中已经有事务存在了,那就变成 follower 角色。leader 角色的任务是安排当前阶段队列中的事务按顺序执行,并且带领队列中所有的事务进入下一个阶段。当 leader 带领队列中的事务进入下一阶段的时候,如果发现下一阶段中已经有事务存在(即下一阶段已有 leader 存在),新来的 leader 自动变成 follower 角色。

三阶段提交在每个阶段都控制了事务的顺序,从而也就控制了事务执行的整体顺序。解决了 prepare_commit_mutex 锁导致的问题,事务可以并发的执行。


参考:http://blog.itpub.net/28218939/viewspace-1975809/
           http://blog.itpub.net/28218939/viewspace-1975822/
http://mysqlmusings.blogspot.jp/2012/06/binary-log-group-commit-in-mysql-56.html  


腾讯云正式成为MariaDB基金会白金会员

图灵之歌 发表了文章 • 0 个评论 • 139 次浏览 • 2017-06-16 16:35 • 来自相关话题

6月16日,全球开源组织MariaDB基金会宣布,腾讯云正式成为MariaDB基金会白金会员,这是基金会最高级别会员。这也是腾讯云继上个月加入CNCF基金会和Linux基金会后,在开源界的又一项新动作,意味腾讯云在开源领域的步伐正在不断深入,从IaaS的开源进入到PaaS的开源。
 


腾讯云将输出腾讯在MariaDB数据库上的经验和技术。腾讯云专家工程师程彬加入基金会成员组,成为全球仅有的7位官方成员之一。程彬负责腾讯云数据库CDB以及内核TXSQL的研发,专注于数据库和分布式存储领域相关技术和产品。他将代表腾讯云的数据库团队参加基金会每周的技术会议,提供MariaDB数据库的意见和建议,并在内部推动腾讯云MariaDB的开源进程,推动MariaDB在中国的开源工作。  
 
MariaDB基金会主席高度评价腾讯云
 
MariaDB基金会主席 Otto Kekäläinen表示:“MariaDB是未来的IT基础设施的一个基本组成部分,腾讯云在这个领域不断的思考和布局,具备锐意开创未来的主宰领导力。”他同时指出,强大的开源文化生长在中国以及亚洲,基金会的目标是将来在亚洲发起更多的活动。我们下一个MariaDB 开发者大会将在中国深圳举行。
 
MariaDB基金会官方同时评价,腾讯云的加入为基金会的发展提供强有力的资源支持,保障MariaDB开源生态的良好运转,为不断成长的用户和开发者提供服务。
 
MariaDB是由MySQL之父Michael Widenius于2009年开创的一个MySQL分支,在MySQL被商业企业收购后,为保证行业仍有开源可用的兼容MySQL的分支可用,Michael Widenius在创立了 MariaDB的同时,还成立了非赢利组织 MariaDB 基金会为MariaDB 项目、用户和开发者社区提供基础架构支持。目前MariaDB是发展最快的MySQL分支版本,新版本发布速度已经超过了商用的MySQL版本,在开源数据库领域拥有较强的技术影响力。
 
腾讯云将重点开源MariaDB内核积累
 
在腾讯内部,一直有一个数据库内核团队,在MySQL和MariaDB基础上之上做出改进优化,重点排出一些平时难以触发的bug,并在此基础上打造出了腾讯云的数据库产品。
 
在参与MySQL和MariaDB优化的过程中,腾讯云完成了主备节点锁拆分、动态调整各种级别复制参数,以及binlog写优化等;发现并解决了刷脏死锁引起的 innodb 600S crash 问题、memory barrier 引起的 hang 问题以及字符串问题等影响数据库稳定的问题。
 
未来,腾讯云还将投入更多精力到内核研发上,并会合并之前的一些内部稳定使用功能发布开源版本,尤其是适合云环境的功能的开源版本,为开源组织贡献自己的一份力量。
 
腾讯云深度拥抱全球开源生态圈
 
近年来,腾讯云大力拥抱开源事业。今年5月,腾讯云以金牌会员的身份正式加入CNCF基金会,为CNCF开源社区开放自身的Kubernetes相关特性,释放自身在容器服务、KVM虚拟化等方面的优势积累。同时基于腾讯云在Linux领域的积极贡献,腾讯云获CNCF基金会邀请加入Linux基金会。
 
此次腾讯云加入MariaDB基金会,将在数据库内核研发和功能开发方面开放助力开源,与国际的数据库专家团体建立更密切的联系,推动开放更多的技术开源。
 
未来腾讯云继续加大在开源领域的步伐,深度拥抱全球开源生态圈,从产品出发,基于大量用户在产品使用的感受和腾讯云的服务实践,将有价值的特性反馈给社区,与社区一起完善相关特性,同时从社区获得广泛的用户反馈,再次回到产品,提升腾讯云的产品体验。
 
 
延伸阅读:
腾讯云加入MariaDB基金会的官方消息:
https://mariadb.org/tencent-cloud-becomes-platinum-sponsor-mariadb-foundation/ 查看全部
6月16日,全球开源组织MariaDB基金会宣布,腾讯云正式成为MariaDB基金会白金会员,这是基金会最高级别会员。这也是腾讯云继上个月加入CNCF基金会和Linux基金会后,在开源界的又一项新动作,意味腾讯云在开源领域的步伐正在不断深入,从IaaS的开源进入到PaaS的开源。
 


腾讯云将输出腾讯在MariaDB数据库上的经验和技术。腾讯云专家工程师程彬加入基金会成员组,成为全球仅有的7位官方成员之一。程彬负责腾讯云数据库CDB以及内核TXSQL的研发,专注于数据库和分布式存储领域相关技术和产品。他将代表腾讯云的数据库团队参加基金会每周的技术会议,提供MariaDB数据库的意见和建议,并在内部推动腾讯云MariaDB的开源进程,推动MariaDB在中国的开源工作。  
 
MariaDB基金会主席高度评价腾讯云
 
MariaDB基金会主席 Otto Kekäläinen表示:“MariaDB是未来的IT基础设施的一个基本组成部分,腾讯云在这个领域不断的思考和布局,具备锐意开创未来的主宰领导力。”他同时指出,强大的开源文化生长在中国以及亚洲,基金会的目标是将来在亚洲发起更多的活动。我们下一个MariaDB 开发者大会将在中国深圳举行。
 
MariaDB基金会官方同时评价,腾讯云的加入为基金会的发展提供强有力的资源支持,保障MariaDB开源生态的良好运转,为不断成长的用户和开发者提供服务。
 
MariaDB是由MySQL之父Michael Widenius于2009年开创的一个MySQL分支,在MySQL被商业企业收购后,为保证行业仍有开源可用的兼容MySQL的分支可用,Michael Widenius在创立了 MariaDB的同时,还成立了非赢利组织 MariaDB 基金会为MariaDB 项目、用户和开发者社区提供基础架构支持。目前MariaDB是发展最快的MySQL分支版本,新版本发布速度已经超过了商用的MySQL版本,在开源数据库领域拥有较强的技术影响力。
 
腾讯云将重点开源MariaDB内核积累
 
在腾讯内部,一直有一个数据库内核团队,在MySQL和MariaDB基础上之上做出改进优化,重点排出一些平时难以触发的bug,并在此基础上打造出了腾讯云的数据库产品。
 
在参与MySQL和MariaDB优化的过程中,腾讯云完成了主备节点锁拆分、动态调整各种级别复制参数,以及binlog写优化等;发现并解决了刷脏死锁引起的 innodb 600S crash 问题、memory barrier 引起的 hang 问题以及字符串问题等影响数据库稳定的问题。
 
未来,腾讯云还将投入更多精力到内核研发上,并会合并之前的一些内部稳定使用功能发布开源版本,尤其是适合云环境的功能的开源版本,为开源组织贡献自己的一份力量。
 
腾讯云深度拥抱全球开源生态圈
 
近年来,腾讯云大力拥抱开源事业。今年5月,腾讯云以金牌会员的身份正式加入CNCF基金会,为CNCF开源社区开放自身的Kubernetes相关特性,释放自身在容器服务、KVM虚拟化等方面的优势积累。同时基于腾讯云在Linux领域的积极贡献,腾讯云获CNCF基金会邀请加入Linux基金会。
 
此次腾讯云加入MariaDB基金会,将在数据库内核研发和功能开发方面开放助力开源,与国际的数据库专家团体建立更密切的联系,推动开放更多的技术开源。
 
未来腾讯云继续加大在开源领域的步伐,深度拥抱全球开源生态圈,从产品出发,基于大量用户在产品使用的感受和腾讯云的服务实践,将有价值的特性反馈给社区,与社区一起完善相关特性,同时从社区获得广泛的用户反馈,再次回到产品,提升腾讯云的产品体验。
 
 
延伸阅读:
腾讯云加入MariaDB基金会的官方消息:
https://mariadb.org/tencent-cloud-becomes-platinum-sponsor-mariadb-foundation/

MYSQL的不同SQL模式解析

chris 发表了文章 • 0 个评论 • 191 次浏览 • 2017-05-15 23:05 • 来自相关话题

一、Mysql SQL Mode简介

通常来说MySQL服务器能够工作在不同的SQL模式下,并能针对不同的客户端以不同的方式应用这些模式。这样,应用程序就能对服务器操作进行量身定制以满足自己的需求。
 
这类模式定义了MySQL应支持的SQL语法,以及应该在数据上执行何种确认检查。这样,就能在众多不同的环境下、与其他数据库服务器一起更容易地使用MySQL。
 
可以使用" --sql-mode="modes" "选项,通过启动mysqld来设置默认的SQL模式。而从MySQL 4.1开始,也能在启动之后,使用SET [SESSION|GLOBAL] sql_mode='modes'语句,通过设置sql_mode变量更改其模式。
 
通常在linux下安装完mysql后,其默认的sql-mode值是空,在这种情形下mysql执行的是一种不严格的检查,例如日期字段可以插入'0000-00-00 00:00:00'这样的值,还有如果要插入的字段长度超过列定义的长度,那么mysql不会终止操作,而是会自动截断后面的字符继续插入操作,如下例:
mysql> create table t1 (c1 char(3));
mysql> insert into t1 values('abcd');
mysql> select * from t1;
+------+
| c1 |
+------+
| abc |
+------+
1 row in set (0.00 sec)我们发现插入的字符被自动截断了,但是如果我们本意希望如果长度超过限制就报错,那么我们可以设置sql_mode为STRICT_TRANS_TABLES,如下:
mysql> set session sql_mode='STRICT_TRANS_TABLES'这样我们再执行同样的操作,mysql就会告诉我们插入的值太长,操作被终止,如下:
mysql> insert into t1 values('abcd');
ERROR 1406 (22001): Data too long for column 'c1' at row 1
经常使用的sql_mode值:




说明:如果把sql_mode的值设置成后面的两个值(也就是我们说的严格模式),那么当在列中插入或更新不正确的值时,mysql将会给出错误,并且放弃insert/update操作。
 
在我们的一般应用中建议使用这两种模式,而不是使用默认的空或ANSI模式。但是需要注意的问题是,如果数据库运行在严格模式下,并且你的存储引擎不支持事务,那么有数据不一致的风险存在,比如一组sql中有两个dml语句,如果后面的一个出现了问题,但是前面的已经操作成功,那么mysql并不能回滚前面的操作。因此说设置sql_mode需要应用人员权衡各种得失,从而得到一个合适的选择。
 
Sql_mode的值还有很多,这里不再累述,可以参考相关的手册。
 
 

二、SQL Mode与可移植性

如果mysql与其它异构数据库之间有数据移植的需求的话,那么下面的sql_mode的组合设置可以达到相应的效果:




 

三、SQL Mode与数据效验

SQL Mode 还可以实现对数据效验和转移等功能如: 
效验日期数据合法性. 在INSERT或UPDATE过程中,如果被零除(或MOD(X,0)),则产生错误 将‘"'视为识别符引号(‘`'引号字符) 禁用反斜线字符(‘\')做为字符串内的退出字符。启用NO_BACKSLASH_ESCAPES模式,反斜线则成为普通字符。 将||视为字符串连接操作符(+)(同CONCAT()),而不视为OR。 查看全部


一、Mysql SQL Mode简介


通常来说MySQL服务器能够工作在不同的SQL模式下,并能针对不同的客户端以不同的方式应用这些模式。这样,应用程序就能对服务器操作进行量身定制以满足自己的需求。
 
这类模式定义了MySQL应支持的SQL语法,以及应该在数据上执行何种确认检查。这样,就能在众多不同的环境下、与其他数据库服务器一起更容易地使用MySQL。
 
可以使用" --sql-mode="modes" "选项,通过启动mysqld来设置默认的SQL模式。而从MySQL 4.1开始,也能在启动之后,使用SET [SESSION|GLOBAL] sql_mode='modes'语句,通过设置sql_mode变量更改其模式。
 
通常在linux下安装完mysql后,其默认的sql-mode值是空,在这种情形下mysql执行的是一种不严格的检查,例如日期字段可以插入'0000-00-00 00:00:00'这样的值,还有如果要插入的字段长度超过列定义的长度,那么mysql不会终止操作,而是会自动截断后面的字符继续插入操作,如下例:
mysql> create table t1 (c1 char(3));
mysql> insert into t1 values('abcd');
mysql> select * from t1;
+------+
| c1 |
+------+
| abc |
+------+
1 row in set (0.00 sec)
我们发现插入的字符被自动截断了,但是如果我们本意希望如果长度超过限制就报错,那么我们可以设置sql_mode为STRICT_TRANS_TABLES,如下:
mysql> set session sql_mode='STRICT_TRANS_TABLES'
这样我们再执行同样的操作,mysql就会告诉我们插入的值太长,操作被终止,如下:
mysql> insert into t1 values('abcd');
ERROR 1406 (22001): Data too long for column 'c1' at row 1

经常使用的sql_mode值:
sqlmode.png

说明:如果把sql_mode的值设置成后面的两个值(也就是我们说的严格模式),那么当在列中插入或更新不正确的值时,mysql将会给出错误,并且放弃insert/update操作。
 
在我们的一般应用中建议使用这两种模式,而不是使用默认的空或ANSI模式。但是需要注意的问题是,如果数据库运行在严格模式下,并且你的存储引擎不支持事务,那么有数据不一致的风险存在,比如一组sql中有两个dml语句,如果后面的一个出现了问题,但是前面的已经操作成功,那么mysql并不能回滚前面的操作。因此说设置sql_mode需要应用人员权衡各种得失,从而得到一个合适的选择。
 
Sql_mode的值还有很多,这里不再累述,可以参考相关的手册。
 
 


二、SQL Mode与可移植性


如果mysql与其它异构数据库之间有数据移植的需求的话,那么下面的sql_mode的组合设置可以达到相应的效果:
databaseclass.png

 


三、SQL Mode与数据效验


SQL Mode 还可以实现对数据效验和转移等功能如: 
  1. 效验日期数据合法性. 
  2. 在INSERT或UPDATE过程中,如果被零除(或MOD(X,0)),则产生错误 
  3. 将‘"'视为识别符引号(‘`'引号字符) 
  4. 禁用反斜线字符(‘\')做为字符串内的退出字符。启用NO_BACKSLASH_ESCAPES模式,反斜线则成为普通字符。 
  5. 将||视为字符串连接操作符(+)(同CONCAT()),而不视为OR。

MYSQL大小写敏感介绍

chris 发表了文章 • 0 个评论 • 195 次浏览 • 2017-05-08 22:55 • 来自相关话题

简介

在MySQL中,数据库对应数据目录中的目录。数据库中的每个表至少对应数据库目录中的一个文件(也可能是多个,取决于存储引擎)。因此,所使用操作系统的大小写敏感性决定了数据库名和表名的大小写敏感性。
 
在大多数Unix中数据库名和表名对大小写敏感,而在Windows中对大小写不敏感。一个显著的例外情况是Mac OS X,它基于Unix但使用默认文件系统类型(HFS+),对大小写不敏感。然而,Mac OS X也支持UFS卷,该卷对大小写敏感,就像Unix一样。
 
变量lower_case_file_system说明是否数据目录所在的文件系统对文件名的大小写敏感。ON说明对文件名的大小写不敏感,OFF表示敏感。
 
例如在Linux下查看:
mysql> show variables like 'lower%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
+------------------------+-------+
2 rows in set (0.00 sec)说明Linux系统对大小写敏感,MySQL也默认设置为对大小写敏感, 而Windows则相反。
 

大小写区分规则

Linux下:
数据库名与表名是严格区分大小写的;表的别名是严格区分大小写的;列名与列的别名在所有的情况下均是忽略大小写的;变量名也是严格区分大小写的;
 
windows下:
都不区分大小写
 
Mac OS下(非UFS卷):
都不区分大小写
 

参数说明

unix下lower_case_table_names默认值为 0 ;  Windows下默认值是 1 ;Mac OS X下默认值是 2 .





由大小写敏感转换为不敏感方法

如果原来所建立库及表都是对大小写敏感的,想要转换为对大小写不敏感,主要需要进行如下3步:
将数据库数据通过mysqldump导出。在my.cnf中更改lower_case_tables_name = 1,并重启mysql数据库。将导出的数据导入mysql数据库。
 

注意事项

为了避免大小写引发的问题,一种推荐的命名规则是:在定义数据库、表、列的时候全部采用小写字母加下划线的方式,不使用任何大写字母
 
在任何系统中可以使用lower_case_tables_name=1。使用该选项的不利之处是当使用SHOW TABLES或SHOW DATABASES时,看不出名字原来是用大写还是小写。
 
请注意在Unix中如果以前lower_case_tables_name = 0将lower_case_tables_name设置为1之前,重启mysqld之前,必须先将旧的数据库名和表名转换为小写。 查看全部


简介


在MySQL中,数据库对应数据目录中的目录。数据库中的每个表至少对应数据库目录中的一个文件(也可能是多个,取决于存储引擎)。因此,所使用操作系统的大小写敏感性决定了数据库名和表名的大小写敏感性。
 
在大多数Unix中数据库名和表名对大小写敏感,而在Windows中对大小写不敏感。一个显著的例外情况是Mac OS X,它基于Unix但使用默认文件系统类型(HFS+),对大小写不敏感。然而,Mac OS X也支持UFS卷,该卷对大小写敏感,就像Unix一样。
 
变量lower_case_file_system说明是否数据目录所在的文件系统对文件名的大小写敏感。ON说明对文件名的大小写不敏感,OFF表示敏感。
 
例如在Linux下查看:
mysql> show variables like 'lower%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
+------------------------+-------+
2 rows in set (0.00 sec)
说明Linux系统对大小写敏感,MySQL也默认设置为对大小写敏感, 而Windows则相反。
 


大小写区分规则


Linux下
  • 数据库名与表名是严格区分大小写的;
  • 表的别名是严格区分大小写的;
  • 列名与列的别名在所有的情况下均是忽略大小写的;
  • 变量名也是严格区分大小写的;

 
windows下
  • 都不区分大小写

 
Mac OS下(非UFS卷):
  • 都不区分大小写

 


参数说明


unix下lower_case_table_names默认值为 0 ;  Windows下默认值是 1 ;Mac OS X下默认值是 2 .
args.png


由大小写敏感转换为不敏感方法


如果原来所建立库及表都是对大小写敏感的,想要转换为对大小写不敏感,主要需要进行如下3步:
  1. 将数据库数据通过mysqldump导出。
  2. 在my.cnf中更改lower_case_tables_name = 1,并重启mysql数据库。
  3. 将导出的数据导入mysql数据库。

 


注意事项


为了避免大小写引发的问题,一种推荐的命名规则是:在定义数据库、表、列的时候全部采用小写字母加下划线的方式,不使用任何大写字母
 
在任何系统中可以使用lower_case_tables_name=1。使用该选项的不利之处是当使用SHOW TABLES或SHOW DATABASES时,看不出名字原来是用大写还是小写。
 
请注意在Unix中如果以前lower_case_tables_name = 0将lower_case_tables_name设置为1之前,重启mysqld之前,必须先将旧的数据库名和表名转换为小写。

MYSQL不同库质检查询

采菊篱下 回复了问题 • 2 人关注 • 1 个回复 • 272 次浏览 • 2017-03-20 11:49 • 来自相关话题

关于haproxy、mycat、mysql的问题

采菊篱下 回复了问题 • 2 人关注 • 1 个回复 • 348 次浏览 • 2017-03-13 15:16 • 来自相关话题

编译cmake MySQL 5.6.10报错

Nock 回复了问题 • 2 人关注 • 1 个回复 • 444 次浏览 • 2017-03-10 16:03 • 来自相关话题

MYSQL的八大缺陷

小白菜 发表了文章 • 0 个评论 • 414 次浏览 • 2016-12-29 11:39 • 来自相关话题

MySQL体积小,速度快,功能丰富,总体拥有成本低。它还是一个开源的关联式数据库管理系统,它的伟大成就说明了一个成功的公司是可以建立在开源之上的。
 
虽然用过mysql的人都曾对其出现的问题而抓狂,但只要是机器,总避免不了出问题的,更何况是一种每秒能保存成千上万行互联网数据,你怎么保证它一点错误都没有呢?
 
以下列举了8个开源关系型数据库的缺陷,其中不仅限于MySQL,还有是针对关系型数据库的。只有明白了关系型数据库和MySQL,才能更好地避免在使用MySQL中尽量少地遇到一些意外。
 

1、无法避免的bugs

任何一个软件包都有bug。但稍微深入了解一下,就会发现和Mysql相关的bugs自成体系。突然你就需要留心,因为NULL并不是以同样的方式出现,外键约束也没有像你想像的那样执行,连主键自动增长也会出错。

到处都避免不了小问题,而且并不总是可以修复的,这就是为什么一些人保持一个列表。还好MySQL维护着一个非常好的bug报告系统,让我们可以知道我些我们无法想像的事情,知道其他人也在经受同样的磨难。
 

2、关系表的不灵活性

关系表具有条理性是好的,但这迫使程序员要编造或把一些数据塞到已经定义好模式的列中。NoSQL之所以越来越受欢迎,其中一个原因就是它为程序员提供了足够的灵活性,以加速数据库的使用。如果一个街道地址需要增加一行,那么,你可以将它很容易地插入到一个NoSQL文档中。如果你想添加一个完整的新的数据块,无论它包含什么内容,文档模型也可以原封不动地接受你的数据,而不必改为它要求的数据格式。

假设你用整数格式建立了一个全部是邮编的表格。这个表十分高效,执行规则也很好。可是有一次,有人上传了一个使用了连字符的九位数邮编。或者说你得到了一位来自他国的客户的信件,上面写有邮政编码。这时,你会感到一切都乱了。老板要求网站要在几小时内恢复正常工作。然而,你已经没有时间重建数据库。程序员可以做什么?使用黑客手段把这位客户的邮政编码由base 64的数字格式改为base 10格式?或者设置一个使用转义编码的辅助表格,用来说明真正的邮政编码或者其他?危险的黑客无处不在,但你没有时间来搞定它。

MySQL的关联规则让每个人都诚实和谨慎,但它能强制我们避开易受攻击和欺骗的麻烦。
 

3、存储引擎混乱

总体来说,Mysql的存储引擎接口定义还算良好的。MySQL不是实际上的同一的数据库。它是由几个数据库组成,它们的大多数细节都被统一的表面掩盖了。开始时有一个MyISAM引擎,它很快但在前后一致上不能做到完备。有时你需要速度并且可以接受不一致的结果时是很好的。

当人们需要更多时,具备完整事务支持的Inno DB出现了。但这还不够。现在,它可能有20种存储引擎的选择——这足以使一个数据库管理员疯狂。当然,有时在不同的存储引擎之间切换而不必重写你的SQL是很好的,但是切换后总会带来混乱。这个表格我选择的引擎是MyISAM还是innoDB呢?或者,我决定输出的数据是CSV格式的吗?
 

4、JOIN联合查询

曾经,将数据分表保存是计算机科学史上的伟大创新。分开后的表不仅结构简单,使用上也简化了许多。但它却需要使用join语句来进行查询。

sql通过一系列join构建的复杂查询将开发者推入了困惑与绝望的深渊。而且存储引擎也需要以最优的方式来高效地解析join语句。开发者需要绞尽脑汁编写查询语句,然后数据库对其进行解析。

这就是很多注重运行速度的开发者放弃数据分表转而使用不规范数据表的原因。不区分数据实体,将所有数据保存到一个大表中——以避免复杂的查询。这样确实很快,并且服务器也不会耗尽内存。

现在的磁盘空间很廉价。8TB的磁盘已经在售,更大容量的也将上市。我们不再需要为使用join而绞尽脑汁了。
 

5、分支的混乱

毋庸置疑,一个可靠的、得到良好支持的MySQL分支,可以带来竞争和选择,但是它也引起困惑和混乱。更糟糕的是,一个称为MariaDB的MySQL分支,由Monty Widenius维护着。他同样也在参与编写MySQL。那么,Maria DB是真正独立的值得我们拥护的吗?或者它是MySQL?我们是否应该坚持使用由创建原始mysql数据库的组织运营的核心代码?或者我们应该加入那些被认为更聪明的,往往很酷的背叛者?

如何获取关于兼容性的信息?虽然Maria DB和MySQL十分相似,但它们之间也有差异。这就是大家一直都在争论它的原因。在性能方面,在我们查询的范围内,在两个阵营中,也许它们的工作方式相同,但也许不同,也许将来会不同。
 

6、开发MySQL的动机

虽然MySQL是一款成功的开源产品,但它仍属于商业中的一款产品,专业开发者需要靠它来获得利益,当然,最直接的利益就是薪资。当大多数用户在持续地享受开源许可证带来的最佳体验时,毫无疑问这家公司还在为赚取足够的钱来维持运营而努力。这导致自由代码在“社区版”和出售给企业的完整产品之间产生了奇怪的分岐。

我们应该对这款产品付钱吗?这种在社区版开展经营的行为是否公平?企业版中额外的功能是不是一个噱头,以引诱我们不断付费的呢?这至少说明一点,它是另一些需要回答的问题:选用哪个版本?遵照哪种许可证?选用它的哪个功能集?
 

7、原生JSON支持的缺乏

通过安装MySQL查看其年龄,然后你就知道需要添加哪些驱动程序使它变得可用。MySQL通常在3306端口上通信,一般输出的是它本身难以理解的格式化数据。如果要让你的代码和它通信,你必须添加另一层代码,将MySQL的语言转换成有用的东西。这些层的代码,以库的形式分发,经常需要人们购买一个商业许可证。

现代数据存储层通常直接以JSON通信。虽然MySQL和Maria DB现在有能力解析SQL中的JSON部分,但这还远远不够,原生的JSON接口已经被广泛使用于CouchDB、MongoDB,或任何最新的工具中。
 

8、封闭源和专有模块的兴起

虽然MySQL是开源的,但除了一些在”开源核心“周边开发的一些较新的、非开源的代码和专有模块。程序员也需要赚钱、需要生活,Oracle需要拿它的辛苦成果来换钱,这是一种现实,也是商业的性质。使用MySQL你也不可以免费得到任何东西。

要求MySQL始终坚持在一个很高的标准上,这有点不公平,因为开源的成功可能是一个圈套。它开始可以免费,但并不意味着它可以始终免费。如果企业需要更多新的功能,他们就要通过各种方式付费来获取。有时向Oracle付费,比自己来编写代码要便宜得多。有时商业的、不开源的代码是有意义的。

MySQL虽然作为一个成功的开源系统,但以上这些问题也总不可避免地出现,这就需要我们在它们发生之前有个深刻的认识,才能在今后的应用中避免不必要的麻烦。 查看全部
MYSQL.png

MySQL体积小,速度快,功能丰富,总体拥有成本低。它还是一个开源的关联式数据库管理系统,它的伟大成就说明了一个成功的公司是可以建立在开源之上的。
 
虽然用过mysql的人都曾对其出现的问题而抓狂,但只要是机器,总避免不了出问题的,更何况是一种每秒能保存成千上万行互联网数据,你怎么保证它一点错误都没有呢?
 
以下列举了8个开源关系型数据库的缺陷,其中不仅限于MySQL,还有是针对关系型数据库的。只有明白了关系型数据库和MySQL,才能更好地避免在使用MySQL中尽量少地遇到一些意外。
 


1、无法避免的bugs


任何一个软件包都有bug。但稍微深入了解一下,就会发现和Mysql相关的bugs自成体系。突然你就需要留心,因为NULL并不是以同样的方式出现,外键约束也没有像你想像的那样执行,连主键自动增长也会出错。

到处都避免不了小问题,而且并不总是可以修复的,这就是为什么一些人保持一个列表。还好MySQL维护着一个非常好的bug报告系统,让我们可以知道我些我们无法想像的事情,知道其他人也在经受同样的磨难。
 


2、关系表的不灵活性


关系表具有条理性是好的,但这迫使程序员要编造或把一些数据塞到已经定义好模式的列中。NoSQL之所以越来越受欢迎,其中一个原因就是它为程序员提供了足够的灵活性,以加速数据库的使用。如果一个街道地址需要增加一行,那么,你可以将它很容易地插入到一个NoSQL文档中。如果你想添加一个完整的新的数据块,无论它包含什么内容,文档模型也可以原封不动地接受你的数据,而不必改为它要求的数据格式。

假设你用整数格式建立了一个全部是邮编的表格。这个表十分高效,执行规则也很好。可是有一次,有人上传了一个使用了连字符的九位数邮编。或者说你得到了一位来自他国的客户的信件,上面写有邮政编码。这时,你会感到一切都乱了。老板要求网站要在几小时内恢复正常工作。然而,你已经没有时间重建数据库。程序员可以做什么?使用黑客手段把这位客户的邮政编码由base 64的数字格式改为base 10格式?或者设置一个使用转义编码的辅助表格,用来说明真正的邮政编码或者其他?危险的黑客无处不在,但你没有时间来搞定它。

MySQL的关联规则让每个人都诚实和谨慎,但它能强制我们避开易受攻击和欺骗的麻烦。
 


3、存储引擎混乱


总体来说,Mysql的存储引擎接口定义还算良好的。MySQL不是实际上的同一的数据库。它是由几个数据库组成,它们的大多数细节都被统一的表面掩盖了。开始时有一个MyISAM引擎,它很快但在前后一致上不能做到完备。有时你需要速度并且可以接受不一致的结果时是很好的。

当人们需要更多时,具备完整事务支持的Inno DB出现了。但这还不够。现在,它可能有20种存储引擎的选择——这足以使一个数据库管理员疯狂。当然,有时在不同的存储引擎之间切换而不必重写你的SQL是很好的,但是切换后总会带来混乱。这个表格我选择的引擎是MyISAM还是innoDB呢?或者,我决定输出的数据是CSV格式的吗?
 


4、JOIN联合查询


曾经,将数据分表保存是计算机科学史上的伟大创新。分开后的表不仅结构简单,使用上也简化了许多。但它却需要使用join语句来进行查询。

sql通过一系列join构建的复杂查询将开发者推入了困惑与绝望的深渊。而且存储引擎也需要以最优的方式来高效地解析join语句。开发者需要绞尽脑汁编写查询语句,然后数据库对其进行解析。

这就是很多注重运行速度的开发者放弃数据分表转而使用不规范数据表的原因。不区分数据实体,将所有数据保存到一个大表中——以避免复杂的查询。这样确实很快,并且服务器也不会耗尽内存。

现在的磁盘空间很廉价。8TB的磁盘已经在售,更大容量的也将上市。我们不再需要为使用join而绞尽脑汁了。
 


5、分支的混乱


毋庸置疑,一个可靠的、得到良好支持的MySQL分支,可以带来竞争和选择,但是它也引起困惑和混乱。更糟糕的是,一个称为MariaDB的MySQL分支,由Monty Widenius维护着。他同样也在参与编写MySQL。那么,Maria DB是真正独立的值得我们拥护的吗?或者它是MySQL?我们是否应该坚持使用由创建原始mysql数据库的组织运营的核心代码?或者我们应该加入那些被认为更聪明的,往往很酷的背叛者?

如何获取关于兼容性的信息?虽然Maria DB和MySQL十分相似,但它们之间也有差异。这就是大家一直都在争论它的原因。在性能方面,在我们查询的范围内,在两个阵营中,也许它们的工作方式相同,但也许不同,也许将来会不同。
 


6、开发MySQL的动机


虽然MySQL是一款成功的开源产品,但它仍属于商业中的一款产品,专业开发者需要靠它来获得利益,当然,最直接的利益就是薪资。当大多数用户在持续地享受开源许可证带来的最佳体验时,毫无疑问这家公司还在为赚取足够的钱来维持运营而努力。这导致自由代码在“社区版”和出售给企业的完整产品之间产生了奇怪的分岐。

我们应该对这款产品付钱吗?这种在社区版开展经营的行为是否公平?企业版中额外的功能是不是一个噱头,以引诱我们不断付费的呢?这至少说明一点,它是另一些需要回答的问题:选用哪个版本?遵照哪种许可证?选用它的哪个功能集?
 


7、原生JSON支持的缺乏


通过安装MySQL查看其年龄,然后你就知道需要添加哪些驱动程序使它变得可用。MySQL通常在3306端口上通信,一般输出的是它本身难以理解的格式化数据。如果要让你的代码和它通信,你必须添加另一层代码,将MySQL的语言转换成有用的东西。这些层的代码,以库的形式分发,经常需要人们购买一个商业许可证。

现代数据存储层通常直接以JSON通信。虽然MySQL和Maria DB现在有能力解析SQL中的JSON部分,但这还远远不够,原生的JSON接口已经被广泛使用于CouchDB、MongoDB,或任何最新的工具中。
 


8、封闭源和专有模块的兴起


虽然MySQL是开源的,但除了一些在”开源核心“周边开发的一些较新的、非开源的代码和专有模块。程序员也需要赚钱、需要生活,Oracle需要拿它的辛苦成果来换钱,这是一种现实,也是商业的性质。使用MySQL你也不可以免费得到任何东西。

要求MySQL始终坚持在一个很高的标准上,这有点不公平,因为开源的成功可能是一个圈套。它开始可以免费,但并不意味着它可以始终免费。如果企业需要更多新的功能,他们就要通过各种方式付费来获取。有时向Oracle付费,比自己来编写代码要便宜得多。有时商业的、不开源的代码是有意义的。

MySQL虽然作为一个成功的开源系统,但以上这些问题也总不可避免地出现,这就需要我们在它们发生之前有个深刻的认识,才能在今后的应用中避免不必要的麻烦。

Mysql同步错误Last_SQL_Errno: 1054

Geek小A 回复了问题 • 2 人关注 • 1 个回复 • 701 次浏览 • 2016-12-27 23:28 • 来自相关话题