Bug #32050 table logging gone wrong.
Submitted: 2 Nov 2007 10:47 Modified: 14 Jan 2008 17:52
Reporter: Domas Mituzas Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: CSV Severity:S1 (Critical)
Version:5.1-bk OS:Any
Assigned to: Sergey Vojtovich
Triage: D1 (Critical)

[2 Nov 2007 10:47] Domas Mituzas
Description:
Fresh MySQL builds crash with assertion on connections, if older general_log table definition is in place:

       Table: general_log
Create Table: CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext,
  `thread_id` int(11) DEFAULT NULL,
  `server_id` int(11) DEFAULT NULL,
  `command_type` varchar(64) DEFAULT NULL,
  `argument` mediumtext
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
1 row in set (0.00 sec)

ha_tina.cc:482: failed assertion `!(*field)->maybe_null()'

#15 0x00265d43 in ha_tina::encode_quote (this=0x5084a10, buf=0x5087c10 "") at ha_tina.cc:482
#16 0x00266417 in ha_tina::write_row (this=0x5084a10, buf=0x5087c10 "") at ha_tina.cc:869
#17 0x001ab32f in handler::ha_write_row (this=0x5084a10, buf=0x5087c10 "") at handler.cc:3658
#18 0x001586fb in Log_to_csv_event_handler::log_general (this=0x49ffff0, thd=0x503fc00, event_time=1193999856, user_host=0xb0469530 "root[root] @ localhost []", user_host_len=25, thread_id=1, command_type=0x458430 "Connect", command_type_len=7, sql_text=0xb0469770 "root@localhost on ", sql_text_len=18, client_cs=0x640800) at log.cc:414
#19 0x00157113 in LOGGER::general_log_write (this=0x67df20, thd=0x503fc00, command=COM_CONNECT, query=0xb0469770 "root@localhost on ", query_length=18) at log.cc:984
#20 0x0015722c in LOGGER::general_log_print (this=0x67df20, thd=0x503fc00, command=COM_CONNECT, format=0x4581a4 "%s@%s on %s", args=0xb0469bdc "0??\005\230XE") at log.cc:1003
#21 0x0015728e in general_log_print (thd=0x503fc00, command=COM_CONNECT, format=0x4581a4 "%s@%s on %s") at log.cc:3770
#22 0x000b4406 in check_user (thd=0x503fc00, command=COM_CONNECT, passwd=0x3df9026 "", passwd_len=0, db=0x0, check_count=true) at sql_connect.cc:447
#23 0x000b58a8 in login_connection (thd=0x503fc00) at sql_connect.cc:884
#24 0x000b5a55 in handle_one_connection (arg=0x503fc00) at sql_connect.cc:1098
#25 0x90024227 in _pthread_body ()

How to repeat:
More problems:

mysql> truncate general_log;
ERROR 1005 (HY000): Can't create table 'mysql.general_log' (errno: -1)

and CSV tables can't be created/recreated/altered in general:

mysql> create table csvtest (a int) engine=CSV;
ERROR 1005 (HY000): Can't create table 'test.csvtest' (errno: -1)

This makes upgrade procedures tricky. 

After dropping the table it is not recreated on restart, nor is it possible to create it with DDL commands. 

This results such errors:
071102 12:44:49 [ERROR] Failed to write to mysql.general_log
071102 12:44:49 [ERROR] Failed to write to mysql.general_log
071102 12:44:49 [ERROR] Failed to write to mysql.general_log

Oh well. 

Suggested fix:
...
[2 Nov 2007 10:48] Domas Mituzas
Verified against ChangeSet@1.2595, 2007-10-25 13:17:44+02:00, joerg@trift2. +1 -0
[2 Nov 2007 10:55] Domas Mituzas
Seems to be caused by:

ChangeSet@1.2573.6.1, 2007-10-18 14:48:04-07:00, antony@pcg5ppc.xiphis.org +7 -0
  Bug#31473
    "CSV does not work with NULL value in datetime fields"
    Attempting to insert a row with a NULL value for a DATETIME field
    results in a CSV file which the storage engine cannot read.
    Don't blindly assume that "0" is acceptable for all field types,
    Since CSV does not support NULL, we find out from the field the
    default non-null value.
    Do not permit the creation of a table with a nullable columns.
[2 Nov 2007 18:02] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/37012

ChangeSet@1.2599, 2007-11-02 11:02:07-07:00, antony@pcg5ppc.xiphis.org +1 -0
  bug#32050
    "table logging gone wrong"
    CSV requires that all columns be NOT NULL, so as part of
    mysql upgrade scripts, we must alter log table definitions so 
    that all columns which are previously nullable are now NOT NULL.
[2 Nov 2007 20:36] Brian Aker
Looks good... no test case, but I don't think one is possible.
[21 Nov 2007 13:02] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/38207

ChangeSet@1.2628, 2007-11-21 17:05:06+04:00, svoj@mysql.com +2 -0
  BUG#32050 - table logging gone wrong.
  
  INSERT/UPDATE against CSV table created by MySQL earlier than 5.1.23
  with NULL-able column results in server crash in debug builds.
  
  Starting with 5.1.23 CSV doesn't permit creation of NULL-able columns,
  but it is still possible to get such table from older MySQL versions.
  
  Relaxed DBUG_ASSERT() so that it accepts tables with NULL-able columns
  created by earlier versions of MySQL.
[21 Nov 2007 13:04] Sergey Vojtovich
Docs note: I believe it is worth to mention in the manual that starting with 5.1.23 it is not possible to create CSV table with NULL-able fields.
[27 Nov 2007 10:13] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/38604

ChangeSet@1.2654, 2007-11-27 14:01:11+04:00, svoj@mysql.com +2 -0
  BUG#32050 - table logging gone wrong.
  
  INSERT/UPDATE against CSV table created by MySQL earlier than 5.1.23
  with NULL-able column results in server crash in debug builds.
  
  Starting with 5.1.23 CSV doesn't permit creation of NULL-able columns,
  but it is still possible to get such table from older MySQL versions.
  
  Fixed by removing excessive DBUG_ASSERT().
[30 Nov 2007 10:58] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/38943

ChangeSet@1.2673, 2007-11-30 15:00:15+04:00, svoj@mysql.com +1 -0
  BUG#32050 - table logging gone wrong.
  
  Reverted log tables alteration.
[12 Dec 2007 23:00] Bugs System
Pushed into 6.0.5-alpha
[12 Dec 2007 23:02] Bugs System
Pushed into 5.1.23-rc
[14 Jan 2008 17:52] Jon Stephens
Documented as follows in the 5.1.23 and 6.0.5 changelogs, and in the 5.1/6.0 CSV chapter:

        It is no longer possible [as of MySQL {5.1.23|6.0.5}] to create
        CSV tables with NULL columns. However, for backwards
        compatibility, you can continue to use such tables that were
        created in previous MySQL releases.