[MDEV-13520] InnoDB attempts UPDATE with DB_TRX_ID=0 if innodb_force_recovery=3 - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.2.7
Fix Version/s: 10.2.8
Component/s: Storage Engine - InnoDB
Labels:
None

Description

InnoDB in MariaDB 10.2 fails to refuse UPDATE if the server is in read-only mode, and will hit a debug assertion failure because trx->id is 0 due to the read-only mode. This can be repeated with a slightly modified test:

diff --git a/mysql-test/suite/innodb/t/read_only_recovery.test b/mysql-test/suite/innodb/t/read_only_recovery.test

index a1a69be724b..aea676d3644 100644

--- a/mysql-test/suite/innodb/t/read_only_recovery.test

+++ b/mysql-test/suite/innodb/t/read_only_recovery.test

@@ -4,9 +4,10 @@

 --connect(con1, localhost, root)

 CREATE TABLE t(a INT PRIMARY KEY) ENGINE=InnoDB;

+INSERT INTO t VALUES(1);

 BEGIN;

 # Generate insert_undo log.

-INSERT INTO t VALUES(1),(2);

+INSERT INTO t VALUES(2);

 # Generate update_undo log.

 DELETE FROM t WHERE a=2;

 --connection default

@@ -27,6 +28,7 @@ ROLLBACK;

 SELECT * FROM t;

 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

 SELECT * FROM t;

+UPDATE t SET a=3 WHERE a=1;

 --let $restart_parameters= --innodb-read-only

 --source include/restart_mysqld.inc

 --echo # Starting with MariaDB 10.2, innodb_read_only implies READ UNCOMMITTED.

10.0 and 10.1 are behaving correctly:

CURRENT_TEST: innodb.read_only_recovery

mysqltest: At line 29: query 'UPDATE t SET a=3 WHERE a=1' failed: 1036: Table 't' is read only

Attachments

Issue Links

relates to

MDEV-13269 Upgrade tests for InnoDB undo log

Closed

Activity

Ascending order - Click to sort in descending order

Marko Mäkelä created issue - 2017-08-14 12:38

Marko Mäkelä made changes - 2017-08-14 12:38

Field	Original Value	New Value
Link		This issue relates to ~~MDEV-13269~~ [ ~~MDEV-13269~~ ]

Marko Mäkelä made changes - 2017-08-14 12:55

Summary

InnoDB fails to refuse UPDATE if innodb_force_recovery=3

InnoDB attempts UPDATE with DB_TRX_ID=0 if innodb_force_recovery=3

Marko Mäkelä made changes - 2017-08-14 13:02

Status

Open [ 1 ]

Confirmed [ 10101 ]

Marko Mäkelä made changes - 2017-08-14 13:02

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Marko Mäkelä added a comment - 2017-08-14 13:04

bb-10.2-marko will continue to allow the UPDATE, and start to allow the read-write transaction creation even if innodb_force_recovery=3.

Marko Mäkelä added a comment - 2017-08-14 13:04 bb-10.2-marko will continue to allow the UPDATE, and start to allow the read-write transaction creation even if innodb_force_recovery=3.

Marko Mäkelä made changes - 2017-08-14 13:04

Assignee	Marko Mäkelä [ marko ]	Jan Lindström [ jplindst ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Marko Mäkelä added a comment - 2017-08-14 13:49

The behaviour change could actually have been introduced in MariaDB 10.2.7 by Do allow writes for innodb_force_recovery=2 or 3.

Marko Mäkelä added a comment - 2017-08-14 13:49 The behaviour change could actually have been introduced in MariaDB 10.2.7 by Do allow writes for innodb_force_recovery=2 or 3 .

Jan Lindström (Inactive) added a comment - 2017-08-15 05:34

Ok to push. Can we document the force_recovery values and their actual meaning better somewhere in KB?

Jan Lindström (Inactive) added a comment - 2017-08-15 05:34 Ok to push. Can we document the force_recovery values and their actual meaning better somewhere in KB?

Jan Lindström (Inactive) made changes - 2017-08-15 05:34

Assignee	Jan Lindström [ jplindst ]	Marko Mäkelä [ marko ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Marko Mäkelä added a comment - 2017-08-15 08:06

I think that we should deprecate innodb_force_recovery and replace it with something better. The option is bundling unrelated subsystems into one linear value.

Here is a short description:

innodb_force_recovery>=1 makes redo log based recovery ignore certain errors, such as missing data files or corrupted data pages. (Any redo log for affected files or pages will be skipped.)

innodb_force_recovery>=2 prevents purge from running. So, the undo logs would keep growing.

innodb_force_recovery>=3 disables the rollback of recovered transactions. It does not affect the rollback of currently active transactions.

Starting with MariaDB Server 10.2.7 write transactions are allowed with innodb_force_recovery<=3. But in that change, I forgot to change the trx_set_rw_mode(), introducing this very bug.

Starting with MariaDB Server 10.2.7, innodb_force_recovery>=3 will prevent some undo-generating background tasks from running. (These tasks could hit a lock wait due to the recovered incomplete transactions whose rollback is being prevented.)

Note: innodb_force_recovery>=4 is an invitation to corrupt the database, and should only be used as a last resort for dumping data when no backups exist.

Marko Mäkelä added a comment - 2017-08-15 08:06 I think that we should deprecate innodb_force_recovery and replace it with something better. The option is bundling unrelated subsystems into one linear value. Here is a short description: innodb_force_recovery>=1 makes redo log based recovery ignore certain errors, such as missing data files or corrupted data pages. (Any redo log for affected files or pages will be skipped.) innodb_force_recovery>=2 prevents purge from running. So, the undo logs would keep growing. innodb_force_recovery>=3 disables the rollback of recovered transactions. It does not affect the rollback of currently active transactions. Starting with MariaDB Server 10.2.7 write transactions are allowed with innodb_force_recovery<=3. But in that change, I forgot to change the trx_set_rw_mode(), introducing this very bug. Starting with MariaDB Server 10.2.7 , innodb_force_recovery>=3 will prevent some undo-generating background tasks from running. (These tasks could hit a lock wait due to the recovered incomplete transactions whose rollback is being prevented.) Note: innodb_force_recovery>=4 is an invitation to corrupt the database, and should only be used as a last resort for dumping data when no backups exist.

Marko Mäkelä made changes - 2017-08-15 08:06

issue.field.resolutiondate

2017-08-15 08:06:59.0

2017-08-15 08:06:59.685

Marko Mäkelä made changes - 2017-08-15 08:06

Fix Version/s		10.2.8 [ 22544 ]
Fix Version/s	10.2 [ 14601 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Marko Mäkelä made changes - 2017-08-15 08:07

Affects Version/s		10.2.7 [ 22543 ]
Affects Version/s	10.2 [ 14601 ]

Ian Gilfillan added a comment - 2017-08-15 08:51

I have expanded the docs at https://mariadb.com/kb/en/mariadb/xtradbinnodb-recovery-modes/ based on the above. Please take a look and let me know of any suggested changes, or go ahead and make them yourselves.

Ian Gilfillan added a comment - 2017-08-15 08:51 I have expanded the docs at https://mariadb.com/kb/en/mariadb/xtradbinnodb-recovery-modes/ based on the above. Please take a look and let me know of any suggested changes, or go ahead and make them yourselves.

Sergei Golubchik made changes - 2021-12-06 21:45

Workflow

MariaDB v3 [ 82071 ]

MariaDB v4 [ 152614 ]

People

Assignee:: Marko Mäkelä

Reporter:: Marko Mäkelä

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2017-08-14 12:38

Updated:: 2017-08-15 08:51

Resolved:: 2017-08-15 08:06

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.