精华内容
下载资源
问答
  • <p>I used the following code to extract tables from a pdf file using the camelot module in python: <p>import camelot tables = camelot.read_pdf('report.pdf',pages='1-15') print ...
  • 接着上篇中遇到的mysql子查询,在问题的诊断中,丹臣注意到一个较为严重的问题,就是我们生产库中全部的数据库访问请求都处于Waiting for tables的状态,在将大查询kill掉后,所有的请求恢复正常;简单的理解为大...

    接着上篇中遇到的mysql子查询,在问题的诊断中,丹臣注意到一个较为严重的问题,就是我们生产库中全部的数据库访问请求都处于Waiting for tables的状态,在将大查询kill掉后,所有的请求恢复正常;简单的理解为大查询阻塞了其他访问请求,但是这个理论是不可信,如果阻塞该表的DML还可以理解,但是把该数据库上的所有请求都阻塞了,这还是说不通的。那么我们就来看看所有的请求处于Waiting for tables这个状态是什么原因导致的:

    The thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. However, to reopen the table, it must wait until all other threads have closed the table in question.

    This notification takes place if another thread has used FLUSH TABLES or one of the following statements on the table in question: FLUSH TABLES tbl_nameALTER TABLERENAME TABLEREPAIR TABLEANALYZE TABLE, orOPTIMIZE TABLE.

    从文档上的解释来看,是主库做了一个flush tables的操作,导致所有的表都需要打开,但是由于在10-07号放假,应该不会有人在主库上执行flush tables,而且从日志中可以看到:

    1044 system user Connect 27406 Flushing tables FLUSH TABLES

    所以可以判断是系统自己执行了这个操作,那么既然不是主库上执行而来,那么这个flush tables操作是从slave上复制过来的(M-M结构),

    那么备库的什么操作会有flush tables,真的百思不其解,我们备库在6点之前做的是什么,后端应用的dump?还是数据库的备份?是不是xtrabackup,很有可能是xtrabackup在备份的时候做的fulsh tables,查看备份脚本,应该轮到mysqldump做逻辑备份操作了,并不是xtrabackup,检查了mysqldump的备份脚本,脚本里:

    -uroot -P$port –protocol=tcp –single-transaction –master-data=2是这样的

    Single-transaction这个选项是加上了的,希望再一次被打破 >_<

    最后想还是想到到官网上去看看,mysqldump+flush tables是否有bug,

    唉,搜索了一下果然发现了蹊跷:

    http://bugs.mysql.com/bug.php?id=35157

    When using the –master-data option with mysqldump, mysqldump uses a FLUSH TABLES command. However, this statement got replicated to the slave(s), which caused the slave(s) to block unnecessarily while the FLUSH tables command completed.

    在5.0存在的bug很好的解释了这个问题,在mysqldump加入了–master-data就会将flush tables记录到binglog中,然后在被同步到主库,主库执行binglog后,由于有一个大查询正在

    执行,这个子查询由于执行了很长时间,阻塞了flush tables的操作,最后导致了雪崩,所有的请求都被阻塞:

    The thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. However, to reopen the table,

    it must wait until all other threads have closed the table in question.

    综合上篇的所写,Mysqldump的在5.0的bug加上数据库的低效子查询构成这次故障的原因。

    峰回路转,山穷水尽,哈哈

    展开全文
  • Error while waiting for Mnesia tables: {failed_waiting_for_tables,['rabbit-2.rabbitmq-headless.mhill.svc.cluster.local','rabbit-1.rabbitmq-headless.mhill.svc.cluster.local','...
  • <div><p>Currently data table instances wait for the data which is not actually used by the table itself.</p><p>该提问来源于开源项目:cBioPortal/cbioportal</p></div>
  • After shut down and restart works about half the time, the other half never restarts due to the waiting error on the db.</p><p>该提问来源于开源项目:bitnami/charts</p></div>
  • From Metalink 382809.1 ...a) Following jobs will purge unwanted records in CTXSYS.DR$WAITING,CTXSYS.DR$PENDING 1.FND Rebuild Help Search Index 2.JTF Item InterMedia Index Optimizing operatio...

    From Metalink 382809.1

    Solution

    a) Following jobs will purge unwanted records in CTXSYS.DR$WAITING,CTXSYS.DR$PENDING

    1.FND Rebuild Help Search Index
    2.JTF Item InterMedia Index Optimizing operation (CRM Foundation)
    3.JTF Item InterMedia Index Sync Operation (CRM Foundation)
    4.MES InterMedia Index Optimizing operation (Oracle MarketView)
    5.MES InterMedia Index Sync Operation (Oracle MarketView)
    6.Knowledge Management Index Synchronization Program (Oracle Service)
    7.Rebuilding Intermedia Index for Task Names
    8.Synchronize JTF_NOTES_TL_C1 index

    b) Execute the following query to find out what are the indexes that still have pending rows in DR$PENDING:

    connect as ctxsys:

    select u.username, i.idx_name
    from dr$index i, dba_users u
    where u.user_id=i.idx_owner#
    and idx_id in (select pnd_cid from dr$pending);

    Synchronize those indexes manully, running:

    exec ctx_ddl.sync_index('USERNAME.INDEX');

    or u can generate sql using:

    select count(*) from ctxsys.dr$waiting
    select count(*) from ctxsys.dr$pending

    select 'exec ctx_ddl.sync_index('''||u.username||'.'||i.idx_name||''')'
    from ctxsys.dr$index i, dba_users u
    where u.user_id=i.idx_owner#
    and idx_id in (select pnd_cid from ctxsys.dr$pending);

    Reference:
    0.Metalink:382809.1 here Are Millions Of Records In CTXSYS.DR$PENDING And CTXSYS.DR$WAITING Tables
    1.Predefined Administrative Accounts CTXSYS
    http://download.oracle.com/docs/cd/B28359_01/server.111/b28337/tdpsg_user_accounts.htm#BABHBBIA
    2.Metalink:731921.1 Cannot Export Data from E-Business Suite to Excel
    3.Metalink:427964.1 Information You Should Upload When Creating an SR for File Export and Attachments

    -EOF-

    add by 2013-10-30

    CTXSYS

    The account used to administer Oracle Text. Oracle Text enables you to build text query applications and document classification applications. It provides indexing, word and theme searching, and viewing capabilities for text.

    See Oracle Text Application Developer's Guide.

    转载于:https://www.cnblogs.com/ebs-blog/archive/2010/07/19/2167318.html

    展开全文
  • MySQL在进行alter ...而且,一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态,后续对TableA的任何操作(包括读)都无法进行,因为他们也会在Opening tables的阶段进入到Waiting for tab...

    MySQL在进行alter table等DDL操作时,有时会出现Waiting for table metadata lock的等待场景。而且,一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态,后续对TableA的任何操作(包括读)都无法进行,因为他们也会在Opening tables的阶段进入到Waiting for table metadata lock的锁等待队列。如果是产品环境的核心表出现了这样的锁等待队列,就会造成灾难性的后果。

    造成alter table产生Waiting for table metadata lock的原因其实很简单,一般是以下几个简单的场景:

    场景一:长事物运行,阻塞DDL,继而阻塞所有同表的后续操作

    通过show processlist可以看到TableA上有正在进行的操作(包括读),此时alter table语句无法获取到metadata 独占锁,会进行等待。

    这是最基本的一种情形,这个和mysql 5.6中的online ddl并不冲突。一般alter table的操作过程中(见下图),在after create步骤会获取metadata 独占锁,当进行到altering table的过程时(通常是最花时间的步骤),对该表的读写都可以正常进行,这就是online ddl的表现,并不会像之前在整个alter table过程中阻塞写入。(当然,也并不是所有类型的alter操作都能online的,具体可以参见官方手册:http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html)

    处理方法: kill 掉 DDL所在的session.

    场景二:未提交事物,阻塞DDL,继而阻塞所有同表的后续操作

    通过show processlist看不到TableA上有任何操作,但实际上存在有未提交的事务,可以在 information_schema.innodb_trx中查看到。在事务没有完成之前,TableA上的锁不会释放,alter table同样获取不到metadata的独占锁。

    处理方法:通过 select * from information_schema.innodb_trx\G, 找到未提交事物的sid, 然后 kill 掉,让其回滚。

    场景三:

    通过show processlist看不到TableA上有任何操作,在information_schema.innodb_trx中也没有任何进行中的事务。这很可能是因为在一个显式的事务中,对TableA进行了一个失败的操作(比如查询了一个不存在的字段),这时事务没有开始,但是失败语句获取到的锁依然有效,没有释放。从performance_schema.events_statements_current表中可以查到失败的语句。

    官方手册上对此的说明如下:

    If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.

    也就是说除了语法错误,其他错误语句获取到的锁在这个事务提交或回滚之前,仍然不会释放掉。because the failed statement is written to the binary log and the locks protect log consistency 但是解释这一行为的原因很难理解,因为错误的语句根本不会被记录到二进制日志。

    处理方法:通过performance_schema.events_statements_current找到其sid, kill 掉该session. 也可以 kill 掉DDL所在的session.

    总之,alter table的语句是很危险的(其实他的危险其实是未提交事物或者长事务导致的),在操作之前最好确认对要操作的表没有任何进行中的操作、没有未提交事务、也没有显式事务中的报错语句。如果有alter table的维护任务,在无人监管的时候运行,最好通过lock_wait_timeout设置好超时时间,避免长时间的metedata锁等待。

    展开全文
  • MySQL在进行alter ...而且,一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态,后续对TableA的任何操作(包括读)都无法进行,因为他们也会在Opening tables的阶段进入到Waiting for tab...

    MySQL在进行alter table等DDL操作时,有时会出现Waiting for table metadata lock的等待场景。而且,一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态,后续对TableA的任何操作(包括读)都无法进行,因为他们也会在Opening tables的阶段进入到Waiting for table metadata lock的锁等待队列。如果是产品环境的核心表出现了这样的锁等待队列,就会造成灾难性的后果。

    造成alter table产生Waiting for table metadata lock的原因其实很简单,一般是以下几个简单的场景:

    场景一:长事物运行,阻塞DDL,继而阻塞所有同表的后续操作

    通过show processlist可以看到TableA上有正在进行的操作(包括读),此时alter table语句无法获取到metadata 独占锁,会进行等待。

    这是最基本的一种情形,这个和mysql 5.6中的online ddl并不冲突。一般alter table的操作过程中(见下图),在after create步骤会获取metadata 独占锁,当进行到altering table的过程时(通常是最花时间的步骤),对该表的读写都可以正常进行,这就是online ddl的表现,并不会像之前在整个alter table过程中阻塞写入。(当然,也并不是所有类型的alter操作都能online的,具体可以参见官方手册:http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html)

    处理方法: kill 掉 DDL所在的session.

    场景二:未提交事物,阻塞DDL,继而阻塞所有同表的后续操作

    通过show processlist看不到TableA上有任何操作,但实际上存在有未提交的事务,可以在 information_schema.innodb_trx中查看到。在事务没有完成之前,TableA上的锁不会释放,alter table同样获取不到metadata的独占锁。

    处理方法:通过 select * from information_schema.innodb_trx\G, 找到未提交事物的sid, 然后 kill 掉,让其回滚。

    场景三:

    通过show processlist看不到TableA上有任何操作,在information_schema.innodb_trx中也没有任何进行中的事务。这很可能是因为在一个显式的事务中,对TableA进行了一个失败的操作(比如查询了一个不存在的字段),这时事务没有开始,但是失败语句获取到的锁依然有效,没有释放。从performance_schema.events_statements_current表中可以查到失败的语句。

    官方手册上对此的说明如下:

    If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.

    也就是说除了语法错误,其他错误语句获取到的锁在这个事务提交或回滚之前,仍然不会释放掉。because the failed statement is written to the binary log and the locks protect log consistency 但是解释这一行为的原因很难理解,因为错误的语句根本不会被记录到二进制日志。

    处理方法:通过performance_schema.events_statements_current找到其sid, kill 掉该session. 也可以 kill 掉DDL所在的session.

    总之,alter table的语句是很危险的(其实他的危险其实是未提交事物或者长事务导致的),在操作之前最好确认对要操作的表没有任何进行中的操作、没有未提交事务、也没有显式事务中的报错语句。如果有alter table的维护任务,在无人监管的时候运行,最好通过lock_wait_timeout设置好超时时间,避免长时间的metedata锁等待。

    展开全文
  • 解决办法故障总结系统中的global read lock被其它线程获取了(被加上了S锁),因此DML在grl上面加IX锁时会出现Waiting for global read lock,而set read_only在grl上加的是S锁,因此此时是可以grant的,而此时系统中...
  • 解决办法故障总结系统中的global read lock被其它线程获取了(被加上了S锁),因此DML在grl上面加IX锁时会出现Waiting for global read lock,而set read_only在grl上加的是S锁,因此此时是可以grant的,而此时系统中...
  • 而且,一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态,后续对TableA的任何操作(包括读)都无法进行,因为他们也会在Opening tables的阶段进入到Waiting for table metadata lock的锁...
  • kill flush tables的思考

    2015-09-08 15:48:31
    数据库发生一个现象: 在mysqldump备份的时候,会执行flush tables语句,再连接上的dml语句就会等待Waiting for table flush。是因为一个长时间运行的select导致flush table...
  • MySQL在进行alter ...而且,一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态,后续对TableA的任何操作(包括读)都无法进行,因为他们也会在Opening tables的阶段进入到Waiting for tabl
  • <div><p>This includes several improvements to keyboard navigation behavior in tables: <ul><li>It's now possible to tab in and out of a table from outside the window (e.g. iframe, or browser chrome...
  • 例会话一select id,sleep(60) from t1;...在MariaDB 10.4 版本以前,执行FLUSH TABLES会把所有的表强制关闭,因会话一持有MDL元数据锁,固FLUSH TABLES会等待Waiting for table metadata lock在Ma...
  • review: fix waiting tasks

    2020-12-09 02:15:54
    <p>We were <a href="https://www.w3schools.com/sql/sql_join.asp">joining</a> the tasks and jobs tables. In SQL terms <code>JOIN</code> means intersection. As waiting tasks do not have jobs they were ...
  • Tables/turkish grade 2

    2020-12-31 14:01:17
    <div><p>This is basically the same PR as #357 just re-submitted as the original ...<p>We are waiting for permission to license under LGPLv2.1</p><p>该提问来源于开源项目:liblouis/liblouis</p></div>
  • <div><h2>Please provide the following details to ... is only for tables. The rest of the time you are "waiting for" something to happen. </p><p>该提问来源于开源项目:juju/juju</p></div>
  • <p>Waiting for an eventual approval on the feature scope, the user guide and the developer guide are not yet impacted. <h2>Change log entry: <p>Section: Changes for developers Add-on can now provide ...
  • 而且,一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态,后续对TableA的任何操作(包括读)都无法进行,因为他们也会在Opening tables的阶段进入到Waiting for table metadata lock的锁...
  • These new tables are now merged to the Liblouis project and probably waiting for next release. But I upload them here for your convenience. I named tables as "fa-ir-g1.utb" for braille output,...
  • <div><p>This PR is quite ...- Still relies on an unreleased version of Spring Data JPA while waiting for <a href="https://jira.spring.io/browse/DATAJPA-1185">this</a> bug to be released as part of the ;...
  • Waiting for table flush故障处理

    千次阅读 2019-12-04 14:00:33
    一 原理总结 ...Closes all open tables, forces all tables in use to be closed, and flushes the query cache. FLUSH TABLES also removes all query results from the query cache, like the ...
  • <p>The use case will apply for all existing tables that fetches records from ChirpStack API. One use case would be when someone is listing a lot of devices, and the following evens are triggered: <ul>...
  • <p>Also number of binaries waiting to be converted is keep increasing also. WHat can I do to fix this? Many thanks. <p>Completed adding 13 releases in 12.92 second(s). 646,287 binaries waiting to be ...
  • MySQL在进行alter table等DDL操作时,有时会出现Waiting for table metadata lock的等待场景。而且,一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态,... tables的阶段进入到Waiting for t
  • MySQL版本为5.6.12。...而且,一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态,后续对TableA的任何操作(包括读)都无法进行,也会在Opening tables的阶段进入Waiting...
  • 而且,一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态,后续对TableA的任何操作(包括读)都无法进行,因为他们也会在Opening tables的阶段进入到Waiting for tab...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 898
精华内容 359
关键字:

tableswaiting