Bug #33717 INSERT...(default) fails for enum. Crashes CSV tables, loads spaces for MyISAM
Submitted: 7 Jan 2008 2:52 Modified: 18 Jun 2010 1:31
Reporter: Patrick Crews Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.0+ OS:Any
Assigned to: Sergey Glukhov CPU Architecture:Any
Tags: disabled
Triage: Triaged: D3 (Medium) / R3 (Medium) / E3 (Medium)

[7 Jan 2008 2:52] Patrick Crews
Description:
INSERT INTO <table> (default) fails for enum data types in CSV tables.  Will result in a crashed table.

INSERT INTO ... () works properly -- populates the field with the first value of the enum declaration.

Also tested against MyISAM, and will not crash the table, but will not populate the field with default

How to repeat:
Use the following SQL:

CREATE TABLE t1 (e enum('foo','bar') NOT NULL) ENGINE = CSV;
INSERT INTO t1 VALUES();
SELECT * from t1;
INSERT INTO t1 VALUES(default);
SELECT * from t1;

# This will not crash the table, but INSERT(default) will result in blank data
CREATE TABLE t2(e2 enum('foo','bar') NOT NULL);
INSERT INTO t2 VALUES();
SELECT * from t2;
INSET INTO t2 values(default);
SELECT * from t2;

Suggested fix:
Ensure that INSERT INTO <table> VALUES(default) works the same as INSERT INTO <table> VALUES() as specified in the documentation.
[7 Jan 2008 9:28] Godofredo Miguel Solorzano
Thank you for the bug report.

mysql> CREATE TABLE t1 (e enum('foo','bar') NOT NULL) ENGINE = CSV;
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO t1 VALUES();
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * from t1;
+-----+
| e   |
+-----+
| foo |
+-----+
1 row in set (0.00 sec)

mysql> INSERT INTO t1 VALUES(default);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT * from t1;
ERROR 1194 (HY000): Table 't1' is marked as crashed and should be repaired
mysql> CREATE TABLE t2(e2 enum('foo','bar') NOT NULL);
Query OK, 0 rows affected (0.08 sec)
[13 Nov 2008 10:18] Gleb Shchepa
5.0 is affected too with exception: CSV doesn't crash, but it inserts wrong default result like MyISAM.
[12 Dec 2008 16:23] Shane Bester
I opened bug #41441 as a sister to this (crash on windows debug server)
[26 Dec 2008 16:47] 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/62353

2746 Sergey Glukhov	2008-12-26
      Bug#33717 INSERT...(default) fails for enum. Crashes CSV tables, loads spaces for MyISAM
      Table corruption happens during table reading in ha_tina::find_current_row() func.
      Field::store() method returns error(true) if stored value is 0, but 0 is legal value.
      The fix is to return false if the value is 0.
      Additional fix:
      INSERT...(default) and INSERT...() have the same behaviour now.
[6 Feb 2009 10:04] 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/65451

2741 Sergey Glukhov	2009-02-06
      Bug#33717 INSERT...(default) fails for enum. Crashes CSV tables, loads spaces for MyISAM
      Table corruption happens during table reading in ha_tina::find_current_row() func.
      Field::store() method returns error(true) if stored value is 0, but 0 is legal value.
      The fix is to return false if the value is 0.
      Additional fix:
        INSERT...(default) and INSERT...() have the same behaviour now.
[27 Feb 2009 10:43] 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/67828

2828 Sergey Glukhov	2009-02-27
      Bug#33717 INSERT...(default) fails for enum. Crashes CSV tables, loads spaces for MyISAM
      Table corruption happens during table reading in ha_tina::find_current_row() func.
      Field::store() method returns error(true) if stored value is 0.
      The fix:
      added special case for enum type which correctly processes 0 value.
      Additional fix:
      INSERT...(default) and INSERT...() have the same behaviour now.
     @ mysql-test/r/csv.result
        test result
     @ mysql-test/r/default.result
        result fix
     @ mysql-test/r/type_ranges.result
        result fix
     @ mysql-test/t/csv.test
        result fix
     @ sql/item.cc
        set default value
     @ sql/sql_insert.cc
        removed check for ENUM field. The reason is that
        ENUM field behavoir should be the same as other
        fileds have.
     @ storage/csv/ha_tina.cc
        Table corruption happens during table reading in ha_tina::find_current_row() func.
        Field::store() method returns error(true) if stored value is 0.
        The fix:
        added special case for enum type which correctly processes 0 value.
[31 Mar 2009 9:51] 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/70869

2840 Sergey Glukhov	2009-03-31
      Bug#33717 INSERT...(default) fails for enum. Crashes CSV tables, loads spaces for MyISAM
      Table corruption happens during table reading in ha_tina::find_current_row() func.
      Field::store() method returns error(true) if stored value is 0.
      The fix:
      added special case for enum type which correctly processes 0 value.
      Additional fix:
      INSERT...(default) and INSERT...() have the same behaviour now.
     @ mysql-test/r/csv.result
        test result
     @ mysql-test/r/default.result
        result fix
     @ mysql-test/t/csv.test
        test case
     @ sql/item.cc
        Changes:
        --do not print warning for 'enum' type if there is no default value
        --set default value
     @ storage/csv/ha_tina.cc
        Table corruption happens during table reading in ha_tina::find_current_row() func.
        Field::store() method returns error(true) if stored value is 0.
        The fix:
        added special case for enum type which correctly processes 0 value.
[19 May 2009 9:17] 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/74467

2840 Sergey Glukhov	2009-05-19
      Bug#33717 INSERT...(default) fails for enum. Crashes CSV tables, loads spaces for MyISAM(for 5.0 & 5.1)
      Table corruption happens during table reading in ha_tina::find_current_row() func.
      Field::store() method returns error(true) if stored value is 0.
      The fix:
      added special case for enum type which correctly processes 0 value.
      Additional fix:
      INSERT...(default) and INSERT...() have the same behaviour now for enum type.
     @ mysql-test/r/csv.result
        test result
     @ mysql-test/r/default.result
        result fix
     @ mysql-test/t/csv.test
        test case
     @ sql/item.cc
        Changes:
        --do not print warning for 'enum' type if there is no default value
        --set default value
     @ storage/csv/ha_tina.cc
        Table corruption happens during table reading in ha_tina::find_current_row() func.
        Field::store() method returns error(true) if stored value is 0.
        The fix:
        added special case for enum type which correctly processes 0 value.
[17 Feb 2010 12:14] 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/100612

3344 Sergey Glukhov	2010-02-17
      Bug#33717 INSERT...(default) fails for enum. Crashes CSV tables, loads spaces for MyISAM
      Table corruption happens during table reading in ha_tina::find_current_row() func.
      Field::store() method returns error(true) if stored value is 0.
      The fix:
      added special case for enum type which correctly processes 0 value.
      Additional fix:
      INSERT...(default) and INSERT...() have the same behaviour now for enum type.
     @ mysql-test/r/csv.result
        test result
     @ mysql-test/r/default.result
        result fix
     @ mysql-test/t/csv.test
        test case
     @ sql/item.cc
        Changes:
        do not print warning for 'enum' type if there is no default value.
        set default value.
     @ storage/csv/ha_tina.cc
        Table corruption happens during table reading in ha_tina::find_current_row() func.
        Field::store() method returns error(true) if stored value is 0.
        The fix:
        added special case for enum type which correctly processes 0 value.
[1 Mar 2010 8:45] Bugs System
Pushed into 5.1.45 (revid:joro@sun.com-20100301083827-xnimmrjg6bh33o1o) (version source revid:sergey.glukhov@sun.com-20100217121342-1ytesk72as92xleb) (merge vers: 5.1.45) (pib:16)
[2 Mar 2010 14:35] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100302142746-u1gxdf5yk2bjrq3e) (version source revid:alik@sun.com-20100225090938-2j5ybqoau570mytu) (merge vers: 6.0.14-alpha) (pib:16)
[2 Mar 2010 14:40] Bugs System
Pushed into 5.5.3-m2 (revid:alik@sun.com-20100302072233-t3uqgjzdukt1pyhe) (version source revid:alexey.kopytov@sun.com-20100221213311-xf5nyv391dsw9v6j) (merge vers: 5.5.2-m2) (pib:16)
[2 Mar 2010 14:45] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100302072432-k8xvfkgcggkwgi94) (version source revid:alik@sun.com-20100224135227-rcqs9pe9b2in80pf) (pib:16)
[14 Mar 2010 2:30] Paul Dubois
Noted in 5.1.45, 5.5.3, 6.0.14 changelogs.

INSERT INTO ... VALUES(DEFAULT) failed to insert the correct value 
for ENUM columns. For MyISAM tables, an empty value was inserted. For
CSV tables, the table became corrupt.
[9 Jun 2010 14:12] 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/110637

3103 Martin Skold	2010-06-09 [merge]
      Merged in 5.1.45
      added:
        mysql-test/include/not_binlog_format_row.inc
        mysql-test/r/bug39022.result
        mysql-test/r/no_binlog.result
        mysql-test/r/partition_debug_sync.result
        mysql-test/std_data/bug48449.frm
        mysql-test/suite/rpl/r/rpl_slow_query_log.result
        mysql-test/suite/rpl/t/rpl_slow_query_log-slave.opt
        mysql-test/suite/rpl/t/rpl_slow_query_log.test
        mysql-test/t/bug39022.test
        mysql-test/t/no_binlog.test
        mysql-test/t/partition_debug_sync.test
      renamed:
        mysql-test/r/variables+c.result => mysql-test/r/variables_community.result
        mysql-test/t/variables+c.test => mysql-test/t/variables_community.test
      modified:
        client/mysql.cc
        client/mysql_upgrade.c
        client/mysqladmin.cc
        client/mysqlbinlog.cc
        client/mysqlcheck.c
        client/mysqldump.c
        client/mysqlimport.c
        client/mysqlshow.c
        client/mysqlslap.c
        client/mysqltest.cc
        cmd-line-utils/readline/rlmbutil.h
        cmd-line-utils/readline/text.c
        configure.in
        extra/yassl/include/yassl_error.hpp
        extra/yassl/src/ssl.cpp
        extra/yassl/src/yassl_error.cpp
        mysql-test/include/mtr_warnings.sql
        mysql-test/lib/My/ConfigFactory.pm
        mysql-test/lib/My/SafeProcess.pm
        mysql-test/lib/My/SafeProcess/safe_process_win.cc
        mysql-test/lib/mtr_cases.pm
        mysql-test/lib/mtr_gprof.pl
        mysql-test/lib/mtr_misc.pl
        mysql-test/lib/mtr_report.pm
        mysql-test/lib/mtr_stress.pl
        mysql-test/lib/v1/mtr_stress.pl
        mysql-test/lib/v1/mysql-test-run.pl
        mysql-test/mysql-test-run.pl
        mysql-test/r/archive.result
        mysql-test/r/backup.result
        mysql-test/r/bigint.result
        mysql-test/r/csv.result
        mysql-test/r/default.result
        mysql-test/r/delete.result
        mysql-test/r/fulltext.result
        mysql-test/r/func_gconcat.result
        mysql-test/r/func_time.result
        mysql-test/r/group_by.result
        mysql-test/r/group_min_max.result
        mysql-test/r/having.result
        mysql-test/r/innodb-autoinc.result
        mysql-test/r/innodb_mysql.result
        mysql-test/r/join.result
        mysql-test/r/log_state.result
        mysql-test/r/multi_update.result
        mysql-test/r/myisam.result
        mysql-test/r/mysqltest.result
        mysql-test/r/show_check.result
        mysql-test/r/sp-bugs.result
        mysql-test/r/sp-error.result
        mysql-test/r/sp.result
        mysql-test/r/sp_notembedded.result
        mysql-test/r/sp_trans.result
        mysql-test/r/subselect.result
        mysql-test/r/type_bit.result
        mysql-test/r/type_blob.result
        mysql-test/r/type_timestamp.result
        mysql-test/r/view.result
        mysql-test/r/view_grant.result
        mysql-test/r/warnings.result
        mysql-test/suite/rpl/r/rpl_sp.result
        mysql-test/suite/rpl/t/rpl_loaddata_symlink.test
        mysql-test/suite/sys_vars/r/log_basic.result
        mysql-test/suite/sys_vars/r/log_bin_trust_routine_creators_basic.result
        mysql-test/suite/sys_vars/r/slow_query_log_func.result
        mysql-test/suite/sys_vars/t/slow_query_log_func.test
        mysql-test/t/archive.test
        mysql-test/t/bigint.test
        mysql-test/t/csv.test
        mysql-test/t/delete.test
        mysql-test/t/fulltext.test
        mysql-test/t/func_gconcat.test
        mysql-test/t/group_by.test
        mysql-test/t/group_min_max.test
        mysql-test/t/having.test
        mysql-test/t/innodb-autoinc.test
        mysql-test/t/innodb_mysql.test
        mysql-test/t/join.test
        mysql-test/t/multi_update.test
        mysql-test/t/myisam.test
        mysql-test/t/mysqltest.test
        mysql-test/t/sp-bugs.test
        mysql-test/t/sp_notembedded.test
        mysql-test/t/subselect.test
        mysql-test/t/type_bit.test
        mysql-test/t/view.test
        mysql-test/t/view_grant.test
        mysys/default.c
        scripts/mysqld_multi.sh
        server-tools/instance-manager/options.cc
        sql/field.cc
        sql/ha_partition.cc
        sql/item.cc
        sql/item.h
        sql/item_cmpfunc.h
        sql/item_row.cc
        sql/item_sum.cc
        sql/item_timefunc.cc
        sql/log.cc
        sql/log_event.cc
        sql/log_event_old.cc
        sql/mysql_priv.h
        sql/mysqld.cc
        sql/opt_range.cc
        sql/opt_sum.cc
        sql/share/errmsg.txt
        sql/sp.cc
        sql/sp_cache.cc
        sql/sp_head.cc
        sql/sql_base.cc
        sql/sql_class.cc
        sql/sql_class.h
        sql/sql_lex.cc
        sql/sql_lex.h
        sql/sql_parse.cc
        sql/sql_repl.cc
        sql/sql_select.cc
        sql/sql_select.h
        sql/sql_table.cc
        sql/sql_trigger.cc
        sql/sql_update.cc
        sql/sql_view.cc
        sql/table.cc
        sql/table.h
        storage/archive/ha_archive.cc
        storage/csv/ha_tina.cc
        storage/innobase/buf/buf0buf.c
        storage/innobase/buf/buf0rea.c
        storage/innobase/handler/ha_innodb.cc
        storage/innobase/include/buf0rea.h
        storage/innobase/lock/lock0lock.c
        storage/innobase/os/os0file.c
        storage/myisam/ft_boolean_search.c
        storage/myisam/mi_dynrec.c
        storage/myisam/mi_locking.c
        support-files/mysql.spec.sh
[17 Jun 2010 11:58] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:38] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609140708-52rvuyq4q500sxkq) (merge vers: 5.1.45-ndb-6.2.19) (pib:16)
[17 Jun 2010 13:24] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)