SQL Function used to alter an existing table object.

learn more… | top users | synonyms

0
votes
1answer
308 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
1answer
33 views

TokuDB Hot Column Expansion

I need to expand a varchar field length from 255 to 4000. I am using tokudb_version: tokudb-7.5.8 running on Linux 3.16.0-60-generic #80~14.04.1-Ubuntu SMP Wed Jan 20 13:37:48 UTC 2016 x86_64 x86_64 ...
0
votes
3answers
543 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 ...
1
vote
1answer
37 views

How to set column DEFAULT using value of a variable

In a script for setting up a database which uses random UUIDs for primary keys, I have: CREATE TABLE alpha (id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), value INT); CREATE TABLE beta (aref UUID ...
4
votes
5answers
18k views

MySQL - fastest way to ALTER TABLE for InnoDB

I have an InnoDB table that I want to alter. The table has ~80M rows, and quit a few indices. I want to change the name of one of the columns and add a few more indices. What is the fastest way to ...
1
vote
2answers
59 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
47 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 ...
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 ...
0
votes
1answer
52 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 ...
3
votes
1answer
45 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
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 ...
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; ...
17
votes
3answers
6k views

How can I add a rowversion column to a large table with minimal downtime

Using SQL Server 2008 and later, I want to add a rowversion column to a large table however when I simply ALTER TABLE [Tablename] ADD Rowversion [Rowversion] NOT NULL Then the table is unavailable ...
24
votes
1answer
8k views

How do I swap tables in MySQL?

Suppose, I have a table foo, which contains some statistics that are computed every now and then. It is heavily used by other queries. That's why I want to compute more recent statistics in foo_new ...
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
21 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
47 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
161 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
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
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 `...
44
votes
5answers
32k views

Why does ALTER COLUMN to NOT NULL cause massive log file growth?

I have a table with 64m rows taking 4.3 GB on disk for its data. Each row is about 30 bytes of integer columns, plus a variable NVARCHAR(255) column for text. I added a a NULLABLE column with data-...
2
votes
1answer
158 views

Table size is not changed after new column is added

I am trying to check how the table size is going to be increased after adding new column with default values. I use the following default values: NULL, ''(empty string) and some text(some random text)...
3
votes
1answer
117 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 ...
0
votes
1answer
93 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 `...
1
vote
1answer
86 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
99 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
101 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
50 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 ...
15
votes
2answers
8k views

I need to run VACUUM FULL with no available disk space

I have one table that is taking up close to 90% of hd space on our server. I have decided to drop a few columns to free up space. But I need to return the space to the OS. The problem, though, is ...
1
vote
1answer
105 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 ...
3
votes
3answers
973 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
1answer
161 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, ...
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 ...
5
votes
3answers
8k 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 #...
1
vote
1answer
333 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
54 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 ...
15
votes
2answers
6k 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 ...
3
votes
2answers
7k views

Modify all tables in a database with a single command

Is there a single or a one line command to modify all tables within a database. I would like to issue this command in every table within a database: ALTER TABLE `table_name` CONVERT TO CHARACTER SET ...
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 ...
0
votes
2answers
147 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 ``` I got syntax error, which is understandable. So, what should I do to delete this column whose name is ...
1
vote
0answers
47 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
81 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 ...
2
votes
2answers
7k views

How long will it take to rename table

How long will it take to rename a table with a size of 219 GB? This is the specification of our server: Memory: 65 GB Available disk space: 266 GB MySQL Ver 14.12 Distrib 5.0.77 Sorry for the ...
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 ...