HI,
I am running Mysql 5.7.11 on windows 7 (32 bit).
My application use ODBC to connect mysql database XYZ.
One of the table of my database is 3.9 GB in size.
When i run a query for this 3.9 GB table from mysql command prompt I get "ERROR 2013 (HY000): Lost connection to MySQL server during query:
I tried to repair the data base using mysqlcheck but mysql dies when it try to repaid that 3.9 GB table.
It seems this particular table is taking no command.(mysql stop after command execution).
Below is my mysql configuration:
"[client]
no-beep
port=3306
[mysql]
default-character-set=utf8
[mysqld]
port=3306
datadir=C:/ProgramData/MySQL/MySQL Server 5.7\Data
character-set-server=utf8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log-output=FILE
general-log=0
general_log_file="[login to view URL]"
slow-query-log=1
slow_query_log_file="[login to view URL]"
long_query_time=10
log-error="[login to view URL]"
server-id=1
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"
max_connections=151
query_cache_size=1M
table_open_cache=2000
tmp_table_size=9M
thread_cache_size=10
#*** MyISAM Specific options
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=10M
key_buffer_size=256M
read_buffer_size=19K
read_rnd_buffer_size=256K
#*** INNODB Specific options ***
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=256M
innodb_log_file_size=76MB
innodb_thread_concurrency=8
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=80
flush_time=0
join_buffer_size=256K
max_allowed_packet=256M
max_connect_errors=100
open_files_limit=4161
query_cache_type=1
sort_buffer_size=256K
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000
plugin_load=0.0
loose_mysqlx_port=0.0 "
1. MySQL version -5.7.11
2. Windows 7 - 32 bit OS / 4 GB RAM
3. Use ODBC to save my application data on MySQL
4. INNODB_per_table - Enable
5. Database name ozekisms. Table to recover "outbox"
6. It is a production server. Before any operation to this server I need to confirm my clients.
I am a faculty teaching the topic of DBMS to university students. I think I can resolve this issue. I will test my change on a backup db so even if you don't consult with your client, it wont make any difference. Please discuss with me the query which is using this big table.