主主复制的mysql从库 内存表The table 'pvlogs' is full问题处理

千家信息网最后更新 2024年09月21日主主复制的mysql从库 内存表The table 'pvlogs' is full问题处理现在有一套主主复制的mysql数据库，其中有个表pvlogs是member引擎的内存表，主库（就是vip所在的那个库）一切正常，但是从库报错：The table 'pvlogs' is full， 经过询问这个问题已经持续好长时间了，我们这个表是每天都要先把数据insert 进另一个表，然后truncate掉。每天都是到111848这个数量就会报错：The table 'pvlogs' is full。 立马想到了控制内存表大小的两个参数： tmp_table_size = 671088640 max_heap_table_size = 671088640 在主从库查看得知设置是一样的，如下所示： 主库查看： MariaDB [log]> show VARIABLES like '%max_heap_table_size%'; +---------------------+------------+ | Variable_name | Value | +---------------------+------------+ | max_heap_table_size | 2271087616 | +---------------------+------------+ 1 row in set (0.00 sec)
MariaDB [log]> show VARIABLES like '%tmp_table_size%'; +----------------+-----------+ | Variable_name | Value | +----------------+-----------+ | tmp_table_size | 527108864 | +----------------+-----------+ 1 row in set (0.00 sec)
从库查看： MariaDB [log]> show VARIABLES like '%max_heap_table_size%'; +---------------------+------------+ | Variable_name | Value | +---------------------+------------+ | max_heap_table_size | 2271087616 | +---------------------+------------+ 1 row in set (0.00 sec)
MariaDB [log]> show VARIABLES like '%tmp_table_size%'; +----------------+-----------+ | Variable_name | Value | +----------------+-----------+ | tmp_table_size | 527108864 | +----------------+-----------+ 1 row in set (0.00 sec)
很显然不是这两个参数导致的，还想到了MAX_ROWS=1000000000，表的属性，经查看两边还是一样的，靠，这就蛋疼了，如下所示： 主库： MariaDB [log]> show create table pvlogs;
| Table | Create Table | pvlogs | CREATE TABLE `pvlogs` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `member_id` int(11) DEFAULT NULL, `jsession` bigint(20) DEFAULT NULL, `ip` bigint(20) DEFAULT NULL, `search_id` bigint(20) DEFAULT NULL, `info_id` bigint(20) DEFAULT NULL, `lastmodify` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `disc` int(11) NOT NULL, `status` int(11) NOT NULL DEFAULT '0' COMMENT 'When the page(html) is open ,this attribute will set 1', PRIMARY KEY (`id`), KEY `info_id` (`info_id`), KEY `member_id` (`member_id`), KEY `ip` (`ip`) ) ENGINE=MEMORY AUTO_INCREMENT=831382377522705486 DEFAULT CHARSET=utf8 MAX_ROWS=2000000000 |
1 row in set (0.00 sec)
从库： MariaDB [log]> show create table pvlogs; | Table | Create Table | pvlogs | CREATE TABLE `pvlogs` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `member_id` int(11) DEFAULT NULL, `jsession` int(11) DEFAULT NULL, `ip` bigint(20) DEFAULT NULL, `search_id` bigint(20) DEFAULT NULL, `info_id` bigint(20) DEFAULT NULL, `lastmodify` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `disc` int(11) NOT NULL, `status` int(11) NOT NULL DEFAULT '0' COMMENT 'When the page(html) is open ,this attribute will set 1', PRIMARY KEY (`id`), KEY `info_id` (`info_id`), KEY `member_id` (`member_id`), KEY `ip` (`ip`) ) ENGINE=MEMORY AUTO_INCREMENT=223696 DEFAULT CHARSET=utf8 MAX_ROWS=2000000000 | +--------+--------------------------------------------------------------------------------------
细心的朋友可能已经发现，这两边的创建表语句中的参数AUTO_INCREMENT是不一样的，我们知道这个参数代表自增的当前值加上自增步长（auto_increment_increment控制列中的值的增量值，也就是步长），这个是某个表特有的属性，会随着自增列id的增大而增大。
查看从库总数据量： MariaDB [log]> select count(*) from pvlogs; +----------+ | count(*) | +----------+ | 111848 | +----------+ 1 row in set (0.00 sec)
于是我尝试着改成一样，在改之前，我想到了另一个问题，就是主主复制的结构中，自增列 多主自增长ID重复

• 假如我们在AB都建立一张test表，表中有一个auto increment的字段
• 停掉A的同步，在B上对数据表test(存在自增长ID)执行插入操作，返回插入ID为1
• 然后停掉B的同步，在A上对数据表test(存在自增长ID)执行插入操作，返回的插入ID也是1
• 然后同时启动A,B，就会出现主键ID重复

解决方法： 我们只要保证两台服务器上插入的自增长数据不同就可以了 如：A插入奇数ID，B插入偶数ID，当然如果服务器多的话，你可以定义算法，只要不同就可以了 在这里我们在A,B上加入参数，以实现奇偶插入 A：my.cnf上加入参数

1. auto_increment_increment=2
2. auto_increment_offset=1

这样A的auto_increment字段产生的数值是：1, 3, 5, 7, …等奇数ID了 B：my.cnf上加入参数

1. auto_increment_increment=2
2. auto_increment_offset=2

一定注意AUTO_INCREMENT可不是初始值，初始值是auto_increment_offset系统参数控制，
于是我修改从库的pvlogs表的AUTO_INCREMENT大小：改成了一个比目前大的一个值。 MariaDB [log]> alter table pvlogs AUTO_INCREMENT=831331632;
从库正常了，不再报错，开始正常插入数据库。。。。。