Re: problem with altering a table

2006-06-21 Thread Alex 

Alex wrote:

I'm running mysql 5.0.22 on SLES9, using the mysql.com appropriate rpm.


I've tried other versions of mysql 5, including 5.0.6, 5.0.17, 5.0.18 
and 5.0.21. The result is always the same. This leads me to believe, 
that there are new requirements for mysql 5 and that's the reason ALTER 
fails.


I can't figure it out myself, please help.

Here's again the create table + alter table which fails:

SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE dbmail_messageblks (
  messageblk_idnr bigint(21) NOT NULL auto_increment,
  message_idnr bigint(21) NOT NULL default '0',
  messageblk longtext NOT NULL,
  blocksize bigint(21) NOT NULL default '0',
  PRIMARY KEY  (messageblk_idnr),
  UNIQUE KEY messageblk_idnr_2 (messageblk_idnr),
  KEY messageblk_idnr (messageblk_idnr),
  KEY msg_index (message_idnr),
  FOREIGN KEY (`message_idnr`) REFERENCES `messages` (`message_idnr`) 
ON DELETE CASCADE

) TYPE=InnoDB;
SET FOREIGN_KEY_CHECKS=0;
SET SQL_LOG_OFF=1;
SET SQL_LOG_UPDATE=0;
ALTER TABLE dbmail_messageblks
DROP INDEX messageblk_idnr,
DROP INDEX messageblk_idnr_2,
DROP INDEX msg_index,
CHANGE message_idnr physmessage_id bigint(21) NOT NULL DEFAULT '0',
ADD COLUMN is_header tinyint(1) DEFAULT '0' NOT NULL,
ADD INDEX physmessage_id_index (physmessage_id),
ADD INDEX physmessage_id_is_header_index (physmessage_id, 
is_header),

ADD FOREIGN KEY physmessage_id_fk (physmessage_id)
REFERENCES dbmail_physmessage (id)
ON DELETE CASCADE ON UPDATE CASCADE;




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: problem with altering a table

2006-06-21 Thread Gabriel PREDA 

When creating the InnoDB table the InnoDB engine asigns to the FOREIGN
KEY you defined a symbol.

On my server it generated dbmail_messageblks_ibfk_1... and if in the
ALTER statement I entered:

  DROP FOREIGN KEY dbmail_messageblks_ibfk_1

Then the ALTER table worked fine...

If you want to continue with this you should add a symbol name
manually like this in the create table statement:

CONSTRAINT `fk_message_idnr_manually_set`  FOREIGN KEY
(`message_idnr`) REFERENCES `messages` (`message_idnr`)

Now in the ALTER statement you will have to write before you change
the name of the column:
  DROP FOREIGN KEY `fk_message_idnr_manually_set`

If you DROP an index a FOREIGN KEY based on that index will not be
dropped automaticaly...

Hope this helps !

--
Gabriel PREDA
Senior Web Developer

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

[SOLVED] Re: problem with altering a table

2006-06-21 Thread Alex 

Gabriel PREDA wrote:


Hope this helps !


Thanks a bunch, that was it. Problem solved.

I'll tell about it on the dbmail list as well.

Alex


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]