Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Not a Bug
-
Affects Version/s: 5.5.33a
-
Fix Version/s: None
-
Component/s: None
-
Labels:None
-
Environment:FreeBSD 9.1-RELEASE-p4
Description
sometimes it fails, not each time:
$ /usr/local/bin/mysqldump -a --add-drop-table --skip-lock-tables $DB_NAME `/bin/cat tables.lst` | /usr/bin/bzip2 -c >dump-$PFX.dump.bz2 mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `gals_kw2_inno`': 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 (1104)
+ the problem, that sql_big_selects=OFF by default.
on mysql start:
MariaDB [dfrot]> show variables like '%join%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | join_buffer_size | 33554432 | | join_buffer_space_limit | 2097152 | | join_cache_level | 2 | | max_join_size | 16777216 | | sql_max_join_size | 16777216 | +-------------------------+----------+ MariaDB [dfrot]> show variables like '%big%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | big_tables | OFF | | sql_big_selects | OFF | | sql_big_tables | OFF | +-----------------+-------+
MariaDB [dfrot]> show table status like 'gals_kw2_inno'; +---------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +---------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ | gals_kw2_inno | InnoDB | 10 | Compact | 93515627 | 40 | 3778691072 | 0 | 1797046272 | 5242880 | NULL | 2013-08-21 14:42:57 | NULL | NULL | latin1_swedish_ci | NULL | | | +---------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ MariaDB [dfrot]> select count(gid) from gals_kw2_inno; +------------+ | count(gid) | +------------+ | 80122933 | +------------+ 1 row in set (38.10 sec)
my.cnf:
# The MySQL server [mysqld] #innodb_force_recovery = 6 user=mysql bind-address=127.0.0.1 port = 3306 socket = /tmp/mysql.sock #skip-locking myisam-recover #safe_show_database skip-name-resolve innodb_file_per_table=1 myisam_sort_buffer_size = 512M max_allowed_packet = 16M table_cache = 2000 thread_cache = 10 query_cache_size= 256M query_cache_type= 1 query_cache_limit = 256M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 # flush all tables every day flush_time = 86400 interactive_timeout = 600 max_connections = 200 max_connect_errors = 1000 max_user_connections = 1000 wait_timeout = 300 #innodb_force_recovery=1 back_log = 120 max_tmp_tables=1500 default_storage_engine=myisam innodb_buffer_pool_size= 6144M key_buffer_size = 4096M sort_buffer_size = 4M read_buffer_size = 2M max_heap_table_size = 6144M tmp_table_size = 6144M join_buffer_size = 32M aria-sort-buffer-size=512M thread-handling=pool-of-threads thread-pool-size=16 innodb_log_file_size=768M innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=2 innodb_flush_method = O_DIRECT ##innodb_force_recovery=1 innodb_read_io_threads=64 innodb_write_io_threads=64 innodb_io_capacity=20000 #(set this to your device's IOPs) #long_query_time=1 #log_slow_queries=/home/logs/mysql_slow.log #log_queries_not_using_indexes=1 tmpdir = /var/tmp_md #tmpdir = /home/tmp table_open_cache = 2048 binlog_cache_size = 1M read_rnd_buffer_size = 16M thread_cache_size = 16 innodb_additional_mem_pool_size = 16M #innodb_read_io_threads=2 #innodb_write_io_threads=2 innodb_lock_wait_timeout = 120 [mysqldump] max_allowed_packet = 16M [myisamchk] key_buffer_size=512M sort_buffer_size=512M read_buffer_size=1M write_buffer_size=1M
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
from /usr/ports/databases/mariadb55-server
there is only one application on server, and it does not set max_join_size (rechecked all sripts),
and it is 16777216 just after mysql start (checked).
Please also check if you have cnf files in other default locations, which the server might have picked up in addition to the desired one. They cannot set sql_big_selects, but might (and probably do) set max_join_size=16M, which, in turn, will cause automatic switch-off for sql_big_selects.
Or, if it's easier, try to start server with the explicit --defaults-file pointing at the cnf that you pasted, and see whether the problem persists.
oops.
i really found another my.cnf with max_join_size=16M.
i removed it and sql_big_selects=on now.
i'll check if mysqldump would not fail in a few days and then i will close the ticket.
In fact, max_join_size is 18446744073709551615 by default, and sql_big_selects is ON.
Do you use our binaries or from the FreeBSD Ports? Perhaps the defaults there are different (though, it's unlikely).
Perhaps one of the applications has changed max_join_size on your server, like