Write locked table can"t handle TRUNCATE/DELETE (MyISAM)

>Description:
A locked table can't handle TRUNCATE or DELETE, reporting error:
Can't execute the given command because you have active locked tables or an active transaction.
>How-To-Repeat:
CREATE TABLE foo `id` int(10) unsigned NOT NULL auto_increment,
`member_id` int(10) unsigned NOT NULL default '0',
`score` int(10) unsigned NOT NULL default '0',
`behaald` datetime NOT NULL default '0000-00-00 00:00:00',
`commentaar` text,
PRIMARY KEY (`id`),
KEY `member_id` (`member_id`,`score`),
KEY `posted` (`behaald`)
) TYPE=MyISAM;
INSERT INTO foo VALUES('','1','5340',NOW(),'yippee');
INSERT INTO foo VALUES('','1','9320',NOW(),'awesom');
LOCK TABLES foo WRITE;
CREATE TEMPORARY TABLE tmp_rank SELECT * FROM foo ORDER BY score DESC, behaald DESC;
TRUNCATE foo;
DELETE FROM foo;

Might be related - I don't use system locking (skip-locking is in my.cnf).

>Fix:
Don't use locking (hmmm....).

>Submitter-Id:
>Originator: MelvynSopacua
>Organization:
IDG.nl
>MySQL support: none
>Synopsis: No truncate/delete on write locked myisam table
>Severity: critical
>Priority: medium
>Category: mysqld
>Class: sw-bug
>Release: mysql-4.0.2-alpha (AIX 4.3.3 statically linked)
>Server: /data/mdev/mysql/bin/mysqladmin Ver 8.35 Distrib 4.0.2-alpha, for ibm-aix4.3.3.0 on rs6000
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.2-alpha-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /data/mdev/sql/tmp/mysql4.sock
Uptime: 7 days 20 hours 1 min 34 sec

Threads: 13 Questions: 3607644 Slow queries: 43 Opens: 9411 Flush tables: 1 Open tables: 64 Queries per second avg: 5.330
>Environment:

System: AIX femke 3 4 0044CA0A4C00


Some paths: /bin/perl /bin/make /home/mdev/local/bin/gmake /usr/local/bin/gcc /home/mdev/bin/cc
GCC: Reading specs from /usr/local/lib/gcc-lib/powerpc-ibm-aix4.3.2.0/2.95.2.1/specs
gcc version 2.95.2.1 19991024 (release)
Compilation info: CC='gcc -pipe -mcpu=power -Wa,-many' CFLAGS='' CXX='gcc -pipe -mcpu=power -Wa,-many' CXXFLAGS='-felide-constructors -fno-exceptions -fno-rtti' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 bin bin 19 May 14 2001 /lib/libc.a -> /usr/ccs/lib/libc.a
lrwxrwxrwx 1 bin bin 19 May 14 2001 /usr/lib/libc.a -> /usr/ccs/lib/libc.a
Configure command: ./configure --prefix=/data/mdev/mysql --localstatedir=/data/mdev/sql/var --disable-shared --with-raid --with-unix-socket-path=/data/mdev/sql/tmp/mysql.sock --with-mysqld-user=mdev --with-mysqld-ldflags=-Wl,-bbigtoc --without-debug '--with-comment=AIX 4.3.3 statically linked' --without-bench --with-charset=latin1 --with-extra-charsets=none --without-isam --with-berkeley-db 'CC=gcc -pipe -mcpu=power -Wa,-many' 'CXXFLAGS=-felide-constructors -fno-exceptions -fno-rtti' 'CXX=gcc -pipe -mcpu=power -Wa,-many'


------------------------------------------------------------ ---------
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-thread12493@lists.mysql.com
To unsubscribe, e-mail

Re: Write locked table can"t handle TRUNCATE/DELETE (MyISAM)

mdev@idg.nl writes:
> >Description:
> A locked table can't handle TRUNCATE or DELETE, reporting error:
> Can't execute the given command because you have active locked tables or an active transaction.
> >How-To-Repeat:
> CREATE TABLE foo `id` int(10) unsigned NOT NULL auto_increment,
> `member_id` int(10) unsigned NOT NULL default '0',
> `score` int(10) unsigned NOT NULL default '0',
> `behaald` datetime NOT NULL default '0000-00-00 00:00:00',
> `commentaar` text,
> PRIMARY KEY (`id`),
> KEY `member_id` (`member_id`,`score`),
> KEY `posted` (`behaald`)
> ) TYPE=MyISAM;
> INSERT INTO foo VALUES('','1','5340',NOW(),'yippee');
> INSERT INTO foo VALUES('','1','9320',NOW(),'awesom');
> LOCK TABLES foo WRITE;
> CREATE TEMPORARY TABLE tmp_rank SELECT * FROM foo ORDER BY score DESC, behaald DESC;
> TRUNCATE foo;
> DELETE FROM foo;
>

HI!

The above is actually the expected behaviour.

As our fine manual explains it :

You will get an error if you have an active LOCK TABLES or
transaction when trying to execute TRUNCATE TABLE ....

Main reason for this is that TRUNCATE is not very much transaction -
safe.

DELETE FROM ... worked just fine.

--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ 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-thread12495@lists.mysql.com
To unsubscribe, e-mail

Re: Write locked table can"t handle TRUNCATE/DELETE (MyISAM)

On Thu, 5 Sep 2002, Sinisa Milivojevic wrote:

SM>>> mdev@idg.nl writes:
SM>>> > >Description:
SM>>> > A locked table can't handle TRUNCATE or DELETE, reporting error:
SM>>> > Can't execute the given command because you have active locked tables or an active transaction.
SM>>> > >How-To-Repeat:
SM>>> > CREATE TABLE foo `id` int(10) unsigned NOT NULL auto_increment,
SM>>> > `member_id` int(10) unsigned NOT NULL default '0',
SM>>> > `score` int(10) unsigned NOT NULL default '0',
SM>>> > `behaald` datetime NOT NULL default '0000-00-00 00:00:00',
SM>>> > `commentaar` text,
SM>>> > PRIMARY KEY (`id`),
SM>>> > KEY `member_id` (`member_id`,`score`),
SM>>> > KEY `posted` (`behaald`)
SM>>> > ) TYPE=MyISAM;
SM>>> > INSERT INTO foo VALUES('','1','5340',NOW(),'yippee');
SM>>> > INSERT INTO foo VALUES('','1','9320',NOW(),'awesom');
SM>>> > LOCK TABLES foo WRITE;
SM>>> > CREATE TEMPORARY TABLE tmp_rank SELECT * FROM foo ORDER BY score DESC, behaald DESC;
SM>>> > TRUNCATE foo;
SM>>> > DELETE FROM foo;
SM>>> >
SM>>>
SM>>> HI!
SM>>>
SM>>> The above is actually the expected behaviour.
SM>>>
SM>>> As our fine manual explains it :
SM>>>
SM>>> You will get an error if you have an active LOCK TABLES or
SM>>> transaction when trying to execute TRUNCATE TABLE ....

Hmm, not in the LOCK TABLE section, but the TRUNCATE syntax. Ok.

SM>>> Main reason for this is that TRUNCATE is not very much transaction -
SM>>> safe.

And if I'm not using transactions, why should I care?

Look at what I'm doing above:
I'm re-ordering a scoring table, so that the PRIMARY KEY is equal to the
ranking. Now I must use DELETE FROM and then use SET INSERT_ID.
Don't know if that's a good thing, when you're already replicating plus
it's much more expensive.

I could use a different approach, but still have the same performance
issues, as these would be based upon 'UPDATE' queries and I would have to
use a ranking field - all very messy.

IOW - can I ask that something like if type=MyISAM { truncate_ok=1;} will
be implemented?

SM>>>
SM>>> DELETE FROM ... worked just fine.

Yes, looking at my history, I did a READ lock there. Logical. Sorry bout that.



--
Best regards,

Melvyn Sopacua
WebMaster IDG.nl
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
If it applies, where it applies - this email is a personal
contribution and does not reflect the views of my employer
IDG.nl.
\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\



------------------------------------------------------------ ---------
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-thread12497@lists.mysql.com
To unsubscribe, e-mail

Re: Write locked table can"t handle TRUNCATE/DELETE (MyISAM)

Melvyn Sopacua writes:
> On Thu, 5 Sep 2002, Sinisa Milivojevic wrote:
>
> And if I'm not using transactions, why should I care?
>

Then just use DELETE FROM table...

TRUNCATE was introduced deliberately for transactional tables ..

Also, you do not have to lock a table just for TRUNCATE operation.

> IOW - can I ask that something like if type=MyISAM { truncate_ok=1;} will
> be implemented?
>
>

Not a bad idea, but such a feature would have to wait for 5.0.

> Yes, looking at my history, I did a READ lock there. Logical. Sorry bout that.
>

No problem.

>
>
> --
> Best regards,
>
> Melvyn Sopacua
> WebMaster IDG.nl
> _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
> If it applies, where it applies - this email is a personal
> contribution and does not reflect the views of my employer
> IDG.nl.
> \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\
>
>
>

--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ 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-thread12498@lists.mysql.com
To unsubscribe, e-mail

Re: Write locked table can"t handle TRUNCATE/DELETE (MyISAM)

At 18:29 5-9-2002, Sinisa Milivojevic wrote:


>Melvyn Sopacua writes:
> > On Thu, 5 Sep 2002, Sinisa Milivojevic wrote:
> >
> > And if I'm not using transactions, why should I care?
> >
>
>Then just use DELETE FROM table...

Performance for one (delete row by row according to manual).
AND in 3.x DELETE FROM reset the insert id.

That doesn't happen in 4.x.

In CMS environments (constructs for 'freshest news on homepages' for
example) it's not a problem - i can drop the table and re-create it.

But for a dynamic example as this, where scores can be inserted at any
point and can be consulted at any point, you need either a lock or
some rather large calculations and update code on the application side.

>TRUNCATE was introduced deliberately for transactional tables ..

Yes, but DELETE FROM changed it's behavior, so in the end, there's
no way I'm able to do the same thing.

>Also, you do not have to lock a table just for TRUNCATE operation.

No, the goal is:

LOCK foo WRITE;
INSERT INTO foo (score, col, ...) VALUES(3000, 'baz', ...);
CREATE TEMPORARY TABLE ordered SELECT * FROM foo ORDER BY score DESC;
TRUNCATE foo;
INSERT INTO foo SELECT '',col,... FROM ordered;
UNLOCK TABLES;

This will re-order the table, in such a way, that id number 1 has the
highest score.

You see - we only show the 'top 10', but allow people to search where they're
actually located and provide that also, when they insert their score.

If I use a 'rank field' I first need to find out, what the 'future rank' of the
newly inserted score is (????) then downgrade all scores below that - quite
a series of upgrade queries that can become.

Maybe there's a smart way around that, but I doubt, it's as fast as the above.

> > IOW - can I ask that something like if type=MyISAM { truncate_ok=1;} will
> > be implemented?
> >
> >
>
>Not a bad idea, but such a feature would have to wait for 5.0.

Hmm, then what about resetting the DELETE FROM functionality or provide a
'Backwards compatiblity mode' for that?

The new DELETE FROM behaviour does break almost all of our sites, and I don't
quite like the alternative of using SET INSERT_ID=1.

In fact - it's not even working:

use test;
DROP TABLE IF EXISTS delete_bug;
CREATE TABLE `delete_bug` (
`id` int(10) unsigned NOT NULL auto_increment,
`txt` text,
PRIMARY KEY (`id`),
KEY `idx_txt` (`txt`(10))
) TYPE=MyISAM;

INSERT INTO delete_bug (txt) VALUES('foo');
INSERT INTO delete_bug (txt) VALUES('bar');
INSERT INTO delete_bug (txt) VALUES('last');

CREATE TEMPORARY TABLE del_bug_tmp SELECT * FROM delete_bug ORDER BY txt;
DELETE FROM delete_bug;
SET INSERT_ID=1;
INSERT INTO delete_bug SELECT '',txt FROM del_bug_tmp;
SELECT * FROM delete_bug;

Output:
+----+------+
| id | txt |
+----+------+
| 1 | bar |
| 4 | foo |
| 5 | last |
+----+------+



Best regards,
Melvyn Sopacua
WebMaster IDG.nl
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
If it applies, where it applies - this email is a personal
contribution and does not reflect the views of my employer
IDG.nl.
\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\


------------------------------------------------------------ ---------
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-thread12499@lists.mysql.com
To unsubscribe, e-mail

Re: Write locked table can"t handle TRUNCATE/DELETE (MyISAM)

Melvyn Sopacua writes:
> At 18:29 5-9-2002, Sinisa Milivojevic wrote:
>

[skip]

>
> This will re-order the table, in such a way, that id number 1 has the
> highest score.
>
> You see - we only show the 'top 10', but allow people to search where they're
> actually located and provide that also, when they insert their score.
>
> If I use a 'rank field' I first need to find out, what the 'future rank' of the
> newly inserted score is (????) then downgrade all scores below that - quite
> a series of upgrade queries that can become.
>
> Maybe there's a smart way around that, but I doubt, it's as fast as the above.
>


All this just to re-order a table ??

Why re-order ??

Use index or as a last resort ALTER TABLE.. ORDER BY ..

--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ 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-thread12500@lists.mysql.com
To unsubscribe, e-mail

Re: Write locked table can"t handle TRUNCATE/DELETE (MyISAM)

Hi!

>>>>> "Sinisa" == Sinisa Milivojevic writes:

Sinisa> Melvyn Sopacua writes:
>> On Thu, 5 Sep 2002, Sinisa Milivojevic wrote:
>>
>> And if I'm not using transactions, why should I care?
>>

Sinisa> Then just use DELETE FROM table...

Sinisa> TRUNCATE was introduced deliberately for transactional tables ..

Sinisa> Also, you do not have to lock a table just for TRUNCATE operation.

>> IOW - can I ask that something like if type=MyISAM { truncate_ok=1;} will
>> be implemented?

The reason for this is that the current code can't do a truncate while
holding a lock on the table. (We physically delete the table files and
recreate them and our locks can't handle that).

In the future we may want to do this on the handler level, after which
it will be able to truncate tables while holding a lock on them.

Regards,
Monty


------------------------------------------------------------ ---------
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-thread12522@lists.mysql.com
To unsubscribe, e-mail

Re: Write locked table can"t handle TRUNCATE/DELETE (MyISAM)

Hi!

>>>>> "Melvyn" == Melvyn Sopacua writes:

>> Also, you do not have to lock a table just for TRUNCATE operation.

Melvyn> No, the goal is:

Melvyn> LOCK foo WRITE;
Melvyn> INSERT INTO foo (score, col, ...) VALUES(3000, 'baz', ...);
Melvyn> CREATE TEMPORARY TABLE ordered SELECT * FROM foo ORDER BY score DESC;
Melvyn> TRUNCATE foo;
Melvyn> INSERT INTO foo SELECT '',col,... FROM ordered;
Melvyn> UNLOCK TABLES;

Melvyn> This will re-order the table, in such a way, that id number 1 has the
Melvyn> highest score.

Another faster way to do this:

LOCK foo WRITE;
INSERT INTO foo (score, col, ...) VALUES(3000, 'baz', ...);
CREATE TABLE foo_new SELECT * FROM foo ORDER BY score DESC;
rename table foo as foo_to_be_deleted, foo_new as foo
UNLOCK TABLES;
drop table foo_to_be_deleted;



Note however that for MyISAM tables, DELETE from table_name should be
as fast as TRUNCATE TABLE; The main differences compared to 3.23 is
that:

- It now returns the number of rows deleted.
- It will not reset auto_increment id.
- It will not work if the .MYI or .MYD file are deleted.

Melvyn> Hmm, then what about resetting the DELETE FROM functionality or provide a
Melvyn> 'Backwards compatiblity mode' for that?

What do you mean with this ?
One of the major complaint with the old delete was that it didn't do
the above things.

Melvyn> The new DELETE FROM behaviour does break almost all of our sites, and I don't
Melvyn> quite like the alternative of using SET INSERT_ID=1.

If you want to reset auto_increment, you should use

ALTER TABLE table_name auto_increment=1;

Melvyn> In fact - it's not even working:

Melvyn> use test;
Melvyn> DROP TABLE IF EXISTS delete_bug;
Melvyn> CREATE TABLE `delete_bug` (
Melvyn> `id` int(10) unsigned NOT NULL auto_increment,
Melvyn> `txt` text,
Melvyn> PRIMARY KEY (`id`),
Melvyn> KEY `idx_txt` (`txt`(10))
Melvyn> ) TYPE=MyISAM;

Melvyn> INSERT INTO delete_bug (txt) VALUES('foo');
Melvyn> INSERT INTO delete_bug (txt) VALUES('bar');
Melvyn> INSERT INTO delete_bug (txt) VALUES('last');

Melvyn> CREATE TEMPORARY TABLE del_bug_tmp SELECT * FROM delete_bug ORDER BY txt;
Melvyn> DELETE FROM delete_bug;
Melvyn> SET INSERT_ID=1;
Melvyn> INSERT INTO delete_bug SELECT '',txt FROM del_bug_tmp;
Melvyn> SELECT * FROM delete_bug;

Melvyn> Output:
Melvyn> +----+------+
Melvyn> | id | txt |
Melvyn> +----+------+
Melvyn> | 1 | bar |
Melvyn> | 4 | foo |
Melvyn> | 5 | last |
Melvyn> +----+------+

Which is correct.
SET INSERT ID will only affect the next inserted row. This command is
mainly used for replication and backup purposes.

If you want to reset the auto_increment id, you should use ALTER
TABLE.


Regards,
Monty

--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Michael Widenius
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ 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-thread12524@lists.mysql.com
To unsubscribe, e-mail

Re: Write locked table can"t handle TRUNCATE/DELETE (MyISAM)

Hi,

Will the implementation at the handler level also solve the problem of DROP
TABLE/TRUNCATE TABLE with a DELAYED thread still running on it ?

Regards,
Jocelyn
----- Original Message -----
From: "Michael Widenius"
To:
Cc: ;
Sent: Tuesday, September 10, 2002 10:00 PM
Subject: Re: Write locked table can't handle TRUNCATE/DELETE (MyISAM)


>
> Hi!
>
> >>>>> "Sinisa" == Sinisa Milivojevic writes:
>
> Sinisa> Melvyn Sopacua writes:
> >> On Thu, 5 Sep 2002, Sinisa Milivojevic wrote:
> >>
> >> And if I'm not using transactions, why should I care?
> >>
>
> Sinisa> Then just use DELETE FROM table...
>
> Sinisa> TRUNCATE was introduced deliberately for transactional tables ..
>
> Sinisa> Also, you do not have to lock a table just for TRUNCATE operation.
>
> >> IOW - can I ask that something like if type=MyISAM { truncate_ok=1;}
will
> >> be implemented?
>
> The reason for this is that the current code can't do a truncate while
> holding a lock on the table. (We physically delete the table files and
> recreate them and our locks can't handle that).
>
> In the future we may want to do this on the handler level, after which
> it will be able to truncate tables while holding a lock on them.
>
> Regards,
> Monty
>
>
> ------------------------------------------------------------ ---------
> 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-thread12522@lists.mysql.com
> To unsubscribe, e-mail
>
>
>
>


------------------------------------------------------------ ---------
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-thread12523@lists.mysql.com
To unsubscribe, e-mail

Re: Write locked table can"t handle TRUNCATE/DELETE (MyISAM)

Hi,

On Tue, 10 Sep 2002, Michael Widenius wrote:

MW>>> >>>>> "Melvyn" == Melvyn Sopacua writes:
MW>>>
MW>>> >> Also, you do not have to lock a table just for TRUNCATE operation.
MW>>>
MW>>> Melvyn> No, the goal is:
MW>>>
MW>>> Melvyn> LOCK foo WRITE;
MW>>> Melvyn> INSERT INTO foo (score, col, ...) VALUES(3000, 'baz', ...);
MW>>> Melvyn> CREATE TEMPORARY TABLE ordered SELECT * FROM foo ORDER BY score DESC;
MW>>> Melvyn> TRUNCATE foo;
MW>>> Melvyn> INSERT INTO foo SELECT '',col,... FROM ordered;
MW>>> Melvyn> UNLOCK TABLES;
MW>>>
MW>>> Melvyn> This will re-order the table, in such a way, that id number 1 has the
MW>>> Melvyn> highest score.
MW>>>
MW>>> Another faster way to do this:
MW>>>
MW>>> LOCK foo WRITE;
MW>>> INSERT INTO foo (score, col, ...) VALUES(3000, 'baz', ...);
MW>>> CREATE TABLE foo_new SELECT * FROM foo ORDER BY score DESC;
MW>>> rename table foo as foo_to_be_deleted, foo_new as foo
MW>>> UNLOCK TABLES;
MW>>> drop table foo_to_be_deleted;

Thanx very much! That is a good alternative.

MW>>>
MW>>>
MW>>>
MW>>> Note however that for MyISAM tables, DELETE from table_name should be
MW>>> as fast as TRUNCATE TABLE; The main differences compared to 3.23 is
MW>>> that:
MW>>>
MW>>> - It now returns the number of rows deleted.
MW>>> - It will not reset auto_increment id.
MW>>> - It will not work if the .MYI or .MYD file are deleted.
MW>>>
MW>>> Melvyn> Hmm, then what about resetting the DELETE FROM functionality or provide a
MW>>> Melvyn> 'Backwards compatiblity mode' for that?
MW>>>
MW>>> What do you mean with this ?
MW>>> One of the major complaint with the old delete was that it didn't do
MW>>> the above things.

Bascially because I didn't find a good alternative, which had the same 'safety net'. I can see the benefits of the new implementation, just didn't like it, that there was no similar way for the old behavior. I use this approach also for homepages, where editors can set a new story at a specific position at the homepage. Being dependant on the application, can be done in that case, but I'd rather have a lock and the safety applied by mysql, just in case the application gets outof sync.

By the way:
I think the above lines, plus your example should be worked into:
http://www.mysql.com/doc/en/Upgrading-from-3.23.html

There's only mention of a speed difference.

MW>>>
MW>>> Melvyn> The new DELETE FROM behaviour does break almost all of our sites, and I don't
MW>>> Melvyn> quite like the alternative of using SET INSERT_ID=1.
MW>>>
MW>>> If you want to reset auto_increment, you should use
MW>>>
MW>>> ALTER TABLE table_name auto_increment=1;

Is that new? I see it mentioned at the bottom now in the manual, it's very possible I've always (=2+ years) overlooked it though, since I don't see it mentioned in the reference at the top and don't consider it a 'table_option'. Thinking about it, it is, since 'there can be only one' per table, but you instinctively think of it as a field option.

MW>>> SET INSERT ID will only affect the next inserted row. This command is
MW>>> mainly used for replication and backup purposes.

In fact - to shield it's use, it make sence to me, to restrict this command to the 'Replication' privilege. Are there any reasons not to do that? Normal usage of this command can have very bad results.

--
Best regards,

Melvyn Sopacua
WebMaster IDG.nl
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
If it applies, where it applies - this email is a personal
contribution and does not reflect the views of my employer
IDG.nl.
\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\



------------------------------------------------------------ ---------
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-thread12527@lists.mysql.com
To unsubscribe, e-mail

Re: Write locked table can"t handle TRUNCATE/DELETE (MyISAM)

Hi!

>>>>> "Jocelyn" == Jocelyn Fournier writes:

Jocelyn> Hi,
Jocelyn> Will the implementation at the handler level also solve the problem of DROP
Jocelyn> TABLE/TRUNCATE TABLE with a DELAYED thread still running on it ?

Yes, but I assume that we will be able to fix this problem even
without having to change to handler-specific truncate (probably in one
of the early 4.1 releases).

Regards,
Monty

--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Michael Widenius
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ 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-thread12547@lists.mysql.com
To unsubscribe, e-mail