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`.`link
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`.`link
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 Prod
> 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
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 syntax; check t
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
wrote:
> On 28
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 |
Have you used pt-online-schema-change.html from
http://www.percona.com/doc/percona-toolkit/2.0/pt-online-schema-change.html ?
What do you think about this tool?
Best regards,
Rafal Radecki.
2013/7/8 Rafał Radecki
> Hi All.
>
> I would like to change the layout of my production database, I woul
- 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/ended, then the
> ALTER TABLE should
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(
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
manually. These are created in tmp folder.
>
> I am not sure if restarting helps...
>
> Regards,
> Shachi Govil
> - Original Message - From: "Ananda Kumar" <[EMAIL PROTECTED]>
> To: "Jonas Genannt" <[EMAIL PROTECTED]>
> Cc:
> Sent: Th
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 commands:
-rw-rw
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.
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
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:
>>
>>
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 '.
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)
>
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
"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. Mu
On Fri, 2006-10-20 at 09:06 -0700, William R. Mussatto wrote:
> 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 kin
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
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 ta
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]>
To:
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 tbl_na
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
rset_name;
-Original Message-
From: news [mailto:[EMAIL PROTECTED] Behalf Of Graham Reeds
Sent: Monday, 19 June 2006 8:53 a.m.
To: mysql@lists.mysql.com
Subject: Re: Alter table command don't work
Graham Reeds wrote:
> Using the text mode mysql client program I tried to al
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 that
correspo
bobgoodwin wrote:
I am running MySql 4.1.6 in FC-4 Linux amd it is a huge learning
exercise for me!
I am using O'Reilly's MYSQL Cookbook, have gone through numerous
pages of the "reference manual" and stuff on Google but still can get
the proper commands and syntax to alter the following
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
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
>>
> > As a sidenote - shouldn't MySQL raise an error when data gets
>truncated?
>
> MySQL raises a warning after such ALTER operation. See:
>
> mysql> create table dt(a decimal(4,1));
> Query OK, 0 rows affected (0.01 sec)
>
> mysql> insert into dt set a=999.1;
> Query OK, 1 row affected (0.00 sec)
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 Duplicates: 0 Warnings: 1
> >>
> mysql> show warnings;
> >>
+-+--+--
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
>>
Gleb,
> > As a sidenote - shouldn't MySQL raise an error when data gets
>truncated?
>
> MySQL raises a warning after such ALTER operation. See:
>
> mysql> create table dt(a decimal(4,1));
> Query OK, 0 rows affected (0.01 sec)
>
> mysql> insert into dt set a=999.1;
> Query OK, 1 row affected (0.00
Hello.
> As a sidenote - shouldn't MySQL raise an error when data gets >truncated?
MySQL raises a warning after such ALTER operation. See:
mysql> create table dt(a decimal(4,1));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into dt set a=999.1;
Query OK, 1 row affected (0.00 sec
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
Incoming from Arno Coetzee:
> sorry ... bit busy on this side... had a quick look...
>
> give this a go...
>
> alter table MEMBERS
> MODIFY MEMBER_INFO varchar(160);
>
> hope this works
It did. Much appreciated.
--
Any technology distinguishable from magic is insufficiently advanced.
(*)
s. keeling wrote:
Incoming from Arno Coetzee:
s. keeling wrote:
Grr. Please, what's wrong with this?!?
alter table MEMBERS
alter MEMBER_INFO varchar(160);
ERROR 1064: You have an error in your SQL syntax. Check the manual \
that corresponds to your MySQL server version for the ri
On 06/10/2005, "s. keeling" wrote:
> Incoming from Arno Coetzee:
> > s. keeling wrote:
> > > alter table MEMBERS
> > >alter MEMBER_INFO varchar(160);
> > >
> > > ERROR 1064: You have an error in your SQL syntax.
> >
> > http://dev.mysql.com/doc/mysql/en/alter-table.html
>
> Yes, I've read i
Incoming from Arno Coetzee:
> s. keeling wrote:
> >
> >Grr. Please, what's wrong with this?!?
> >
> > alter table MEMBERS
> >alter MEMBER_INFO varchar(160);
> >
> >ERROR 1064: You have an error in your SQL syntax. Check the manual \
> > that corresponds to your MySQL server version for the r
s. keeling wrote:
Incoming from s. keeling:
Incoming from Pooly:
2005/10/3, s. keeling <[EMAIL PROTECTED]>:
I'd like to add a bit of history data to a table (who changed a record
last, and when it was last changed). Is this the way to do it?
[snip]
alter table MEMBERS
Incoming from s. keeling:
> Incoming from Pooly:
> > 2005/10/3, s. keeling <[EMAIL PROTECTED]>:
> > > I'd like to add a bit of history data to a table (who changed a record
> > > last, and when it was last changed). Is this the way to do it?
> > > [snip]
> > >alter table MEMBERS
> > >a
Incoming from Pooly:
> 2005/10/3, s. keeling <[EMAIL PROTECTED]>:
> > I'd like to add a bit of history data to a table (who changed a record
> > last, and when it was last changed). Is this the way to do it?
> > [snip]
> >alter table MEMBERS
> >alter CHG_DATE set default CURRENT_DATE
>
2005/10/3, s. keeling <[EMAIL PROTECTED]>:
> I'd like to add a bit of history data to a table (who changed a record
> last, and when it was last changed). Is this the way to do it?
>
>alter table MEMBERS
>add CHG_BY varchar(3)
>
>alter table MEMBERS
>alter CHG_BY set defaul
Hello.
> http://dev.mysql.com/doc/mysql/en/alter-table.html document, but I
> don't see any examples of altering table types, just a lot of other
The syntax for many of the allowable alterations is similar
to clauses of the CREATE TABLE statement. This includes
table_options modificatio
* Rhino:
> You'll need three alter statements:
> - one to add the new column
> - one to get rid of the old primary key
> - one to set the new column as the primary key
In addition to adding the new column, there will be neccessary to populate
the column with unique values. A primary key must allwa
From: "Philippe Poelvoorde"
> > No, you forgot the DROP PRIMARY KEY ;-)
>
> well not really, that's the statement before ! ;-)
Sorry, I misread your mail.
> So is that normal that I can't specify BEFORE username ?
Yes, because MySQL only supports AFTER `column_name` ;-P
http://dev.mysql.com/do
You'll need three alter statements:
- one to add the new column
- one to get rid of the old primary key
- one to set the new column as the primary key
Something like this, i.e. I've left out some details of the syntax:
ALTER TABLE FOO ADD USERID CHAR(8);
ALTER TABLE FOO DROP PRIMARY KEY;
ALTER TAB
Jigal van Hemert wrote:
From: "Philippe Poelvoorde"
[Note,
ALTER TABLE foo ADD userid integer unsigned NOT NULL auto_increment
BEFORE username, ADD PRIMARY KEY(userid);
does not seems to work, possibly a bug ?
]
No, you forgot the DROP PRIMARY KEY ;-)
well not really, that's the statement
From: "Philippe Poelvoorde"
> [Note,
> ALTER TABLE foo ADD userid integer unsigned NOT NULL auto_increment
> BEFORE username, ADD PRIMARY KEY(userid);
> does not seems to work, possibly a bug ?
> ]
No, you forgot the DROP PRIMARY KEY ;-)
ALTER TABLE `foo` DROP PRIMARY KEY, ADD `userid` INTEGER UN
bruce wrote:
hi..
i have a table that i want to add a column to, and define the columm to be
the primary key. however, i already have a column in the table that's used
as the primary. can someone tell me the approach/commands i should use to
get my results?
table foo
username varchar(50), prima
From: "bruce"
> table foo
> username varchar(50), primary
>
> what i want...
> userid int(), primary
> username varchar(50)
>
> with userid listed before username!!
> can someone tell me what the commands are that i need to enter to get
this!!
http://dev.mysql.com/doc/mysql/en/alter-table.html
AL
Michael Stassen wrote:
Did you try perror?
~: perror 150
Error code 150: Unknown error: 150
150 = Foreign key constraint is incorrectly formed
Right, I forgot to alter one table that had a FK on this table. Thanks ;)
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For l
Did you try perror?
~: perror 150
Error code 150: Unknown error: 150
150 = Foreign key constraint is incorrectly formed
Michael
Philippe Poelvoorde wrote:
Hi,
I'm trying to add an 'unsigned' attribute to one column :
> alter table markets modify Id integer unsigned not null auto_increment;
Hello,
I am using 4.1.7 and it works for it. I just wanted to
know from which version it support has started because
it does not run on my 3.23.58 server.
Regards,
Karam
--- Andy Ford <[EMAIL PROTECTED]> wrote:
> Which version are you running. Quite often when you
> see a 'check the
> version y
Which version are you running. Quite often when you see a 'check the
version you are running' message indicates a syntax error in your mySQL
statement..
Andy
On Tue, 2005-01-11 at 05:13 -0800, Karam Chand wrote:
> Hello,
>
> Can somebody tell me from which version of MySQL is -
>
> ALTER TABLE
Hey,
As I found out on the list, you want to SET SQL_LOG_BIN=0 in your
session. The user making the changes needs have the SUPER privilege.
http://dev.mysql.com/doc/mysql/en/SET_SQL_LOG_BIN.html
On Tue, 9 Nov 2004 20:35:22 +0100, harm <[EMAIL PROTECTED]> wrote:
> Hello,
>
> I want to convert a
id | int(11) | | PRI | 0 | |
| fc_date | date| | PRI | -00-00 | |
+-+-+--+-----+----+---+
2 rows in set (0.00 sec)
mysql>
- Original Message -
From: "Victor Pendleton" <[EMAIL PROTECTED]>
Newsgrou
Can you mysqldump the table then rebuild the table from the dump file?
-Original Message-
From: Rich Schramm
To: 'Victor Pendleton'; [EMAIL PROTECTED]
Sent: 5/18/04 2:04 PM
Subject: RE: Alter table primary key and foreign keys
The error log shows nothing when the binary dies
ch Schramm '; '[EMAIL PROTECTED] '
Subject: RE: Alter table primary key and foreign keys
I would first see if an upgrade to a later version of InnoDB tables is
possible. What is being written to the error log? The ALTER TABLE
statement subtly creates a new table, with new contraint name
I would first see if an upgrade to a later version of InnoDB tables is
possible.
What is being written to the error log?
The ALTER TABLE statement subtly creates a new table, with new contraint
names that the child table is unaware of, and drops the original table. Have
you tried rebuilding the chi
Alter table 'tablename' change column 'columnname' 'new-columnname' not null
default 0
If you have any null values in that column, you will not be able to make the
change.
J.R.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.c
> If you are unsure you should make a copy of the table and perform you test
> actions on that table. The answer is yes you can convert your unsigned
integer
> column to a varchar column.
Yep, first make a backup copy of your table, then alter the column to
varchar type.
To convert the existing dec
If you are unsure you should make a copy of the table and perform you test
actions on that table. The answer is yes you can convert your unsigned integer
column to a varchar column.
On Mon, 05 Apr 2004, joe collins wrote:
>
> I have a table that has a column :
> session_id INTEGER UNSIGNED NOT
On Monday 22 March 2004 18:26, Shane Nelson wrote:
> Thanks, the help file cleared up the rest.
>
> In my case the table tt wasn't a merge table, it was just a table I
> created normally. Even so the alter table line didn't create an error.
If the table type other than MERGE, UNION part of the AL
Shane Nelson <[EMAIL PROTECTED]> wrote:
>
> Any idea what this would do?
>
> alter table tt union=(t1,t2,t3);
With this statement you specify that MERGE table tt will union tables t1, t2, t3 and
they will used as one:
http://www.mysql.com/doc/en/MERGE.html
>
> The four tables are all
This worked for me:
ALTER TABLE `test` CHANGE `somefield` `somefield` enum('new','used') DEFAULT 'new'
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hi Mike,
It's just part of modifying the column to change the DEFAULT value.
e.g. you might use this (changes to NOT NULL and DEFAULT value of
'new'):
ALTER TABLE table MODIFY type ENUM('new','used') NOT NULL DEFAULT 'new';
Hope that helps.
Matt
- Original Message -
From: "Mike Maps
Hi Chris,
I don't know exactly what you mean by ALTER being as good as OPTIMIZE...
But yes, an ALTER that recreates the data file (as ALTER ... ORDER BY
does) will defragment the data file too.
However, OPTIMIZE also analyzes the key distribution (I don't know if
it's remembered after an ALTER o
Chris,
for MyISAM and InnoDB, ALTER TABLE normally locks the table to be altered in
a read-only mode.
Best regards,
Heikki
Innobase Oy
http://www.innodb.com
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyI
I ended up stopping and restarting MySQL -- which cleared up the
problem. Index creation took around a minute.
Dan
At 05:38 PM 10/15/03, Matt W wrote:
Hi Dan,
133MHz huh? :-) Well, how large is the table? Huge rows? How many other
indexes are on the table and on how many columns? Are those colu
Hi Dan,
133MHz huh? :-) Well, how large is the table? Huge rows? How many other
indexes are on the table and on how many columns? Are those columns
large? If you had a full-text index on a large column, for example, it
could take very long on that system, especially if you're using 3.23.
Hard dis
Hi Dan,
O.K just making sure ;-)
How big is the table ?
Does it contain any indexes ? Could you remove the indexes, alter the table
and than add the indexes again ?
I read over and over that the Kernel 2.4.18 has lots of BUGS, but I am not
sure if this applies to your case. If you look in the
Hi Dan,
Just a guess though, but you are sure you have PLENTY of harddisk space laying
around ?
If not that would perfectly explain the behaviour.
Best regards
Nils Valentin
Tokyo/Japan
2003年 8月 13日 水曜日 08:19、Dan Edwards さんは書きました:
> Hi about 2 months ago I had trouble with alter table on lar
Ittay,
ALTER TABLE ... AUTO_INCREMENT=...
does not work with InnoDB type tables.
http://www.innodb.com/ibman.html#InnoDB_restrictions
"
For an AUTO_INCREMENT column one must always define a key to the table, and
that key must contain just the auto-increment column. InnoDB does not
support AUTO_
Hi Dan,
I wanted to comment on the comparison between the scsi cards - plase see
below.
2003年 6月 10日 火曜日 16:55、Jeremy Zawodny さんは書きました:
> On Mon, Jun 09, 2003 at 05:27:00PM -0700, Dan Edwards wrote:
> > > Can you grab the output of 'vmstat' or iostat or something that'll
> > > measure disk I/O n
On Mon, Jun 09, 2003 at 05:27:00PM -0700, Dan Edwards wrote:
> >
> > Can you grab the output of 'vmstat' or iostat or something that'll
> > measure disk I/O next time this happens?
>
> I'm going to try real hard not to do that again until it is upgraded or
> another solution is found.
Yeah, I k
I would assume that if you have two MySQL threads, one doing the ugly ALTER TABLE
and another responsible for a simple SELECT query (perhaps even query cached) on a
table in another database, that no matter how long that ALTER TABLE thread took,
the OS would schedule the SELECT thread soon enough a
Can you grab the output of 'vmstat' or iostat or something that'll
measure disk I/O next time this happens?
I'm going to try real hard not to do that again until it is upgraded or
another solution is found.
What'd help more is adding more disks not a single faster one--unless
the SCSI disk is an
On Mon, Jun 09, 2003 at 04:39:22PM -0700, Dan Edwards wrote:
>
> Against my better judgement I went ahead and added a field to a table
> with 875,000 records, it took over 10 minutes. That pretty much makes
> our games unplayable during that time. During this time other queries
> that normally
Against my better judgement I went ahead and added a field to a table
with 875,000 records, it took over 10 minutes. That pretty much makes
our games unplayable during that time. During this time other queries
that normally take a few milliseconds took 1-30 seconds. For smooth
operation it need
You say you are using replication. In this situation, if you make an update to
the master (using ALTER...) that takes a long time, this will get serialized
into the binary log as normal and block all subsequent queries from executing on
the slave until it has completed. One of the issues with
On Mon, Jun 09, 2003 at 01:05:43PM -0700, Dan Edwards wrote:
>
> I have a mysql server containing multiple databases, one of the
> databases is very important that it is not slowed down by other
> databases. This database is used for a real time game server (card
> games), and any stalls causes all
"Lilian" <[EMAIL PROTECTED]> wrote:
> I have tested that function
>
> ALTER TABLE table_name_here AUTO_INCREMENT = 1000;
>
> on version 4.0.12 and it's not working.
> (table TYPE=InnoDB)
>
> in 3.23.54 that is OK
>
> does anybody know what's wrong here?
Option AUTO_INCREMENT works only on MyI
ailing.database.mysql
Sent: Monday, June 02, 2003 10:15 AM
Subject: Re: ALTER TABLE
> Is 'id' indexed? AUTO_INCREMENT fields must be to work correctly. Try
this:
>
> ALTER TABLE users
> ADD INDEX idx_id (id),
> AUTO_INCREMENT=1000;
>
>
> Quite ho
RIMARY KEY AUTO_INCREMENT,
AUTO_INCREMENT=1000;
Edward Dudlik
Becoming Digital
www.becomingdigital.com
- Original Message -
From: "Lilian" <[EMAIL PROTECTED]>
To: "Becoming Digital" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, 02 June, 20
1 - 100 of 159 matches
Mail list logo
