SQL Function used to alter an existing table object.

learn more… | top users | synonyms

0
votes
1answer
27 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 ...
0
votes
1answer
38 views

Alter column opreation failed due to disk space in SQL Server

When i apply alter column on table, it full log file and disk too, and fails alter operation. Why this happened even table size is less than free disk? Can anyone please suggest a way to complete this ...
0
votes
0answers
9 views

Granting permission on an altered table in Teradata

I have a table, which has two columns say A and B as part of the Primary index PI_AB. I have to alter the length of B column - increase it. I was planning to alter the primary index to just have A, ...
3
votes
1answer
76 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 ...
4
votes
2answers
315 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 ...
1
vote
2answers
26 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
20 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
24 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
65 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
63 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
117 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 ...
1
vote
2answers
76 views

Is it safe to cancel a PostgreSQL ALTER TABLE query that is waiting on a lock?

We started an ALTER TABLE query hours ago and only recently realized (via pg_stat_activity) that it is waiting on a lock. We discovered the other query that is holding a lock on the table we want to ...
1
vote
1answer
65 views

Cannot add a column to an InnoDB table in Mariadb

I have this InnoDB table with less than 10 rows in mysql 5.5.40-MariaDB on Linux (x86_64). +-----------------+--------------+------+-----+-------------------+----------------+ | Field | ...
1
vote
3answers
85 views

Unable to find how a table is being populated

Using SSMS2014 w/ SQL Server 2008 R2. I have a table that I am trying to find out how and from where data inside it is coming from. (I didn't create the table and am unable to contact the person who ...
0
votes
1answer
16 views

Alter doesn't show in processlist

I'm performing an alter. It's currently running on a large table (300,000,000). MariaDB [my_database]> ALTER TABLE my_table -> add INDEX a (x, y, z), -> add INDEX d (x); Stage: 1 of ...
1
vote
2answers
83 views

T-SQL — Why do ALTER and UPDATE statement only run when separated into their own batch?

I have update and alter statements that are only running when they are put into they're own batch. Below is my shortened version. It'd probably be a good idea to skip the code and read my question ...
1
vote
1answer
70 views

Dropping multiple KEYS of a table

Dropping multiple KEYS in a table is a preferred option or one by one is preferred? “DROP KEY A,DROP KEY B,DROP KEY C" For more 900 million records in the table. How does it work internally?
3
votes
1answer
249 views

Alter table and update in sql server transactions

I'm trying to do an alter table to add a new column on a table, then I would like to do some update on the existing rows in the table to add a value to the recently new added column and finally do a ...
1
vote
1answer
80 views

How can one drop all default values and allow NULL for all columns of a table?

I'm working on a PHP script to modify the primary key (PK) of a table. My current code is: $q = "SHOW KEYS FROM $db.$table WHERE Key_name='PRIMARY';"; $keys = Query ( $q); if ( $keys) { $q = ...
3
votes
2answers
1k views

Update statement is giving an invalid column name error, even though the column exists

I have a temporary table created at the beginning of this stored procedure. It is created successfully and can be selected from and inserted to. Here is the create statement CREATE TABLE ...
2
votes
2answers
70 views

Cannot ALTER TABLE

I have a particular table that I cannot add a key for: mysql> ALTER TABLE tasks ADD KEY `fruitful_user_count` (`user_id`, `is_fruitful`); ERROR 1034 (HY000): Incorrect key file for table 'tasks'; ...
1
vote
0answers
31 views

Prevent truncate without denying ALTER permission

Is there a way to disallow users from truncating a table without denying ALTER permission on the table? Users should be able to maintain the standard ALTER permissions (adding columns to a table, ...
0
votes
0answers
75 views

How alter on infobright (BRIGHTHOUSE storage engine) actually works

I have been using infobright-3.4.1 (5.1.40) for data warehousing. I know it is outdated version but there no plan to upgrade the version currently. Even though infobright is build on mysql, the way it ...
0
votes
2answers
115 views

Add ENum Attribute to existing table using Alter

I am using oracle XE and trying to add a attribute that restricts to only three available value 'Small','Medium','large' to an existing table using Alter Table and Enum. Tried doing, ALTER TABLE ...
0
votes
0answers
114 views

How to avoid 2013 error when executing ALTER TABLE

I have a huge table (2.8 GB) and I need to add a column. After I run this query I experience a lost connection error (Error code: 2013. Lost connection to MySQL server during query): ALTER TABLE ...
0
votes
0answers
60 views

How to diagnose MariaDB crash when changing InnoDB column to AUTO_INCREMENT?

I have this table, and I'm trying to change the first column to AUTO_INCREMENT: +------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | ...
0
votes
1answer
117 views

postgresql replace table without losing dependancies

i want to replace an existing table with a new one, without losing foreign keys or inheritance relations. But I feel lazy to alter it to make it look like the new one. Is there a way to replace the ...
0
votes
0answers
38 views

How to combine a default value with a new value?

I need to start the value of a column with CON, so when I insert a new value, it needs to "combine" both default and new value; for example: In the CONTACTO_ASP2_TAB table, the column NOMBRE has to ...
0
votes
1answer
47 views

Fastest way to alter column type in a 2-billion+ records innodb table

I have a MySQL table with 4 columns. The 1st column record_id is of type int, primary key, and auto-incremented. Now that I have added more than 2 billion records to this table I am finding that I ...
1
vote
0answers
43 views

pt-online-schema-change with circular replication

So I have an alter table (adding 4 columns) to do on a fairly large table (74 million rows). The problem is this table has firstly to remain online and writable during the whole operation and ...
7
votes
1answer
111 views

Adding nullable column to table costs more than 10 minutes

I have problems to add a new column on a table. I tried to run it a couple of times, but after more than 10 minutes running, I decided to cancel the query because of lock time. ALTER TABLE mytable ...
0
votes
0answers
57 views

PostgreSQL: How can I identify the connections which is blocking ALTER TABLE?

I would like to add some fields to a table in all testing and production DBs, all of which are stored in PostgreSQL 9.3 on Amazon RDS. ALTER TABLE users DROP COLUMN IF EXISTS device_language; ALTER ...
0
votes
2answers
52 views

How to delete a column named grave accent in MySQL

As title. Every time when i try to execute the query: ALTER TABLE tablename DROP COLUMN ```, got syntax error, which is understandable. So, what should i do to delete this column whose name is the ...
-1
votes
1answer
86 views

Does changing the collation of a column change TIMESTAMP (rowversion) values?

I need to run ALTER TABLE ... ALTER COLUMN ... COLLATE Czech_CI_AS ... for multiple columns in a database. All those tables have TIMESTAMP column which is used for database synchronization. Will ...
0
votes
1answer
55 views

Apparantly errorneous Exceeds 8K row length message

In attempting to alter an SQL Server table adding DATE and FLOAT columns. I get a message: Warning: The table "CustomerTransactions" has been created, but its maximum row size exceeds the allowed ...
0
votes
1answer
81 views

Altering a table to add a New Column vs Create table with New Column

I have Table A which i'm tasked to add a new column is_fully_complete bit(1), and i'm thinking: Would it be better to a.) Create a new table Table_A_new like Table_A and add the new column there ...
2
votes
2answers
588 views

Changing data type from mediumint to int without downtime

My current table is using MEDIUMINT for ID column ‘primary key auto increment’. The current table records soon will reach the maximum value for mediumint which is 8388607. That being the case, I need ...
1
vote
0answers
181 views

SQL Server : ALTER TABLE tablename SWITCH PARTITION 2 to tempempty

Have a sliding partition where I need to remove the trailing partition when they expire. I create an empty table that matches the existing table. When I create the empty table, I specify the file ...
2
votes
1answer
694 views

Auto_increment value needs to be set as S1

Hi I need to set auto increment for a table as S1. I tried by altering the table and also by creating by new table I am aware how to set auto_increment but when i try to set auto increment as S1 i am ...
3
votes
3answers
1k views

Adding non-nullable NEWSEQUENTIALID() column to existing table

In SQL Server 2008, I am adding a non-nullable column to an existing table, and want to make the default NEWSEQUENTIALID(). ALTER TABLE MyTable ADD MyColumn UNIQUEIDENTIFIER NULL CONSTRAINT ...
1
vote
1answer
182 views

Why does adding a column to a MySQL table take incredibly long?

I am using MySQL version 5.6 and I have a database that is in total around 110 GB in size. One of the tables, "outputs" is 49 GB in size. I tried to add a simple BOOL column to the outputs table with ...
0
votes
2answers
100 views

Alter auto-increment of table with 16 million rows

Learned this the hard way. Need to alter a table's auto-increment with 16 million rows. Now, it's taking quite sometime. Any estimate how long it would take? Sorry.
0
votes
1answer
573 views

How to add a column in table via stored procedure? [closed]

create or replace procedure SP_PARAMETER_TEST -- this is my table ( NAME_KEY in VARCHAR2 -- this contains a value assume NAME_KEY contains "Gypsum" ) is begin ALTER TABLE PARAMETER_TEST ADD ...
1
vote
1answer
174 views

MySQLSyntaxErrorException on ALTER TABLE … CONVERT TO … in MySQL Connector/J

Running Ubuntu 14.04 with mysql-connector-java-5.1.28 installed. Let's create a table: CREATE TABLE mytable (value1 VARCHAR(32000), value2 TEXT, value3 VARCHAR(1000), value4 VARCHAR(32000)) ...
1
vote
1answer
42 views

Should I add it as an additional column or a seperate table?

I have a table in my SQL Server that's called "Regions", it has information about 9 regions and the 10th row holds the info about our Division. Now that we will be merging another 2 divisions with its ...
1
vote
1answer
75 views

How does the alter command work internally when adding a column?

Can anyone explain what the internal operations are that takes place when an alter command is issued? How is column modified? Does it use created_tmp_tables or created_tmp_files? As per my test, I ...
0
votes
1answer
159 views

MySQL 'copying to tmp table' while enlarging VARCHAR

One user called today because he has tried to enlarge a VARCHAR column (from 20 to 100) in a slightly large table resulting in a write-locked table. I watched PROCESSLIST and saw that ALTER TABLE ...
3
votes
1answer
241 views

Add NOT NULL constraint to large table without table scan

Trying to add a NOT NULL constraint to a table with 1 billion rows. I cannot afford a table lock for more than a couple of seconds. Is there a way to prevent a full table scan during the alter table ...
3
votes
3answers
2k views

Change column type from varchar to text in all tables at once

I am using Postgres. I would like to change column type in all tables where column name is "description" from varchar(255) to text. If anyone knows that I would be very glad for your help.
0
votes
2answers
3k views

Moving a table to a different tablespace in Oracle using Online Redefinition Vs Alter table move tablespace

I have a table with 1 Billion records with size 300GB to move to different tablespace. My question here is - What is the advantage of using DBMS_REDEFINITION (Online redefinition) package over Alter ...