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 r
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 unde
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
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
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 re
es MySQL for
all it's examples and explains that it does so 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'
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"
M, Jesper Wisborg Krogh
wrote:
> 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
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 tryi
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 synta
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
wr
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 | Typ
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
tabase, 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
- Original Message -
> From: "Rafał Radecki"
>
> 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/ende
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 be
ing the secondary indexes after
>> > the
>> > data import or such alters are complete.
>> >
>> > Regards,
>> > Akshay Suryavanshi
>> >
>> >
>> > On Fri, Sep 28, 2012 at 1:56 AM, Rick James
>> > wrote:
>> >>
>> &
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 li
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, do
you carry this operation by adding the secondary indexes after
>> > the
>> > data import or such alters are complete.
>> >
>> > Regards,
>> > Akshay Suryavanshi
>> >
>> >
>> > On Fri, Sep 28, 2012 at 1:56 AM, Rick James
>&
ndo 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
> >> >
ote:
>>
>> 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
&
nal 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 (Was: Re:
> > checking progress of a
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 wrote:
> Isn't ALTER a DDL, not DML? So I don't think you would find anything in undo
> logs.
>
>&
: 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 in 'show engine innodb status' would correspond to
&g
e sizes would be optimistic.
>
>> -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
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 w
.
> -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 alte
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:
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_tabl
trx_general_rollback_for_mysql(trx, NULL);
3909 trx->error_state = DB_SUCCESS;
3910 goto funct_exit;
3911 }
2.3 client's output:
root@sbtest 05:00:24>alter table sb1 add column d2 int;
Query OK, 0 rows affected (9 min 47.9
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/
On
>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 wrote:
> At 02:52 PM 8/18/2010, Xn Nooby wrote:
>>
>
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 i
not the indexes so you don't
> have to drop the indexes below.
That is good to know. I did not mind dropping the indices in this
case, because the table was still empty.
>
>
>
>> # drop minor indices on clone
>>
>> ALTER TABLE the_table_clone DROP INDEX IX_
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
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
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
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
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
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
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 COLU
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, 2009
Michael Dykman wrote:
> On Fri, May 22, 2009 at 12:26 AM, PJ wrote:
>
>> Michael Dykman wrote:
>>
>>> On Thu, May 21, 2009 at 11:06 PM, PJ wrote:
>>>
>>>
I have a seemingly impossible situation. I cannot insert values into the
tables and I cannot alter or delete the prima
On Fri, May 22, 2009 at 12:26 AM, PJ wrote:
> Michael Dykman wrote:
>> On Thu, May 21, 2009 at 11:06 PM, PJ 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 for
Michael Dykman wrote:
> On Thu, May 21, 2009 at 11:06 PM, PJ 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
>>
>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.
On Thu, May 21, 2009 at 11:06 PM, PJ 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.
>
> CREATE TABLE `b
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 NUL
5 GB) due to wasted space from transactions
and old data pages and old index pages.
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
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
engine=InnoDB"
but the space shall not regaing unless you start the table with
"innodb_file_per_table" option.
Then
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 free
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 comm
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 producti
nt" <[EMAIL PROTECTED]>
Cc:
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
O
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 te
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: http
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
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 on the
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
Hi Everybody,
I would like to know that, while changing storage engine from one type to
another locks that table or not.
alter table engine=myisam;
alter table engine=innodb;
During the process of changing storage engine from one type to another.
Whether it will work fine for an online system
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 s
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 f
gt;> 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 './sur
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
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
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;
&g
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 rep
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
"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 0
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 see that it says the state is
> > "copying into tmp table"
> >
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
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
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 tab
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
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 P
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
Sent: 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 yo
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
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
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 cha
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 t
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 syn
up the table have all been via the mysql command line.
If someone could 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
en via the mysql command line.
If someone could 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
-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 see
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
Asun
one of my table columns is unique. i mean cant av duplicate contents. How do
i remove this?
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
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
>>
9.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 phil
> 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 Duplic
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
t 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;
> +-+--+
ow 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;
+-+--+-+
| L
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
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
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 runnin
1 - 100 of 374 matches
Mail list logo
