MySQL Bugs: #9475: last row not deleted

Bug #9475	last row not deleted
Submitted:	30 Mar 2005 6:20	Modified:	5 May 2005 15:08
Reporter:	Berto van de Kraats	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.4-beta-nightly-20050328	OS:	Linux (linux)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
all rows of the table should be deleted; however the last row is NOT deleted.

How to repeat:
compile/run attached c-program

last row is not deleted

Attachment: bug2.c (application/octet-stream, text), 3.36 KiB.

Peter,

a week old 5.0.3 build works ok from the mysql client.

Can you repeat the problem if you use the mysql interactive SQL client?

Regards,

Heikki

heikki@hundin:~/mysql-5.0/client> ./mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.3-beta-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE xxx (id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(20) NOT N
ULL) TYPE = INNODB;
Query OK, 0 rows affected, 1 warning (0.09 sec)

mysql> insert into xxx (id, name) values (1, 'aaa');
Query OK, 1 row affected (0.04 sec)

mysql> insert into xxx (id, name) values (2, 'bbb');
Query OK, 1 row affected (0.01 sec)

mysql> insert into xxx (id, name) values (3, 'ccc');
Query OK, 1 row affected (0.01 sec)

mysql> delete from xxx;
Query OK, 3 rows affected (0.01 sec)

mysql> select * from xxx;
Empty set (0.05 sec)

mysql>

Hi!

I cannot repeat this with mysql with the latest 5.0.4 build.

I tested both with latin1 and UTF-8.

What is your my.cnf like?

Regards,

Heikki

heikki@hundin:~/mysql-5.0/client> ./mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.4-beta-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table xxx;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE xxx (id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(20) NOT N
ULL) TYPE = INNODB;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into xxx (id, name) values (1, 'aaa');
Query OK, 1 row affected (0.01 sec)

mysql> insert into xxx (id, name) values (2, 'bbb');
Query OK, 1 row affected (0.01 sec)

mysql> insert into xxx (id, name) values (3, 'ccc');
Query OK, 1 row affected (0.00 sec)

mysql> delete from xxx;
Query OK, 3 rows affected (0.01 sec)

mysql> select * from xxx;
Empty set (0.00 sec)

mysql> exit
Bye
heikki@hundin:~/mysql-5.0/client> ./mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.4-beta-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show create table xxx;
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------+
| Table | Create Table
                                                                    |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------+
| xxx   | CREATE TABLE `xxx` (
  `id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql>

I cannot reproduce the problem either from the mysql client. It only occurs if done from the c-api. (so please use the small c-program I attached when I did report the bug)

mysql> status
--------------
mysql  Ver 14.9 Distrib 5.0.4-beta-nightly-20050328, for pc-linux (i686)

Connection id:          1
Current database:       baan
Current user:           root@localhost
SSL:                    Not in use
Current pager:          less
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.4-beta-nightly-20050328
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /tmp/mysql.sock
Uptime:                 4 min 45 sec

Threads: 1  Questions: 15  Slow queries: 0  Opens: 0  Flush tables: 2  Open tables: 1  Queries per second avg: -0.000

Peter,
Please attach your my.cnf. Are there any messages in the error log? Does the bug occur if you create the ibdata files from the scratch?

1) are you referring to following logfile ?

/usr1/mysql/mysql-5.0.4-beta-nightly-20050328-pc-linux-i686/data/nlbaldev1.err

this file is not updated when the test-program is run

2) we have no my.cnf file specified (see below), so everything should I think be default.

mysql    11502 11480  0 11:34 pts/15   00:00:00 /usr1/mysql/mysql-5.0.4-beta-nightly-20050328-pc-linux-i686/bin/mysqld --defaults-e 
tra-file=/usr1/mysql/mysql-5.0.4-beta-nightly-20050328-pc-linux-i686/data/my.cnf --basedir=/usr1/mysql/mysql-5.0.4-beta-nightly-200 
0328-pc-linux-i686 --datadir=/usr1/mysql/mysql-5.0.4-beta-nightly-20050328-pc-linux-i686/data --user=mysql --pid-file=/usr1/mysql/m 
sql-5.0.4-beta-nightly-20050328-pc-linux-i686/data/nlbaldev1.pid --skip-locking

3) how should I create ibdata from scratch ?

Peter,

I am changing the Category to 'Client library' because this bug most probably is not in the mysqld server.

Please double-check that your little C program is correct. There are so may ways one can write incorrect C client programs.

Regards,

Heikki

I tested your application built it on Windows and running it against
a server running on Linux, the output was:

C:\temp>bug9475.exe
ccc

but in the server:

miguel@hegel:~/dbs/5.0$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.4-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from xxx;
Empty set (0.00 sec)

I can't repeat the bug when using the latest 4.1 or 5.0 tree.
This must have been fixed along with one of the resent cursors/prepared statements fixes.
Feel free to reopen the bug report if the bug is reproducible in your setup when using the latest
MySQL version.
Here is the output I get (the test case was modified to call mysql_get_server_info):

kostja@dragonfly:~> gcc bug2.c -I/home/kostja/work/mysql-5.0-root/include -L/home/kostja/work/mysql-5.0-root/libmysql/.libs -lmysqlclient -lz -lcrypt -lssl
kostja@dragonfly:~> ./a.out 
4.1.12-valgrind-max-debug
no rows found
kostja@dragonfly:~> ./a.out
5.0.6-beta-valgrind-max-debug
no rows found
kostja@dragonfly:~> ./a.out
5.0.6-beta-valgrind-max-debug
no rows found
kostja@dragonfly:~>