-2

Recently, a production MySQL server has crashed occasionally after upgrade from 5.5 to 5.7.19. The following is the stack trace in error log and the table related to the problematic query。I had turned on the general log and every time MySQL crashed. There was a very large insert on duplicate key query in the last few log entries. 

0xf4bd75 my_print_stacktrace + 53
0x7d0144 handle_fatal_signal + 1188
0x34d8a0f710 _end + -693094128
0x800b23 Field_blob::copy_blob_value(st_mem_root*) + 51
0xe9af6e mysql_prepare_blob_values(THD*, List<Item>&, st_mem_root*) + 686
0xe9b575 write_record(THD*, TABLE*, COPY_INFO*, COPY_INFO*) + 565
0xe9c952 Sql_cmd_insert::mysql_insert(THD*, TABLE_LIST*) + 2146
0xe9d16e Sql_cmd_insert::execute(THD*) + 222
0xd10279 mysql_execute_command(THD*, bool) + 4025
0xd1481d mysql_parse(THD*, Parser_state*) + 1005
0xd160ac dispatch_command(THD*, COM_DATA const*, enum_server_command) + 6188
0xd16a74 do_command(THD*) + 404
0xdea70c handle_connection + 668
0xf69d64 pfs_spawn_thread + 372
0x34d8a079d1 _end + -693126191
0x311e4e8b6d _end + 475909485


CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col2` varchar(128) DEFAULT NULL, 
  `col3` int(11) DEFAULT NULL ,
  `col4` int(11) DEFAULT NULL ,
  `col5` int(11) DEFAULT NULL ,
  `col6` int(11) DEFAULT NULL ,
  `col7` varchar(128) DEFAULT NULL ,
  `col8` varchar(1024) DEFAULT NULL ,
  `report` longtext ,
  `create_date` datetime DEFAULT NULL ,
  `start_date` datetime DEFAULT NULL ,
  `finish_date` datetime DEFAULT NULL ,
  `state` varchar(128) DEFAULT NULL ,
  `col9` text ,
  `col10` int(11) DEFAULT NULL ,
  `col11` int(11) DEFAULT NULL ,
  `col12` text ,
  `count_post` int(11) DEFAULT NULL ,
  `count_reply` int(11) DEFAULT NULL ,
  `count_link` int(11) DEFAULT NULL ,
  `remark` text ,
  PRIMARY KEY (`id`),
  UNIQUE KEY `col2` (`col2`),
  KEY `col4` (`col4`),
  KEY `col5` (`col5`),
  KEY `col6` (`col6`),
  KEY `col7` (`col7`),
  KEY `create_date` (`create_date`),
  KEY `finish_date` (`finish_date`)
) ENGINE=InnoDB AUTO_INCREMENT=405597973 DEFAULT CHARSET=utf8'

Is it this a bug of 5.7.19? I have found a related issue Crash on UPDATE ON DUPLICATE KEY but I can not repeat it. How can I avoid this or how can I fixed it?

As Wilson Hauck pointed out, the following are things related to threads_%

mysql> SHOW GLOBAL STATUS LIKE 'threads_%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 1     |
| Threads_connected | 2530  |
| Threads_created   | 5920  |
| Threads_running   | 2     |
+-------------------+-------+

mysql>  SHOW GLOBAL VARIABLES LIKE 'thread_%';
+-------------------+---------------------------+
| Variable_name     | Value                     |
+-------------------+---------------------------+
| thread_cache_size | 8                         |
| thread_handling   | one-thread-per-connection |
| thread_stack      | 262144                    |
+-------------------+---------------------------+
zczhuohuo
  • 169
  • 1
  • 13
  • It appears THREAD creation/spawning was involved in the problem. Could you share with us results of SHOW GLOBAL STATUS LIKE 'threads_%'; and SHOW GLOBAL VARIABLES LIKE 'thread_%' – Wilson Hauck Sep 23 '17 at 14:15
  • @WilsonHauck I had appended things related to `thread` at the end of my question. – zczhuohuo Sep 25 '17 at 14:29

3 Answers3

2

Looks identical to https://bugs.mysql.com/bug.php?id=79243 and https://bugs.launchpad.net/percona-server/+bug/1667552 I would suggest comment on the upstream bug and follow that bug and click 'Affects me'.

Daniël van Eeden
  • 603
  • 5
  • 7
0

The MySQL 5.7.19 changelog says this under bugs fixed:

Incorrect behavior could occur for INSERT statements executed in stored-program or prepared-statement context, if the VALUES part of an ON DUPLICATE KEY UPDATE clause referred to a BLOB value in the INSERT column list. (Bug #24538207, Bug #25361251, Bug #25530880, Bug #25684790)

Evidently they didn't fix it fully in 5.7.19. :-(

But it suggests a workaround to me.

If you have a statement like this:

INSERT INTO t (id, report) VALUES (1234, 'report report report')
ON DUPLICATE KEY UPDATE report='report report report';

You can rewrite it to avoid using the blob value in the UPDATE clause:

INSERT INTO t (id, report) VALUES (1234, 'report report report')
ON DUPLICATE KEY UPDATE report=VALUES(report);

This syntax means to set the report column to the same value that you had tried to insert in the first line. It's just shorthand, so you don't have to repeat the literal values twice for each column.

The bug description seems to say that doing this will cause a crash if you do this in a stored procedure or a prepared statement. I like to use the VALUES() trick because it's handy, but I can't say that I've tried it with a blob in a stored procedure in 5.7.19.

Given the wording of the bug "if the VALUES part of an ON DUPLICATE KEY UPDATE clause referred to a BLOB value...", I'm thinking it might work around the problem if you repeat the whole blob value explicitly instead of using the VALUES() shorthand.

Or else don't do this in a stored procedure, if that's the problem.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
0

threads_connected ~2500+ indicates there is no logout/logoff disconnect being processed when a client has finished using the system.

To avoid thread creation/termination churn, I would change in my.ini/cnf thread_cache_size = 100 # from 8 for the CAP suggested in 8.0 to support the 5,000+ threads created in this instance.

For more detailed analysis, please post

RAM   on your server
SHOW GLOBAL STATUS;
SHOW GLOBAL VARIABLES;
SHOW ENGINE INNODB STATUS;

and one minute of your general log.

Wilson Hauck
  • 2,094
  • 1
  • 11
  • 19