Re: [Maria-developers] [Merge] lp:~paul-mccullagh/maria/pbxt-1.0.11 into lp:maria

Hi Kristian, Thanks for clearing this up. The problem is certainly as you say: it depends on the speed of a background thread. From this point of view the test does not make all that much sense for PBXT which returns inconsistent row count estimates. I think I will adjust the test to work in all cases with PBXT. On May 10, 2010, at 4:57 PM, Kristian Nielsen wrote:

Paul McCullagh writes:

...

All tests in the PBXT suite run through on Mac and Linux, except for one error under Linux, which is a bit weird (see below).

...

------------------

pbxt.select_safe [ fail ] Test ended at 2010-05-06 17:19:13

CURRENT_TEST: pbxt.select_safe mysqltest: At line 19: query 'select 1 from t1,t1 as t2,t1 as t3' failed: 1104: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

The result from queries just before the failure was: drop table if exists t1; SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=4, SQL_MAX_JOIN_SIZE=9; create table t1 (a int auto_increment primary key, b char(20)); insert into t1 values(1,"test"); SELECT SQL_BUFFER_RESULT * from t1; a b 1 test update t1 set b="a" whe

Ok, I looked into this.

So this test does a three-way cartesian self-join on a table with 2 rows (a total of 8 rows). And it sets SQL_MAX_JOIN_SIZE=9. And sometimes (apparently timing dependent), this test fails because the optimiser estimates more than 9 rows in the join. So the problem here is that the optimiser estimate is sometimes too big for SQL_MAX_JOIN_SIZE=9.

What I found is that when the test case fails, the function ha_pbxt::info() returns records==3 for the table. When the test case succeeds, it returns records==2. So it's pretty clear that the server will throw an error when records==3 and SQL_MAX_JOIN_SIZE=9.

What remains to consider is why the returned records value differs between test runs.

The testcase actually first inserts a row, then deletes it, then inserts two more rows. I'm speculating that PBXT has some background cleanup thread or similar that causes a race between freeing up space from the first row and allocating space for the two new rows?

So Paul, do you think returning records = 2 or 3 at random from ha_pbxt::info() is expected or not?

If expected, one way to fix the problem is to create a new table before running the test:

create table t2 like t1; insert into t2 select * from t1; analyze table t2; # PBXT: required to get the correct COUNT(*) select 1 from t2 as t1,t2,t2 as t3;

Then there is no possibility for the insert+delete to take up a slot and cause a failure (I was not able to repeat the failure with this change).

Alternatively, we can just increate the SQL_MAX_JOIN_SIZE value.

Or alternatively, you may decide that the test is meaningless for PBXT due to imprecise statistics, and just remove it from select_safe.test.

Just let me know what you prefer, and I'll change it as needed.

- Kristian.

-- Paul McCullagh PrimeBase Technologies www.primebase.org www.blobstreaming.org pbxt.blogspot.com