max_join_size报错

当尝试执行大型JOIN查询时,遇到了MySQL的MAX_JOIN_SIZE错误。通过设置SQL_BIG_SELECTS=1可以临时解决,但问题根源在于启动脚本、my.cnf配置文件或用户配置。检查发现用户配置文件中的绑定IP导致了限制,移除特定参数后问题解决。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

问题:

MariaDB [fltpricedb]> select count(id) from fdflightcabinpricedetail;
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 MAX_JOIN_SIZE=# if the SELECT is okay
MariaDB [fltpricedb]> SET SQL_BIG_SELECTS=1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [fltpricedb]> select count(id) from fdflightcabinpricedetail;
+-----------+
| count(id) |
+-----------+
|  11491064 |
+-----------+
1 row in set (2.50 sec)


思路:

#/data/mysql/bin/my_print_defaults  --mysqld

--lower_case_table_names=1
--port=33107
--socket=/data/mysql/mysql.sock
--pid-file=/data/mysql/db01.zp.com.pid
--basedir=/data/mysql/
--datadir=/data/mysql/data
--tmpdir=/data/mysql/tmp
--character-set-server=utf8
--default-storage-engine=INNODB
--sql-mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
--table_open_cache=2000
--open_files_limit=65535
--sort_buffer_size=2M
--thread_cache_size=300
--tmp_table_size=256M
--key_buffer_size=2048M
--read_buffer_size=1M
--read_rnd_buffer_size=16M
--query_cache_type=0
--query_cache_size=20M
--query_cache_limit=0M
--skip-name-resolve
--skip-host-cache
--skip-external-locking
--init_connect=SET autocommit  =  0
--init_connect=SET NAMES utf8
--init_connect=SET character_set_client  =  utf8
--init_connect=SET character_set_results  =  utf8
--init_connect=SET character_set_connection  =  utf8
--skip-character-set-client-handshake
--wait_timeout=10
--max_allowed_packet=64M
--max_connections=1200
--max_connect_errors=6000
--event_scheduler=ON
--general_log=0
--general_log_file=/data/mysql/mysql_logs/mysql.log
--log-output=file
--log_warnings=2
--back_log=1500
--server_id=1010
--binlog-format=ROW
--log-output=file
--log-bin=/data/mysql/mysql_logs/binary_log/mysqldb01-bin
--log-bin-index=/data/mysql/mysql_logs/binary_log/mysqldb01-bin.index
--binlog_cache_size=4M
--max_binlog_size=100M
--max_binlog_cache_size=512m
--expire_logs_days=10
--binlog-checksum=CRC32
--master-verify-checksum=1
--slave-sql-verify-checksum=1
--sync-master-info=1
--relay-log=/data/mysql/mysql_logs/relay-log
--relay-log-index=/data/mysql/mysql_logs/relay-log.index
--relay_log_purge=1
--relay_log_recovery=1
--master-info-file=/data/mysql/data/master.info
--sync_master_info=1
--relay-log-info-file=/data/mysql/data/relay-log.info
--replicate-ignore-db=mysql,information_schema
--sync_binlog=1
--innodb_support_xa=1
--binlog_commit_wait_count=20
--binlog_commit_wait_usec=50000
--slave_parallel_threads=8
--log-slave-updates=true
--report-host=192.168.0.187
--report-port=33107
--log-error=/data/mysql/mysql_logs/error_log/error.log
--slow_query_log=1
--slow_query_log_file=/data/mysql/mysql_logs/slow_query_log/slowquery.log
--long_query_time=3
--log-output=file
--log_queries_not_using_indexes=1
--myisam_max_sort_file_size=100G
--myisam_sort_buffer_size=58M
--innodb_buffer_pool_instances=8
--innodb_data_home_dir=/data/mysql/innodb_data
--innodb_data_file_path=ibdata1:200M;ibdata2:200M:autoextend
--innodb_file_per_table=1
--innodb_log_buffer_size=16M
--innodb_buffer_pool_size=52000M
--innodb_log_group_home_dir=/data/mysql/mysql_logs/innodb_log/
--innodb_log_files_in_group=3
--innodb_log_file_size=2G
--innodb_max_dirty_pages_pct=90
--innodb_flush_log_at_trx_commit=2
--innodb_thread_concurrency=0
--innodb_thread_sleep_delay=500
--innodb_concurrency_tickets=5000
--innodb_read_io_threads=16
--innodb_write_io_threads=4
--innodb_io_capacity=2000
--innodb_lock_wait_timeout=30
--innodb_purge_threads=1
--innodb_purge_batch_size=300
--innodb_open_files=2000
--innodb_read_ahead_threshold=56
--innodb_flush_method=O_DIRECT
--innodb_use_native_aio=on
--innodb_use_sys_malloc=on
--innodb_buffer_pool_dump_at_shutdown=1
--innodb_buffer_pool_dump_now=1
--innodb_buffer_pool_load_at_startup=1
--innodb_buffer_pool_load_now=1
--thread_handling=pool-of-threads
--innodb_buffer_pool_populate=1



#mysqld --print-defaults

mysqld would have been started with the following arguments:
--lower_case_table_names=1 --port=33107 --socket=/data/mysql/mysql.sock --pid-file=/data/mysql/db01.zp.com.pid --basedir=/data/mysql/ --datadir=/data/mysql/data --tmpdir=/data/mysql/tmp --character-set-server=utf8 --default-storage-engine=INNODB --sql-mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION --table_open_cache=2000 --open_files_limit=65535 --sort_buffer_size=2M --thread_cache_size=300 --tmp_table_size=256M --key_buffer_size=2048M --read_buffer_size=1M --read_rnd_buffer_size=16M --query_cache_type=0 --query_cache_size=20M --query_cache_limit=0M --skip-name-resolve --skip-host-cache --skip-external-locking --init_connect=SET autocommit  =  0 --init_connect=SET NAMES utf8 --init_connect=SET character_set_client  =  utf8 --init_connect=SET character_set_results  =  utf8 --init_connect=SET character_set_connection  =  utf8 --skip-character-set-client-handshake --wait_timeout=10 --max_allowed_packet=64M --max_connections=1200 --max_connect_errors=6000 --event_scheduler=ON --general_log=0 --general_log_file=/data/mysql/mysql_logs/mysql.log --log-output=file --log_warnings=2 --back_log=1500 --server_id=1010 --binlog-format=ROW --log-output=file --log-bin=/data/mysql/mysql_logs/binary_log/mysqldb01-bin --log-bin-index=/data/mysql/mysql_logs/binary_log/mysqldb01-bin.index --binlog_cache_size=4M --max_binlog_size=100M --max_binlog_cache_size=512m --expire_logs_days=10 --binlog-checksum=CRC32 --master-verify-checksum=1 --slave-sql-verify-checksum=1 --sync-master-info=1 --relay-log=/data/mysql/mysql_logs/relay-log --relay-log-index=/data/mysql/mysql_logs/relay-log.index --relay_log_purge=1 --relay_log_recovery=1 --master-info-file=/data/mysql/data/master.info --sync_master_info=1 --relay-log-info-file=/data/mysql/data/relay-log.info --replicate-ignore-db=mysql,information_schema --sync_binlog=1 --innodb_support_xa=1 --binlog_commit_wait_count=20 --binlog_commit_wait_usec=50000 --slave_parallel_threads=8 --log-slave-updates=true --report-host=192.168.0.187 --report-port=33107 --log-error=/data/mysql/mysql_logs/error_log/error.log --slow_query_log=1 --slow_query_log_file=/data/mysql/mysql_logs/slow_query_log/slowquery.log --long_query_time=3 --log-output=file --log_queries_not_using_indexes=1 --myisam_max_sort_file_size=100G --myisam_sort_buffer_size=58M --innodb_buffer_pool_instances=8 --innodb_data_home_dir=/data/mysql/innodb_data --innodb_data_file_path=ibdata1:200M;ibdata2:200M:autoextend --innodb_file_per_table=1 --innodb_log_buffer_size=16M --innodb_buffer_pool_size=52000M --innodb_log_group_home_dir=/data/mysql/mysql_logs/innodb_log/ --innodb_log_files_in_group=3 --innodb_log_file_size=2G --innodb_max_dirty_pages_pct=90 --innodb_flush_log_at_trx_commit=2 --innodb_thread_concurrency=0 --innodb_thread_sleep_delay=500 --innodb_concurrency_tickets=5000 --innodb_read_io_threads=16 --innodb_write_io_threads=4 --innodb_io_capacity=2000 --innodb_lock_wait_timeout=30 --innodb_purge_threads=1 --innodb_purge_batch_size=300 --innodb_open_files=2000 --innodb_read_ahead_threshold=56 --innodb_flush_method=O_DIRECT --innodb_use_native_aio=on --innodb_use_sys_malloc=on --innodb_buffer_pool_dump_at_shutdown=1 --innodb_buffer_pool_dump_now=1 --innodb_buffer_pool_load_at_startup=1 --innodb_buffer_pool_load_now=1 --thread_handling=pool-of-threads --innodb_buffer_pool_populate=1 




# ps -ef|grep mysql|grep -v grep
root      1252  1237  0 Apr21 pts/0    00:00:00 mysql -U -hlocalhost -uroot -px xxxxxxxxxxxxxxxx --auto-rehash
root      4705  4690  0 Apr21 pts/3    00:00:00 mysql -U -hlocalhost -uroot -px xxxxxxxxxxxxxxxx --auto-rehash
mysql    15599  8845 99 Apr21 pts/4    4-12:26:03 /data/mysql/bin/mysqld --basedir=/data/mysql/ --datadir=/data/mysql/data --plugin-dir=/data/mysql//lib/plugin --user=mysql --log-error=/data/mysql/mysql_logs/error_log/error.log --open-files-limit=28192 --pid-file=/data/mysql/db01.zp.com.pid





MariaDB > SELECT @@max_join_size, @@global.max_join_size;




解决办法:
--1.手动每个会话设置
SET SQL_BIG_SELECTS=1;



---2.手动设置,寻找根源
SELECT @@max_join_size, @@global.max_join_size;


set @@local.max_join_size=18446744073709551615, @@global.max_join_size=18446744073709551615;


select count(id) from fdflightcabinpricedetail;


但是查询

> SELECT @@max_join_size, @@global.max_join_size,@@local.max_join_size;

+----------------------+------------------------+-----------------------+
| @@max_join_size      | @@global.max_join_size | @@local.max_join_size |
+----------------------+------------------------+-----------------------+
| 1000000 |   18446744073709551615 |  18446744073709551615 |
+----------------------+------------------------+-----------------------+
1 row in set (0.00 sec)


说明修改的值是生效了但是被一些设置强制限制了,有哪几种可能呢?

1).首先想到的是mysql使用的是安全启动.
查看启动脚本,确实使用的是mysqld_safe

# cat /data/ocpyang/mysqlstartupv.sh 
#!/bin/sh

#ocpyang@126.com

MY_CNF=/data/mysql/my.cnf #mysql configure file

MYSQLD_SAFE=/data/mysql/bin/mysqld_safe

source /data/ocpyang/mysql_env.ini

ERRORLOG=`grep -i "^log-error" $MY_CNF |cut -d = -f 2`

INIT_NUM=1

PORTNUM=`netstat -lnt|grep ${MYSQL_PORT}|wc -l`




if [ ! -x $MYSQLD_SAFE ]; then
  echo "mysql not install $MYSQLD_SAFE or NO PRIVILEGES"
  exit 1  
else   
	if [ $PORTNUM = 1  ]; then
	echo -e '\e[32m   A mysqld process already exists,please stop mysql at first! \e[m'
	exit 0
	else
		echo "staring local mysql server port is :$MYSQL_PORT"
		$MYSQLD_SAFE --defaults-file=$MY_CNF  > /dev/null &
		while  [ $INIT_NUM  -le 5 ]
		do
		PORTNUM=`netstat -lnt|grep ${MYSQL_PORT}|wc -l`
		echo "--------------------------------------------"
		echo "mysql staring ....Wait a minute..."
		sleep 10
			if [ $PORTNUM = 1  ];
			then
 			echo -e '\e[31m    **** MYSQL  Start Success**** \e[m'
			exit 0
			fi
	
		INIT_NUM=$(($INIT_NUM +1))
		done
	fi
  	echo -e '\e[32m  mysql start fail,please check the log `echo 'cat ' ${ERRORLOG}` \e[m'
	echo "*****************************************"
	exit 0
fi


但是进一步检查发现这个脚本里并没有limit这个参数值得选项.


2).其次mysql参数文件即my.cnf
检查一遍也没找到蛛丝马迹.


3).最后想到用户配置文件


#ocpyang set(绑定mysql到内网IP只能写内网IP)
alias  mysql="mysql -U -hlocalhost -uroot -ppasswd123\!@\#zp01gj "

原来如此,果然问题在这里,拿掉-U就ok了.




<think>嗯,用户遇到了SQL中CTE递归的最大深度限制问题。我需要先回忆一下CTE递归的基本概念以及不同数据库系统的限制。首先,CTE(公共表表达式)的递归查询在SQL Server、PostgreSQL和Oracle中的实现可能有所不同。 SQL Server默认的递归深度是100层,超过会报错。我记得可以通过OPTION (MAXRECURSION number)来调整这个限制,但最大值是32767。不过用户可能需要知道这个调整是否会影响性能,或者是否有其他方法绕过限制。 PostgreSQL默认没有限制,但受限于内存和配置参数,比如stack_depth,通常默认是2MB。可能需要检查是否因为栈溢出导致的问题,这时候需要调整服务器配置或者优化查询。 Oracle的递归查询使用CONNECT BY,但默认限制可能不同,或者有没有明确的限制?可能需要确认Oracle的文档,不过用户的问题可能主要集中在SQL Server和PostgreSQL上。 用户可能需要解决的方法包括:检查递归逻辑是否正确,避免无限循环;调整最大递归层数;或者将递归转换为迭代,比如使用临时表和循环。此外,分步处理数据或者优化查询条件也可能有效。 还要考虑生成相关问题,比如如何调试无限递归,不同数据库的限制差异,或者替代方案。需要确保回答准确,引用来源,并且语法正确,比如使用$$包裹独立公式。</think># SQL CTE递归最大深度限制及解决方法 ## 1. 主要数据库的递归深度限制 - **SQL Server**: 默认最大递归深度为$100$层,超过时抛出`Maximum recursion depth exhausted`错误[^1]。可通过查询提示扩展: ```sql OPTION (MAXRECURSION 32767) -- 最大允许值32767 ``` - **PostgreSQL**: 无硬性限制,但受`stack_depth`参数限制(默认$2MB$),实际限制约$10^5$层级[^2] - **Oracle**: 使用`CONNECT BY`时默认层级不限,但受`SESSION_MAX_STACK_SIZE`参数影响 ## 2. 常见解决方法 ### (1) 检查递归逻辑 验证递归出口条件,防止无限循环。例如检查树结构是否存在循环引用: ```sql WITH RECURSIVE cte AS ( SELECT id, parent_id, CAST(id AS VARCHAR(MAX)) AS path FROM hierarchy WHERE parent_id IS NULL UNION ALL SELECT child.id, child.parent_id, cte.path + '->' + CAST(child.id AS VARCHAR) FROM hierarchy child JOIN cte ON child.parent_id = cte.id WHERE CHARINDEX(CAST(child.id AS VARCHAR), cte.path) = 0 -- 防止循环 ) ``` ### (2) 分层处理数据 将递归拆分为多阶段处理: ```sql CREATE TABLE #temp (id INT, level INT); INSERT INTO #temp SELECT root_id, 0; WHILE @@ROWCOUNT > 0 BEGIN INSERT INTO #temp SELECT child.id, t.level + 1 FROM hierarchy child JOIN #temp t ON child.parent_id = t.id WHERE NOT EXISTS ( SELECT 1 FROM #temp WHERE id = child.id ) END ``` ### (3) 优化查询条件 添加有效的过滤条件减少递归层级: $$ \text{执行时间} \propto \frac{N}{\text{过滤效率}} $$ 其中$N$为数据量,通过索引优化可提升过滤效率 ## 3. 性能对比 | 方法 | 时间复杂度 | 适用场景 | 内存消耗 | |---------------|------------|-------------------|----------| | 原生递归CTE | O(n) | 深层级简单查询 | 高 | | 临时表迭代 | O(n) | 超深层级处理 | 中 | | 物化路径 | O(1) | 频繁层级查询 | 低 | [^1]: Microsoft SQL Server文档 [^2]: PostgreSQL官方配置手册
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值