MySQL Bugs: #49091: mysql source should support -xO4 optimization level using SunStudio12

Bug #49091	mysql source should support -xO4 optimization level using SunStudio12
Submitted:	25 Nov 2009 9:11	Modified:	10 Dec 2009 21:35
Reporter:	Sunanda Menon	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Compiling	Severity:	S3 (Non-critical)
Version:	5.1.30,5.1.37, 5.1 bzr	OS:	Other (OpenSolaris2009.06)
Assigned to:		CPU Architecture:	Any

Description:
Having built the mysql 5.1.30 source for OpenSolaris2009.06,the compilation goes through fine but reports a bug.The compilation flag used was -xO4 and the bug reproduced is as shown below.
On lowering the optimization level from -xO4 to -xO2 as is the case in mysql.com,the problem was resolved with a workaround for now.
But since -xO4 is supported by SS12,mysql source should also support the same level of performance on OpenSolaris.

To reproduce on the new and clean zone:

# pkg install SUNWmysql51
# pkg install SUNWwget
# svcadm enable mysql
# wget http://bugpipe.org/bugs/lbs-partial.mysqldump
# mysql test <lbs-partial.mysqldump
# mysql test

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.30 Source distribution

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

mysql>  select 1 from lbs_games g  left join lbs_tournaments t on
t.id=g.tournament_id left join lbs_teams t1 on t1.id=g.team_id_1  left join
lbs_teams t2 on t2.id=g.team_id_2;
ERROR 2013 (HY000): Lost connection to MySQL server during query

# cat /var/mysql/data/*.err

...
090914  3:40:01 - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary or
one of the libraries it was linked against is corrupt, improperly built, or
misconfigured. This error can also be caused by malfunctioning hardware. We
will try our best to scrape up some info that will hopefully help diagnose the
problem, but since we have already crashed, something is definitely wrong and
this may fail.

key_buffer_size=16384
read_buffer_size=262144
max_used_connections=1
/usr/mysql/5.1/bin/mysqld'my_print_stacktrace+0x1c [0x874a108]
/usr/mysql/5.1/bin/mysqld'handle_segfault+0x1f3 [0x82be297]
/lib/libc.so.1'__sighndlr+0xf [0xfeb0d0cf]
/lib/libc.so.1'call_user_handler+0x2af [0xfeb001bf]
/usr/mysql/5.1/bin/mysqld'__1cQbest_access_path6FpnEJOIN_pnNst_join_table_pnDTHD_XIdd_v_+0x5a1
[0x83433e1]
/usr/mysql/5.1/bin/mysqld'__1cbGbest_extension_by_limited_search6FpnEJOIN_XIddII_b_+0x251
[0x8345335]
/usr/mysql/5.1/bin/mysqld'__1cbGbest_extension_by_limited_search6FpnEJOIN_XIddII_b_+0x58d
[0x8345671]
/usr/mysql/5.1/bin/mysqld'__1cbGbest_extension_by_limited_search6FpnEJOIN_XIddII_b_+0x58d
[0x8345671]
/usr/mysql/5.1/bin/mysqld'__1cbGbest_extension_by_limited_search6FpnEJOIN_XIddII_b_+0x58d
[0x8345671]
/usr/mysql/5.1/bin/mysqld'__1cLchoose_plan6FpnEJOIN_X_b_+0x2b3 [0x8344d77]
/usr/mysql/5.1/bin/mysqld'__1cUmake_join_statistics6FpnEJOIN_pnKTABLE_LIST_pnEItem_pnQst_dynamic_array__b_+0x16d9
[0x833f6c9]
/usr/mysql/5.1/bin/mysqld'__1cEJOINIoptimize6M_i_+0x5a0 [0x8339c20]
/usr/mysql/5.1/bin/mysqld'__1cMmysql_select6FpnDTHD_pppnEItem_pnKTABLE_LIST_IrnEList4n0B___p2IpnIst_order_9D39DXpnNselect_result_pnSst_select_lex_unit_pnNst_select_lex__b_+0x363
[0x833de7b]
/usr/mysql/5.1/bin/mysqld'__1cNhandle_select6FpnDTHD_pnGst_lex_pnNselect_result_L_b_+0x11f
[0x83385d3]
/usr/mysql/5.1/bin/mysqld'__1cVexecute_sqlcom_select6FpnDTHD_pnKTABLE_LIST__b_+0x20f
[0x82d8f73]
/usr/mysql/5.1/bin/mysqld'__1cVmysql_execute_command6FpnDTHD__i_+0x497
[0x82d19ff]
/usr/mysql/5.1/bin/mysqld'__1cQdispatch_command6FnTenum_server_command_pnDTHD_pcI_b_+0x2418
[0x82cfc54]
/usr/mysql/5.1/bin/mysqld'__1cKdo_command6FpnDTHD__b_+0x105 [0x82cd695]
/usr/mysql/5.1/bin/mysqld'handle_one_connection+0x3b6 [0x82cba12]
/lib/libc.so.1'_thrp_setup+0x7e [0xfeb0cd66]
/lib/libc.so.1'_lwp_start+0x0 [0xfeb0cff0]
max_threads=151
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 49277
Kbytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0xb1b0608
Attempting backtrace. You can use the following information to find out where
mysqld died. If you see no messages after this, something went terribly
wrong...
stack_bottom = fe2a0fb0 thread_stack 0x20000
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at b252618 = select 1 from lbs_games g  left join lbs_tournaments t
on
t.id=g.tournament_id left join lbs_teams t1 on t1.id=g.team_id_1  left join
lbs_teams t2
on t2.id=g.team_id_2
thd->thread_id=1
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
090914 03:40:01 mysqld_safe mysqld restarted

To verify that the mysql.com version works:

# pkg uninstall SUNWmysql
# pkg install SUNWpkgcmds
# wget
http://downloads.mysql.com/archives/mysql-5.1/mysql-5.1.30-solaris10-i386.pkg.gz
# gunzip mysql-*.gz
# pkgadd -d mysql*.pkg
# /opt/mysql/mysql/scripts/mysql_install_db
# /opt/mysql/mysql/bin/mysqld_safe &
# mysql test < lbs-partial.mysqldump
# mysql 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 1
Server version: 5.1.30 MySQL Community Server (GPL)

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

mysql>  select 1 from lbs_games g  left join lbs_tournaments t on
t.id=g.tournament_id left join lbs_teams t1 on t1.id=g.team_id_1  left join
lbs_teams t2 on t2.id=g.team_id_2;
...
| 1 |
| 1 |
...

--

How to repeat:
Build the mysql.com source using -x04 compilation flag and run the steps as shown
on a new and clean zone.

Thank you for the problem report. Please, check with a newer version of MySQL server, 5.1.41, and inform about the results.

This is not fixed in 5.1.37 that I tested right now.I will not be able to keep testing the sources due to time constraints,but the problem does exists.

Thank you for the report.

Verified as described.

Configure used:

./configure --prefix=/users/ssmirnova/host/build/mysql-5.1 --enable-assembler --with-extra-charsets=complex --enable-thread-safe-client --with-big-tables  --with-plugins=max-no-ndb --with-embedded-server --enable-local-infile CC=cc CXX=CC CFLAGS=-xO4 CXXFLAGS=-xO4

Problem is not repeatable on Solaris Sparc box.

With -xO3, it crashes in a different function.

$ mdb ../sql/mysqld var/mysqld.1/data/core
Loading modules: [ libc.so.1 ld.so.1 ]
> $c
libc.so.1`_lwp_kill+7(4, b, fea2c658)
my_write_core+0x24(b, 8601db4, ea2e, febbff59)
handle_segfault+0x1ac(b, 0, fea2c770)
libc.so.1`__sighndlr+0xf(b, 0, fea2c770, 8219c28)
libc.so.1`call_user_handler+0x22b(b, 0, fea2c770)
libc.so.1`sigacthandler+0x65(b, 0, fea2c770)
__1cRprev_record_reads6FpnEJOIN_IX_d_+0x2df(89f1860, 3, 1, 0)
__1cQbest_access_path6FpnEJOIN_pnNst_join_table_pnDTHD_XIdd_v_+0x1e8(89f1860, 89eeaa4, 89c2b88, 8, 0, 3)
__1cbGbest_extension_by_limited_search6FpnEJOIN_XIddII_b_+0x11d(89f1860, 8, 0, 3, 0, 40cca580)
__1cbGbest_extension_by_limited_search6FpnEJOIN_XIddII_b_+0x2e2(89f1860, c, 0, 2, 0, 40cca580)
__1cbGbest_extension_by_limited_search6FpnEJOIN_XIddII_b_+0x2e2(89f1860, e, 0, 1, 0, 40cca580)
__1cbGbest_extension_by_limited_search6FpnEJOIN_XIddII_b_+0x2e2(89f1860, f, 0, 0, 0, 3ff00000)
__1cNgreedy_search6FpnEJOIN_XII_b_+0xe1(89f1860, f, 0, 3e, 1, 4)
__1cLchoose_plan6FpnEJOIN_X_b_+0xd6(89f1860, f, 0, 8290073)
__1cUmake_join_statistics6FpnEJOIN_pnKTABLE_LIST_pnEItem_pnQst_dynamic_array__b_+0x13fa(89f1860, 89ecee0, 0, 89f2960)
__1cEJOINIoptimize6M_i_+0x443(89f1860, 0, 89c3e80, 89c3c10)
__1cMmysql_select6FpnDTHD_pppnEItem_pnKTABLE_LIST_IrnEList4n0B___p2IpnIst_order_9D39DXpnNselect_result_pnSst_select_lex_unit_pnNst_select_lex
__b_+0x2f4(89c2b88, 89c3f78, 89ecee0, 0, 89c3f14, 0)
__1cNhandle_select6FpnDTHD_pnGst_lex_pnNselect_result_L_b_+0xf4(89c2b88, 89c3bb4, 89ee528, 0)
__1cVexecute_sqlcom_select6FpnDTHD_pnKTABLE_LIST__b_+0x17a(89c2b88, 89ecee0, 0, 0)
__1cVmysql_execute_command6FpnDTHD__i_+0x429(89c2b88, fea2ebb0, 0, a7)
__1cQdispatch_command6FnTenum_server_command_pnDTHD_pcI_b_+0x2154(3, 89c2b88, 89e4c79, a7)
__1cKdo_command6FpnDTHD__b_+0xf0(89c2b88, 3c, fea2efd8, 8225f01)
handle_one_connection+0x303(89c2b88)
libc.so.1`_thr_setup+0x4e(fea30a00)
libc.so.1`_lwp_start(fea30a00, 0, 0, fea2eff8, febc72e0, fea30a00)

Difference in prev_record_reads optimisation between -xO2:

         5479. static double
          5480. prev_record_reads(JOIN *join, uint idx, table_map found_ref)
          5481. {
          5482.   double found=1.0;
          5483.   POSITION *pos_end= join->positions - 1;
        
        Discovered loop below has tag L266
        Bounds test for L266 moved to top of loop
          5485.   for (POSITION *pos= join->positions + idx - 1; pos != pos_end; pos--)

And -xO3:

          5479. static double
          5480. prev_record_reads(JOIN *join, uint idx, table_map found_ref)
          5481. {
          5482.   double found=1.0;
          5483.   POSITION *pos_end= join->positions - 1;
        
        Source loop below has tag L49
        L49 multi-versioned for improved-termination-test. Specialized version is L218
          5485.   for (POSITION *pos= join->positions + idx - 1; pos != pos_end; pos--)

Workaround: 
#pragma opt 2 (prev_record_reads)

Setting to W5 since this bug only affects users who compile from source.

Setting effort to E2. This is a compiler bug, but we could conditionallly include the above workaround on Solaris/Intel platforms.

See bug #41710.

I had this bug on a fresh installed OpenSOlaris 2009.06 and the GA webstack (# pkg install amp)  that I installed a few days ago.

I just want to mention that I had the issue on a non-recompiled version of mysql:

root# pkg list SUNWmysql51     
NAME (PUBLISHER)                              VERSION         STATE      UFIX
SUNWmysql51                                   5.1.30-0.111    installed  ----

root# cat /etc/release
                         OpenSolaris 2009.06 snv_111b X86
           Copyright 2009 Sun Microsystems, Inc.  All Rights Reserved.
                        Use is subject to license terms.
                              Assembled 07 May 2009

I was able to reproduce the issue easyly using the SQL commands given in the error messages and pasted it into a mysql session => mysqld crashed.

So, I assume that a lot of people will hit this bug.

I 'll need to revert to Mysql 5.0.67 version.

Regards,

Duplicate of  bug#43741

Probably should be marked Critical as it is part of the standard OpenSolaris download and it is forcing a downgrade. 

Details

          Name: SUNWmysql51
       Summary: MySQL 5.1 - MySQL Database Management System (root component)
     Publisher: opensolaris.org
       Version: 5.1.30
 Build Release: 5.11
        Branch: 0.111
Packaging Date: Fri May  8 16:23:07 2009
          Size: 448.23 MB
          FMRI: pkg:/SUNWmysql51@5.1.30,5.11-0.111:20090508T162307Z