MySQL Bugs: #64923: inserted rows not visible to other sessions after commit (autocommit off)

Bug #64923	inserted rows not visible to other sessions after commit (autocommit off)
Submitted:	10 Apr 2012 9:03	Modified:	12 Apr 2012 20:16
Reporter:	Hans-Peter Sloot	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	Server version: 5.1.52	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	autocommit, commit, insert

Description:
After inserting a number of rows in a partitioned table, while autocommit is set to off, the rows are not visible to another session after committing the rows.

The behavior is not shown when the table is not partitioned.

How to repeat:
create partitioned table like:
CREATE TABLE `SALES` (
  `PROD_ID` int(11) NOT NULL,
  `CUST_ID` int(11) NOT NULL,
  `TIME_ID` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `CHANNEL_ID` int(11) NOT NULL,
  `PROMO_ID` int(11) NOT NULL,
  `QUANTITY_SOLD` float NOT NULL,
  `AMOUNT_SOLD` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(TIME_ID))
(PARTITION SALES_1995 VALUES LESS THAN (820450800) ENGINE = InnoDB,
 PARTITION SALES_1996 VALUES LESS THAN (852073200) ENGINE = InnoDB,
 PARTITION SALES_H1_1997 VALUES LESS THAN (867708000) ENGINE = InnoDB,
 PARTITION SALES_H2_1997 VALUES LESS THAN (883609200) ENGINE = InnoDB ..

Set autocommit to off:
set autocommit = off; 

Insert a number of rows.

Commit the inserted rows.

Query the table in a new session.
select count(*) from SALES;

In 9 out of 10 times the new session will not see the inserted rows after the commit has finished.

Rows become visible after :
1. analyze table SALES; 
2. A new is inserted after the commit;

Insert a number of rows 

Suggested fix:
Unknown.

create statement of partitioned table

Attachment: SALES.sql (application/octet-stream, text), 3.09 KiB.

Inserts for SALES table

Attachment: insertSALES.sql (application/octet-stream, text), 9.91 KiB.

Thank you for the report.

I can not repeat described behavior.

Is parallel session really new? What following sequence of queries outputs:

ROLLBACK;
select count(*) from SALES;

Do you have query cache enabled?

The settings are:
mysql> show variables like '%query_cache%'
    -> ;
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+

The complete sequence is shown below.
You may have to try more than once.

mysql> truncate table SALES;        
Query OK, 0 rows affected (0.07 sec)

mysql> \. sales2.sql
Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from SALES;   
+----------+
| count(*) |
+----------+
|       16 |
+----------+
1 row in set (0.00 sec)

mysql> exit
Bye
[root@nlvora33 mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 5.1.52 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> use sh
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 count(*) from SALES;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

It looks as if the problem disappears with caching set to off.
mysql> show variables like '%cache%';
+------------------------------+----------------------+
| Variable_name                | Value                |
+------------------------------+----------------------+
| binlog_cache_size            | 32768                |
| have_query_cache             | YES                  |
| key_cache_age_threshold      | 300                  |
| key_cache_block_size         | 1024                 |
| key_cache_division_limit     | 100                  |
| max_binlog_cache_size        | 18446744073709547520 |
| query_cache_limit            | 1048576              |
| query_cache_min_res_unit     | 4096                 |
| query_cache_size             | 0                    |
| query_cache_type             | OFF                  |
| query_cache_wlock_invalidate | OFF                  |
| table_definition_cache       | 256                  |
| table_open_cache             | 64                   |
| thread_cache_size            | 0                    |
+------------------------------+----------------------+

Thank you for the feedback.

If query cache affects this behavior it is duplicate of bug #53775 fixed in version 5.1.63. Please upgrade.