innodb_fake_changes causes SELECTs to fail if autocommit enabled

[Valentine Gostev]

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set innodb_fake_changes=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
ERROR 1180 (HY000): Got error 131 during COMMIT

Expected to get a valid query result. Same result for insert and update.

[Valentine Gostev (longbow)]

In the linked branch patch was included.
Test percona_innodb_fake_changes_select added to patch

CURRENT_TEST: main.percona_innodb_fake_changes_select
mysqltest: At line 13: query 'SELECT * FROM t1' failed: 1180: Got error 131 during COMMIT

The result from queries just before the failure was:
< snip >
INSERT INTO t1 values (123);
SELECT * FROM t1;
a
123
SHOW VARIABLES LIKE 'innodb_fake_changes';
Variable_name Value
innodb_fake_changes OFF
SET innodb_fake_changes=1;
SHOW VARIABLES LIKE 'innodb_fake_changes';
Variable_name Value
innodb_fake_changes ON

[Yasufumi Kinoshita (yasufumi-kinoshita)]

It is intended that
all transactions should be ended by rollback for innodb_fake_changes=ON.

As transactional RDBMS, any fake commit should not be allowed.
For safeness, all commit are treated as error, for now.

(technical reason: fake change doesn't cause any logs and we cannot distinguish modifying transaction by whether the transaction has modified logs or not.)

Thinking the objective of the function "for mksp+ ?",
it should not be problem.

This behavior is not bug, I intended.

[Stewart Smith (stewart)]

On Tue, 23 Aug 2011 10:14:43 -0000, Yasufumi Kinoshita <email address hidden> wrote:
> all transactions should be ended by rollback for innodb_fake_changes=ON.
>
> As transactional RDBMS, any fake commit should not be allowed.
> For safeness, all commit are treated as error, for now.

This is with autocommit=0 though, so how otherwise do you run read-only
transactions?

--
Stewart Smith

[Yasufumi Kinoshita (yasufumi-kinoshita)]

This function is for mk-slave-prefetch.
Why does the read-only transaction exist?

[Yasufumi Kinoshita (yasufumi-kinoshita)]

This function is for mk-slave-prefetch.
(to avoid that rollback cost. & for minimum processing for the prefetch)

[Peter Zaitsev (pz-percona)]

I do not think we should have features which are For something but rather
what do something
We use them some way in our tool while other people might find different use
for it.

For example I see this as a tool for almost realistic benchmarking where
instead of replying
only selects I can reply all queries (selects and updates) in the read only
mode and make sure
the data stays the same. It will not be as good as real update but it is
better than select for
benchmarking.

It also makes it a lot easier to explain to users - innodb_fake_changes
means changes are not
done is a lot better than long list of side effect what works and what does
not.

On Wed, Aug 24, 2011 at 10:12 PM, Yasufumi Kinoshita <
<email address hidden>> wrote:

> This function is for mk-slave-prefetch.
> Why does the read-only transaction exist?
>
> --
> You received this bug notification because you are a member of Percona
> developers, which is the registrant for Percona Server.
> https://bugs.launchpad.net/bugs/829732
>
> Title:
> innodb_fake_changes causes error on commit during dml
>
> Status in Percona Server with XtraDB:
> Triaged
> Status in Percona Server 5.1 series:
> Triaged
>
> Bug description:
> mysql> set autocommit=0;
> Query OK, 0 rows affected (0.00 sec)
> mysql> set innodb_fake_changes=1;
> Query OK, 0 rows affected (0.00 sec)
> mysql> select * from t1;
> ERROR 1180 (HY000): Got error 131 during COMMIT
>
> Expected to get a valid query result. Same result for insert and
> update.
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/percona-server/+bug/829732/+subscriptions
>

--
Peter Zaitsev, CEO, Percona Inc.
Tel: [phone] ext 501 Skype: peter_zaitsev
24/7 Emergency Line [phone] ext 911

Percona Live MySQL Conference comes to London!
http://www.percona.com/live/london-2011/

[Yasufumi Kinoshita (yasufumi-kinoshita)]

Peter,

I cannot allow such dangerous&insincere function as ACID transactional RDBMS.
The consistency is kept only by disallowing commit operation in the mode.

Your opinion seems only for your curiosity and laziness.
There should be other method for that. This function is not for the objective what you point.

[Yasufumi Kinoshita (yasufumi-kinoshita)]

I mean, I'd like to make the possibility to cause bug 0%, because it is fatal function.

[Peter Zaitsev (pz-percona)]

Yasufumi,

I think implementing this function by disallowing commit it a good one. I
just do not understand why it should
break things in a way described in this but. What if we simply do rollback
instead of commit if this option is
enabled - in such case selects should continue to work at any point in time.

On Wed, Aug 24, 2011 at 11:10 PM, Yasufumi Kinoshita <
<email address hidden>> wrote:

> Peter,
>
> I cannot allow such dangerous&insincere function as ACID transactional
> RDBMS.
> The consistency is kept only by disallowing commit operation in the mode.
>
> Your opinion seems only for your curiosity and laziness.
> There should be other method for that. This function is not for the
> objective what you point.
>
> --
> You received this bug notification because you are a member of Percona
> developers, which is the registrant for Percona Server.
> https://bugs.launchpad.net/bugs/829732
>
> Title:
> innodb_fake_changes causes error on commit during dml
>
> Status in Percona Server with XtraDB:
> Triaged
> Status in Percona Server 5.1 series:
> Triaged
>
> Bug description:
> mysql> set autocommit=0;
> Query OK, 0 rows affected (0.00 sec)
> mysql> set innodb_fake_changes=1;
> Query OK, 0 rows affected (0.00 sec)
> mysql> select * from t1;
> ERROR 1180 (HY000): Got error 131 during COMMIT
>
> Expected to get a valid query result. Same result for insert and
> update.
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/percona-server/+bug/829732/+subscriptions
>

--
Peter Zaitsev, CEO, Percona Inc.
Tel: [phone] ext 501 Skype: peter_zaitsev
24/7 Emergency Line [phone] ext 911

Percona Live MySQL Conference comes to London!
http://www.percona.com/live/london-2011/

[Yasufumi Kinoshita (yasufumi-kinoshita)]

Thanks.

I think we should choose the least risk method.
"disallow commit" is the most certain method, because we should port for every version of mysql update.
Otherwise, we should check all path of the transaction.

[Yasufumi Kinoshita (yasufumi-kinoshita)]

Sorry,

It did not work as I intended.
I fixed at the branch 5.1-kill_idle_transaction-innodb_fake_changes

[Valentine Gostev (longbow)]

Seems this bug is fixed in mentioned branch. Thanks!

[Stewart Smith (stewart)]

So does this mean we'll see a merge request for this?

[Oleg Tsarev (tsarev)]

Valentin, Yasufumi,

What the current status of this bug? Released?

[Laurynas Biveinis (laurynas-biveinis)]

Self-contained testcase from duplicate bug 1067346 (s/have_innodb_plugin/have_innodb for 5.5):

--source include/have_innodb_plugin.inc

--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

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

INSERT INTO t1 VALUES (1);

SET innodb_fake_changes=1;
SELECT * FROM t1; # failed: 1180: Got error 131 during COMMIT

SET innodb_fake_changes=0;
DROP TABLE t1;

[Laurynas Biveinis (laurynas-biveinis)]

If I understand correctly, the safety of this feature is achieved by never, ever letting a commit through. In case a commit is attempted, its transaction is rolled back and error is returned. I think we would not sacrifice any safety by keeping on doing rollbacks instead of commits, but returning success instead of error for certain types of transacations (i.e. SELECTs with autocommit=0).

[Laurynas Biveinis (laurynas-biveinis)]

5.6 needs re-verification as read-only transaction detection might kick in for autocommit=1 SELECTs.

[Valerii Kravchuk (valerii-kravchuk)]

For now PS 5.6.10 just does NOT have this variable:

[openxs@centos Percona-Server-5.6.10-alpha60.2-324.Linux.x86_64]$ bin/mysql --no-defaults --socket=/tmp/vk56.sock -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.10-alpha60.2-log Percona Server with XtraDB (GPL), Release alpha60.2,б═Revisionб═324

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.04 sec)

mysql> SET innodb_fake_changes=1;
ERROR 1193 (HY000): Unknown system variable 'innodb_fake_changes'
...

[Wagner Bianchi (wagnerbianchijr)]

My database operations were affected by this problem with innodb_fake_changes, configuring it as ON. The COMMIT of a transaction returned the same error as reported here. The below procedure has a properly START TRANSACTION and COMMIT/ROLLBACK well defined and it was possible to complete successfully right after tuning off innodb_fake_changes.

mysql> call sp_move_data(xxxxxxxxxx);
...
ERROR 1180 (HY000): Got error 131 during COMMIT

Tuning off the variable:

mysql> set global innodb_fake_changes=off;
Query OK, 0 rows affected (0.00 sec)

Executing the procedure again (if using mysql client, log off and logon)

mysql> call sp_move_data(xxxxxxxxxx);
...
+----------------------------------------+
| This is the end of this process! Ciao! |
+----------------------------------------+
| 2014-06-26 10:03:43 |
+----------------------------------------+
1 row in set (4 min 9.47 sec)

Query OK, 0 rows affected (4 min 9.47 sec)

[Valerii Kravchuk (valerii-kravchuk)]

I can not reproduce on PS 5.6 with the original test case:

openxs@ao756:~$ mysql -uroot -proot test
Warning: Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 5.6.17-66.0 Percona Server (GPL), Release 66.0, Revision 608

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table t1(id int primary key, c1 int) engine=InnoDB;
Query OK, 0 rows affected (0,33 sec)

mysql> insert into t1 values(1,1);
Query OK, 1 row affected (0,05 sec)

mysql> select * from t1;
+----+------+
| id | c1 |
+----+------+
| 1 | 1 |
+----+------+
1 row in set (0,00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0,00 sec)

mysql> set innodb_fake_changes=1;
Query OK, 0 rows affected (0,00 sec)

mysql> select * from t1;
+----+------+
| id | c1 |
+----+------+
| 1 | 1 |
+----+------+
1 row in set (0,00 sec)

mysql> show variables like 'innodb_fake%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| innodb_fake_changes | ON |
+---------------------+-------+
1 row in set (0,01 sec)

So, we obviously need more details here.