… the tool copies the table structure correctly, and 2) it allows the tool to test the –alter statement: Altering new table… ALTER TABLE `test`.`_t_new` add column (foo char(2)) Altered `test`.`_t_new` OK. If the syntax of the –alter statement…. That signals that the tool has done something wrong. Are all columns selected? Hopefully the tool will never make such a…
Post: A recovery trivia or how to recover from a lost ibdata1 file
June 3, 2011 By 8 Comments
… by creating fake Innodb tables Once the Innodb tablespace id is minus one of a customer table alter the table definition from MyISAM to Innodb Discard the tablespace Replace the table ibd file with the…, annoying. I decided, also for portability, to do “alter table tablename engine=MyISAM;” Script all that In order to get a decent disk…
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… never have any use for UTF8 in this database at all, and that we want to achieve the best performance possible… in a new session. We move on to table 2: (root@localhost) [oops4]> alter table dos2 change column fkto3 fkto3 char(32) character…
Post: Best kept MySQLDump Secret
March 23, 2012 By 13 Comments
… — – 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 TIME_… solutions for this problem ? you can use mysqldump –lock-all-tables instead which does not have this problem at the …
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 10 Comments
… the backup with innodb_fast_shutdown=0 and therefore merging all the insert buffers. # innobackupex –defaults-file=/etc/my.cnf –export… only the “salaries” table: Discard the tablespace of the salaries table: mysql> set FOREIGN_KEY_CHECKS=0; mysql> ALTER TABLE salaries DISCARD TABLESPACE; Copy…
Post: Should you name indexes while doing ALTER TABLE ?
May 28, 2008 By 10 Comments
…> 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… ALTER TABLE on the SLAVE server to check things out and when run it again on the master to apply changes to all…
Post: Finding your MySQL High-Availability solution – Replication
November 13, 2009 By 6 Comments
… that with MySQL, altering a table means recreating the whole table and it is a blocking operations. Altering a large table may takes many hours. The trick here is to run the alter table… following figure summarize the simplest HA architecture using MySQL replication. All writes are going to the master while reads are spread…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
…_index_creation=1 which builds table with primary key only when creating a table and when builds all indexes by sort. The speedup… 20x performance improvement to the speed of ALTER TABLE, as this is not only step which ALTER TABLE does the improvement to the index…: If you’re having large tables and need to run ALTER TABLE which rebuilds the table or OPTIMIZE TABLE do not forget to enable expand…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 18 Comments
… table with no indexes and bringing box down. You can also use this trick for ALTER TABLE which requires table rebuild. Dropping all indexes; doing ALTER and when adding them back can be a lot faster than straight ALTER TABLE. P…
Post: Slow DROP TABLE
June 16, 2009 By 21 Comments
…table, the mutex remains locked for all that time stalling all queries: …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…
