Hi!
>>>>> "Michael" == Michael Jessop <[EMAIL PROTECTED]> writes: Michael> _mysql_exceptions.OperationalError: (1114, "The table '#sql700_2_0' is full") Michael> ...I reinstalled MySQL with the Innodb backend, and configured it (per the nice example at mysql.org) to fit on my 10GB disk with 128 mb ram, I start the server with the --big-tables option and *I* still get this message. I can't believe it is true. The query performs a union of one small table (700 records) to the left join of the same small table to a large table of 700,000 records (to eliminate certain records). Criminy, Microsoft Access can handle this, so I have to believe the error lies somewhere other than MySQL itself. But can anyone please point me in the right direction?! This is a bit frustrating. Michael> insert into merged_works Michael> select works.* Michael> from works Michael> union all Michael> select worksets.* Michael> from worksets Michael> left join works Michael> on worksets.wrk_inst = works.wrk_inst Michael> where works.wrk_inst is null; <cut> The problem here is that UNION's doesn't use to the --big-tables option but assumes all result sets should fit into the in-memory temporary table. (This is fixed in the 4.0.2 source tree). Simple fix: Use 2 commands to do the insert. insert into merged_works select works.* from works; insert into merged_works select worksets.* from worksets left join works on worksets.wrk_inst = works.wrk_inst where works.wrk_inst is null; Another option is to enlarge the value 'tmp_table_size' to be big enough to hold the temporary set. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
