Tivemos alguma lentidão em nossa aplicação. Quando verifiquei, descobri que uma tabela específica estava bloqueada para que todas as transações fossem rejeitadas pelo Lock wait timeout exceeded. Quando fiz SHOW PROCESSLIST;esperava ver alguns processos com Waiting for table level lockestado mas não mostra nada!? apenas algumas consultas INSERT/UPDATE na mesma tabela demorando exatamente 120 segundos antes do tempo limite e outros processos no estado de suspensão (veja a imagem):
Eu tenho uma replicação MASTER/SLAVE ativa (Mariadb 10.2) com um único banco de dados (veja o cenário 1)
Eu decidi adicionar no banco de dados IMPORT para preencher o banco de dados MASTER conforme descrito no cenário 2. No entanto, a instância SLAVE fica OFF e o erro “ Importação de banco de dados desconhecido ” é rastreado.
Para superar isso, criei então um banco de dados com o mesmo nome no servidor SLAVE (importação) e o erro desapareceu (ver cenário 3).
Porém, o banco de dados criado (slave import) estava sem tabelas (de propósito) então para ter certeza que os dados são inseridos no slave eu testei criando uma tabela de teste no banco de dados MASTER e inseri dados do banco de dados de importação do Master .
Como resultado, os dados estavam obviamente disponíveis no banco de dados escravo mesmo que o banco de dados de importação do escravo não tivesse tabelas.
Então, basicamente, precisamos apenas do mesmo nome de banco de dados na seção slave para trabalhar sem a necessidade de tabelas.
Você poderia me explicar como funciona? E por que precisamos do mesmo db no escravo se for um formato baseado em linha?
Estou tentando instalar o MariaDB Galera Cluster com 2 nodes:
nó 1/172.23.0.2 :
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
binlog_format=ROW
wsrep_cluster_address='gcomm://'
wsrep_sst_receive_address = '172.23.0.2:4444'
wsrep_cluster_name='cluster'
wsrep_node_name='n_01'
wsrep_sst_method=rsync
wsrep_sst_auth=cluster_user:cluster_pass
nó 2/ 172.23.0.3 :
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
binlog_format=ROW
wsrep_cluster_address='gcomm://172.23.0.2'
wsrep_sst_receive_address = '172.23.0.3:4444'
wsrep_cluster_name='cluster'
wsrep_node_name='n_02'
wsrep_sst_method=rsync
wsrep_sst_auth=cluster_user:cluster_pass
o primeiro nó começou sem erros:
Variable_name Value
-------------------- ---------
wsrep_cluster_size 1
wsrep_cluster_status Primary
wsrep_connected ON
wsrep_ready ON
mas quando iniciei o nó 2n, obtive isso:
mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
Active: failed (Result: exit-code) since jeu. 2017-08-24 19:11:32 CEST; 14s ago
Process: 14656 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 20222 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
Process: 18861 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
Process: 18858 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Main PID: 20222 (code=exited, status=1/FAILURE)
Status: "MariaDB server is down"
CGroup: /system.slice/mariadb.service
├─20357 /bin/bash -ue /usr//bin/wsrep_sst_rsync --role joiner --address 172.23.0.3 --datadir /home/mysql/ --parent 20309 --binlog /var/log/mariadb/binlog/mysql_binlog
├─20391 rsync --daemon --no-detach --port 4444 --config /home/mysql//rsync_sst.conf
├─22006 /bin/bash -ue /usr//bin/wsrep_sst_rsync --role joiner --address 172.23.0.3 --datadir /home/mysql/ --parent 21997 --binlog /var/log/mariadb/binlog/mysql_binlog
├─22638 sleep 0.2
└─22648 sleep 0.2
août 24 19:11:23 ovh38 mysqld[20222]: 2017-08-24 19:11:23 127079663351552 [ERROR] WSREP: Failed to read 'ready <addr>' from: wsrep_sst_rsync --role 'joiner' --address '172.23.0.3:4444' --datadir '/home/mysql/' --pa...log/mysql_binlog'
août 24 19:11:23 ovh38 mysqld[20222]: Read: '(null)'
août 24 19:11:23 ovh38 mysqld[20222]: 2017-08-24 19:11:23 127079663351552 [ERROR] WSREP: Process completed with error: wsrep_sst_rsync --role 'joiner' --address '172.23.0.3:4444' --datadir '/home/mysql/' --parent '...eady in progress)
août 24 19:11:23 ovh38 mysqld[20222]: 2017-08-24 19:11:23 127080155712256 [ERROR] WSREP: Failed to prepare for 'rsync' SST. Unrecoverable.
août 24 19:11:23 ovh38 mysqld[20222]: 2017-08-24 19:11:23 127080155712256 [ERROR] Aborting
août 24 19:11:32 ovh38 mysqld[20222]: Error in my_thread_global_end(): 1 threads didn't exit
août 24 19:11:32 ovh38 systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
août 24 19:11:32 ovh38 systemd[1]: Failed to start MariaDB database server.
août 24 19:11:32 ovh38 systemd[1]: Unit mariadb.service entered failed state.
août 24 19:11:32 ovh38 systemd[1]: mariadb.service failed.
Hint: Some lines were ellipsized, use -l to show in full.
ATUALIZADA :
A origem deste erro é devido ao programa rsync já em uso, então a solução foi finalizá-lo:
Proto Recv-Q Send-Q Adresse locale Adresse distante Etat PID/Program name
tcp 0 0 0.0.0.0:21 0.0.0.0:* LISTEN 1087/proftpd: (acce
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 708/sshd
tcp 0 0 0.0.0.0:4444 0.0.0.0:* LISTEN 15510/rsync
tcp6 0 0 :::80 :::* LISTEN 19059/httpd
tcp6 0 0 :::22 :::* LISTEN 708/sshd
tcp6 0 0 :::443 :::* LISTEN 19059/httpd
tcp6 0 0 :::4444 :::* LISTEN 15510/rsync
tcp6 0 0 :::545 :::* LISTEN 19059/httpd
#kill -9 15510
Eu tento reiniciar o segundo nó: systemctl start mariadb
No primeiro nó: SHOW STATUS LIKE 'wsrep_cluster%'
Variable_name Value
------------------------ --------------------------------------
wsrep_cluster_conf_id 2
wsrep_cluster_size 2
wsrep_cluster_state_uuid 00edfa0e-88d5-11e7-8f43-5ea901e83b3a
wsrep_cluster_status Primary
No entanto, outro erro apareceu:
# systemctl status mariadb.service
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
Active: failed (Result: timeout) since ven. 2017-08-25 10:42:09 CEST; 11min ago
Process: 14656 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 12697 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
Process: 12685 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Main PID: 15310
CGroup: /system.slice/mariadb.service
├─15310 /usr/sbin/mysqld --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1
├─15468 /bin/bash -ue /usr//bin/wsrep_sst_rsync --role joiner --address 172.23.0.3 --datadir /home/mysql/ --parent 15310 --binlog /var/log/mariadb/binlog/mysql_binlog
├─15510 rsync --daemon --no-detach --port 4444 --config /home/mysql//rsync_sst.conf
├─15980 /bin/bash -ue /usr//bin/wsrep_sst_rsync --role joiner --address 172.23.0.3 --datadir /home/mysql/ --parent 15901 --binlog /var/log/mariadb/binlog/mysql_binlog
├─18646 sleep 0.2
├─18670 sleep 0.2
├─18675 sleep 0.2
├─18676 sleep 0.2
├─18686 sleep 0.2
├─20357 /bin/bash -ue /usr//bin/wsrep_sst_rsync --role joiner --address 172.23.0.3 --datadir /home/mysql/ --parent 20309 --binlog /var/log/mariadb/binlog/mysql_binlog
├─22006 /bin/bash -ue /usr//bin/wsrep_sst_rsync --role joiner --address 172.23.0.3 --datadir /home/mysql/ --parent 21997 --binlog /var/log/mariadb/binlog/mysql_binlog
└─23982 /bin/bash -ue /usr//bin/wsrep_sst_rsync --role joiner --address 172.23.0.3 --datadir /home/mysql/ --parent 23794 --binlog /var/log/mariadb/binlog/mysql_binlog
août 25 10:39:10 ovh38 mysqld[15310]: 2017-08-25 10:39:10 115191544425216 [Note] WSREP: New cluster view: global state: 00edfa0e-88d5-11e7-8f43-5ea901e83b3a:0, view# 2: Primary, number of nodes: 2, my index: 0, protocol version 3
août 25 10:39:10 ovh38 mysqld[15310]: 2017-08-25 10:39:10 115191544425216 [Warning] WSREP: Gap in state sequence. Need state transfer.
août 25 10:39:10 ovh38 mysqld[15310]: 2017-08-25 10:39:10 115190975993600 [Note] WSREP: Running: 'wsrep_sst_rsync --role 'joiner' --address '172.23.0.3' --datadir '/home/mysql/' --parent '15310' --binlog '/var/log/...g/mysql_binlog' '
août 25 10:39:10 ovh38 rsyncd[15510]: rsyncd version 3.0.9 starting, listening on port 4444
août 25 10:39:13 ovh38 mysqld[15310]: 2017-08-25 10:39:13 115191014389504 [Note] WSREP: (42fd49d1, 'tcp://0.0.0.0:4567') turning message relay requesting off
août 25 10:40:39 ovh38 systemd[1]: mariadb.service start operation timed out. Terminating.
août 25 10:42:09 ovh38 systemd[1]: mariadb.service stop-final-sigterm timed out. Skipping SIGKILL. Entering failed mode.
août 25 10:42:09 ovh38 systemd[1]: Failed to start MariaDB database server.
août 25 10:42:09 ovh38 systemd[1]: Unit mariadb.service entered failed state.
août 25 10:42:09 ovh38 systemd[1]: mariadb.service failed.
Hint: Some lines were ellipsized, use -l to show in full.
Alguma ideia para resolver este problema?
Estou trabalhando em um projeto espacial de banco de dados no Mysql e estava tentando realizar uma operação de união para um grupo de formas. Estou procurando algo semelhante à função SQL Server UnionAggregateou a função PostGIS Spatial AggregateST_Union
por exemplo (no SQL SERVER):
SELECT
geometry::STGeomFromWKB(Shape,27582),
area_code
FROM area_table
WHERE area_code='xxxxxxx';
ST_GeomFromWKB faz o mesmo trabalho no Mysql.
na segunda parte e com a UnionAggregatefunção + group by :
select
dbo.UnionAggregate((geometry::STGeomFromWKB(Shape,27582)).MakeValid()),
area_code
FROM area_table
WHERE area_code='xxxxxxx'
GROUP BY area_code;
Como executar uma operação semelhante (agregar + agrupar por) no MySQL?
Eu enfrentei alguns problemas reais em relação à execução de alguns procedimentos armazenados usando o servidor Mysql. Às vezes, um dos meus procedimentos não é executado (ou talvez seja interrompido) durante uma chamada externa (do aplicativo), então estou pensando:
Obrigado.
Estou procurando uma maneira de importar dados de um arquivo de texto para um banco de dados MySQL, o arquivo de texto costumava ser importado em um banco de dados SQL SERVER via BCP e opção FORMATFILEs por exemplo:
BULK INSERT [MyTable] FROM 'myFolder\.txt'
WITH (
FORMATFILE = 'myFolder\MyTable.fmt',
ROWS_PER_BATCH = 5000,
MAXERRORS = 5000,
CODEPAGE = 'ACP',
ERRORFILE = 'myFolder\errors.txt'
)
MyTable.fmt :
7.0
4
1 SQLCHAR 0 3 "" 1 Field1
2 SQLCHAR 0 7 "" 2 Field2
3 SQLCHAR 0 3 "" 3 Field3
4 SQLCHAR 0 2 "" 4 Field4
Os campos (ou colunas) no arquivo de origem não são separados, a única coisa a distinguir os dados é o número de caracteres por cada coluna, conforme mencionado no arquivo de formato MyTable.fmt, por exemplo:
Alguma idéia de como proceder no MySQL?
Eu preciso obter toda a mensagem de erro retornada do MySQL e atribuí-la a @error_string:
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
SET @error_string = 'SQLEXCEPTION_ERROR_MESSAGE'; <-- here
END;
Existe alguma alternativa, obrigado.
Oi, eu estava trabalhando por horas para acelerar esta consulta usando índices, mas é inútil. Estou tentando otimizar eliminando 'Usando temporário' e usando apenas índices. Será que existe outra alternativa para agilizar essa consulta??
consulta :
SELECT DISTINCT
tbl_dv_fg.id,
IFNULL(
tbl_dv_fg.nb_action_rea / tbl_dv_fg.nb_action_total,
0
) AS txRea,
tbl_dv_fg.num_cmde AS numcmde,
IF(tbl_fgp.NUMCMDE IS NOT NULL, 1, 0) AS isPlurale,
IF(
tbl_dv_fg.relance_date = DATE_FORMAT(NOW(), '%Y-%m-%d'),
0,
1
) AS DateRelanceArrivee,
DATE_FORMAT(
IFNULL(
tbl_fg.szdatevente,
tbl_fgp.szdatevente
),
'%d/%m/%Y'
) AS szdatevente,
DATE_FORMAT(
tbl_pilotage.date_mada,
'%d/%m/%Y'
) AS dateMadaPilotage,
DATE_FORMAT(
tbl_pilotage.date_mada_eng,
'%d/%m/%Y'
) AS dateMadaEngPilotage,
DATE_FORMAT(
tbl_pilotage.date_liv_clt_conv,
'%d/%m/%Y'
) AS dateLivCltConv,
IFNULL(
tbl_fg.szdatevente,
tbl_fgp.szdatevente
) AS szdateventeSort,
tbl_pilotage.date_mada AS dateMadaPilotageSort,
tbl_pilotage.date_mada_eng AS dateMadaEngPilotageSort,
tbl_pilotage.date_liv_clt_conv AS dateLivCltConvSort,
tbl_pilotage.ord_c AS ordC,
CONCAT(
tbl_dv_fg.sznomclient,
' ',
tbl_dv_fg.szprenomclient
) AS CLIENT,
CONCAT(
tbl_user.user_nom,
' ',
tbl_user.user_prenom
) AS vendeur,
DATE_FORMAT(
tbl_livraison.livraison_date_facture,
'%d/%m/%Y'
) AS dateFacture,
IFNULL(
tbl_fg.SZMARQUE,
tbl_fgp.SZMARQUE
) AS SZMARQUE,
IFNULL(
tbl_fg.SZMODELE,
tbl_fgp.SZMODELE
) AS SZMODELE
FROM
`tbl_dv_fg`
LEFT JOIN `tbl_fg_plural`
ON (
tbl_dv_fg.num_cmde = tbl_fg_plural.num_cmde
AND tbl_dv_fg.affaire_id = tbl_fg_plural.affaire_id
)
LEFT JOIN `tbl_fg`
ON (
tbl_dv_fg.num_cmde = tbl_fg.NUMCMDE
AND tbl_dv_fg.affaire_id = tbl_fg.user_rrf
)
LEFT JOIN `tbl_fg` `tbl_fgp`
ON (
tbl_fg_plural.user_id = tbl_fgp.user_id
AND tbl_fg_plural.fg_id = tbl_fgp.id
)
LEFT JOIN `tbl_pilotage`
ON (
tbl_dv_fg.NUM_CMDE = tbl_pilotage.num_cde_sed
AND tbl_dv_fg.AFFAIRE_ID = tbl_pilotage.rrf
)
LEFT JOIN `tbl_user`
ON (tbl_dv_fg.id_user = tbl_user.id)
LEFT JOIN `tbl_livraison`
ON (
tbl_pilotage.chassis = tbl_livraison.livraison_vin
)
LEFT JOIN `ged_tbl_document_dv` `dv_document_1`
ON (
tbl_dv_fg.id = dv_document_1.dv_id
)
LEFT JOIN `ged_tbl_document_type` `document_type_1`
ON (
dv_document_1.document_type_id = document_type_1.document_type_id
)
LEFT JOIN `ged_tbl_document_dv` `dv_document_2`
ON (
tbl_dv_fg.id = dv_document_2.dv_id
)
LEFT JOIN `ged_tbl_document_type` `document_type_2`
ON (
dv_document_2.document_type_id = document_type_2.document_type_id
)
LEFT JOIN `ged_tbl_document_dv` `dv_document_3`
ON (
tbl_dv_fg.id = dv_document_3.dv_id
)
LEFT JOIN `ged_tbl_document_type` `document_type_3`
ON (
dv_document_3.document_type_id = document_type_3.document_type_id
)
LEFT JOIN `ged_tbl_document_dv` `dv_document_4`
ON (
tbl_dv_fg.id = dv_document_4.dv_id
)
LEFT JOIN `ged_tbl_document_type` `document_type_4`
ON (
dv_document_4.document_type_id = document_type_4.document_type_id
)
WHERE tbl_dv_fg.affaire_id = '00001154'
AND (
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
tbl_dv_fg.id_user = '811'
OR tbl_dv_fg.id_user = '789'
)
OR tbl_dv_fg.id_user = '790'
)
OR tbl_dv_fg.id_user = '791'
)
OR tbl_dv_fg.id_user = '10654'
)
OR tbl_dv_fg.id_user = '10112'
)
OR tbl_dv_fg.id_user = '10246'
)
OR tbl_dv_fg.id_user = '792'
)
OR tbl_dv_fg.id_user = '11381'
)
OR tbl_dv_fg.id_user = '10152'
)
OR tbl_dv_fg.id_user = '10133'
)
OR tbl_dv_fg.id_user = '10983'
)
OR tbl_dv_fg.id_user = '10113'
)
OR tbl_dv_fg.id_user = '797'
)
OR tbl_dv_fg.id_user = '807'
)
OR tbl_dv_fg.id_user = '298'
)
OR tbl_dv_fg.id_user = '10245'
)
OR tbl_dv_fg.id_user = '11151'
)
OR tbl_dv_fg.id_user = '808'
)
OR tbl_dv_fg.id_user = '809'
)
OR tbl_dv_fg.id_user = '10763'
)
OR tbl_dv_fg.id_user = '810'
)
OR tbl_dv_fg.id_user = '11681'
)
OR tbl_dv_fg.id_user = '10502'
)
AND tbl_dv_fg.nb_action_rea != tbl_dv_fg.nb_action_total
AND dv_document_1.cond_valide = 1
AND dv_document_2.cond_valide = 1
AND dv_document_3.cond_valide = 1
AND dv_document_4.cond_valide = 1
AND (
(
(
document_type_1.document_type_id = 129
AND (
dv_document_1.document_dv_statut = 0
OR dv_document_1.document_dv_statut IS NULL
)
)
OR (
document_type_2.document_type_id = 53
AND (
dv_document_2.document_dv_statut = 0
OR dv_document_2.document_dv_statut IS NULL
)
)
)
OR (
(
document_type_3.document_type_id = 40
AND (
dv_document_3.document_dv_statut = 0
OR dv_document_3.document_dv_statut IS NULL
)
)
OR (
document_type_4.document_type_id = 138
AND (
dv_document_4.document_dv_statut = 0
OR dv_document_4.document_dv_statut IS NULL
)
)
)
)
AND tbl_dv_fg.deleted_at IS NULL
Explique a afirmação:
id select_type table type possible_keys key key_len ref rows Extra
------ ----------- --------------- ------ ------------------------------------------- --------------- ------- --------------------------------------------------------- ------ ------------------------------------
1 SIMPLE tbl_dv_fg range PRIMARY,tbl_dv_fg_FI_1,affaire_id tbl_dv_fg_FI_1 4 (NULL) 10426 Using where; Using temporary
1 SIMPLE tbl_fg_plural eq_ref numcmde numcmde 178 manperf.tbl_dv_fg.num_cmde,manperf.tbl_dv_fg.affaire_id 1 Using index
1 SIMPLE tbl_fg ref commande,idx_NUMCMDE,idx_numcmde_user_rrf commande 180 manperf.tbl_dv_fg.affaire_id,manperf.tbl_dv_fg.num_cmde 1
1 SIMPLE tbl_fgp eq_ref PRIMARY,idx_fg_multi3 PRIMARY 8 manperf.tbl_fg_plural.user_id,manperf.tbl_fg_plural.fg_id 1
1 SIMPLE tbl_pilotage ref idx_num_cde_sed,idx_rrf,idx_num_cde_sed_rrf idx_num_cde_sed 20 manperf.tbl_dv_fg.num_cmde 1
1 SIMPLE tbl_user eq_ref PRIMARY PRIMARY 4 manperf.tbl_dv_fg.id_user 1
1 SIMPLE tbl_livraison ref vin vin 57 manperf.tbl_pilotage.chassis 1
1 SIMPLE dv_document_1 ref Unique,idx_statut_dv Unique 4 manperf.tbl_dv_fg.id 75 Using where; Distinct
1 SIMPLE document_type_1 eq_ref PRIMARY PRIMARY 4 manperf.dv_document_1.document_type_id 1 Using index; Distinct
1 SIMPLE dv_document_2 ref Unique,idx_statut_dv Unique 4 manperf.dv_document_1.dv_id 75 Using where; Distinct
1 SIMPLE document_type_2 eq_ref PRIMARY PRIMARY 4 manperf.dv_document_2.document_type_id 1 Using index; Distinct
1 SIMPLE dv_document_3 ref Unique,idx_statut_dv Unique 4 manperf.dv_document_2.dv_id 75 Using where; Distinct
1 SIMPLE document_type_3 eq_ref PRIMARY PRIMARY 4 manperf.dv_document_3.document_type_id 1 Using index; Distinct
1 SIMPLE dv_document_4 ref Unique,idx_statut_dv Unique 4 manperf.tbl_dv_fg.id 75 Using where; Distinct
1 SIMPLE document_type_4 eq_ref PRIMARY PRIMARY 4 manperf.dv_document_4.document_type_id 1 Using where; Using index; Distinct