Many databases combined with MariaDB Spider engine tables [on hold]

We are in the process of refactoring the architecture of our main ETL process in our company.

Basically, the process:

  1. Extracts data from WebServices, MySQL Databases, etc.
  2. The data gets standarized.
  3. The data gets saved in our database.

The data sources are other businesses integrations. We develop a new integration for every client. A integration is given by an extractor application, specifically adaptaed to the sources endpoints (mainly some WebServices). The data extracted is then sent to AWS SQS queues for standarization. The standarizer app runs every hour consuming the messages. Once they’re standarized, messages are sent to another queue (the saver queue). Every hour, the saver app runs and process the messages, saving the content in our database.

enter image description here

Since we’re growing in the number of business integrations, our database is getting some stress during the ETL process and we fear the we’ll end up in a bottleneck. Mainly because of the huge amount of write IOPS that the saver introduces in the main database.

I’m currently in charge of the refactor and I came up with the following architecture design. Given the modular design of the saver and standarizer, they could be “packed” in a app stack alogn with the extractor and a dedicated database for each business. Like so:

enter image description here

The ? labeled box is because the consumer API executes some query over the database that required that all the data is stored on a single database (some reducing, grouping, etc), so, a service of some kind should be present there to let the Consumer API app execute those queries as if a single database is still present.

Doing some research I found MariaDB’s Spider storage engine as an option. Basically, what I was thinking is to mount a EC2 instance with MariaDB installed, and define tables that are partitioned across the businesses databases. That way, the Consumer API will think that only a single database exists, and would let MariaDB take care of the SQL request distributed across each database.

My question is:

Is this a valid use case for the Spider engine?+

To take in mind:

  1. We are not in a position of implementing a ESB (small team, low cost)
  2. We must avoid refactoring the SQL queries of the Consumer API.

Thanks in advance.

ShineServers.Com – FLAT 70% Discount, SSD Storage, CLOUDLINUX, MariaDB, cPanel, Unlimited BW

Started In 2010 With An Aim To Provide A Comprehensive Range Of Secure Web Hosting Services; Shine Servers Proudly Serves Thousands Of Satisfied Customers At Present And Still Counting. Registered By Name Of "Shine Servers LLP" In New Delhi, India And Serving The Worldwide, We Have An Overall Experience Of 10 Years In The Web Hosting Industry. Quality And Customer Service Is Our Stronghold…

ShineServers.Com – FLAT 70% Discount, SSD Storage, CLOUDLINUX, MariaDB, cPanel, Unlimited BW

MariaDB Galera + Replication (GTID error)

  • Using MariaDB 10.2.25 + Galera with 3 masters
  • All Masters have binlogs active
  • Want to switch the Master_Host of an external salve (also running MariaDB 10.2.25) to a different Master.
  • Replication is using GTID (CHANGE MASTER TO master_use_gtid=slave_pos)
  • Replication works perfectly until attempting to change Master_host

Changing master:

STOP SLAVE; CHANGE MASTER TO MASTER_HOST='x.x.x.x'; START SLAVE; 

Results in:

            Last_IO_Errno: 1236             Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find GTID state requested by slave in any binlog files. Probably the slave state is too old and required binlog files have been purged.'                Last_SQL_Errno: 0                Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 107                Master_SSL_Crl: /etc/mysql/ssl/ca-cert.pem            Master_SSL_Crlpath:                    Using_Gtid: Slave_Pos                   Gtid_IO_Pos: 1-107-1746809509 

Reading the correct binlog from the Master, I see:

... #190626 19:57:36 server id 107  end_log_pos 55375314    Update_rows: table id 148 flags: STMT_END_F # at 55375314 #190626 19:57:36 server id 107  end_log_pos 55375341    Xid = 1983287875 COMMIT/*!*/; # at 55375341 #190626 19:57:36 server id 107  end_log_pos 55375379    GTID 1-107-1746809509 trans /*!100001 SET @@session.gtid_seq_no=1746809509*//*!*/; BEGIN ... 

What is going on here? The GTID exists in the Master binlogs.

load_file returns NULL in Mariadb without error

I’m trying:

select load_file(‘/home/rosita/NotificacionExp 1.pdf’); select load_file(‘/home/rosita/PruebaBlob.txt’);

both results are NULL

I’d like to attach files to the next file:

sql_mode and global.sql_mode are blank

$ ls -l /home/rosita both fiels in this directory

Grants are: GRANT ALL PRIVILEGES ON . TO ‘ROsa’@’localhost’ IDENTIFIED BY PASSWORD ‘xx’ WITH GRANT OPTION |

local_infile Value ON

Where IN com parâmetro com vários valores (MySQL / MariaDB)

Tenho 2 tabelas: Grupos e Sub Grupos

Preciso selecionar todos os sub grupos de acordo com o grupo selecionado. No meu caso selecionar 1 grupo e ver todos os sub grupos não foi problema. O problema está em selecionar varios grupos que no caso irá obter todos os sub grupos dos grupos selecionados.

Eu consegui fazer sem usando o Where IN. inserir a descrição da imagem aqui

Colocar os valores manualmente funciona, meu problema está em colocar esses valores por parametro.

inserir a descrição da imagem aqui

Na segunda imagem eu coloquei um parametro pra colocar esses valores (ignorem o primeiro e o terceiro parametro. É o segundo parametro), mas como pode ver ele só pega o primeiro valor, os outros são ignorados.

Can we use MariaDB mysqldump CLI to take dump from MySQL Community version?

MySQL server version:

mysqld Ver 5.7.26 for Linux on x86_64 (MySQL Community Server (GPL))

MariaDB mysqldump CLI version:

mysqldump Ver 10.17 Distrib 10.3.12-MariaDB, for Linux (x86_64)

I am using this mysqldump version to take dump from the mentioned mysqld version. Data is around 25 gb.

I am getting below error while importing the dump,

ERROR 3105 (HY000) at line 82: The value specified for generated column ‘column_name’ in table ‘table_name’ is not allowed.

Can we use MariaDB mysqldump CLI to take dump from MySQL Community version?

Fazer 2 select na mesma procedure MariaDB

Essa minha procedure faz o select de uma busca de usuários da minha tabela de usuários, porém tenho outra tabela que tem a parametrização de quantos registros podem ser mostrados nessa tela de usuários. A idéia é fazer o select na mesma procedure e retornar o valor desse “LIMIT”, mas ta errada a sintaze que codifiquei. Alguém sabe me orientar como faço isso?

inserir a descrição da imagem aqui

MariaDB limit value of column

I want to limit the value of the column limited_column, where 0 >= limited_column <= 100 SQL side, on MariaDB

I’ve tried creating a trigger on INSERT ad UPDATE as such:

DROP TABLE IF EXISTS `users`; CREATE TABLE `users` (   `username` varchar(25) NOT NULL,   `user_id` int(100) NOT NULL,   `limited_column` bigint(20) unsigned NOT NULL DEFAULT '0', [...]   PRIMARY KEY (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;  DELIMITER $  $   CREATE TRIGGER `limited_column_check_on_insert_trigger` BEFORE INSERT ON `users` FOR EACH ROW BEGIN   DECLARE dummy,baddataflag INT;   SET baddataflag = 0;   IF NEW.limited_column > 100 THEN     SET baddataflag = 1;   END IF;   IF NEW.limited_column < 0 THEN     SET baddataflag = 1;   END IF;   IF baddataflag = 1 THEN     SELECT CONCAT('Cannot INSERT new value because limited_column is > 100, value was ',NEW.limited_column)     INTO dummy FROM information_schema.tables;   END IF; END; $  $   CREATE TRIGGER `limited_column_check_on_update_trigger` BEFORE UPDATE ON `users` FOR EACH ROW BEGIN   DECLARE dummy,baddataflag INT;   SET baddataflag = 0;   IF NEW.limited_column > 100 THEN     SET baddataflag = 1;   END IF;   IF NEW.limited_column < 0 THEN     SET baddataflag = 1;   END IF;   IF baddataflag = 1 THEN     SELECT CONCAT('Cannot UPDATE new value because limited_column is > 100, value was ',NEW.limited_column)     INTO dummy FROM information_schema.tables;   END IF; END; $  $   DELIMITER ; 

This is what I get if I try inserting a new user when limited_column > 100 (limited_column > 100 works):

MariaDB [NameOfADatabase]> INSERT INTO users (username,user_id,limited_column,[...]) VALUES ('testestes',1,1000,[...]); ERROR 1172 (42000): Result consisted of more than one row MariaDB [NameOfADatabase]> INSERT INTO users (username,user_id,limited_column,[...]) VALUES ('testestes',2,100,[...]); Query OK, 1 row affected (0.02 sec) 

Any ideas on what I can do to make this more graceful?