Merge lp:~laurynas-biveinis/percona-server/26611-bug1064333-5.1 into lp:percona-server/5.1

[Laurynas Biveinis]

Fix bug 1064333 (Fake changes bump the changed row counters).

There are following issues with the stat acounting with fake changes:
- InnoDB handler methods write_row, update_row, delete_row update the
  userstat changed row counter unconditionally.
- row_insert_for_mysql(), row_update_for_mysql() and
  row_update_cascade_for_mysql() update the number of changed rows
  since last stat update, innodb_rows_inserted, innodb_rows_updated
  and innodb_rows_deleted unconditionally.

Fixed and extended the percona_innodb_fake_changes testcase to check
these stats for all the fixed cases.

http://jenkins.percona.com/job/percona-server-5.1-param/450/

26611

[Stewart Smith (stewart)]

review: Approve

=== modified file 'Percona-Server/mysql-test/r/percona_innodb_fake_changes.result'
--- Percona-Server/mysql-test/r/percona_innodb_fake_changes.result	2012-10-18 14:17:27 +0000
+++ Percona-Server/mysql-test/r/percona_innodb_fake_changes.result	2012-10-18 14:17:28 +0000
@@ -22,6 +22,7 @@
 OFF
 # Explicit COMMIT should fail when innodb_fake_changes is enabled
 # DML should be fine
+SET @@GLOBAL.userstat_running= TRUE;
 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
 INSERT INTO t1 VALUES (1);
 SET autocommit=0;
@@ -42,9 +43,21 @@
 test.t1	check	status	OK
 should_be_1
 1
+should_be_0
+0
+should_be_0
+0
+should_be_0
+0
+should_be_0
+0
+should_be_0
+0
+should_be_0
+0
 DROP TABLE t1;
 # DDL must result in error
-CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
 SET autocommit=0;
 SET innodb_fake_changes=1;
 BEGIN;
@@ -58,6 +71,32 @@
 ERROR HY000: Got error 131 during COMMIT
 ROLLBACK;
 SET innodb_fake_changes=0;
+INSERT INTO t1 VALUES (1), (2);
+COMMIT;
+CREATE TABLE t2 (a INT PRIMARY KEY, b INT, INDEX b_ind (b),
+FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1, 1);
+INSERT INTO t2 VALUES (2, 2);
+COMMIT;
+SET innodb_fake_changes=1;
+DELETE FROM t1 WHERE a=1;
+UPDATE t1 SET a=3 WHERE a=2;
+COMMIT;
+ERROR HY000: Got error 131 during COMMIT
+should_be_1
+1
+should_be_0
+0
+should_be_0
+0
+should_be_0
+0
+should_be_0
+0
+should_be_0
+0
+SET innodb_fake_changes=0;
+DROP TABLE t2;
 CREATE TABLE t3 (a INT primary key, b text) ENGINE=InnoDB;
 INSERT INTO t3 VALUES (1,'');
 COMMIT;
@@ -73,3 +112,4 @@
 should_be_1
 1
 DROP TABLE t1, t3;
+SET @@GLOBAL.userstat_running= default;

=== modified file 'Percona-Server/mysql-test/t/percona_innodb_fake_changes.test'
--- Percona-Server/mysql-test/t/percona_innodb_fake_changes.test	2012-10-18 14:17:27 +0000
+++ Percona-Server/mysql-test/t/percona_innodb_fake_changes.test	2012-10-18 14:17:28 +0000
@@ -17,9 +17,18 @@
 
 --echo # Explicit COMMIT should fail when innodb_fake_changes is enabled
 --echo # DML should be fine
+SET @@GLOBAL.userstat_running= TRUE;
 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
 INSERT INTO t1 VALUES (1);
+
 let $t1_checksum_1= `CHECKSUM TABLE t1 EXTENDED`;
+let $innodb_rows_inserted_1= `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_rows_inserted'`;
+let $innodb_rows_deleted_1= `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_rows_deleted'`;
+let $innodb_rows_updated_1= `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_rows_updated'`;
+let $table_rows_estimate_1= `SELECT ROWS FROM INFORMATION_SCHEMA.INNODB_TABLE_STATS WHERE TABLE_SCHEMA LIKE 'test' AND TABLE_NAME LIKE 't1'`;
+let $table_rows_changed_1= `SELECT ROWS_CHANGED FROM INFORMATION_SCHEMA.TABLE_STATISTICS WHERE TABLE_SCHEMA LIKE 'test' AND TABLE_NAME LIKE 't1'`;
+let $table_rows_changed_x_indexes_1= `SELECT ROWS_CHANGED_X_INDEXES FROM INFORMATION_SCHEMA.TABLE_STATISTICS WHERE TABLE_SCHEMA LIKE 'test' AND TABLE_NAME LIKE 't1'`;
+
 SET autocommit=0;
 SET innodb_fake_changes=1;
 BEGIN;
@@ -33,13 +42,25 @@
 --echo # Verify that the fake changes to t1 did not leak through
 CHECK TABLE t1;
 let $t1_checksum_2= `CHECKSUM TABLE t1 EXTENDED`;
+let $innodb_rows_inserted_2= `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_rows_inserted'`;
+let $innodb_rows_deleted_2= `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_rows_deleted'`;
+let $innodb_rows_updated_2= `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_rows_updated'`;
+let $table_rows_estimate_2= `SELECT ROWS FROM INFORMATION_SCHEMA.INNODB_TABLE_STATS WHERE TABLE_SCHEMA LIKE 'test' AND TABLE_NAME LIKE 't1'`;
+let $table_rows_changed_2= `SELECT ROWS_CHANGED FROM INFORMATION_SCHEMA.TABLE_STATISTICS WHERE TABLE_SCHEMA LIKE 'test' AND TABLE_NAME LIKE 't1'`;
+let $table_rows_changed_x_indexes_2= `SELECT ROWS_CHANGED_X_INDEXES FROM INFORMATION_SCHEMA.TABLE_STATISTICS WHERE TABLE_SCHEMA LIKE 'test' AND TABLE_NAME LIKE 't1'`;
 --disable_query_log
 eval SELECT "$t1_checksum_1" LIKE "$t1_checksum_2" AS should_be_1;
+eval SELECT "$innodb_rows_inserted_2" - "$innodb_rows_inserted_1" AS should_be_0;
+eval SELECT "$innodb_rows_deleted_2" - "$innodb_rows_deleted_1" AS should_be_0;
+eval SELECT "$innodb_rows_updated_2" - "$innodb_rows_updated_1" AS should_be_0;
+eval SELECT "$table_rows_estimate_2" - "$table_rows_estimate_1" AS should_be_0;
+eval SELECT "$table_rows_changed_2" - "$table_rows_changed_1" AS should_be_0;
+eval SELECT "$table_rows_changed_x_indexes_2" - "$table_rows_changed_x_indexes_1" AS should_be_0;
 --enable_query_log
 DROP TABLE t1;
 
 --echo # DDL must result in error
-CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
 SET autocommit=0;
 SET innodb_fake_changes=1;
 BEGIN;
@@ -53,8 +74,49 @@
 ALTER TABLE t1 ENGINE=MyISAM;
 ROLLBACK;
 
+# Test stat counters foreign key constraints
+SET innodb_fake_changes=0;
+INSERT INTO t1 VALUES (1), (2);
+COMMIT;
+CREATE TABLE t2 (a INT PRIMARY KEY, b INT, INDEX b_ind (b),
+       FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1, 1);
+INSERT INTO t2 VALUES (2, 2);
+COMMIT;
+
+let $t2_checksum_1= `CHECKSUM TABLE t2 EXTENDED`;
+let $innodb_rows_deleted_1= `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_rows_deleted'`;
+let $innodb_rows_updated_1= `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_rows_updated'`;
+let $table_rows_estimate_1= `SELECT ROWS FROM INFORMATION_SCHEMA.INNODB_TABLE_STATS WHERE TABLE_SCHEMA LIKE 'test' AND TABLE_NAME LIKE 't2'`;
+let $table_rows_changed_1= `SELECT ROWS_CHANGED FROM INFORMATION_SCHEMA.TABLE_STATISTICS WHERE TABLE_SCHEMA LIKE 'test' AND TABLE_NAME LIKE 't2'`;
+let $table_rows_changed_x_indexes_1= `SELECT ROWS_CHANGED_X_INDEXES FROM INFORMATION_SCHEMA.TABLE_STATISTICS WHERE TABLE_SCHEMA LIKE 'test' AND TABLE_NAME LIKE 't2'`;
+
+SET innodb_fake_changes=1;
+DELETE FROM t1 WHERE a=1;
+UPDATE t1 SET a=3 WHERE a=2;
+--error 1180
+COMMIT;
+
+let $t2_checksum_2= `CHECKSUM TABLE t2 EXTENDED`;
+let $innodb_rows_deleted_2= `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_rows_deleted'`;
+let $innodb_rows_updated_2= `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_rows_updated'`;
+let $table_rows_estimate_2= `SELECT ROWS FROM INFORMATION_SCHEMA.INNODB_TABLE_STATS WHERE TABLE_SCHEMA LIKE 'test' AND TABLE_NAME LIKE 't2'`;
+let $table_rows_changed_2= `SELECT ROWS_CHANGED FROM INFORMATION_SCHEMA.TABLE_STATISTICS WHERE TABLE_SCHEMA LIKE 'test' AND TABLE_NAME LIKE 't2'`;
+let $table_rows_changed_x_indexes_2= `SELECT ROWS_CHANGED_X_INDEXES FROM INFORMATION_SCHEMA.TABLE_STATISTICS WHERE TABLE_SCHEMA LIKE 'test' AND TABLE_NAME LIKE 't2'`;
+
+--disable_query_log
+eval SELECT "$t2_checksum_1" LIKE "$t2_checksum_2" AS should_be_1;
+eval SELECT "$innodb_rows_deleted_2" - "$innodb_rows_deleted_1" AS should_be_0;
+eval SELECT "$innodb_rows_updated_2" - "$innodb_rows_updated_1" AS should_be_0;
+eval SELECT "$table_rows_estimate_2" - "$table_rows_estimate_1" AS should_be_0;
+eval SELECT "$table_rows_changed_2" - "$table_rows_changed_1" AS should_be_0;
+eval SELECT "$table_rows_changed_x_indexes_2" - "$table_rows_changed_x_indexes_1" AS should_be_0;
+--enable_query_log
+
+SET innodb_fake_changes=0;
+DROP TABLE t2;
+
 # Test for bug 890404: uninitialized value warning in btr_cur_pessimistic_update
-SET innodb_fake_changes=0;
 CREATE TABLE t3 (a INT primary key, b text) ENGINE=InnoDB;
 INSERT INTO t3 VALUES (1,'');
 COMMIT;
@@ -73,3 +135,5 @@
 eval SELECT "$t3_checksum_1" LIKE "$t3_checksum_2" AS should_be_1;
 --enable_query_log
 DROP TABLE t1, t3;
+
+SET @@GLOBAL.userstat_running= default;

=== modified file 'Percona-Server/storage/innodb_plugin/handler/ha_innodb.cc'
--- Percona-Server/storage/innodb_plugin/handler/ha_innodb.cc	2012-10-18 14:17:27 +0000
+++ Percona-Server/storage/innodb_plugin/handler/ha_innodb.cc	2012-10-18 14:17:28 +0000
@@ -5367,7 +5367,9 @@
 	error = row_insert_for_mysql((byte*) record, prebuilt);
 
 #ifdef EXTENDED_FOR_USERSTAT
-	if (error == DB_SUCCESS) rows_changed++;
+	if (UNIV_LIKELY(error == DB_SUCCESS && !trx->fake_changes)) {
+		rows_changed++;
+	}
 #endif
 
 	/* Handle duplicate key errors */
@@ -5714,7 +5716,9 @@
 	}
 
 #ifdef EXTENDED_FOR_USERSTAT
-	if (error == DB_SUCCESS) rows_changed++;
+	if (UNIV_LIKELY(error == DB_SUCCESS && !trx->fake_changes)) {
+		rows_changed++;
+	}
 #endif
 
 	innodb_srv_conc_exit_innodb(trx);
@@ -5779,7 +5783,9 @@
 	error = row_update_for_mysql((byte*) record, prebuilt);
 
 #ifdef EXTENDED_FOR_USERSTAT
-	if (error == DB_SUCCESS) rows_changed++;
+	if (UNIV_LIKELY(error == DB_SUCCESS && !trx->fake_changes)) {
+		rows_changed++;
+	}
 #endif
 
 	innodb_srv_conc_exit_innodb(trx);

=== modified file 'Percona-Server/storage/innodb_plugin/row/row0mysql.c'
--- Percona-Server/storage/innodb_plugin/row/row0mysql.c	2012-05-29 06:32:03 +0000
+++ Percona-Server/storage/innodb_plugin/row/row0mysql.c	2012-10-18 14:17:28 +0000
@@ -1188,17 +1188,19 @@
 
 	que_thr_stop_for_mysql_no_error(thr, trx);
 
-	prebuilt->table->stat_n_rows++;
-
-	srv_n_rows_inserted++;
-
-	if (prebuilt->table->stat_n_rows == 0) {
-		/* Avoid wrap-over */
-		prebuilt->table->stat_n_rows--;
+	if (UNIV_LIKELY(!(trx->fake_changes))) {
+
+		prebuilt->table->stat_n_rows++;
+
+		if (prebuilt->table->stat_n_rows == 0) {
+			/* Avoid wrap-over */
+			prebuilt->table->stat_n_rows--;
+		}
+
+		srv_n_rows_inserted++;
+		row_update_statistics_if_needed(prebuilt->table);
 	}
 
-	if (!(trx->fake_changes))
-	row_update_statistics_if_needed(prebuilt->table);
 	trx->op_info = "";
 
 	return((int) err);
@@ -1446,6 +1448,11 @@
 
 	que_thr_stop_for_mysql_no_error(thr, trx);
 
+	if (UNIV_UNLIKELY(trx->fake_changes)) {
+		trx->op_info = "";
+		return((int) err);
+	}
+
 	if (node->is_delete) {
 		if (prebuilt->table->stat_n_rows > 0) {
 			prebuilt->table->stat_n_rows--;
@@ -1460,7 +1467,6 @@
 	that changes indexed columns, UPDATEs that change only non-indexed
 	columns would not affect statistics. */
 	if (node->is_delete || !(node->cmpl_info & UPD_NODE_NO_ORD_CHANGE)) {
-		if (!(trx->fake_changes))
 		row_update_statistics_if_needed(prebuilt->table);
 	}
 
@@ -1669,6 +1675,11 @@
 		return(err);
 	}
 
+	if (UNIV_UNLIKELY((trx->fake_changes))) {
+
+		return(err);
+	}
+
 	if (node->is_delete) {
 		if (table->stat_n_rows > 0) {
 			table->stat_n_rows--;
@@ -1679,7 +1690,6 @@
 		srv_n_rows_updated++;
 	}
 
-	if (!(trx->fake_changes))
 	row_update_statistics_if_needed(table);
 
 	return(err);