Member Avatar for pitic

Hello,
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:
XML example

<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>

SQL queries

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)

Thanks

  • 3 Contributors
  • 3 Replies
  • 453 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by Alekjenderbo

Recommended Answers

All 3 Replies

Member Avatar for debasisdas

Creating /altering table at run time is not a good idea.

why not create the table before executing any insert.

Member Avatar for pitic

Creating /altering table at run time is not a good idea.

why not create the table before executing any insert.

i'm creating it like a temporary table. i have a drop statement after inserting values. the whole process is like this:

i have a table with data which i need to update/replace.
1. copy data from t1 to t2 (in case something goes wrong)
2. delete data from t1
3. create table t3 from xml (insert from xml)
4. update table t3 with values from another xml
5. copy data from t3 to t1
6. drop t3

edit: the reason i cannot create it is because i do not know the column names/types and number of columns untill i parse the xml

Edited by pitic because: n/a
Member Avatar for Alekjenderbo

column names are read from the xml, as are the values. If the loop reads a column name that was not created in the table, however, as the "phone", it returns a sql column name "emergency phone" not found "and attempts to modify a table before inserting values

ALTER TABLE `person` add column 'phone `varchar (20) NULL default NULL;

this is where the program crashes. I checked in mysql administrator of the session and the state is "waiting for lock metadata"

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.