MariaDB非分布式数据库经典案例

原创

owenwtyang_杨文涛

修改于 2019-06-03 10:53:27

2.4K0

修改于 2019-06-03 10:53:27

文章被收录于专栏：大客户售后经典数据库案例

常见问题

CASE 1：Update语法

问题现象：tdsql-xxxxxx 库plidb表letterperson字段PrintState被大量置为了‘0’

问题原因：开发/操作人员update语法错误导致。

错误语法：

update letterperson set PrintState = '4' and t.SerPersonNo = '7e7732fc64d9';

正确语法：

update letterperson set PrintState = '4',t.SerPersonNo = '7e7732fc64d9';

语法说明：

实验分析：

解决方法：

在一条UPDATE语句中，需要用逗号“,”分隔需要更新的多个字段，为什么会是0？--解析器看成了正则表达式。

update letterperson set PrintState = '4' and t.SerPersonNo = '7e7732fc64d9';

等价于update letterperson set PrintState = （'4' and t.SerPersonNo = '7e7732fc64d9'）;

CASE 2：JDBC与Mysql“CST”时区协商误差13小时

问题现象：

数据库中存储的 Timestamp 字段的 unix_timestamp() 值比真实值少了 13 个小时。

问题原因：

1.JDBC （java）误认CST为CST-5时区；

2.JBDC 将Timestamp+0 转为 CST-5 的 String-5；

3.MySQL 认为会话时区在 CST+8，将 String-5 转为 Timestamp-13；

实验分析：

当TDSQL的time_zone 值为 SYSTEM 时，会取 system_time_zone 值作为协调时区。

mysql> show variables like '%time_zone%';

+------------------+--------+

| Variable_name | Value |

+------------------+--------+

| system_time_zone | CST |

| time_zone | SYSTEM |

+------------------+--------+

2 rows in set (0.00 sec)

解决方法：

明确指定 MySQL 数据库的时区，不使用引发误解的 CST ：

mysql> set global time_zone = '+08:00';

Query OK, 0 rows affected (0.00 sec)

mysql> set time_zone = '+08:00';

Query OK, 0 rows affected (0.00 sec)

CASE 3： MySQL 5.7自定义Function 处理date时间对象上转换异常

问题现象：子查询result为null，但是外层函数返回真；

问题原因：

暂无。

影响版本：

版本：Percona 5.7.17

规避方法：

使用FUNCTION STR_TO_DATE('20181201','%Y%m%d %h%i'))

替换自定义时间转换函数。

附客户自定义时间函数

to_date

DELIMITER $$

CREATE FUNCTION `to_date`(datevalue VARCHAR(100), formatvalue VARCHAR(100) ) RETURNS datetime

BEGIN

IF UPPER(formatvalue) = 'YYYYMM' THEN RETURN STR_TO_DATE(CONCAT(datevalue,'01'),'%Y%m%d');

ELSEIF UPPER(formatvalue) = 'YYYY-MM' THEN RETURN STR_TO_DATE(CONCAT(datevalue,'-01'),'%Y-%m-%d');

ELSEIF UPPER(formatvalue) = 'YYYY-MM-DD' THEN RETURN STR_TO_DATE(datevalue,'%Y-%m-%d');

ELSEIF UPPER(formatvalue) = 'YYYYMMDD' THEN RETURN STR_TO_DATE(datevalue,'%Y%m%d');

ELSEIF UPPER(formatvalue) = 'HH24:MI:SS' THEN RETURN STR_TO_DATE(CONCAT( DATE_FORMAT(LAST_DAY(NOW()),'%Y-%m-' ),'01 ',datevalue),'%Y-%m-%d %H:%i:%s');

ELSEIF UPPER(formatvalue) = 'YYYY-MM-DD HH24:MI:SS' THEN RETURN STR_TO_DATE(datevalue,'%Y-%m-%d %H:%i:%s');

ELSE RETURN STR_TO_DATE(datevalue,formatvalue);

END IF;

END $$

DELIMITER ;

CASE 4：Varchar类型字段输入不可见字符导致查询异常

问题现象：

一行数据中有a、b、c三个字段为Varchar类型，当用a、c两个字段过滤时，结果集为真，当用where后用字段b过滤时，查询结果集为null。

问题原因：

字段b输入了不可见字符。

实验分析：

Instance_id: IP: DB: is_comdb Table: tps_cp | tps_cp | CREATE TABLE `tps_cp` ( `sidxxx` int(11) NOT NULL AUTO_INCREMENT, `sxxx` varchar(20) DEFAULT NULL, `tf_code` varchar(50) DEFAULT NULL, `dp_code` varchar(50) DEFAULT NULL, ... `location_id` varchar(50) DEFAULT NULL, ... PRIMARY KEY (`sid`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1646762 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT |

当用tf_code 与location_id查询时，结果集为真：

当用dp_code 查询时，结果集为null：

解决方法：编码层面去掉不可见字符。

CASE 5：单个交易业务偶现整体执行超时

问题现象：

单个交易包含上百个独立SQL，整体执行时间出现抖动（偶现总体执行耗时增加60%以上毛刺）。

问题原因：通过ping测发现单个网关到后端DB响应延时3ms左右高于正常值（<1ms,）。

问题影响：发送到高延时网关的SQL执行耗时远高于正常的网关导致整体交易耗时增加60%+。

规避方法：

屏蔽响应慢网关（多个网关是等价关系）。

解决方法：

如果没有跨可用区，需要修复路由问题。

主备切换

CASE 6：大事务导致TDSQL实例主从切换

问题现象：

大事务SQL运行时TDSQL实例发生主从切换(Percona 引擎)。

问题原因：

大事务SQL产生了大量binlog，导致binlog在组提交的时候需要长时间写入binlog，阻塞了agent心跳语句的binlog写入，agent心跳写入超时，触发主备切换。

注：目前MariaDB binlog_write_threshold 限制是1.6G，Percona无限制。

max_binlog_size默认最大是1G，但是上面参数限制了最大binlog size。

问题影响：

当TDSQL实例的大事务在执行时发生了主从切换，会导致当前未commit的事务数据丢失。

规避方法：

避免大事务SQL执行。

需要重新评估当前事务涉及SQL执行时涉及修改表的数据量，采用Limit 方式循环执行提交。将一个大事务分割为多个小事务。

CASE 7：IO利用率100%引发主备切换导致UPDATE操作失败

问题现象：

Update操作完成后发现update操作提交的数据不可见。

问题原因：

Update操作数据量巨大导致IO利用率100%，由于探活agent写binglog阻塞，导致agent 判断主库down机，发生主备切换。由于一主两从为异步方式，所以binlog未同步到备机。最终导致update操作的数据丢失。

规避方法：

修改同步方式为强同步，避免主备切换导致binlog丢失。

CASE 8：SELECT… FROM DUAL 语句导致主从切换

问题现象：云上TDSQL实例发生主从切换、CPU利用率掉底、连接出现中断

问题原因：

SQL中跑了 SELECT …… FROM DUAL 语句,会引起MySQL异常重启，从而引起业务连接断掉。当重启的时间过长，会被TDSQL的HA机制检测到，触发正常的主从切换。

涉及版本：

社区版的mysql-5.7.17也有这个bug，目前看是所有的mysql都有影响，官方目前无修复补丁与升级计划。

规避方法：

请联系大客户售后，并提工单。工单上提供实例ID、打select... from dual引起DB重启补丁、可以操作的时间窗口等信息。

权限管理

CASE 9：控制台授权失败

问题现象：控制台修改账户对表select等权限成功，但是实际不生效。

问题原因：对表授予execute权限，前台显示成功，实际后台失败。

授权截图：

解决方法：

对表没有execute权限，所以赋予表的权限给用户时不要赋execute权限。

Procedure 才有execute权限。

CASE 10：控制台创建读写用户报错

问题现象：

TDSQL控制台创建读写帐号，在赋予权限时，却报 "failed to execute IsReadOnlyUser[InternalError.DbOperationFailed"

问题原因：

用户创建账户其实是个异步过程，但是目前前端是同步的交互。有时候会出现帐号还没创建出来，但是前端界面已经进入设置权限的交互，这时候就会报如上图账户不存在的错误；

规避方法：

等5分钟左右后在控制台看到帐号后再赋予权限。

数据迁移

CASE 11：

新版数据迁移不支持视图、MyISAM Engine、无主键表

问题现象：

MyIsam engine、无主键表，校验阶段报错；有视图，迁移中途报错。

问题原因：

1) 由于当前TDSQL 10.1.9及以上不支持MyIsam以及无主键表，因此新版本数据迁移开启了这两个条件校验。

2) 由于视图与对应的表有依赖关系，数据迁移暂无能力检验这种依赖关系。

问题影响：

都会导致迁移失败。

规避方法：

1）不要迁移MyIsam表与无主键表（更改Engine，加主键）。

2）不要迁移视图，迁移完成后再重执行建视图。

自建主从

CASE 12：自建主从时Mydumper导出数据时报锁超时

问题现象：

Mydumper备份数据报锁超时。

问题原因：

mydumper备份时FTWRL需要获取全局只读锁，由于业务持有锁，导致导数据报错。

Mydumper备份原理如下：

1. 主线程 FLUSH TABLES WITH READ LOCK, 施加全局只读锁，以阻止DML语句写入，保证数据的一致性；

2. 读取当前时间点的二进制日志文件名和日志写入的位置并记录在metadata文件中，以供即使点恢复使用；

3. N个（线程数可以指定，默认是4）dump线程 START TRANSACTION WITH CONSISTENT SNAPSHOT; 开启读一致的事物；

4. dump non-InnoDB tables, 首先导出非事务引擎的表；

5. 主线程 UNLOCK TABLES 非事物引擎备份完后，释放全局只读锁；

6. dump InnoDB tables, 基于事物导出InnoDB表；

7. 事物结束。

问题影响：

对业务无影响。

规避方法：

用只读帐号或者业务低峰备份。

CASE 13：自建主从时Mydumper备份数据恢复报错

问题现象：

Myloader备份数据报表不存在。

问题原因：

The MyISAM storage engine was the default storage engine from MySQL 3.23 until it was replaced by InnoDB in MariaDB and MySQL 5.5. Historically, MyISAM is a replacement for the older ISAM engine, removed in MySQL 4.1.

数据库里有MyIsam的表，导入数据时无法创建，导致报表不存在。

问题影响：

MyISAM表数据无法导入。

规避方法：

手动修改建表语句的Engine修改为innodb后重新导入。