MySQL Bugs: #37521: Row inserted through view not always visible in base table immediately after

Bug #37521	Row inserted through view not always visible in base table immediately after
Submitted:	19 Jun 2008 13:37	Modified:	24 Jun 2008 14:33
Reporter:	Vemund Ã˜stgaard
Status:	Verified
Category:	Server: Locking	Severity:	S2 (Serious)
Version:	5.1.25-rc	OS:	Linux (2.6.9-34.ELsmp #1 SMP Fri Feb 24 16:56:28 EST 2006 x86_64 x86_64 x86_64 GNU/Linux)
Assigned to:		Target Version:
Tags:	regression
Triage:	Triaged: D2 (Serious) / R1 (None/Negligible) / E4 (High)

Description:
The problem was discovered as instability in an upgrade test (from 5.1.24 to 5.1.25),
I've created a simpler reproduction test case based on the original test that I will
attack to this report after creation.

What basically happens in the test is:
1. A table is created and two rows inserted as root user.
2. A second user creates two views on the table.
3. A third user inserts three rows using the newly created views.
4. A fourth user selects * from the base table.

The expected result is to see 5 rows in the base table, the original two inserted in the
base table and the three new ones inserted through the views.

The test however fails (but not very often) with the last row missing from the
resultset.

The original upgrade test did fail in similar ways in several different places, I just
picked the first failure for my reproduction case.

How to repeat:
The problem can be reproduced with the repro testcase that I will attach, but it only
happens now and then so it's not very reliable. I have been reproducing this on two
different machines that both have the same hardware and software (2 CPU RH4 64-bit 2Gig
memory).

On 5.1.25 I saw it 12 times in 1000 runs
On 5.1.24 I saw it ~20 times in 100 runs

On 5.1.22 I did not see it at all in 10000 runs. This could indicate that it is a
regression, but the test ran quite a bit slower on this version so if it is timing
dependent that slowness could be hiding the problem.

On 5.0.62 I did not see it at all in 1000 runs. Here the test seemed to run just as fast
as on the latest 5.1 versions.

reproduction testcase .test file

Attachment: REPRO-view1.test (application/octet-stream, text), 2.45 KiB.

Reproduction testcase .result file

Attachment: REPRO-view1.result (application/octet-stream, text), 1.82 KiB.

After connections of your users please make:
mysql> show grants;

and paste output here.

One important thing I forgot to mention in the report:
When I connect to the database from a separate client as root after the test is finished
and select from the base table, I see all the rows that was inserted including the one
not visible in the test.

Information from show grants follows below.

User creating the views:

Grants for view_create@localhost
GRANT USAGE ON *.* TO 'view_create'@'localhost' IDENTIFIED BY PASSWORD
'*906B4414DFB7A82305EAAC8210CF5ADA9CCA3F88'
GRANT SELECT, CREATE VIEW ON `viewdb`.* TO 'view_create'@'localhost'
 
User inserting rows:

Grants for view_insert@localhost
GRANT USAGE ON *.* TO 'view_insert'@'localhost' IDENTIFIED BY PASSWORD
'*B34BA5E90726FA529CF3A2C11186C5681E540DBA'
GRANT SELECT, INSERT ON `viewdb`.* TO 'view_insert'@'localhost'

User selecting rows:

It proved hard to reproduce the problem with the show grants; statement in between the
user connecting and doing the select, so the show grants; was issued after the select was
performed:

Grants for view_select@localhost                                                         
                         
GRANT USAGE ON *.* TO 'view_select'@'localhost' IDENTIFIED BY PASSWORD
'*A49821C5A4B04EDD612988117D840840FFBA77C2' |
GRANT SELECT ON `viewdb`.* TO 'view_select'@'localhost'

Thank you for the feedback.

You said:

> When I connect to the database from a separate client as root after the test is
finished and select from the base table, I see all the rows that was inserted including
the one not visible in the test.

Does this mean you run test like: ./mysql-test-run.pl --extern? Please specify
mysql-test-run options and provide your server configuration file.

> Does this mean you run test like: ./mysql-test-run.pl --extern? Please
> specify mysql-test-run options and provide your server configuration
> file.

I am not very familiar with the testframework but it seems that at least when my test
fails the database the test created is left running afterwards. i just run:

./mysql-test-run.pl REPRO-view1

when I run the reproduction testcase.

Setting to verified as I am able to reproduce this, although it happens rarely.

Possibly not a bug. 
Should be re-tested with myisam_concurrent_insert=off

I have not been able to reproduce the issue when running with the setting:

concurrent_insert=off

Seen from the perspective of the testclient the insert and select are not concurrent,
although I realize they may be so from the perspective of the database server. Can we be
sure that this is not a bug? The behavior in older 5.1 versions and in 5.0 versions is
different, so something must have changed.

If you can't reproduce the issue with disabled concurrent inserts, it's fairly certain
this is not a bug.
WRT earlier versions, what is the setting of concurrent_insert there?
Which version exactly do you have in mind?

As mentioned in the original description:

On 5.1.22 I did not see it at all in 10000 runs. 

On 5.0.62 I did not see it at all in 1000 runs. 

I checked these two versions, and the default for both is concurrent_inserts=1

See also Bug#29334