MySQL Bugs: #27816: Log tables ran with partitions crashes the server when logging is enabled.

Bug #27816	Log tables ran with partitions crashes the server when logging is enabled.
Submitted:	13 Apr 2007 19:11	Modified:	16 Jun 2007 13:13
Reporter:	Tobias Asplund	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S1 (Critical)
Version:	5.1.16, 5.1.17, 5.1.18BK	OS:	MacOS (Windows)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
When a partition is created on the log tables, the server crashes when it tries to write a log event.

How to repeat:
-- Make sure you have log_output = FILE in your my.cnf

SET GLOBAL general_log = 0;
ALTER TABLE general_log   PARTITION BY RANGE (TO_DAYS(event_time)) (   PARTITION p0 VALUES LESS THAN (733144) );

mysql> ALTER TABLE general_log   PARTITION BY RANGE (TO_DAYS(event_time)) (   PARTITION p0 VALUES LESS THAN (733144) );
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SET GLOBAL general_log = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM general_log;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 

Suggested fix:
Please don't just disallow partitions on this, like it happened with triggers crashing under these circumstances, there are a lot of cases why you'd want this (and triggers) to work on log tables to make it a useful tool.

Tobias, this seems to have been fixed already:

mysql> ALTER TABLE general_log   PARTITION BY RANGE (TO_DAYS(event_time)) (   PARTITION
    -> p0 VALUES LESS THAN (733144) );
ERROR 1562 (HY000): Engine cannot be used in partitioned tables
mysql> SET GLOBAL general_log = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM general_log;
+---------------------+-------------
| event_time          | user_host
+---------------------+-------------
| 2007-04-14 07:08:28 | [root] @  [1
+---------------------+-------------
1 row in set (0.05 sec)

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.1.18-beta-debug |
+-------------------+
1 row in set (0.00 sec)

The bug described in the original report happens if you alter the log tables to be used as MyISAM (Which I guess most people would be using it for that use this since you can actually index it).

On 5.1.17 on Windows, this actually causes the whole server to hang whilst enabling general query logging:

mysql> use mysql
Database changed
mysql> SET GLOBAL general_log = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table general_log engine = myisam;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE general_log   PARTITION BY RANGE (TO_DAYS(event_time)) (   PARTITION
    -> p0 VALUES LESS THAN (733144) );
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SET GLOBAL general_log = 1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

The lost connection is attempting to stop the service, which kills the thread, but does not manage to actually kill the server/service. 

Whilst the server is hung - no other connections can connect to the server either. 

Setting to S1, P2, Verifying - as this is an indefinite hang/loss of service.

I hadn't seen that the tables must be altered to be myisam.
5.1.18BK crashes now, using Mark's testcase:

Version: '5.1.18-beta-debug'  socket: '/tmp/mysql.sock'  port: 3306  yes
070416 18:08:45 - 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=8388600
read_buffer_size=131072
max_used_connections=1
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 = 337597 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x8dce318
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...
Cannot determine thread, fp=0x428624ac, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x81f8fec handle_segfault + 796
0x823beda _Z10MYSQLparsePv + 86970
0x811b117 _Z22mysql_unpack_partitionP3THDPKcjS2_jP8st_tablebP10handlerton + 951
0x8255f2b _Z21open_table_from_shareP3THDP14st_table_sharePKcjjjP8st_tableb + 811
0x824b8ee _Z17open_unireg_entryP3THDP8st_tableP13st_table_listPKcPcjP11st_mem_rootj + 430
0x824e364 _Z10open_tableP3THDP13st_table_listP11st_mem_rootPbj + 2788
0x824f2d7 _Z11open_tablesP3THDPP13st_table_listPjj + 903
0x824f932 _Z25simple_open_n_lock_tablesP3THDP13st_table_list + 98
0x82b1209 _ZN24Log_to_csv_event_handler14open_log_tableEj + 281
0x82b1829 _ZN6LOGGER20activate_log_handlerEP3THDj + 505
0x82220eb _ZN17sys_var_log_state6updateEP3THDP7set_var + 155
0x8215b69 _ZN7set_var6updateEP3THD + 89
0x822108c _Z17sql_set_variablesP3THDP4ListI12set_var_baseE + 188
0x820cc31 _Z21mysql_execute_commandP3THD + 18225
0x82129a4 _Z11mysql_parseP3THDPcj + 612
0x8213cc1 _Z16dispatch_command19enum_server_commandP3THDPcj + 4545
0x8214985 _Z10do_commandP3THD + 421
0x820045f handle_one_connection + 271
0x4004daa7 _end + 931782103
0x4017ec2e _end + 933031774
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do 
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at (nil)  is invalid pointer
thd->thread_id=0
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
070416 18:08:45  mysqld restarted

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/28404

ChangeSet@1.2551, 2007-06-08 17:12:42+03:00, gkodinov@magare.gmz +4 -0
  Bug #27816: Log tables ran with partitions crashes the server 
   when logging is enabled.
  Currently the partition engine doesn't allow log tables to
  be partitioned. But this was not checked and the server crashed.
  Fixed by adding a check in ALTER TABLE to disable partitioning the
  log tables.
  While working on the cause of the problem improved the way the log
  thread structures are initialized before opening the log tables.

Pushed into 5.1.20-beta

"Make sure you have log_output = FILE in your my.cnf" <- Something in the water does not compute. Please explain.

Jon, 

right, the reported probably meant TABLE there. Check Mark's comment [16 Apr] : I used his commands (that are not windows specific) to reproduce (and fix) the bug.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented feature change in 5.1.20 changelog, Log Tables, and Partitioning Limitations section of 5.1 Manual.