SQL Function used to alter an existing table object.

learn more… | top users | synonyms

1
vote
1answer
60 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 ...
2
votes
3answers
516 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'; ...
0
votes
1answer
157 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 ...
1
vote
1answer
119 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
3answers
432 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 ...
40
votes
5answers
26k 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 ...
1
vote
2answers
531 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
0answers
21 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
6k 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
239 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
35 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 ...
13
votes
2answers
2k 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
5k 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
20 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
126 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
35 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
44 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 ...
13
votes
2answers
6k 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
2answers
6k 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 ...
4
votes
4answers
15k 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 ...
0
votes
1answer
45 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
1answer
128 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 ...
2
votes
3answers
1k views

Changing a column's data type fills the transaction log

Running this code: ALTER TABLE npidata ALTER COLUMN npi varchar(20) Gives this error: Msg 9002, Level 17, State 4, Line 2 The transaction log for database 'SalesDWH' is full. To find out why ...
1
vote
2answers
59 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
58 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 ...
7
votes
1answer
158 views

Error in partition switching in Sql Server 2014 when migrating a database from Sql Server 2005

I am migrating an old database from SQL Server 2005 to SQL Server 2014. This database has a partitioned table; a scheduled job periodically moves old data from the "online" table to the the historical ...
2
votes
1answer
479 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 ...
11
votes
3answers
27k views

Reset IDENTITY value

I have a table with an IDENTITY column. While developing I delete the rows from time to time and add them again. But the IDENTITY values always kept increasing and didn't start from 1 when I added ...
4
votes
1answer
314 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 ...
0
votes
1answer
275 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
4answers
1k 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
89 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 ...
3
votes
1answer
681 views

Alter mysql database engine doesn't work from cluster to other

I got into this issue when I tried to alter my database table engine to InnoDB from ndbcluster, It did not work. But When I changed engine from InnoDB to ndbcluster it worked. It did not throw any ...
0
votes
2answers
80 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
52 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
74 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.
0
votes
1answer
39 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
193 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 ...
0
votes
1answer
81 views

How do I 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
97 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
75 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
50 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
3k views

H2 ALTER TABLE ADD COLUMN BEFORE/AFTER

According to H2 syntax documentation available here I should be able to execute a query like: ALTER TABLE EMPLOYEE ADD COLUMN SECOND_NAME VARCHAR (124) AFTER FIRST_NAME; However apparently only ...
2
votes
1answer
448 views

Mysql HY000 the table table_name is full when adding new column

I can't add a new column innodb table which size's 16MB. when I try to add, mysql says HY000 the table table_name is full. I changed my tmp_table_size and heap_size but mysql still says same error. ...
1
vote
2answers
322 views

How to recover previous values in Mysql Table?

I have MySQL Innodb table of 14M rows. There was a column called is_disabled which had different values. By mistake I ran the query without the where clause: update url_queue set is_disabled=1 ...
1
vote
1answer
285 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 ...
1
vote
3answers
2k 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
31 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
0answers
69 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
103 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 ...