…. There was no truncation: MEDIUMTEXT is bigger than TEXT. MySQL does this to make sure there’s *no* truncation, as explained in the manual – http://dev.mysql.com/doc/refman/5.1/en/alter-table.html “For a…
Post: Rotating MySQL slow logs safely
April 18, 2013 By 7 Comments
…): copytruncate – Copies the file to a new name, and then truncates the original file. no copytruncate – Uses the rename() system call… because the OS serializes access to the inode during the truncate operation. This problem is particularly evident when using the ext3… 65481 explains that the HUP signal also flushes tables in addition to logs. Flushing tables can impact running queries. Disable MySQL slow…
Post: AUTO_INCREMENT and MERGE TABLES
September 11, 2008 By Leave a Comment
… stable merge table auto_increment values are by truncating the table we just inserted data to: mysql> truncate table a2; ERROR 1105 (HY000): MyISAM table ‘a2′ is in use (most likely by a MERGE table). Try FLUSH TABLES. mysql> flush tables; Query…
Post: Slow DROP TABLE
June 16, 2009 By 21 Comments
… DROP TABLE to minimize the effect, such as: TRUNCATE TABLE large_table; ALTER TABLE large_table ENGINE=…; DROP TABLE large_table; TRUNCATE TABLE large_table; OPTIMIZE TABLE large_table; DROP TABLE large_table; Unfortunately as it turned out each of the administrative commands like ALTER TABLE or OPTIMIZE TABLE…
Comment: Review of MySQL 5.6 Defaults Changes
October 9, 2012 By 10 Comments
innodb_file_per_table=1 makes TRUNCATE TABLE and DROP TABLE much slower, as in they could stall a server for 1 second. The DROP TABLE stall is fixed in 5.6. The TRUNCATE TABLE STALL remains. Hopefully TRUNCATE will get fixed and then using innodb_file_per_table=1 will be all good news.
Post: MySQL automatic data truncation can backfire
January 9, 2007 By 10 Comments
…. There is set of cache tables which cache certain content in MyISAM tables and queries for these tables such as: select data from… which can possibly be in the database we have MySQL truncating this value to 2^32-1, then performing index ref… before no values matched supplied key value. So beware, data truncation can backfire in a ways you might not ever expect 
Post: Concatenating MyISAM files
November 19, 2012 By 5 Comments
…… I calculated that with something like insert into MyISAM_table… select * from Innodb_table… would take about 10 days. The bottleneck was… to the mix: mysql> truncate table test_concat; Query OK, 0 rows affected (0.00 sec) mysql> truncate table test_concat_part; Query OK, 0 rows affected (0.01 sec) mysql> alter table test_concat add data…
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 10 Comments
… drop, truncate or alter the schema of the table after the backup has been taken. The variable innodb_file_per_table must be… are going to recover only the “salaries” table: Discard the tablespace of the salaries table: mysql> set FOREIGN_KEY_CHECKS=0; mysql… to import tables from different Server instance, when table was altered or truncated in the meanwhile. Though this only works if table was “exported…
Post: MySQL Slow query log in the table
May 31, 2007 By 1 Comment
… significant overhead, so you can instead create analyze_low_log table of similar structure with needed index and populate it with…) only read access is possible, with exception of TRUNCATE query. Which just recreates table which should be fast. Interesting enough however what… if one would use storage engine for log table which does not optimize TRUNCATE ? I guess it should be blocked as normal…
Post: Percona XtraBackup 1.6.3
September 23, 2011 By 5 Comments
…_file_per_table server option is being used and DDL operations, TRUNCATE TABLE, DROP/CREATE the_same_table or ALTER statements on InnoDB tables are… (Alexey Kopytov). Executing DDL operations, TRUNCATE TABLE, DROP/CREATE the_same_table or ALTER statements on InnoDB tables while taking a backup could lead…
