| Bug #4000 | subquery with variable on BDB table fails + damages table | ||
|---|---|---|---|
| Submitted: | 4 Jun 2004 11:51 | Modified: | 23 Jun 2004 17:55 |
| Reporter: | Matthias Leich | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1 | OS: | Linux (Linux) |
| Assigned to: | Sergei Golubchik | CPU Architecture: | Any |
[4 Jun 2004 11:54]
Matthias Leich
Test case file for mysqltest
Attachment: bla.test (application/octet-stream, text), 1.74 KiB.
[7 Jun 2004 14:51]
Matthias Leich
The bug seems to be bigger, because the variable is not needed.
I have an a little bit different(correlated subquery,no variable) test case
which gives the same error. I assume there is the same reason for the same
wrong behaviour. That's the reason why I do not write a new bug report.
Please have a look on the file ml_err5.test.
BTW: I have also a test case (correlated subquery, prepared statement) with
that error.
[7 Jun 2004 14:53]
Matthias Leich
test case with correlated subquery + no variable
Attachment: ml_err5.test (application/octet-stream, text), 250 bytes.
[7 Jun 2004 17:17]
Ramil Kalimullin
Matthias, thank you for the excellent test cases! ChangeSet 1.1896 04/06/07 20:16:02 ram@gw.mysql.r18.ru +3 -0 A fix (Bug #4000: subquery with variable on BDB table fails + damages table).
[22 Jun 2004 10:48]
Michael Widenius
Sergei will shortly push a fix for index_init/index_end usage that will fix this bug.
[23 Jun 2004 17:55]
Sergei Golubchik
fixed in 4.1.3
Description: If have a Subquery using a variable on a table of type BDB. select 1 as my_column FROM t1 where exists ( select 1 from t2 where b = @arg00 ) ; (@arg00='two') table t1: non empty, MYISAM table t2: empty, BDB This subquery fails with: mysqltest: At line 34: query 'select 1 as my_column FROM t1 where exists ( select 1 from t2 where b = @arg00 ) ' failed: 1015: Can't lock file (errno: -30982) mysql.err: ..../mysql-4.1/dst/libexec/mysqld: ready for connections. Version: '4.1.2-alpha' socket: ..../mysql.sock' port: 41000 040604 10:54:43 bdb: transaction has active cursors 040604 10:54:43 bdb: PANIC: Invalid argument 040604 10:54:43 bdb: fatal region error detected; run recovery 040604 10:55:01 bdb: fatal region error detected; run recovery 040604 10:55:01 bdb: fatal region error detected; run recovery 040604 10:55:01 bdb: fatal region error detected; run recovery After that statement I'm unable to drop t2 (-> 1051: Unknown table 't2'), but a create t2 also fails (->1050: Table 't2' already exists). So I assume that some table information is damaged. Further experiments: The (incomplete) property matrix remark outer table inner table use of variable problem case 1: empty empty YES NO case 2: empty non empty YES NO case 3: non empty empty NO NO problem : non empty empty YES YES BTW: The effect will not change if - I replace t1 with t2 (work on the same physical table) or - the constant 1 is replaced with a column from the outer table. My assumptions: - The problem occurs during execution of the statement, when the server already knows that that the outer table is not empty but before he detects, that the subquery will get row not found. He is unable to lock the table file. - The behaviour is very strange, because it depends on the use of the variable. - The problem depends on the use of the BDB table handler for the table t2. If I replace BDB with MYISAM, the problem disappears. My environment: Intel PC with Linux(SuSE 9.0), MySQL 4.1 source distrib. (last bk pull 2004-06-01) How to repeat: Please use my test script. Suggested fix: It depends on the field where the error is situated: - BDB engine: It is NO MySQL bug, do what you want. - mysqltest or MySQL server: Please repair ist.