Bug #5355 INSERT ... SELECT with tables that have longblob columns

• Submitted: 1 Sep 2004 22:15
• Modified: 2 Sep 2004 17:55
• Reporter: David Ishee
• Status: Not a Bug
• Category: MySQL Server: MyISAM storage engine
• Severity: S2 (Serious)
• Version: 3.23.58
• OS: Other (IRIX 6.5)
• CPU Architecture: Any

[1 Sep 2004 22:15] David Ishee

Description:
INSERT ... SELECT syntax doesn't appear to work with tables that have longblob columns when selecting from a temporary table and inserting into a normal table. The same syntax works with other tables that have data, but don't have logblob columns.

How to repeat:
Create a table like this:

CREATE TABLE `cp_binary_data` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `tempID` varchar(60) NOT NULL default '',
  `cp_parent_ID` int(10) unsigned NOT NULL default '0',
  `name` varchar(200) NOT NULL default '',
  `type` varchar(100) NOT NULL default '',
  `data` longblob,
  PRIMARY KEY  (`ID`),
  KEY `cpParentKey` (`cp_parent_ID`),
  KEY `tempIDkey` (`tempID`)
) TYPE=MyISAM

Fill in some data for the table. 

Create a temporary table in a temporary database with data from the permanent table:

CREATE TEMPORARY TABLE test_temp.cp_binary_data_1094066804518 AS SELECT * FROM cp_binary_data WHERE cp_parent_ID='1'

Run an update command on the temp table:

UPDATE test_temp.cp_binary_data_1094066804518 SET cp_parent_ID='6'  WHERE cp_parent_ID='1'

Insert the updated record back into the permanent table:

INSERT INTO cp_binary_data SELECT * FROM test_temp.cp_binary_data_1094066804518

0 records are affected on the last INSERT ... SELECT statement. Why?

Suggested fix:
Unknown.

[1 Sep 2004 22:34] David Ishee

Calling out the specific columns is a workaround for this problem:

e.g.

INSERT INTO cp_binary_data (cp_parent_ID, name, type, data) SELECT cp_parent_ID,name,type,data FROM test_temp.cp_binary_data_1094066804518

[1 Sep 2004 22:36] David Ishee

Note: The above workaround is for inserting the data back into permanent table from the temp table. The data gets into the temp table OK.

[2 Sep 2004 15:28] MySQL Verification Team

Hi,

I tested your example and also got "0 rows affected" for INSERT query, but it's expected behavior, because of duplicates.
Original table 'cp_binary_data' already has rows with the same ID (PRIMARY KEY), that is why all rows from the temporary table are ignored.

[2 Sep 2004 16:40] David Ishee

I researched my code further and found other examples where I cleared out the ID column in the temp table before reinserting into the permanent table. 

In this case I forgot to do that. 

I agree that this was programmer error, not a bug. (just don't tell anyone about it OK?)