I've an online game the game server execute more than 1M queries [ SELECT , REPLACE , UPDATE ] every 10 sec the problem the memory usage still increasing i dun know why ?? i mean if we suppose that memory usage at the moment is 2000M and it increases to 3000M when the game server execute the queries . why don't the memory usage back again to 2000M or around value after excuting ??? .. it stills increasing and increasing This Problem Makes The Following Error Occur 2016-7-14 16:17:52 -- ERROR: Database ExecuteSQL(MySQL client run out of memory) occur mysql error(SELECT * FROM cq_tutor WHERE tutor_id = 16090848 AND Betrayal_flag = 0). 2016-7-14 16:17:52 -- ERROR: Database ExecuteSQL(Lost connection to MySQL server during query) occur mysql error(SELECT * FROM cq_tutor WHERE Student_id = 16090848 AND Betrayal_flag = 0 LIMIT 1). btw when i restart the game server the memory usage back to 2000M and still increasing too .. I want to know the reason of memory usage increasing i stopped caching of mysql but the same problem occur mysql version is 4.0.17 the client i use is winmysqladmin 1.4 mysql> SHOW VARIABLES; +------------------------------+----------------------------------- | Variable_name | Value | +------------------------------+----------------------------------- | back_log | 50 | basedir | E:\ZFSERVER\MYSQL\ | binlog_cache_size | 32768 | bulk_insert_buffer_size | 8388608 | character_set | latin1 | character_sets | latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 | | concurrent_insert | ON | connect_timeout | 5 | convert_character_set | | datadir | E:\ZFSERVER\MYSQL\data\ | default_week_format | 0 | delay_key_write | ON | delayed_insert_limit | 100 | delayed_insert_timeout | 300 | delayed_queue_size | 1000 | flush | OFF | flush_time | 1800 | ft_boolean_syntax | + -> SHOW STATUS; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Aborted_clients | 36 | | Aborted_connects | 0 | | Bytes_received | 41352179 | | Bytes_sent | 84359720 | | Com_admin_commands | 181 | | Com_alter_table | 2 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin | 0 | | Com_change_db | 64 | | Com_change_master | 0 | | Com_check | 1 | | Com_commit | 0 | | Com_create_db | 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_table | 252 | | Com_delete | 70 | | Com_delete_multi | 0 | | Com_drop_db | 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 276 | | Com_flush | 0 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_insert | 292241 | | Com_insert_select | 0 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables | 0 | | Com_optimize | 2 | | Com_purge | 0 | | Com_rename_table | 0 | | Com_repair | 9 | | Com_replace | 6 | | Com_replace_select | 0 | | Com_reset | 0 | | Com_restore_table | 0 | | Com_revoke | 0 | | Com_rollback | 0 | | Com_savepoint | 0 | | Com_select | 15736 | | Com_set_option | 12 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 0 | | Com_show_create | 25 | | Com_show_databases | 27 | | Com_show_fields | 2760 | | Com_show_grants | 0 | | Com_show_keys | 9 | | Com_show_logs | 0 | | Com_show_master_status | 0 | | Com_show_new_master | 0 | | Com_show_open_tables | 0 | | Com_show_processlist | 2 | | Com_show_slave_hosts | 0 | | Com_show_slave_status | 0 | | Com_show_status | 1934 | | Com_show_innodb_status | 0 | | Com_show_tables | 57 | | Com_show_variables | 59 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_truncate | 0 | | Com_unlock_tables | 0 | | Com_update | 2138 | | Connections | 207 | | Created_tmp_disk_tables | 0 | | Created_tmp_tables | 0 | | Created_tmp_files | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Delayed_errors | 0 | | Flush_commands | 1 | | Handler_commit | 4 | | Handler_delete | 1 | | Handler_read_first | 3363 | | Handler_read_key | 36141 | | Handler_read_next | 68771 | | Handler_read_prev | 0 | | Handler_read_rnd | 132 | | Handler_read_rnd_next | 3039359 | | Handler_rollback | 63 | | Handler_update | 10 | | Handler_write | 292202 | | Key_blocks_used | 2157 | | Key_read_requests | 459308 | | Key_reads | 352 | | Key_write_requests | 197090 | | Key_writes | 197085 | | Max_used_connections | 24 | | Not_flushed_key_blocks | 0 | | Not_flushed_delayed_rows | 0 | | Open_tables | 0 | | Open_files | 0 | | Open_streams | 0 | | Opened_tables | 1906 | | Questions | 315904 | | Qcache_queries_in_cache | 0 | | Qcache_inserts | 0 | | Qcache_hits | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 0 | | Qcache_free_memory | 0 | | Qcache_free_blocks | 0 | | Qcache_total_blocks | 0 | | Rpl_status | NULL | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 129 | | Select_range_check | 0 | | Select_scan | 2691 | | Slave_open_temp_tables | 0 | | Slave_running | OFF | | Slow_launch_threads | 0 | | Slow_queries | 0 | | Sort_merge_passes | 0 | | Sort_range | 22 | | Sort_rows | 132 | | Sort_scan | 6 | | Table_locks_immediate | 311455 | | Table_locks_waited | 0 | | Threads_cached | 0 | | Threads_created | 206 | | Threads_connected | 4 | | Threads_running | 1 | | Uptime | 21423 | +-------------------------+----------+ ----------------------------------------------- select che from arenaa where mapid=81513 and status=1 select nameb from arenac where mapid=81529 DELETE FROM arenaa where mapid=81529 select nameb from arenac where mapid=81530 select che from arenaa where mapid=81501 and status=2 REPLACE INTO arenac set mapid=\"81502\",nameb=\"%user_name\" select namea from arenaa where mapid=81505 ------------------------------------------------ -- ---------------------------- -- Table structure for arenaa -- ---------------------------- DROP TABLE IF EXISTS arenaa; CREATE TABLE arenaa ( mapid int(100) NOT NULL default '0', namea varchar(100) NOT NULL default '0', nameb varchar(100) NOT NULL default '0', status int(100) NOT NULL default '0', che int(100) NOT NULL default '1', UNIQUE KEY imapid (mapid) ) TYPE=MyISAM; -- ---------------------------- -- Records of arenaa -- ---------------------------- ############################################# SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for arenac -- ---------------------------- DROP TABLE IF EXISTS arenac; CREATE TABLE arenac ( mapid int(100) NOT NULL default '0', nameb varchar(100) NOT NULL default '0', UNIQUE KEY imapid (mapid) ) TYPE=MyISAM; -- ---------------------------- -- Records of arenac -- ---------------------------- thanks for reading ,,

We have MySQL (version 8.x) with InnoDB engine. We were doing weekly MySQL restart (for rotating log files, like slow-query, error log etc) earlier and our RAM usage was in under control. Sometime back we started using mysqladmin flush-logs without restarting MySQL process. After this we are seeing the memory usage increases proportional to time. Does flush-logs remove's slow-query.log and host_name.err log files from RAM or keep them as cached? We are keeping 4 old log files and only renaming (mv command) them in flush-log operation. Will moving them to another folder helps?

Do wait_timeout session clear all running session or only sessions which are in SLEEP mode for specified seconds ?

I am using MariaDB(10.1.21) with following storage engines: - InnoDB - TokuDB - Spider Engine System Configuration is : - 3GB RAM - Dual Core Processor I have tried pt-mysql-summary tool to identify the memory usage and it shows 90% of innodb buffer pool size is filled,when no process is running on server. I have also tried restarting the MariaDB server and this reduces memory for about 1-2 hours only and after that it eats up memory. Any clue why it is consuming too much memory? Sharing my configurations below: **My.cnf** [client] port = 3306 socket = /var/run/mysqld/mysqld.sock # Here is entries for some specific programs # The following values assume you have at least 32M ram # This was formally known as [safe_mysqld]. Both versions are currently parsed. [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] #performance_schema=ON tmp_table_size=16M max_heap_table_size=16M skip-log-bin #table_open_cache =16384 #table_definition_cache =16384 # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc_messages_dir = /usr/share/mysql lc_messages = en_US skip-external-locking #bind-address = 127.0.0.1 #skip-networking # SAFETY # max_allowed_packet= 16M max-connect-errors = 1000000 skip-name-resolve #sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY # CACHES AND LIMITS # tmp-table-size = 32M max-heap-table-size = 32M query-cache-type = 0 query-cache-size = 0 max-connections = 500 thread-cache-size = 50 open-files-limit = 65535 table-definition-cache = 1024 table-open-cache = 2048 # LOGGING # log_warnings = 2 slow_query_log =1 log-output = TABLE long_query_time = 5 log_slow_verbosity = query_plan log-error =/var/log/mysql/system_error.err # INNODB # innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-file-size = 128M innodb-flush-log-at-trx-commit = 1 innodb-file-per-table = 1 innodb-buffer-pool-size = 1720M [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] #no-auto-rehash # faster start of mysql but no tab completion [isamchk] key_buffer = 16M **TokuDB.cnf** plugin-load-add=ha_tokudb.so tokudb_data_dir=/var/lib/mysql/toku_db_data tokudb_read_block_size=64k tokudb_row_format=tokudb_zlib tokudb_directio=on **Spider.cnf** [mariadb] #spider_internal_limit =1 spider_direct_order_limit =1 spider_skip_default_condition =1 spider_casual_read =1 spider_bgs_mode =2 spider_direct_dup_insert =1 spider_auto_increment_mode =2 #optimizer_switch='engine_condition_pushdown=on' #optimizer_switch='mrr=on,mrr_sort_keys=off'

I'm trying to load mysqldump and I keep getting following error: > ERROR 2013 (HY000) at line X: Lost connection to MySQL server during > query /etc/my.cnf: [mysqld] max_allowed_packet = 16M net_read_timeout = 30 net_write_timeout = 60 ... [mysqldump] max_allowed_packet = 16M I tried to increase these values, but I keep getting that error no matter what( What else can I do to overcome this error?

We have deployed REDIS to hold some frequent query result to reduce number of connections made on Database server. We need to find out Max_used_connection after deploying REDIS. So we plan to reset mysql status variable Max_used_connection . While googling i found to reset Max_used_connections we need to use FLUSH STATUS. Is it safe to execute **Flush status** in production ? MySQL docs says: > **FLUSH STATUS** - resets the counters for key caches (default and named) to zero and sets Max_used_connections to the current number of open connections. I don't know impact of resetting counters for key caches. Is it possible to reset Max_used_connections alone ? Thanks in Advance.

Using Debian 10.10. I have a MySQL problem. service mysql reload results 'Access denied' service mysql start executes correctly. All databases are working correctly after starting mysql. Strange, that reloading results access denied error. Connecting to mysql with mysql -p -u root -h localhost connects successfully. Only mysqladmin has problems: mysqladmin start mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' mysqladmin reload mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' Any idea what is the problem with mysqladmin? Maybe some permission problem?

I am using MySQL Workbench for many months, it was working fine. Today, when I tried to open Server Status in the MANAGEMENT tab, below error occurred. MYSQL80 and Windows Management Instrumentation services are running. I saw some posts regarding this issue, but the solution they have given was for older version. Everything works fine. Even Test Connection is successful. But I am not able to access management related activities even though I am using "root" user. How to resolve this issue? If it's administration access issue, how to check and change administration access privilege's?

I have a new install of MariaDB 10.2.27 on RHEL7. Our security scans have flagged the root account, so we have modified the name to orgdba, assigning all of the applicable privileges. I have not encountered any issues, except with mysqladmin. I know I have my password correct as I can connect via mysql using it. It is only erroring on mysqladmin. I ended up creating a root account specifically to execute mysqladmin commands. I cannot execute with that account either. It is not until I create the root account using the Unix Socket plugin that I can successfully execute mysqladmin commands. I cannot find anything in any documentation that expands on this topic. I have no idea if this is a bug in the version of MariaDB that I am using. Does anyone have any insight? Error connecting to mysqladmin: root@db01 $ mysqladmin -uorgdba -p status Enter password: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'orgdba'@'localhost' (using password: YES)' root@db01 $ mysqladmin -u orgdba -p status Enter password: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'orgdba'@'localhost' (using password: YES)' Same user/same password, successfully connects to mysql: root@db01 $ mysql -u orgdba -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 52347 Server version: 10.2.27-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> Any insight on this, or additional documentation on mysqladmin, would be appreciated.

My Issues is that when I am logged in as a normal user on unix server and then try to login as MySQL root. It throws below error but when I am logged in as root to unix server and then try to connect to MySQL command line it allows me. I don't understand why is this happening. and how to resolve it. I want connect to MySQL command line with any normal unix user not root always. -sh-4.2$ mysql -u root -pPASSWORD ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13) -sh-4.2$ su Password: [root@hostname]# mysql -u root -pPASSWORD Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 78203 Server version: 5.5.52-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

I am new to MySQL and I am in need of the script which is taking backup of multiple tables from the multiple databases in single file in MySQL?

I am trying to set a cron job for taking backup from my slave machine. So I need to stop the slave I issued a command mysqladmin --user=root --password=test_pass stop-slave But it is throwing error: > mysqladmin: connect to server at 'localhost' failed error: 'Access > denied for user 'root'@'localhost' (using password: YES)' Now I tried with the command mysqladmin --user=root --password stop-slave It prompts for password and I gave as test_pass and everything was fine. Why so happened? What is the alternative? NB: BTW my mysql version is mysql-5.0.95-5, it it make any sense.

I'm new to this whole database thing. Got a uni project which I was hoping y'all could help with. So I'm creating a webapp that is essentially an online art gallery store. The database will contain an inventory of items and possibly a customer list (this is what I'm not sure about). I want to allow for various levels of manipulation to the inventory list. I want user 1 let's call that "sales" to add new items, edit and delete. I also want sales to to the same for a customer list. I want user 2 "artists" to be able to add new, edit what they added and delete what they've added to the inventory list, along with their own info. I want user 3 "customer" to be able edit one field in the inventory list (quantity required), along with their own info. So would this come under rules and privileges?? I want an admin account to oversee the db and all connections. Would I need to create separate rules for all others? Do i add type 1 "sales" as admin with certain rights? Can I contain new users type 2&3 in a customer list and have that list manipulate the inventory list with rules? E.g. So if customer list is type 2, user is an "artist" and can add new stock, but if they are type 3, they are "customer" and can only edit quantity. Again I am a noob. I hope what I'm asking makes sense. Thanks for your help kind folks.

Just installed mysql on Ubuntu - sudo apt-get install mysql-server sudo apt-get install mysql-client As part of the installation process, I provided a password for the root user. I can now log in with mysql -u root -p But I'd love some other user besides root to own the mysql administration. I'm coming over from the world of Postgres and have never used MySQL before, so I'm wondering how I could change this user. Thanks! **EDIT** This is based off a recommendation from the Wordpress installation guide : > It is a safer practice to choose a so-called "mortal" account as your mysql admin, so that you are not entering the command "mysql" as the root user on your system. (Any time you can avoid doing work as root you decrease your chance of being exploited.)

I am using MySQL 5.7.17 Enterprise edition in RHEL with configuration innodb_file_per_table=ON . So now here i have 2 question : 1. Instead of ibd file per table ,i can see ibdata1,ib_logfile1,ib_logfile0 in data directory which is getting updated regularly .However these files are not big in size but collectively , these 3 files are consuming 15-20 GB space . So , instead of having ibd file per table "ON" , do these files still exist or if they exist what they contain which is making it updated regularly ? 2. I have devoted 393 GB to the data directory . and one database folder is occupying 293 GB alone. I have table_name.ibd files of size 84 GB-100 GB. Is there any way that i can check if any memory is getting waste ? and if it is getting waste how can i reclaim it ? I ran optimize tables for some small tables only ( it helped) because if i run this command for big tables , i need more space before using optimize command as space required is table_size*2 . I have checked table sizes as well . Size of table and size of respective table's ibd file , vary with 2 -3 GB difference . Can anyone help me , what can i do here ? Should i point MySQL data directory to new path ? if it is possible , how can i do this ? Size of file (in GB) in Descending order : 93 33 19 17 16 14 13 11 11 9.2

What I have : - a windows machine with one instance of MySQL 5.6 Server. - two database named test and test2. - Test database has a table called activity with columns id and class What I need : - replication of test.activity table to test2 database with a condition that Test.activity.class = 'B'. I know how to replicate with in different server. But not for same server within different database.I have checked this link , but it does not give enough information.

AppA stores/retrieves data from dbA.tableA AppB stores/retrieves data from dbB.tableA tableA definition is the same across these databases. To start with dbB.tableA was copied from dbA.tableA (assuming both had 5 rows). row6 was created by AppA (say primary key 6) row7 was created by AppB (say primary key 7) I would like row7 to be copied to dbA.tableA and row6 to dbB.tableA 1. Is this even possible to setup bi-directional replication, so that the AppA, AppB view the same data at any point in time. 2. If the primary key is an auto-increment, would it be possible to maintain integrity of data or is there a possibility that there would be collisions on the primary key.

I've downloaded mysql 5.25 on mac os x Lion (10.7.4). I just tried setting my password in the usual way, however I'm getting an access denied error which is odd: [user@Macintosh:~] #mysqladmin -uroot password 'secret' mysqladmin: connect to server at 'localhost' failed Is there something I'm missing about setting the root password for this version of mysql on this version of the mac? The database is definitely running according to what I see in the control panel / ps -ef.

i have a table A and i want to update the creation time of this table. I understand i cant simple update ( using update query) as it is not any table. Is there any way i can update it. for example creation time is '2015-07-03 12:03:33' to '2015-06-03 12:03:33'

I have an Ubuntu server with MySQL 5.5 installed. In /var/lib/mysql a .err file is kept on updating with these warnings only: [Warning] IP address '234.22.11.221' could not be resolved: Temporary failure in name resolution [Warning] IP address '234.22.11.221' could not be resolved: Temporary failure in name resolution Its size is currently around 2 GB. I googled this error and in most of the places it has been written that I need to write skip-host-cache in my.cnf file and then need to give a restart mysql. Currently this server is carrying lot of running web services, applications and giving a restart may effect all these applications. As far as I understand, these warnings are not native mysql errors. Also, can someone tell me whether these warnings effect my database in future if I will not take any action and is it possible that due to other web applications, these errors are generating? What is the other way around for this without restarting mysql? IS there any probability , that mysql will not come up after restart ???