Bug #22710 Failing CREATE TABLE SELECT with too many joins on the master stop the slaves
Submitted: 26 Sep 2006 19:58 Modified: 4 May 2007 13:15
Reporter: jocelyn fournier (Silver Quality Contributor) Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.0.24a OS:Linux (linux)
Assigned to: CPU Architecture:Any
Tags: qc

[26 Sep 2006 19:58] jocelyn fournier
Description:
Hi,

I've just deployed 4 SQL in replication (1 master, 3 slaves).
All are running with MySQL 5.0.24a. 3 uses x86-64 version (1 master + 2 slaves), and 1 slave runs with a x86 version.
I've hit the following problem : 2 slaves are stuck in the same state :

show full processlist returns on the slaves :

| 602587 | forum       | 172.16.32.231:52168 | forum_sqlforum | Query   |     4 | Waiting for the slave SQL thread to advance position | SELECT MASTER_POS_WAIT('mysql-bin.000001',836336067, 600)                                            |
| 602612 | forum       | 172.16.32.231:52223 | forum_sqlforum | Query   |     2 | Waiting for the slave SQL thread to advance position | SELECT MASTER_POS_WAIT('mysql-bin.000001',836367242, 600)                                            |
| 602615 | forum       | 172.16.32.231:52229 | forum_sqlforum | Query   |     1 | Waiting for the slave SQL thread to advance position | SELECT MASTER_POS_WAIT('mysql-bin.000001',836374770, 600)                                            |
| 602625 | forum       | 172.16.32.231:52254 | forum_moto     | Query   |     0 | Waiting for the slave SQL thread to advance position | SELECT MASTER_POS_WAIT('mysql-bin.000001',836406109, 600)   
[...]

SHOW SLAVE STATUS returns for both x86-64 slaves :

| Waiting for master to send event | 172.16.32.247 | repl        |        3306 |            60 | mysql-bin.000001 |           837178872 | caraforumsql2-relay-bin.000002 |     779300664 | mysql-bin.000001      | Yes              | Yes               |                 |                     |                    |                        |                         |                             |          0 |            |            0 |           779300527 |       837179009 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                  3094 |

MASTER seems to be happy as well :

|       2 | repl    | 172.16.32.249:32788 | NULL           | Binlog Dump    | 67590 | Has sent all binlog to slave; waiting for binlog to be updated | NULL             |
|       3 | repl    | 172.16.32.248:32788 | NULL           | Binlog Dump    | 67519 | Has sent all binlog to slave; waiting for binlog to be updated | NULL             |
|     226 | repl    | 172.16.32.233:35226 | NULL           | Binlog Dump    | 67247 | Has sent all binlog to slave; waiting for binlog to be updated | NULL             |

However the remaining x86 slave was stopped because of an error during a select (MAX_JOIN_SIZE reached, which is lower than on x86-64 platform). Restarting this slave didn't solve the problem of the two others slave.
I'm not even able to stop and restart them (STOP SLAVE; seems to be stucked as well...).

Any idea of what could be wrong here, and how to gracefully restart the slave without killing the mysql process ? (note the CPU of the 2 slaves is currently stucked at 100% as well)

How to repeat:
1 x86-64 Master, 2 x86-64 slaves, 1 x86 slave which fails with an error about MAX_JOIN_SIZE reached during a big join.
No other clues.

Regards,
  Jocelyn
[26 Sep 2006 20:17] jocelyn fournier
Actually I've just figured out the MAX_JOIN_SIZE for both the x86-64 and x86 server :

| max_join_size                   | 18446744073709551615                                                       

So I can't explain the SELECT error on the x86 slave.
[30 Sep 2006 20:49] jocelyn fournier
Hi,

Finally, the problem is different from what I was thinking initialy.
Actually it seems if a failing CREATE TEMPORARY TABLE ... SELECT with too many joins is executed on the master, then this query is replicated on the slave and the thread IO is then stopped. I think the failing CREATE TEMPORARY TABLE should not be replicated on the slave ? (or at least not stop the slave).

Thanks,
  Jocelyn
[5 Oct 2006 8:58] Valeriy Kravchuk
Thank you for a problem report. Please, send my.cnf content from both master and any of the slaves.
[5 Oct 2006 9:13] jocelyn fournier
Master :

[mysqld]
log-bin=mysql-bin
server-id=1
skip-locking
skip-bdb
skip-name-resolve
ft_stopword_file=/dev/null
delay-key-write=OFF
skip-innodb
skip-log-warnings
user            = mysql
port            = 3306
socket          = /tmp/mysql.sock
datadir         = /opt/mysql
tmpdir          = /tmp
set-variable    = ft_min_word_len=1
set-variable    = max_allowed_packet=16M
set-variable    = key_buffer_size=4G
set-variable    = table_cache=16384
set-variable    = read_buffer_size=2M
set-variable    = read_rnd_buffer_size=4M
set-variable    = back_log=300
set-variable    = thread_cache_size=60
set-variable    = query_cache_limit=8M
set-variable    = query_cache_size=256M
set-variable    = delayed_insert_limit=500
set-variable    = max_delayed_threads=60
set-variable    = join_buffer_size=32M
set-variable    = sort_buffer_size=4M
set-variable    = delayed_insert_timeout=20
set-variable    = wait_timeout=120
set-variable    = max_connections=500
set-variable    = myisam_sort_buffer_size=512M
set-variable    = thread_concurrency=8

One slave :

[mysqld]
server-id=2
read-only
skip-locking
skip-bdb
skip-name-resolve
ft_stopword_file=/dev/null
delay-key-write=OFF
skip-innodb
skip-log-warnings
user            = mysql
port            = 3306
socket          = /tmp/mysql.sock
datadir         = /opt/mysql
tmpdir          = /tmp
set-variable    = ft_min_word_len=1
set-variable    = max_allowed_packet=16M
set-variable    = key_buffer_size=4G
set-variable    = table_cache=16384
set-variable    = read_buffer_size=2M
set-variable    = read_rnd_buffer_size=4M
set-variable    = back_log=300
set-variable    = thread_cache_size=60
set-variable    = query_cache_limit=8M
set-variable    = query_cache_size=256M
set-variable    = delayed_insert_limit=500
set-variable    = max_delayed_threads=60
set-variable    = join_buffer_size=32M
set-variable    = sort_buffer_size=4M
set-variable    = delayed_insert_timeout=20
set-variable    = wait_timeout=120
set-variable    = max_connections=500
set-variable    = myisam_sort_buffer_size=512M
set-variable    = thread_concurrency=8
[23 Nov 2006 17:01] Valeriy Kravchuk
Can you, please, upload the binary (relay) log with that (failed) CREATE TEMPORARY TABLE in it? It may be yet another case of http://bugs.mysql.com/bug.php?id=23196. Please, check.
[23 Nov 2006 20:41] jocelyn fournier
Hi,

My issue was definitly not a memory/disk space problem.
As for the binlog, I do not have it anymore, I workarounded the problem by using STRAIGHT join.

Regards,
  Jocelyn
[12 Feb 2007 12:31] Valeriy Kravchuk
We had a CREATE TEMPORARY TABLE ... SELECT ... that executed successfully on master. So, it was written to the binary log (I had checked that failed statement of that kind will not be written to the binary log, at least, on 5.0.36-BK) and ended up in relay logs. Then this statement was executing for too long on some x86_64 slaves, and (due to resources limitation, I suppose) failed on x86 slave. You have to skip that statement on failed slave, or add resources, or make that statement use less resources (as you did). What is the replication bug here then? Please, clarify.

The only potential bug I can identify is in optimizer that spends too much "resources" of some kind on complex queries like the one in this report.
[12 Feb 2007 12:57] jocelyn fournier
Hi,

The big here is why the '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' occurs on the x86 server, and not on the x86-64 ones, since the MAX_JOIN_SIZE is the same on all the machines.

Regards,
  Jocelyn
[12 Feb 2007 12:58] jocelyn fournier
oups, ignore my latest append.
[12 Feb 2007 13:02] jocelyn fournier
Hi,

If you confirm in 5.0.36-BK that failing CREATE TABLE SELECT with MAX_JOIN_SIZE error are not replicated to the slave, then you can close this bug.

Thanks,
  Jocelyn
[4 May 2007 13:15] Valeriy Kravchuk
I've checked with latest 5.0.42-BK in the following way:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.42-debug-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set max_join_size=1000;
Query OK, 0 rows affected (0.00 sec)

mysql> create table tbig(i int auto_increment primary key, c char(100));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tbig(c) values ('123');
Query OK, 1 row affected (0.02 sec)

mysql> insert into tbig(c) select c from tbig;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into tbig(c) select c from tbig;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

...

mysql> insert into tbig(c) select c from tbig;
Query OK, 1024 rows affected (0.02 sec)
Records: 1024  Duplicates: 0  Warnings: 0

mysql> insert into tbig(c) select c from tbig;
Query OK, 2048 rows affected (0.03 sec)
Records: 2048  Duplicates: 0  Warnings: 0

mysql> show variables like 'max_join%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| max_join_size | 1000  |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select t1.c from tbig t1, tbig t2;
ERROR 1104 (42000): 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 SELE
CT is okay
mysql> insert into tbig(c) select t1.c from tbig t1, tbig t2;
ERROR 1104 (42000): 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 SELE
CT is okay
mysql> create temporary table tt1 select t1.c from tbig t1, tbig t2;
ERROR 1104 (42000): 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 SELE
CT is okay
mysql> create temporary table tt1 select t1.c from tbig t1;
Query OK, 4096 rows affected (0.01 sec)
Records: 4096  Duplicates: 0  Warnings: 0

mysql> create temporary table tt2 select t1.c from tbig t1, tbig t2 limit 100;
ERROR 1104 (42000): 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 SELE
CT is okay
mysql> show master status;
+-----------------+----------+--------------+------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------+----------+--------------+------------------+
| suse-bin.000001 |     2012 |              |                  |
+-----------------+----------+--------------+------------------+
1 row in set (0.02 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.0> bin/mysqlbinlog var/suse-bin.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#070504  4:26:04 server id 1  end_log_pos 98    Start: binlog v 4, server v 5.0.
42-debug-log created 070504  4:26:04 at startup
# Warning: this binlog was not closed properly. Most probably mysqld crashed wri
ting it.
ROLLBACK/*!*/;
# at 98
#070504  4:26:32 server id 1  end_log_pos 225   Query   thread_id=1     exec_tim
e=0     error_code=0
use test/*!*/;
SET TIMESTAMP=1178241992/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.uniq
ue_checks=1/*!*/;
SET @@session.sql_mode=0/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.
collation_server=8/*!*/;
create table tbig(i int auto_increment primary key, c char(100))/*!*/;
# at 225
#070504  4:26:40 server id 1  end_log_pos 253   Intvar
SET INSERT_ID=1/*!*/;
# at 253
#070504  4:26:40 server id 1  end_log_pos 350   Query   thread_id=1     exec_tim
e=0     error_code=0
SET TIMESTAMP=1178242000/*!*/;
insert into tbig(c) values ('123')/*!*/;
# at 350
#070504  4:27:05 server id 1  end_log_pos 378   Intvar
SET INSERT_ID=2/*!*/;
# at 378
#070504  4:27:05 server id 1  end_log_pos 479   Query   thread_id=1     exec_tim
e=0     error_code=0
SET TIMESTAMP=1178242025/*!*/;
insert into tbig(c) select c from tbig/*!*/;

...

# at 1511
#070504  4:27:09 server id 1  end_log_pos 1539  Intvar
SET INSERT_ID=513/*!*/;
# at 1539
#070504  4:27:09 server id 1  end_log_pos 1640  Query   thread_id=1     exec_tim
e=0     error_code=0
SET TIMESTAMP=1178242029/*!*/;
insert into tbig(c) select c from tbig/*!*/;
# at 1640
#070504  4:27:09 server id 1  end_log_pos 1668  Intvar
SET INSERT_ID=1025/*!*/;
# at 1668
#070504  4:27:09 server id 1  end_log_pos 1769  Query   thread_id=1     exec_tim
e=0     error_code=0
SET TIMESTAMP=1178242029/*!*/;
insert into tbig(c) select c from tbig/*!*/;
# at 1769
#070504  4:27:10 server id 1  end_log_pos 1797  Intvar
SET INSERT_ID=2049/*!*/;
# at 1797
#070504  4:27:10 server id 1  end_log_pos 1898  Query   thread_id=1     exec_tim
e=0     error_code=0
SET TIMESTAMP=1178242030/*!*/;
insert into tbig(c) select c from tbig/*!*/;
# at 1898
#070504  4:28:11 server id 1  end_log_pos 2012  Query   thread_id=1     exec_tim
e=0     error_code=0
SET TIMESTAMP=1178242091/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
create temporary table tt1 select t1.c from tbig t1/*!*/;
# at 2012
#070504  4:28:47 server id 1  end_log_pos 2130  Query   thread_id=1     exec_tim
e=0     error_code=0
SET TIMESTAMP=1178242127/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=8,@@session
.collation_server=8/*!*/;
DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `test`.`tt1`/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

So, as you can see, CREATE TEMPORARY TABLE ... SELECT that was not executed because of MAX_JOIN_SIZE setting is NOT in binary log on master. Hence, it will not be replicated by any slave.

I am closing this report as 'Can't repeat' on current versions.