I raised tmp_table_size to 1000M and restarted mysql on the slave and still got the same error. Any other variables I should be looking at?
Thanks, Brian "I don't need any of that SQL stuff -- I just want a database!" -----Original Message----- From: Sasha Pachev [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 25, 2004 6:09 PM To: Stanton, Brian Cc: mysql (E-mail) Subject: Re: SQL_BIG_TABLES and replication Stanton, Brian wrote: > I'm currently running mysql 4.0.13 on red hat 7.2. The following create > table query currently requires the user to use SET SQL_BIG_TABLES=1 for the > query to go through on the master successfully. However, that doesn't seem > to get set when the slave tries to replicate the create table statement. > I've tried restarting the slave with the --big-tables option, but that > doesn't seem to help either. > > The resulting table files on the master are relatively small... > > 8.4k Feb 16 22:37 60dayREGusers_sep_oct2003.frm > 5.0M Feb 16 22:37 60dayREGusers_sep_oct2003.MYD > 9.5M Feb 16 22:37 60dayREGusers_sep_oct2003.MYI > > however the ProfileIDValue_REGID table is rather large. > > 8.4k Sep 15 09:44 ProfileIDValue_REGID.frm > 499M Feb 16 22:17 ProfileIDValue_REGID.MYD > 443M Feb 16 22:18 ProfileIDValue_REGID.MYI > 8.4k Nov 13 11:47 UniqueID_oct2003.frm > 32M Nov 13 11:53 UniqueID_oct2003.MYD > 51M Nov 13 11:53 UniqueID_oct2003.MYI > 8.4k Jan 9 10:50 UniqueID_sep2003.frm > 34M Jan 9 10:58 UniqueID_sep2003.MYD > 55M Jan 9 10:58 UniqueID_sep2003.MYI > > > Anyone have any thoughts? > > ---------------------------- > ERROR: 1114 The table '#sql_931_0' is full > 040225 15:34:25 Slave: error 'The table '#sql_931_0' is full' on query > 'create table 60dayREGusers_sep_oct2003 (primary key(UniqueID)) > select distinct UniqueID_sep2003.UniqueID from > UniqueID_sep2003,ProfileIDValue_REGID > where UniqueID_sep2003.UniqueID=ProfileIDValue_REGID.UniqueID > union > select distinct UniqueID_oct2003.UniqueID from > UniqueID_oct2003,ProfileIDValue_REGID > where UniqueID_oct2003.UniqueID=ProfileIDValue_REGID.UniqueID', > error_code=1114 > ---------------------------- Looks like a bug to me. MySQL should be able to figure out it needs to use the disk when an in-memory temp table exceeds tmp_table_size without SQL_BIG_TABLES. It would be nice if you could create a test case for it and submit it to MySQL developers. For now, try increasing tmp_table_size ( make sure you have enough RAM + swap space to deal with it, though). If re-writing the query is an option, I would also try it without UNION, which is a fairly new feature and could still have a few quirks. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
