Migration 5 to 6 Lost connection to MySQL

[zomane0]

Hello, I'm trying to migrate from 5.0.10(installed from source) to 6.0.12-1~bpo9+1(from debian packet).
Both are using mysql
the old one is
| innodb_version | 5.6.36-82.1 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 10.0.32-MariaDB-0+deb8u1

New one is
| innodb_version | 5.6.41-84.1 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 10.1.37-MariaDB-0+deb9u1 |

So I installed a new server with 6.0.12 and mariadb/mysql from the package management the distro is Debian Stretch.
DB dump from the old one and the import on the new one were fine but when I try to run the migration it always fails here

[Mon Dec 17 14:56:35 2018] DBUpdate-to-6.pl: DBD::mysql::db do failed: Lost connection to MySQL server during query at /usr/share/otrs/Kernel/System/DB.pm line 474.
ERROR: OTRS-otrs.Console.pl-Maint::Database::Check-10 Perl: 5.24.1 OS: linux Time: Mon Dec 17 14:56:35 2018

Message: Lost connection to MySQL server during query, SQL: 'EXECUTE FKStatement'

Traceback (10659):
Module: scripts::DBUpdateTo6::Base::ExecuteXMLDBString Line: 394
Module: scripts::DBUpdateTo6::Base::ExecuteXMLDBArray Line: 342
Module: scripts::DBUpdateTo6::PostArticleTableStructureChanges::_UpdateArticleDataMimeAttachmentTable Line: 378
Module: scripts::DBUpdateTo6::PostArticleTableStructureChanges::Run Line: 62

That are the DB settings
MariaDB [mysql]> show variables like '%timeout';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 31536000 |
| delayed_insert_timeout | 300 |
| innodb_flush_log_at_timeout | 10 |
| innodb_lock_wait_timeout | 200 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 31536000 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 31536000 |
| net_write_timeout | 30000 |
| slave_net_timeout | 3600 |
| thread_pool_idle_timeout | 60 |
| wait_timeout | 31536000 |
+-----------------------------+----------+
MariaDB [mysql]> show variables like '%max_all%';
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| max_allowed_packet | 1073741824 |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+

I even edited the /usr/share/otrs/Kernel/System/DB.pm so the DB connect is now

$Self->{dbh} = DBI->connect(
$Self->{DSN},
$Self->{USER},
$Self->{PW},
$Self->{Backend}->{'DB::Attribute'},
$Self->{mysql_auto_reconnect}->{1},
$Self->{mysql_read_timeout}->{100000000},
$Self->{mysql_write_timeout}->{100000000},
$Self->{mysql_connect_timeout}->{100000000},
);

Instead of the default
$Self->{dbh} = DBI->connect(
$Self->{DSN},
$Self->{USER},
$Self->{PW},
$Self->{Backend}->{'DB::Attribute'},

But it is still dying with
[Mon Dec 17 14:56:35 2018] DBUpdate-to-6.pl: DBD::mysql::db do failed: Lost connection to MySQL server during query at /usr/share/otrs/Kernel/System/DB.pm line 474.

The DB is not big from my point of view
+--------------------+---------------+
| DB Name | DB Size in MB |
+--------------------+---------------+
| information_schema | 0.2 |
| mysql | 1.0 |
| otrs | 19314.9 |

All suggestions I have found online were to increase the timeout's and max_allowed which I did but no success as you can see.
Thanks in advance for any help

[zomane0]

Today I found that I hit that mysql bug https://jira.mariadb.org/browse/MDEV-17130 and due to that the migration fails

Code: Select all

2018-12-18 09:39:35 7f6c653c1700  InnoDB: Assertion failure in thread 140103531632384 in file ha_innodb.cc line 6092
InnoDB: Failing assertion: share->idx_trans_tbl.index_count == mysql_num_index
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: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
181218  9:39: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.1.37-MariaDB-0+deb9u1
key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=17
max_threads=153
thread_count=13
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 352467 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f6b2273c008
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 = 0x7f6c653c0cb8 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x55a3c08c739e]
/usr/sbin/mysqld(handle_fatal_signal+0x3bd)[0x55a3c040897d]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x110c0)[0x7f6c68bfb0c0]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0xcf)[0x7f6c67768fff]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x16a)[0x7f6c6776a42a]
/usr/sbin/mysqld(+0x828005)[0x55a3c068c005]
/usr/sbin/mysqld(+0x835559)[0x55a3c0699559]
/usr/sbin/mysqld(_ZN7handler7ha_openEP5TABLEPKcij+0x33)[0x55a3c040ccd3]
/usr/sbin/mysqld(_Z21open_table_from_shareP3THDP11TABLE_SHAREPKcjjjP5TABLEb+0xb63)[0x55a3c0321dc3]
/usr/sbin/mysqld(_Z19open_table_uncachedP3THDP10handlertonP34st_mysql_const_unsigned_lex_stringPKcS6_S6_bb+0x1c6)[0x55a3c022c126]
/usr/sbin/mysqld(_Z17mysql_alter_tableP3THDPcS1_P14HA_CREATE_INFOP10TABLE_LISTP10Alter_infojP8st_orderb+0x3d5e)[0x55a3c030743e]
/usr/sbin/mysqld(_ZN19Sql_cmd_alter_table7executeEP3THD+0x5c9)[0x55a3c034e499]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x152c)[0x55a3c0270a7c]
/usr/sbin/mysqld(_ZN18Prepared_statement7executeEP6Stringb+0x4de)[0x55a3c028d59e]
/usr/sbin/mysqld(+0x42971f)[0x55a3c028d71f]
/usr/sbin/mysqld(_Z22mysql_sql_stmt_executeP3THD+0x12b)[0x55a3c028de5b]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x243f)[0x55a3c027198f]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x311)[0x55a3c0278801]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x2615)[0x55a3c027c165]
/usr/sbin/mysqld(_Z10do_commandP3THD+0x179)[0x55a3c027c9a9]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x1d2)[0x55a3c034b842]
/usr/sbin/mysqld(handle_one_connection+0x40)[0x55a3c034b9a0]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x7494)[0x7f6c68bf1494]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f6c6781eacf]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x7f6b21344520): ALTER TABLE article_flag ADD CONSTRAINT FK_article_flag_article_id_id FOREIGN KEY (article_id) REFERENCES article (id)
Connection ID (thread ID): 1959
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=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=off

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.
2018-12-18  9:39:43 140064358576576 [Note] Using unique option prefix 'innodb_open_file' is error-prone and can break in the future. Please use the full name 'innodb-open-files' instead.

Any ideas for a workaround will be appreciated because for the moment I dont see any fix coming from mysql/mariadb

[root]

Hi,

Looks like you should check your database configuration.

- Roy

[zomane0]

Thanks for the suggestion Roy but sadly nothing to do with the DB config. I played enough with it, literally wasted days there.
As I wrote it is a bug.
In order get around I installed one Debian sid which comes with MySQL 5.7 and the migration finished successfully.

[root]

Thanks for the suggestion Roy but sadly nothing to do with the DB config. I played enough with it, literally wasted days there.
As I wrote it is a bug.
In order get around I installed one Debian sid which comes with MySQL 5.7 and the migration finished successfully.

Thanks for letting us know.

- Roy