Mysql slave skip error

mysql master-slave replication, often encounter errors and lead to slave end replication interruption, this time generally requires manual intervention, skip errors to continue

There are two ways to skip errors:

1.1 Skip a specified number of transactions:

mysql>stop slave;
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;        #Skip a transaction
mysql>start slave;

1.2 Modify mysql configuration file to skip all errors or specified types of errors through the slave_skip_errors parameter

vi /etc/my.cnf
[mysqld]
#slave-skip-errors=1062,1053,1146 #Skip the error of the specified error no type
#slave-skip-errors=all #Skip all errors

2 cases

Let’s simulate an error scenario
Environment (a configured master-slave replication environment)
master database IP: 192.168.247.128
Slve database IP: 192.168.247.130
mysql version: 5.6.14
binlog-do-db = mydb

Execute the following statement on master:

mysql>use mysql;
mysql>create table t1 (id int);
mysql>use mydb;
mysql>insert into mysql.t1 select 1;

View replication status on slave

mysql> show slave status G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.247.128
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000017
          Read_Master_Log_Pos: 2341
               Relay_Log_File: DBtest1-relay-bin.000011
                Relay_Log_Pos: 494
        Relay_Master_Log_File: mysql-bin.000017
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1146
                   Last_Error: Error 'Table 'mysql.t1' doesn't exist' on query. Default database: 'mydb'. Query: 'insert into mysql.t1 select 1'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1919
              Relay_Log_Space: 1254
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1146
               Last_SQL_Error: Error 'Table 'mysql.t1' doesn't exist' on query. Default database: 'mydb'. Query: 'insert into mysql.t1 select 1'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: f0f7faf6-51a8-11e3-9759-000c29eed3ea
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 131210 21:37:19
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

As can be seen from the results, Read_Master_Log_Pos: 2341, Exec_Master_Log_Pos: 1919 error Last_SQL_Error: Error’Table’mysql.t1’does’t exist’ on query.
Because only binlog is recorded for mydb, errors occur when tables in other databases are operated on the mydb library but do not exist on slave.

Let’s look at the transaction content in the binlog, where a row represents a transaction.

mysql> SHOW BINLOG EVENTS in 'mysql-bin.000017' from 1919G
*************************** 1. row ***************************
   Log_name: mysql-bin.000017
        Pos: 1919
 Event_type: Query
  Server_id: 1
End_log_pos: 1999
       Info: BEGIN
*************************** 2. row ***************************
   Log_name: mysql-bin.000017
        Pos: 1999
 Event_type: Query
  Server_id: 1
End_log_pos: 2103
       Info: use `mydb`; insert into mysql.t1 select 1
*************************** 3. row ***************************
   Log_name: mysql-bin.000017
        Pos: 2103
 Event_type: Xid
  Server_id: 1
End_log_pos: 2134
       Info: COMMIT /* xid=106 */
*************************** 4. row ***************************
   Log_name: mysql-bin.000017
        Pos: 2134
 Event_type: Query
  Server_id: 1
End_log_pos: 2213
       Info: BEGIN
*************************** 5. row ***************************
   Log_name: mysql-bin.000017
        Pos: 2213
 Event_type: Query
  Server_id: 1
End_log_pos: 2310
       Info: use `mydb`; insert into t1 select 9
*************************** 6. row ***************************
   Log_name: mysql-bin.000017
        Pos: 2310
 Event_type: Xid
  Server_id: 1
End_log_pos: 2341
       Info: COMMIT /* xid=107 */
6 rows in set (0.00 sec)

From the above results, we need to skip two transactions (Pos: 1999 insert, Pos: 2103 commit)
Skip operation:

mysql>stop slave;
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2;        Skip a transaction
mysql>start slave;
mysql> show slave statusG
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    3
Current database: mydb

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.247.128
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000017
          Read_Master_Log_Pos: 3613
               Relay_Log_File: DBtest1-relay-bin.000018
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000017
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3613
              Relay_Log_Space: 458
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: f0f7faf6-51a8-11e3-9759-000c29eed3ea
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.01 sec)

The replication status is normal.

Programmer Group

A programming skills sharing group

mysql master-slave replication skips error items

mysql master-slave replication, often encounter errors and lead to slave end replication interruption, this time generally requires manual intervention, skip errors to continue

There are two ways to skip errors:

1.1 Skip a specified number of transactions:

1.2 Modify mysql configuration file to skip all errors or specified types of errors through the slave_skip_errors parameter

2 cases

Let’s simulate an error scenario
Environment (a configured master-slave replication environment)
master database IP: 192.168.247.128
Slve database IP: 192.168.247.130
mysql version: 5.6.14
binlog-do-db = mydb

Execute the following statement on master:

View replication status on slave

As can be seen from the results, Read_Master_Log_Pos: 2341, Exec_Master_Log_Pos: 1919 error Last_SQL_Error: Error’Table’mysql.t1’does’t exist’ on query.
Because only binlog is recorded for mydb, errors occur when tables in other databases are operated on the mydb library but do not exist on slave.

Let’s look at the transaction content in the binlog, where a row represents a transaction.

From the above results, we need to skip two transactions (Pos: 1999 insert, Pos: 2103 commit)
Skip operation:

The replication status is normal.

Posted by golfinggod on Thu, 24 Jan 2019 06:45:15 -0800

Источник

MySQL Replication skip error

MySQL Replication skip error

There are two ways you can configure MySQL replication : normal and GTID based replication. From MySQL 5.6 GTID based replication is started. However, you can again explore how to setup MySQL Replication on the the following links

Sometimes, replication on Slave server is stopped for many a reasons and you need to apply “MySQL Replication skip error” technique to start quickly Slave SQL process. In the following output you will see Replication process is stopped due to Slave_SQL_Running is stopped:

You see “Slave_IO_Running: Yes” but “Slave_SQL_Running: No” that means your Slave IO process is running and retrieving data from Master but couldn’t execute due to Slave_SQL_Running process is stopped.

Apply the following commands:

Step-1: Stop the slave server

Step-2 : Set counter so that slave skip the error sql

Step-3: Start slave

For GTID based replication see the following procedure

You see “Slave_IO_Running: Yes” but “Slave_SQL_Running: No” that means your Slave IO process is running and retrieving data from Master but couldn’t execute due to Slave_SQL_Running process is stopped. You will find the reasons at line

You now need to know which exact GTID needs to skip to start the replication again. You see at the output

It means Slave retrieved the last GTID 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:241806257.
The next line of the output

It means Slave could execute GTIDs upto 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:234830504. So GTIDs 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:234830505 to 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:241806257 are retrieved but pending to be executed.

That means it stopped during execution of GTID 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:234830505(one greater than last executed). So we need to skip this GTID to be executed and this is called “MySQL Replication skip error” tricks. Follow the step by step procedure to apply “MySQL Replication skip error” technique

1. Stop The slave
2. Set global GTID_NEXT variable to intended GTID to be skipped
3. Execute an empty transaction
4. Set global GTID_NEXT variable to AUTOMATIC again
5. Start Slave again
6. Check Slave status again

After issuing “start slave” command , you may again face the same problem. You may need to repeat the procedure until replication process is started without error. Want to do it automatically, read the article “fixing mysql replication error automatically” .

Top Reviewed Books regarding MySQL at amazon.com

admin

7 thoughts on “ MySQL Replication skip error ”

I tried to run the command manually and this automated command, however I noticed that the fields: Retrieved_Gtid_Set: and Executed_Gtid_Set: are in the bank, why can not correct the error and run the script.

Follow my error screen:

SET GTID_NEXT = ‘46620f04-9913-11e6-9b69-000c29f31b4f: 161024’;

mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: x.x.x.x
Master_User: replicacao
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 1697992
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 149077
Relay_Master_Log_File: mysql-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1032

Last_Error: Could not execute Update_rows event on table cyber.radacct; Can’t find record in ‘radacct’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log mysql-bin.000009, end_log_pos 276553

Skip_Counter: 0
Exec_Master_Log_Pos: 275864
Relay_Log_Space: 1571785
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1032

Last_SQL_Error: Could not execute Update_rows event on table cyber.radacct; Can’t find record in ‘radacct’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log mysql-bin.000009, end_log_pos 276553

Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 46620f04-9913-11e6-9b69-000c29f31b4f
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 161024 16:02:45
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:

Can you help me?

Does anybody have the solution? I have exactly the same problem

Источник

MySQL GTID Replication Error

MySQL GTID replication error can happen unexpectedly for various reasons and as often as the classic master slave, circular or multi-master replication but in this short tutorial we will learn, step by step, how to skip MySQL GTID transaction error in order to keep in sync our MySQL slave server. Before we begin we need to say that all below steps were tested in a master-slave setup using MySQL 5.6 and MySQL 5.7, we think that this MySQL skipping transaction method detailed below can be easily applied even for newer MySQL versions like 8+. Please note that all below operations were taken on the slave server only, no master queries or changes were needed.

Table of Contents

MySQL GTID Replication Error

Show MySQL Slave Status via CLI

Let’s start by checking our MySQL slave server status via CLI by executing show slave status G query like shown in the example below:

We can clearly see on the row called Slave_SQL_Running that our slave isn’t executing any new SQL queries received from the master due a SQL query error like shown on the row Last_SQL_Error .

Stop MySQL Slave

Our next step is to stop MySQL slave by executing a SQL query in order to fix the replication error, we won’t stop mysql / mysqld service (daemon) for this we just need to stop the process that handles the replication. Let’s run the SQL query that will stop the slave process for us:

By executing once again the SQL query show slave statusG we should be able now to see that our slave is stopped and no replication is happening between master and slave:

Fix MySQL GTID Replication Error

Knowing now that our MySQL replication is stopped let’s take a closer look at the error message.

The error message says that the ALTER USER query failed for the user named dummyuser . Let’s check our mysql.user table to identify the issue with this query.

We can now see the differences between the SQL query that’s failing and the actual data that’s in our table. First issue that we notice is present in the host column, the error shows users host as being 10.1.%.% but in our table we have 10.% . The second issue is with authentication_string column, we can see that the password has been updated and it doesn’t match anymore. The conclusion here is that the password for our dummyuser has been updated on the master. On the slave server we didn’t had that record to match the criteria, meaning that someone else updated the record on the slave manually by replacing the host entry with 10.% .

To avoid such situations make sure that you never perform any changes on slave, all SQL changes must be carried out on the master server as this is the data source for our slave.

Let’s fix this error by updating our record on the slave server.

Using the queries above we’ve updated our host and authentication_string (hashed password) records now to match with the records from our master server. Now we have the same data on the slave as on the master, no data was lost.

Skip MySQL GTID Replication Error

The classic method for skipping master-slave errors won’t work in this particular case as our replication is GTID.

The error above is expected as we’re running a MySQL GTID replication setup.

Let’s have a look on the last transaction executed on the slave that failed:

We know now that the transaction number 59506053 coming from our master server having the ID bdf3bf63-0cc9-11e8-89e8-000d3a365fa6 failed.

Having now the record fixed manually on the slave we can carry on and insert an empty transaction in order to bypass the error. We’ll need to increment the transaction via a SQL query like shown below in order to bypass the error.

Now we’ll have to commit our change:

Start MySQL Slave

Having everything in place like transaction number incremented and change committed we can try to start our GTID slave replication back with the next SQL query:

A successful show slave statusG will look like this:

Check MySQL Slave Replication Status

We know that our slave is running now, receiving data from the master and we can check how far the slave is behind the master like this:

Once the Seconds_Behind_Master value goes down to 0 seconds then we can say that our slave is fully synced.

Источник

Another reason why SQL_SLAVE_SKIP_COUNTER is bad in MySQL

It is everywhere in the world of MySQL that if your replication is broken because an event caused a duplicate key or a row was not found and it cannot be updated or deleted, then you can use ‘ STOP SLAVE ; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 ; START SLAVE ; ‘ and be done with it. In some cases this is fine and you can repair the offending row or statements later on. But what if the statement is part of a multi-statement transaction? Well, then it becomes more interesting, because skipping the offending statement will cause the whole transaction to be skipped. This is well documented in the manual by the way. So here’s a quick example.

3 rows on the master:

Execute a transaction on the master to break replication:

An attempt to fix replication only caused bigger inconsistencies on slave:

This happens because the replication honors transaction boundaries, and is definitely something you should consider the next time you try to use this workaround on a broken slave. Of course, there is pt-table-checksum and pt-table-sync to rescue you when inconsistencies occur, however, prevention is always better than cure. Make sure to put safeguards in place to prevent your slaves from drifting.

Lastly, the example above is for ROW-based replication as my colleague pointed out, but can similarly happen with STATEMENT for example with a duplicate key error. You can optionally fix the error above by temporarily setting slave_exec_mode to IDEMPOTENT so errors because of missing rows are skipped, but then again, it does not apply in all cases like an UPDATE statement that cannot be applied because the row on the slave is missing.

Here is a demonstration of the problem with STATEMENT-based replication:

As Senior Consultant, Jervin partners with Percona’s customers on building reliable and highly performant MySQL infrastructures while also doing other fun stuff like watching cat videos on the internet. Jervin joined Percona in Apr 2010.

An eye opener. Cool post.

I wonder, how anyone can use in production MySQL database knowing, that every thing is made “partial”. We give you row based replication which is, quoting from manual: “(..) is the same as in most other database management systems; knowledge about other systems transfers to MySQL.”, but hey – it can make your slave drift, because we broken something. Of course you can report this to our bug system, and maybe in this decade we will fix it.

Yes, this is my point of view and maybe I dramatize to much. But for example – PostgreSQL had make it replication “slave drift safe”. I wonder how they could done that and MySQL is unable to do it for a long time.

And now technical question. What is the reason that such errors are likely to happen? I mean – database is running and some day you noticed that your slave is out if sync? Mysql is missing something like CRCs for records? I assume that queries sent to master are written to be “replication safe”.

Источник

Раскрываем магию MySQL или о строгости и мягкости MySQL

Очень часто в интернете встречаюсь со статьями, в которых приводят кучу примеров с якобы странным поведением MySQL по сравнению с другими БД. Чтобы стало понятно, о чём я говорю, приведу несколько примеров:
1. Деление на ноль возвращает NULL вместо ошибки
2. Выход за диапазон допустимых значений неявно приводит число к допустимому значению, а не к ошибке и откату транзакции
3. Вставка неверного типа данных также приводит к неявному преобразованию и успешному выполнению операции
Таких примеров я могу привести огромное число, но цель статьи не сделать очередное собрание высосанных из пальца примеров, а объяснить, почему происходит то или иное действие. Вся эта мистика MySQL давно описана в документации и легко объяснима, в чём вы сможете убедиться сами, прочитав статью до конца.
Для меня эта первая статья на хабре, поэтому я старался писать дотошно подробно. Уверен, что она будет полезна всем, кто работает с MySQL. Большую помощь в написании статьи оказала подготовка к сдаче на сертификат разработчика MySQL, а точнее книга «MySQL Certification Study Guide».
Итак, мой друг, начнём!

SQL Modes

SQL modes – это настройка поведения работы сервера MySQL, состоящая из режимов, каждый из которых контролирует какой-либо один аспект обработки запроса.

Возможности SQL mode:
1. Устанавливает строгую или мягкую проверку входных данных
2. Включает или отключает следование SQL стандарту
3. Обеспечивает лучшую синтаксическую совместимость с другими БД
По сути, SQL mode очень мощный механизм тюнинга БД, позволяющий гибко манипулировать обработкой запросов и уведомлениями MySQL.

Прежде чем мы перейдём к последующей теории, вы должны строго-настрого уяснить, что изменение режима SQL mode после создания и вставки данных в партиционные таблицы (partitioning tables) может привести к существенным изменениям в поведении таких таблиц, что, в свою очередь, может привести к потере или повреждению данных. Настоятельно рекомендуется, чтобы вы никогда не изменяли SQL режим после создания партиционных таблиц.
При репликации партиционных таблиц, отличающиеся параметры SQL mode на Primary и Slave MySQL серверах также может привести к проблемам. Для стабильной работы репликации между серверами, настройки SQL mode должны быть идентичными.

Теперь, после того, как вы осознали всю ответственность в использовании SQL режимов, перейдём к его сути.

Контроль текущего SQL режима происходит через системную переменную sql_mode. Для задания значения используется команда SET. Ниже представлены возможные варианты установки данного режима.

1. Соответствует значению по умолчанию для только что установленной БД (никаких специальных режимов не установлено). Кавычки являются обязательными.

2. Установка одного режима sql_mode. Возможно два варианта – с кавычками и без них.

3. Установка нескольких режимов sql_mode. Указание кавычек является обязательным!

Несмотря на то, что названия режимов регистронезависимые, я для удобства прочтения буду везде в статье писать их в верхнем регистре.

В примерах выше мы устанавливали режимы для текущей сессии, но если вы обладаете привилегиями суперпользователя, то можно задать глобальный режим для всего сервера и всех текущих коннектов, указав параметр GLOBAL. Полный синтаксис установки sql_mode выглядит так:

Для просмотра текущих значений глобального и сессионного режима сервера используйте следующие запросы:

Помимо команды SET существует ещё два способа задания режима работы БД:
1. Запустить сервер с опцией —sql-mode=» »
2. Установить в файле my.cnf (для unix подобных систем) или my.ini (для windows) параметр sql-mode=» «

Краткий справочник режимов

ANSI_QUOTES

Заставляет сервер интерпретировать двойную кавычку ( » ) точно также, как и обратную кавычку ( ` ), при этом она теряет способность обрамлять строки. Как можно было догадаться, этот режим заставляет MySQL приблизиться к SQL стандарту.

IGNORE_SPACE

По умолчанию, между функцией и открывающейся круглой скобкой нельзя устанавливать пробелы. Включение этого режима разрешает серверу игнорировать пробелы, но платой за такую вольность станет то, что все функции станут зарезервированными словами, а значит, при совпадении имени столбца с именем функции придётся в обязательном порядке экранировать такой столбец.

ERROR_FOR_DIVISION_BY_ZERO

При делении на ноль в строгом режиме генерируется ошибка, а нестрогом — предупреждение при выполнении операторов INSERT или UPDATE. Без этого параметра деление на ноль возвращает предупреждение и вставляет в таблицу NULL. Про строгость будет сказано в следующем режиме, пока постарайтесь абстрагироваться.

В приведённых примерах мы получали исключительно предупреждения, потому что строгий режим был выключен. Понимание строгости очень важное понятие для БД MySQL, потому что в классических базах данных такого нет. Забегая вперёд, скажу, что все БД изначально строгие и не позволяют тех вольностей, которые есть в MySQL. Мягкость MySQL сложилась исторически, когда ещё не было InnoDB. Посудите сами, в нетранзакционных таблицах действуют совершенно другие правила, нежели чем в транзакционных, поэтому следование жестким правилам зачастую приводило бы к нежелательному результату.

STRICT_TRANS_TABLES

Включает «строгий режим» для всех таблиц, поддерживающих транзакции, т.е. на InnoDB и BDB. Этот режим возвращает ошибку, вместо предупреждения в следующих случаях:
1. Тип входных данных не соответствует заданному типу. Например, вставка строки в колонку c числовым типом
2. Число или дата находится вне допустимого диапазона. Диапазон определяется типом данных. Например, для типа unsigned tinyint допустимым диапазоном являются числа от 0 до 255
3. При вставке данных пропущено значение колонки, для которой не задано значение по умолчанию и имеет атрибут NOT NULL
4. Длина значения выходит за пределы заданного диапазона. Например, для колонки типа CHAR(5) вы не сможете вставить строку более 5 символов
5. Для типов ENUM и SET отсутствует вставляемое или обновляемое значение
Более подробно об особенностях работы данного режима будет рассказано отдельно в последующей ниже главе.

STRICT_ALL_TABLES

STRICT_ALL_TABLES полностью идентично STRICT_TRANS_TABLES, но действие режима уже распространяется на все таблицы MySQL, а не только на транзакционные.
Из-за разницы подходов к работе транзакционных и не транзакционных таблиц не всегда есть смысл использовать данный режим. Если это вам ещё не очевидно, то в главах о строгом и нестрогом режимах вы поймёте разницу.

TRADITIONAL

Композитный режим, включает в себя целый набор режимов, в который входит «строгий режим», а также ряд других режимов, налагающих ограничения на входные данные.
Заставляет MySQL вести себя как большинство «традиционных» баз данных SQL.
Посмотрим на полный перечень режимов, который содержит в себе данный режим.

Другой композитный режим, делающий MySQL «ANSI-подобным», т.е. приближенным к стандарту SQL.
Включает в себя следующие режимы: REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE.
Последние два режима были обсуждены ранее, поэтому кратко опишу первые два:
REAL_AS_FLOAT – тип данных real является синонимом float, а не double.
PIPES_AS_CONCAT – разрешает использовать для конкатенации строк ( || ), вместо логического ИЛИ.

ONLY_FULL_GROUP_BY

Генерирует ошибку в запросах, в которых GROUP BY имеет не полный список не агрегированных параметров из SELECT и HAVING.

Если вы желаете узнать обо всех SQL mode режимах и окунуться глубже в проблему, то милости прошу в официальную документацию http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html

Работа с SQL mode в PHP

По правде сказать, данную главу вряд ли можно назвать прикладной, потому что в реальных проектах конфигурировать нужно непосредственно на сервере MySQL, а не средствами языка программирования, поэтому глава скорее теоретическая, но для общего развития неплохо иметь ввиду и такой способ.
Чаще всего соединение с БД происходит через экземпляр класса PDO, поэтому рассмотрим его в деталях.
Есть два способа передать в БД специальные инструкции. Первый способ – передача в конструкторе. Посмотрим на полное описание конструктора:

Думаю, с первыми тремя параметрами всё и так понятно, а вот $driver_options как раз таки предоставляет нам возможность выполнять запросы непосредственно в БД. Для задания системной переменной sql_mode режимом TRADITIONAL, будем делать так:

Второй способ – конфигурирование на лету, через метод setAttribute;

Конечно, некоторые из вас могут возразить, что можно выполнять запросы методом query или exec, но поскольку изначально глава теоретическая, то не буду заострять внимание на таком способе.
Дополнительно о PDO можно прочитать в официальной документации php.net/manual/ru/book.pdo.php
Предопределённые PDO константы для работы с MySQL php.net/manual/ru/ref.pdo-mysql.php

Строгий режим

Мы уже немного познакомились со строгим режимом в главе SQL Mode, когда изучали режимы STRICT_TRANS_TABLES, STRICT_ALL_TABLES и композитный TRADITIONAL. Уже из самого названия легко догадаться, что все входные данные проверяются с особой тщательностью и в случае нарушений любых ограничений, вас неминуемо будет ждать ошибка.
Ошибка в транзакционных таблицах вызывает откат транзакции (rollback). Даже если ваши запросы не предварены командой start transaction, то неявно каждый запрос в отдельности по-любому будет обёрнут командами start transaction и commit. Так работают все традиционные БД, что в равной степени относится и к транзакционным таблицам MySQL. Из этого следует, что нарушив ограничение, вызывается rollback, который откатывает все изменения.
Для не транзакционных таблиц всё немного сложнее. Так, при вставке, обновлении или удалении нескольких строк, в случае ошибки отменяется только последнее действие, вместо полного отката. Проиллюстрирую это на примере.

Генерация ошибки происходит в следующих случаях:
1. Тип вставляемых данных отличается от заданного типа столбца
2. Опущено значение для столбца, которому не задано значение по умолчанию и имеет атрибут NOT NULL
3. Для чисел и дат – данные находятся вне диапазона допустимых значений
4. Для строк – превышение допустимой длины
5. Для типов ENUM и SET – значение не является допустимым для заданного перечисления
6. Для столбца, определённого как NOT NULL — вставка NULL

Дефолтные значения для типов данных

Если в insert запросе не указаны данные для одной из колонок, то MySQL будет обрабатывать эту ситуацию в следующем порядке:
1. Если столбец имеет значение по умолчанию, то используется это значение и на этом всё заканчивается, в противном случае происходит переход к следующему шагу
2. Если столбец не имеет параметр NOT NULL, то присваивается NULL и на этом всё заканчивается, в противном случае поведение зависит от переменной sql_mode, точнее от строгости самого режима.

Как можно было догадаться из предыдущей главы, строгий режим сразу вернёт ошибку, которая в свою очередь откатит транзакцию для транзакционных таблиц или отменит последнее действие для не транзакционных таблиц.

Для нестрого режима будет вставлено значение по умолчанию для заданного типа данных.
Значения по умолчанию для каждого типа данных:

• Для типов int и float значение по умолчанию ноль (0)
• Для всех строк, кроме типа enum – пустая строка (»)
• Для enum – первый нумерованный член
• Для даты и времени – нулевые значения ‘0000-00-00 00:00:00’
• Для timestamp — текущая дата и время, если колонка была автоматически инициализирована, либо нулевыми значениями ‘0000-00-00 00:00:00’

Нестрогий режим

Ура! Наконец-то, мы добрались до самой «загадочной» части статьи, которую некоторые освещают как некую магию MySQL, но, увы, это лишь фокусы на потеху детей. И так, поехали!
Возможно, нужно было ранее описать все случаи, для которых применяются правила проверки данных, но я решил сделать это только сейчас. Их всего три, но каждый из них требует отдельного рассмотрения.
Запросы на модификация данных: INSERT, UPDATE, REPLACE, LOAD DATA INFILE
Обновление описания схем таблиц: ALTER TABLE
Задание значения по умолчанию (DEFAULT) в описании колонки

Напоминаю, что в строгом режиме некорректные данные приведут к генерации ошибки и откат данных, а в нестрогом – значение будет не явно приведено к корректному значению и сгенерировано предупреждение. Для просмотра ошибок используйте SHOW WARNINGS.
Ниже будут подробно рассмотрены все случаи обработки некорректных значений и их разрешений на уровне БД.

Выход из диапазона допустимых значений

Если число меньше минимального значения допустимого диапазона, то присваивается минимально-допустимое число. Если больше максимального – максимально-допустимое.

Обработка строк

Строки длиннее заданной длины — усекаются.

ENUM и SET типы данных

Если присваиваемое значение колонке с типом ENUM не перечислено в определении ENUM, то MySQL преобразует его в пустую строку.
Если значение, которое присваивается SET столбцу содержит элементы, которые не перечислены в определении SET, то MySQL отбрасывает эти элементы, сохраняя значения только легальным элементам.

Преобразование в тип даты

При попытке сохранения значения, которое не может быть преобразовано в тип данных столбца, MySQL неявно преобразует его в значение по умолчанию для данного типа.

Таблица преобразований

Присвоение NULL для колонки с NOT NULL

Результат зависит от того, будет вставлена одна строка или множество в INSERT запросе.
При вставке одной строки возникает ошибка и изменения не применяются. При множественной вставке — MySQL неявно преобразует значение по умолчанию для этого типа данных.

Обновление описания схем таблиц: ALTER TABLE

При изменении типа данных на колонку налагаются ограничения нового типа, что может привести к неожиданному изменению самих данных по правилам описанных выше.

Если на колонку налагается ограничение NOT NULL, то все NULL значения конвертируются в значения по умолчанию для заданного типа данных текущей колонки. Значения по умолчанию описаны в главе «Дефолтные значения для типов данных»

Задание значения по умолчанию (DEFAULT) в описании колонки

Вообще, всё уже было сказано в прошлой главе, поэтому здесь нечего добавить.

Ну что ж, мой дорогой читатель. Теперь ты можешь по-праву назваться настоящим джедаем и получить чёрный пояс)))

Извлекаем плюсы

IGNORE

Ключевое слово IGNORE заставляет MySQL включать для такого запроса нестрогий режим. Также его можно использовать для генерации предупреждения вместо ошибки, при нарушении целостности первичного ключа (PRIMARY KEY) или уникальности (UNIQUE).

ON DUPLICATE KEY UPDATE

Такой вид запроса не совсем идеальный пример, но включил, чтобы лишний раз вспомнить, что такое вообще существует. Используется для выполнения вставки данных, либо обновлении при нарушении ограничения целостности первичного ключа (PRIMARY KEY) или уникальности (UNIQUE).

Источник

MySQL GTID replication error can happen unexpectedly for various reasons and as often as the classic master slave, circular or multi-master replication but in this short tutorial we will learn, step by step, how to skip MySQL GTID transaction error in order to keep in sync our MySQL slave server. Before we begin we need to say that all below steps were tested in a master-slave setup using MySQL 5.6 and MySQL 5.7, we think that this MySQL skipping transaction method detailed below can be easily applied even for newer MySQL versions like 8+. Please note that all below operations were taken on the slave server only, no master queries or changes were needed.

Table of Contents

MySQL GTID Replication Error

This tutorial about MySQL GTID Replication Error contains the next sections:
Show MySQL Slave Status via CLI
Stop MySQL Slave
Fix MySQL GTID Replication Error
Skip MySQL GTID Replication Error
Start MySQL Slave
Check MySQL Slave Replication Status

Show MySQL Slave Status via CLI

Let’s start by checking our MySQL slave server status via CLI by executing show slave status G query like shown in the example below:

mysql> SHOW SLAVE STATUS G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 10.1.1.1
                  Master_User: dbrepuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin.000706
          Read_Master_Log_Pos: 374830771
               Relay_Log_File: dbm-relay-bin.000713
                Relay_Log_Pos: 959223458
        Relay_Master_Log_File: bin.000691
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1396
                   Last_Error: Error 'Operation ALTER USER failed for 'dummyuser'@'10.1.%.%'' on query. Default database: 'mysql'. Query: 'ALTER USER 'dummyuser'@'10.1.%.%' IDENTIFIED WITH 'mysql_native_password' AS '*1D1219A2256FC58143DF98BA9457EFE385AD7B2C''
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 959223257
              Relay_Log_Space: 16698942535
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1396
               Last_SQL_Error: Error 'Operation ALTER USER failed for 'dummyuser'@'10.1.%.%'' on query. Default database: 'mysql'. Query: 'ALTER USER 'dummyuser'@'10.1.%.%' IDENTIFIED WITH 'mysql_native_password' AS '*1D1219A2256FC58143DF98BA9457EFE385AD7B2C''
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: bdf3bf63-0cc9-11e8-89e8-000d3a365fa6
             Master_Info_File: /mnt/mysql-data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 180628 06:29:51
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: bdf3bf63-0cc9-11e8-89e8-000d3a365fa6:58869595-59928201
            Executed_Gtid_Set: b2c84f0c-5dcb-11e8-a550-005056847b4c:1-13, bdf3bf63-0cc9-11e8-89e8-000d3a365fa6:52044900-59506053
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

We can clearly see on the row called Slave_SQL_Running that our slave isn’t executing any new SQL queries received from the master due a SQL query error like shown on the row Last_SQL_Error.

Stop MySQL Slave

Our next step is to stop MySQL slave by executing a SQL query in order to fix the replication error, we won’t stop mysql/mysqld service (daemon) for this we just need to stop the process that handles the replication. Let’s run the SQL query that will stop the slave process for us:

mysql> STOP SLAVE;

By executing once again the SQL query show slave statusG we should be able now to see that our slave is stopped and no replication is happening between master and slave:

mysql> SHOW SLAVE STATUS G
...
Slave_IO_Running: No
Slave_SQL_Running: No
...

Fix MySQL GTID Replication Error

Knowing now that our MySQL replication is stopped let’s take a closer look at the error message.

Last_SQL_Error: Error 'Operation ALTER USER failed for 'dummyuser'@'10.1.%.%'' on query. Default database: 'mysql'. Query: 'ALTER USER 'dummyuser'@'10.1.%.%' IDENTIFIED WITH 'mysql_native_password' AS '*1D1219A2256FC58143DF98BA9457EFE385AD7B2C''

The error message says that the ALTER USER query failed for the user named dummyuser. Let’s check our mysql.user table to identify the issue with this query.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select user, host, authentication_string from user where user like '%dummyuser%';
+----------------+------+-------------------------------------------+
| user           | host | authentication_string                     |
+----------------+------+-------------------------------------------+
| dummyuser      | 10.% | *1B2319A2556FC59343FE08BC9027EFF385AD7A1D |
+----------------+------+-------------------------------------------+
1 row in set (0.00 sec)

We can now see the differences between the SQL query that’s failing and the actual data that’s in our table. First issue that we notice is present in the host column, the error shows users host as being 10.1.%.% but in our table we have 10.%. The second issue is with authentication_string column, we can see that the password has been updated and it doesn’t match anymore. The conclusion here is that the password for our dummyuser has been updated on the master. On the slave server we didn’t had that record to match the criteria, meaning that someone else updated the record on the slave manually by replacing the host entry with 10.%.

To avoid such situations make sure that you never perform any changes on slave, all SQL changes must be carried out on the master server as this is the data source for our slave.

Let’s fix this error by updating our record on the slave server.

mysql> UPDATE user SET host='10.1.%.%' WHERE host='10.%' AND user='dummyuser';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> ALTER USER 'dummyuser'@'10.1.%.%' IDENTIFIED WITH 'mysql_native_password' AS '*1D1219A2256FC58143DF98BA9457EFE385AD7B2C';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Using the queries above we’ve updated our host and authentication_string (hashed password) records now to match with the records from our master server. Now we have the same data on the slave as on the master, no data was lost.

Skip MySQL GTID Replication Error

The classic method for skipping master-slave errors won’t work in this particular case as our replication is GTID.

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction

The error above is expected as we’re running a MySQL GTID replication setup.

Let’s have a look on the last transaction executed on the slave that failed:

Executed_Gtid_Set: b2c84f0c-5dcb-11e8-a550-005056847b4c:1-13, bdf3bf63-0cc9-11e8-89e8-000d3a365fa6:52044900-59506053

We know now that the transaction number 59506053 coming from our master server having the ID bdf3bf63-0cc9-11e8-89e8-000d3a365fa6 failed.

Having now the record fixed manually on the slave we can carry on and insert an empty transaction in order to bypass the error. We’ll need to increment the transaction via a SQL query like shown below in order to bypass the error.

mysql> SET GTID_NEXT='bdf3bf63-0cc9-11e8-89e8-000d3a365fa6:59506054';

Now we’ll have to commit our change:

mysql> BEGIN; COMMIT; SET GTID_NEXT='AUTOMATIC';

Start MySQL Slave

Having everything in place like transaction number incremented and change committed we can try to start our GTID slave replication back with the next SQL query:

mysql> START SLAVE;

A successful show slave statusG will look like this:

mysql> SHOW SLAVE STATUS G
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...

Check MySQL Slave Replication Status

We know that our slave is running now, receiving data from the master and we can check how far the slave is behind the master like this:

mysql> SHOW SLAVE STATUS G
...
Seconds_Behind_Master: 168356
...

Once the Seconds_Behind_Master value goes down to 0 seconds then we can say that our slave is fully synced.

mysql> SHOW SLAVE STATUS G
...
Seconds_Behind_Master: 0
...