主页 / dba / 问题

问题[crash](dba)

Martin Hope
Heinzi
Asked: 2018-07-18 05:03:29 +0800 CST
  • 23

我们有运行 SQL Server（2008 SP4 和 2016 SP1）的 PC，它们经常断电。显然，这有时会导致 SQL Server 数据库（索引）损坏，我们需要在之后进行恢复。

我知道 SQL Server 不是为这种情况而设计的，正确的解决方案是解决掉电的原因（如果您好奇，请参阅下文）。尽管如此，我可以设置 SQL Server 中的任何调整选项来降低断电时数据库损坏的风险吗？

背景：“PC”是安装在叉车上的 Windows 平板电脑。当用户关闭叉车时，平板电脑就会断电。我们曾尝试教用户在关闭叉车之前正确关闭 Windows，但失败了（可能是因为大多数时候只是将其关闭“有效”）。我们目前还在研究其他选项，例如添加一个 UPS，该 UPS 会向平板电脑发出信号以在断电时关闭。

sql-server crash
Martin Hope
leandro2219
Asked: 2018-06-19 06:04:47 +0800 CST
  • 0

我在 Azure 上有一台带有 MySQL 数据库的服务器，我们在那里有两个应用程序，有时我们会在代码中遇到此错误：

Warning: mysqli::query(): (HY000/1194): 
Table 'noticia' is marked as crashed and should be repaired 
in D:\home\site\wwwroot\classes\ConnectionFactory.php on line 122

一些问题：

  1. 什么样的事情会导致这个错误？
  2. 我在哪里可以找到日志以获得错误原因？
  3. 这可能是 PHP 问题吗？

这太奇怪了，因为多年来我一直在使用同一个 ConnectionFactory 类连接到数据库，这是我们第一次遇到这个错误。

mysql crash
Martin Hope
Delirium
Asked: 2016-06-28 00:44:48 +0800 CST
  • 1

MySQL 不断随机崩溃 - 错误日志中的最近错误说明了这一点

160626 21:50:42 [Warning] IP address '113.108.21.*' could not be resolved: Name or service not known
160626 22:42:57 [Warning] IP address '124.172.137.*' could not be resolved: Temporary failure in name resolution
160626 22:43:10 [Warning] IP address '124.172.137.*' could not be resolved: Temporary failure in name resolution
160626 22:43:25 [Warning] IP address '124.172.137.*' could not be resolved: Temporary failure in name resolution
160626 22:43:41 [Warning] IP address '124.172.137.*' could not be resolved: Temporary failure in name resolution
160626 22:43:54 [Warning] IP address '124.172.137.*' could not be resolved: Temporary failure in name resolution
160626 22:44:09 [Warning] IP address '124.172.137.*' could not be resolved: Temporary failure in name resolution
160627  0:45:24 [Warning] IP address '117.79.84.*' could not be resolved: Name or service not known

160624  7:00:31 [Warning] IP address '58.221.62.*' could not be resolved: Name or service not known
160624  7:05:14 [Note] /usr/sbin/mysqld: Normal shutdown

160624  7:05:14 [Note] Event Scheduler: Purging the queue. 0 events
160624  7:05:16 [Warning] /usr/sbin/mysqld: Forcing close of thread 16955  user: 'amember'

160624  7:05:16 [Warning] /usr/sbin/mysqld: Forcing close of thread 16953  user: 'amember'

160624  7:05:16  InnoDB: Starting shutdown...
160624  7:05:22  InnoDB: Shutdown completed; log sequence number 195606046122
160624  7:05:22 [Note] /usr/sbin/mysqld: Shutdown complete

160624 07:05:22 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
160624 07:05:23 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

在 my.cnf 中有这些通道，它可以以某种方式解决这个问题？

key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 16M

服务器CPUs具有20cores和258450MBRAM

mysql crash
Martin Hope
Oscar Caraballo
Asked: 2016-04-13 09:06:22 +0800 CST
  • 3

我在 Oracle 实例上执行日常管理任务，但无法连接到它。

我继续执行以下操作以进行诊断：

  1. 我检查了内存中的 Oracle 进程，发现实例已关闭。
  2. 我尝试了startup该实例，但它从未响应启动命令。它只是停滞不前。无论我等待多长时间，都没有输出或反馈。只有一个kill -9可以让我摆脱它。

  3. 我查看了警报日志，最后一条消息是 3 天前：

    "DBW0: terminating instance due to error 472
Instance terminated by DBW0, pid = 14952"

问题

  • 什么会导致 BDW 终止实例？
  • 为什么我在尝试启动实例时没有收到任何反馈，无论是在 sqlplus 控制台还是警报日志中。

当我运行 strace 时，我得到了这个：

错误：无法打开 /dev/log。

我在 SunOS 5.8 Generic_117350-08 sun4u sparc SUNW,Sun-Fire 上运行 Oracle。RDBMS 版本为 9.2.0.8.0

编辑：

我听从了@balazs-papp 和@jsapkota 关于跑步的建议truss：

当我在 sqlplus 上运行 truss 时，我尝试启动实例，得到以下输出：

read(0, " s t a r t u p\n", 1024)               = 8
write(9, "\0 U\0\006\0\0\0\0\0038A".., 85)      = 85
read(10, 0x10029A536, 2064)     (sleeping...)
signotifywait()                 (sleeping...)
lwp_cond_wait(0xFFFFFFFF7D62B058, 0xFFFFFFFF7D62B068, 0xFFFFFFFF7D621C80) (sleeping...)
lwp_cond_wait(0xFFFFFFFF7D62B058, 0xFFFFFFFF7D62B068, 0xFFFFFFFF7D621C80) (sleeping...)
door_return(0x00000000, 0, 0x00000000, 0) (sleeping...)
  • 我如何解释这个输出？这对我来说完全是神秘的。
oracle crash
Martin Hope
Tola Odejayi
Asked: 2013-12-01 12:13:31 +0800 CST
  • 5

我遇到了一个问题，我的 MySQL 服务器无缘无故崩溃并且必须重新启动。

本质上，错误日志具有如下条目：

131128 12:16:35 mysqld_safe Number of processes running now: 0
131128 12:16:35 mysqld_safe mysqld restarted
131128 12:16:36 [Note] Plugin 'FEDERATED' is disabled.
131128 12:16:37 InnoDB: The InnoDB memory heap is disabled
131128 12:16:37 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131128 12:16:37 InnoDB: Compressed tables use zlib 1.2.3
131128 12:16:37 InnoDB: Using Linux native AIO
131128 12:16:37 InnoDB: Initializing buffer pool, size = 768.0M
131128 12:16:37 InnoDB: Completed initialization of buffer pool
131128 12:16:37 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 5922117901
131128 12:16:37  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 5922118503
131128 12:16:41  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
131128 12:16:41  InnoDB: Waiting for the background threads to start
131128 12:16:42 InnoDB: 5.5.32 started; log sequence number 5922118503
131128 12:16:42 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
131128 12:16:42 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
131128 12:16:42 [Note] Server socket created on IP: '0.0.0.0'.
131128 12:16:43 [Note] Event Scheduler: Loaded 0 events
131128 12:16:43 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.32-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
131128 13:26:34 mysqld_safe Number of processes running now: 0
131128 13:26:34 mysqld_safe mysqld restarted
131128 13:26:35 [Note] Plugin 'FEDERATED' is disabled.
131128 13:26:35 InnoDB: The InnoDB memory heap is disabled
131128 13:26:35 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131128 13:26:35 InnoDB: Compressed tables use zlib 1.2.3
131128 13:26:35 InnoDB: Using Linux native AIO
131128 13:26:35 InnoDB: Initializing buffer pool, size = 768.0M
131128 13:26:35 InnoDB: Completed initialization of buffer pool
131128 13:26:35 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 5922607951
131128 13:26:35  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 5922608207
131128 13:26:39  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
131128 13:26:41 InnoDB: 5.5.32 started; log sequence number 5922608207
131128 13:26:41 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
131128 13:26:41 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
131128 13:26:41 [Note] Server socket created on IP: '0.0.0.0'.
131128 13:26:42 [Note] Event Scheduler: Loaded 0 events
131128 13:26:42 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.32-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
131128 19:17:10 mysqld_safe Number of processes running now: 0
131128 19:17:10 mysqld_safe mysqld restarted
131128 19:17:10 [Note] Plugin 'FEDERATED' is disabled.
131128 19:17:10 InnoDB: The InnoDB memory heap is disabled
131128 19:17:10 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131128 19:17:10 InnoDB: Compressed tables use zlib 1.2.3
131128 19:17:10 InnoDB: Using Linux native AIO
131128 19:17:10 InnoDB: Initializing buffer pool, size = 768.0M
131128 19:17:10 InnoDB: Completed initialization of buffer pool
131128 19:17:10 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
131128 19:17:10  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
131128 19:17:22  InnoDB: Waiting for the background threads to start
131128 19:17:23 InnoDB: 5.5.32 started; log sequence number 5923508470
131128 19:17:23 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
131128 19:17:23 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
131128 19:17:23 [Note] Server socket created on IP: '0.0.0.0'.
131128 19:17:23 [Note] Event Scheduler: Loaded 0 events
131128 19:17:23 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.32-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
131128 23:48:32 mysqld_safe Number of processes running now: 0
131128 23:48:32 mysqld_safe mysqld restarted
131128 23:48:33 [Note] Plugin 'FEDERATED' is disabled.
131128 23:48:33 InnoDB: The InnoDB memory heap is disabled
131128 23:48:33 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131128 23:48:33 InnoDB: Compressed tables use zlib 1.2.3
131128 23:48:33 InnoDB: Using Linux native AIO
131128 23:48:33 InnoDB: Initializing buffer pool, size = 768.0M
131128 23:48:33 InnoDB: Completed initialization of buffer pool
131128 23:48:33 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 5925065422
131128 23:48:33  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 5925065795
131128 23:48:38  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
131128 23:48:39  InnoDB: Waiting for the background threads to start
131128 23:48:40 InnoDB: 5.5.32 started; log sequence number 5925065795
131128 23:48:40 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
131128 23:48:40 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
131128 23:48:40 [Note] Server socket created on IP: '0.0.0.0'.
131128 23:48:41 [Note] Event Scheduler: Loaded 0 events
131128 23:48:41 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.32-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
131129 02:06:39 mysqld_safe Number of processes running now: 0
131129 02:06:39 mysqld_safe mysqld restarted
131129  2:06:44 [Note] Plugin 'FEDERATED' is disabled.
131129  2:06:45 InnoDB: The InnoDB memory heap is disabled
131129  2:06:45 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131129  2:06:45 InnoDB: Compressed tables use zlib 1.2.3
131129  2:06:45 InnoDB: Using Linux native AIO
131129  2:06:45 InnoDB: Initializing buffer pool, size = 768.0M
131129  2:06:45 InnoDB: Completed initialization of buffer pool
131129  2:06:45 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 5925464870
131129  2:06:45  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 5925465752
131129  2:06:49  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
131129  2:06:52  InnoDB: Waiting for the background threads to start
131129  2:06:53 InnoDB: 5.5.32 started; log sequence number 5925465752
131129  2:06:54 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
131129  2:06:54 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
131129  2:06:54 [Note] Server socket created on IP: '0.0.0.0'.
131129  2:06:55 [Note] Event Scheduler: Loaded 0 events
131129  2:06:55 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.32-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
131129 06:23:13 mysqld_safe Number of processes running now: 0
131129 06:23:14 mysqld_safe mysqld restarted
131129  6:23:30 [Note] Plugin 'FEDERATED' is disabled.
131129  6:23:33 InnoDB: The InnoDB memory heap is disabled
131129  6:23:33 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131129  6:23:33 InnoDB: Compressed tables use zlib 1.2.3
131129  6:23:33 InnoDB: Using Linux native AIO
131129  6:23:33 InnoDB: Initializing buffer pool, size = 768.0M
131129  6:23:33 InnoDB: Completed initialization of buffer pool
131129  6:23:34 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 5926929016
131129  6:23:34  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 5926968418
131129  6:23:38  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
131129  6:23:39  InnoDB: Waiting for the background threads to start
131129  6:23:40 InnoDB: 5.5.32 started; log sequence number 5926968418
131129  6:23:40 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
131129  6:23:40 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
131129  6:23:40 [Note] Server socket created on IP: '0.0.0.0'.
131129  6:23:41 [Note] Event Scheduler: Loaded 0 events
131129  6:23:41 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.32-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
131129 06:28:05 mysqld_safe Number of processes running now: 0
131129 06:28:05 mysqld_safe mysqld restarted
131129  6:28:06 [Note] Plugin 'FEDERATED' is disabled.
131129  6:28:06 InnoDB: The InnoDB memory heap is disabled
131129  6:28:06 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131129  6:28:06 InnoDB: Compressed tables use zlib 1.2.3
131129  6:28:06 InnoDB: Using Linux native AIO
131129  6:28:06 InnoDB: Initializing buffer pool, size = 768.0M
131129  6:28:06 InnoDB: Completed initialization of buffer pool
131129  6:28:06 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
131129  6:28:06  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
131129  6:28:07  InnoDB: Waiting for the background threads to start
131129  6:28:08 InnoDB: 5.5.32 started; log sequence number 5926985672
131129  6:28:08 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
131129  6:28:08 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
131129  6:28:08 [Note] Server socket created on IP: '0.0.0.0'.
131129  6:28:11 [Note] Event Scheduler: Loaded 0 events
131129  6:28:11 [Note] /usr/sbin/mysqld: ready for connections.

在互联网上阅读有关此问题的信息，我发现这可能是因为其中一个服务器数据库中的数据已损坏。

所以我这样做了：

  • 添加innodb_force_recovery = 4到/etc/my.cnf
  • 重启了mysql服务
  • 使用转储数据库中的数据mysqldump
  • 停止了mysql服务
  • 备份现有的 InnoDB 日志/数据文件
  • 从innodb_force_recovery = 4_/etc/my.cnf
  • 重启了mysql服务
  • 使用之前创建的转储重新创建数据库并向其添加表对象和数据

但崩溃仍在继续。

这是我的 /etc/my.cnf 文件的副本：

[mysqld]
tmp_table_size=192M
query_cache_limit=16M
join_buffer_size=64M
max_heap_table_size=192M
query_cache_size=64M
max_allowed_packet=256M
sort_buffer_size=64M
myisam_sort_buffer_size=64M
key_buffer=32M
read_buffer_size=64M
sort_buffer_size=64M

open_files_limit=2090
max_connections=32
wait_timeout=1800
table_cache=6400
thread_cache_size=16
max_connect_errors=999999
query_cache_type=1
max_user_connections=200
interactive_timeout=180
connect_timeout=30
net_read_timeout = 600
net_write_timeout = 600
socket="/var/lib/mysql/mysql.sock"
port=3306
default-storage-engine=InnoDB

log-error="/var/lib/mysql/error.log"
slow_query_log_file="/var/lib/mysql/slow-query.log"
slow_query_log=1
long_query_time=5
general_log_file="/var/lib/mysql/general.log"
general_log=1

innodb_buffer_pool_size=768M
innodb_log_buffer_size=32M
innodb_log_file_size=192M
innodb_additional_mem_pool_size=20M
innodb_data_file_path="ibdata1:32M:autoextend"
innodb_data_home_dir="/var/lib/mysql/"
innodb_fast_shutdown=0
innodb_thread_concurrency=8
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=0
;innodb_force_recovery=4

[client]
socket="/var/lib/mysql/mysql.sock"
port=3306

我启用了常规日志，但在崩溃时我没有看到任何特殊查询始终在运行。

有没有人有任何进一步的建议？有人告诉我这可能是硬件问题，但我的托管服务提供商说他们看不到任何问题。

mysql crash