0

I have a wordpress website that run on 3 machines

                       Machine1      | Machine 2 {nginx+lsyncd+mysql}
visitor > cloudflare > haproxy ------
                                     | Machine 3 {nginx+lsyncd+mysql}

the machine 2 and machine 3 running centos 7.6 os 64 bit with mysql 5.7 and phpmyadmin

I have one database running for replication, the problem i see that the total sum for both database on each server not equal, for example, this is screenshot form phpmyadmin for the server number 1

enter image description here

the screenshot from phpmyadmin in server number 2

enter image description here

you can see rows not equal so I start to search for that table that miss this 1 row record and I found that the table that missing this 1 record is the table wp_options

wp_options server 1

mysql> SHOW CREATE TABLE wp_options;
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| wp_options | CREATE TABLE `wp_options` (
  `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `option_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `option_value` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `autoload` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`option_id`),
  UNIQUE KEY `option_name` (`option_name`)
) ENGINE=MyISAM AUTO_INCREMENT=2883462 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

wp_options server 2

mysql> SHOW CREATE TABLE wp_options;
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| wp_options | CREATE TABLE `wp_options` (
  `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `option_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `option_value` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `autoload` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`option_id`),
  UNIQUE KEY `option_name` (`option_name`)
) ENGINE=MyISAM AUTO_INCREMENT=2883462 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

checksum server 1

mysql> CHECKSUM TABLE wp_options;
+-------------------------+------------+
| Table                   | Checksum   |
+-------------------------+------------+
| mobizil_data.wp_options | 3191386356 |
+-------------------------+------------+
1 row in set (0.03 sec)

checksum server 2

mysql> CHECKSUM TABLE wp_options;
+-------------------------+------------+
| Table                   | Checksum   |
+-------------------------+------------+
| mobizil_data.wp_options | 2698212655 |
+-------------------------+------------+
1 row in set (0.01 sec)

the error log found in mysql-error.log file

mysql error log 2019-05-12T22:49:27.028225Z 11 [Note] Slave SQL for channel '': Could not execute Delete_rows event on table database_name.wp_options; Can't find record in 'wp_options', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000006, end_log_pos 174775695, Error_code: 1032

Here are my.cnf in both server

[mysqld]
# REPLICATION #
skip-name-resolve
server-id=2
log-bin=/var/log/mysql/mysql-bin.log
relay-log=/var/log/mysql/relay-bin.log
binlog_do_db           = database_name
binlog-format=ROW
log-slave-updates
slave_net_timeout = 60
slave-skip-errors=1062,1032
expire_logs_days=14
auto_increment_increment = 10
auto_increment_offset = 2
sql_mode=""

# GENERAL #
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
performance-schema=0
symbolic-links=0
log-error=/var/lib/mysql/mysql-error.log
pid-file=/var/run/mysqld/mysqld.pid
default-storage-engine=MyISAM
innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=10000

The server-id and auto_increment_offset is different on each server

I want to know why this happens and how to troubleshoot it and prevent from happening again

  • 1
    What are the numbers? Where did they come from? See percona tools for checksum and comparision tools. – Rick James May 10 '19 at 0:12
  • 1
    Given you're ignoring two errors could those ignores be the reason for the missing rows. You aren't doing something reckless like writing to both nodes at the same time are you? – danblack May 10 '19 at 0:17
  • hey @RickJames , i am not aware too much with mysql cli to give u the output from it. so easy thing i use are phpmyadmin the web interface u sure aware of it. the only mismatch rows i see in table called wp_options , i update my question with more information about the environment i am using replication for. can you guide me through percona tools and tell me what commands should i use? – iLinux85 May 10 '19 at 0:27
  • hey @danblack i update my question with more information about the environment i am using replication for. i don't believe there is two query excute at the same time because each visitor on his own unique session. – iLinux85 May 10 '19 at 0:28
  • Please provide SHOW CREATE TABLE wp_options – Rick James May 10 '19 at 0:31
0

Based on an error message that finally came to light, plus the table being MyISAM, I recommend

CHECK TABLE wp_options;

If it complains about anything, do

REPAIR TABLE wp_options;
  • so if I use check and repair on wp_options table the sum count will be fixed? – iLinux85 May 13 '19 at 11:54
  • @iLinux85 - Maybe it will be fixed. I think you have described a problem that should not happen unless the data is corrupt. REPAIR can fix some forms of corruption. (Switching to InnoDB is a better idea in the long run.) – Rick James May 13 '19 at 17:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.