Manse提出的问题 -dba

Manse

Asked: 2016-09-24 12:44:44 +0800 CST

删除数据库恢复

1

我需要解决一个问题。我删除了一个数据库。我已经恢复了*.frm和*.ibd文件。

我还没有将它们复制到 mysql 文件夹中 - 我可以只将文件复制回去并恢复服务器吗？

ibdata1，ib_logfile0并且在我惊慌ib_logfile1地退出服务器时仍然完好无损。kill -9

最好的方法是什么——我还没有尝试过任何东西，因为我担心我可能会丢失数据（如果它还没有丢失的话）。

Manse

Asked: 2016-07-28 01:55:26 +0800 CST

SQL 服务器死锁

2

我正在运行 SQL Server 2012 Service Pack 1 + 非安全更新 (KB2793634)。我经常遇到死锁，99% 的时间都是使用相同的 2 个查询。我怎样才能防止这种僵局发生？

这是死锁的详细信息：

<deadlock-list>
 <deadlock victim="process578a88188">
  <process-list>
   <process id="process578a88188" taskpriority="0" logused="2036" waitresource="KEY: 6:72057594089963520 (6533c2597184)" waittime="2954" ownerId="28095456501" transactionname="user_transaction" lasttranstarted="2016-07-20T13:30:16.490" XDES="0x38670a6c8" lockMode="X" schedulerid="3" kpid="5536" status="suspended" spid="124" sbid="1" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-07-20T13:30:16.627" lastbatchcompleted="2016-07-20T13:30:16.610" lastattention="1900-01-01T00:00:00.610" clientapp="PHP" hostname="WL-DC-A-QUEUE01" hostpid="3208" loginname="simpro" isolationlevel="read committed (2)" xactid="28095456501" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="52" sqlhandle="0x02000000c1ad4b2a727cb2c9f7ba407e398d53c0824708850000000000000000000000000000000000000000">
UPDATE workflow_request SET updated = @P1 WHERE id = @P2     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@P1 nvarchar(26),@P2 int)UPDATE workflow_request SET updated = @P1 WHERE id = @P2    </inputbuf>
   </process>
   <process id="process61e0a9868" taskpriority="0" logused="8484" waitresource="KEY: 6:72057594089963520 (49806bcce5bc)" waittime="3032" ownerId="28095456347" transactionname="user_transaction" lasttranstarted="2016-07-20T13:30:16.407" XDES="0x46e5c03a8" lockMode="U" schedulerid="4" kpid="5896" status="suspended" spid="91" sbid="1" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-07-20T13:30:16.550" lastbatchcompleted="2016-07-20T13:30:16.547" lastattention="1900-01-01T00:00:00.547" clientapp="PHP" hostname="WL-DC-A-QUEUE01" hostpid="2664" loginname="simpro" isolationlevel="read committed (2)" xactid="28095456347" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="108" sqlhandle="0x0200000029bc6522620287ccc7de73b3135f3b2e2fc578260000000000000000000000000000000000000000">
UPDATE workflow_request SET current_state_id = @P1, status_id = @P2, updated_by_id = @P3, updated = @P4 WHERE id = @P5     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@P1 char(1),@P2 int,@P3 int,@P4 nvarchar(26),@P5 int)UPDATE workflow_request SET current_state_id = @P1, status_id = @P2, updated_by_id = @P3, updated = @P4 WHERE id = @P5    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594089963520" dbid="6" objectname="simprov4.dbo.workflow_request" indexname="PK__workflow__3213E83F498EEC8D" id="lock1474033780" mode="X" associatedObjectId="72057594089963520">
    <owner-list>
     <owner id="process61e0a9868" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process578a88188" mode="X" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594089963520" dbid="6" objectname="simprov4.dbo.workflow_request" indexname="PK__workflow__3213E83F498EEC8D" id="lock4e91ab400" mode="X" associatedObjectId="72057594089963520">
    <owner-list>
     <owner id="process578a88188" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process61e0a9868" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

这是表创建语句：

CREATE TABLE [dbo].[workflow_request](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [requester_id] [int] NULL,
    [sim_id] [int] NULL,
    [action_id] [int] NULL,
    [activate_data_id] [int] NULL,
    [cancel_data_id] [int] NULL,
    [allocate_ip_data_id] [int] NULL,
    [transition_id] [int] NULL,
    [current_state_id] [int] NULL,
    [status_id] [int] NULL,
    [locked_by_id] [int] NULL,
    [customer_request_batch_id] [int] NULL,
    [batch_id] [int] NULL,
    [skip] [bit] NULL,
    [network_reference] [nvarchar](255) NULL,
    [locked] [bit] NOT NULL,
    [last_result] [bit] NULL,
    [last_message] [varchar](max) NULL,
    [retry] [bit] NOT NULL,
    [retry_count] [smallint] NOT NULL,
    [created] [datetime2](6) NOT NULL,
    [updated] [datetime2](6) NOT NULL,
    [sim_swap_data_id] [int] NULL,
    [enable_full_bar_data_id] [int] NULL,
    [disable_full_bar_data_id] [int] NULL,
    [edit_sims_data_id] [int] NULL,
    [enable_roaming_data_id] [int] NULL,
    [disable_roaming_data_id] [int] NULL,
    [sim_refresh_data_id] [int] NULL,
    [suspend_data_id] [int] NULL,
    [send_to_network] [bit] NULL,
    [spreference] [nvarchar](50) NULL,
    [updated_by_id] [int] NULL,
    [skip_creation_email] [bit] NULL,
    [skip_completion_email] [bit] NULL,
    [auto_complete] [bit] NULL,
    [send_to_customer] [bit] NULL,
    [manual_override] [bit] NULL,
    [ignore_failed_flag] [bit] NULL,
    [failed_status_id] [int] NULL,
    [email_to] [nvarchar](255) NULL,
    [delete_sims_data_id] [int] NULL,
    [undelete_sims_data_id] [int] NULL,
    [update_apn_data_id] [int] NULL,
PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F161A0C43] FOREIGN KEY([customer_request_batch_id])
REFERENCES [dbo].[workflow_customer_request_batch] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F161A0C43]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F4355328A] FOREIGN KEY([update_apn_data_id])
REFERENCES [dbo].[workflow_update_apn_data] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F4355328A]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F44F7BE9D] FOREIGN KEY([suspend_data_id])
REFERENCES [dbo].[workflow_suspend_data] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F44F7BE9D]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F4AA4DEB] FOREIGN KEY([sim_refresh_data_id])
REFERENCES [dbo].[workflow_sim_refresh_data] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F4AA4DEB]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F4E0AB869] FOREIGN KEY([disable_roaming_data_id])
REFERENCES [dbo].[workflow_disable_roaming_data] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F4E0AB869]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F5131BD6D] FOREIGN KEY([cancel_data_id])
REFERENCES [dbo].[workflow_cancel_data] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F5131BD6D]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F54A7A1D9] FOREIGN KEY([allocate_ip_data_id])
REFERENCES [dbo].[workflow_allocate_ip_data] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F54A7A1D9]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F575024C3] FOREIGN KEY([sim_swap_data_id])
REFERENCES [dbo].[workflow_sim_swap_data] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F575024C3]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F5E711585] FOREIGN KEY([failed_status_id])
REFERENCES [dbo].[workflow_request_failed_status] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F5E711585]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F6BF700BD] FOREIGN KEY([status_id])
REFERENCES [dbo].[workflow_request_status] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F6BF700BD]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F7A88E00] FOREIGN KEY([locked_by_id])
REFERENCES [dbo].[core_user] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F7A88E00]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F896DBBDE] FOREIGN KEY([updated_by_id])
REFERENCES [dbo].[core_user] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F896DBBDE]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F8BF1A064] FOREIGN KEY([transition_id])
REFERENCES [dbo].[workflow_transition] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F8BF1A064]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F9527B049] FOREIGN KEY([enable_roaming_data_id])
REFERENCES [dbo].[workflow_roaming_data] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F9527B049]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F97C93AED] FOREIGN KEY([enable_full_bar_data_id])
REFERENCES [dbo].[workflow_enable_full_bar_data] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F97C93AED]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F98A046EB] FOREIGN KEY([current_state_id])
REFERENCES [dbo].[workflow_state] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F98A046EB]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192F9D32F035] FOREIGN KEY([action_id])
REFERENCES [dbo].[workflow_action] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192F9D32F035]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192FAC7C372D] FOREIGN KEY([disable_full_bar_data_id])
REFERENCES [dbo].[workflow_disable_full_bar_data] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192FAC7C372D]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192FC79087FC] FOREIGN KEY([activate_data_id])
REFERENCES [dbo].[workflow_activate_data] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192FC79087FC]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192FE7793046] FOREIGN KEY([delete_sims_data_id])
REFERENCES [dbo].[workflow_delete_sims_data] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192FE7793046]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192FED442CF4] FOREIGN KEY([requester_id])
REFERENCES [dbo].[core_user] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192FED442CF4]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192FEF677A38] FOREIGN KEY([undelete_sims_data_id])
REFERENCES [dbo].[workflow_undelete_sims_data] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192FEF677A38]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192FF81AF80C] FOREIGN KEY([sim_id])
REFERENCES [dbo].[core_sim] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192FF81AF80C]
GO

ALTER TABLE [dbo].[workflow_request]  WITH CHECK ADD  CONSTRAINT [FK_39DB192FF94413E8] FOREIGN KEY([edit_sims_data_id])
REFERENCES [dbo].[workflow_edit_sims_data] ([id])
GO

ALTER TABLE [dbo].[workflow_request] CHECK CONSTRAINT [FK_39DB192FF94413E8]
GO

每个查询的执行计划 -第一个查询 UPDATE workflow_request SET updated = @P1 WHERE id = @P2和第二个查询 UPDATE workflow_request SET current_state_id = @P1, status_id = @P2, updated_by_id = @P3, updated = @P4 WHERE id = @P5

Manse

Asked: 2016-06-11 06:18:31 +0800 CST

我在哪里更改 mysql 集群上的 my.cnf

2

我正在尝试更改max_allowed_packetmysql 集群（版本 7.4.8）上的设置 - 我有 5 个节点

2x SQL 节点
2x 数据节点
1x 管理节点

我已经更新了my.cnf所有服务器上的 ->

[mysqld]
datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
user=mysql
ndbcluster
ndb-connectstring=manager-db
default_storage_engine=ndbcluster
port=3306
max_allowed_packet=1073741824

但show variables like '%max_allowed_packet%'仍然返回524288000

我究竟做错了什么？我需要在哪里设置它？

删除数据库恢复

SQL 服务器死锁

我在哪里更改 mysql 集群上的 my.cnf

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

Manse's questions