MySQL Bugs: #20017: #42000The SELECT would examine more than MAX_JOIN

Bug #20017	#42000The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE a
Submitted:	23 May 2006 6:52	Modified:	1 Sep 2006 11:11
Reporter:	ajay kumar	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0	OS:	Windows (windows XP)
Assigned to:		CPU Architecture:	Any

Description:
i am connecting from vb.net.when i m running the query in my sql command prompt then it is running well bu from my page it is returning the following error.

#42000The 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

why so?
whats the solution ?

How to repeat:
this is the query which i wrote.

select a.Party_id,a.Voucher_No,date_format(a.Voucher_Date,'%d-%m-%Y') as Voucher_Date,
date_format(a.Voucher_Date,'%d') as VoucherDay,date_format(a.Voucher_Date,'%m') as VoucherMonth,
date_format(a.Voucher_Date,'%Y') as VoucherYear, a.Voucher_Amt,a.Voucher_DedAmt,b.vendor_code, 
b.Payee_Name,'CD' as Unit from tds_party_mst a,tds_payee_mst b,tds_payee_sec c  where a.vendor_code=b.Vendor_Code 
and b.isform16A='Y' and a.Head_GLCode='15100'  and a.Head_SLCode='151001003' and a.party_id not in 
(select party_id from tds_deposit) and  a.Company_Code='SUMI' and a.Financial_Year='2005-2006'  
and a.Company_Code=b.Company_Code and a.Financial_Year=b.Financial_Year  and a.vendor_code=c.vendor_code 
and c.Section_Code='194I' order by a.Voucher_Date

Thank you for a problem report. Please, send the results of:

SHOW VARIABLES LIKE 'max_j%'\G

and

EXPLAIN select a.Party_id,a.Voucher_No,date_format(a.Voucher_Date,'%d-%m-%Y') as
Voucher_Date,
date_format(a.Voucher_Date,'%d') as VoucherDay,date_format(a.Voucher_Date,'%m')
as VoucherMonth,
date_format(a.Voucher_Date,'%Y') as VoucherYear,
a.Voucher_Amt,a.Voucher_DedAmt,b.vendor_code, 
b.Payee_Name,'CD' as Unit from tds_party_mst a,tds_payee_mst b,tds_payee_sec c 
where a.vendor_code=b.Vendor_Code 
and b.isform16A='Y' and a.Head_GLCode='15100'  and a.Head_SLCode='151001003' and
a.party_id not in 
(select party_id from tds_deposit) and  a.Company_Code='SUMI' and
a.Financial_Year='2005-2006'  
and a.Company_Code=b.Company_Code and a.Financial_Year=b.Financial_Year  and
a.vendor_code=c.vendor_code 
and c.Section_Code='194I' order by a.Voucher_Date\G

Looks like a consequence of http://bugs.mysql.com/bug.php?id=20023

disable pooling in vb.net connection string and the mysql_change_user() shouldn't be called anymore.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

looks like a duplicate of bug #20023

Please Use latest drivers 5.0.1 for mysql it will be rosolved:

{"#42000The 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"}

I've got MySql 5.0 with mysql-connector-net-5.0.9 and I'm still getting the error when I execute a query in my asp.net app. The strange thing is that if I run the query in MySql Query Browser, I get the results OK...

Here're the query and the error message I get.

SELECT p.ProductID, p.SerialNo, p.ManufactureDate AS ManufactureDate1, p.InputVoltage, p.TypeID, c.Name AS Customer, MAX(tt.StartDate) AS TroubleT, fv.VisitDate, s.Name AS Site, c.Rating AS RatingCustomer, s.Rating AS RatingSite
FROM {oj { oj { oj { oj { oj { oj { oj Product p LEFT OUTER JOIN Delivery d ON d.ProductID = p.ProductID }
LEFT OUTER JOIN Customer c ON c.CustomerID = d .CustomerID }
LEFT OUTER JOIN TroubleTicket tt ON tt.ProductID = p.ProductID
AND tt.EndDate IS NULL}
LEFT OUTER JOIN FieldVisits fv ON fv.ProductID = p.ProductID }
LEFT OUTER JOIN TroubleTicket tt2 ON tt2.ProductID = p.ProductID }
LEFT OUTER JOIN Installation i ON d .DeliveryID = i.DeliveryID }
LEFT OUTER JOIN Site s ON s.SiteID = i.SiteID }
, Component com, Designation des
WHERE (com.ProductID = p.ProductID)
AND (des.DesignatorID = com.Designator)
AND (tt2.EndDate IS NULL)
AND (tt2.StartDate IS NOT NULL)
AND (p.TypeID LIKE 'TFX%')
GROUP BY p.ProductID

Error Message when running the query in the asp.net app.

MySql.Data.MySqlClient.MySqlException: #42000The 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 at MySql.Data.MySqlClient.PacketReader.CheckForError() at MySql.Data.MySqlClient.PacketReader.ReadHeader() at MySql.Data.MySqlClient.PacketReader.OpenPacket() at MySql.Data.MySqlClient.NativeDriver.ReadResult(Int64& affectedRows, Int64& lastInsertId) ................

Istoo easy just get a MySQLConnector v.6.04
I got it! but I don't know how load it here, but the problem is a bug that was fiexed in the last version. but hurry cause it will be unable soon