… -B <<'EOF' > discard-ddl.sql SELECT CONCAT(‘ALTER TABLE `’, table_name, ‘` DISCARD TABLESPACE;’) AS _ddl FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=’orig’ AND ENGINE=’InnoDB… -B <<'EOF' > import-ddl.sql SELECT CONCAT(‘ALTER TABLE `’, table_name, ‘` IMPORT TABLESPACE;’) AS _ddl FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=’orig’ AND ENGINE=’InnoDB…
Post: The small improvements of MySQL 5.6: Duplicate Index Detection
May 31, 2013 By Leave a Comment
…> ALTER TABLE test ADD INDEX (col2); Query OK, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE test…> ALTER TABLE test ADD INDEX (col2); Query OK, 0 rows affected (0.36 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE test… 5.7 the checks will be stricter: in the default SQL mode, a duplicate index will throw a warning instead of…
Post: Percona XtraBackup 2.0.7 for MySQL available for download
May 6, 2013 By 1 Comment
… be imported by ALTER TABLE IMPORT TABLESPACE on MySQL and Percona Server 5.6 as described in Exporting and Importing Tables guide. Bugs… crash when preparing the 5.6 backup with partitioned tables. Bug fixed #1169169. Tables that were dropped between taking a full backup… copying failure. Bug fixed #1170806. innobackupex would fail if the SQL_MODE was set to ANSI_QUOTES. Bug fixed #945161. Missing…
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… happens: (root@localhost) [foobar]> ALTER TABLE bar drop j; ERROR 1025 (HY000): Error on rename of ‘./foobar/#sql-3c13_11′ to ‘./foobar/bar… in a new session. We move on to table 2: (root@localhost) [oops4]> alter table dos2 change column fkto3 fkto3 char(32) character…
Post: Using innodb_sys_tables and innodb_sys_indexes
June 15, 2012 By Leave a Comment
…tables we added in Percona Server. I was doing simple ALTER TABLE such as: “alter table…
Post: Slow DROP TABLE
June 16, 2009 By 21 Comments
…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…
Comment: Using MMM to ALTER huge tables
March 27, 2008 By 11 Comments
If you’re just doing an ALTER, a simple way to do it is run: SET SQL_LOG_BIN=0; ALTER TABLE … SET SQL_LOG_BIN=1; First on one server then on the other, switching masters each time.
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…alter_db_upgradeblogpercona.commanual Com_alter_eventblogpercona.commanual Com_alter_functionblogpercona.commanual Com_alter_procedureblogpercona.commanual Com_alter_serverblogpercona.commanual Com_alter_tableblogpercona.commanual Com_alter…
Post: Knowing what pt-online-schema-change will do
November 1, 2012 By 1 Comment
… 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…. The technical details behind the creation of the SQL statements for copying the table are beyond the scope of this blog post…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…-uroot test –innodb-optimize-keys > dump_optimized.sql $ time mysql -uroot test < dump_unoptimized.sql real 2m52.785s user 0m3.179s …in 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 …
