MySQL Bugs: #42956: SBL, INSERT .. SELECT .. LIMIT error,STRICT_TRANS

Bug #42956	SBL, INSERT .. SELECT .. LIMIT error,STRICT_TRANS_TABLES mode, even MyISAM table
Submitted:	18 Feb 2009 5:10	Modified:	16 Jun 2009 15:43
Reporter:	Guangbao Ni	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1-bugteam, 5.1, 6.0 bzr	OS:	Any
Assigned to:	Luis Soares	CPU Architecture:	Any

Description:
Wrongly elevate warning to error for non transactional table, even when SQL_MODE=STRICT_TRANS_TABLES.

How to repeat:
mysql> use test;
Database changed
mysql> drop table if exists r1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop table if exists r2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table r1 (a int primary key) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> create table r2 (a int primary key)ENGINE=MyISAM;;
Query OK, 0 rows affected (0.00 sec)

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

mysql>insert into r1 select * from t2 limit 1;

Suggested fix:
N/A

Thank you for the report.

I can not repeat described behavior.

Additionally test contains misprint;

insert into r1 select * from t2 limit 1;

instead of

insert into r1 select * from r2 limit 1; (I guess)

Please check if you tested with correct query.

Sorry, misspelling.
Did you turn on logging function and binlog-fromat=statement?

/Guangbao

Thank you for the feedback.

You are right: I forgot about binary logging.

Bot mysql-5.1 and mysql-5.1-bugteam crash for me with following backtrace:

Thread 1 (process 20534):
#0  0x002ce402 in __kernel_vsyscall ()
#1  0x0046264f in pthread_kill () from /lib/libpthread.so.0
#2  0x085a3775 in my_write_core (sig=6) at stacktrace.c:310
#3  0x0824c139 in handle_segfault (sig=6) at mysqld.cc:2505
#4  <signal handler called>
#5  0x002ce402 in __kernel_vsyscall ()
#6  0x00314f90 in raise () from /lib/libc.so.6
#7  0x00316678 in abort () from /lib/libc.so.6
#8  0x0030e269 in __assert_fail () from /lib/libc.so.6
#9  0x08232681 in Diagnostics_area::set_ok_status (this=0x9ed8f34, thd=0x9ed8188, affected_rows_arg=0, last_insert_id_arg=0, message_arg=0xb74ba080 "Records: 0  Duplicates: 0  Warnings: 0")
    at sql_class.cc:436
#10 0x08174c4f in my_ok (thd=0x9ed8188, affected_rows=0, id=0, message=0xb74ba080 "Records: 0  Duplicates: 0  Warnings: 0") at sql_class.h:2253
#11 0x082f86f8 in select_insert::send_eof (this=0x9f3b7c0) at sql_insert.cc:3215
#12 0x082eb060 in return_zero_rows (join=0x9f3b838, result=0x9f3b7c0, tables=0x9f3b570, fields=@0x9ed95b0, send_row=false, select_options=3489942016, info=0x86d35c8 "no matching row in const table", 
    having=0x0) at sql_select.cc:7071
#13 0x082eb70f in JOIN::exec (this=0x9f3b838) at sql_select.cc:1725
#14 0x082ed809 in mysql_select (thd=0x9ed8188, rref_pointer_array=0x9ed9614, tables=0x9f3b570, wild_num=1, fields=@0x9ed95b0, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, 
    select_options=3489942016, result=0x9f3b7c0, unit=0x9ed92ac, select_lex=0x9ed951c) at sql_select.cc:2378
#15 0x082edb57 in handle_select (thd=0x9ed8188, lex=0x9ed9250, result=0x9f3b7c0, setup_tables_done_option=1073741824) at sql_select.cc:268
#16 0x08264260 in mysql_execute_command (thd=0x9ed8188) at sql_parse.cc:3142
#17 0x0826a85e in mysql_parse (thd=0x9ed8188, inBuf=0x9f3b210 "insert into r1 select * from r2 limit 1", length=39, found_semicolon=0xb74bb2fc) at sql_parse.cc:5810
#18 0x0826b49a in dispatch_command (command=COM_QUERY, thd=0x9ed8188, packet=0x9f282f1 "insert into r1 select * from r2 limit 1", packet_length=39) at sql_parse.cc:1216
#19 0x0826c6b8 in do_command (thd=0x9ed8188) at sql_parse.cc:857
#20 0x08259315 in handle_one_connection (arg=0x9ed8188) at sql_connect.cc:1115
#21 0x0045fbd4 in start_thread () from /lib/libpthread.so.0
#22 0x003b74fe in clone () from /lib/libc.so.6

Version 6.0 crashes as well.

Hi Sveta,

The crash is caused by bug#42640, but the bug is report another issues. :)

To clarify:

in 5.1.31 "Statement is not safe to log in statement format." was error, but not warning:

$cat src/tests/t/bug42956.test  -n
     1  --source include/have_log_bin.inc
     2
     3  set binlog_format='statement';
     4
     5  drop table if exists r1;
     6
     7  drop table if exists r2;
     8
     9  create table r1 (a int primary key) ENGINE=MyISAM;
    10
    11  create table r2 (a int primary key)ENGINE=MyISAM;
    12
    13  SET @@SQL_MODE = STRICT_TRANS_TABLES;
    14
    15  insert into r1 select * from r2 limit 1;

$do_test.sh -b mysql-5.1.31-linux-i686-glibc23 -s .
Logging: ./mysql-test-run.pl --record --force bug42956
090616 17:40:21 [Warning] Forcing shutdown of 1 plugins
MySQL Version 5.1.31
Using dynamic switching of binlog format
Skipping ndbcluster, mysqld not compiled with ndbcluster
Setting mysqld to support SSL connections
Using MTR_BUILD_THREAD      = 0
Using MASTER_MYPORT         = 9306
Using MASTER_MYPORT1        = 9307
Using SLAVE_MYPORT          = 9308
Using SLAVE_MYPORT1         = 9309
Using SLAVE_MYPORT2         = 9310
Using IM_PORT               = 9313
Using IM_MYSQLD1_PORT       = 9314
Using IM_MYSQLD2_PORT       = 9315
Killing Possible Leftover Processes
Removing Stale Files
Creating Directories
Installing Master Database
Saving snapshot of installed databases
=======================================================

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

main.bug42956                  [ fail ]

mysqltest: At line 15: query 'insert into r1 select * from r2 limit 1' failed: 1592: Statement is not safe to log in statement format.

With current 5.1 sources bug is not repeatable.