Bug #14171 | Wrong (NOT NULL) default for BINARY(n) | ||
---|---|---|---|
Submitted: | 20 Oct 2005 10:35 | Modified: | 24 Jan 2007 21:02 |
Reporter: | Matthias Leich | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.0 | OS: | |
Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
[20 Oct 2005 10:35]
Matthias Leich
[20 Oct 2005 10:37]
Matthias Leich
test script
Attachment: ml034.test (application/test, text), 526 bytes.
[20 Oct 2005 10:58]
Matthias Leich
It must be mentioned, that this bug might harm customers, who perform 1. Have a table created in MySQL 4.1 with CREATE TABLE .... <column> BINARY(n) NOT NULL <no explicit default !!> MySQL 4.1 Show CREATE TABLE presents ... BINARY(n) NOT NULL DEFAULT '' (two single quotes without space between) 2. Upgrade the data in place (without mysqldump) to MySQL 5.0 MySQL 5.0 Show CREATE TABLE presents ... BINARY(n) NOT NULL DEFAULT ' ' (two single quotes with space between) 3. Downgrade everything to MySQL 4.1 (via mysqldump) MySQL 4.1 Show CREATE TABLE presents ... BINARY(n) NOT NULL DEFAULT ' ' (two single quotes with space between) That means you will not get a table with the same properties. I assume that - the probability of a table/application history like above is very rare - customers will have to adjust their tables/applications when being faced with the new properties of BINARY in MySQL 5.0 and they will remember such things when downgrading So I guess such unexpected Upgrade/Downgrade issues are very rare.
[22 Dec 2006 17:55]
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/17335 ChangeSet@1.2349, 2006-12-22 20:54:32+03:00, evgen@moonbone.local +3 -0 Bug#14171: Wrong internal default value for a BINARY field. A BINARY field is represented by the Field_string class. The space character is used as the filler for unused characters in such a field. But a BINARY field should use \x00 instead. Field_string:reset() now detects whether the current field is a BINARY one and if so uses the \x00 character as a default value filler.
[12 Jan 2007 0:19]
Peter Gulutzan
I tested with MySQL 5.1.15 pulled today. Last changeset was: ChangeSet@1.2384, 2007-01-11 23:05:13+01:00. I see: mysql> CREATE TABLE t1 (f1 BIGINT, f2 BINARY(2) NOT NULL); Query OK, 0 rows affected (0.06 sec) mysql> insert into t1 (f1) values (1); Query OK, 1 row affected, 1 warning (0.08 sec) mysql> show warnings; +---------+------+-----------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------+ | Warning | 1364 | Field 'f2' doesn't have a default value | +---------+------+-----------------------------------------+ 1 row in set (0.02 sec) mysql> select f1,hex(f2) from t1; +------+---------+ | f1 | hex(f2) | +------+---------+ | 1 | 2020 | +------+---------+ 1 row in set (0.39 sec) I'm setting status back to "patch queued".
[15 Jan 2007 8:09]
Sergei Glukhov
fixed in 5.0.34, 5.1.15-beta
[24 Jan 2007 21:02]
Paul DuBois
Noted in 5.0.34, 5.1.15 changelogs. Inserting a row into a table without specifying a value for a BINARY(N) NOT NULL column caused the column to be set to spaces, not zeroes.