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
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
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
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
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
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 AFTER column_name where you replace column_name with
the
column name you want to position the modified column after.
Oh
- 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, then the
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 radecki.ra...@gmail.com
Hi All.
I would like to change the layout of my
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
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
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
not sure if restarting helps...
Regards,
Shachi Govil
- Original Message - From: Ananda Kumar [EMAIL PROTECTED]
To: Jonas Genannt [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Thursday, December 04, 2008 4:09 PM
Subject: Re: Alter Table - InnoDB
So, now u dont have free space
.
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
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
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
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 kind of speed
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 a
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 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]
To:
: 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
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
;
-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 alter a table - but
received
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
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
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 sec)
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
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)
mysql
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
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)
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
alter CHG_DATE set
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 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 right \
syntax
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 it. Your point?
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
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.
(*)
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
btw, you
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 default sbk
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
* 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 allways
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
ALTER TABLE
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),
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 UNSIGNED
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
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
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
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;
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
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
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 you
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
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
'; '[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 names that the
child table
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. I can't
[EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Tuesday, May 18, 2004 11:05 PM
Subject: RE: Alter table primary key and foreign keys
Can you mysqldump the table then rebuild the table from the dump file?
-Original Message-
From: Rich Schramm
To: 'Victor Pendleton'; [EMAIL
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:
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
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
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 ALTER
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 the
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
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 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
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
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
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
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:19Dan Edwards :
Hi about 2 months ago I had trouble with alter table on large tables
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
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
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
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 know what
Hi Dan,
I wanted to comment on the comparison between the scsi cards - plase see
below.
2003 6 10 16:55Jeremy 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 next time this
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
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
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
02, 2003 9:04 AM
Subject: Re: ALTER TABLE
Did you check http://www.mysql.com/doc/en/ALTER_TABLE_problems.html for
info?
Edward Dudlik
Becoming Digital
www.becomingdigital.com
- Original Message -
From: Lilian [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, 02 June, 2003 01
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, 2003 02:28
Subject: Re: ALTER TABLE
I have check http
: 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 honestly, your ID field should probably be the primary key, so:
ALTER TABLE users
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 MyISAM tables.
Of course, I'd figure it out right after sending this. For the interested:
alter table XYZ drop key foo;
Take care,
--Noel
- Original Message -
Given an existing table with a field defined as:
foo char(60) not null unique
What would be the proper syntax for an ALTER TABLE
1 - 100 of 148 matches
Mail list logo
