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]
