Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5165

Duplicate MDEV-4452

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 10.0.4, 5.5.33a
    • Fix Version/s: N/A
    • Component/s: OTHER
    • Labels:
      None
    • Environment:
      Debian Squeeze

      Description

      Facing the same MDEV-4452 issue form a trigger that is triggered form a replication .

      sur MYSQL1 :

      répli "ccmstats_lucifer"

      Error 'Got error 10000 'Error on remote system: 2006: MySQL server has gone away' from FEDERATED' on query. Default database: 'ccmstats_shard03'. Query: 'replace into `ccmstats_shard03`.`ccmreferers`(`ip`,`date`,`firstseenon`,`keyword`,`domaine`,`referer`,`keyword_crc64`)values(3363428860,'2013-10-19 22:27:41','/download/start/descargar-14103-driver-de-video-de-lenovo-ibm-thinkpad-t30','','es.kioskea.net','http://static.ak.facebook.com/connect/xd_arbiter.php?version=27',9175071638627673410)'

      répli "ccmstats_mysql1"

      Error 'Got error 10000 'Error on remote system: 2006: MySQL server has gone away' from FEDERATED' on query. Default database: 'ccmstats_shard07'. Query: 'replace into `ccmstats_shard07`.`ccmreferers`(`ip`,`date`,`firstseenon`,`keyword`,`domaine`,`referer`,`keyword_crc64`)values(1947240397,'2013-10-19 09:33:46','/sites/details/1089563.jjwxc.net','','www.commentcamarche.net','http://www.quanneiren.com/seo/?page=1068&url=1089563.jjwxc.net',2180121150729504318)'

      sur LUCIFER :

      répli "ccmstats_gertrude"

      Error 'Got error 10000 'Error on remote system: 2006: MySQL server has gone away' from FEDERATED' on query. Default database: 'ccmstats_shard13'. Query: 'replace into `ccmstats_shard13`.`ccmreferers`(`ip`,`date`,`firstseenon`,`keyword`,`domaine`,`referer`,`keyword_crc64`)values(1323859475,'2013-10-19 11:47:46','/forum/affich-1573832-pourquoi-mon-timer-ne-s-execute-pas','','codes-sources.commentcamarche.net','http://www.google.fr/url?sa=t&rct=j&q=&esrc=s&source=web&cd=2&ved=0CDQQFjAB&url=http%3A%2F%2Fcodes-sources.commentcamarche.net%2Fforum%2Faffich-1573832-pourquoi-mon-timer-ne-s-execute-pas&ei=NVViUoq0PMa90QXl9ICACQ&usg=AFQjCNH10nmC3K-H9cMsSbl1rj7V7M_V7Q&bv',2736937289062304772)'

      répli "ccmstats_lucifer"

      Error 'Got error 10000 'Error on remote system: 2006: MySQL server has gone away' from FEDERATED' on query. Default database: 'ccmstats_shard10'. Query: 'replace into `ccmstats_shard10`.`ccmreferers`(`ip`,`date`,`firstseenon`,`keyword`,`domaine`,`referer`,`keyword_crc64`)values(2903347022,'2013-10-19 09:27:37','/news/12118-firefox-la-nuova-versione-nel-play-store','','it.kioskea.net','http://184.84.222.35/news/12118-Firefox%2C+la+nuova+versione+nel+Play+Store',9684943911008351985)'

      sur GERTRUDE :

      Error 'Got timeout reading communication packets' on query. Default database: 'ccmstats_shard24'. Query: 'replace into `ccmstats_shard24`.`ccmreferers`(`ip`,`date`,`firstseenon`,`keyword`,`domaine`,`referer`,`keyword_crc64`)values(3192578384,'2013-10-19 04:04:09','/download/start/descargar-16307-driver-de-audio-de-placa-base-pcchips-p27g','','es.kioskea.net','http://static.ak.facebook.com/connect/xd_arbiter.php?version=27',6488389282267718615)'

      The master is 5.5.33a

      CREATE TABLE `domaine_federated` (
      `domaine` varchar(128) DEFAULT NULL,
      `idDomaine` tinyint(4) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`idDomaine`),
      UNIQUE KEY `idx_domaine` (`domaine`)

      ) ENGINE=federated CONNECTION='PUMA/domaine'
      DEFAULT CHARSET=latin1 ;

      CREATE TABLE `url_federated` (
      `url` char(255) DEFAULT NULL,
      `idUrl` int(10) unsigned NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`idUrl`),
      UNIQUE KEY `idx_url` (`url`)
      ) ENGINE=federated CONNECTION='PUMA/url'
      DEFAULT CHARSET=latin1 ;

      The slave have a before insert trigger

      define like this

      CREATE TABLE `ccmreferers` (
      `ip` int(4) unsigned NOT NULL,
      `date` datetime NOT NULL,
      `firstseenon` varchar(255) NOT NULL,
      `keyword` varchar(128) NOT NULL,
      `domaine` varchar(128) NOT NULL,
      `referer` varchar(255) NOT NULL,
      `keyword_crc64` bigint(20) unsigned NOT NULL,
      PRIMARY KEY (`ip`,`keyword`),
      KEY `date` (`date`),
      KEY `referer` (`referer`,`firstseenon`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

      DECLARE l_idUrl INT unsigned DEFAULT 0;
      DECLARE i_idDom TINYINT unsigned DEFAULT 0;
      DECLARE c_kw VARCHAR(128);
      IF NOT EXISTS (SELECT 1 FROM ccmreferers
      WHERE keyword = NEW.keyword AND ip = NEW.ip)
      THEN
      SET l_idUrl = ccmstats.GetIdUrl(NEW.firstseenon);
      SET i_idDom = ccmstats.GetIdDomaine(NEW.domaine);
      INSERT INTO stats_url_cur
      SET keyword_crc64 = NEW.keyword_crc64,
      DATE = NEW.date,
      idUrl = l_idUrl,
      idDomaine = i_idDom,
      nb = 1
      ON DUPLICATE KEY UPDATE nb=nb+1;
      IF LENGTH(NEW.keyword) > 0 THEN
      SET c_kw = REPLACE(TRIM(NEW.keyword),' ',' ');
      INSERT INTO stats_url_kw_cur
      SET keyword_crc64 = NEW.keyword_crc64,
      DATE = NEW.date,
      idUrl = l_idUrl,
      keyword = c_kw,
      idDomaine = i_idDom,
      nb = 1
      ON DUPLICATE KEY UPDATE nb=nb+1;
      END IF;
      END IF;

      delimiter //
      drop function GetIdUrl //

      CREATE DEFINER=`root`@`%` FUNCTION `GetIdUrl`(l_strUrl varchar(255)) RETURNS int(11)
      READS SQL DATA
      BEGIN
      DECLARE l_IdUrl int default 0;
      SELECT IdUrl into l_IdUrl FROM `url` where Url = l_strUrl;
      IF l_IdUrl=0 then
      INSERT IGNORE INTO url_federated (Url) VALUES (l_strUrl);
      SELECT IdUrl into l_IdUrl FROM `url_federated` where Url = l_strUrl ;
      END IF;
      RETURN l_IdUrl;
      END;
      //

      delimiter //
      drop function GetIdDomaine //

      CREATE DEFINER=`root`@`%` FUNCTION `GetIdDomaine`(l_strDomaine char(50)) RETURNS int(11)
      READS SQL DATA
      BEGIN
      DECLARE l_IdDomaine int default 0;
      SELECT IdDomaine into l_IdDomaine FROM domaine where Domaine = l_strDomaine;
      IF l_IdDomaine=0 THEN
      INSERT IGNORE INTO domaine_federated (Domaine) VALUES (l_strDomaine);

      SELECT IdDomaine into l_IdDomaine FROM domaine_federated where Domaine = l_strDomaine;

      END IF;
      RETURN l_IdDomaine;
      END;
      //

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              elenst Elena Stepanova
              Reporter:
              stephane@skysql.com VAROQUI Stephane
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: