1

What I was trying to do is avoid listing my TEXT columns in the INSERTs and they would have a default '' (empty string).

I managed to set (see below) a default '' value for my TEXT column, but it generated a warning and I cannot see what changed on my table structure.

Please check the code below to reproduce the issue.

DROP TABLE IF EXISTS `text_test`;

CREATE TABLE `text_test` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `text` TEXT NOT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;
-- created OK

SHOW CREATE TABLE `text_test`;
-- CREATE TABLE `text_test` (
--   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
--   `text` text COLLATE utf8_unicode_ci NOT NULL,
--   PRIMARY KEY (`id`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
-- no surprises here...

INSERT INTO `text_test` () VALUES ();
-- warning: text has no default value

SELECT * FROM `text_test`;
-- id=1, text=''

ALTER TABLE `text_test` 
    CHANGE COLUMN `text` `text` TEXT NOT NULL DEFAULT '' COLLATE 'utf8_unicode_ci' AFTER `id`;
-- warning: blob/text cannot have default...

SHOW CREATE TABLE `text_test`;
-- CREATE TABLE `text_test` (
--   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
--   `text` text COLLATE utf8_unicode_ci NOT NULL,
--   PRIMARY KEY (`id`)
-- ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
-- no surprises, seems like the table did not change at all...

INSERT INTO `text_test` () VALUES ();
-- no warning generated, but why if the table structure is still the same??

SELECT * FROM `text_test`;
-- id=1, text=''
-- id=2, text=''

My server info:

SHOW VARIABLES LIKE "%version%";
-- "Variable_name"           "Value"
-- "innodb_version"          "5.1.73-14.6"
-- "protocol_version"        "10"
-- "version"                 "5.1.73-rel14.11-log"
-- "version_comment"         "(Percona Server (GPL), 14.11)"
-- "version_compile_machine" "x86_64"
-- "version_compile_os"      "debian-linux-gnu"
3
  • u cant give a default value to TEXT column Commented Jan 17, 2015 at 17:15
  • possible duplicate of Why can't a text column have a default value in MySQL? Commented Jan 17, 2015 at 17:21
  • @Umair and @Strawberry thanks for the comments ... it is a similar question, but not exactly the same. I am trying to understand why my first INSERT generates a warning while my second INSERT does not, if they are the same and in theory the table is the same... any help would be appreciated! Thanks Commented Jan 17, 2015 at 18:58

1 Answer 1

1

MySQL manual: "BLOB and TEXT columns cannot have DEFAULT values".

The warning is intentional, for cases of automated table creation and code implying an expectation of a default value.

Ignore and carry on :)

Sign up to request clarification or add additional context in comments.

6 Comments

Hi... yes I understood that. After changing the column to DEFAULT '' it issues a warning that it cannot have DEFAULT so far it is normal (it cannot have default values). However, after we do so, the table stops generating warnings for the TEXT field if you do not provide a value... why the first INSERT above generates a WARNING but the second does not? That's what I am curious about ...
Reading through the developer thread on MySQL about this, it would likely be the case that the warning is simply applied as a standard. I would also suspect that if the warning is provided during table creation, it wouldn't be required to be created during insert because the warning is deemed as a catch-all for implied expectation of a default value. In other words, the code has already been warned during the create. Now, how it stores that, and on what "standards" premise it is applied is just beyond me.
Hi Lance. This behaviour is new to me, I had no idea that some warnings raised with the table structure would could possibly remove warnings from data manipulation. IMHO this is erratic because, once you dump your tables and load the dump files, several warnings could raise. For those who rollback and abort a script when warnings are raised (am I the only one who does that?) it could be an issue. Of course, now that I know about it I can be prepared.
BTW, can you point me this developer thread about MySQL? I would love to learn more about it... thanks a lot!
Sure, I started here, then just jumped around... sorry, retracing my exact steps from 7 hours ago is boring :) bugs.mysql.com/bug.php?id=19498
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.