Solved

What does "flush tables;" do exactly?

Posted on 2016-11-08
7
164 Views
Last Modified: 2016-11-08
Long story short, I was trying to trouble an error:

User XXXXXXXXXX already has more than 'max_user_connections' active connections...


This user has  many different web sites all using one MySQL database, I had already restarted all their sites that I could find across varying web servers, and that didn't fix the error, so I then went on the MySQL server and did a "flush hosts" , that didn't help.

Then I read something along about doing:
flush tables;


So I ran that, it's still running, so far other sites using this db server aren't affected but now I'm second guessing myself and wondering if it was OK to run flush tables in the first place.

As I said, the process is still running so I'm hoping I didn't break anything.

Probably I shoudl have just restarted the MySQL service, but I didn't want to affect other users.

This is a Windows MySQL server and I could not find a command or script to kill a specific user's processes in one swoop (I did find a linux method but it involved grep and awk , so would not have worked on Windows)


Thanks
0
Comment
Question by:Vas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 28

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41878734
Flush tables - Closes all open tables, forces all tables in use to be closed, and flushes the query cache.

It also removes all query results from the query cache, like the RESET QUERY CACHE statement.

Read more at - http://dev.mysql.com/doc/refman/5.7/en/flush.html
http://www.thecreativedev.com/mysql-flush-commands/
0
 
LVL 1

Author Comment

by:Vas
ID: 41878735
thanks, so is there any risk to running flush tables?

It's still running so I don't know if I've caused any issues yet.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41878742
It will clear the query cache content. When your caches are empty, MySQL can better utilize available memory. It can also used to unlock tables.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 1

Author Comment

by:Vas
ID: 41878755
For this part you said " Closes all open tables, forces all tables in use to be closed"  that sounds like a bad thing though right? (forcing tables to close)

I had run the command exactly like this with no other parameters or switches:

flush tables;


(it's still running)
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41879757
Hi,
Sorry for the delay in reply. Is it done?Did you face any issues?

Pls let me know.

Regards,
Pawan
0
 
LVL 1

Author Comment

by:Vas
ID: 41879799
Hi, it didn't hurt anything, for an unrelated reason we had to reboot the server so it never completed fully but all is good now (the reboot must have cleared connections anyway)

Thanks so much for your help earlier
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41879802
Great that everything is fine. !!
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Windows Active Directory Upgrade from 2008 to 2012 21 52
Windows Modify Permissions 19 62
Server 2008 Not services listed 23 44
error doing substr 3 33
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This tutorial will give a an overview on how to deploy remote agents in Backup Exec 2012 to new servers. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as connecting to a remote Back…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question