MySQLTalk.org MYSQL discussions groups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  Archive engine for log tables?          MySQLTalk.org Forum Index -> MySQL Internals View previous topic :: View next topic   Author Message Giuseppe Maxia Guest Posted: Mon May 07, 2007 4:45 pm    Post subject: Archive engine for log tables? Hi, Can anyone tell me why I can't change the log tables to use the ARCHIVE storage engine? It makes sense to use it instead of CSV. Log tables must only support INSERT statements, not UPDATE or DELETE ones. Thus, Archive seems a sensible choice, since logs usually grow large. BTW, you can assign the Archive engine to a log table, using a workaround. use mysql; DROP TABLE IF EXISTS gl0, gl1, CREATE TABLE gl1 like general_log; ALTER TABLE gl1 ENGINE=ARCHIVE; RENAME TABLE general_log to gl0, gl1 to general_log; Thanks for any insight in this matter Best regards Giuseppe -- Giuseppe Maxia, QA Developer MySQL AB, www.mysql.com Back to top Geoffroy Cogniaux Guest Posted: Mon May 07, 2007 7:33 pm    Post subject: RE: Archive engine for log tables? Hi, Guiseppe Maxia wrote : Quote: Can anyone tell me why I can't change the log tables to use the ARCHIVE storage engine? The mysql_alter_table functions disables alter of log tables to unsupported engine. This means that the engine of a log table must have the HTON_SUPPORT_LOG_TABLE flag. Only Tina and MyIsam engine have it. But I don't know if Archive could. Perhaps Archive should be used only as "backup" table, not as "online". Regards, Geoffroy. -----Message d'origine----- De : Giuseppe Maxia [mailto:giuseppe (AT) mysql (DOT) com] Envoyé : lundi 7 mai 2007 13:45 À : internals (AT) lists (DOT) mysql.com Objet : Archive engine for log tables? Hi, Can anyone tell me why I can't change the log tables to use the ARCHIVE storage engine? It makes sense to use it instead of CSV. Log tables must only support INSERT statements, not UPDATE or DELETE ones. Thus, Archive seems a sensible choice, since logs usually grow large. BTW, you can assign the Archive engine to a log table, using a workaround. use mysql; DROP TABLE IF EXISTS gl0, gl1, CREATE TABLE gl1 like general_log; ALTER TABLE gl1 ENGINE=ARCHIVE; RENAME TABLE general_log to gl0, gl1 to general_log; Thanks for any insight in this matter Best regards Giuseppe -- Giuseppe Maxia, QA Developer MySQL AB, www.mysql.com -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/internals?unsub=geoffroy.cogniaux (AT) outreweb (DOT) fr Back to top Brian Aker Guest Posted: Mon May 07, 2007 8:41 pm    Post subject: Re: Archive engine for log tables? Hi! We are working on a new patch for Log tables right now that makes them available for all engines. Right now log tables require a special flag, and Archive does not have that flag. Cheers, -Brian On May 7, 2007, at 4:45 AM, Giuseppe Maxia wrote: Quote: Hi, Can anyone tell me why I can't change the log tables to use the ARCHIVE storage engine? It makes sense to use it instead of CSV. Log tables must only support INSERT statements, not UPDATE or DELETE ones. Thus, Archive seems a sensible choice, since logs usually grow large. BTW, you can assign the Archive engine to a log table, using a workaround. use mysql; DROP TABLE IF EXISTS gl0, gl1, CREATE TABLE gl1 like general_log; ALTER TABLE gl1 ENGINE=ARCHIVE; RENAME TABLE general_log to gl0, gl1 to general_log; Thanks for any insight in this matter Best regards Giuseppe -- Giuseppe Maxia, QA Developer MySQL AB, www.mysql.com -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/internals? unsub=brian (AT) tangent (DOT) org Back to top Sergei Golubchik Guest Posted: Mon May 07, 2007 10:42 pm    Post subject: Re: Archive engine for log tables? Hi! On May 07, Geoffroy Cogniaux wrote: Quote: Guiseppe Maxia wrote : Can anyone tell me why I can't change the log tables to use the ARCHIVE storage engine? The mysql_alter_table functions disables alter of log tables to unsupported engine. This means that the engine of a log table must have the HTON_SUPPORT_LOG_TABLE flag. Only Tina and MyIsam engine have it. But I don't know if Archive could. If only MyISAM and CSV have it, it may be because of concurrent inserts. They both support it, ARCHIVE does not, and current logging code relies on it. Regards / Mit vielen Grüssen, Sergei -- __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik <serg (AT) mysql (DOT) com> / /|_/ / // /\ \/ /_/ / /__ Senior Software Developer /_/ /_/\_, /___/\___\_\___/ MySQL GmbH, Radlkoferstr. 2, D-81373 München <___/ Geschäftsführer: Kaj Arnö - HRB München 162140 Back to top Brian Aker Guest Posted: Mon May 07, 2007 10:47 pm    Post subject: Re: Archive engine for log tables? Hi! On May 7, 2007, at 10:42 AM, Sergei Golubchik wrote: Quote: If only MyISAM and CSV have it, it may be because of concurrent inserts. They both support it, ARCHIVE does not, and current logging code relies on it. Just to point this out, Archive, like Innodb and others, has concurrent insert. None of these engines rely on MySQL locking behavior in the upper end to support this. The new log table patch removes the need for engines to declare this though, so its not an issue in the future. Cheers, -Brian Quote: Regards / Mit vielen Grüssen, Sergei -- __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik <serg (AT) mysql (DOT) com / /|_/ / // /\ \/ /_/ / /__ Senior Software Developer /_/ /_/\_, /___/\___\_\___/ MySQL GmbH, Radlkoferstr. 2, D-81373 München ___/ Geschäftsführer: Kaj Arnö - HRB München 162140 -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/internals? unsub=brian (AT) tangent (DOT) org Back to top Giuseppe Maxia Guest Posted: Tue May 08, 2007 10:46 pm    Post subject: Re: Archive engine for log tables? Hi Brian, Thanks for the news. I wrote an article about hacking table logs, in order to use the Archive or Federated storage engines for logging. Through federated tables, you can then use any other storage, including InnoDB (which crashes the server when used directly - see Bug#28274). http://www.oreillynet.com/databases/blog/2007/05/hacking_mysql_logs.html Cheers Giuseppe Brian Aker wrote: Quote: Hi! We are working on a new patch for Log tables right now that makes them available for all engines. Right now log tables require a special flag, and Archive does not have that flag. Cheers, -Brian On May 7, 2007, at 4:45 AM, Giuseppe Maxia wrote: Hi, Can anyone tell me why I can't change the log tables to use the ARCHIVE storage engine? It makes sense to use it instead of CSV. Log tables must only support INSERT statements, not UPDATE or DELETE ones. Thus, Archive seems a sensible choice, since logs usually grow large. BTW, you can assign the Archive engine to a log table, using a workaround. use mysql; DROP TABLE IF EXISTS gl0, gl1, CREATE TABLE gl1 like general_log; ALTER TABLE gl1 ENGINE=ARCHIVE; RENAME TABLE general_log to gl0, gl1 to general_log; Thanks for any insight in this matter Best regards Giuseppe --Giuseppe Maxia, QA Developer MySQL AB, www.mysql.com --MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/internals?unsub=brian (AT) tangent (DOT) org -- Giuseppe Maxia, QA Developer MySQL AB, www.mysql.com Back to top Antony T Curtis Guest Posted: Wed May 09, 2007 5:37 am    Post subject: Re: Archive engine for log tables? Hi Giuseppe, I think the patch that Brian is referring to is WL#3771 which would permit logging directly to any local storage engine and would also permit triggers. Regards, Antony, On Tue, 2007-05-08 at 19:46 +0200, Giuseppe Maxia wrote: Quote: Hi Brian, Thanks for the news. I wrote an article about hacking table logs, in order to use the Archive or Federated storage engines for logging. Through federated tables, you can then use any other storage, including InnoDB (which crashes the server when used directly - see Bug#28274). http://www.oreillynet.com/databases/blog/2007/05/hacking_mysql_logs.html Cheers Giuseppe Brian Aker wrote: Hi! We are working on a new patch for Log tables right now that makes them available for all engines. Right now log tables require a special flag, and Archive does not have that flag. Cheers, -Brian On May 7, 2007, at 4:45 AM, Giuseppe Maxia wrote: Hi, Can anyone tell me why I can't change the log tables to use the ARCHIVE storage engine? It makes sense to use it instead of CSV. Log tables must only support INSERT statements, not UPDATE or DELETE ones. Thus, Archive seems a sensible choice, since logs usually grow large. BTW, you can assign the Archive engine to a log table, using a workaround. use mysql; DROP TABLE IF EXISTS gl0, gl1, CREATE TABLE gl1 like general_log; ALTER TABLE gl1 ENGINE=ARCHIVE; RENAME TABLE general_log to gl0, gl1 to general_log; Thanks for any insight in this matter Best regards Giuseppe --Giuseppe Maxia, QA Developer MySQL AB, www.mysql.com --MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/internals?unsub=brian (AT) tangent (DOT) org -- Giuseppe Maxia, QA Developer MySQL AB, www.mysql.com -- Antony T Curtis, Senior Software Developer MySQL Inc, www.mysql.com SIP: 4468 (AT) sip (DOT) mysql.com Back to top Calvin Sun Guest Posted: Wed May 09, 2007 10:11 am    Post subject: Re: Archive engine for log tables? Here is the correct link to the article Giuseppe mentioned: http://www.oreillynet.com/databases/blog/2007/05/hacking_mysql_table_logs.html Thanks, Calvin Sun Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         MySQLTalk.org Forum Index -> MySQL Internals All times are GMT Page 1 of 1   Jump to: Select a forum MySQL----------------MySQL and JavaMySQL AnnouncementsMySQL++MySQL and .NETMySQL ODBCMySQL and PerlDatabase BenchmarksMySQL BugsMySQL Cluster DiscussionMySQL CommunityMySQL DocumentationEventum DevelopmentEventum General DiscussionMySQL General DiscussionMySQL GUI ToolsMySQL InternalsMySQL CommitsMaxDBMySQL PackagersMySQL Replication DiscussionBrisbane MySQL Users GroupMySQL on Win32  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.