…://dev.mysql.com/doc/refman/5.1/en/alter-table.html “For a column that has a data type of VARCHAR or one…, CONVERT TO CHARACTER SET will change the data type as necessary to ensure that the new column is long enough to store… characters as the original column.” [followed by a complete explanation of why TEXT coverts to MEDIUMTEXT] Maybe something changed since this article…
Post: The small improvements of MySQL 5.6: Duplicate Index Detection
May 31, 2013 By Leave a Comment
… a series of posts on the small but handy improvements – changes and bug corrections – in MySQL 5.6 that can make…`,`col3`) # Column types: # `col2` int(11) default null # `col3` varchar(200) default null # To remove this duplicate index, execute: ALTER TABLE `test`.`test…`), # Column types: # `col2` int(11) default null # `id` int(11) not null auto_increment # To shorten this duplicate clustered index, execute: ALTER TABLE…
Comment: Implications of Metadata Locking Changes in MySQL 5.5
March 20, 2013 By 9 Comments
… row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 alter table t1 add column title2 varchar(30) default ‘ook’; (hang) mysql> commit; Query OK, 0 rows affected (0.01 sec) mysql> alter table t1… COMMIT /* xid=18 */ use `bruce`; alter table t1 add column title3 varchar(30) default ‘oook’ As you see,alter table is logged after update,that…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
…()),’-',sha1(rand()),’-',sha1(rand()),’-',sha1(rand())); and added key on column C: alter table sbtest add key c(c); The box I’m… only applies to the case when you’re adding/dropping/changing columns as if you just add index it will be done…: 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: Should you name indexes while doing ALTER TABLE ?
May 28, 2008 By 10 Comments
… set (0.00 sec) Such automatically table generated names are not really helpful for multiple column indexes – you can’t easily see…> alter table t1 add key(i); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t1… run ALTER TABLE on the SLAVE server to check things out and when run it again on the master to apply changes to…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
…that the ALTER waits only at the last stages when its making changes to the table metadata, a table ALTER that alters a big table can …| 229 | Waiting for table metadata lock | ALTER TABLE test_2 DROP COLUMN c | | 7 | msandbox | localhost | test | Query | 195 | Waiting for table metadata lock | …
Post: On Character Sets and Disappearing Tables
June 21, 2012 By 1 Comment
…]> alter table dos1 change column fkto2 fkto2 char(32) character set ascii not null, change column fkto3 fkto3 char(32) character set ascii not null, change column fkfrom3… move on to table 2: (root@localhost) [oops4]> alter table dos2 change column fkto3 fkto3 char(32) character set ascii not null, change column fkfrom1 fkfrom1 char…
Post: Knowing what pt-online-schema-change will do
November 1, 2012 By 1 Comment
…. When ran with –dry-run and –print, pt-online-schema-change changes nothing but prints a lot of information about what it… 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…
Post: pt-online-schema-change and default values
June 21, 2012 By 1 Comment
… ALTER TABLE in MySQL I can ignore default value and it will be assigned based on the column type. For example this alter table…online-schema-change –execute –alter=”add column v varchar(100) not null” D=sbtest,t=sbtest Altering `sbtest`.`sbtest`… Creating new table… Created new table …
Post: AUTO_INCREMENT and MERGE TABLES
September 11, 2008 By Leave a Comment
…columns inserting into Merge Table and in the last table…ALTER TABLE for underlying tables. Unlike with TRUNCATE TABLE there is no warning displayed but the MERGE TABLE…
