MariaDB FEDERATED text column

Question

I created a FEDERATED database on a Centos 7 with MariaDB 5.5.44 from a db located on a Centos 6.x with Mysql 5.1.69.

Updating value for a TEXT column from Centos 7 - MariaDb returns no error but does not update column value.

Update works fine with varchar & int.

Does anyone encoutered this issue and have some information about it ?

Thanks Nicolas

(copied from comment and prettyprinted...)

CREATE TABLE IF NOT EXISTS session (
    session int(11) NOT NULL, 
    session_name varchar(32) COLLATE latin1_general_ci DEFAULT NULL, 
    om_utilisateur int(11) NOT NULL, 
    clef varchar(100) COLLATE latin1_general_ci NOT NULL, 
    valeur text COLLATE latin1_general_ci NOT NULL, 
    crea_modi timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
    PRIMARY KEY (session), 
    KEY session_name (session_name)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://USER:PASS@prod/utilisation/news';

CREATE TABLE IF NOT EXISTS `session` ( `session` int(11) NOT NULL, `session_name` varchar(32) COLLATE latin1_general_ci DEFAULT NULL, `om_utilisateur` int(11) NOT NULL, `clef` varchar(100) COLLATE latin1_general_ci NOT NULL, `valeur` text COLLATE latin1_general_ci NOT NULL, `crea_modi` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`session`), KEY `session_name` (`session_name`) ) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://USER:PASS@prod/utilisation/news'; — nicohell, Dec 03 '15 at 09:34
UPDATE icm_utilisation.session SET session_name = 'a921ff8df2bac2a58468d3af60daefc4', om_utilisateur = '0', clef = 'sqltabtaches-info', valeur = '[4480,4459,4460,4439,4031,4040,4041,4008,4007,4392,4002,4000,3971,3968,3966,4351,3949,4333,4342,3931,3932,4328,3909,3911,4704,4673,4295,3858,4647,4645,4597,4200,4193,4185,4183,3743,3739,4593,4532,4529]' WHERE session = 195 --> valeur is json_encode — nicohell, Dec 03 '15 at 09:39
UP today same issue with 2 Centos 7 and mysql Ver 15.1 Distrib 5.5.44-MariaDB, for Linux (x86_64) using readline 5.1 — nicohell, Mar 28 '17 at 12:48

score 0 · Answer 1 · edited May 23 '17 at 12:10

We have the same problem with a Debian Stretch "mysql Ver 15.1 Distrib 10.1.22-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2" accessing with federated tables to a Debian Wheezy "mysql Ver 14.14 Distrib 5.5.55, for debian-linux-gnu (x86_64) using readline 6.2"

We have not a real solution but two possible workarounds:

update value to NULL or empty string first and save the wanted value afterwards
use big VARCHAR instead of TEXT

A bug like this was reported on the mysql bug site: https://bugs.mysql.com/bug.php?id=34997

Other people speaking about this here: mySql federated table unable to update blob columns

MariaDB FEDERATED text column

1 Answers1