我们的应用程序有些缓慢。当我检查时，我发现一个特定的表被锁定，所以所有事务都被拒绝了Lock wait timeout exceeded。当我这样做时，SHOW PROCESSLIST;我希望看到一些带有Waiting for table level lock状态的进程，但它什么也没显示！？只有在同一张表上的一些查询 INSERT/UPDATE 在超时前恰好需要 120 秒，而其他进程则处于睡眠状态（见图）：
我有一个带有单个数据库的活动主/从复制（Mariadb 10.2）（参见场景 1）
我决定添加 IMPORT 数据库以填充 MASTER 数据库，如方案 2 中所述。但是，SLAVE 实例关闭并且“未知数据库导入”错误被追溯。
为了克服这个问题，我在 SLAVE 服务器（导入）中创建了一个同名的数据库，并且错误消失了（参见场景 3）。
但是，创建的数据库（从属导入）没有表（故意），因此为了确保数据被插入到我测试的从属中，我在MASTER 数据库中创建了一个测试表，并且我确实从Master 的导入数据库中插入了数据。
结果，即使从库的导入库没有表，从库中的数据也很明显是可用的。
所以基本上，我们只需要在从属部分中使用相同的数据库名称就可以在不需要表的情况下工作。
您能否向我解释一下它是如何工作的？如果它是基于行的格式，为什么我们需要在从站中使用相同的数据库？
我正在尝试使用 2 个节点安装 MariaDB Galera Cluster：
节点 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
节点 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
第一个节点启动时没有错误：
Variable_name Value
-------------------- ---------
wsrep_cluster_size 1
wsrep_cluster_status Primary
wsrep_connected ON
wsrep_ready ON
但是当我启动 2n 节点时，我得到了这个：
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.
更新 ：
此错误的来源是由于 rsync 程序已在使用中，因此解决方案是终止它：
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
我尝试重新启动第二个节点： systemctl start mariadb
在第一个节点中： 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
然而，又出现了一个错误：
# 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.
任何想法来解决这个问题？
我正在 Mysql 中开发一个 DB-spacial 项目，我试图对一组形状执行联合操作。我正在寻找类似于 SQL Server 函数UnionAggregate或 PostGIS 空间聚合函数的东西ST_Union
例如（在 SQL SERVER 中）：
SELECT
geometry::STGeomFromWKB(Shape,27582),
area_code
FROM area_table
WHERE area_code='xxxxxxx';
ST_GeomFromWKB 在 Mysql 中做同样的工作。
在第二部分和UnionAggregate函数 + group by 中：
select
dbo.UnionAggregate((geometry::STGeomFromWKB(Shape,27582)).MakeValid()),
area_code
FROM area_table
WHERE area_code='xxxxxxx'
GROUP BY area_code;
如何在 MySQL 中执行类似的操作（聚合+分组）？
我遇到了一些关于使用 Mysql 服务器执行某些存储过程的实际问题。有时，我的一个过程在外部调用（来自应用程序）期间未执行（或可能停止），所以我想知道：
- Mysql有没有可能自动停止并杀死进程？
- 如果是真的，是什么指标和因素让Mysql选择了它的牺牲品？
- 是否有替代方法来设置运行查询的优先级列表？
谢谢。
我正在寻找一种将数据从文本文件导入 MySQL 数据库的方法，该文本文件过去常常通过 BCP 和 FORMATFILEs 选项导入到 SQL SERVER 数据库中，例如：
BULK INSERT [MyTable] FROM 'myFolder\.txt'
WITH (
FORMATFILE = 'myFolder\MyTable.fmt',
ROWS_PER_BATCH = 5000,
MAXERRORS = 5000,
CODEPAGE = 'ACP',
ERRORFILE = 'myFolder\errors.txt'
)
我的表格.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
源文件中的字段（或列）没有分开，唯一不同的数据是每列的字符数，如格式文件 MyTable.fmt 中所述，例如：
- Field1 左边的前 3 个字符
- Field2 左边的前（3+） 7 个字符
- Field3 左边的第（3+7+） 3个字符
- Field4 左边的第(3+7+3+) 2个字符
任何想法如何在 MySQL 中进行？
我需要获取从 MySQL 返回的整个错误消息并将其分配给@error_string：
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
SET @error_string = 'SQLEXCEPTION_ERROR_MESSAGE'; <-- here
END;
有没有替代方案，谢谢。
嗨，我花了好几个小时来使用索引来加速这个查询，但它没用..我试图通过消除“使用临时”和只使用索引来优化。我想知道是否有另一种方法可以加快此查询的速度？？
询问 ：
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
解释声明：
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