… | Waiting for table metadata lock | ALTER TABLE test_2 DROP COLUMN c | | 7 | msandbox | localhost | test | Query | 195 | Waiting for table metadata lock | select… table that is already in the Table Cache, any query to a table that has a DDL operation waiting, will block. Why? Because MySQL…
Post: Should you name indexes while doing ALTER TABLE ?
May 28, 2008 By 10 Comments
… warning: mysql> alter table t1 add key(i); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t1 add key(i); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t1…: mysql> alter table t1 add key idx_i(i); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table…
Post: On Character Sets and Disappearing Tables
June 21, 2012 By 1 Comment
…(j) ) ENGINE=INNODB; trying to do something like “ALTER TABLE bar DROP j” or “ALTER TABLE bar MODIFY COLUMN j j SMALLINT NOT NULL… in a new session. We move on to table 2: (root@localhost) [oops4]> alter table dos2 change column fkto3 fkto3 char(32) character… I think there’s something of an inconsistency present when MySQL won’t allow us to change an FK-referenced column…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL manual, especially the option and variable reference table…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
…table. No matter if it’s InnoDB or MyISAM, you’d usually ALTER TABLE `huge_table` …mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) mysql> SHOW CREATE TABLE `huge_table`\G *************************** 1. row *************************** Table: huge_table Create Table: CREATE TABLE `huge_table…
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 10 Comments
… recover only the “salaries” table: Discard the tablespace of the salaries table: mysql> set FOREIGN_KEY_CHECKS=0; mysql> ALTER TABLE salaries DISCARD TABLESPACE; Copy the…/lib/mysql/data/employees/ Import the new tablespace: mysql> set FOREIGN_KEY_CHECKS=0; mysql> ALTER TABLE salaries IMPORT TABLESPACE; mysql> set FOREIGN_KEY_CHECKS=1; mysql…
Post: Using innodb_sys_tables and innodb_sys_indexes
June 15, 2012 By Leave a Comment
… information_schema tables we added in Percona Server. I was doing simple ALTER TABLE such as: “alter table sbtest …… and indeed it does. mysql> select * from innodb_sys_tables where table_id=18; +———-+——–+————–+——+——–+——-+ | TABLE_ID | SCHEMA | NAME | FLAG …
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… table that the extra index in Innodb could be removed by doing: ALTER TABLE table_name ENGINE=Innodb; This, of course, rebuilds the whole table based on the .frm table definition and removes the…
Post: MySQL Upgrade Webinar Questions Followup
June 28, 2012 By 7 Comments
… MyISAM tables? MyISAM have not been getting any significant development since MySQL 4.1, so in-place upgrade of MySQL 5.0 to MySQL… plain ALTER TABLE statements? Yes. This is exactly how it is done in many cases. There is really no gain in dumping table… MyISAM tables are on track to be depreciated in future MySQL versions also MyISAM tables are essentially at the state they were in MySQL…
Post: Is your MySQL Application having Busy IO by Oracle Measures ?
January 8, 2012 By 1 Comment
… at MySQL we often start “sharding” or splitting data other way because operationally working with large table and data sizes in MySQL can be painful, lacking online operations to alter table, defragmentation etc. Yes there…
