Querying mysql_fdw tables results in truncated results

[jstnlvns]

Looking for a little advice or help on how to debug an issue I'm encountering. I am using PostgreSQL (v11) and foreign data wrappers for data warehousing for >70 databases covering Oracle, MySQL, and PostgreSQL. Over the past couple weeks, I've been having trouble with one of my MySQL schemas. Basically the setup is, I am using mysql_fdw to get the raw tables into PSQL, then I have materialized views off the foreign tables so I can run indexes and control access a bit easier. The materialized view isn't really doing anything other than selecting all the rows then renaming/normalizing the field to snake case. Using the account mapped to the foreign tables, I can load the foreign table get all the results or run a count on all the rows and get the correct number of expected rows, however, whenever I query or create the materialized view it runs without error, but not all the results are in the view. Some of these tables are pretty large >700,000 rows and ~300 columns, mostly text.

Reviewing the PSQL logs, I'm not really getting any errors except on the occasion that it's not able to mysql server, and in this case, I just execute the query again and it runs.

MySQL is crashing though:

021-01-28T13:53:03.531740Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.23'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.
13:59:35 UTC - mysqld got signal 11 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x7f73ac000f80
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 = 7f7490064d10 thread_stack 0x46000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x560c40cb149d]
/usr/sbin/mysqld(handle_fatal_signal+0x2fb) [0x560c3fc5b22b]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x12980) [0x7f74cebbc980]
/usr/sbin/mysqld(handler::ha_rnd_next(unsigned char*)+0x196) [0x560c3fd65e46]
/usr/sbin/mysqld(Materialized_cursor::fetch(unsigned long)+0x87) [0x560c3facd467]
/usr/sbin/mysqld(mysqld_stmt_fetch(THD*, Prepared_statement*, unsigned long)+0x129) [0x560c3fb4d069]
/usr/sbin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x1102) [0x560c3fb25322]
/usr/sbin/mysqld(do_command(THD*)+0x1c4) [0x560c3fb26fe4]
/usr/sbin/mysqld(+0x1077020) [0x560c3fc4c020]
/usr/sbin/mysqld(+0x2640091) [0x560c41215091]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76db) [0x7f74cebb16db]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f) [0x7f74ccda371f]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f73ac027248): is an invalid pointer
Connection ID (thread ID): 34
Status: NOT_KILLED

Some of the things I've tried:

• creating the materialized view with NO DATA - this creates the empty view, but when I run REFRESH WITH DATA, it's truncating
• upped the tcp_keepalives
• set the mysql_fdw.wait_timeout and mysql_fdw.interactive_timeout
• created a smaller version of the materialized view WITH DATA with only about 40 fields, this runs fine

I realize I'm getting a crash on the MySQL side but the fact that I'm able to load the foreign table without it crashing is strange to me. When PSQL is sending the query over to create the materialized view, it seems like something is happening here but I can't figure it out. Any help would be much appreciated.

[surajkharage19]

Thank you for reporting this issue.

I have tried to reproduce the issue at my end by inserting some sample data in text column and created materialized views on foreign table but could able to retrieve all the data. Would it be possible for you to provide the smallest test case with some sample data, so that we can reproduce and investigate it further?

Since you have mentioned that the tables have mostly text columns and the result set is truncating, I remember one known issue in that area where column data length > MAXDATALEN (which is 65536) then we get truncated data. Refer to a similar issue #205 for more details. I suspect that some of the columns have larger data than MAXDATALEN. You tried with the first 40 columns which just runs fine, so there are chances that the remaining columns may have larger data. Can you please check that once?