I have the following situation which I can seem to resolve. When I do an "ALTER TABLE" in mysql (v5.5) it hangs in with "waiting for metadata lock". Here is how I got there:
- i have an xml which i want to import into a table; i use the elements in the xml to create columns in the table like this:
<name> some name </name> <address>dsada</address> there are some nodes in the xml that have different number of elements, like: <name> some name </name> <address>ds adsa</address> <phone> fndjsf</phone>
create table `person` ( `id` bigint(12) unsigned NOT NULL auto_increment ,`name` varchar(200) NOT NULL ,`address` varchar(250) NOT NULL ,PRIMARY KEY (`id`),) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert into person(name, address) values ...
the column names are read from the xml, so are the values. If the for loop reads a column name that hasn't been created in the table yet, like "phone" it returns a sql exception "column name "phone" not found" and tries an alter table before inserting the values
alter table 'person' ADD COLUMN `phone` varchar(20) NULL default NULL;
here is where the program hangs. i've checked in mysql admin for the session and the state is "waiting for metadata lock"
how can i pass this? or how do i remove the table metadata lock created by the previous insert which raised the exception. (the import is made in java that's why i use a for loop to parse the xml and get the element names and values)