MySQL Bugs: #95831: ALTER TABLE: ERROR 2013 (HY000): Lost connection to MySQL server during query

Bug #95831	ALTER TABLE: ERROR 2013 (HY000): Lost connection to MySQL server during query
Submitted:	17 Jun 2019 7:32	Modified:	20 Jun 2019 13:18
Reporter:	Nadeem Mehraj	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.16	OS:	Ubuntu (16.04 LLTS)
Assigned to:	MySQL Verification Team	CPU Architecture:	x86

Description:
I am trying to add an auto increment column to a mysql table as:

mysql> ALTER TABLE scraped_comments ADD row_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

ERROR 2013 (HY000): Lost connection to MySQL server during query 

 I thought the issue may be caused by less memory so I had htop open in another terminal but less than half of memory was being used. 

I checked /var/log/mysql/error.log file and found following in that file:

    ...  
InnoDB: ###### Diagnostic info printed to the standard error stream
2019-06-14T06:21:49.965496Z 0 [ERROR] [MY-012872] [InnoDB] Semaphore wait has lasted > 38632704 seconds. We intentionally crash the server because it appears to be hung.[FATAL] Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung.  
2019-06-14T06:21:49.965563Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: ut0ut.cc:629 thread 140385843095296  
InnoDB: We intentionally generate a memory trap.  
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.  
InnoDB: If you get repeated assertion failures or crashes, even  
InnoDB: immediately after the mysqld startup, there may be  
InnoDB: corruption in the InnoDB tablespace. Please refer to  
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html  
InnoDB: about forcing recovery.  
06:21:49 UTC - mysqld got signal 6 ;  
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.  
Thread pointer: 0x0  
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 = 0 thread_stack 0x46000  
/usr/sbin/mysqld(my_print_stacktrace(unsigned char*, unsigned long)+0x3d) [0x1dc2f5d]  
/usr/sbin/mysqld(handle_fatal_signal+0x351) [0xeee441]  
/lib/x86_64-linux-gnu/libpthread.so.0(+0x11390) [0x7fae50d4f390]  
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x38) [0x7fae4f241428]  
/lib/x86_64-linux-gnu/libc.so.6(abort+0x16a) [0x7fae4f24302a]  
/usr/sbin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x2db) [0x20736cb]  
/usr/sbin/mysqld(ib::fatal::~fatal()+0x18e) [0x2075afe]  
/usr/sbin/mysqld(srv_error_monitor_thread()+0xc07) [0x200ccf7]  
/usr/sbin/mysqld(std::thread::_Impl<std::_Bind_simple<Runnable (void (*)())> >::_M_run()+0x63) [0x1e6e543]  
/usr/lib/x86_64-linux-gnu/libstdc++.so.6(+0xb8c80) [0x7fae4fbadc80]  
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba) [0x7fae50d456ba]  
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d) [0x7fae4f31341d]  
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.  
2019-06-14T06:21:53.867586Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.16) starting as process 6199  
2019-06-14T06:21:58.097227Z 0 [System] [MY-010229] [Server] Starting crash recovery...  
2019-06-14T06:21:58.114311Z 0 [System] [MY-010232] [Server] Crash recovery finished.  
2019-06-14T06:21:59.096291Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.  
2019-06-14T06:21:59.225066Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.16'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.  
2019-06-14T06:21:59.436191Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060  
...

Output of SHOW CREATE TABLE:

mysql> SHOW CREATE TABLE scraped_comments;
...
 CREATE TABLE `scraped_comments` (
  `crawl_site` varchar(50) DEFAULT NULL,
  `comment` text,
  `user_rating` float DEFAULT NULL,
  `review_date` date DEFAULT NULL,
  `jta_id` varchar(50) DEFAULT NULL,
  `url` varchar(500) DEFAULT NULL,
  `username` varchar(50) DEFAULT NULL,
  `opinions` float DEFAULT NULL,
  `recommended_by` float DEFAULT NULL,
  `overallrating` float DEFAULT NULL,
  `city_avg` float DEFAULT NULL,
  `maintenence` float DEFAULT NULL,
  `neighborhood` float DEFAULT NULL,
  `noise` float DEFAULT NULL,
  `grounds` float DEFAULT NULL,
  `safety` float DEFAULT NULL,
  `office_Staff` float DEFAULT NULL,
  `avgratinglist` varchar(25) DEFAULT NULL,
  `construction` varchar(25) DEFAULT NULL,
  `parking` varchar(25) DEFAULT NULL,
  `is_monthly` tinyint(4) DEFAULT NULL,
  `id` int(11) NOT NULL,
  `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `insert_date` date DEFAULT NULL,
  KEY `fk_id_scraped_comments_idx` (`id`),
  KEY `userr_index` (`user_rating`),
  KEY `timestamp_index_sc` (`timestamp`),
  KEY `in_date_index` (`insert_date`),
  KEY `crawl_siteindex` (`crawl_site`),
  CONSTRAINT `fk_id_scraped_comments` FOREIGN KEY (`id`) REFERENCES `scraped_metadata` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
...

How to repeat:
Included as much info as possible

The error got resolved. Kindly check

https://stackoverflow.com/questions/56594157/alter-table-error-2013-hy000-lost-connection-...

Hi,

This is due to improper configuration/sizing of the server, not a bug.