Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)?

share|improve this question
    
And Thanks in advance –  FÔx Gênki Jun 11 '13 at 21:30
    
Why would you want to do that in the first place? That sounds like a really weird thing to do. –  a_horse_with_no_name 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. –  FÔx Gênki Jun 11 '13 at 22:32
    
And why would that require to change the storage engine? –  a_horse_with_no_name 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 –  FÔx Gênki Jun 11 '13 at 22:39
show 3 more comments

3 Answers

up vote 1 down vote accepted

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.

share|improve this answer
    
So bad :/. thanks @federico –  FÔx Gênki Jun 11 '13 at 21:49
add comment

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.

share|improve this answer
add comment

All the tables in the information_schema are temporary tables.

As an example, just run SHOW CREATE TABLE information_schema.processlist\G.

mysql> show create table information_schema.processlist\G
*************************** 1. row ***************************
       Table: PROCESSLIST
Create Table: CREATE TEMPORARY TABLE `PROCESSLIST` (
  `ID` bigint(4) NOT NULL DEFAULT '0',
  `USER` varchar(16) NOT NULL DEFAULT '',
  `HOST` varchar(64) NOT NULL DEFAULT '',
  `DB` varchar(64) DEFAULT NULL,
  `COMMAND` varchar(16) NOT NULL DEFAULT '',
  `TIME` int(7) NOT NULL DEFAULT '0',
  `STATE` varchar(64) DEFAULT NULL,
  `INFO` longtext
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.06 sec)

mysql>

Temporary tables NEVER HAVE .frm FILES IN EXISTENCE. It is impossible to change the transient nature of any table in the information_schema database.

The information_schema is not a disk-based database under datadir. In other words, if you go to the OS and run:

cd /var/lib/mysql (or wherever datadir is defined)
ls -l

you will not see a subfolder named information_schema. The reason information_schema.processlist has to be MyISAM is due to the INFO column, which is TEXT. MEMORY tables cannot cut it in that instance. So, do not look to convert it into any other storage engine.

ALTERNATIVE

If you want to capture who logs into mysql, when they logged on, and what query was executed, you could activate the general log, convert it to MyISAM, index it by the timestamp, and setup log rotations.

See my earlier posts about using mysql.general_log:

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.