[MXS-56] MySQL insert binary blob don't works Created: 2015-03-26  Updated: 2016-02-04  Resolved: 2016-02-04

Status: Closed
Project: MariaDB MaxScale
Component/s: Core
Affects Version/s: 1.0.5
Fix Version/s: 1.2.1

Type: Bug Priority: Major
Reporter: André Bauer Assignee: Massimiliano Pinto (Inactive)
Resolution: Fixed Votes: 1
Labels: None
Environment:

Ubuntu 14.04
Maxscale 1.1 DEB from your APT Repo
MariaDB 10.0.0.17
Galera 3 Node Cluster


 Description   

We're using OTRS 4.0.6 ( www.otrs.org ) as ticketsystem.
When trying to use an attachment (JPEG Images or ZIP) in an outgoing mail, which is saved as binarys blob in the attachment table, it does not work.

OTRS log says:
Access denied for user 'Ô…Ü-ªC´†�¢[É\r*\"5}4r;\Z�Ɓ�vË°.2\0…S˜?t�Ä‚°ÆsghuÒ!g×C^�¹[íR¯\'ŽR�k=�Aß�r†Öqg(wò{Ã�JNÑ.i4sb>ÅÌISjzL.ü4‰¯ý15?èBLÿÀ\'\"�

{Dç�ÍnË}

'@'192.168.10.50' (using password: YES), SQL: '
MySQL server has gone away, SQL:

You can try with this file: http://download.wsusoffline.net/wsusoffline951.zip

When i change the Config.pm of OTRS to use the Mysql server directly (without maxscale as loadbalancer) it works.

Unfortunately there are no other log entrys available. Neither maxscale or mysql.

Seems to me this could be some escaping problem of the binatry part of the sql query?
Dont knwo where he takes the 192.168.10.50 host from but its not our maxscale server but the otrs server itself.
Any hints?

 Comments   
Comment by Massimiliano Pinto (Inactive) [ 2015-03-30 ]

Hi,

it's possible to share the table description?

At least for the blob column.

Even MaxScale.cfg would be very useful.

A first test with read connection router and a blob column updated by a PHP script was fine:

CREATE TABLE `picture` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`image` blob,
PRIMARY KEY (`id`)
) ENGINE=InnoDB

First I uploaded a JPG, then the zip file mentioned above:

MariaDB [test]> select id, OCTET_LENGTH(image) from picture;
-----------------------+

id OCTET_LENGTH(image)

-----------------------+

1 9728
11 9728
14 9728
15 9728
16 65535
17 65535
18 65535

-----------------------+
7 rows in set (0.01 sec)

In my test case BLOB is limited to 65535, the zip file it's about 2.2 MBytes.

It would be worth having more informations from you.

Comment by André Bauer [ 2015-03-31 ]

This is the table OTRS uses:

 
 
CREATE TABLE IF NOT EXISTS `article_attachment` (
 
`id` bigint(20) NOT NULL,
 
  `article_id` bigint(20) NOT NULL,
 
  `filename` varchar(250) DEFAULT NULL,
 
  `content_size` varchar(30) DEFAULT NULL,
 
  `content_type` text,
 
  `content_id` varchar(250) DEFAULT NULL,
 
  `content_alternative` varchar(50) DEFAULT NULL,
 
  `content` longblob NOT NULL,
 
  `create_time` datetime NOT NULL,
 
  `create_by` int(11) NOT NULL,
 
  `change_time` datetime NOT NULL,
 
  `change_by` int(11) NOT NULL,
 
  `disposition` varchar(15) DEFAULT NULL
 
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7110 ;

Imho you could test easily by installing OTRS.
Its not that hard.
You also could use the appliance:
https://www.otrs.com/download-thank-you/thank-you-downloading-otrs-software/?download=otrs-4.0.7-appliance.iso

Comment by André Bauer [ 2015-04-10 ]

Problem still exists in Maxscale 1.1

Comment by markus makela [ 2016-02-04 ]

monotekCan you try this with the 1.3.0-beta version of MaxScale? The packages can be found here: http://maxscale-jenkins.mariadb.com/ci-repository/1.3.0-beta-release1/mariadb-maxscale/

Comment by André Bauer [ 2016-02-04 ]

Can't test the Beta in production.
But seems to be OK in 1.2.1 now.

Comment by markus makela [ 2016-02-04 ]

That's good to hear. I'll close this and if it happens again, please open this bug or create a new one.

Comment by markus makela [ 2016-02-04 ]

Closing this as fixed in 1.2.1.

Generated at Thu Feb 08 03:56:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.