LOCK problem with SELECT table alias

>Description:
A SELECT statement fails if you have table aliases and use
table locking with the LOCK command
>How-To-Repeat:
DROP TABLE IF EXISTS CK1;
CREATE TABLE CK1 (
ID INT UNSIGNED NOT NULL,
PRIMARY KEY(ID)
);
LOCK TABLES CK1 READ; # you must have locking privileges
SELECT * FROM CK1; # works
SELECT * FROM CK1 AS SOME_ALIAS; # fails, error 1100: Table 'SOME_ALIAS' was not locked with LOCK TABLES
UNLOCK TABLES;
>Fix:
In the locking routines, do not check for aliases, but for table names.

>Submitter-Id:
>Originator: Christian Kohlschuetter
>Organization: NewsClub.de

>MySQL support: none
>Synopsis: LOCK problem with SELECT table aliases
>Severity: serious
>Priority: high
>Category: mysql
>Class: sw-bug
>Release: mysql-4.0.7-gamma (Official MySQL RPM)
>Server: /usr//bin/mysqladmin Ver 8.38 Distrib 4.0.7-gamma, for pc-linux on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version 4.0.7-gamma-Max
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 24 min 8 sec

Threads: 1 Questions: 91 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 5 Queries per second avg: 0.063
>C compiler: 2.95.3
>C++ compiler: 2.95.3
>Environment:

System: Linux blue.localnet 2.4.19-16mdk #1 Fri Sep 20 18:15:05 CEST 2002 i686 unknown unknown GNU/Linux
Architecture: i686

Some paths: /usr//bin/perl /usr//bin/make /usr//bin/gmake /usr//bin/gcc /usr//bin/cc
GCC: Reading specs from /usr//bin/../lib/gcc-lib/i586-mandrake-linux-gnu/3.2.1/specs
Configured with: ../configure --prefix=/usr --libdir=/usr/lib --with-slibdir=/lib --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --enable-long-long --enable-__cxa_atexit --enable-languages=c,c++,ada,f77,objc,java --host=i586-mandrake-linux-gnu --with-system-zlib
Thread model: posix
gcc version 3.2.1 (Mandrake Linux 9.1 3.2.1-2mdk)
Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' ASFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Jan 1 13:43 /lib/libc.so.6 -> libc-2.3.1.so
-rwxr-xr-x 1 root root 1224712 Dez 24 10:57 /lib/libc-2.3.1.so
-rw-r--r-- 1 root root 204 Dez 24 10:45 /usr/lib/libc.so
Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openssl' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--with-embedded-server' '--enable-thread-safe-client' '--with-comment=Official MySQL RPM' 'CC=gcc' 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' 'CXX=gcc'


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13404@lists.mysql.com
To unsubscribe, e-mail

Re: LOCK problem with SELECT table alias

On Sun 2003-01-05 at 15:08:18 +0100, ck@newsclub.de wrote:
> >Description:
> A SELECT statement fails if you have table aliases and use
> table locking with the LOCK command
> >How-To-Repeat:
> DROP TABLE IF EXISTS CK1;
> CREATE TABLE CK1 (
> ID INT UNSIGNED NOT NULL,
> PRIMARY KEY(ID)
> );
> LOCK TABLES CK1 READ; # you must have locking privileges
> SELECT * FROM CK1; # works
> SELECT * FROM CK1 AS SOME_ALIAS; # fails, error 1100: Table 'SOME_ALIAS' was not locked with LOCK TABLES
> UNLOCK TABLES;
> >Fix:
> In the locking routines, do not check for aliases, but for table names.

It is designed this way. You have to lock the aliases, too. IIRC, that
needed in order to guarantee dead-lock free operation.

It's documented, too. From http://www.mysql.com/doc/en/LOCK_TABLES.html:

When you use LOCK TABLES, you must lock all tables that you are
going to use and you must use the same alias that you are going to
use in your queries! If you are using a table multiple times in a
query (with aliases), you must get a lock for each alias!

Regards,

Benjamin.


--
benjamin-mysql@pflugmann.de

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13405@lists.mysql.com
To unsubscribe, e-mail

Re: LOCK problem with SELECT table alias

Am Sonntag, 5. Januar 2003 16:12 schrieb Benjamin Pflugmann:
> On Sun 2003-01-05 at 15:08:18 +0100, ck@newsclub.de wrote:
> > >Description:
> >
> > A SELECT statement fails if you have table aliases and use
> > table locking with the LOCK command
> >
> > >How-To-Repeat:
> >
> > DROP TABLE IF EXISTS CK1;
> > CREATE TABLE CK1 (
> > ID INT UNSIGNED NOT NULL,
> > PRIMARY KEY(ID)
> > );
> > LOCK TABLES CK1 READ; # you must have locking privileges
> > SELECT * FROM CK1; # works
> > SELECT * FROM CK1 AS SOME ALIAS; # fails, error 1100: Table
> > 'SOME ALIAS' was not locked with LOCK TABLES UNLOCK TABLES;
> >
> > >Fix:
> >
> > In the locking routines, do not check for aliases, but for table
> > names.
>
> It is designed this way. You have to lock the aliases, too. IIRC, that
> needed in order to guarantee dead-lock free operation.
>
> It's documented, too. From http://www.mysql.com/doc/en/LOCK TABLES.html:
>
> When you use LOCK TABLES, you must lock all tables that you are
> going to use and you must use the same alias that you are going to
> use in your queries! If you are using a table multiple times in a
> query (with aliases), you must get a lock for each alias!
>
> Regards,
>
> Benjamin.

Right, I also just found that paragraph in the LOCK TABLES documentation...
Though, I do not see a reason for this :-)

As you cannot have nested LOCK TABLES commands, one table statement does=20
suffice for locking the table. Of course, mysql should not throw an error i=
f=20
you lock tables with and without aliases, but if the table is already locke=
d=20
without alias, there should not be a need to lock the table explicitly with=
=20
the alias.

So this might be a "feature request".
=2D-=20
Christian Kohlschütter
ck@newsclub.de

http://www.newsclub.de - Der Meta-Nachrichten-Dienst


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13406@lists.mysql.com
To unsubscribe, e-mail

Re: LOCK problem with SELECT table alias

On Sunday 05 January 2003 20:22, Christian Kohlschuetter wrote:
>
> >
> > When you use LOCK TABLES, you must lock all tables that
> > you are going to use and you must use the same alias that
> > you are going to use in your queries! If you are using a
> > table multiple times in a query (with aliases), you must get
> > a lock for each alias!
> >
> > Regards,
> >
> > Benjamin.
>
> Right, I also just found that paragraph in the LOCK TABLES
> documentation... Though, I do not see a reason for this :-)
>
> As you cannot have nested LOCK TABLES commands, one table
> statement does suffice for locking the table. Of course, mysql
> should not throw an error if you lock tables with and without
> aliases, but if the table is already locked without alias,
> there should not be a need to lock the table explicitly with
> the alias.
>
> So this might be a "feature request".

Actually this comes from a way how MySQL works with tables locks
etc. Other similar problem is forbidden self-joins for temporary
tables.

These problems already shceduled to be fixed in one of next major
MySQL releases.



--
MySQL 2003 Users Conf. -> http://www.mysql.com/events/uc2003/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13411@lists.mysql.com
To unsubscribe, e-mail

Re: LOCK problem with SELECT table alias

Am Montag, 6. Januar 2003 17:52 schrieb Peter Zaitsev:
> On Sunday 05 January 2003 20:22, Christian Kohlschuetter wrote:
> > Right, I also just found that paragraph in the LOCK TABLES
> > documentation... Though, I do not see a reason for this :-)
> >
> > As you cannot have nested LOCK TABLES commands, one table
> > statement does suffice for locking the table. Of course, mysql
> > should not throw an error if you lock tables with and without
> > aliases, but if the table is already locked without alias,
> > there should not be a need to lock the table explicitly with
> > the alias.
> >
> > So this might be a "feature request".
>
> Actually this comes from a way how MySQL works with tables locks
> etc. Other similar problem is forbidden self-joins for temporary
> tables.
>
> These problems already scheduled to be fixed in one of next major
> MySQL releases.

Great!

Best wishes,
=2D-=20
Christian Kohlschütter
ck@newsclub.de

http://www.newsclub.de - Der Meta-Nachrichten-Dienst


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13412@lists.mysql.com
To unsubscribe, e-mail