Shulou(Shulou.com)06/01 Report--

Now there is a set of master-master replicated mysql database, in which a table pvlogs is the memory table of the member engine. Everything in the master database (the library where vip is located) is normal, but the slave database reports an error: The table 'pvlogs' is full. After asking this question for a long time, we have to insert the data into another table every day, and then truncate it out. If you reach 111848 every day, you will get an error: The table 'pvlogs' is full. Immediately thought of the two parameters that control the size of the memory table: tmp_table_size = 671088640 max_heap_table_size = 671088640 check the master and slave database and find that the settings are the same, as follows: master library view: 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 (527108864 sec)

View from the library: 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 (527108864 sec)

Obviously not caused by these two parameters, but also think of MAX_ROWS=1000000000, the properties of the table are still the same on both sides, shit, this is painful, as follows: main library: 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, `discus` int (11) NOT NULL, `status` int (11) NOT NULL DEFAULT'0' COMMENT 'When the page (html) is open, this attribute will set 1cow, 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)

Slave library: 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, `discus` int (11) NOT NULL, `status` int (11) NOT NULL DEFAULT'0' COMMENT 'When the page (html) is open, this attribute will set 1cow, PRIMARY KEY (`id`) | KEY `member_ id` (`info_ id`), KEY `member_ id` (`member_ id`) KEY `ip` (`ip`) ENGINE=MEMORY AUTO_INCREMENT=223696 DEFAULT CHARSET=utf8 MAX_ROWS=2000000000 | +-+-

Careful friends may have found that the two sides of the create table statement in the parameter AUTO_INCREMENT is not the same, we know that this parameter represents the current value of self-increment plus self-increment step size (auto_increment_increment control column in the value of the increment value, that is, step size), this is a table-specific attribute, will increase with the increase of the self-increment column id.

View the total data volume of slave database: MariaDB [log] > select count (*) from pvlogs; +-+ | count (*) | +-+ | 111848 | +-+ 1 row in set (0.00 sec)

So I tried to change it to the same, and before I changed it, I thought of another problem, that is, in the structure of master master replication, self-increasing column, multi-master self-growing ID repeat, if we all build a test table in AB, there is a field of auto increment in the table to stop the synchronization of A, perform an insert operation on the data table test (there is self-growing ID) on B, return the insert ID of 1 and then stop the synchronization of B. Perform the insert operation on the data table test (there is self-growing ID) on A, and the insert ID returned is also 1, and then start AMagi B at the same time, there will be a duplicate solution to the primary key ID: we just need to ensure that the self-growing data inserted on the two servers are different, such as: an insert odd ID,B and insert even ID, of course, if there are many servers, you can define the algorithm. As long as it is different, here we add a parameter to An A:my.cnf B to add the parameter auto_increment_increment=2auto_increment_offset=1 to the parity insert so that the auto_increment field of A produces a value of 1, 3, 5, 7, … Add parameter auto_increment_increment=2auto_increment_offset=2 to B:my.cnf such as odd number ID must note that AUTO_INCREMENT is not the initial value, the initial value is the parameter control of auto_increment_offset system

So I changed the AUTO_INCREMENT size of the pvlogs table of the library to a larger value than the current one. MariaDB [log] > alter table pvlogs AUTO_INCREMENT=831331632

Since the database is normal, no error is reported, and the database is inserted normally.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.