SQL Function used to alter an existing table object.

learn more… | top users | synonyms

1
vote
2answers
55 views

mysql - ALTER IGNORE ADD INDEX removed in mysql 5.7.4 - What to use instead?

I am trying to add an index to a table, but only if one doesn't exist. I don't know what the index name is. In the mysql manual I found the alter ignore command: ALTER [IGNORE] TABLE tbl_name [...
2
votes
1answer
26 views

Large Number of ALTER TABLE Statements

I am managing a database in SQL Server 2008. Across about 15 tables, I have 1600 VARCHAR columns that need to be changed to NVARCHAR. I was able to generate all the appropriate ALTER TABLE ...
-1
votes
1answer
43 views

How to add default constraint in Oracle while altering column [closed]

I'm new in Oracle and my requirement is to port SQL Server statement to Oracle. I want to alter a column with adding a new constraint with a default value. SQL Server Query for the same: ALTER ...
0
votes
1answer
47 views

MySQL2::Error: BLOB/TEXT column can't have a default value

I am trying to run a rails mysql migration on Amazon Elastic Beanstalk. It fails with this error message: Mysql2::Error: BLOB/TEXT column 'description' can't have a default value: ALTER TABLE ...
1
vote
0answers
37 views

MySql - Update the defualt table schema on production server

The story starts with a MySql table called table on the production server: As you can see, I f***ed up when specifying defaults - 0000-00-00 00:00:00 is not a valid MySql datetime. I need to ...
3
votes
1answer
43 views

Is DROP/ADD UNIQUE constraint in ALTER TABLE atomic?

I have a UNIQUE constraint of a number of fields on a table. Image that I want to change the fields in this constraint (remove one field). Normally I would do something like: DROP INDEX unique_name ...
1
vote
1answer
19 views

Renaming a column in a materialized view with an unknown name

I have a materialized view with an unknown column name, and I need to give that column a name. I can't just recreate the view because it took a long time to create. dba=# \d temp_name_relationships; ...
6
votes
2answers
280 views

alter column size take very long time?

I have a table with 45M rows (45 GB data space and 2GB Index space). I added a new column and it finished instantly. alter table T add C char(25) Then I found the size is too small so I run the ...
0
votes
1answer
20 views

Change date precision values

Exposition: I have an oracle database table with date format as 'mm/dd/yyyy' and an unique constraint that contains the date column. My issue is that I can't seem to find a method to change the ...
1
vote
1answer
45 views

MySQL 5.6 Online DDL lead to to much table-metadata-locks when I just add index

It's normal that table-metadata-lock shows up for a short time when ALTER TABLE t add index ... starts and ends. But I have encountered all DML operations blocked in Waiting for table metadata lock, ...
6
votes
3answers
159 views

Does alter column change datatype use tempdb version store?

I was running alter column from INT to BIGINT, my table size is 250 GB and after 1 hour I got the version store error because tempdb was full. As far as I know it only takes your memory pages and log ...
0
votes
1answer
89 views

Alter table add column like another column syntax

I was looking into MySQL specification, but couldn't find any solution to my problem. I want to clone specific columns in several tables like this: ALTER TABLE `table` ADD COLUMN `new_column` LIKE `...
0
votes
1answer
37 views

Adding and populating column in Postgres with data from existing column

I have a TEXT column in one of my existing database tables that holds both domain names and IP addresses. I'd like to add a new column to the existing table that's of type INET and populate it with ...
1
vote
1answer
85 views

How to add Subpartition to all partitions

I believe adding sub-partition in Oracle 11g is easy ALTER TABLE PART_TEST modify partition OCT19 add subpartition OCT19AXCS values ('AXCS'); I have close to 250 partitions..namely jan07 feb07 ... ...
2
votes
2answers
97 views

Database “frozen” on ALTER TABLE

Our production environment just froze* this morning for a while when altering a table, adding a column actually. Offending SQL:ALTER TABLE cliente ADD COLUMN topicos character varying(20)[]; * ...
0
votes
1answer
39 views

Mysql takes more tmp space than the table size

I have a mysql table with 2.8GB data+index size. I wanted to add a column to the table and it was failing due to insufficient space on /tmp. When I looked at the size available on /tmp it was 3.9GB. ...
-1
votes
1answer
49 views

Alter - Subpartition a partitioned table

I have a table which is partitioned by RANGE on DATE key CREATE TABLE T1 DOC_ID VARCHAR NOT NULL. STATE VARCHAR ... DOC_DATE ... PARTITION BY RANGE (DOC_DATE)( PARTITION feb16 VALUES LESS ...
0
votes
1answer
99 views

How to add a foreign key constraint to a postgresql table

I have a postgresql table called Message. It's columns are text and creation_time. How do I alter this table such that a new column called sender is added to it, which is a foreign key on the User ...
0
votes
1answer
19 views

InnoDB, Writing TRIGGER to INSERT into tables

Table1 "user_address" Field "u_id" Field "street_name" Field "locality_name" Table2 "street_master" Field "s_id" Field "s_name" I am supposed to write a trigger which BEFORE INSERT ON user_address(...
1
vote
1answer
38 views

Altering a large table to INNODB

I have a table of 4GB(18 Million Rows) in MYISAM Engine on one of my slave server, and I want to convert it to INNODB Engine, the server is replicating from a master. I tried to use the pt-online-...
0
votes
0answers
64 views

oracle : Effect of inserting new rows when row movement is enabled

I have a table which is partitioned by key. I want to move few records (which I require later) to another partition so that I can drop this partition and get some free space. I am enabling row ...
1
vote
0answers
48 views

Manual postgres migration for Django (python) app

I have a Django (python) data model called Message: class Message(models.Model): text = models.CharField(max_length=50) sending_time = models.DateTimeField(auto_now_add=True) I added sender ...
1
vote
0answers
28 views

MySQL Cluster “ALTER ONLINE TABLE … STORAGE MEMORY” doubt

I have a MySQL Cluster database where the data is both in-memory and written to disk. I'm trying to understand the ALTER ONLINE TABLE ... STORAGE MEMORY statement, so that I can use this technique to ...
0
votes
1answer
53 views

How move MySql table to another disk?

According to this it is impossible to use alter table for moving tables. So if I understand correctlly - to move table it is neccessary to do: Create new table Copy all row from old one drop old ...
1
vote
1answer
33 views

Mysql (Ver 14.14/Redhat): is a new column with default values efficient on column creation?

I want to add a new column to my production database. I would like to add a default value in order to prevent all the if not null and ... business in all my queries (both new and old). What order of ...
1
vote
0answers
46 views

What should be the best practice to alter the table using the script to shift columns

I have a MySQL table which has columns 93 in it. All first 92 columns are INT(11) and the last column is of type DATE. |number|0|1|2|3........|88|89|90|ddate| | | | | | ........| | | | | ...
0
votes
0answers
79 views

Galera Cluster crashes because of “ALTER TABLE” statement?

We use on RHEL7 # rpm -qa | grep maria mariadb-5.5.41-2.el7_0.x86_64 mariadb-galera-common-5.5.41-2.el7ost.x86_64 mariadb-galera-server-5.5.41-2.el7ost.x86_64 Do I analyze logs correct that Galera ...
0
votes
1answer
67 views

Prevent replication of ALTER commands

I am using MariaDB 10.0 multi-source replication for a specific use case. For security reasons, I would like to prevent ALTER commands on master to replicate (such as CREATE, ALTER, DROP...) whatever ...
1
vote
2answers
1k views

Why am I seeing `ERROR 1290 (HY000)` when altering my table?

When I run the following statements on my test_date table, I get error ERROR 1290 (HY000): desc test_date; +------------+-----------+------+-----+-------------------+-------+ | Field | Type ...
1
vote
1answer
101 views

How to run ALTER TABLE on a list of tables sequentially in a function

I have a schema (called import) where data are imported as tables, and a function that processes them all. After that I want to move these tables away in another schema (called data_archive). The ...
1
vote
2answers
95 views

Can I make postgres more verbosely tell me what it's doing?

I'm trying to use ALTER TABLE on a live db (not in production, just poking around at a dev box and trying to learn some things) Here is the command I'm trying: psql -h $IP -p 5432 -U $DBUSER -d $...
1
vote
0answers
61 views

Space usage when copying

Short version Copy table took up more space than I expected or tested Why would that be? Could it be the replication / binlog? If so, what then is written to the binlog during this query: INSERT ...
0
votes
1answer
562 views

Oracle 11g - drop maxvalue partition, keep data

I have some tables with data like this: CREATE TABLE "PARTTEST4" ("year" number(4,0) NOT NULL ENABLE) PARTITION BY RANGE ("year") (PARTITION "P_OLD" VALUES LESS THAN (2010), PARTITION "...
4
votes
1answer
654 views

Column cannot be converted from type 'int' to type 'bigint(20) unsigned'?

I have a Percona MySQL cluster, with a master and a few slaves. Each of them is running "Ver 14.14 Distrib 5.5.40-36.1" of MySQL. Replication is row based. I ran an alter query on one of the tables ...
4
votes
4answers
2k views

SQL Server - Alter table vs drop and create

Our database is SQL Server 2008 R2. We have some tables that have some varchar(500) columns that I want to switch to datetime2 or bigint. I can guarantee all the data in the columns to be switched are ...
1
vote
1answer
142 views

Rename PK column names when they are FK in another tables

I need to change the names on PK columns and that has relations with another tables meaning they are FK also so this is what I am doing: SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @...
0
votes
1answer
302 views

SET sql_log_bin = 0 in RDS MYSQL 5.6

I have moved into RDS MYSQL 5.6 and now I have a problem. I have 5 read replicas and a/z master. I am trying to alter a table and want to change a column value and also on some tables I want to add a ...
0
votes
2answers
115 views

How to get the second string in the ORA-02296 error to show something meaningful

Whenever I run the following script*: ALTER TABLE ANIMAL.MONKEY MODIFY TAIL_COUNT NOT NULL; I receive the following error: ORA-02296: cannot enable (ANIMAL.) - null values found I have a ...
1
vote
3answers
90 views

guideline for calling optimize table after alter table?

Should I be calling 'optimize table' when changing datatypes of columns, adding indexes or dropping indexes, assuming a table with millions of rows, heavily used, both for reading and writing (InnoDB)....
1
vote
1answer
98 views

Is there a way to add a column to a table without affecting performance?

How do you add a column to a table in production without affecting its performance? Or is this impossible? I'm asking about Postgres.
1
vote
1answer
160 views

Automatic migration failed on Azure Database

I am using Entity Framework 6.1.1. The database has three million records; migration has to change the precision of a column of type decimal. The migration timeout window is set to the maximum value, ...
0
votes
1answer
42 views

Table1 ID is auto increment and i also want to store the information from table 2 against that auto generated ID

This table has an auto increment ID on back end. "INSERT INTO `test`.`personal_info` (`username`, `email`,`password`) VALUES ('$username','$email','$password')"; This was for signing up of one ...
0
votes
1answer
288 views

Table partitioning in MYSQL Master Slave

I have to Partition a MYSQL table using Range partitioning. Table is 200M rows big and I have Master/Slave replication. What I want to do is to stop replication on slave, partition the table on ...
1
vote
2answers
105 views

Recreate a table that was deleted during an ALTER statement (errno: -1)?

For the third time in the past 2-3 weeks I've gotten the following message during an ALTER statement: Error 2013: Lost connection to MySQL server during query SQL Statement: ALTER TABLE `...
0
votes
1answer
151 views

Duration of adding versus dropping a column

I have a table containing more than 50 million rows. When I add a column: ALTER TABLE my_table ADD my_column NUMBER; It's very fast. Just after adding the column, I drop it. ALTER TABLE my_table ...
1
vote
2answers
100 views

How do you minimize downtime for an ALTER?

I've recently inherited a MySQL database that is growing. It's just over a billion records and the performance of one of the tables is abysmal. I would like to change databases eventually, but I do ...
0
votes
0answers
56 views

Is doing an online alter on a MySQL table to add comments safe?

Is doing an online alter on a massive MySQL table to add or edit table or column 'comments' safe? Or do you need to take precautions like you would while adding, removing columns, etc on that table?
1
vote
1answer
375 views

trigger or check constraint insert values based upon existence of others

For simplicity (and since it captures the essence of my problem), suppose I have a table with data. CREATE TABLE my_practice_table ( my_id NUMBER(12), field_2 VARCHAR2(20), field_3 ...
15
votes
2answers
5k views

Changing a column from NOT NULL to NULL - What's going on under the hood?

We have a table with 2.3B rows in it. We'd like to change a column from NOT NULL to NULL. The column is contained in one index (not the clustered or PK index). The data type isn't changing (it's an ...
1
vote
1answer
326 views

MS Access: adding column if not exist (or try/catch it)

this is an easy question, but I can't find out the solution... I have to check if a column exists and add it if the test is negative. How can I do with only sql code? This is the syntax for SQL ...