…_File: mysql-bin.000007 …. omitted …….. Exec_Master_Log_Pos: 207 slave1 > show status like ‘%temp%’; +————————+——-+ | Variable_name | Value | +————————+——-+ | Slave_open_temp_tables | 1…, I still don’t see any way to use temporary tables with MySQL statement-based replication without some risk of breaking slaves…
Post: Can MySQL temporary tables be made safe for statement-based replication?
May 26, 2008 By 8 Comments
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 11 Comments
…, because the file isn’t really deleted. The MySQL server keeps an open filehandle to the file, and it won’t be… permanently. The pmp-check-mysql-deleted-files plugin will alert when files have been deleted but remain open via a filehandle. Wrong…, a pile-up of connections all trying to open or close tables can indicate LOCK_open contention. Replication is delayed or stopped. Our…
Post: Innodb vs MySQL index counts
November 29, 2011 By 6 Comments
…] Table database_name/table_name contains 8 indexes inside InnoDB, which is different from the number of indexes 7 defined in the MySQL… saw that the error only happened when the table was opened and not on every table access. So, it was a possibility that…_SCHEMA.INNODB_SYS_INDEXES table, which exists in Percona Server 5.1, but doesn’t appear in MySQL until 5.6 (if…
Post: Recovery deleted ibdata1
August 10, 2012 By 9 Comments
… customer deleted the InnoDB main table space – ibdata1 – and redo logs – ib_logfile*. MySQL keeps InnoDB files open all the time. The following…:/var/lib/mysql# And restart MySQL: root@localhost:/var/lib/mysql# /etc/init.d/mysql restart After the restart all InnoDB tables are reachable: mysql> select…
Post: Recovering CREATE TABLE statement from .frm file
December 17, 2008 By 5 Comments
… following MySQL creative error message: mysql> show tables; +—————-+ | Tables_in_test | +—————-+ | queue | | test_innodb | +—————-+ 2 rows in set (0.00 sec) mysql> show create table test… find or open table test/test_innodb from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe… would much rather see MySQL to report some more reasonable error message, something like Storage Engine could not open table or something like…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…=’Contains all avaialble data from 1988 to 2010′; mysql> use ontime1; Database changed mysql> show table status like ‘ontime_fact’G *************************** 1. row… table-definition-cache=16384 table-open-cache=128 thread-cache-size=32 thread-stack=256K tmp-table-size=64M transaction-isolation=READ-COMMITTED user=mysql…
Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
…affected (0.02 sec) node2 mysql> show create table autoinc\G *************************** 1. row *************************** Table: autoinc Create Table: CREATE TABLE `autoinc` ( `i` int(10) unsigned… so good. We have an open transaction on node1 updating row 1 in our table. Note we haven’t committed…
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 7 Comments
… InnoDB table statistics is used for JOIN optimizations and helping the MySQL optimizer choose the appropriate index for a query. If a table… the first opening of the table or manually running ANALYZE TABLE on it? The 2 instances below are documented from the MySQL and InnoDB plugin’s manual: Metadata commands like SHOW INDEX, SHOW TABLE STATUS and SHOW [FULL] TABLES (or their…
Post: What to tune in MySQL Server after installation
September 29, 2006 By 118 Comments
… case MySQL Server crashes. Value 2 only cause data loss with full OS crash. table_cache – Opening tables can be expensive. For example MyISAM tables mark MYI header to mark table as currently in use…
Post: How to obtain the "LES" (Last Executed Statement) from an Optimized Core Dump?
September 9, 2012 By 9 Comments
…First we inspect the mysql_execute_command frame: (gdb) frame 21 #21 0x00000000005a30af in mysql_execute_command …test”, db_length = 4}, <Open_tables_state> = { m_reprepare_observer = 0×0, open_tables = 0x7f3eac0085d0, temporary_tables = 0x7f3e84034a50, LOCK_temporary_tables = {m_mutex = { mutex = …
