New answers tagged alter-table
You cannot get around this with MySQL RDS, ... EVER !!! Can we disable binary logging for one specific session or user in AWS RDS MySQL Documentation says it requires SUPER even at the session level The Scale-Out Blog: Replicating from MySQL to Amazon RDS says We will take a recent replicator build that contains the RDS changes, which are documented ...
Provide a name for the constraint: SQL> create table monkey(tail_count number); Table created. SQL> insert into monkey values (null); 1 row created. SQL> alter table monkey modify tail_count not null; alter table monkey modify tail_count not null * ERROR at line 1: ORA-02296: cannot enable (ANIMAL.) - null values found SQL> alter table ...
I believe you could just use: select * from ANIMAL.MONKEY where TAIL_COUNT is null; to identify problematic rows, or add clause NOVALIDATE at the end of your alter statement.
The short answer is: "Never use OPTIMIZE TABLE on InnoDB tables." There are exceptions, but they are rare and esoteric. You have not listed such a situation.
No, you should not do OPTIMIZE TABLE because it is the same as ALTER TABLE tblname ENGINE=WhateverTheStorageEngineIs; ANALYZE TABLE tblname; Thus, doing an ALTER TABLE tblname ... ;of any kind followed byOPTIMIZE TABLE tblname;` would create two temp tables. BTW when you do OPTIMIZE TABLE tblname; on an InnoDB Table you get this mysql> OPTIMIZE TABLE ...
InnoDB does not support optimize, so does an "empty" alter table instead. If you just did an alter changing structure of the table, then optimize is meaningless. It might have some meaning for INPLACE algorithm of alter in newer MySQL versions, but it would be better do the normal alter instead in the first place.
For Postgres, adding a column without a default value is essentially a no-op as only the catalog tables get rewritten. Apart from the short exclusive(!) lock there is no performance impact. Although that lock is only held for a very short time, getting the lock on a busy system might be a problem because all open transactions and queries need to be ...
You want to update field ID and set to 10? Some sample: UPDATE test.personal_info SET ID = '10' WHERE db1.table1 = db2.table2 OR UPDATE test.personal_info SET ID = '10' ON <DB.TABLE> JOIN TABLE1.FIELD = DB.TABLE2.FIELD
The manual does not list a problem, but... Statement Based Replication should not have any problem since the Slave simply re-executes the DML statement without knowing or caring (up front) about whether the table is partitioned. Some rare statements are very likely to break replication: SELECT ... PARTITION pnnn -- 5.6.2 ALTER TABLE -- to change ...
Top 50 recent answers are included