Bug #8442 Unstable View
Submitted: 11 Feb 2005 15:34 Modified: 11 Feb 2005 15:48
Reporter: David Hammink Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.2 OS:Linux (Linux Fedora Core 2)
Assigned to: CPU Architecture:Any

[11 Feb 2005 15:34] David Hammink
Description:
So far View behave dreadfully unstable
Just a simple view between two tables 
CREATE OR REPLACE VIEW `DB`.`Numbers` SELECT * FROM tblNrDet DET LEFT JOIN tblNum NUM USING(StammNr);
SELECT * FROM Numbers;

Crashes the server. Replaced it with
CREATE OR REPLACE VIEW `DB`.`Numbers` SELECT * FROM tblNrDet DET , tblNum NUM WHERE DET.StammNr=NUM.StammNr;
SELECT * FROM Numbers;
And it works.
DrOP  VIEW Numbers;
And  SELECT * FROM Numbers; still gives me values (Still in Cache ?)

Added a third table in the same manner. 
The server crashes.
Drop the VIEW. Add the working View with two tables.
The server crashes

View are most certainly not ready yet .....

How to repeat:
CREATE TABLE TestError;
USE TestError;
CREATE TABLE tblNum ( StammNr INT NOT NULL PRIMARY KEY,TarifGruppe CHAR(10);
CREATE TABLE tblNrDet (ProviderNr INT NOT NULL PRIMARY KEY,StammNr INT);
CREATE OR REPLACE VIEW TestError.Numbers AS SELECT DET.ProviderNr,NUM.TarifGruppe FROM tblNrDet DET , tblNum NUM WHERE DET.StammNr=NUM.StammNr;
SELECT * FROM Numbers;

And the server crashes everytime

Error log mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=402653184
read_buffer_size=2093056
max_used_connections=1
max_connections=100
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 802415 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x89de168
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x614d3f1c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8122d87
0xdbe0b8
0xdbaea5
0x8133666
0x8138779
0x8131fe8
0x8131990
0x813108a
0xdb77fc
0xc27aba
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do 
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8a2e9d8 = SElECT * FROM Numbers2
thd->thread_id=3
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
050211 16:29:18  mysqld restarted
[11 Feb 2005 15:48] MySQL Verification Team
Hi,

Thank you for the report, but I wasn't able to repeat it with 5.0.3-alpha-debug-log.

mysql> CREATE TABLE tblNum ( StammNr INT NOT NULL PRIMARY KEY,TarifGruppe CHAR(10));
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE tblNrDet (ProviderNr INT NOT NULL PRIMARY KEY,StammNr INT);
Query OK, 0 rows affected (0.17 sec)

mysql> CREATE OR REPLACE VIEW test.Numbers AS SELECT
    -> DET.ProviderNr,NUM.TarifGruppe FROM tblNrDet DET , tblNum NUM WHERE
    -> DET.StammNr=NUM.StammNr;
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT * FROM Numbers;
Empty set (0.01 sec)