alter table problems

[[email protected]]

1) I have imported a database from MS Access.

2) I have then changed all the table types to InnoDB

3) I now want to set up primary key settings. To do this I am using ver 4.0 Beta 2. I attach details of the first table I am trying to change at the bottom of this post:

i) When I go into Alter table, I still get the “could not read data for the table” message

ii) If I try to make changes – remove default = 0, tick PK, and tick auto-increment – I receive “Error No. 1091 Can't DROP 'PRIMARY'; check that column/key exists”

Please advise

Regards

Richard

/*Column Information For – transact_mysql.activity*/

---

Field Type Collation Null Key Default Extra Privileges Comment

---

---

---

---

---

---

---

---

---

activity_id int(10) NULL 0 select,insert,update,references

activity varchar(50) latin1_swedish_ci YES (NULL) select,insert,update,references

activity_description blob NULL YES (NULL) select,insert,update,references

employee_issue char(1) latin1_swedish_ci select,insert,update,references

activity_type_id int(10) NULL YES (NULL) select,insert,update,references

/*Index Information For – transact_mysql.activity*/

---

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment

---

---

---

---

---

---

---

---

---

---

---

---

/*DDL Information For – transact_mysql.activity*/

---

Table Create Table

---

---

activity CREATE TABLE `activity` (

`activity_id` int(10) NOT NULL default '0',

`activity` varchar(50) default NULL,

`activity_description` blob,

`employee_issue` char(1) NOT NULL default '',

`activity_type_id` int(10) default NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

[Richard Prossor]

I have just found that I don't have these problems using MySQL-Front……..I think you may have lost a customer (but at least I paid for the product)

😎

Regards

Richard

[Ritesh]

We are not able to reproduce this error at our end. If you help us reproduce this error at our end, we assure you to fix it in the next three business days.

Please mention the following information:

• Exact MySQL version
• Table schema
• Relationships between test tables
• Index information

[Richard Prossor]

Hi Ritesh

the MySQL version is 4.1.4-gamma-max-log

As detailed in the first post, I am importing from MS Access. After the import, because the primary key and other indexes and relationships are not transferred, I need to set these up again manually – that is what I am trying to do when I get the errors I reported

I am assuming the 1091 error is a sql error rather than related to your software but do not understand what it is trying to tell me

This is the table after I have added the information I require using mysql-front

Regards

Richard

/*Column Information For – transact_mysql.activity*/

---

Field Type Collation Null Key Default Extra Privileges Comment

---

---

---

---

---

---

---

---

---

activity_id int(10) NULL PRI (NULL) auto_increment select,insert,update,references

activity varchar(50) latin1_swedish_ci YES (NULL) select,insert,update,references

activity_description blob NULL YES (NULL) select,insert,update,references

employee_issue char(1) latin1_swedish_ci select,insert,update,references

activity_type_id int(10) NULL MUL 0 select,insert,update,references

/*Index Information For – transact_mysql.activity*/

---

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment

---

---

---

---

---

---

---

---

---

---

---

---

activity 0 PRIMARY 1 activity_id A 8 (NULL) (NULL) BTREE

activity 1 NewIndex 1 activity_type_id A 8 (NULL) (NULL) BTREE

/*DDL Information For – transact_mysql.activity*/

---

Table Create Table

---

---

activity CREATE TABLE `activity` (

`activity_id` int(10) NOT NULL auto_increment,

`activity` varchar(50) default NULL,

`activity_description` blob,

`employee_issue` char(1) NOT NULL default '',

`activity_type_id` int(10) NOT NULL default '0',

PRIMARY KEY (`activity_id`),

KEY `NewIndex` (`activity_type_id`),

CONSTRAINT `activity_ibfk_1` FOREIGN KEY (`activity_type_id`) REFERENCES `activity_type` (`activity_type_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

[Ritesh]

Hello Richard,

We confirm the presence of above bugs in SQLyog 4.0 BETA 2. We are working on these issues and will release BETA 3 soon with the bugs fixed.

With this, I would like to say that these error occur due to anomalies in the way MySQL returns Meta-data information.

1.) First error

In versions prior to MySQL 4.1.4-gamma the result returned from SQL:

show table status from mydb was:

Name Type Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Charset Create_options Comment

where as in MySQL 4.1.4-gamma it is:

Name Engine Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Charset Create_options Comment

This causes SQLyog to throw up the error: could not read data for the table

2.) Second error

In versions < 4.1.4, MySQL allowed to issue SQL stmt. to drop primary key for a table even if there were none. With 4.1.4, it has changed. Now we need to have a PRIMARY KEY to issue DROP PRIMARY KEY stmt. This issue results in SQLyog throwing up error: Error No. 1091 Can't DROP 'PRIMARY'; check that column/key exists

Therefore, the errors returned are specific to version of MySQL you are using.

[Richard Prossor]

Hi Ritesh

thanks for your reply identifying the bugs. I look forward to receiving advice of the new version because I do like your software 🙂

Regards

Richard

[Shadow]

To Ritesh: “Type” keyword is deprecated as of 4.1.4, “Engine” must be used instead.

[Ritesh]

SQLyog 4.0 BETA 3 has been released. This release fixes the above bug(s).

For more information please visit: http://www.webyog.com/forums/index.php?act…ct=ST&f=2&t=977

[Author]

Posts