…SELECT * FROM t WHERE i > 20 FOR UPDATE; +——+ | i | +——+ | 21 | | 25 | | 30 | +——+ transaction2> START TRANSACTION; transaction2> INSERT INTO t VALUES(26); transaction2> COMMIT; transaction1> select * from…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…insert into C values (“a“,1) | 0 | 0 | 1 | | 10304 | root | localhost | dumptest | Field List | 15 | Waiting for table…SELECT queries to let backup to proceed, but resolving server gridlock one way or another. If you’re just using Innodb tables and…
Post: A recovery trivia or how to recover from a lost ibdata1 file
June 3, 2011 By 7 Comments
A few day ago, a customer came to Percona needing to recover data. Basically, while doing a transfer from one SAN to another, something went wrong and…mysql -u root -e “insert into tables (name,spaceid) values (‘$tname’,conv(‘$spaceid’,16,10))” test; done Recovering the tables…
Post: AUTO_INCREMENT and MERGE TABLES
September 11, 2008 By Leave a Comment
…table a2; Query OK, 0 rows affected (0.00 sec) mysql> insert into am values(null); Query OK, 1 row affected (0.00 sec) mysql> select * from…inserting into Merge Table and in the last table directly will have different results. Another…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…how to avoid enormous tables that are hard to maintain, back up, and restore. And the partitioning functionality is not done yet and not fully integrated into…MySQL can’t handle too many of those at once. Aside from star schemas, another tactic I see people try a lot is to…
Post: Can MySQL temporary tables be made safe for statement-based replication?
May 26, 2008 By 8 Comments
…how to make MySQL replication reliable, part of which is to eliminate temporary tables. The idea is this: if a slave is stopped (or crashed) while a…table test.t(a int) engine=innodb; -> insert into test.t(a) values(1); -> insert into test.ins(a) select * from test.t; -> drop temporary table…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…to create incrementally refreshable views from SQL statements. It reads one or more “CREATE TABLE db.schema … AS SELECT” and/or “INSERT INTO db.schema … AS SELECT” statements from…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
…table. Now let us populate antest_innodb table which is same but uses Innodb format: mysql> insert into antest_innodb select * from…Another difference when it comes from handling the statistics comes from NULL handling. MyISAM has a… “c” to NULL in both tables and see how values change: mysql> update …
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
…and error approach: mysql> insert into test2 select * from test; ERROR 2013 (HY000): Lost connection to MySQL server during query You may think will will scan the table…MySQL crashes to make sure indexes are not corrupted. So we looked at how to get your data back from simple Innodb Table…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 7 Comments
…and SELECT from the same table in a single query in MySQL. But we can use MySQL‘s multi-table DELETE syntax, to find any path that terminate at a…
