Ошибка Warning: mysqli::query(): (HY001/1038): Out of sort memory, consider increasing server sort buffer size i

[munhgauzen]

Ребята подскажите мб кто сталкивался. VPS ubuntu 20.04

При попытке зайти в админке в каталог товаров получаю вот такую ошибку

Поискал решения  сделал корректировку в

etc/mysql/conf.d/mysql.cnf

 

[mysql]
sort_buffer_size to 512K

Все равно ошибка та же.

 

Warning: mysqli::query(): (HY001/1038): Out of sort memory, consider increasing server sort buffer size in /system/library/db/mysqli.php on line 19Notice: Error: Out of sort memory, consider increasing server sort buffer size
Error No: 1038
SELECT * FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) WHERE pd.language_id = '3' GROUP BY p.product_id ORDER BY pd.name ASC LIMIT 0,20 in/system/library/db/mysqli.php on line 41Notice: Trying to get property of non-object in /system/storage/modification/admin/model/catalog/product.php on line 430Warning: Invalid argument supplied for foreach() in /system/storage/modification/admin/controller/catalog/product.php on line 350

Змінено 28 вересня 2020 користувачем munhgauzen

[Ulv]

А вы перезапускали mysql сервис? 

[AlexDW]

выполните запрос SHOW VARIABLES

и смотрите что в sort_buffer_size

 

также убедитесь что не забыли mysql перезапустить

[munhgauzen]

да перегружал полностью сервер

[munhgauzen]

выполнил SHOW VARIABLES   sort_buffer_size 262144

[AlexDW]

4 минуты назад, munhgauzen сказал:

выполнил SHOW VARIABLES

sort_buffer_size

262144

 

т.е. явно не

35 минут назад, munhgauzen сказал:

Поискал решения  сделал корректировку в etc/mysql/conf.d/mysql.cnf

[mysql]
sort_buffer_size to 512K

 

[Otvet]

41 минуту назад, munhgauzen сказал:

sort_buffer_size to 512K

 

[munhgauzen]

SQL-запрос успешно выполнен.
SHOW VARIABLES
Профилирование [Построчное редактирование] [ Изменить ] [ Создать PHP-код ] [ Обновить]
+ Параметры
Variable_name     Value     
Variable_name     Value     
activate_all_roles_on_login     OFF
admin_address     
admin_port     33062
admin_ssl_ca     
admin_ssl_capath     
admin_ssl_cert     
admin_ssl_cipher     
admin_ssl_crl     
admin_ssl_crlpath     
admin_ssl_key     
admin_tls_ciphersuites     
admin_tls_version     TLSv1,TLSv1.1,TLSv1.2,TLSv1.3
auto_generate_certs     ON
auto_increment_increment     1
auto_increment_offset     1
autocommit     ON
automatic_sp_privileges     ON
avoid_temporal_upgrade     OFF
back_log     151
basedir     /usr/
big_tables     OFF
bind_address     127.0.0.1
binlog_cache_size     32768
binlog_checksum     CRC32
binlog_direct_non_transactional_updates     OFF
binlog_encryption     OFF
binlog_error_action     ABORT_SERVER
binlog_expire_logs_seconds     2592000
binlog_format     ROW
binlog_group_commit_sync_delay     0
binlog_group_commit_sync_no_delay_count     0
binlog_gtid_simple_recovery     ON
binlog_max_flush_queue_time     0
binlog_order_commits     ON
binlog_rotate_encryption_master_key_at_startup     OFF
binlog_row_event_max_size     8192
binlog_row_image     FULL
binlog_row_metadata     MINIMAL
binlog_row_value_options     
binlog_rows_query_log_events     OFF
binlog_stmt_cache_size     32768
binlog_transaction_compression     OFF
binlog_transaction_compression_level_zstd     3
binlog_transaction_dependency_history_size     25000
binlog_transaction_dependency_tracking     COMMIT_ORDER
block_encryption_mode     aes-128-ecb
bulk_insert_buffer_size     8388608
caching_sha2_password_auto_generate_rsa_keys     ON
caching_sha2_password_private_key_path     private_key.pem
caching_sha2_password_public_key_path     public_key.pem
character_set_client     utf8mb4
character_set_connection     utf8mb4
character_set_database     utf8mb4
character_set_filesystem     binary
character_set_results     utf8mb4
character_set_server     utf8mb4
character_set_system     utf8
character_sets_dir     /usr/share/mysql/charsets/
check_proxy_users     OFF
collation_connection     utf8mb4_unicode_ci
collation_database     utf8mb4_0900_ai_ci
collation_server     utf8mb4_0900_ai_ci
completion_type     NO_CHAIN
concurrent_insert     AUTO
connect_timeout     10
core_file     OFF
create_admin_listener_thread     OFF
cte_max_recursion_depth     1000
datadir     /var/lib/mysql/
default_authentication_plugin     mysql_native_password
default_collation_for_utf8mb4     utf8mb4_0900_ai_ci
default_password_lifetime     0
default_storage_engine     InnoDB
default_table_encryption     OFF
default_tmp_storage_engine     InnoDB
default_week_format     0
delay_key_write     ON
delayed_insert_limit     100
delayed_insert_timeout     300
delayed_queue_size     1000
disabled_storage_engines     
disconnect_on_expired_password     ON
div_precision_increment     4
end_markers_in_json     OFF
enforce_gtid_consistency     OFF
eq_range_index_dive_limit     200
error_count     0
event_scheduler     ON
expire_logs_days     0
explicit_defaults_for_timestamp     ON
external_user     
flush     OFF
flush_time     0
foreign_key_checks     ON
ft_boolean_syntax     + -><()~*:""&|
ft_max_word_len     84
ft_min_word_len     4
ft_query_expansion_limit     20
ft_stopword_file     (built-in)
general_log     OFF
Variable_name     Value     
general_log_file     /var/lib/mysql/vps-24336.log
generated_random_password_length     20
group_concat_max_len     1024
group_replication_consistency     EVENTUAL
gtid_executed     
gtid_executed_compression_period     1000
gtid_mode     OFF
gtid_next     AUTOMATIC
gtid_owned     
gtid_purged     
have_compress     YES
have_dynamic_loading     YES
have_geometry     YES
have_openssl     YES
have_profiling     YES
have_query_cache     NO
have_rtree_keys     YES
have_ssl     YES
have_statement_timeout     YES
have_symlink     DISABLED
histogram_generation_max_mem_size     20000000
host_cache_size     279
hostname     vps-24336.vps-default-host.net
identity     0
immediate_server_version     999999
information_schema_stats_expiry     86400
init_connect     
init_file     
init_slave     
innodb_adaptive_flushing     ON
innodb_adaptive_flushing_lwm     10
innodb_adaptive_hash_index     ON
innodb_adaptive_hash_index_parts     8
innodb_adaptive_max_sleep_delay     150000
innodb_api_bk_commit_interval     5
innodb_api_disable_rowlock     OFF
innodb_api_enable_binlog     OFF
innodb_api_enable_mdl     OFF
innodb_api_trx_level     0
innodb_autoextend_increment     64
innodb_autoinc_lock_mode     2
innodb_buffer_pool_chunk_size     134217728
innodb_buffer_pool_dump_at_shutdown     ON
innodb_buffer_pool_dump_now     OFF
innodb_buffer_pool_dump_pct     25
innodb_buffer_pool_filename     ib_buffer_pool
innodb_buffer_pool_in_core_file     ON
innodb_buffer_pool_instances     1
innodb_buffer_pool_load_abort     OFF
innodb_buffer_pool_load_at_startup     ON
innodb_buffer_pool_load_now     OFF
innodb_buffer_pool_size     134217728
innodb_change_buffer_max_size     25
innodb_change_buffering     all
innodb_checksum_algorithm     crc32
innodb_cmp_per_index_enabled     OFF
innodb_commit_concurrency     0
innodb_compression_failure_threshold_pct     5
innodb_compression_level     6
innodb_compression_pad_pct_max     50
innodb_concurrency_tickets     5000
innodb_data_file_path     ibdata1:12M:autoextend
innodb_data_home_dir     
innodb_deadlock_detect     ON
innodb_dedicated_server     OFF
innodb_default_row_format     dynamic
innodb_directories     
innodb_disable_sort_file_cache     OFF
innodb_doublewrite     ON
innodb_doublewrite_batch_size     0
innodb_doublewrite_dir     
innodb_doublewrite_files     2
innodb_doublewrite_pages     4
innodb_fast_shutdown     1
innodb_file_per_table     ON
innodb_fill_factor     100
innodb_flush_log_at_timeout     1
innodb_flush_log_at_trx_commit     1
innodb_flush_method     fsync
innodb_flush_neighbors     0
innodb_flush_sync     ON
innodb_flushing_avg_loops     30
innodb_force_load_corrupted     OFF
innodb_force_recovery     0
innodb_fsync_threshold     0
innodb_ft_aux_table     
innodb_ft_cache_size     8000000
innodb_ft_enable_diag_print     OFF
innodb_ft_enable_stopword     ON
innodb_ft_max_token_size     84
innodb_ft_min_token_size     3
innodb_ft_num_word_optimize     2000
innodb_ft_result_cache_limit     2000000000
innodb_ft_server_stopword_table     
innodb_ft_sort_pll_degree     2
innodb_ft_total_cache_size     640000000
innodb_ft_user_stopword_table     
innodb_idle_flush_pct     100
innodb_io_capacity     200
innodb_io_capacity_max     2000
Variable_name     Value     
innodb_lock_wait_timeout     50
innodb_log_buffer_size     16777216
innodb_log_checksums     ON
innodb_log_compressed_pages     ON
innodb_log_file_size     50331648
innodb_log_files_in_group     2
innodb_log_group_home_dir     ./
innodb_log_spin_cpu_abs_lwm     80
innodb_log_spin_cpu_pct_hwm     50
innodb_log_wait_for_flush_spin_hwm     400
innodb_log_write_ahead_size     8192
innodb_lru_scan_depth     1024
innodb_max_dirty_pages_pct     90.000000
innodb_max_dirty_pages_pct_lwm     10.000000
innodb_max_purge_lag     0
innodb_max_purge_lag_delay     0
innodb_max_undo_log_size     1073741824
innodb_monitor_disable     
innodb_monitor_enable     
innodb_monitor_reset     
innodb_monitor_reset_all     
innodb_numa_interleave     OFF
innodb_old_blocks_pct     37
innodb_old_blocks_time     1000
innodb_online_alter_log_max_size     134217728
innodb_open_files     4000
innodb_optimize_fulltext_only     OFF
innodb_page_cleaners     1
innodb_page_size     16384
innodb_parallel_read_threads     4
innodb_print_all_deadlocks     OFF
innodb_print_ddl_logs     OFF
innodb_purge_batch_size     300
innodb_purge_rseg_truncate_frequency     128
innodb_purge_threads     4
innodb_random_read_ahead     OFF
innodb_read_ahead_threshold     56
innodb_read_io_threads     4
innodb_read_only     OFF
innodb_redo_log_archive_dirs     
innodb_redo_log_encrypt     OFF
innodb_replication_delay     0
innodb_rollback_on_timeout     OFF
innodb_rollback_segments     128
innodb_sort_buffer_size     1048576
innodb_spin_wait_delay     6
innodb_spin_wait_pause_multiplier     50
innodb_stats_auto_recalc     ON
innodb_stats_include_delete_marked     OFF
innodb_stats_method     nulls_equal
innodb_stats_on_metadata     OFF
innodb_stats_persistent     ON
innodb_stats_persistent_sample_pages     20
innodb_stats_transient_sample_pages     8
innodb_status_output     OFF
innodb_status_output_locks     OFF
innodb_strict_mode     ON
innodb_sync_array_size     1
innodb_sync_spin_loops     30
innodb_table_locks     ON
innodb_temp_data_file_path     ibtmp1:12M:autoextend
innodb_temp_tablespaces_dir     ./#innodb_temp/
innodb_thread_concurrency     0
innodb_thread_sleep_delay     10000
innodb_tmpdir     
innodb_undo_directory     ./
innodb_undo_log_encrypt     OFF
innodb_undo_log_truncate     ON
innodb_undo_tablespaces     2
innodb_use_native_aio     ON
innodb_validate_tablespace_paths     ON
innodb_version     8.0.21
innodb_write_io_threads     4
insert_id     0
interactive_timeout     28800
internal_tmp_mem_storage_engine     TempTable
join_buffer_size     262144
keep_files_on_create     OFF
key_buffer_size     16777216
key_cache_age_threshold     300
key_cache_block_size     1024
key_cache_division_limit     100
keyring_operations     ON
large_files_support     ON
large_page_size     0
large_pages     OFF
last_insert_id     0
lc_messages     ru_RU
lc_messages_dir     /usr/share/mysql/
lc_time_names     en_US
license     GPL
local_infile     OFF
lock_wait_timeout     31536000
locked_in_memory     OFF
log_bin     ON
log_bin_basename     /var/lib/mysql/binlog
log_bin_index     /var/lib/mysql/binlog.index
log_bin_trust_function_creators     OFF
log_bin_use_v1_row_events     OFF
log_error     /var/log/mysql/error.log
Variable_name     Value     
log_error_services     log_filter_internal; log_sink_internal
log_error_suppression_list     
log_error_verbosity     2
log_output     FILE
log_queries_not_using_indexes     OFF
log_raw     OFF
log_slave_updates     ON
log_slow_admin_statements     OFF
log_slow_extra     OFF
log_slow_slave_statements     OFF
log_statements_unsafe_for_binlog     ON
log_throttle_queries_not_using_indexes     0
log_timestamps     UTC
long_query_time     10.000000
low_priority_updates     OFF
lower_case_file_system     OFF
lower_case_table_names     0
mandatory_roles     
master_info_repository     TABLE
master_verify_checksum     OFF
max_allowed_packet     67108864
max_binlog_cache_size     18446744073709547520
max_binlog_size     104857600
max_binlog_stmt_cache_size     18446744073709547520
max_connect_errors     100
max_connections     151
max_delayed_threads     20
max_digest_length     1024
max_error_count     1024
max_execution_time     0
max_heap_table_size     16777216
max_insert_delayed_threads     20
max_join_size     18446744073709551615
max_length_for_sort_data     4096
max_points_in_geometry     65536
max_prepared_stmt_count     16382
max_relay_log_size     0
max_seeks_for_key     18446744073709551615
max_sort_length     1024
max_sp_recursion_depth     0
max_user_connections     0
max_write_lock_count     18446744073709551615
min_examined_row_limit     0
myisam_data_pointer_size     6
myisam_max_sort_file_size     9223372036853727232
myisam_mmap_size     18446744073709551615
myisam_recover_options     BACKUP
myisam_repair_threads     1
myisam_sort_buffer_size     8388608
myisam_stats_method     nulls_unequal
myisam_use_mmap     OFF
mysql_native_password_proxy_users     OFF
mysqlx_bind_address     *
mysqlx_compression_algorithms     DEFLATE_STREAM,LZ4_MESSAGE,ZSTD_STREAM
mysqlx_connect_timeout     30
mysqlx_deflate_default_compression_level     3
mysqlx_deflate_max_client_compression_level     5
mysqlx_document_id_unique_prefix     0
mysqlx_enable_hello_notice     ON
mysqlx_idle_worker_thread_timeout     60
mysqlx_interactive_timeout     28800
mysqlx_lz4_default_compression_level     2
mysqlx_lz4_max_client_compression_level     8
mysqlx_max_allowed_packet     67108864
mysqlx_max_connections     100
mysqlx_min_worker_threads     2
mysqlx_port     33060
mysqlx_port_open_timeout     0
mysqlx_read_timeout     30
mysqlx_socket     /var/run/mysqld/mysqlx.sock
mysqlx_ssl_ca     
mysqlx_ssl_capath     
mysqlx_ssl_cert     
mysqlx_ssl_cipher     
mysqlx_ssl_crl     
mysqlx_ssl_crlpath     
mysqlx_ssl_key     
mysqlx_wait_timeout     28800
mysqlx_write_timeout     60
mysqlx_zstd_default_compression_level     3
mysqlx_zstd_max_client_compression_level     11
net_buffer_length     16384
net_read_timeout     30
net_retry_count     10
net_write_timeout     60
new     OFF
ngram_token_size     2
offline_mode     OFF
old     OFF
old_alter_table     OFF
open_files_limit     10000
optimizer_prune_level     1
optimizer_search_depth     62
optimizer_switch     index_merge=on,index_merge_union=on,index_merge_so...
optimizer_trace     enabled=off,one_line=off
optimizer_trace_features     greedy_search=on,range_optimizer=on,dynamic_range=...
optimizer_trace_limit     1
optimizer_trace_max_mem_size     1048576
optimizer_trace_offset     -1
original_commit_timestamp     36028797018963968
Variable_name     Value     
original_server_version     999999
parser_max_mem_size     18446744073709551615
partial_revokes     OFF
password_history     0
password_require_current     OFF
password_reuse_interval     0
performance_schema     ON
performance_schema_accounts_size     -1
performance_schema_digests_size     10000
performance_schema_error_size     4772
performance_schema_events_stages_history_long_size     10000
performance_schema_events_stages_history_size     10
performance_schema_events_statements_history_long_...     10000
performance_schema_events_statements_history_size     10
performance_schema_events_transactions_history_lon...     10000
performance_schema_events_transactions_history_siz...     10
performance_schema_events_waits_history_long_size     10000
performance_schema_events_waits_history_size     10
performance_schema_hosts_size     -1
performance_schema_max_cond_classes     100
performance_schema_max_cond_instances     -1
performance_schema_max_digest_length     1024
performance_schema_max_digest_sample_age     60
performance_schema_max_file_classes     80
performance_schema_max_file_handles     32768
performance_schema_max_file_instances     -1
performance_schema_max_index_stat     -1
performance_schema_max_memory_classes     450
performance_schema_max_metadata_locks     -1
performance_schema_max_mutex_classes     300
performance_schema_max_mutex_instances     -1
performance_schema_max_prepared_statements_instanc...     -1
performance_schema_max_program_instances     -1
performance_schema_max_rwlock_classes     60
performance_schema_max_rwlock_instances     -1
performance_schema_max_socket_classes     10
performance_schema_max_socket_instances     -1
performance_schema_max_sql_text_length     1024
performance_schema_max_stage_classes     175
performance_schema_max_statement_classes     218
performance_schema_max_statement_stack     10
performance_schema_max_table_handles     -1
performance_schema_max_table_instances     -1
performance_schema_max_table_lock_stat     -1
performance_schema_max_thread_classes     100
performance_schema_max_thread_instances     -1
performance_schema_session_connect_attrs_size     512
performance_schema_setup_actors_size     -1
performance_schema_setup_objects_size     -1
performance_schema_users_size     -1
persist_only_admin_x509_subject     
persisted_globals_load     ON
pid_file     /var/lib/mysql/vps-24336.vps-default-host.pid
plugin_dir     /usr/lib/mysql/plugin/
port     3306
preload_buffer_size     32768
print_identified_with_as_hex     OFF
profiling     OFF
profiling_history_size     15
protocol_compression_algorithms     zlib,zstd,uncompressed
protocol_version     10
proxy_user     
pseudo_slave_mode     OFF
pseudo_thread_id     23
query_alloc_block_size     8192
query_prealloc_size     8192
rand_seed1     0
rand_seed2     0
range_alloc_block_size     4096
range_optimizer_max_mem_size     8388608
rbr_exec_mode     STRICT
read_buffer_size     131072
read_only     OFF
read_rnd_buffer_size     262144
regexp_stack_limit     8000000
regexp_time_limit     32
relay_log     vps-24336-relay-bin
relay_log_basename     /var/lib/mysql/vps-24336-relay-bin
relay_log_index     /var/lib/mysql/vps-24336-relay-bin.index
relay_log_info_file     relay-log.info
relay_log_info_repository     TABLE
relay_log_purge     ON
relay_log_recovery     OFF
relay_log_space_limit     0
report_host     
report_password     
report_port     3306
report_user     
require_row_format     OFF
require_secure_transport     OFF
resultset_metadata     FULL
rpl_read_size     8192
rpl_stop_slave_timeout     31536000
schema_definition_cache     256
secondary_engine_cost_threshold     100000.000000
secure_file_priv     /var/lib/mysql-files/
server_id     1
server_id_bits     32
server_uuid     8e9a843f-e9f2-11ea-afc2-00163e005f10
session_track_gtids     OFF
Variable_name     Value     
session_track_schema     ON
session_track_state_change     OFF
session_track_system_variables     time_zone,autocommit,character_set_client,characte...
session_track_transaction_info     OFF
sha256_password_auto_generate_rsa_keys     ON
sha256_password_private_key_path     private_key.pem
sha256_password_proxy_users     OFF
sha256_password_public_key_path     public_key.pem
show_create_table_skip_secondary_engine     OFF
show_create_table_verbosity     OFF
show_old_temporals     OFF
skip_external_locking     ON
skip_name_resolve     OFF
skip_networking     OFF
skip_show_database     OFF
slave_allow_batching     OFF
slave_checkpoint_group     512
slave_checkpoint_period     300
slave_compressed_protocol     OFF
slave_exec_mode     STRICT
slave_load_tmpdir     /tmp
slave_max_allowed_packet     1073741824
slave_net_timeout     60
slave_parallel_type     DATABASE
slave_parallel_workers     0
slave_pending_jobs_size_max     134217728
slave_preserve_commit_order     OFF
slave_rows_search_algorithms     INDEX_SCAN,HASH_SCAN
slave_skip_errors     OFF
slave_sql_verify_checksum     ON
slave_transaction_retries     10
slave_type_conversions     
slow_launch_time     2
slow_query_log     OFF
slow_query_log_file     /var/lib/mysql/vps-24336-slow.log
socket     /var/run/mysqld/mysqld.sock
sort_buffer_size     262144
sql_auto_is_null     OFF
sql_big_selects     ON
sql_buffer_result     OFF
sql_log_bin     ON
sql_log_off     OFF
sql_mode     ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_...
sql_notes     ON
sql_quote_show_create     ON
sql_require_primary_key     OFF
sql_safe_updates     OFF
sql_select_limit     18446744073709551615
sql_slave_skip_counter     0
sql_warnings     OFF
ssl_ca     ca.pem
ssl_capath     
ssl_cert     server-cert.pem
ssl_cipher     
ssl_crl     
ssl_crlpath     
ssl_fips_mode     OFF
ssl_key     server-key.pem
stored_program_cache     256
stored_program_definition_cache     256
super_read_only     OFF
sync_binlog     1
sync_master_info     10000
sync_relay_log     10000
sync_relay_log_info     10000
system_time_zone     UTC
table_definition_cache     2000
table_encryption_privilege_check     OFF
table_open_cache     4000
table_open_cache_instances     16
tablespace_definition_cache     256
temptable_max_ram     1073741824
temptable_use_mmap     ON
thread_cache_size     9
thread_handling     one-thread-per-connection
thread_stack     286720
time_zone     SYSTEM
timestamp     1601302152.907774
tls_ciphersuites     
tls_version     TLSv1,TLSv1.1,TLSv1.2,TLSv1.3
tmp_table_size     16777216
tmpdir     /tmp
transaction_alloc_block_size     8192
transaction_allow_batching     OFF
transaction_isolation     REPEATABLE-READ
transaction_prealloc_size     4096
transaction_read_only     OFF
transaction_write_set_extraction     XXHASH64
unique_checks     ON
updatable_views_with_limit     YES
use_secondary_engine     ON
validate_password.check_user_name     ON
validate_password.dictionary_file     
validate_password.length     8
validate_password.mixed_case_count     1
validate_password.number_count     1
validate_password.policy     MEDIUM
validate_password.special_char_count     1
version     8.0.21-0ubuntu0.20.04.4
version_comment     (Ubuntu)
Variable_name     Value     
version_compile_machine     x86_64
version_compile_os     Linux
version_compile_zlib     1.2.11
wait_timeout     28800
warning_count     0
windowing_use_high_precision     ON

Змінено 28 вересня 2020 користувачем munhgauzen

[neonox]

Вам же уже указали на 2 ошибки:

1. sort_buffer_size = 512K, а не sort_buffer_size to 512K

2. У вас совсем другая цифра в выводе

 

[private]

Слышу звон, но не вижу где он.

[munhgauzen]

всем спасибо за подсказки. Решение:

выполнить запрос:

SET GLOBAL sort_buffer_size = 1024000

 

1024 - на всякий случай)