SQL Function used to alter an existing table object.

learn more… | top users | synonyms

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 ...
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 ...
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 ...
1
vote
0answers
36 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 ...
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
2answers
541 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
61 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
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 ...
0
votes
1answer
281 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
317 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
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 ...
0
votes
1answer
160 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
81 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
53 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
75 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.
7
votes
1answer
160 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 ...
1
vote
1answer
120 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
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
194 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
99 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
286 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 ...
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 ...
1
vote
1answer
244 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
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 ...
1
vote
1answer
129 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 ...
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 ...
5
votes
2answers
657 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
192 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
33 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
81 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
433 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
198 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
2k 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 ...
2
votes
2answers
635 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
412 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
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 ...
1
vote
1answer
94 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
242 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
906 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
1k 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
717 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 = ...
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 ...
2
votes
3answers
520 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'; ...