mysql 使用过程的错误记录(一)（mysql语句错误）

mysql 使用过程的错误记录（一）

一：MySQL 索引

在修改表结构时出现了错误：Specified key was too long;max key length is 1000 bytes.

alter table pre_common_diy_data modify column targettplname varchar(255);

如果是按一个字符占两个字节计算 2*255=510 并没有超过1000字符，怎么会报错呢?

在查询相关资料后发现，MySQL MyIsam 存储引擎在创建索引的时候，索引键长度是有一个较为严格的长度限制的，所有索引键最大长度总和不能超过1000，而且不是实际数据长度的总和，而是索引键字段定义长度的总和。

主要字符集的计算方式如下：

latin1 = 1 byte = 1 character

utf8 = 3 byte = 1 character

gbk = 2 byte = 1 character

utf8mb4 = 4 byte = 1 character

1、查看MySQL存储引擎，默认存储引擎为MyISAM。

mysql> show engines;

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

| Engine | Support | Comment | Transactions | XA | Savepoints |

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

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

2、我们查看MySQL表结构，总索引长度为：(100+80）=180

mysql> desc pre_common_diy_data;

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

| Field | Type | Null | Key | Default | Extra |

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

| targettplname | varchar(100) | NO | PRI | | |

| tpldirectory | varchar(80) | NO | PRI | | |

3、查看表的字符集，为utf8字符，那么索引总长度为：180*3=480

mysql> show create table pre_common_diy_data;

| Table | Create Table

| pre_common_diy_data | CREATE TABLE `pre_common_diy_data` (

`targettplname` varchar(240) NOT NULL DEFAULT '',

`tpldirectory` varchar(80) NOT NULL DEFAULT '',

) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

1 row in set (0.00 sec)

再查看我们的执行报错SQL：

alter table pre_common_diy_data modify column targettplname varchar(255);

计算总长度：（80+255）*3=1005，已经超过了1000，所以出错。

解决的方法是减少字段的长度：

alter table pre_common_diy_data modify column targettplname varchar(240);

执行成功。

另外对于创建innodb的组合索引，也是有索引键长度长度限制的。

二：Waiting for table metadata lock

最好通过lock_wait_timeout设置好超时时间，避免长时间的metadata锁等待

show global variables like '%timeout%';

三：忽略大小写

/etc/my.cnf

lower_case_table_names=1

1.查看数据库当前是否大小写敏感

show variables like '%case_table%';

lower_case_table_names=1 （不敏感）

lower_case_table_names=0 （敏感）

2.只对新建的库，表启作用，已经存在的会报错

四：innodb_force_recovery

因为日志已经损坏，这里采用非常规手段,首先修改innodb_force_recovery参数，使mysqld跳过恢复步骤，将mysqld 启动,将数据导出来然后重建数据库。

innodb_force_recovery可以设置为1-6,大的数字包含前面所有数字的影响。

1. (SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页。

2. (SRV_FORCE_NO_BACKGROUND):阻止主线程的运行，如主线程需要执行full purge操作，会导致crash。

3. (SRV_FORCE_NO_TRX_UNDO):不执行事务回滚操作。

4. (SRV_FORCE_NO_IBUF_MERGE):不执行插入缓冲的合并操作。

5. (SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日志，InnoDB存储引擎会将未提交的事务视为已提交。

6. (SRV_FORCE_NO_LOG_REDO):不执行前滚的操作。

在my.cnf中修改以下两个参数

innodb_force_recovery=6

innodb_purge_thread=0