version: windows x64 mariadb 10.4.7.
Before i do it, mariadb is work well.
Here is what i done:
I try to dump database data, but i get an error.
mysqldump -uxxx -pxxxx agilor_ws > ag.sql
unknown variable 'character_set_client=utf8mb'
So, i try to change
my.ini file, i update many times and restart mariadb many times (start success but mysqldump error), i just change character code, like
utf8, and still get that error message.
Finally i rollback
my.ini and remove
[mysqldump], and it worked. I successfully dump the data.
my.ini finally look like.
[client] plugin-dir=D:/mariadb-10.4.7-winx64/lib/plugin default-character-set=utf8mb4 [mysqld] datadir=D:/mariadb-10.4.7-winx64/data character-set-client-handshake = false character_set_server = utf8mb4 character_set_filesystem = binary character_set_client = utf8mb4 collation_server = utf8mb4_unicode_ci init_connect='SET NAMES utf8mb4' [mysqldump] # character_set_client=utf8mb [mysql] default-character-set=utf8mb4
And now, the real problem was happend. When i type
select * from control_point, i get an error
table control_point doesn t exist in engine. The table is in database
building_control, it has nothing to do with the database
agilor_ws i just exported.
I follow this tutorial to try restore the
control_point table use
I use method #1 in that tutorial.
First, i install mariadb in an new machine.
create database test1; use test1 CREATE TABLE IF NOT EXISTS `test1`.`control_point` ( `id` CHAR(32) NOT NULL, `path` VARCHAR(200) NULL, `control_path` VARCHAR(200) NULL, `title` VARCHAR(20) NULL, `enabled` TINYINT(1) NULL, `control_point_template_id` CHAR(32) NOT NULL, `device_id` CHAR(32) NOT NULL, `connector_id` CHAR(32) NOT NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB;
ALTER TABLE control_point DISCARD TABLESPACE;
And the file
control_point.ibd is removed when i execute the command.
I copy the
control_point.ibd file from server into
ALTER TABLE control_point IMPORT TABLESPACE;
When i execute this command, i get an error
ERROR 2013 (HY000): Lost connection to MySQL server during query.
And mariadb server is down.
Here is the mysql log.
2020-06-10 18:29:35 8 [Note] InnoDB: Sync to disk 2020-06-10 18:29:35 8 [Note] InnoDB: Sync to disk - done! 2020-06-10 18:29:35 8 [Warning] InnoDB: Table `test1`.`control_point` should have 1 indexes but the tablespace has 5 indexes 2020-06-10 18:29:35 8 [Note] InnoDB: Phase I - Update all pages 2020-06-10 18:29:35 8 [Note] InnoDB: Sync to disk 2020-06-10 18:29:35 8 [Note] InnoDB: Sync to disk - done! 2020-06-10 18:29:35 8 [Note] InnoDB: Phase III - Flush changes to disk 2020-06-10 18:29:35 8 [Note] InnoDB: Phase IV - Flush complete 2020-06-10 18:29:35 8 [ERROR] InnoDB: Table `test1`.`control_point` is missing instant ALTER metadata 200610 18:29:35 [ERROR] mysqld got exception 0xc0000005 ; 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.7-MariaDB key_buffer_size=134217728 read_buffer_size=131072 max_used_connections=3 max_threads=65537 thread_count=7 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 136383 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x237d8a82528 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... mysqld.exe!ha_innobase::discard_or_import_tablespace()[ha_innodb.cc:12867] mysqld.exe!mysql_discard_or_import_tablespace()[sql_table.cc:5945] mysqld.exe!Sql_cmd_discard_import_tablespace::execute()[sql_alter.cc:543] mysqld.exe!mysql_execute_command()[sql_parse.cc:6108] mysqld.exe!mysql_parse()[sql_parse.cc:7913] mysqld.exe!dispatch_command()[sql_parse.cc:1845] mysqld.exe!do_command()[sql_parse.cc:1359] mysqld.exe!threadpool_process_request()[threadpool_common.cc:359] mysqld.exe!tp_callback()[threadpool_common.cc:186] ntdll.dll!TpReleaseWait() ntdll.dll!RtlInitializeResource() KERNEL32.DLL!BaseThreadInitThunk() ntdll.dll!RtlUserThreadStart() Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x237d8a8f810): ALTER TABLE control_point IMPORT TABLESPACE Connection ID (thread ID): 8 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 at D:\tools\MariaDB 10.4\data\
Is there other way to recover
control_point table data from
User contributions licensed under CC BY-SA 3.0