精华内容
下载资源
问答
  • 在对其他表进行修改的时候,无论是修改字段长度还是删除索引添加唯一约束都没有问题,但是唯独有一张表,无论进行什么表操作全部都会出现Waiting for table metadata lock。 刚开始的解决思路: 找到没完成DDL的操作...

    简单描述一下遇到的问题:
    根据项目数据入库要求,在之前没有设置唯一约束的表上添加唯一约束,这就涉及到需要修改表结构。
    在对其他表进行修改的时候,无论是修改字段长度还是删除索引添加唯一约束都没有问题,但是唯独有一张表,无论进行什么表操作全部都会出现Waiting for table metadata lock。

    刚开始的解决思路:
    找到没完成DDL的操作进程将其杀死即可。
    但是使用select * from information_schema.innodb_trx;得到的进程太多了,根本无法及时将具体是哪个问题进程杀死(因为不知道是哪一个)
    在这里插入图片描述

    后来一狠心将源表进行结构和数据的copy:
    然后在复制下来的表上进行修改,最后将原表删除,copy的表修改成原表名。
    然而事实是原表根本删除不了,还是会因为Waiting for table metadata lock而导致无法删除表。

    最后的解决办法:
    select concat(“kill “,trx_mysql_thread_id,”;”) as kill_id from information_schema.INNODB_TRX where trx_lock_structs=0 and trx_weight=0 and trx_rows_locked=0 and trx_rows_modified=0 and trx_state=‘RUNNING’;
    一次性找到所有未完成的进行,并通过sql语句将其全部找到,然后统一杀死(万不得已,不建议这么做,基本就等于宁错杀一千也不放过一个)
    在这里插入图片描述
    在这里插入图片描述

    当然通过上面的操作是可以解决Waiting for table metadata lock导致的无法进行的表操作。

    展开全文
  • MySQL在进行alter table等DDL操作时,有时会出现Waiting for table metadata lock的等待场景。而且,一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态,后续对TableA的任

    转自:http://ctripmysqldba.iteye.com/blog/1938150 (有修改)

    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 (trx_mysql_thread_id), 然后 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子查询,在问题的诊断中,丹臣注意到一个较为严重的问题,就是我们生产库中全部的数据库访问请求都处于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加上数据库的低效子查询构成这次故障的原因。

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

    展开全文
  • 而且,一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态,后续对TableA的任何操作(包括读)都无法进行,因为他们也会在Opening tables的阶段进入到Waiting for table metadata lock的锁...
  • 总结 1、FLUSH TABLES关闭所有打开的表,强制关闭所有正在使用的表,并刷新查询缓存和预准备语句缓存,不会刷新脏块 2、FLUSH TABLES WITH READ LOCK关闭所有打开的表并使用全局读锁...

    总结
    1、FLUSH TABLES关闭所有打开的表,强制关闭所有正在使用的表,并刷新查询缓存和预准备语句缓存,不会刷新脏块
    2、FLUSH TABLES WITH READ LOCK关闭所有打开的表并使用全局读锁锁定所有数据库的所有表,不会刷新脏块
    3、如果一个会话中使用LOCK TABLES tbl_name lock_type语句对某表加了表锁,在该表锁未释放前,那么另外一个会话如果执行FLUSH TABLES语句会被阻塞,执行FLUSH TABLES WITH READ LOCK也会被堵塞
    4、如果一个会话正在执行DDL语句,那么另外一个会话如果执行FLUSH TABLES 语句会被阻塞 ,执行FLUSH TABLES WITH READ LOCK也会被堵塞
    5、如果一个会话正在执行DML大事务(DML语句正在执行,数据正在发生修改,而不是使用lock in share mode和for update语句来显式加锁),那么另外一个会话如果执行FLUSH TABLES语句会被阻塞,执行FLUSH TABLES WITH READ LOCK也会被堵塞
    6、FLUSH TABLES WITH READ LOCK语句不会阻塞日志表的写入,例如:查询日志,慢查询日志等
    7、mysqldump的--master-data、--lock-all-tables参数引发FLUSH TABLES和FLUSH TABLES WITH READ LOCK
    8、FLUSH TABLES tbl_name [, tbl_name] ... FOR EXPORT 会刷新脏块
    9、FLUSH TABLES WITH READ LOCK可以针对单个表进行锁定,比如只锁定table1则flush tables table1 with read lock;


    FLUSH TABLES
    https://dev.mysql.com/doc/refman/5.7/en/flush.html#flush-tables
         Closes all open tables, forces all tables in use to be closed, and flushes the query cache and prepared statement cache. FLUSH TABLES also removes all query results from the query cache, like the RESET QUERY CACHE statement.
         关闭所有打开的表,强制关闭所有正在使用的表,并刷新查询缓存和预准备语句缓存。 FLUSH TABLES还会从查询缓存中删除所有查询结果,例如RESET QUERY CACHE语句。


    RESET QUERY CACHE
    https://dev.mysql.com/doc/refman/5.7/en/query-cache.html
         The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.
         The query cache can be useful in an environment where you have tables that do not change very often and for which the server receives many identical queries. This is a typical situation for many Web servers that generate many dynamic pages based on database content.
         The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.
         查询缓存存储SELECT语句的文本以及发送到客户端的相应结果。 如果稍后收到相同的语句,则服务器从查询缓存中检索结果,而不是再次解析和执行语句。 查询缓存在会话之间共享,因此可以发送由一个客户端生成的结果集以响应由另一个客户端发出的相同查询。
         查询缓存在您拥有不经常更改且服务器接收许多相同查询的表的环境中非常有用。 这是许多基于数据库内容生成许多动态页面的Web服务器的典型情况。
         查询缓存不返回过时数据。 修改表时,将刷新查询缓存中的所有相关条目。


    FLUSH TABLES is not permitted when there is an active LOCK TABLES ... READ. To flush and lock tables, use FLUSH TABLES tbl_name ... WITH READ LOCK instead.

    当有表正处于LOCK TABLES … READ语句加锁状态时,不允许使用FLUSH TABLES语句(另外一个会话执行FLUSH TABLES会被阻塞),如果已经使用LOCK TABLES … READ语句对某表加读锁的情况下要对另外的表执行刷新,可以在另外一个会话中使用FLUSH TABLES tbl_name … WITH READ LOCK语句


    会话1先执行
    mysql> lock tables table1 read ;
    会话2,堵塞
    mysql> flush tables ;
    会话3,堵塞
    mysql> flush tables table1 with read lock;
    会话4,不堵塞
    mysql> flush tables table2 with read lock;


    FLUSH TABLES tbl_name [, tbl_name] ...
    With a list of one or more comma-separated table names, this statement is like FLUSH TABLES with no names except that the server flushes only the named tables. If a named table does not exist, no error occurs.
    使用一个或多个逗号分隔的表名列表,表示只刷新这些表名的表,如果命名表不存在,则不会发生错误。


    FLUSH TABLES WITH READ LOCK
         Closes all open tables and locks all tables for all databases with a global read lock. This is a very convenient way to get backups if you have a file system such as Veritas or ZFS that can take snapshots in time. Use UNLOCK TABLES to release the lock.
         关闭所有打开的表并使用全局读锁锁定所有数据库的所有表。 如果您具有可以及时拍摄快照的Veritas或ZFS等文件系统,则这是一种非常方便的备份方式。 使用UNLOCK TABLES释放锁定。(你可以及时使用支持快照的文件系统进行快照备份,备份完成之后,使用UNLOCK TABLES语句释放锁。)


         FLUSH TABLES WITH READ LOCK acquires a global read lock rather than table locks, so it is not subject to the same behavior as LOCK TABLES and UNLOCK TABLES with respect to table locking and implicit commits:
         UNLOCK TABLES implicitly commits any active transaction only if any tables currently have been locked with LOCK TABLES. The commit does not occur for UNLOCK TABLES following FLUSH TABLES WITH READ LOCK because the latter statement does not acquire table locks.
             Beginning a transaction causes table locks acquired with LOCK TABLES to be released, as though you had executed UNLOCK TABLES. Beginning a transaction does not release a global read lock acquired with FLUSH TABLES WITH READ LOCK.
         FLUSH TABLES WITH READ LOCK获取全局读锁而不是表锁,因此在表锁定和隐式提交方面,表现行为不会像LOCK TABLES和UNLOCK TABLES语句:
         当前任何表已被LOCK TABLES tbl_name lock_type语句锁定时,UNLOCK TABLES会隐式提交任何活动事务。但是执行FLUSH TABLES WITH READ LOCK之后,再执行UNLOCK TABLES不会发生提交,因为后一个语句没有获取表锁。
         开始事务会导致释放使用LOCK TABLES tbl_name lock_type语句获取的表锁,就像您已经执行了UNLOCK TABLES一    样。  开始事务不会释放使用FLUSH TABLES WITH READ LOCK获取的全局读锁定。


    FLUSH TABLES WITH READ LOCK is not compatible with XA transactions.
    FLUSH TABLES WITH READ LOCK does not prevent the server from inserting rows into the log tables
    FLUSH TABLES WITH READ LOCK 与XA事务不兼容。
    FLUSH TABLES WITH READ LOCK 不会阻止服务器将行插入日志表,例如:查询日志,慢查询日志等


    FLUSH TABLES tbl_name [, tbl_name] ... WITH READ LOCK
         This statement flushes and acquires read locks for the named tables. The statement first acquires exclusive metadata locks for the tables, so it waits for transactions that have those tables open to complete. Then the statement flushes the tables from the table cache, reopens the tables, acquires table locks (like LOCK TABLES ... READ), and downgrades the metadata locks from exclusive to shared. After the statement acquires locks and downgrades the metadata locks, other sessions can read but not modify the tables.
         Because this statement acquires table locks, you must have the LOCK TABLES privilege for each table, in addition to the RELOAD privilege that is required to use any FLUSH statement.
         This statement applies only to existing base (non-TEMPORARY) tables. If a name refers to a base table, that table is used. If it refers to a TEMPORARY table, it is ignored. If a name applies to a view, an ER_WRONG_OBJECT error occurs. Otherwise, an ER_NO_SUCH_TABLE error occurs.
         Use UNLOCK TABLES to release the locks, LOCK TABLES to release the locks and acquire other locks, or START TRANSACTION to release the locks and begin a new transaction.
         This FLUSH TABLES variant enables tables to be flushed and locked in a single operation. It provides a workaround for the restriction that FLUSH TABLES is not permitted when there is an active LOCK TABLES ... READ.
         This statement does not perform an implicit UNLOCK TABLES, so an error results if you use the statement while there is any active LOCK TABLES or use it a second time without first releasing the locks acquired.
         If a flushed table was opened with HANDLER, the handler is implicitly flushed and loses its position.
         此语句刷新并获取指定表的读锁定。 该语句首先获取表的独占元数据锁,因此它等待打开这些表的事务完成。 然后语句从表缓存中刷新表,重新打开表,获取表锁(如LOCK TABLES ... READ),并将元数据锁从独占降级为共享。 在语句获取锁并降级元数据锁后,其他会话可以读取但不能修改表。
         由于此语句获取表锁,因此除了使用任何FLUSH语句所需的RELOAD权限外,还必须为每个表具有LOCK TABLES权限。
         此语句仅适用于现有的基本(非TEMPORARY)表。 如果名称引用基本表,则使用该基本表。 如果它引用TEMPORARY表,则忽略它。 如果名称适用于视图,则会发生ER_WRONG_OBJECT错误。 否则,发生ER_NO_SUCH_TABLE错误。
         使用UNLOCK TABLES释放锁,使用LOCK TABLES释放该锁并获取其他锁,或使用START TRANSACTION释放锁并开始新的事务。
         此FLUSH TABLES变量使表能够在单个操作中刷新和锁定。 它提供了一个解决方法,当有一个活动的LOCK TABLES ... READ时,不允许FLUSH TABLES。
         此语句不执行隐式UNLOCK TABLES,因此如果在有任何活动的LOCK TABLES时使用该语句,或者在没有首先释放获取的锁的情况下再次使用该语句,则会导致错误。
         如果使用HANDLER打开已刷新的表,则会隐式刷新处理程序并丢失其位置。


    FLUSH TABLES tbl_name [, tbl_name] ... FOR EXPORT
         This FLUSH TABLES variant applies to InnoDB tables. It ensures that changes to the named tables have been flushed to disk so that binary table copies can be made while the server is running.
    The statement works like this:
         a.It acquires shared metadata locks for the named tables. The statement blocks as long as other sessions have active transactions that have modified those tables or hold table locks for them. When the locks have been acquired, the statement blocks transactions that attempt to update the tables, while permitting read-only operations to continue.
         b.It checks whether all storage engines for the tables support FOR EXPORT. If any do not, an ER_ILLEGAL_HA error occurs and the statement fails.
         c.The statement notifies the storage engine for each table to make the table ready for export. The storage engine must ensure that any pending changes are written to disk.
         d.The statement puts the session in lock-tables mode so that the metadata locks acquired earlier are not released when the FOR EXPORT statement completes.
         The FLUSH TABLES ... FOR EXPORT statement requires that you have the SELECT privilege for each table. Because this statement acquires table locks, you must also have the LOCK TABLES privilege for each table, in addition to the RELOAD privilege that is required to use any FLUSH statement.
         This statement applies only to existing base (non-TEMPORARY) tables. If a name refers to a base table, that table is used. If it refers to a TEMPORARY table, it is ignored. If a name applies to a view, an ER_WRONG_OBJECT error occurs. Otherwise, an ER_NO_SUCH_TABLE error occurs.
         InnoDB supports FOR EXPORT for tables that have their own .ibd file file (that is, tables created with the innodb_file_per_table setting enabled). InnoDB ensures when notified by the FOR EXPORT statement that any changes have been flushed to disk. This permits a binary copy of table contents to be made while the FOR EXPORT statement is in effect because the .ibd file is transaction consistent and can be copied while the server is running. FOR EXPORT does not apply to InnoDB system tablespace files, or to InnoDB tables that have FULLTEXT indexes.
         FLUSH TABLES ...FOR EXPORT is supported for partitioned InnoDB tables.
         When notified by FOR EXPORT, InnoDB writes to disk certain kinds of data that is normally held in memory or in separate disk buffers outside the tablespace files. For each table, InnoDB also produces a file named table_name.cfg in the same database directory as the table. The .cfg file contains metadata needed to reimport the tablespace files later, into the same or different server.
         When the FOR EXPORT statement completes, InnoDB will have flushed all dirty pages to the table data files. Any change buffer entries are merged prior to flushing. At this point, the tables are locked and quiescent: The tables are in a transactionally consistent state on disk and you can copy the .ibd tablespace files along with the corresponding .cfg files to get a consistent snapshot of those tables.
         For the procedure to reimport the copied table data into a MySQL instance, see Section 14.6.3.7, “Copying Tablespaces to Another Instance”.
         After you are done with the tables, use UNLOCK TABLES to release the locks, LOCK TABLES to release the locks and acquire other locks, or START TRANSACTION to release the locks and begin a new transaction.
         While any of these statements is in effect within the session, attempts to use FLUSH TABLES ... FOR EXPORT produce an error:
    FLUSH TABLES ... WITH READ LOCK
    FLUSH TABLES ... FOR EXPORT
    LOCK TABLES ... READ
    LOCK TABLES ... WRITE
         While FLUSH TABLES ... FOR EXPORT is in effect within the session, attempts to use any of these statements produce an error:
    FLUSH TABLES WITH READ LOCK
    FLUSH TABLES ... WITH READ LOCK
    FLUSH TABLES ... FOR EXPORT
         FLUSH TABLES tbl_name [, tbl_name] ... FOR EXPORT适用于InnoDB表。它确保已将指定表的更改刷新到磁盘,以便在服务器运行时创建二进制表副本。
    声明的作用如下:
         a.它获取指定表的共享元数据锁。只要其他会话具有已修改这些表或为其保存表锁的活动事务,该语句就会阻塞。获取锁定后,该语句将阻止尝试更新表的事务,同时允许只读操作继续。
         b.它检查表的所有存储引擎是否支持FOR EXPORT。如果没有,则发生ER_ILLEGAL_HA错误,并且语句失败。
         c.该语句通知存储引擎的每个表以使表准备好导出。存储引擎必须确保将所有挂起的更改写入磁盘。
         d.该语句将会话置于锁定表模式,以便在FOR EXPORT语句完成时不会释放先前获取的元数据锁。
         FLUSH TABLES ... FOR EXPORT语句要求您具有每个表的SELECT权限。 由于此语句获取表锁,因此除了使用任何FLUSH语句所需的RELOAD权限之外,还必须为每个表具有LOCK TABLES权限。
         此语句仅适用于现有的基本(非TEMPORARY)表。 如果名称引用基本表,则使用该基本表。 如果它引用TEMPORARY表,则忽略它。 如果名称适用于视图,则会发生ER_WRONG_OBJECT错误。 否则,发生ER_NO_SUCH_TABLE错误。
         对于具有自己的.ibd文件文件的表(即,启用了innodb_file_per_table设置创建的表),InnoDB支持FOR EXPORT。 InnoDB确保FOR EXPORT语句发出时任何更改都已刷新到磁盘。这允许在FOR EXPORT语句生效时生成表内容的二进制副本,因为.ibd文件是事务一致的,并且可以在服务器running时进行复制。 FOR EXPORT不适用于InnoDB系统表空间文件,也不适用于具有FULLTEXT索引的InnoDB表。
         FLUSH TABLES ... FOR EXPORT支持分区的InnoDB表。
         当FOR EXPORT通知时,InnoDB会将数据写入磁盘,这些数据通常保存在内存中或表空间文件之外的单独磁盘缓冲区中。对于每个表,InnoDB还在与表相同的数据库目录中生成名为table_name.cfg的文件。 .cfg文件包含稍后将表空间文件重新导入相同或不同服务器所需的元数据。
         当FOR EXPORT语句完成时,InnoDB会将所有脏页刷新到表数据文件。 在刷新之前合并任何更改缓冲区条目。 此时,表已锁定且处于静止状态:表在磁盘上处于事务一致状态,您可以将.ibd表空间文件与相应的.cfg文件一起复制,以获得这些表的一致快照。
          有关将复制的表数据重新导入MySQL实例的过程,请参见第14.6.3.7节“将表空间复制到另一个实例”。
          完成表后,使用UNLOCK TABLES释放锁,使用LOCK TABLES释放锁并获取其他锁,或使用START TRANSACTION释放锁并开始新事务。

        如下语句中的任何一个在会话中都有效,但在这个会话中再执行FLUSH TABLES ... FOR EXPORT会产生错误:
            (报错信息ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction,当然在其他会话执行不会报错,但是会一直等待,等待这个会话释放)
           FLUSH TABLES ... WITH READ LOCK
           FLUSH TABLES ... FOR EXPORT
           LOCK TABLES ... READ
           LOCK TABLES ... WRITE
        虽然FLUSH TABLES ... FOR EXPORT在会话中生效,但在这个会话中再使用如下语句中的任何一个都会产生错误:
            (报错信息ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction,当然在其他会话执行不会报错,但是会一直等待,等待这个会话释放)
            FLUSH TABLES WITH READ LOCK
            FLUSH TABLES ... WITH READ LOCK
            FLUSH TABLES ... FOR EXPORT

    来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30126024/viewspace-2374623/,如需转载,请注明出处,否则将追究法律责任。

    转载于:http://blog.itpub.net/30126024/viewspace-2374623/

    展开全文
  • 测试xtrabackup等待'Waiting for table flush' 窗口1: select count(1) from t as a1,t as a2 ,t as a3 ; 等待很长时间 窗口2: 开始备份xtrabackup备份 2019-12-30 20:04:15.331821394 =======================...
  • 文章目录多线程回放+flush tables with read lock 死锁一、场景描述二、死锁排查三、解决办法四、如何复现的? 多线程回放+flush tables with read lock 死锁 一、场景描述 MySQL-5.7.18 slave实例上夜间进行备份...
  • MySQL5.7 Waiting FOR TABLE FLUSH

    千次阅读 2018-12-21 15:48:54
    系统不能使用,数据库上99%的会话等待是Waiting FOR TABLE FLUSH,找到等待时间最长的会话kill,系统恢复正常。是什么原因造成的呢? 日志分析: Time: 2018-12-15T10:45:50.116723+08:00 User@Host: gg[gg] @ ...
  • 三个服务都试了下,都不行,看错误日志: 2020-06-28 10:50:25.814 [warning] <0.277.0> Error while waiting for Mnesia tables: {timeout_waiting_for_tables,[rabbit_user,rabbit_user_permission,rabbit_topic_...
  • Waiting for Commit Lock

    千次阅读 2016-05-20 13:37:42
    Bug#19843808: DEADLOCK ON FLUSH TABLES WITH READ LOCK + SHOW SLAVE STATUS Problem: If a client thread on an slave does FLUSH TABLES WITH READ LOCK; then master does some updates, SHOW SLAVE STATUS in ...
  • 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 ...
  • 问题:Waiting for table metadata lock kill掉所有的wating进程,依然drop不了表,而且Waiting for table metadata lock有重新出现在进程里。innodb_trx也没有相关的信息。 (原因:开发人员在创建表的...
  • 参数说明版本 percona-xtrabackup-2.4.8-Linux-x86_64 --kill-long-queries-timeout=N 指的是执行flush tables with read lock以后,如果flush操作被卡了N秒,则杀掉卡住它的线程,默认0的情况就是不杀死任何...
  • 平时我们在做mysql的增量备份时,会有flush logs这个操作,这个操作当时用root用户时,是没有问题的...SQL: FLUSH TABLES WITH READ LOCK 这个时候我们只需要root用户下给对应的用户赋予reload权限即可,必须拥有r
  • 背景: mycat读写分离,应用大量select超时 1.检查 通过检查发现大量select处于Waiting for tab...
  • 测试环境 centos7.6+mysql5.6.46 Waiting for table flush出现原因 ...The thread is executing FLUSH TABLES and is waiting for all threads to close their tables, or the thread got a notification that
  • 解决办法故障总结系统中的global read lock被其它线程获取了(被加上了S锁),因此DML在grl上面加IX锁时会出现Waiting for global read lock,而set read_only在grl上加的是S锁,因此此时是可以grant的,而此时系统...
  • Xtrabackup备份的时候执行flushs tables with read lock和show slave status会有可能和SQL Thread形成死锁,导致SQL Thread一直被卡主,STOP也没有用,Kill我们测试会丢失数据,只有Restart Server才行。 原因是SQL...
  • FLUSH TABLES WITH READ LOCK

    2018-02-07 20:10:23
    昨天碰到一个flush tables with read lock阻塞其他所有操作的案例,在博客园中6年前我已经写过一篇文章,再次拿出来分享下。 现象 id 账号 来源ip 库名 执行时间(秒) 状态 线程占用的内存(字节) SQL占用的内存(字节)...
  • <p>最近遇到一个案例,很多查询被阻塞没有返回结果,使用show processlist查看,发现不少<...MySQL</a>线程处于Waiting for <a href="https://www.centos.bz/tag/table/">tab...
  • Waiting for table metadata lock

    千次阅读 2016-06-03 15:55:08
    当表a的ddl操作因为无法获得独占的metadata lock(Waiting for table metadata lock)被阻塞时,后续对于表a的任何操作都会被阻塞,包括select操作(状态为Waiting for table metadata lock)   场景3:  in...
  • 最近有一台MySQL的从库老是报延迟,观察到:FLUSH TABLES WITH READ LOCK,阻塞了4个多小时,还有另外一条SQL语句select *,从现象上来看是select * 阻塞了flush tables with read lock。 flush tables with read ...
  • 用mysqldump 做个备份来搭建从库,接到业务人员反映,系统卡住了 ,执行命令:select * from information_schema.processlist where db='semir_33'\G 查看进程,发现有Waiting for table metadata lock: ...
  • | system user | Waiting for commit lock | 1 | +--------------+----------------------------------------+----------+ 8 rows in set (0.01 sec) mysql> 有许多类似下面的线程: | 516544 | cacti_user | ...
  • mysqldump有一个参数--lock-tables,以前对这个参数也没有详细了解过,直到上次有个网友问“参数lock-tables 是一次性锁定当前库的所有表,还是锁定当前导出表?“ ,之前一直以为只是锁定当前导出表,后面看了参数...
  • MySQL5.7 Waiting for global read lock

    千次阅读 2018-12-21 15:26:57
    系统报故障不能使用,从应用的日志上分析: Cause: java.sql.SQLException: The MySQL server is running with the --read-only option so it...show processlist可以看到 99%的(3200个)会话报Waiting for global rea...
  • MySQL 表锁以及FLUSH TABLES操作

    万次阅读 2015-10-22 17:11:15
    | 8826 | root | localhost | test | Query | 45 | Waiting for table flush | flush tables t1 | +------+------+-----------+------+---------+------+-------------------------+-------------------------------...
  • 环境:CentOS 7.6 原因分析: mysql开启了DNS的反向解析功能,这样mysql对连接的客户端会进行DNS主机名查找。 解决方案: 在 my.cnf 配置文件中的 [mysqld] 区域添加 skip-name-...skip-grant-tables #跳过..

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 21,143
精华内容 8,457
关键字:

tableswaiting