MySQL Bugs: #100102: Innodb table import is a huge burden and suffers from a range of critical bugs

Bug #100102	Innodb table import is a huge burden and suffers from a range of critical bugs
Submitted:	3 Jul 2020 12:53	Modified:	26 Aug 2022 11:49
Reporter:	John K	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.19, mysql vanilla and percona	OS:	Linux
Assigned to:		CPU Architecture:	x86 (same issue on 3 different independend servers, 2 of them AWS 1 is a dedicated server)
Tags:	blobs, compression, IMPORT TABLESPACE, innodo, segfault

Description:
I've a mix of feature request and multi-bug report, given the nature of the issues I can not offer reproduction.

1) Feature request
2) Bug report on IMPORT TABLESPACE #1 (critical error)
3) Bug report on IMPORT TABLESPACE #2 (critical error)
My guess is that Bug #2 and Bug #3 are related to each other, one time causing mysql to crash silently internally one time causing the full segfault.

1) Feature request:
Please make a non import INNODB table transfer possible.
Optimally the IBD file should contain everything needed to reconstruct the table (including the create table syntax as meta header).
Suboptimally the meta data is generated on "FLUSH FOR EXPORT"
The import must work without processing the entire table, that's so important for the future of MySql and would have saved my company from months of frustration over the past years.

1.1) Reasons:
a) Backup, transfer and reconstruction of tables is one of the most essential features required for a professional database environment. 
Even without the current (critical) bugs on IMPORT TABLESPACE, that's a horribly slow procedure.
My company resorts now to having each database in an own docker container so the data is transportable but this way no joins or selects can be made between the databases as they run on distinct servers.
b) Restoration after a fatal crash or data loss
Currently users have to know the exact create table syntax, especially after a catastrophic error that can be a huge burden.
In addition they need a CFG file (which won't be available)
In addition they need to perform a needless complex routine (create table, discard empty tablespace, import tablespace)
The current process is very slow, it's faster than SQL inserts, faster than load data infile but still can take 10 hours on a table with 500+GB 
It could take a few seconds given that all the data is available in the IBD file already.
c) For simplicity, a database should be carefree to transfer, backup, reconstruct.
MyISAM one time was like this: copy table into folder -> use it 
Innodb is a great engine but this disadvantage is a huge issue for professional use.

2) Bug report related to IMPORT TABLESPACE #1
I've had dozens of segmentation faults on 3 different servers all during IMPORT TABLESPACE.
I even recreated the entire table before transfer to ensure it's without binary errors.
I was never able to track the rows that cause the segfault but it's likely related to table compression and the content of blobs (jpeg data stored)
The only solution after weeks of trying various methods was to uncompress the table.
Segfaults look always like this:
21:22:26 UTC - mysqld got signal 11 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x7fb5141003c0
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 = 7fb6c8189d80 thread_stack 0x46000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x2e) [0x563594cbe80e]
/usr/sbin/mysqld(handle_fatal_signal+0x351) [0x563593dd9641]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x110e0) [0x7fb6dda050e0]
/usr/sbin/mysqld(lob::z_index_page_t::get_n_index_entries() const+0x8) [0x56359514c0f8]
/usr/sbin/mysqld(lob::z_index_page_t::import(unsigned long)+0x18) [0x56359514c628]
/usr/sbin/mysqld(PageConverter::update_page(buf_block_t*, unsigned long&)+0x3e1) [0x563594ea3051]
/usr/sbin/mysqld(PageConverter::operator()(unsigned long, buf_block_t*)+0x322) [0x563594ea36a2]
/usr/sbin/mysqld(fil_tablespace_iterate(dict_table_t*, unsigned long, PageCallback&)+0x9ef) [0x5635950cb27f]
/usr/sbin/mysqld(row_import_for_mysql(dict_table_t*, dd::Table*, row_prebuilt_t*)+0xdc6) [0x563594ea4ad6]
/usr/sbin/mysqld(ha_innobase::discard_or_import_tablespace(bool, dd::Table*)+0x422) [0x563594d7a742]
/usr/sbin/mysqld(Sql_cmd_discard_import_tablespace::mysql_discard_or_import_tablespace(THD*, TABLE_LIST*)+0x1bc) [0x563593d237cc]
/usr/sbin/mysqld(mysql_execute_command(THD*, bool)+0x2645) [0x563593cb2b15]
/usr/sbin/mysqld(mysql_parse(THD*, Parser_state*)+0x360) [0x563593cb5e70]
/usr/sbin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x1e93) [0x563593cb8203]
/usr/sbin/mysqld(do_command(THD*)+0x168) [0x563593cb8eb8]
/usr/sbin/mysqld(+0xfcc9c8) [0x563593dca9c8]
/usr/sbin/mysqld(+0x23fdeb5) [0x5635951fbeb5]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x74a4) [0x7fb6dd9fb4a4]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f) [0x7fb6dba86d0f]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fb5142d1bc8): ALTER TABLE `framework`.`statistic_core_preparing_5` IMPORT TABLESPACE
Connection ID (thread ID): 9
Status: NOT_KILLED

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.

---------------

The IBD file itself is flawless, it was working fine on the exact same version of mysql. But the import is impossible.
Sadly I can't provide it due to NDA reasons, though the stacktrace probably is enough for a core developer to know what was going on.

2) Bug report related to IMPORT TABLESPACE #1

This bug is again on a compressed table, this time uncompressing it was no solution as it's too large.
The IBD file originally came from another server, then it was imported by 8.0.19 successfully.
Then the imported IBD file was exported again and copied to a second server on the same machine.
This time it will always cause mysql to totally hang.
After a while processing IMPORT TABLESPACE disk IO and CPU go to zero, I've waited 90,000 seconds and 60,000 seconds.
The time to process the file was usually within 2-3 hours.
The only solution here is to give up on import tablespace, we now copy the entire database folder instead of the table and open it in a docker. 
This also means no joins can be performed anymore as we now need 1 more database server running instead of just copying one table over.

SHOW PROCESSLIST:
56      tool    localhost       framework       Killed  87820   System lock     ALTER TABLE `framework`.`p_core` IMPORT TABLESPACE

FROM ENGINE INNODB STATUS:
(see file attached)

How to repeat:
I can not provide the data needed.
I did try to find the rows responsible for both bugs but the first table contains 30 million and the second one 600 million rows.

The segfault backtrace provided should be enough for a developer of the compression / blob related data to look what was going on.

Suggested fix:
1) The source of the segfault on compressed table imports needs to be found
2) the current procedure to import tables is not sufficient for professional servers. It's way too slow on large tables, too complex (sql syntax, cfg files, complex discard and import procedures) and any sort of error during import will render the reconstruction impossible like in my cases.

I recommend to develop an IN FILE copy of the table, all you should need to do is to
SOURCE: "FLUSH TABLE FOR EXPORT"
DESTINTION: "LOAD TABLE AS 'new_name' FROM EXPORT"

The loading not process the entire tablespace if no version conversions have to be done, it shouldn't rewrite any IDs or clustered meta data inside the IBD. 
It should just be able to open the table as if it was natively created.

the "mysql hangs during import tablespace" status

Attachment: hangs.txt (text/plain), 10.52 KiB.

Hi Mr. K,

Thank you for your bug report.

First of all, we accept bug reports on our binaries only, not on the binaries from other sources.

Next, feature request is already implemented in the latest 8.0 release. In order to do things correctly, EXPORT should be done properly and .cfg file preserved, together with a tablespace. This is all very well described in our Reference Manual.

Regarding the bugs, we must inform you that we are not providing support for any container product, so we do not process feature request and bug reports involving any type of container.

If you have a bug with our latest binary (8.0.21) where you followed the manual to the last step and you encounter a bug, then we shall be very happy to process. But, that report should contain a full test case, so that we just run it and see the bug popping up.

Interim, we can not accept your report as a bug report.

I have this bug on a regular mysql 8.0.19 debian release as well. 
As I wrote in the version "mysql vanilla" on a dedicated server..

In the end it's up to you if you want to fix a critical crash in your server, I have the impression it seems like I am asking a favor.
You software has a critical segmentation fault in compressed blob handling, if that's not enough to warrant a developer correct it I wonder what is.

Also the Mysql 8 handling of import tablespace is exactly what I was talking about in my feature request.

In the end I do not get the impression you care about quality, so I am out. 
You can close the bugs if you don't want to correct them.

Hi Mr. K,

For the case that you have described on a dedicated machine, can you send us a fully repeatable test case. This should include a proper EXPORT with cfg file and a proper IMPORT.

If we manage to repeat the behaviour, we shall be happy to verify this report as a bug.

Yes, my SQL bug is identified for the long-range for all people. The service of the bug for the ability to https://www.dissertation-service.org/economics-dissertation/ for the funds. The termism evaluated for the behest of the goals. SQL bug is removed for threats and all paths for the teamwork.

Thanks for sharing this article. http://www.google.com

HI All,

This is a forum for the reporting bugs. Report of the bug should include the fully repeatable test case. That is because we can not fix a bug, unless we are able to repeat it. When we repeat the behaviour , we can then search for the code that causes it and try to fix it.

A simple statement that there is a bug means nothing. Source code of our server is huge and we can't search for the needle in a haystack.

Hi Ms. Reilly,

We can not accept series of URLs as a report of the bug. This is a forum for reporting bugs in MySQL server and related products and not a forum for presenting discussions in other forums.

To make things much more clear to you, what we need from you is to send us a fully repeatable test case. This should include a proper EXPORT with cfg file and a proper IMPORT.

We will then use release 8.0.30 to check the behaviour. We will NOT use ancient and unsupported releases, like 8.0.19.

If we manage to repeat the behaviour, we shall be happy to verify this report as a bug.

Beside repeatable test cases, we can also accept a fully fledged and very detailed source code analysis depicting a bug very clearly.

Unsupported.