Alter Table Difficulty

[Sorensson]

Successfully(?) set up DB for several hundred experimental recipes; eight tables in DB.

Upon preparation to enter first data, realized I needed a time-of-column. Getting to Alter Table by right click on table name or from drop down list presents array of table definitions properly enough. Adding Time column seems proper, but when Alter is executed message is:

“locked with Read Lock and can't be updated.”

Unable to find means to remove Read Lock within SQLyog and going out to mysql to unlock is ineffective also.

Interestingly, an attempt to run Alter Table on any other table stirs message:

“Could not read data for table.” Alter Table still calls up the proper array for the original table.

Exiting from DB clears the “Could not read …” problem. But the “locked with Read Lock …” arises immediately when Alter Table in invoked on any table.

There is no data in any table.

What am I doing wrong? How do I kill that Read Lock?

[CalEvans]

What OS is the server running on? This really sounds like a permissions issue of some kind. (At least that's where I would start looking.) I've never seen these errors.

Can you issue the statemetns in the mysql client directly?

Debuging 101: Start Eliminating until the problem is gone.

=C=

[CalEvans]

one other idea, have you bounces the mysql daemon? (stop/start or restart?)

=C=

[Sorensson]

Defeated, if not corrected, Alter Table problem by killing MySQL and restarting. Came upon this dodge after killing the machine to replace a cable.

I am attempting to use MySQL on Win2K. Simple stupidity is probable driver of my problems. Now struggling with the following:

Trying to add an 'enum' column to several of the eight tables -again using Alter Table. Works fine on three of four tables (column details are the same in each table). Attempt on fourth table elicits response:

“Error 1064. You have an error in SQL syntax near '.'NotSRB' NULL, drop primary key, add primary key (`StrtNr3`)' at line 1”

“NotSRB” is the last alternative enumerated. Not sure what “NULL” refers to and I have not set the enum column as primary. “StrtNr3” is already set as primary key so the last phrase is puzzling.

In one of the three successfully altered tables, the first alternative (“Excell.”) has been set as Default by SQLyog, but not so in the other two. If I delete that “Excell.”, close Alter Table, and open it again “Excell.” is again the default. This is no real problem, I imagine. But in the other two successfully modified tables, there is no default value chosen for the enum. When I put “Excell” as the default close Alter Table and re-open, it has been deleted.

Thus far, I have found no way to examine the SQL statements implicitly defined and executed when monkeying with Alter Table. I think I have chased down every potential path to the statements. What am I missing?

[CalEvans]

There is a 'History' TAB below the main window. The SQL it is trying to execute should be there. Snag it, paste it in here and let us all have a gander.

=C=

[Author]

Posts