…(free space) after this I executed alter table abc engine=innodb and re-executed SELECT TABLE_SCHEMA, TABLE_NAME, CONCAT(ROUND(data_length / ( 1024 * 1024… / ( 1024 * 1024 ), 2), ‘MB’)FREE from information_schema.TABLES where TABLE_SCHEMA=’xyz’ and table_name=’abc’ and Data_free > 0 limit 10; still…
Post: Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs
February 23, 2012 By Leave a Comment
… mysql> drop table salaries; — Ooops! Query OK, 0 rows affected (0.49 sec) mysql> alter table employees add …at position 336797160: mysql> show binary logs; +——————+———–+ | Log_name | File_size | +——————+———–+ … | mysql-bin.000022 | 336797725 | | mysql-bin….
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
…table with modified structure, copy data to that table and when drop original table and rename such temporary table to original name… — Dumping data for table `C` — LOCK TABLES `C` WRITE; /*!40000 ALTER TABLE `C` DISABLE KEYS */; /*!40000 ALTER TABLE `C` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET …
Comment: Give feedback on a pt-online-schema-change update
March 23, 2012 By 2 Comments
… as column names, it will be good to have the col name in quotes inside the osc, which will allow altering a table without restrictions of col names. I have also… handle the part of rolling forward of changes of a table in chunks with sleep ? this cause a huge replication delays…
Comment: Fixing column encoding mess in MySQL
March 14, 2012 By 32 Comments
Before banging head to the table after “ALTER TABLE CONVERT TO CHARACTER SET utf8″ try looking at your columns via HEX(column_name).. It did convert everything very well. My problem was not setting connection encoding… i.e. “SET NAMES utf8″ ..
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…table_definition_cacheblogpercona.commanual table_lock_wait_timeoutblogpercona.commanual Table_locks_immediateblogpercona.commanual Table_locks_waitedblogpercona.commanual table_open_cacheblogpercona.commanual table…
Post: Should you name indexes while doing ALTER TABLE ?
May 28, 2008 By 10 Comments
… Server does not require you to specify name of the index if you’re running ALTER TABLE statement – it is optional. Though what… you would specify index name MySQL will complain if you try to create index with same name again: mysql> alter table t1 add key…: 0 mysql> alter table t1 add key idx_i(i); ERROR 1061 (42000): Duplicate key name ‘idx_i’ Adding indexes without name specified is…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…57.65 sec) mysql> SHOW TABLE STATUS LIKE ‘t’\G *************************** 1. row *************************** Name: t Engine: InnoDB Version: …ALTER TABLE are ignored to enforce uniqueness where necessary when copying the data to a temporary table; ALTER TABLE and OPTIMIZE TABLE always process tables…
Post: A recovery trivia or how to recover from a lost ibdata1 file
June 3, 2011 By 7 Comments
…table recording the space ids of the tables mysql> show create table test.tablesG *************************** 1. row *************************** Table: tables Create Table: CREATE TABLE `tables` ( `name…
Post: Dropping unused indexes
January 15, 2009 By 6 Comments
… information_schema.table_statistics ts where ts.table_schema=s.table_schema and ts.table_name=s.table_name)>0) d group by table_schema,table_name; +———————————————————–+ | stmt | +———————————————————–+ | alter table board…
