[MDEV-12936] upgrade to 10.2.6 failed upon tables with virtual columns Created: 2017-05-27  Updated: 2018-12-20  Resolved: 2017-07-05

Status: Closed
Project: MariaDB Server
Component/s: Server, Virtual Columns
Affects Version/s: 10.2.6, 10.2
Fix Version/s: 10.2.7

Type: Bug Priority: Blocker
Reporter: Pascal HOARAU Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None
Environment:

centos 7.3

Issue Links:
Relates
relates to MDEV-13320 virtual columns fail when expression ... Closed
relates to MDEV-17199 Assertion `pos < table->n_v_def' fail... Closed

 Description   

2 types of errors on mysql_upgrade from 10.1.23 to 10.2.6

1/
jbm.conf_client_settings
Error : Function or expression 'AUTO_INCREMENT' cannot be used in the UNTYPED clause of `pk`
error : Corrupt

2/
jbm_adobe_campaign.bl_kpi_demo
Error : Table 'jbm_adobe_campaign.bl_kpi_demo' doesn't exist in engine
status : Operation failed

tail -100 /var/log/mysql_errors.log 2017-05-27 20:51:04 139656884177152 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2017-05-27 20:51:04 139656884177152 [Note] Plugin 'FEEDBACK' is disabled.
2017-05-27 20:51:04 139656884177152 [ERROR] Unknown/unsupported storage engine: InnoDB
2017-05-27 20:51:04 139656884177152 [ERROR] Aborting

 Comments   
Comment by Elena Stepanova [ 2017-05-27 ]

Could you please check other logs, syslog, messages or journal for more errors? Somewhere there must be another error before InnoDB refuses to start. It's also unclear how mysql_upgrade could run at all if the server aborted on startup.

Comment by Pascal HOARAU [ 2017-05-27 ]

well from begining:

"yum update"
Sorry it is in french;

yum update
 
Modules complémentaires chargés : priorities
 
mariadb                                                                                                                                     | 2.9 kB  00:00:00     
mariadb/primary_db                                                                                                                          |  21 kB  00:00:00     
1 packages excluded due to repository priority protections
 
Résolution des dépendances
 
--> Lancement de la transaction de test
 
---> Le paquet MariaDB-client.x86_64 0:10.1.23-1.el7.centos sera mis à jour
 
---> Le paquet MariaDB-client.x86_64 0:10.2.6-1.el7.centos sera utilisé
 
---> Le paquet MariaDB-common.x86_64 0:10.1.23-1.el7.centos sera mis à jour
 
---> Le paquet MariaDB-common.x86_64 0:10.2.6-1.el7.centos sera utilisé
 
--> Traitement de la dépendance : MariaDB-compat pour le paquet : MariaDB-common-10.2.6-1.el7.centos.x86_64
 
---> Le paquet MariaDB-server.x86_64 0:10.1.23-1.el7.centos sera mis à jour
 
---> Le paquet MariaDB-server.x86_64 0:10.2.6-1.el7.centos sera utilisé
 
---> Le paquet MariaDB-shared.x86_64 0:10.1.23-1.el7.centos sera mis à jour
 
---> Le paquet MariaDB-shared.x86_64 0:10.2.6-1.el7.centos sera utilisé
 
--> Lancement de la transaction de test
 
---> Le paquet MariaDB-compat.x86_64 0:10.2.6-1.el7.centos sera installé
 
--> Résolution des dépendances terminée
 
 
 
Dépendances résolues
 
 
 
===================================================================================================================================================================
 
 Package                                  Architecture                     Version                                         Dépôt                             Taille
 
===================================================================================================================================================================
 
Mise à jour :
 
 MariaDB-client                           x86_64                           10.2.6-1.el7.centos                             mariadb                            48 M
 
 MariaDB-common                           x86_64                           10.2.6-1.el7.centos                             mariadb                           197 k
 
 MariaDB-server                           x86_64                           10.2.6-1.el7.centos                             mariadb                           108 M
 
 MariaDB-shared                           x86_64                           10.2.6-1.el7.centos                             mariadb                           387 k
 
Installation pour dépendances :
 
 MariaDB-compat                           x86_64                           10.2.6-1.el7.centos                             mariadb                           2.8 M
 
 
 
Résumé de la transaction
 
===================================================================================================================================================================
 
Installation             ( 1 Paquet en dépendance)
 
Mettre à jour  4 Paquets
 
 
 
Taille totale des téléchargements : 159 M
 
Is this ok [y/d/N]: y
 
Downloading packages:
 
Delta RPMs disabled because /usr/bin/applydeltarpm not installed.
 
(1/5): MariaDB-10.2.6-centos73-x86_64-common.rpm                                                                                            | 197 kB  00:00:00     
(2/5): MariaDB-10.2.6-centos73-x86_64-compat.rpm                                                                                            | 2.8 MB  00:00:01     
(3/5): MariaDB-10.2.6-centos73-x86_64-client.rpm                                                                                            |  48 MB  00:00:05     
(4/5): MariaDB-10.2.6-centos73-x86_64-shared.rpm                                                                                            | 387 kB  00:00:00     
(5/5): MariaDB-10.2.6-centos73-x86_64-server.rpm                                                                                            | 108 MB  00:00:05     
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Total                                                                                                                               21 MB/s | 159 MB  00:00:07     
Running transaction check
 
Running transaction test
 
Transaction test succeeded
 
Running transaction
 
  Mise à jour  : MariaDB-common-10.2.6-1.el7.centos.x86_64                                                                                                     1/9 
  Installation : MariaDB-compat-10.2.6-1.el7.centos.x86_64                                                                                                     2/9 
  Mise à jour  : MariaDB-client-10.2.6-1.el7.centos.x86_64                                                                                                     3/9 
 
 
******************************************************************
 
A MySQL or MariaDB server package (MariaDB-server-10.1.23-1.el7.centos.x86_64) is installed.
 
 
 
Upgrading directly from MySQL 10.1 to MariaDB 10.2 may not
 
be safe in all cases.  A manual dump and restore using mysqldump is
 
recommended.  It is important to review the MariaDB manual's Upgrading
 
section for version-specific incompatibilities.
 
 
 
A manual upgrade is required.
 
 
 
- Ensure that you have a complete, working backup of your data and my.cnf
 
  files
 
- Shut down the MySQL server cleanly
 
- Remove the existing MySQL packages.  Usually this command will
 
  list the packages you should remove:
 
  rpm -qa | grep -i '^mysql-'
 
 
 
  You may choose to use 'rpm --nodeps -ev <package-name>' to remove
 
  the package which contains the mysqlclient shared library.  The
 
  library will be reinstalled by the MariaDB-shared package.
 
- Install the new MariaDB packages supplied by MariaDB Foundation
 
- Ensure that the MariaDB server is started
 
- Run the 'mysql_upgrade' program
 
 
 
This is a brief description of the upgrade process.  Important details
 
can be found in the MariaDB manual, in the Upgrading section.
 
******************************************************************
 
error: %pre(MariaDB-server-10.2.6-1.el7.centos.x86_64) scriptlet failed, exit status 1
 
Error in PREIN scriptlet in rpm package MariaDB-server-10.2.6-1.el7.centos.x86_64
 
error: MariaDB-server-10.2.6-1.el7.centos.x86_64: install failed
 
  Mise à jour  : MariaDB-shared-10.2.6-1.el7.centos.x86_64                                                                                                     5/9 
error: MariaDB-server-10.1.23-1.el7.centos.x86_64: erase skipped
 
  Nettoyage    : MariaDB-client-10.1.23-1.el7.centos.x86_64                                                                                                    6/9 
  Nettoyage    : MariaDB-shared-10.1.23-1.el7.centos.x86_64                                                                                                    7/9 
  Nettoyage    : MariaDB-common-10.1.23-1.el7.centos.x86_64                                                                                                    8/9 
  Vérification : MariaDB-shared-10.2.6-1.el7.centos.x86_64                                                                                                     1/9 
  Vérification : MariaDB-compat-10.2.6-1.el7.centos.x86_64                                                                                                     2/9 
  Vérification : MariaDB-client-10.2.6-1.el7.centos.x86_64                                                                                                     3/9 
  Vérification : MariaDB-common-10.2.6-1.el7.centos.x86_64                                                                                                     4/9 
  Vérification : MariaDB-shared-10.1.23-1.el7.centos.x86_64                                                                                                    5/9 
  Vérification : MariaDB-common-10.1.23-1.el7.centos.x86_64                                                                                                    6/9 
  Vérification : MariaDB-client-10.1.23-1.el7.centos.x86_64                                                                                                    7/9 
  Vérification : MariaDB-server-10.2.6-1.el7.centos.x86_64                                                                                                     8/9 
MariaDB-server-10.1.23-1.el7.centos.x86_64 was supposed to be removed but is not!
 
  Vérification : MariaDB-server-10.1.23-1.el7.centos.x86_64                                                                                                    9/9 
 
 
Dépendances installées :
 
  MariaDB-compat.x86_64 0:10.2.6-1.el7.centos                                                                                                                      
 
 
Mis à jour :
 
  MariaDB-client.x86_64 0:10.2.6-1.el7.centos           MariaDB-common.x86_64 0:10.2.6-1.el7.centos           MariaDB-shared.x86_64 0:10.2.6-1.el7.centos          
 
 
Échec:
 
  MariaDB-server.x86_64 0:10.1.23-1.el7.centos                                     MariaDB-server.x86_64 0:10.2.6-1.el7.centos                                    
 
 
Terminé !

I am not sure update order is correct.
So tried again:
rpm -e --nodeps MariaDB-shared MariaDB-server MariaDB-compat MariaDB-common MariaDB-client

Then "systemctl mysql start" was OK but when I did mysql_upgrade I got errors I gave to you

jbm.conf_client_settings
Error : Function or expression 'AUTO_INCREMENT' cannot be used in the UNTYPED clause of `pk`
error : Corrupt

jbm_adobe_campaign.bl_kpi_demo
Error : Table 'jbm_adobe_campaign.bl_kpi_demo' doesn't exist in engine
status : Operation failed

2017-05-28  1:16:26 139889625270400 [Warning] InnoDB: Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html
 
2017-05-28  1:16:26 139889625270400 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
 
2017-05-28  1:16:26 139889625270400 [Note] InnoDB: Uses event mutexes
 
2017-05-28  1:16:26 139889625270400 [Note] InnoDB: Compressed tables use zlib 1.2.7
 
2017-05-28  1:16:26 139889625270400 [Note] InnoDB: Using Linux native AIO
 
2017-05-28  1:16:26 139889625270400 [Note] InnoDB: Number of pools: 1
 
2017-05-28  1:16:26 139889625270400 [Note] InnoDB: Using SSE2 crc32 instructions
 
2017-05-28  1:16:26 139889625270400 [Note] InnoDB: Initializing buffer pool, total size = 47G, instances = 8, chunk size = 128M
 
2017-05-28  1:16:27 139889625270400 [Note] InnoDB: Completed initialization of buffer pool
 
2017-05-28  1:16:28 139835265206016 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
 
2017-05-28  1:16:28 139889625270400 [Note] InnoDB: Highest supported file format is Barracuda.
 
2017-05-28  1:16:28 139889625270400 [Note] InnoDB: Upgrading redo log: 2*3072 pages; LSN=2753074062750
 
2017-05-28  1:16:28 139889625270400 [Note] InnoDB: Starting to delete and rewrite log files.
 
2017-05-28  1:16:28 139889625270400 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
 
2017-05-28  1:16:28 139889625270400 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
 
2017-05-28  1:16:28 139889625270400 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
 
2017-05-28  1:16:28 139889625270400 [Note] InnoDB: New log files created, LSN=2753074062750
 
2017-05-28  1:16:29 139889625270400 [Note] InnoDB: 128 out of 128 rollback segments are active.
 
2017-05-28  1:16:29 139889625270400 [Note] InnoDB: Creating shared tablespace for temporary tables
 
2017-05-28  1:16:29 139889625270400 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
 
2017-05-28  1:16:29 139889625270400 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
 
2017-05-28  1:16:29 139889625270400 [Note] InnoDB: Waiting for purge to start
 
2017-05-28  1:16:29 139889625270400 [Note] InnoDB: 5.7.14 started; log sequence number 0
 
2017-05-28  1:16:29 139836455487232 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
 
2017-05-28  1:16:29 139889625270400 [Note] Plugin 'FEEDBACK' is disabled.
 
2017-05-28  1:16:29 139889625270400 [Note] Server socket created on IP: '::'.
 
2017-05-28  1:16:29 139889625270400 [Warning] 'user' entry 'root@jbm' ignored in --skip-name-resolve mode.
 
2017-05-28  1:16:29 139889625270400 [Warning] 'proxies_priv' entry '@% root@www2.junkboxmonitor.com' ignored in --skip-name-resolve mode.
 
2017-05-28  1:16:29 139889625270400 [Note] /usr/sbin/mysqld: ready for connections.
 
Version: '10.2.6-MariaDB-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
 
2017-05-28  1:16:29 139836455487232 [Note] InnoDB: Buffer pool(s) load completed at 170528  1:16:29
 
2017-05-28  1:16:33 139889501923072 [Warning] InnoDB: Table jbm_adobe_campaign/bl_kpi_demo contains 11 user defined columns in InnoDB, but 16 columns in MariaDB. Please check INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
 
2017-05-28  1:16:33 139889501923072 [Warning] InnoDB: Cannot open table jbm_adobe_campaign/bl_kpi_demo from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.

rm /var/lib/mysql/ib_logfile* did not solve

Comment by Elena Stepanova [ 2017-06-25 ]

Thanks for the provided data, I am able to reproduce the problem.

The error happens upon upgrade of tables containing virtual columns.

To reproduce:

Run on an older server, e.g. 10.1

 
CREATE TABLE t1 (i INT, v1 INT AS (i)) ENGINE=InnoDB;
 
CREATE TABLE t2 (i INT, v2 INT AS (i) VIRTUAL) ENGINE=InnoDB;
 
CREATE TABLE t3 (pk INT AUTO_INCREMENT PRIMARY KEY, v3 INT AS (pk)) ENGINE=InnoDB;
 
shutdown;

Start 10.2 server on the same datadir and run mysql_upgrade.

Results with a release server:

Processing databases
 
information_schema
 
performance_schema
 
test
 
test.t1
 
Error    : Table 'test.t1' doesn't exist in engine
 
status   : Operation failed
 
test.t2
 
Error    : Table 'test.t2' doesn't exist in engine
 
status   : Operation failed
 
test.t3
 
Error    : Function or expression 'AUTO_INCREMENT' cannot be used in the UNTYPED clause of `pk`
 
error    : Corrupt
 
 
 
Repairing tables
 
test.t1
 
Error    : Table 'test.t1' doesn't exist in engine
 
status   : Operation failed
 
test.t2
 
Error    : Table 'test.t2' doesn't exist in engine
 
status   : Operation failed
 
test.t3
 
Error    : Function or expression 'AUTO_INCREMENT' cannot be used in the UNTYPED clause of `pk`
 
error    : Corrupt
 
Phase 7/7: Running 'FLUSH PRIVILEGES'
 
OK

Results with a debug server:

Processing databases
 
information_schema
 
performance_schema
 
test
 
test.t1
 
Error    : Table 'test.t1' doesn't exist in engine
 
status   : Operation failed
 
test.t2
 
Error    : Table 'test.t2' doesn't exist in engine
 
status   : Operation failed
 
bin/mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'CHECK TABLE ...  FOR UPGRADE'
 
FATAL ERROR: Upgrade failed

10.2 0d69d313a11fb03f31d71282f622b25d0a4764b8

 
mysqld: /data/src/10.2/sql/field.h:655: const char* Virtual_column_info::get_vcol_type_name() const: Assertion `vcol_type != VCOL_TYPE_NONE' failed.
 
170625 18:55:18 [ERROR] mysqld got signal 6 ;
 
 
 
#4  0x000055e6769ee3a2 in Virtual_column_info::get_vcol_type_name (this=0x7fdb8834edd0) at /data/src/10.2/sql/field.h:655
 
#5  0x000055e6769e00c9 in fix_and_check_vcol_expr (thd=0x7fdb88000b00, table=0x7fdb883d5030, vcol=0x7fdb8834edd0) at /data/src/10.2/sql/table.cc:2866
 
#6  0x000055e6769e0395 in unpack_vcol_info_from_frm (thd=0x7fdb88000b00, mem_root=0x7fdb883d5ae8, table=0x7fdb883d5030, expr_str=0x7fdbf414c290, vcol_ptr=0x7fdb8834ec40, error_reported=0x7fdbf414c619) at /data/src/10.2/sql/table.cc:2941
 
#7  0x000055e6769d9c54 in parse_vcol_defs (thd=0x7fdb88000b00, mem_root=0x7fdb883d5ae8, table=0x7fdb883d5030, error_reported=0x7fdbf414c619) at /data/src/10.2/sql/table.cc:1069
 
#8  0x000055e6769e1043 in open_table_from_share (thd=0x7fdb88000b00, share=0x7fdb883b3548, alias=0x7fdb88011018 "t3", db_stat=33, prgflag=8, ha_open_flags=48, outparam=0x7fdb883d5030, is_create_table=false) at /data/src/10.2/sql/table.cc:3172
 
#9  0x000055e67686f1ab in open_table (thd=0x7fdb88000b00, table_list=0x7fdb88011020, ot_ctx=0x7fdbf414cba0) at /data/src/10.2/sql/sql_base.cc:1877
 
#10 0x000055e676871c22 in open_and_process_table (thd=0x7fdb88000b00, lex=0x7fdb88004518, tables=0x7fdb88011020, counter=0x7fdbf414cc34, flags=0, prelocking_strategy=0x7fdbf414ccb0, has_prelocking_list=false, ot_ctx=0x7fdbf414cba0) at /data/src/10.2/sql/sql_base.cc:3409
 
#11 0x000055e676872d5d in open_tables (thd=0x7fdb88000b00, options=..., start=0x7fdbf414cc18, counter=0x7fdbf414cc34, flags=0, prelocking_strategy=0x7fdbf414ccb0) at /data/src/10.2/sql/sql_base.cc:3928
 
#12 0x000055e676874552 in open_and_lock_tables (thd=0x7fdb88000b00, options=..., tables=0x7fdb88011020, derived=true, flags=0, prelocking_strategy=0x7fdbf414ccb0) at /data/src/10.2/sql/sql_base.cc:4682
 
#13 0x000055e676867a59 in open_and_lock_tables (thd=0x7fdb88000b00, tables=0x7fdb88011020, derived=true, flags=0) at /data/src/10.2/sql/sql_base.h:493
 
#14 0x000055e676a37228 in open_only_one_table (thd=0x7fdb88000b00, table=0x7fdb88011020, repair_table_use_frm=false, is_view_operator_func=true) at /data/src/10.2/sql/sql_admin.cc:393
 
#15 0x000055e676a37807 in mysql_admin_table(THD *, TABLE_LIST *, HA_CHECK_OPT *, const char *, thr_lock_type, bool, bool, uint, int (*)(THD *, TABLE_LIST *, HA_CHECK_OPT *), struct {...}, int (*)(THD *, TABLE_LIST *, HA_CHECK_OPT *)) (thd=0x7fdb88000b00, tables=0x7fdb88011020, check_opt=0x7fdb880054c8, operator_name=0x55e67737939c "check", lock_type=TL_READ_NO_INSERT, open_for_modify=false, repair_table_use_frm=false, extra_open_options=32, prepare_func=0x0, operator_func=(int (handler::*)(handler * const, THD *, HA_CHECK_OPT *)) 0x55e676b56774 <handler::ha_check(THD*, st_ha_check_opt*)>, view_operator_func=0x55e6769d56ef <view_check(THD*, TABLE_LIST*, st_ha_check_opt*)>) at /data/src/10.2/sql/sql_admin.cc:516
 
#16 0x000055e676a3a910 in Sql_cmd_check_table::execute (this=0x7fdb88011638, thd=0x7fdb88000b00) at /data/src/10.2/sql/sql_admin.cc:1338
 
#17 0x000055e6768e9a58 in mysql_execute_command (thd=0x7fdb88000b00) at /data/src/10.2/sql/sql_parse.cc:6207
 
#18 0x000055e6768ee318 in mysql_parse (thd=0x7fdb88000b00, rawbuf=0x7fdb88010f38 "CHECK TABLE `t3`  FOR UPGRADE", length=29, parser_state=0x7fdbf414e250, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:7875
 
#19 0x000055e6768dc3f1 in dispatch_command (command=COM_QUERY, thd=0x7fdb88000b00, packet=0x7fdb88019161 "", packet_length=29, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:1817
 
#20 0x000055e6768dad32 in do_command (thd=0x7fdb88000b00) at /data/src/10.2/sql/sql_parse.cc:1362
 
#21 0x000055e676a263ad in do_handle_one_connection (connect=0x55e6793517b0) at /data/src/10.2/sql/sql_connect.cc:1354
 
#22 0x000055e676a2613a in handle_one_connection (arg=0x55e6793517b0) at /data/src/10.2/sql/sql_connect.cc:1260
 
#23 0x00007fdc0c46e494 in start_thread (arg=0x7fdbf414f700) at pthread_create.c:333
 
#24 0x00007fdc0a5e693f in clone () from /lib/x86_64-linux-gnu/libc.so.6

The last problem with AUTO_INCREMENT is also reproducible with MyISAM..

Generated at Thu Feb 08 08:01:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.