Bug #27713 INSERTs fail for empty table using WITH_CHECK_OPTION
Submitted: 9 Apr 2007 11:17 Modified: 10 Apr 2007 7:38
Reporter: Christian Hammers (Silver Quality Contributor) (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Views Severity:S3 (Non-critical)
Version:5.0.38, 5.1, 5.2 OS:Any
Assigned to: Evgeny Potemkin CPU Architecture:Any
Tags: qc
Triage: Triaged: D3 (Medium) / R3 (Medium) / E3 (Medium)

[9 Apr 2007 11:17] Christian Hammers
Description:
Reported as Debian bug on http://bugs.debian.org/418248

--------------------------------------------------------------------
From: Stephan Peijnik <sp@sp.or.at>

When trying to INSERT into a view using "WITH_CHECK_OPTION" fails while the view is empty.
INSERT'ing also fails after a row has been inserted into the underlying table and works once
one has inserted a row into the underlying table and SELECTs from the view.

More information including error messages and an SQL dump can be found at 
http://paste.lisp.org/display/39398/.

I'm unsure about if this is really a bug, but the behaviour (having to SELECT before INSERT works)
makes me believe it is one.

Version: 5.0.38
Architecture: i386 (i686)
Kernel: Linux 2.6.18-4-686
---------------------------------------------------------------------

I ask the user for a simpler test case.

bye,

-christian-

How to repeat:
See URL

Suggested fix:
-
[9 Apr 2007 12:50] Sveta Smirnova
Thank you for the report.

Christian, can you repeat yourself described bahaviour?

Please describe actions needed to repeat: I mean when and as which user I should connect to successfully insert into the view?
[9 Apr 2007 14:00] Stephan Peijnik
As Christian asked me to I have tried coming up with a smaller testcase but was unable to.

The same user that is not able to insert into the empty view is able to insert as soon as a.) the view returns at least a row and b.) the user has selected from the ROW once.

Having a look at how to exactly reproduce the problem, you get a 'timeline' like this:

User 'tester' connects to the database, tries to insert into (the now empty) 'requests' view and that fails.
Now another user with full access to the database (such as root) inserts data into the underlying 'request' table.
User 'tester' tries to insert again, without success.
Now 'tester' SELECTs the view, gets a row back, tries to insert again and inserting works.

I have also noticed that if the view is not empty when the user connects to the database inserting works as it should.

I hope that helps.
[10 Apr 2007 6:10] Stephan Peijnik
I wanted to let you know that I have just successfully reproduced the problem on mysqld 5.0.32 (-Debian_7etch1-log).
[10 Apr 2007 6:13] Sveta Smirnova
Stephan, thank you for the feedback. I can not repeat the issue with current sources.

Please indicate accurate version of MySQL server error is repeatable with. Also try with our binaries accessible from http://dev.mysql.com/downloads/mysql/5.0.html and say us result.
[10 Apr 2007 6:28] Stephan Peijnik
The problem is repeatable with mysqld versions (output of 'SELECT VERSION();'):

5.0.32-Debian_7etch1-log
5.0.38-Debian_1-log
5.0.37

Please note that 5.0.38-Debian_1-log and 5.0.37 are running on the same system, whilst 5.0.32-Debian_7etch1-log is running on a different system.
Also, I tried coming up with a smaller, simpler test-case but failed. 
I was unable to determine what exactly in my database setup causes the problem. Just using the tables needed for the view to work did not cause the problem.

Also, I noticed that it seems to work for the 'tester' account now, but still not for the 'sp' account.
[10 Apr 2007 6:56] Sveta Smirnova
Stephan,

have you created views as root user?
[10 Apr 2007 7:09] Stephan Peijnik
Yes, I created the views as root user.
[10 Apr 2007 7:37] Sveta Smirnova
Thank you for the additional comment.

Verified as described. All versions are affected.
[10 Apr 2007 7:38] Sveta Smirnova
test case loadable by our testsuite

Attachment: bug27713.test (application/octet-stream, text), 19.70 KiB.