Bug #78851 longblob

• Submitted: 15 Oct 2015 22:19
• Modified: 16 Oct 2015 12:31
• Reporter: charanjeet c
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.5
• OS: CentOS
• CPU Architecture: Any
• Tags: longblob utf warning

[15 Oct 2015 22:19] charanjeet c

Description:
Hi, I am inserting image from django web application to mysql. Same django code is working in local system and production server. Code is working fine in local system but not in production server. Setting of system variables is same for local and production mysql server. Error is because of mysql tries to convert binary to utf8 format. which cause warning and rollback condition. In Binary log file insert statement is same for both the cases

Error:
Warning: Invalid utf8 character string: 'FFD8FF'

table:
 document_repo | CREATE TABLE `document_repo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ref_id` int(11) NOT NULL,
  `document` longblob,
  `created_time` datetime NOT NULL,
  `comments` longtext,
  PRIMARY KEY (`id`),
  KEY `document_repo_501d92a0` (`ref_id`),
  CONSTRAINT `ref_id_refs_id_b94c4c81` FOREIGN KEY (`ref_id`) REFERENCES `document_details` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8 |

django shell:

>>  x = DocumentRepo(ref_id=1, document="""\xff\xd8\xff\""")
>>  x.save()

Note: value in document is image binary string in above case its incomplete. String above is example how string will look like in real its png file string format.     

How to repeat:
I am tried to simulate the error in local server but not able to simulate. I am using 64bit machine. django 1.6.5 and python 2.7.5. mysql 5.6.24 in local and 5.6.27 in production server

[16 Oct 2015 12:31] MySQL Verification Team

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.

[2 Jun 2016 12:55] Walter Doekes

A simpler test case:

mysql> show create table pstore_nonce\G
*************************** 1. row ***************************
       Table: pstore_nonce
Create Table: CREATE TABLE `pstore_nonce` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created` datetime NOT NULL,
  `user_id` int(11) NOT NULL,
  `value` longblob NOT NULL,
  `encrypted` longblob NOT NULL,
  PRIMARY KEY (`id`),
  KEY `pstore_nonce_fbfc09f1` (`user_id`),
  CONSTRAINT `user_id_refs_id_1dce6d95` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

====

# printf "insert into pstore_nonce (created, user_id, value, encrypted) values (now(), 1, 'abc', '\xff'); show warnings;" | ./manage dbshell
Level	Code	Message
Warning	1300	Invalid utf8 character string: 'FF'