Description:
With sql_mode set to STRICT_ALL_TABLES, using INSERT...SELECT to insert a LONGTEXT value into a TEXT column silently truncates the value.
How to repeat:
DROP TABLE IF EXISTS long_texts;
DROP TABLE IF EXISTS texts;
DROP TABLE IF EXISTS tiny_texts;
SHOW VARIABLES WHERE Variable_name = 'sql_mode';
CREATE TABLE long_texts (
id INT(11) NOT NULL AUTO_INCREMENT,
long_text_field LONGTEXT,
PRIMARY KEY (id)
);
CREATE TABLE texts (
id INT(11) NOT NULL AUTO_INCREMENT,
text_field TEXT,
PRIMARY KEY (id)
);
CREATE TABLE tiny_texts (
id INT(11) NOT NULL AUTO_INCREMENT,
tiny_text_field TINYTEXT,
PRIMARY KEY (id)
);
INSERT INTO long_texts (long_text_field)
VALUES
(REPLACE(SPACE(10), ' ', '.')),
(REPLACE(SPACE(100000), ' ', '.'));
SELECT LENGTH(long_text_field), SUBSTR(long_text_field, -10) FROM long_texts;
INSERT INTO texts (text_field)
SELECT (long_text_field) FROM long_texts;
SELECT LENGTH(text_field), SUBSTR(text_field, -10) FROM texts;
INSERT INTO tiny_texts (tiny_text_field)
SELECT (long_text_field) FROM long_texts;
SELECT LENGTH(tiny_text_field), SUBSTR(tiny_text_field, -10) FROM tiny_texts;
Description: With sql_mode set to STRICT_ALL_TABLES, using INSERT...SELECT to insert a LONGTEXT value into a TEXT column silently truncates the value. How to repeat: DROP TABLE IF EXISTS long_texts; DROP TABLE IF EXISTS texts; DROP TABLE IF EXISTS tiny_texts; SHOW VARIABLES WHERE Variable_name = 'sql_mode'; CREATE TABLE long_texts ( id INT(11) NOT NULL AUTO_INCREMENT, long_text_field LONGTEXT, PRIMARY KEY (id) ); CREATE TABLE texts ( id INT(11) NOT NULL AUTO_INCREMENT, text_field TEXT, PRIMARY KEY (id) ); CREATE TABLE tiny_texts ( id INT(11) NOT NULL AUTO_INCREMENT, tiny_text_field TINYTEXT, PRIMARY KEY (id) ); INSERT INTO long_texts (long_text_field) VALUES (REPLACE(SPACE(10), ' ', '.')), (REPLACE(SPACE(100000), ' ', '.')); SELECT LENGTH(long_text_field), SUBSTR(long_text_field, -10) FROM long_texts; INSERT INTO texts (text_field) SELECT (long_text_field) FROM long_texts; SELECT LENGTH(text_field), SUBSTR(text_field, -10) FROM texts; INSERT INTO tiny_texts (tiny_text_field) SELECT (long_text_field) FROM long_texts; SELECT LENGTH(tiny_text_field), SUBSTR(tiny_text_field, -10) FROM tiny_texts;