Hi!
I use mysql/innodb tables on aws services
in a small table (about 2M records) I changed some columns datatypes from
unsigned int to decimal and from float to decimal
I didn't change anything about primary key or other indexes
after the change (done without troubles), all my queries where
Looking at the docs:
http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html
how do I alter the child table, links, so that it has a foreign key with
the parent table, feeds?
The feed_id field in links should, in fact, be constrained by the foreign
key of feeds with a RESTRICT
Hi,
This is an example:
ALTER TABLE cart ADD CONSTRAINT fk_cart_customers FOREIGN KEY (custid)
REFERENCES customers (custid)
ON DELETE CASCADE
ON UPDATE NO ACTION;
(take from the GUI tool Database Workbench, avoids having to know the syntax
;) )
With regards,
Martijn Tonies
Upscene
On 14-11-17 01:42 AM, thufir wrote:
Looking at the docs:
http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html
how do I alter the child table, links, so that it has a foreign key with
the parent table, feeds?
The workbench GUI came up with:
ALTER TABLE `rome_aggregator
On 14-11-17 01:42 AM, thufir wrote:
Looking at the docs:
http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html
how do I alter the child table, links, so that it has a foreign key with
the parent table, feeds?
The workbench GUI came up with:
ALTER TABLE `rome_aggregator
On Mon, 17 Nov 2014 10:59:15 +0100, Martijn Tonies (Upscene Productions)
wrote:
In general, an index is used for performance, while a constraint is
used for logical concepts, like uniqueness. Most often, the DBMS uses an
index to implement these logical concepts, but it's better to understand
Hello,
I'm trying to use a very basic alter table command to position a column
after another column.
This is the table as it exists now:
mysql describe car_table;
+-+--+--+-+-++
| Field | Type | Null | Key | Default | Extra
On 28-06-2014 19:11, Tim Dunphy wrote:
Hello,
I'm trying to use a very basic alter table command to position a column
after another column.
This is the table as it exists now:
mysql describe car_table;
+-+--+--+-+-++
| Field | Type | Null
Cool guys, that did it..
ALTER TABLE car_table MODIFY COLUMN color VARCHAR(10) AFTER model;
For some reason the book I'm following doesn't specify that you have to
note the data type in moves! This helped. and thanks again.
Tim
On Sat, Jun 28, 2014 at 1:24 PM, Carsten Pedersen cars
Hey guys,
Sorry to hit you with one more. But I'm trying to use a positional
statement in a column move based on what you all just taught me:
mysql alter table modify column color varchar(10) sixth;
But I am getting this error:
ERROR 1064 (42000): You have an error in your SQL syntax; check
Hi Tim,
-Original Message-
From: Tim Dunphy [mailto:bluethu...@gmail.com]
Sent: Sunday, 29 June 2014 03:45
Cc: mysql@lists.mysql.com
Subject: Re: alter table modify syntax error
Hey guys,
Sorry to hit you with one more. But I'm trying to use a positional statement
in a column
Tim,
-Original Message-
From: Tim Dunphy [mailto:bluethu...@gmail.com]
Sent: Sunday, 29 June 2014 03:45
Cc: mysql@lists.mysql.com
Subject: Re: alter table modify syntax error
Hey guys,
Sorry to hit you with one more. But I'm trying to use a positional
statement
Hi Tim,
-Original Message-
From: Tim Dunphy [mailto:bluethu...@gmail.com]
Sent: Sunday, 29 June 2014 10:09
To: Jesper Wisborg Krogh
Cc: mysql@lists.mysql.com
Subject: Re: alter table modify syntax error
The syntax sixth is not a supported syntax. You should use the
syntax
because MySQL is a free and
open source version of SQL that's easy to install. But maybe you're right
and they do depart into other syntaxes of SQL. I just don't know where they
got that 'first, second, third, etc' version of the alter table syntax
from. Definitely not sweatin' this detail tho, I am
Hi All.
I would like to change the layout of my production database, I would like
to add a column to an existing table. As I think before the ALTER TABLE
statement all access to the database should be denied/ended, then the ALTER
TABLE should be performed, and then user/applications should
- Original Message -
From: Rafał Radecki radecki.ra...@gmail.com
I would like to change the layout of my production database, I would
like to add a column to an existing table. As I think before the ALTER
TABLE statement all access to the database should be denied/ended
production database, I would like
to add a column to an existing table. As I think before the ALTER TABLE
statement all access to the database should be denied/ended, then the ALTER
TABLE should be performed, and then user/applications should be able to use
the database once again.
My tables
, September 27, 2012 1:20 PM
To: Rick James
Cc: mysql mailing list
Subject: checking progress of alter table on an InnoDB table (Was:
Re:
checking progress of alter table on a MyISAM table)
So we changed the table from MyISAM to InnoDB. I read that the 'undo
log entries
Message-
From: Larry Martell [mailto:larry.mart...@gmail.com]
Sent: Wednesday, September 26, 2012 8:52 AM
To: mysql mailing list
Subject: checking progress of alter table on a MyISAM table
Is there any way to check on the progress of a long running alter table
on a MyISAM table? I know it can
Isn't ALTER a DDL, not DML? So I don't think you would find anything in undo
logs.
-Original Message-
From: Larry Martell [mailto:larry.mart...@gmail.com]
Sent: Thursday, September 27, 2012 1:20 PM
To: Rick James
Cc: mysql mailing list
Subject: checking progress of alter table
I read that here:
http://gabrielcain.com/blog/2009/08/05/mysql-alter-table-and-how-to-observe-progress/comment-page-1/
On Thu, Sep 27, 2012 at 2:26 PM, Rick James rja...@yahoo-inc.com wrote:
Isn't ALTER a DDL, not DML? So I don't think you would find anything in undo
logs.
-Original
-
From: Larry Martell [mailto:larry.mart...@gmail.com]
Sent: Thursday, September 27, 2012 1:20 PM
To: Rick James
Cc: mysql mailing list
Subject: checking progress of alter table on an InnoDB table (Was: Re:
checking progress of alter table on a MyISAM table)
So we changed the table
? So I don't think you would find anything in
undo logs.
-Original Message-
From: Larry Martell [mailto:larry.mart...@gmail.com]
Sent: Thursday, September 27, 2012 1:20 PM
To: Rick James
Cc: mysql mailing list
Subject: checking progress of alter table on an InnoDB table
a DDL, not DML? So I don't think you would find anything in
undo logs.
-Original Message-
From: Larry Martell [mailto:larry.mart...@gmail.com]
Sent: Thursday, September 27, 2012 1:20 PM
To: Rick James
Cc: mysql mailing list
Subject: checking progress of alter table
, September 27, 2012 1:20 PM
To: Rick James
Cc: mysql mailing list
Subject: checking progress of alter table on an InnoDB table (Was:
Re:
checking progress of alter table on a MyISAM table)
So we changed the table from MyISAM to InnoDB. I read that the 'undo
log entries' shown
Am 27.09.2012 23:15, schrieb Larry Martell:
Indexes are slowing this down. your calculation of 79 hours should be
correct, only if there are no unique indexes, otherwise this will slow down
more as the data increases.
It is what it is - there's no free lunch - dropping the indexes, doing
I try to figure out something observing the stats with SHOW STATUS. There are
some reads, writes, etc that tell something about what is going on.
Looking just at the file sizes is likely going to not tell much about the
progress.
If there is a better way to monitor this progress, I would
Is there any way to check on the progress of a long running alter
table on a MyISAM table? I know it can be done with an InnoDB table,
but I haven't found a way to do it on with a MyISAM table.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http
.
-Original Message-
From: Larry Martell [mailto:larry.mart...@gmail.com]
Sent: Wednesday, September 26, 2012 8:52 AM
To: mysql mailing list
Subject: checking progress of alter table on a MyISAM table
Is there any way to check on the progress of a long running alter table
on a MyISAM table? I
2012/09/26 09:31 -0700, Rick James
You could look at the .TYD and .TYI file sizes and compare to the .MYD and
.MYI, but that can be deceptive. If the table is really big, and has lots of
indexes, the generation of the indexes might go slower and slower -- hence any
math on the sizes would be
*summary:*
Recently we hit lost tables during DDL for online products, and after some
observersion, we found some interesting hehaviors if fil_rename_tablespace
failed as retry 25.
That might be another issue that not discussed here.
THERE MUST BE SINGLE TABLESPACE
Here is the steps to show
mysql tables are lost for DDL of alter table .. add column ...
*1. mysqld's error.log*
110803 3:39:16 InnoDB: Warning: problems renaming
'feel_22/#sql-2635_23d3a8' to 'feel_22/feed_send_1451', 25000 iterations
（first 25000，fil0fil.c:: fil_rename_tablespace）
InnoDB: Warning: tablespace
Most alter table operations in 5.0 will rebuild the entire table. The
best thing to increase for alter table speed in innodb is the buffer
pool. For more details on how innodb handles alter table see
http://ebergen.net/wordpress/2007/05/07/how-alter-table-locks-tables-and-handles-transactions
I have been trying to speed up an ALTER TABLE command that adds a
column to a large InnoDB table of about 80M rows.
I have found and tried many different methods, but they are all slow.I
have tried both optimizing the ALTER TABLE
command, and dumping and loading the table (in both SQL and CSV
At 10:34 AM 8/18/2010, Xn Nooby wrote:
minutes to dump the 70M rows. However, it takes the LOAD FILE command
13 hours to import the CSV file. My
understanding of LOAD FILE was that it was already optimized to load
the data, then build the indices afterwords. I
don't understand why it takes so
Nooby [mailto:xno...@gmail.com]
Sent: Wednesday, August 18, 2010 9:34 AM
To: mysql@lists.mysql.com
Subject: Slow ALTER TABLE on 70M row InnoDB table
I have been trying to speed up an ALTER TABLE command that adds a
column to a large InnoDB table of about 80M rows.
I have found and tried many
USE the_database;
DROP TABLE IF EXISTS the_table_clone;
CREATE TABLE the_table_clone LIKE the_table;
# drop minor indices on clone
ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col2_col3;
ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col4_col6;
ALTER TABLE the_table_clone
but not the indexes so you don't
have to drop the indexes below.
# drop minor indices on clone
ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col2_col3;
ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col4_col6;
ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col5_col2
indices on clone
ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col2_col3;
ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col4_col6;
ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col5_col2;
# drop primary index on clone
ALTER TABLE the_table_clone CHANGE id id
It appears the ALTER TABLE starts off quick, and then slows down. I
feel like the indices are larger than allocated RAM, and the system is
slowing down because it is busy swapping out to disk. Is there an
InnoDB specific buffer than can help this? The sort_buffer_size
apparently is only
From what I have read, ALTER TABLE to add an index causes the entire
table to be duplicated, so wouldn't my ALTER TABLE command be
duplicating the work done by the SELECT command?
On Wed, Aug 18, 2010 at 4:50 PM, mos mo...@fastmail.fm wrote:
At 02:52 PM 8/18/2010, Xn Nooby wrote:
Below
Given table: CREATE TABLE testtab (d_col CHAR(4));
Question 1: It appears that there is no harm in just appending
directives onto the alter table command even if the order doesn't make
sense. It appears the parser figures it out... For example...
ALTER TABLE testtab ADD COLUMN c_col char(4
If your table testtab is populated, neither suggestion is efficient.
You could the following instead:
#
# Create an empty table `testtab_copy`
#
1) CREATE TABLE testtab_copy LIKE testtab;
2) Do either of you suggestions:
ALTER TABLE testtab ADD COLUMN c_col char(4) FIRST,ADD COLUMN b_col char
On Fri, May 22, 2009 at 12:26 AM, PJ af.gour...@videotron.ca wrote:
Michael Dykman wrote:
On Thu, May 21, 2009 at 11:06 PM, PJ af.gour...@videotron.ca wrote:
I have a seemingly impossible situation. I cannot insert values into the
tables and I cannot alter or delete the primary key (which
Michael Dykman wrote:
On Fri, May 22, 2009 at 12:26 AM, PJ af.gour...@videotron.ca wrote:
Michael Dykman wrote:
On Thu, May 21, 2009 at 11:06 PM, PJ af.gour...@videotron.ca wrote:
I have a seemingly impossible situation. I cannot insert values into the
tables and I cannot
You do need the foreign keys for integrity and the columns which make
the foreign reference should be indexed as well. My only point is
that bookID is already indexed as the first element in the primary
key, so the additional index on bookID alone is superfluous.
- michael
On Fri, May 22,
I have a seemingly impossible situation. I cannot insert values into the
tables and I cannot alter or delete the primary key (which should not
exist) or delete the foreign keys nor remove the constraint. G search
doesn't help.
CREATE TABLE `book_categories` (
`bookID` smallint(6) unsigned NOT
On Thu, May 21, 2009 at 11:06 PM, PJ af.gour...@videotron.ca wrote:
I have a seemingly impossible situation. I cannot insert values into the
tables and I cannot alter or delete the primary key (which should not
exist) or delete the foreign keys nor remove the constraint. G search
doesn't help.
I cannot insert values into the tables
What is the error message? Has the smallint key run out of values?
and I cannot alter or delete the primary key (which should not exist)
Eh? Without a PK, it ain't a table.
or delete the foreign keys nor remove the constraint. G search doesn't
help.
Michael Dykman wrote:
On Thu, May 21, 2009 at 11:06 PM, PJ af.gour...@videotron.ca wrote:
I have a seemingly impossible situation. I cannot insert values into the
tables and I cannot alter or delete the primary key (which should not
exist) or delete the foreign keys nor remove the
Hello,
we having an 60 GB InnoDB database. The table with the problem is about
12GB.
On of our scripts has got a problem and run 60 times an alter table:
ALTER TABLE `foo` ADD INDEX ( `bar` ) ;
We had to kill the alter table commands with kill on the mysql console.
Now we have one index
Hi Jonas,
When ever an alter table command is executed on mysql tables, mysql creates
a temp file with all the data + the current table. Once the alter table
command is done, it will drop the current table and rename the new temp
file to the current table, that the reason the disk usage goes high
Hi Ananda,
Since u have cancled the job, those in-complete temp files can be
deleted from the file system.
ok - but I'm using InnoDB. The IBdata file is bumped up. There are no
temp files on the database directory.
Greets,
Jonas
--
MySQL General Mailing List
For list archives:
So, now u dont have free space in your file system.
Is this a production db.
I think, restarting the db, should not cause any harm. Which version of
mysql.
regards
anandkl
On 12/4/08, Jonas Genannt [EMAIL PROTECTED] wrote:
Hi Ananda,
Since u have cancled the job, those in-complete temp
: mysql@lists.mysql.com
Sent: Thursday, December 04, 2008 4:09 PM
Subject: Re: Alter Table - InnoDB
So, now u dont have free space in your file system.
Is this a production db.
I think, restarting the db, should not cause any harm. Which version of
mysql.
regards
anandkl
On 12/4/08, Jonas Genannt
Hi Ananda,
So, now u dont have free space in your file system.
Is this a production db.
I think, restarting the db, should not cause any harm. Which version
of mysql.
no free space it not my problem. I have only noticed that ibdata file
uses more space than before. Yes this is an production
Hello Shachi,
I thought you always have to go to the physical location and delete
the tmp files manually. These are created in tmp folder.
I am not sure if restarting helps...
since I am using InnoDB, there is no tmp folder.
The Ibdata file after killing alter table commands:
-rw-rw 1
Hi,
Since u have cancled the job, those in-complete temp files can be
deleted from the file system.
ok - but I'm using InnoDB. The IBdata file is bumped up. There are no
temp files on the database directory.
What's the problem with a larger ibdata-file? If the index is dropped,
there is
Hi jones,
Innodb does not release the space unless you optimize the tables. To dot
that you need to run dummy alter on all tables, by issuing Alter table
table name engine=InnoDB
but the space shall not regaing unless you start the table with
innodb_file_per_table option.
Then if you run
.
Give it a try and let me know if this worked. I know it will work.
-Original Message-
From: Chandru [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 04, 2008 7:10 AM
To: Shachi Govil; Jonas Genannt
Cc: Ananda Kumar; mysql@lists.mysql.com
Subject: Re: Alter Table - InnoDB
Hi jones
Hi Everybody,
I would like to know that, while changing storage engine from one type to
another locks that table or not.
alter table table-name engine=myisam;
alter table table-name engine=innodb;
During the process of changing storage engine from one type to another.
Whether it will work fine
it will lock the table,read and write are blocked during the process.
-原始邮件-
发件人: Krishna Chandra Prajapati [mailto:[EMAIL PROTECTED]
发送时间: 2007年11月19日 15:24
收件人: MySql
主题: Alter table
Hi Everybody,
I would like to know that, while changing storage engine from one type to
another
Chris White wrote:
Jay Paulson wrote:
2) both tables have data in them.
This is most likely your issue then, depending on the table size, go
through and make sure that anything in the referenced column matches the
referencing column. You should also be able to use SHOW INNODB STATUS
to
I really don¹t know what to do because I keep getting this error. Any
ideas?
SQL query:
ALTER TABLE pl_reports ADD CONSTRAINT fk_region FOREIGN KEY ( region )
REFERENCES Region( id ) ON UPDATE CASCADE ON DELETE CASCADE
MySQL said: Documentation
#1005 - Can't create table './survey_localhost
Jay Paulson wrote:
I really don¹t know what to do because I keep getting this error. Any
ideas?
SQL query:
ALTER TABLE pl_reports ADD CONSTRAINT fk_region FOREIGN KEY ( region )
REFERENCES Region( id ) ON UPDATE CASCADE ON DELETE CASCADE
MySQL said: Documentation
#1005 - Can't create table
1) both tables are InnoDB.
2) both tables have data in them.
3) both table are the exact same data types.
On 2/20/07 3:51 PM, Chris White [EMAIL PROTECTED] wrote:
Jay Paulson wrote:
I really don¹t know what to do because I keep getting this error. Any
ideas?
SQL query:
ALTER TABLE
Jay Paulson wrote:
2) both tables have data in them.
This is most likely your issue then, depending on the table size, go
through and make sure that anything in the referenced column matches the
referencing column. You should also be able to use SHOW INNODB STATUS
to see what's possibly
David Sparks wrote:
I want to move 3 100GB .ibd files into a new DB.
I followed the instructions here:
http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html
But it doesn't work:
mysql alter table reports discard tablespace;
Query OK, 0 rows affected (0.04 sec)
mysql
I want to move 3 100GB .ibd files into a new DB.
I followed the instructions here:
http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html
But it doesn't work:
mysql alter table reports discard tablespace;
Query OK, 0 rows affected (0.04 sec)
mysql alter table reports import
expected?
I don't really understand how the alter table add column is done, but
when I look at the show processlist I see that it says the state is
copying into tmp table
Does the alter table mean that MySQL has to copy the Entire table,
row-by-row into a temporary table, and add
it says this is done so that other read processes can still
access the DB/table in it's OLD state w/o any hiccups.
I do not understand
so how does the MSSQL DB work when we alter table add column* *
2006/10/22, Ow Mun Heng [EMAIL PROTECTED]:
On Fri, 2006-10-20 at 09:06 -0700, William R
On Sun, 2006-10-22 at 21:32 +0800, 黄小聪 wrote:
it says this is done so that other read processes can still
access the DB/table in it's OLD state w/o any hiccups.
I do not understand
so how does the MSSQL DB work when we alter table add column
Frankly, I don't really know. I tried adding
On Thu, October 19, 2006 18:24, Ow Mun Heng said:
Just curious to know,
I tried to update a table with ~1.7 million rows (~1G in size) and the
update took close to 15-20 minutes before it says it's done.
Is this kind of speed expected?
I don't really understand how the alter table add
Just curious to know,
I tried to update a table with ~1.7 million rows (~1G in size) and the
update took close to 15-20 minutes before it says it's done.
Is this kind of speed expected?
I don't really understand how the alter table add column is done, but
when I look at the show processlist I
Just curious to know,
I tried to update a table with ~1.7 million rows (~1G in size) and the
update took close to 15-20 minutes before it says it's done.
Is this kind of speed expected?
I don't really understand how the alter table add column is done, but
when I look at the show processlist I
On Wed, 2006-10-18 at 09:29 +0800, Ow Mun Heng wrote:
Just curious to know,
I tried to update a table with ~1.7 million rows (~1G in size) and the
update took close to 15-20 minutes before it says it's done.
Is this kind of speed expected?
I don't really understand how the alter table
Hi,
Try this:
To modify an existing field:
ALTER TABLE doc MODIFY docts timestamp DEFAULT CURRENT_TIMESTAMP;
or
To add new field
ALTER TABLE doc ADD docts timestamp DEFAULT CURRENT_TIMESTAMP;
Thanks,
ViSolve DB Team.
- Original Message -
From: Peter Lauri [EMAIL PROTECTED
Hi,
I am doing this thru the phpmyadmin interface:
ALTER TABLE doc ALTER docts SET DEFAULT CURRENT_TIMESTAMP
However, it returns #1064 - You have an error in your SQL syntax near
'DEFAULTCURRENT_TIMESTAMP' at line 1
As you can see the error shows that DEFAULT an CURRENT_TIMESTAMP
: Monday, 19 June 2006 10:36 a.m.
To: Graham Reeds
Cc: mysql@lists.mysql.com
Subject: Re: Alter table command don't work
Graham Reeds wrote:
Quentin Bennett wrote:
http://dev.mysql.com/doc/refman/4.1/en/alter-table.html
From MySQL 4.1.2 on, if you want to change the table default
character set
Using the text mode mysql client program I tried to alter a table - but
received a syntax error. Why?
mysql alter table blog convert to character set utf8;
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax
Graham Reeds wrote:
Using the text mode mysql client program I tried to alter a table - but
received a syntax error. Why?
This is using MySQL 4.0.25 btw.
mysql alter table blog convert to character set utf8;
ERROR 1064: You have an error in your SQL syntax. Check the manual
http://dev.mysql.com/doc/refman/4.1/en/alter-table.html
From MySQL 4.1.2 on, if you want to change the table default character set and
all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a
statement like this:
ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name
Quentin Bennett wrote:
http://dev.mysql.com/doc/refman/4.1/en/alter-table.html
From MySQL 4.1.2 on, if you want to change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this:
ALTER TABLE tbl_name CONVERT TO CHARACTER
Graham Reeds wrote:
Quentin Bennett wrote:
http://dev.mysql.com/doc/refman/4.1/en/alter-table.html
From MySQL 4.1.2 on, if you want to change the table default
character set and all character columns (CHAR, VARCHAR, TEXT) to a
new character set, use a statement like this: ALTER TABLE
of a working ALTER TABLE T1
. command it would be greatly appreciated.
Thank you.
Bob Goodwin Zuni, Virginia
# uname -a
Linux box3 2.6.11-1.1369_FC4
#1 Thu Jun 2 22:55:56 EDT 2005 i686 athlon i386 GNU/Linux
mysql select version();
+---+
| version
provide an example of a working ALTER TABLE T1
. command it would be greatly
appreciated.
Thank you.
Bob Goodwin Zuni, Virginia
# uname -a
Linux box3 2.6.11-1.1369_FC4
#1 Thu Jun 2 22:55:56 EDT 2005 i686 athlon i386 GNU/Linux
mysql select version
-file) and piped that into the first of the new servers.
I then startet doing alter table abc type=ndb-queries and everything looked
fine at the beginning. After having moved 70-80% of the tables into the NDB-Engine (they
all show up correctly on the other mysql-server and everything seems to work) I
one of my table columns is unique. i mean cant av duplicate contents. How do
i remove this?
ALTER TABLE db.table DROP INDEX nameOfTheIndex;
If you don't know nameOfTheIndex use:
Show create table db.table
Regards!
-Mensaje original-
De: Yemi Obembe [mailto:[EMAIL PROTECTED]
Enviado el: Lunes, 02 de Enero de 2006 10:03 a.m.
Para: mysql@lists.mysql.com
Asunto: Alter table
Confused about how ALTER TABLE foo ADD col2... operates.
On an otherwise idle server, iostat 1 shows the disk being written to at a
rate of 15MB/sec (its capacity is about 50MB/sec), but the ibdata-autoextend
file only grows at a rate of 500kb/sec.
12GB table (w/ 1.5GB index) took 6
)
mysql alter table dt change a a decimal(2,1);
Query OK, 1 row affected, 1 warning (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 1
mysql show warnings;
+-+--+-+
| Level | Code | Message
In article [EMAIL PROTECTED],
Martijn Tonies [EMAIL PROTECTED] writes:
mysql insert into dt set a=999.1;
Query OK, 1 row affected (0.00 sec)
mysql alter table dt change a a decimal(2,1);
Query OK, 1 row affected, 1 warning (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 1
mysql show
mysql insert into dt set a=999.1;
Query OK, 1 row affected (0.00 sec)
mysql alter table dt change a a decimal(2,1);
Query OK, 1 row affected, 1 warning (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 1
mysql show warnings
Martijn Tonies wrote:
| Warning | 1264 | Out of range value adjusted for column 'a' at row 1
Could be me ... but isn't this a little too late?
eg: AFTER you have lost your data?
IMO, it should raise an error UNLESS you force it to truncate the data.
This would contradict the MySQL design
alter table dt change a a decimal(2,1);
Query OK, 1 row affected, 1 warning (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 1
mysql show warnings;
+-+--+-+
| Level | Code | Message
Hello.
If a value doesn't fit (in the domain of tinyint), an exception
should be raised. Plain and simple.
MySQL 5.0 has this ability. Check STRICT_ALL_TABLES and
STRICT_TRANS_TABLES SQL modes at:
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
CREATE
Hello.
I don't think that it is possible to recover the data from the new
table, because ALTER operation creates a new table and fills it with
data (and truncates the data). But why did the system backup not help
you? Could you describe the situation more in detail. If you haven't
flushed
Gleb,
I don't think that it is possible to recover the data from the new
table, because ALTER operation creates a new table and fills it with
data (and truncates the data). But why did the system backup not help
you? Could you describe the situation more in detail. If you haven't
flushed you
)
mysql alter table dt change a a decimal(2,1);
Query OK, 1 row affected, 1 warning (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 1
mysql show warnings;
+-+--+-+
| Level | Code | Message
My bad. I was renaming some columns in a table. I incorrectly set the
type to decimal(4,2) and the data was truncated/hosed. Is there a way to
recover the data. I tried a system backup from yesterday. That changed
nothing. I have already set the column type back to the correct
settings. I am
1 - 100 of 358 matches
Mail list logo
