0

I use MySQL. My database's name is "db", and I have a table called "Test".

I want recover the last update, so I used this query:

SELECT update_time
FROM information_schema.tables
WHERE table_schema='db'
AND table_name='Test';

But the result is NULL, so I converted my Table from InnoDB to MYISAM by using this query:

mysql> ALTER TABLE db.Test ENGINE=MYISAM;

and it worked.

But the problem is that when I want to convert the table PROCESSLIST from InnoDB to MYISAM I get this message:

mysql> use information_schema
Database changed
mysql> ALTER TABLE PROCESSLIST ENGINE=MYISAM;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

So, is there another solution to know what is the last update for this table (PROCESSLIST)?

Mat
  • 10,079
  • 4
  • 42
  • 40
dardar.moh
  • 275
  • 1
  • 3
  • 12
  • Why would you want to do that in the first place? That sounds like a really weird thing to do. –  Jun 11 '13 at 22:09
  • @a_horse_with_no_name : I'm working on an application where I want to list the user logged on MySQL server, and when a user change his name my application must notify me and all this things should be in real time and I can't use trigger on system tables. – dardar.moh Jun 11 '13 at 22:32
  • And why would that require to change the storage engine? –  Jun 11 '13 at 22:33
  • Because when I use this query : SELECT update_time FROM information_schema.tables WHERE table_schema='information_schema' AND table_name='PROCESSLIST';

    I get Update_time = NULL

     – dardar.moh Jun 11 '13 at 22:39
  • Try to run your select in auto-commit mode, or end the transaction using a commit before running the select. Could be you are affected by MySQL's default isolation level REPEATABLE READ –  Jun 11 '13 at 22:41
  • No, it's not about REPEATABLE READ because if I used this query : SELECT update_time FROM information_schema.tables WHERE table_schema='Mydb' AND table_name='Test'; i get NULL but when I used this query : mysql> ALTER TABLE db.Test ENGINE=MYISAM; and I do again the previous query i get 11-06-212.that's mean it's work. – dardar.moh Jun 11 '13 at 22:46
  • When you alter the engine for the test table you are committing (i.e. ending) your current transaction (all DDL implicitely commits your transaction). What happens if you run commit instead of ALTER TABLE and then query information_schema? –  Jun 12 '13 at 06:31

2 Answers2

1

There is no way, because they are virtual tables. This is also the reason why you can't do anything with them, except for SELECT.

Federico Razzoli
  • 696
  • 5
  • 15
1

You can specify log_warnings=2 to get "Warnings related to‘Aborted connection information’ and replication will be logged to MySQL error log.

If you are using MySQL enterprise then "MySQL Audit" feature will be useful as it includes login and logoff attempts, attempts to access a database or a table, changes to database schema and much more for each and every MySQL user who accesses server.

Peter Venderberghe
  • 857
  • 5
  • 10