Running db update for UTF8mb4 tables after `drush si` destroys the db.

[tabroughton]

Description of the bug After using dush si i get a warning about UTF8mb4 being available but not all tables updated, running the Database 4 byte UTF-8 upgrade destroys the database.

Steps To Reproduce To reproduce the behavior:

1. install backdrop using drush si
2. log into site as admin (click a red message "1" in banner)
3. read message about UTF8mb4 and click fix
4. run fix - site will error after half way of progress bar

Actual behavior

An AJAX HTTP error occurred. HTTP Result Code: 500 Debugging information follows. Path: /batch?id=2&op=do_nojs&op=do StatusText: Service unavailable (with message) ResponseText: 

(no repsonse text)

refresh page

Backdrop
Error
The website encountered an unexpected error. Please try again later.

Expected behavior tables update and warning is removed

Additional information running in docker containers.

from drush status:

$ docker exec -it backdrop ./vendor/bin/drush st --root=build
 Backdrop version         :  1.14.x-dev                            
 Site URI                 :  http://default                        
 Database driver          :  mysql                                 
 Database hostname        :  backdrop_db_server                    
 Database port            :  3306                                  
 Database username        :  backdropuser                          
 Database name            :  backdrop_db                           
 Backdrop bootstrap       :  Successful                            
 Backdrop user            :                                        
 PHP executable           :  /usr/local/bin/php                    
 PHP configuration        :                                        
 PHP OS                   :  Linux                                 
 Drush script             :  /var/www/vendor/drush/drush/drush.php 
 Drush version            :  8.3.0                                 
 Backdrop Drush           :  1.0.0                                 
 Drush temp directory     :  /tmp                                  
 Drush configuration      :                                        
 Drush alias files        :                                        
 Install profile          :  standard                              
 Backdrop Settings File   :  ./settings.php    

from MariaDB logs:

Version: '10.4.6-MariaDB-1:10.4.6+maria~bionic'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution
2019-07-20 00:54:35 0x7f0274433700  InnoDB: Assertion failure in file /home/buildbot/buildbot/build/mariadb-10.4.6/storage/innobase/data/data0type.cc line 67
InnoDB: Failing assertion: !(prefix_len % mbmaxlen)
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to https://jira.mariadb.org/
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: https://mariadb.com/kb/en/library/innodb-recovery-modes/
InnoDB: about forcing recovery.
190720  0:54:35 [ERROR] 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.

To report this bug, see https://mariadb.com/kb/en/reporting-bugs

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.

Server version: 10.4.6-MariaDB-1:10.4.6+maria~bionic
key_buffer_size=134217728
read_buffer_size=2097152
max_used_connections=1
max_threads=102
thread_count=7
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 760240 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f020c000c08
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 = 0x7f0274432dd8 thread_stack 0x49000
mysqld(my_print_stacktrace+0x2e)[0x5588e017ae0e]
mysqld(handle_fatal_signal+0x515)[0x5588dfbf3f95]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x12890)[0x7f027dec1890]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0xc7)[0x7f027d1d1e97]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x141)[0x7f027d1d3801]
mysqld(+0x561336)[0x5588df900336]
mysqld(+0xbacb38)[0x5588dff4bb38]
mysqld(+0xab5dde)[0x5588dfe54dde]
mysqld(+0xabde3a)[0x5588dfe5ce3a]
mysqld(+0xabff20)[0x5588dfe5ef20]
mysqld(+0xa11e6a)[0x5588dfdb0e6a]
mysqld(_Z17mysql_alter_tableP3THDPK25st_mysql_const_lex_stringS3_P14HA_CREATE_INFOP10TABLE_LISTP10Alter_infojP8st_orderb+0x2e08)[0x5588dfa7ab48]
mysqld(_ZN19Sql_cmd_alter_table7executeEP3THD+0x3fe)[0x5588dfacfc1e]
mysqld(_Z21mysql_execute_commandP3THD+0x1f0b)[0x5588df9e5b9b]
mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x22a)[0x5588df9ec71a]
mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x166d)[0x5588df9eeedd]
mysqld(_Z10do_commandP3THD+0x148)[0x5588df9f0348]
mysqld(_Z24do_handle_one_connectionP7CONNECT+0x2c2)[0x5588dfacb542]
mysqld(handle_one_connection+0x3d)[0x5588dfacb60d]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76db)[0x7f027deb66db]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f027d2b488f]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x7f020c00fee0): ALTER TABLE `menu_links`               MODIFY `link_path` varchar(255) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci' NOT NULL DEFAULT ''  COMMENT 'The Backdrop path or external path this link points to.'
Connection ID (thread ID): 31
Status: NOT_KILLED

Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on

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.
Writing a core file...
Working directory at /var/lib/mysql
Resource Limits:
Limit                     Soft Limit           Hard Limit           Units     
Max cpu time              unlimited            unlimited            seconds   
Max file size             unlimited            unlimited            bytes     
Max data size             unlimited            unlimited            bytes     
Max stack size            8388608              unlimited            bytes     
Max core file size        unlimited            unlimited            bytes     
Max resident set          unlimited            unlimited            bytes     
Max processes             unlimited            unlimited            processes 
Max open files            1048576              1048576              files     
Max locked memory         67108864             67108864             bytes     
Max address space         unlimited            unlimited            bytes     
Max file locks            unlimited            unlimited            locks     
Max pending signals       62680                62680                signals   
Max msgqueue size         819200               819200               bytes     
Max nice priority         0                    0                    
Max realtime priority     0                    0                    
Max realtime timeout      unlimited            unlimited            us        
Core pattern: |/usr/share/apport/apport %p %s %c %d %P

Fatal signal 11 while backtracing

just adding a bit of extra info:

mysql conf:

[client]                                                                                                                                   
default-character-set = utf8mb4                                                                                                            

[mysql]                                                                                                                                    
default-character-set = utf8mb4                                                                                                            

[mysqld]                                                                                                                                   
character-set-client-handshake = FALSE                                                                                                     
character-set-server = utf8mb4                                                                                                             
collation-server = utf8mb4_unicode_ci                                                                                                      
innodb_large_prefix=true                                                                                                                   
innodb_file_format=barracuda                                                                                                               
innodb_file_per_table=true                                                                                                                 

and settings.php has the line at the bottom as expected

...
$database_charset = 'utf8mb4'; 

[tabroughton]

could it be related to this #3858? which was recently merged/closed

[tabroughton]

I have tested on 1.13.2 which was released before the #3858 issue was merged so it isn't this.

This is probably an issue with drush so I will close and report there.

[tabroughton]

I've found the issue in backdrop core not in drush.

There is no way to set the charset of the database when installing and not in interactive mode.

[oadaeh]

I tested this on my server, and it fixes a problem I've had for a long time with my install script where new installations would have the red dot saying the database needed to be converted, even though it was already UTF8MB4.

The code changes are minimal and look fine to me.

[klonos]

Yup, code looks good, and based on @oadaeh's comment above, also tagging as WFM.

[quicksketch]

Huh, weird. Yeah it looks like the method to check UTF-8 MB4 was added but never used. Very strange. With both @oadaeh and @klonos confirming the fix I think this is good to go.

[quicksketch]

Merged https://github.com/backdrop/backdrop/pull/2794 into 1.x and 1.13.x. Thanks @tabroughton, @oadaeh, and @klonos!