Mysql – #1036 – Table ‘pma__recent’ is read only in phpMyAdmin / xampp / Windows

You need to restart mysql from the Linux command line like this:

service mysql restart --skip-grant-tables --skip-networking

If you are using Windows, add the two options in the mysqld section of my.ini:

[mysqld]
skip-grant-tables
skip-networking

and then run

net stop mysql (wait 10 seconds)
net start mysql

This will allow immedidate login without authentication and without any remoye connection sneaking in on you.

INSERT INTO mysql.user SET
Host='localhost',
User='mynewuser',
Password=PASSWORD('whateverpassword'),
Select_priv='Y',
Insert_priv='Y',
Update_priv='Y',
Delete_priv='Y',
Create_priv='Y',
Drop_priv='Y',
Reload_priv='Y',
Shutdown_priv='Y',
Process_priv='Y',
File_priv='Y',
Grant_priv='Y',
References_priv='Y',
Index_priv='Y',
Alter_priv='Y',
Show_db_priv='Y',
Super_priv='Y',
Create_tmp_table_priv='Y',
Lock_tables_priv='Y',
Execute_priv='Y',
Repl_slave_priv='Y',
Repl_client_priv='Y';

then

service mysql restart

At least you will have a localhost user named mynewuser with enough privileges to login.

You should then run this query:

select user,host,password from mysql.user;

and see if any new users were added by a hacker. If you see any remote users that should not be there, you can remove them with:

DELETE FROM mysql.user WHERE ...;
FLUSH PRIVILEGES;

If you are using Windows, please remember to remove skip-grants-tables and skip-networking from my.ini, then stop and start mysql service.

CAVEAT

Make a backup of /var/lib/mysql/mysql before you do anything.

UPDATE 2011-09-26 10:41 EDT

If the INSERT INTO mysql.user query does not work, it simply means the query is not compatible with the version of mysql I took this example from.

In order to know the correct INSERT query to user, please run this query:

desc mysql.user;

You will see all columns with the type ENUM('Y','N') for each privilege. Adjust the INSERT query to match all columns and then run that INSERT.

To get the exact columns to set to Y for all privileges, user this query:

SELECT CONCAT('SET ... ',GROUP_CONCAT(CONCAT(column_name,'=''Y'''),';'))
FROM information_schema.columns
WHERE table_schema='mysql'
AND table_name='user'
AND column_type='enum(''N'',''Y'')'
ORDER BY ORDINAL_POSITION\G

This will present to columns to set regardless of the version of MySQL you are using.

I have managed to recover my data. (so happy)

After hours of reading forums and solutions I found a way to fix it. Here's what I did:

• In xampp I backed up my old copy (after crash) of folder DbScrapes (dbname) from C:\xampp\mysql\data with files db.opt, scraped_user.frm, scraped_user.ibd
• In C:\xampp\mysql\data I backed up an old copy (after crash) of files ib_logfile0, ib_logfile1, ibdata1.
• Uninstalled my xampp
• Restarted my computer to be sure ports that xampp is using will be close
• Checked if there is a MySQL service in services.msc
• On my computer there was a MySQL service.
• I deleted it using cmd command sc delete "service_name"
• Restart again to make sure everything is fresh.
• Installed a new copy of xampp
• Test if apache and MySQL is ok, if it's ok click stop
• Copy the files that I have backed up to the location where I get them so it's C:\xampp\mysql\data
• Run xampp's apache and MySQL again.

Now I'm running xampp and I have my data.

Note: Don't delete ibdata1 - it will delete all your database tables.

The crashing issue I think was a bug in xampp.