Hi,

What I am trying to do:

LOCK TABLES profilename WRITE;

INSERT INTO profilename (profilename,email) SELECT
concat('user',count(*)+1),'my (AT) email (DOT) com' from profilename;
ERROR 1100 (HY000): Table 'profilename' was not locked with LOCK
TABLES

So you are supposed to do:
LOCK TABLES profilename AS x WRITE;
Then use x in the query somehow? I really don't understand how this is
suppose to be so the locking works and one don't get the error.

I tried to find some info about this on and it lead me to the
following threads:
http://groups.google.com/group/comp....413068246d8aba

http://bugs.mysql.com/bug.php?id=25180

But I am still not able to figure it out how the syntax is supposed to
be.
Thanks for any help,
Regards Jon Berg.

On Mon, 8 Mar 2010 01:38:47 -0800 (PST), jonbbbb wrote:
What problem is this query attempting to solve? It looks like you're
trying to come up with a guaranteed-unique identifier for a row, and
MySQL already had a mechanism to do that.

--
Don't even get me started on the MCSEs I know. It's a miracle of
modern technology that some of these fsckwits still draw breath,
much less a paycheck.
-- Marc Bowden

The row needs to have a unique string as a default value for that
column. I suppose you mean that I could
have this row as an AUTO_INCREMENT number, but it really needs to be
a string.

So the query will create a default value that will always work, and it
does work since it will not be allowed to change it
to something that starts with "user" later on.

I really don't need an AUTO_INCREMENT number for the row, since the
email is sort of the key which it is matched against.


Regards,
Jon Berg.

On Mon, 8 Mar 2010 06:16:54 -0800 (PST), jonbbbb wrote:
Why? If you're asserting that this is part of the problem, then you need
to explain the the gap so we can help you fix it.

Again, I'm not clear on the "why". Email address is a fine way to search
for a record, and there's perfectly clear reasons why that might need to
be unique, and there's obvious reasons why an auto-inc column can be
handy, but I'm not at all clear on why these need to be parts of the
same column. I think you're overloading the column unnecessarily, but
would be happy to hear WHY you think you're not.

--
'Cluids' - def: "Fluids having the effect of restoring or imparting
Clue. Eg; beer, coffee, sulphuric acid, etc."
-- Lionel, in the Monastery

On Mar 8, 5:08*pm, "Peter H. Coffin" <hell... (AT) ninehells (DOT) com> wrote:
Well, have you seen youtube or flickr or any other kind of web site,
they have the concept of a profile name. Even if you log in with your
email, you are often
identified with your profile name. A profile name is good since it
does not expose the email address
if you don't want to.

And it would kind of bad if that always have to be a number. And it
would
be kind of confusing if it was not unique since a lot of people would
have the
same profile name.

Just tell me how I can get that insert with the subquery to work on a
locked table. LOL :-)

Regards, Jon Berg.

jonbbbb wrote:
So you have a profile name. That doesn't have to be the auto_increment
value.

I highly suggest you learn about database normalization.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On Mon, 8 Mar 2010 08:46:48 -0800 (PST), jonbbbb wrote:
Just throw a UNIQUE index on the profile column. When someone tries to
create a profile that already exists, the database will say "No way,
dude" in a detectable way, and you can tell the user to pick a different
profile name. Since the mechanism is an index, it's fast enough that you
can probably have something even check the profile for availability
pretty much as someone types or within a very short time after they move
to the next field in a form. If you insist on doing that concat thing
you posted originally, takes a closer look at the LOCK TABLES syntax page
where it talks about locking a single table multiple times with table
aliases, in a single LOCK statement. It's about half way down

http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html

--
Liberty, equality, diversity. Pick any two.