Re: Database or table level logging?
Right, sorry, this is 5.0.x (5.0.18 at the moment). Thanks for the pointer on 5.1.6, though that looks like it has the ability to log to a table instead of a file, not log access to a specific table. David On Jul 18, 2006, at 9:23 PM, Chris wrote: David Felio wrote: Is it possible to have general query type logging on just a specific database or table? I don't want all queries to all databases, I just need a log of accesses to a specific table. I figure I can use stored procedures or a separate mysql instance as a workaround if I need to, but would prefer to be able to use the built-in logging if possible to avoid having to make changes to existing applications. What version of mysql? http://dev.mysql.com/doc/refman/5.1/en/log-tables.html That will only work for 5.1.6+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database or table level logging?
Through judicious use of SQL you could easily get the log entries for a specific table ... I haven't got 5.1.6 to check but if the log table included database and table name columns, you're set. select query_text from mysql.general_log where database_name = 'mydb' and table_name = 'mytable' order by query_date (note the column names are fictitious) Understand you're on 5.0 not 5.1 but this could prove an elegant solution to your requirement, so an upgrade might be warranted depending on how badly you need it. Of course 5.1 isn't production yet but I've used pre-production releases and generally have found them to be quite stable (YMMV of course!). Dan On 7/19/06, David Felio [EMAIL PROTECTED] wrote: Right, sorry, this is 5.0.x (5.0.18 at the moment). Thanks for the pointer on 5.1.6, though that looks like it has the ability to log to a table instead of a file, not log access to a specific table. David On Jul 18, 2006, at 9:23 PM, Chris wrote: David Felio wrote: Is it possible to have general query type logging on just a specific database or table? I don't want all queries to all databases, I just need a log of accesses to a specific table. I figure I can use stored procedures or a separate mysql instance as a workaround if I need to, but would prefer to be able to use the built-in logging if possible to avoid having to make changes to existing applications. What version of mysql? http://dev.mysql.com/doc/refman/5.1/en/log-tables.html That will only work for 5.1.6+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database or table level logging?
Thanks. I could also get the same result with the existing query log using grep, but retrieving a little data from a large batch isn't the issue. The issue is the overhead of logging ever single query to the server when I only need a handful of tables. I know we can log everything and retrieve from there, but logging everything is not an option. I am looking for a way to log just a specific database or a few specific tables. David On Jul 19, 2006, at 9:06 AM, Dan Buettner wrote: Through judicious use of SQL you could easily get the log entries for a specific table ... I haven't got 5.1.6 to check but if the log table included database and table name columns, you're set. select query_text from mysql.general_log where database_name = 'mydb' and table_name = 'mytable' order by query_date (note the column names are fictitious) Understand you're on 5.0 not 5.1 but this could prove an elegant solution to your requirement, so an upgrade might be warranted depending on how badly you need it. Of course 5.1 isn't production yet but I've used pre-production releases and generally have found them to be quite stable (YMMV of course!). Dan On 7/19/06, David Felio [EMAIL PROTECTED] wrote: Right, sorry, this is 5.0.x (5.0.18 at the moment). Thanks for the pointer on 5.1.6, though that looks like it has the ability to log to a table instead of a file, not log access to a specific table. David On Jul 18, 2006, at 9:23 PM, Chris wrote: David Felio wrote: Is it possible to have general query type logging on just a specific database or table? I don't want all queries to all databases, I just need a log of accesses to a specific table. I figure I can use stored procedures or a separate mysql instance as a workaround if I need to, but would prefer to be able to use the built-in logging if possible to avoid having to make changes to existing applications. What version of mysql? http://dev.mysql.com/doc/refman/5.1/en/log-tables.html That will only work for 5.1.6+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database or table level logging?
You might possibly be able to set up a trigger on the log tables to discard entries for all but those databases/tables you are interested in ... Just a thought. Dan On 7/19/06, David Felio [EMAIL PROTECTED] wrote: Thanks. I could also get the same result with the existing query log using grep, but retrieving a little data from a large batch isn't the issue. The issue is the overhead of logging ever single query to the server when I only need a handful of tables. I know we can log everything and retrieve from there, but logging everything is not an option. I am looking for a way to log just a specific database or a few specific tables. David On Jul 19, 2006, at 9:06 AM, Dan Buettner wrote: Through judicious use of SQL you could easily get the log entries for a specific table ... I haven't got 5.1.6 to check but if the log table included database and table name columns, you're set. select query_text from mysql.general_log where database_name = 'mydb' and table_name = 'mytable' order by query_date (note the column names are fictitious) Understand you're on 5.0 not 5.1 but this could prove an elegant solution to your requirement, so an upgrade might be warranted depending on how badly you need it. Of course 5.1 isn't production yet but I've used pre-production releases and generally have found them to be quite stable (YMMV of course!). Dan On 7/19/06, David Felio [EMAIL PROTECTED] wrote: Right, sorry, this is 5.0.x (5.0.18 at the moment). Thanks for the pointer on 5.1.6, though that looks like it has the ability to log to a table instead of a file, not log access to a specific table. David On Jul 18, 2006, at 9:23 PM, Chris wrote: David Felio wrote: Is it possible to have general query type logging on just a specific database or table? I don't want all queries to all databases, I just need a log of accesses to a specific table. I figure I can use stored procedures or a separate mysql instance as a workaround if I need to, but would prefer to be able to use the built-in logging if possible to avoid having to make changes to existing applications. What version of mysql? http://dev.mysql.com/doc/refman/5.1/en/log-tables.html That will only work for 5.1.6+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database or table level logging?
David Felio wrote: Is it possible to have general query type logging on just a specific database or table? I don't want all queries to all databases, I just need a log of accesses to a specific table. I figure I can use stored procedures or a separate mysql instance as a workaround if I need to, but would prefer to be able to use the built-in logging if possible to avoid having to make changes to existing applications. What version of mysql? http://dev.mysql.com/doc/refman/5.1/en/log-tables.html That will only work for 5.1.6+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]