MySQL Bugs: #27713: INSERTs fail for empty table using WITH_CHECK

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:	Assigned Account	CPU Architecture:	Any
Tags:	qc

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:
-

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?

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.

I wanted to let you know that I have just successfully reproduced the problem on mysqld 5.0.32 (-Debian_7etch1-log).

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.

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.

Stephan,

have you created views as root user?

Yes, I created the views as root user.

Thank you for the additional comment.

Verified as described. All versions are affected.

test case loadable by our testsuite

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