max_join_size报错

问题: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 okayMariaDB [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-defaultsmysqld 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 greproot      1252  1237  0 Apr21 pts/0    00:00:00 mysql -U -hlocalhost -uroot -px xxxxxxxxxxxxxxxx --auto-rehashroot      4705  4690  0 Apr21 pts/3    00:00:00 mysql -U -hlocalhost -uroot -px xxxxxxxxxxxxxxxx --auto-rehashmysql    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.pidMariaDB > 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#[e-mail]_CNF=/data/mysql/my.cnf #mysql configure fileMYSQLD_SAFE=/data/mysql/bin/mysqld_safesource /data/ocpyang/mysql_env.iniERRORLOG=`grep -i "^log-error" $MY_CNF |cut -d = -f 2`INIT_NUM=1PORTNUM=`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  ]; thenecho -e '\e[32m   A mysqld process already exists,please stop mysql at first! \e[m'exit 0elseecho "staring local mysql server port is :$MYSQL_PORT"$MYSQLD_SAFE --defaults-file=$MY_CNF  > /dev/null &while  [ $INIT_NUM  -le 5 ]doPORTNUM=`netstat -lnt|grep ${MYSQL_PORT}|wc -l`echo "--------------------------------------------"echo "mysql staring ....Wait a minute..."sleep 10if [ $PORTNUM = 1  ];then echo -e '\e[31m    **** MYSQL  Start Success**** \e[m'exit 0fiINIT_NUM=$(($INIT_NUM +1))donefi  echo -e '\e[32m  mysql start fail,please check the log `echo 'cat ' ${ERRORLOG}` \e[m'echo "*****************************************"exit 0fi但是进一步检查发现这个脚本里并没有limit这个参数值得选项.2）.其次mysql参数文件即my.cnf检查一遍也没找到蛛丝马迹.3).最后想到用户配置文件#ocpyang set(绑定mysql到内网IP只能写内网IP)alias  mysql="mysql -U -hlocalhost -uroot -ppasswd123\!@\#zp01gj "原来如此,果然问题在这里，拿掉-U就ok了.