0

I was checking mysql by mistake i updated the (max_questions =1) . Now i searched over google and stack overflow for this solution every one has a answer like ,

UPDATE mysql.user SET max_questions=0 WHERE User='root';
SET CHARACTER SET 'utf8'; SELECT @@character_set_client;
GRANT USAGE ON *.* TO 'root'@'localhost' WITH MAX_QUERIES_PER_HOUR 0;


but no one is giving the answer that where to execute these query because phpmyadmin is showing this error
Error

SQL query: Edit Edit

SET CHARACTER SET 'utf8';

MySQL said: Documentation

1226 - User 'root' has exceeded the 'max_questions' resource (current value: 1)

Community
  • 1
  • 1
asif
  • 13
  • 1
  • 3
  • 7
  • This is an sql statement. You can execute it with whatever client you have available. If phpmyadmin is not working for you, then use another one. Easiest probably is the cli mysql client. The `mysql-workbench` might also be an option. Or some scripting language like php. But easiest certainly is the cli client mysql brings. – arkascha Aug 01 '15 at 09:40
  • sir but its not my answer i just know how to run phpmyadmin , but now its not working and showing error of Error SQL query: Edit Edit SET CHARACTER SET 'utf8'; MySQL said: Documentation 1226 - User 'root' has exceeded the 'max_questions' resource (current value: 1) tell me how can i update max_question to '0'; – asif Aug 01 '15 at 09:49
  • Sure, I understood that you are using phpmyadmin. But aparently you locked yourself out by specifically denying any more requests to your root user (whyever). So you cannot use that tool to fix the issue. You have to look for an alternative way. And as said: the cli client probably is the easiest alternative. It is like if you broke the door handle of a car. Without you cannot open the door any more. You have to check the other doors for an alternative to get inside :-) – arkascha Aug 01 '15 at 09:50
  • exactly , but what is cli client i dont know would you please guide – asif Aug 01 '15 at 09:52
  • I refer to the normal mysql client that is provided along with the server part of that database management system. You have to install it, if it is not present on your system. It is started on the command line by typing `mysql` along with a few options telling the client what server to connect to and what login credentials to use. The clients manual page documents the options you have to use. Use `man mysql` to look at those manual pages. You need something like `mysql -h -u root -p` – arkascha Aug 01 '15 at 09:53
  • sir , i am using xamp server how to do like what you say .. – asif Aug 01 '15 at 09:54
  • So, does your xampp installation contain the mysql client? – arkascha Aug 01 '15 at 09:55
  • Ah, sorry, if you are using xampp the you are probably using an MS-Windows system? Sorry, I have next to no experience with that. So I cannot guide you with the specific details of that setup. – arkascha Aug 01 '15 at 09:58
  • sir i am using now mysql prompt screen . and executing these query but there is an error – asif Aug 01 '15 at 10:03
  • And what error is that? Sorry, but I cannot _guess_ it. You have to post it. Oh, and by the way: you probably only need the first line of those three sql statements you posted in your question. Just the update. Then if that works without an error, afterwards you should restart the mysql server once (so probably xampp in your case). – arkascha Aug 01 '15 at 10:03
  • when i am using this query UPDATE mysql.user SET max_questions=0 WHERE User='root';
    the error shows
    check the manual that corresponds to your MySQL server version for the right syntax to use near '=0 where user='root' at line 1     – asif Aug 01 '15 at 10:11
  • That error looks _very_ strange. The syntax is correct. Please _triple_ check that you have no typo in there. Especially do not copy&paste the commend, but type it. Maybe that `0` you have is not really a zero there... – arkascha Aug 01 '15 at 10:15
  • Sir , the error is showing again and again – asif Aug 01 '15 at 10:25
  • OK, I just checked here locally: the statement `UPDATE mysql.user SET max_questions=0 WHERE User='root';` is absolutely correct and executes without an error. There must be something else wrong on your side. – arkascha Aug 01 '15 at 10:29
  • @RyanVincent as said I have no idea about MS-Windows, but if the OP gets the error he posted, then surely he _does_ have access to some mysql client and executes a query, I would say... – arkascha Aug 01 '15 at 10:29
  • maybe useful? Basically, just wait a while? [How to change "#1226 - User 'root' has exceeded the 'max_questions' resource" error](http://forums.mysql.com/read.php?10,32194). If you are really 'stuck' then it should be possible to edit the `mysql.users` table directly: [Restoring Accidently Deleted Root User in MySQL](http://www.servaxnet.com/blog/2010/07/10/restoring-accidently-deleted-root-user-in-mysql/). Update the column to be changed. Looks 'interesting'. I suggest you take a backup first? – Ryan Vincent Aug 01 '15 at 12:01
  • i have uninstalled xamp server and installed new , before i have copeid whole folder now how can i recover my databases . i opened xamp/mysql/data there has my data base files i have copied my files to there but its only showing database names not there tables and data – asif Aug 01 '15 at 13:24
  • Using `innoDB` tables? [14.5.2 Moving or Copying InnoDB Tables to Another Machine](https://dev.mysql.com/doc/refman/5.6/en/innodb-migration.html). I suggest you look in the old MySQL directories as mentioned in the article. – Ryan Vincent Aug 01 '15 at 14:01
  • innoDB ,, Where to check it – asif Aug 01 '15 at 14:03
  • how can i recover mysql database from .frm files – asif Aug 01 '15 at 14:05
  • maybe useful? [questions/10934745 - Restore the mysql database from .frm files](http://stackoverflow.com/questions/10934745/restore-the-mysql-database-from-frm-files) – Ryan Vincent Aug 01 '15 at 14:09

1 Answers1

1

In mysql you use the mysql db, e.g.

use mysql;
update user set max_questions=0 where user = 'root';

where 0 is unlimited. Could be smaller number but in development I use 0.
James Armstrong
  • 71
  • 4