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

[30 Mar 2005 6:20] Berto van de Kraats
Description:
all rows of the table should be deleted; however the last row is NOT deleted.

How to repeat:
compile/run attached c-program
[30 Mar 2005 6:21] Berto van de Kraats
last row  is not deleted

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

[30 Mar 2005 9:15] Heikki Tuuri
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>
[30 Mar 2005 9:39] Heikki Tuuri
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>
[30 Mar 2005 9:49] Berto van de Kraats
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
[30 Mar 2005 10:08] Marko Mäkelä
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?
[30 Mar 2005 11:00] Berto van de Kraats
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 ?
[30 Mar 2005 12:23] Heikki Tuuri
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
[30 Mar 2005 13:43] Miguel Solorzano
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)
[5 May 2005 15:08] Konstantin Osipov
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:~>