MySQL Bugs: #11841: LONGBLOB fields do not accept large files

Bug #11841	LONGBLOB fields do not accept large files
Submitted:	10 Jul 2005 2:44	Modified:	11 Jul 2005 17:39
Reporter:	Yuri Koba	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.7-beta	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
I am having trouble inserting large files (say 1Mb) into LONGBLOB fields.
The warning back is:

  Can't set character set back to utf8 while saving resultset changes

and MySQL Query Browser quits.

How to repeat:
Add LONGBLOB field to any table and try to insert a large file (say 1Mb) for that field using MySQL Query Browser.

Could you please provide table sample definition and a query (how you insert large data into field) ?

The backup of the test database is as follows:

-----------------------------------------------------------------------------------

-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version	5.0.7-beta-nt

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

--
-- Create schema test
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ test;
USE test;

--
-- Table structure for table `test`.`t1`
--

DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `BlobField` longblob NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 15360 kB; InnoDB free: 14336 kB';

--
-- Dumping data for table `test`.`t1`
--

/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` (`ID`,`BlobField`) VALUES 
 (1,'');
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

-----------------------------------------------------------------------------------

I am using MySQL Query Browser to run a query:

SELECT * FROM `test`.`t1`

Next steps:

1) I click "Edit" to be able to insert the field value.
2) The BlobField of the t1 table now shows the envelope icon which I click and get the prompt to select a file.
3) After selecting a big file I click "Open" and MySQL Query Browser starts to insert the specified file.
4) Before saving I can view the download by clicking the field viewer icon.
The size of the download looks OK.
5) By selecting "Apply Changes" I get a warning in a DOS window:

     Can't set character set back to utf8 while saving resultset changes

The DOS window with the title of the type "...\MySQLQueryBrowser.exe".
MySQL Query Browser shows "MySQL server has gone away", and
"Error while applying actions". 

6) After I close the DOS window MySQL Query Browser quits.

Sorry. I was not able to reproduce the bug. If I tried to send big file I got an error message "Got a packet bigger than 'max_allowed_packet' bytes". It's ok. I did't see any dos windows. But I would like to notice I tested on 5.0.8.
Could you please say me version of MySQL Query Browser ?

MySQL Query Browser is 1.1.13
Currently it is the latest available on www.mysql.com

Since you are saying that "Got a packet bigger than 'max_allowed_packet' bytes". message is not a bug, then it is an inconvenience. From your message I understand that you also were not able to insert the big file into the LONGBLOG field. If the developers intend to keep this inconvenience then I have no more comments other than the fact that MySQL documentation says that LONGBLOB fields should be able to store data much more than 1Mb.

Is there a way to change 'max_allowed_packet' value without going to the source code and re-compiling?

max_allowed_packet it is a configuration variable for the client, then just
edit your my.ini option file and  try again.

 --max_allowed_packet=# 
                      Max packet length to send to, or receive from server

As you advised I was able to solve my problem by setting max_allowed_packet configuration variable in my.ini file.

Thanks.