Bug #28274 Server crashes if general log table is changed to innodb
Submitted: 7 May 2007 9:30 Modified: 14 Apr 2012 9:47
Reporter: Giuseppe Maxia Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Logging Severity:S2 (Serious)
Version:5.1.18 OS:Any
Assigned to: CPU Architecture:Any
Tags: crash, engines, innodb, table logs
Triage: Triaged: D1 (Critical)

[7 May 2007 9:30] Giuseppe Maxia
Description:
Direct changing of general_log engine to anything other than CSV or MyISAM is not allowed. However, renaming an existing INNODB table  (or any other engine) to general_log is allowed.
When the log is trying to use a InnoDB general_log, the server crashes.

Notice that there seem to be no problems when using Archive, Blackhole, and Federated tables for the same purpose. 

Changing the general_log to PBXT does not crash the server, but the table is not readable while logging is enabled.

How to repeat:
-- source include/have_innodb.inc
SET GLOBAL general_log=OFF;
use mysql;
--disable-warnings
drop table if exists gl0, gl1;
--enable-warnings
create table gl1 like general_log;
alter table gl1 engine=innodb;
rename table general_log to gl0, gl1 to general_log;
SET GLOBAL general_log=ON;
show tables;
# here crashes

Suggested fix:
Add a check to the RENAME command, to prevent the general_log to be assigned to InnoDB tables.
Don't prevent usage of other engines.
[9 May 2007 8:34] Paul McCullagh
Hi Giuseppe,

I have done the test on PBXT, and traced the API calls (see below).

The calls have the affect that it looks like one insert statement, which inserts many rows. For example:

INSERT t1 VALUES (1, 2), (3, 4), (5, 6), ...

This is a single statement so it is handled as a single transaction by the engine.

So this has the effect that the logged values are only visible after "SET GLOBAL general_log=off;".

Of course, this is not the desired affect. Each insert in the log should be immediately visible by other threads.

So this would require a change in the way MySQL handles the log writing (unfortunately, I believe it cannot be solved by simply calling commit() in between inserts due to other API complications).

Otherwise PBXT behaves correctly, however, I get a crash in PBXT when the server is shutdown while logging is on, which I believe is a bug in PBXT. So I will take a closer look at this.

Best regards,

Paul

-----------------------------------

SET GLOBAL general_log=on;
System lock_2 ha_pbxt::open ./mysql/general_log
System lock_2 virtual int ha_pbxt::info(uint)
System lock_2 ha_pbxt::extra ./mysql/general_log  operation=5
System lock_2 ha_pbxt::store_lock ./mysql/general_log 7->5
System lock_2 (null)
System lock_2 ha_pbxt::EXTERNAL_LOCK ./mysql/general_log lock_type=3
System lock_2 xt_xn_begin
System lock_2 ha_pbxt::extra ./mysql/general_log  operation=36
System lock_2 ha_pbxt::rnd_init ./mysql/general_log

show tables;
System lock_2 ha_pbxt::write_row ./mysql/general_log
System lock_2 ha_pbxt::write_row trans_register_ha all=FALSE

/* At this point a select from the general_log does not return the row containing the "show tables" done above. */

SET GLOBAL general_log=off;
System lock_2 ha_pbxt::write_row ./mysql/general_log
System lock_2 virtual int ha_pbxt::rnd_end()
System lock_2 ha_pbxt::EXTERNAL_LOCK ./mysql/general_log lock_type=UNLOCK
System lock_2 xt_xn_commit
System lock_2 ha_pbxt::close ./mysql/general_log

/* At this point the "show tables;" can be seen in the log table. */
[9 May 2007 21:46] Giuseppe Maxia
Notice that Falcon displays the same behavior previously noted for PBXT. The only difference that I have seen between these two engines is that if you shut down the server while logging is enabled, PBXT prevents the server from shutting down, while Falcon does not complain.
[27 Oct 2008 21:57] Konstantin Osipov
Can't repeat in 6.0.
[14 Apr 2012 9:47] Jon Olav Hauglid
The crash is no longer repeatable with 5.6.
The remaining issue reported here is a duplicate of Bug#62908.