Problems with update in federated table

Question

Problems with update in federated table

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I have a problem understanding an error when using a federated table.

Scenery:

Server A: create view federated_view as SELECT ....

Server B: create table table_federada (A, B, C)

ENGINE = FEDERATED CONNECTION = 'mysql: // user: paswd @: 3306 / database / federated_view';

As long as I perform an update on the federated table, locally on server B, execution of the command returns OK, without errors, but the value does not change:

mysql> update tabela_federada set incidentes_utilizados = 1 where customer_id = 'teste' and incidentes_utilizados = 10;
Query OK, 1 row affected (0.17 sec)

mysql> select * from tabela_federada where customer_id = 'teste'\G
incidentes_utilizados: 10
     bugs_qualificados: 3
requisicoes_utilizadas: 2
     horas_disponiveis: 4.00

mysql sql-update table

asked by anonymous 21.06.2017 / 16:13

2 answers

Remove javascript mask How to use Python integrated with .CSV

score 0 · Answer 1

Have you checked that the servers that access the database have all the necessary permissions?

Make sure to grant the necessary privileges on the ips that access the MySQL server:

GRANT ALL PRIVILEGES ON nome_da_base_de_dados.* TO 'usuario'@'ip_servidor';

If you prefer, you can use "%" instead of "ip_server" to grant permission to all ips (NOT PERMANENTLY RECOMMENDED) , but for test purpose setting "%" temporarily can quickly be a good suggestion, at least to confirm if it really is a privilege problem (permission)

score 0 · Answer 2

Thanks for the help I received, I found the documentation for federated tables in MySQL, and what happened was that the view used to create the federated table has many aggregated functions, such as JOIN and therefore, does not perform the update in the source table, nor in the federated table.

The output was to create a table on server A from view , and create the federated table from this new table on server A.