… version but it is so as of MySQL 5.0 The workaround is to modify statement to become SELECT with appropriate where clause. This however would not tell you full story, especially now as MySQL has triggers which can make updates very different. Also there is no way to tell you how ALTER table would…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… table rebuilds without affecting the table size. mysql> SET expand_fast_index_creation=ON; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE t MODIFY…) mysql> ALTER TABLE t MODIFY v VARCHAR(8); Query OK, 4194304 rows affected (1 min 13.69 sec) Records: 4194304 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE…
Post: Converting Character Sets
March 17, 2009 By 22 Comments
… TABLE `t1` ( ->Â Â `c1` text NOT NULL -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.02 sec) mysql> ALTER TABLE… to be. Consider the following ALTER statement against the table in Approach #1: ALTER TABLE `t1` DEFAULT CHARSET=utf8, MODIFY COLUMN `c1` text CHARACTER SET…
Post: Statement based replication with Stored Functions, Triggers and Events
December 16, 2011 By 4 Comments
…a little bit more difficult: Our procedure modifies a table that has a associated ON INSERT trigger … in this case we alter every event adding DISABLE ON SLAVE. ALTER EVENT even_name DISABLE ON… on the slave. Example: mysql> SELECT YEAR(CURDATE()) INTO @this_year; mysql> insert into t VALUES(@this…
Post: Reasons for run-away main Innodb Tablespace
June 10, 2010 By 2 Comments
… the following can be helpful: mysql> select * from innodb_rseg; +———+———-+———-+———+————+———–+ | rseg_id | …ALTER TABLE will not require excessive amount of undo space even for very large tables…update rate, say 10K+ rows are modified every second even 5 minute …
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… inside InnoDB 166 mysql tables in use 1, locked 0 MySQL thread id …tables locked by transactions. Innodb does not lock tables for normal operation so number of tables locked normally stays 0, unless it is ALTER TABLE…typically done by calling fsync() for modified files. Constant high values for…
Comment: Hacking to make ALTER TABLE online for certain changes
May 22, 2008 By 26 Comments
… ran:: (using world.sql) mysql> FLUSH TABLE WITH READ LOCK; mysql> ALTER TABLE City ENGINE=InnoDB; mysql> CREATE TABLE Citytmp LIKE City; mysql> ALTER TABLE Citytmp MODIFY ID INT UNSIGNED NOT NULL AUTO_INCREMENT; # cp /var/lib/mysql/world…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… that had single-column indexes on them, MySQL could sometimes make use of the multiple …s a dynamically-adjustable setting and can be modified globally or per-session: SET [GLOBAL|SESSION] … to go mucking about with server configuration, altering the table structure, or creating messy sub-selects. …
Post: Estimating Undo Space needed for LVM Snapshot
June 9, 2008 By 5 Comments
…using relatively sequential IO. Also if you modify same block again there is almost no …ALTER TABLE or OPTIMIZE TABLE will cause table rebuild and use a lot of space. Batch jobs such as recounting rating summary tables…undo space. For example MySQL on disk temporary files and tables can waste quite a …
Post: When should you store serialized objects in the database?
January 21, 2010 By 47 Comments
… how they changed from storing data in MySQL columns to serializing data and just storing…a large percentage of the data is actually modified. If you have to write back the …ALTER TABLE commands are no longer required. Until 5.1-plugin simple operations like adding a secondary index on an InnoDB table…
