SQL Function used to alter an existing table object.

learn more… | top users | synonyms

6
votes
3answers
116 views
+150

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
48 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
30 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
64 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
72 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
26 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
47 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
50 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
18 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 ...
1
vote
1answer
32 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 ...
0
votes
0answers
48 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
33 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
43 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
29 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
44 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
65 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
58 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
88 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
81 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
60 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
496 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
530 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
131 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
239 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
100 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
75 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 ...
1
vote
1answer
94 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
145 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
41 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
251 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
101 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
138 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
90 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
354 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 ...
14
votes
2answers
4k 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
290 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 ...
0
votes
1answer
32 views

Dropped column still has value when recreated with Postgres table of 150M rows

I need to set the column to NULL. Until now, this has worked, but for some reason, on this table which is much larger than the rest, it doesn't seem to be working: ALTER TABLE ...
1
vote
1answer
150 views

Alter column opreation failed due to disk space in SQL Server

I am attempting to alter a column on a specific table; however the process is failing because the log file runs out of space once the disk runs out of space. The size of the table is less than the ...
3
votes
1answer
115 views

Will dropping text column on SQL Server 2014 lock the table?

I have been tasked to drop a text column from a production SQL Server 2014 table. The table contains 61,683,821 records. Question: will dropping the column lock the table (or cause similar ...
5
votes
2answers
804 views

Truncating table without using Alter Table permission

So my DBA don't want developers to give Alter table permission. Now one of the procedure that we created requires to Truncate tables (since apparently delete only removes records, but truncate frees ...
2
votes
2answers
274 views

How pt-online-schema-change ensures consistency during data copy

I have read pt-online-schema-change documentation and understood that it works by creating triggers and copying data in chunks with in chunk-time seconds(0.5 seconds in default) Let us consider this ...
2
votes
1answer
39 views

How can I add a numeric version_id column to an existing table?

I have a table in a postgres db with rows versioned with a timestamp that I'd like to add a numeric version_id column to. eg. existing: ID VERSION_DATE -- ----------------------- A 2015-02-05 ...
1
vote
0answers
95 views

MySQL Cluster - 'ONLINE' is deprecated Please use ALGORITHM=INPLACE

I'm just testing MySQL CLuster 7.3.8. According to the documentation, after adding new Data Nodes I need to ALTER my tables to spread the data to the new Data Nodes. So, I run: ALTER ONLINE TABLE ...
0
votes
3answers
510 views

MySQL - Alter Table Fails As Workbench Tries To Create Existing Table

I have been trying to add another column to a table in my database using MySQL Workbench. This generates an 'ALTER TABLE' command which then fails as it fails attempting a 'CREATE TABLE, which it ...
0
votes
1answer
292 views

Unable to update or insert values table with DateTime column defaulted to GETDATE

I have atlered table by adding Datetime column. This column is not null and also default value from GETDATE(). ALTER TABLE myTable ADD myDateColun DATETIME NOT NULL DEFAULT (GETDATE()) I'm trying ...
1
vote
2answers
3k views

2013. Lost connection to MySQL server during query error

I have a table with close to 20 million rows. In phpMyAdmin, I attempt to add a new column: ALTER TABLE `myTable` ADD `newColumn` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0'; After about 60 secs ...