CODEWITHSUNDEEP
mysqlview
Marco Veenendaal
Marco Veenendaal

Reputation: 186

Can I see if a view is being used in MySQL

I want to migrate some tables in a large database into another structure. For backwards compatibility I want to expose the old structure trough a view. Now I want to see if this view is being used so I can DROP it if it's not used anymore. Due to the database size and architecture query log can not be put on.

Is there any way to do this? I.e. insert a records into an accesslog table on select on that view, or any other way.

Upvotes: 2

Views: 251

Answers (2)

Solarflare
Solarflare

Reputation: 11096

You can use a function as a column in your view to write a log entry in a table.

create table LogView (
   Viewname varchar(50) not null, 
   Username varchar(50) not null, 
   dt datetime, 
   primary key (Viewname, Username)
);

delimiter $$

create function fctViewLog(Viewname varchar(50)) returns int deterministic
begin
  insert into LogView (Viewname, username, dt) 
  values (Viewname, Current_User(), now()) 
  on duplicate key update dt = now();
  return 0;
end $$

Delimiter ;

create view ViewOldTable as (
select ..., fctViewLog('ViewOldTable') as JustForLogging from YourNewTable);

This will add a column to your view with the value 0. If that new column is a concern, you can use an existing column from your view and just return its value. If your view has any integer value, e.g. id, you can use:

create function fctViewLog(Viewname varchar(50), in_value int) returns int deterministic
begin
  insert into LogView (Viewname, username, dt) 
  values (Viewname, Current_User(), now()) 
  on duplicate key update dt = now();
  return in_value;
end $$

Delimiter ;

create view ViewOldTable as (
select ..., fctViewLog('ViewOldTable', id) as id 
from YourNewTable);

then the number and names of your columns will remain the same.

It will add a measureable overhead to your views, since it will update the LogTable for every row in your view, but this will only happen when the old structure is used and not the new one, which, I guess, is the main concern; and it might have the sideeffect to urge the user to update the client. You can optimize it a little by e.g. not updating the dt-row when the entry exists (e.g. if the time doesn't matter anyway), but it will still slow it down.

Upvotes: 1

Shadow
Shadow

Reputation: 34231

The free version of mysql only offers the general query log for logging any statements sent to the server. Calling a view does not result in any trappable events, so you cannot populate your own query log. I see 3 options you may follow:

  1. Use stored procedures to return the data instead of views. In the stored procedure you can do the logging an you can also return a resultset. Obviously, you cannot easily join or filter the resultset.

  2. Get an entrerprise version of mysql and install the audit log plugin to get a more preformance friendly logging option. This way you can ckeck if your views are still used.

  3. To be honest, I would not necessarily track the use of those views, esspecially if your intention is to switch them off in the end. Give the users a timeline till when the views are available, remind them often as the deadline draws closer and disable the views at the specified deadline. If any users have a well established solution based on the views, they will be reluctant to change it, unless you force them to.

Upvotes: 1

Related Questions