MySQL Bugs: #14105: MySQL crashing on CREATE ... SELECT statement

Bug #14105	MySQL crashing on CREATE ... SELECT statement
Submitted:	18 Oct 2005 1:27	Modified:	22 Feb 2006 8:10
Reporter:	Tobias Asplund	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.15	OS:	Linux (Linux)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
crashing the server with a constructed CREATE TABLE ... SELECT command.

Assertion failed: (data_offset == ((null_count + 7) / 8)), function make_empty_rec, file unireg.cc, line 775.
mysqld got signal 6;
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=8388608
read_buffer_size=1044480
max_used_connections=2
max_connections=20
threads_connected=2
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 192431 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

How to repeat:
-- Use the world.sql database at
-- http://downloads.mysql.com/docs/world.sql

CREATE TABLE Continents (
  Continent VARCHAR(20) NOT NULL, 
  Population BIGINT UNSIGNED NOT NULL DEFAULT'0', 
  Countries TINYINT NOT NULL
)  
SELECT Continent, SUM(Population) AS Population , COUNT(*) AS Countries   
FROM Country 
GROUP BY Continent;

I was unable for to repeat the crash on Linux, with today BK source
server:

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

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

mysql> CREATE TABLE Continents (
    ->   Continent VARCHAR(20) NOT NULL, 
    ->   Population BIGINT UNSIGNED NOT NULL DEFAULT'0', 
    ->   Countries TINYINT NOT NULL
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT Continent, SUM(Population) AS Population , COUNT(*) AS Countries
    ->   
    -> FROM Country 
    -> GROUP BY Continent;
+---------------+------------+-----------+
| Continent     | Population | Countries |
+---------------+------------+-----------+
| Asia          | 3705025700 |        51 |
| Europe        |  730074600 |        46 |
| North America |  482993000 |        37 |
| Africa        |  784475000 |        58 |
| Oceania       |   30401150 |        28 |
| Antarctica    |          0 |         5 |
| South America |  345780000 |        14 |
+---------------+------------+-----------+
7 rows in set (0.01 sec)

mysql> 

could you please test on FreeBSD.

I tried this on 5.4-STABLE, using the mysql50-server port and also using the mysql-debug-5.0.13-rc binary from the MySQL downloads site.

I was unable to get a crash.  I did:

mysqladmin create world
mysql world < world.sql
mysql world
mysql> SELECT Continent, SUM(Population) AS Population , COUNT(*) AS Countries    FROM Country  GROUP BY Continent;
+---------------+------------+-----------+
| Continent     | Population | Countries |
+---------------+------------+-----------+
| Asia          | 3705025700 |        51 |
| Europe        |  730074600 |        46 |
| North America |  482993000 |        37 |
| Africa        |  784475000 |        58 |
| Oceania       |   30401150 |        28 |
| Antarctica    |          0 |         5 |
| South America |  345780000 |        14 |
+---------------+------------+-----------+
7 rows in set (0.00 sec)

Regards,

Timothy

I just re-tested this, using the actual procedure reported in the bug and not something I made up in my head.  :)  I can repeat this now.

Here is a back trace from gdb:

#0  0x425e27a2 in _dl_sysinfo_int80 () from /lib/ld-linux.so.2
#1  0x42621e59 in raise () from /lib/tls/libc.so.6
#2  0x42623882 in abort () from /lib/tls/libc.so.6
#3  0x4261b728 in __assert_fail () from /lib/tls/libc.so.6
#4  0x081fd448 in make_empty_rec (thd=0x8b9a068, file=36, 
    table_type=DB_TYPE_MYISAM, table_options=9, create_fields=@0x0, 
    reclength=31, data_offset=1) at unireg.cc:780
#5  0x081fbd83 in mysql_create_frm (thd=0x8b9a068, 
    file_name=0x8289e0 "./world/continents.frm", db=0x8b86068 "world", 
    table=0x8bc6558 "continents", create_info=0x8b9a5cc, 
    create_fields=@0x8b9a4f4, keys=0, key_info=0x8bc81c0, db_file=0x82776f)
    at unireg.cc:157
#6  0x081fc092 in rea_create_table (thd=0x8b9a068, 
    file_name=0x8289e0 "./world/continents.frm", db=0x8b86068 "world", 
    table=0x8bc6558 "continents", create_info=0x8b9a5cc, 
    create_fields=@0x8b9a4f4, keys=0, key_info=0x8bc81c0) at unireg.cc:261
#7  0x0822be98 in mysql_create_table (thd=0x8b9a068, db=0x8b86068 "world", 
    table_name=0x8bc6558 "continents", create_info=0x8b9a5cc, 
    fields=@0x8b9a4f4, keys=@0x8b9a4e8, internal_tmp_table=false, 
    select_field_count=3) at sql_table.cc:1618
#8  0x0822c377 in create_table_from_items (thd=0x8b9a068, 
    create_info=0x8b9a5cc, create_table=0x8bc6590, extra_fields=0x8b9a4f4, 
    keys=0x8b9a4e8, items=0x0, lock=0x8bc6fc8) at sql_table.cc:1757
#9  0x081cf778 in select_create::prepare (this=0x8bc6f60, values=@0x8b9a344, 
    u=0x0) at sql_insert.cc:2482
#10 0x081adef5 in JOIN::prepare (this=0x8bc6fd0, rref_pointer_array=0x8b9a3e0, 
    tables_init=0x0, wild_num=0, conds_init=0x0, og_num=1, order_init=0x0, 
    group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x0, 
    unit_arg=0x8b9a0b8) at sql_select.cc:471
#11 0x081b168e in mysql_select (thd=0x8b9a068, rref_pointer_array=0x8b9a3e0, 
    tables=0x8bc6cd0, wild_num=0, fields=@0x8b9a344, conds=0x0, og_num=1, 
    order=0x0, group=0x8bc6f28, having=0x0, proc_param=0x0, 
    select_options=2424588800, result=0x8bc6f60, unit=0x8b9a0b8, 
    select_lex=0x8b9a2c4) at sql_select.cc:1811
#12 0x081ad77a in handle_select (thd=0x8b9a068, lex=0x8b9a0a8, 
    result=0x8bc6f60, setup_tables_done_option=0) at sql_select.cc:234
#13 0x0817a410 in mysql_execute_command (thd=0x8b9a068) at sql_parse.cc:2862
#14 0x08180ae8 in mysql_parse (thd=0x8b9a068, 
    inBuf=0x8bc6420 "CREATE TABLE Continents (   Continent VARCHAR(20) NOT NULL,    Population BIGINT UNSIGNED NOT NULL DEFAULT'0',    Countries TINYINT NOT NULL )   SELECT Continent, SUM(Population) AS Population , COUNT"..., 
    length=146383016) at sql_parse.cc:5536
#15 0x08177f95 in dispatch_command (command=COM_QUERY, thd=0x8b9a068, 
    packet=0x8bbe3c1 "CREATE TABLE Continents (   Continent VARCHAR(20) NOT NULL,    Population BIGINT UNSIGNED NOT NULL DEFAULT'0',    Countries TINYINT NOT NULL )   SELECT Continent, SUM(Population) AS Population , COUNT"..., 
    packet_length=253) at sql_parse.cc:1697
#16 0x08177955 in do_command (thd=0x8b9a068) at sql_parse.cc:1498
#17 0x08176c54 in handle_one_connection (arg=0x0) at sql_parse.cc:1143
#18 0x4274898c in start_thread () from /lib/tls/libpthread.so.0
#19 0x426b67da in clone () from /lib/tls/libc.so.6

I forgot to mention in my previous comment - I tested this on Linux with the latest BK code for 5.0.15.  Tobias mentioned that this query has been crashing for him since around 5.0.6, but not on Windows.

Thank you for bugreport, but I can't repeat it on current bk:
1) mysql build with compile-pentium-debug-max (linux)
2) cd mysql-test; ./mysql-test-run alias (prepare empty db)
3) run mysql
../sql/mysqld --no-defaults --basedir=. --datadir=./var/master-data --skip-innodb --skip-ndbcluster --skip-bdb     --language=../sql/share/english/ --character-sets-dir=../sql/share/charsets/ --key_buffer_size=8388608 --read_buffer_size=1044480 --max_connections=20
4) from the other console:
[bell@sanja mysql-test]$ ../client/mysql -u root test <world.sql.1
[bell@sanja mysql-test]$ ../client/mysql -u root 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 3 to server version: 5.0.17-debug

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

mysql> CREATE TABLE Continents (
    ->   Continent VARCHAR(20) NOT NULL,
    ->   Population BIGINT UNSIGNED NOT NULL DEFAULT'0',
    ->   Countries TINYINT NOT NULL
    -> )
    -> SELECT Continent, SUM(Population) AS Population , COUNT(*) AS Countries  
    -> FROM Country
    -> GROUP BY Continent;
Query OK, 7 rows affected, 2 warnings (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql>

Was that original table, Country, created on 5.0.15 or on some previous version of MySQL, 4.x.y?

I think it worked like it should on 4.1, but crashed 5.0.

Please, answer my question:

Was that original table, Country, created on some previous version of MySQL, 4.x.y, and then you just upgraded binaries to 5.0.15?

I need to know that, because if it was created in 4.x.y, it can lead me to a possible reason and a way to repeat the behaviour you initially described.

flupps@eeyore:~$ mysqladmin create bugtest
flupps@eeyore:~$ mysql bugtest < /www/mysql/Data-world.sql
flupps@eeyore:~$ mysql bugtest
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 129081 to server version: 5.0.15-debug-log

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

mysql> CREATE TABLE Continents (
    ->   Continent VARCHAR(20) NOT NULL,
    ->   Population BIGINT UNSIGNED NOT NULL DEFAULT'0',
    ->   Countries TINYINT NOT NULL
    -> )
    -> SELECT Continent, SUM(Population) AS Population , COUNT(*) AS Countries
    -> FROM Country
    -> GROUP BY Continent;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Thank you for a test case, but, as you already know from the previous messages, this test case works OK since 5.0.17 at least. So, please, upgrade to a newer version, 5.0.18.

I had a (strange) idea about upgrade from 4.x.y as a possible reason for the difference in tests results, but you proved it is wrong. Thank you.