MySQL Bugs: #33857: mysql hangs on certain querys in strict mode

Bug #33857	mysql hangs on certain querys in strict mode
Submitted:	14 Jan 2008 4:15	Modified:	15 Jan 2008 13:18
Reporter:	Oliver Gehring	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Any (windows and linux)
Assigned to:		CPU Architecture:	Any

Description:
Please sorry for my bad english, i hope you can unterstand me.

I have an strange error in mysql and i think this is a bug:

When mysql runs in strict mode an an certain order of statements comes in one query then mysql gives no answer and comes never back. I mean, mysql do not crash, only the connection who have made this query go of sleep and give no return-message.
This error comes only when:
- mysql runs in strict mode
- the table have  fields with "NOT NUL"-option
- the table have an TEXT ore BLOB field
- you make an INSERT with not all fields AFTER an INSERT with all fields

How to repeat:
ALL IN STRICT MODE!

First Create an table like this:

CREATE TABLE IF NOT EXISTS bugtestdb
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 f1 INT NOT NULL,
 f2 INT NOT NULL,
 t1 TEXT NOT NULL)
ENGINE=MyISAM;

then make follow query:
INSERT INTO bugtestdb (f1,f2,t1) VALUES (22,33,'is that a bug?');
result-> Query-Okay

then make follow query:
INSERT INTO bugtestdb (f1) VALUES (22);
result->error 1364-f2 have no default value
that is okay, the error message is korrekt!

and now make follow in one query:
INSERT INTO bugtestdb (f1,f2,t1) VALUES (22,33,'is that a bug?');
INSERT INTO bugtestdb (f1) VALUES (22);

NO RESULT -> mysql "hangs" and no message, error or anything comes back!!!
after 8 houres the connection goes lost (that is the default timeout)

and now:
please try this:
INSERT INTO bugtestdb (f1,f2) VALUES (22,33);
INSERT INTO bugtestdb (f1) VALUES (22);
result->comes back correctly with 1364-errors

here the complete querry to try:

SET sql_mode='STRICT_TRANS_TABLES';
CREATE TABLE IF NOT EXISTS bugtestdb
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 f1 INT NOT NULL,
 f2 INT NOT NULL,
 t1 TEXT NOT NULL)
ENGINE=MyISAM;
INSERT INTO bugtestdb (f1,f2,t1) VALUES (22,33,'is that a bug?');
INSERT INTO bugtestdb (f1) VALUES (22);

I think that effect is not realy want...

Suggested fix:
i dont know

Thank you for a problem report. Have you used mysql command line client to test? I am asking as I was not able to repeat the behaviour described:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot test -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.54-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

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

mysql> set @@sql_mode='strict_all_tables';
Query OK, 0 rows affected (0.05 sec)

mysql> select @@sql_mode;
+-------------------+
| @@sql_mode        |
+-------------------+
| STRICT_ALL_TABLES |
+-------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS bugtestdb
    -> (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->  f1 INT NOT NULL,
    ->  f2 INT NOT NULL,
    ->  t1 TEXT NOT NULL)
    -> ENGINE=MyISAM;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO bugtestdb (f1,f2,t1) VALUES (22,33,'is that a bug?');
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO bugtestdb (f1) VALUES (22);
ERROR 1364 (HY000): Field 'f2' doesn't have a default value

Now, 2 statements in one line:

mysql> INSERT INTO bugtestdb (f1,f2,t1) VALUES (22,33,'is that a bug?'); INSERT
INTO bugtestdb (f1) VALUES (22);
Query OK, 1 row affected (0.00 sec)

ERROR 1364 (HY000): Field 'f2' doesn't have a default value

Now your exact "How to repeat" pasted:

mysql> SET sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS bugtestdb
    -> (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->  f1 INT NOT NULL,
    ->  f2 INT NOT NULL,
    ->  t1 TEXT NOT NULL)
    -> ENGINE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> INSERT INTO bugtestdb (f1,f2,t1) VALUES (22,33,'is that a bug?');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO bugtestdb (f1) VALUES (22);
ERROR 1364 (HY000): Field 'f2' doesn't have a default value

I have test this effect in follow enviroments:

- PHP5-script with mysql-funktions on windows and linux
- PHP5-script with mysqli-funktions on windows and linux
- newest and older version of phpMyAdmin on windows and linux
- commandline on an fresh installed mysql 5.0.45 in an winXP-sp2 dos-box

on all this enviroments this error/effect exists!

now i have this test once again, the same effect on all enviroments.

i have made two screen-movies so you can see the effect with
your own eyes.

video-1 shows this effect in an XP-DOS-box with fresh installet mysql
video-2 shows this effect in an phpMyAdmin on an test-server

in both videos you can see mysql answers never when send two querys and the
first query have an TEXT-field (the same when it is an BLOB)

here you can get the videos:

video-1 as avi:
http://www.osone.net/bugmov/mysql-bug-movie.avi (..3MB)

video-1 as zip:
http://www.osone.net/bugmov/mysql-bug-movie.zip (..1MB)

video-2 as avi:
http://www.osone.net/bugmov/mysql-bug-movie-2.avi (..21MB)

video-2 as zip:
http://www.osone.net/bugmov/mysql-bug-movie-2.zip (..3MB)

i hope now you can see it an you can it repeat.

completion:

- you must show the video in full-screen-mode to see it correct
  (1280x1000)

- i used the community-edition, not the enterprise

- now i have it test on a account of an big german hoster (1und1),
   they have the newest mysql-version (5.0.45-community): the same effect!

- importand: you must send the sql-query without "newline" between the statements!!!
   example: sql-1;sql-2;sql-3; ->send
   not:
sql-1; ->send
sql-1; ->send
sql-1; ->send

in practice:

try not this:
-------------
SET sql_mode='STRICT_TRANS_TABLES';
CREATE TABLE IF NOT EXISTS bugtestdb (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f1 INT NOT NULL, f2 INT NOT NULL, t1 TEXT NOT NULL) ENGINE=MyISAM;
INSERT INTO bugtestdb (f1,f2,t1) VALUES (22,33,'is that a bug?');
INSERT INTO bugtestdb (f1) VALUES (22);

try this:
---------
SET sql_mode='STRICT_TRANS_TABLES';CREATE TABLE IF NOT EXISTS bugtestdb (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f1 INT NOT NULL, f2 INT NOT NULL, t1 TEXT NOT NULL) ENGINE=MyISAM;INSERT INTO bugtestdb (f1,f2,t1) VALUES (22,33,'is that a bug?');INSERT INTO bugtestdb (f1) VALUES (22);