| Bug #10838 | CREATE TABLE produces strange DEFAULT value | ||
|---|---|---|---|
| Submitted: | 24 May 2005 19:27 | Modified: | 25 May 2005 17:27 |
| Reporter: | Hakan Küçükyılmaz | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.0.6-beta | OS: | Linux (SuSE 9.3) |
| Assigned to: | Mikael Ronström | CPU Architecture: | Any |
[24 May 2005 19:28]
Dean Ellis
Verified against current 5.0.7 bk pull.
[25 May 2005 7:47]
Georg Richter
If you create the same table without default values and use engine MyISAM the following
behaviour occured:
mysql> insert into t2 (b) values ('1');
Query OK, 1 row affected, 10 warnings (0.00 sec)
mysql> show warnings;
+---------+------+-----------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------+
| Warning | 1364 | Field 'a' doesn't have a default value |
| Warning | 1364 | Field 'q' doesn't have a default value |
| Warning | 1364 | Field 'r' doesn't have a default value |
| Warning | 1364 | Field 't' doesn't have a default value |
| Warning | 1364 | Field 'u' doesn't have a default value |
| Warning | 1364 | Field 'v' doesn't have a default value |
| Warning | 1364 | Field 'w' doesn't have a default value |
| Warning | 1364 | Field 'y' doesn't have a default value |
| Warning | 1364 | Field 'z' doesn't have a default value |
| Warning | 1364 | Field 'a1' doesn't have a default value |
+---------+------+-----------------------------------------+
10 rows in set (0.07 sec)
mysql> select * from t2;
ERROR 1194 (HY000): Table 't2' is marked as crashed and should be repaired
[25 May 2005 15:54]
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/internals/25270
[25 May 2005 17:01]
Mikael Ronström
Pushed fix to 5.0.6 build clone
Description: CREATE TABLE produces strange DEFAULT value with alot of \0\0\0's How to repeat: SQL_MODE is: sql_mode="ANSI_QUOTES,NO_BACKSLASH_ESCAPES,TRADITIONAL,IGNORE_SPACE" [21:25] root@test>DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( Query OK, 0 rows affected (0.01 sec) 5.0.7-beta-debug on 10.17.69.84 [21:25] root@test> 5.0.7-beta-debug on 10.17.69.84 [21:25] root@test>CREATE TABLE t1 ( -> a varchar(30) binary NOT NULL DEFAULT ' ', -> b varchar(1) binary NOT NULL DEFAULT ' ', -> c varchar(4) binary NOT NULL DEFAULT '0000', -> d tinyblob NULL, -> e tinyblob NULL, -> f tinyblob NULL, -> g tinyblob NULL, -> h tinyblob NULL, -> i tinyblob NULL, -> j tinyblob NULL, -> k tinyblob NULL, -> l tinyblob NULL, -> m tinyblob NULL, -> n tinyblob NULL, -> o tinyblob NULL, -> p tinyblob NULL, -> q varchar(30) binary NOT NULL DEFAULT ' ', -> r varchar(30) binary NOT NULL DEFAULT ' ', -> s tinyblob NULL, -> t varchar(4) binary NOT NULL DEFAULT ' ', -> u varchar(1) binary NOT NULL DEFAULT ' ', -> v varchar(30) binary NOT NULL DEFAULT ' ', -> w varchar(30) binary NOT NULL DEFAULT ' ', -> x tinyblob NULL, -> y varchar(5) binary NOT NULL DEFAULT ' ', -> z varchar(20) binary NOT NULL DEFAULT ' ', -> a1 varchar(30) binary NOT NULL DEFAULT ' ', -> b1 tinyblob NULL -> ) ENGINE=InnoDB DEFAULT CHARACTER SET = latin1 COLLATE latin1_bin; Query OK, 0 rows affected (0.02 sec) 5.0.7-beta-debug on 10.17.69.84 [21:25] root@test>SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE "t1" ( "a" varchar(30) collate latin1_bin NOT NULL default ' \0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0 0000\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0 \0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0 \0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0 \0\0\0 \0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0', "b" varchar(1) collate latin1_bin NOT NULL default ' ', "c" varchar(4) collate latin1_bin NOT NULL default '0000', "d" tinyblob, "e" tinyblob, "f" tinyblob, "g" tinyblob, "h" tinyblob, "i" tinyblob, "j" tinyblob, "k" tinyblob, "l" tinyblob, "m" tinyblob, "n" tinyblob, "o" tinyblob, "p" tinyblob, "q" varchar(30) collate latin1_bin NOT NULL default ' ', "r" varchar(30) collate latin1_bin NOT NULL default ' ', "s" tinyblob, "t" varchar(4) collate latin1_bin NOT NULL default ' ', "u" varchar(1) collate latin1_bin NOT NULL default ' ', "v" varchar(30) collate latin1_bin NOT NULL default ' ', "w" varchar(30) collate latin1_bin NOT NULL default ' ', "x" tinyblob, "y" varchar(5) collate latin1_bin NOT NULL default ' ', "z" varchar(20) collate latin1_bin NOT NULL default ' ', "a1" varchar(30) collate latin1_bin NOT NULL default ' ', "b1" tinyblob ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin 1 row in set (0.03 sec)