MySQL Bugs: #111092: dumpInstance produces empty pre-view SQL if SHOW FIELDS FROM results in error

Bug #111092	dumpInstance produces empty pre-view SQL if SHOW FIELDS FROM results in error
Submitted:	19 May 2023 19:00	Modified:	4 Sep 2023 14:45
Reporter:	Duke Lee	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Shell Dump & Load	Severity:	S3 (Non-critical)
Version:	8.0.33	OS:	Ubuntu (20.04)
Assigned to:		CPU Architecture:	x86

Description:
If a view produces an error when running SHOW FIELDS FROM against it, dumpInstance will create an empty "db@view.pre.sql" file. This can break loadDump in scenarios where a user has a SELECT grant against such a view, potentially catching users off guard. The loadDump process normally:

1) Creates placeholder views
2) Creates users
3) Applies grants
4) Rereates actual views
5) Loads data

but loadDump fails at step 3 due to missing placeholder views since the "pre" file is empty (technically, not empty. It has a DROP TABLE view; statement but no CREATE VIEW placeholder statement.)

The workaround:

mysql --force -u root -p < dump/@.users.sql
mysqlsh -u root -- util loadDump "dump" --loadData=false --loadDdl=true --loadUsers=false --loadIndexes=false --deferTableIndexes=all --skipBinlog=true
mysql -u root -p < dump/@.users.sql
mysqlsh -u root -- util loadDump "dump" --loadData=true --loadDdl=false --loadUsers=false --loadIndexes=true --deferTableIndexes=all --skipBinlog=true --updateGtidSet=replace

1) Creates all users, skipping GRANT errors via --force
2) Loads DDL, where DEFINERs can potentially depend on the existence of certain users
3) Applies grants, which can run successfully now that both users and DDL exist
4) Loads data

How to repeat:
podman run --replace --rm -d --name=mysql -p 3306:3306 -e MYSQL_ALLOW_EMPTY_PASSWORD=true mysql:8.0.33

mysql -h 127.0.0.1 -u root

create database test;
use test;

-- Below taken from https://bugs.mysql.com/bug.php?id=35869 to forcibly invoke SHOW FIELDS FROM error

create table t1 (a int);
insert into t1 (a) values (1), (2), (3);
create table t2 like t1;
insert into t2 (a) values (4), (5), (6);
create view v2 as select max(a) from t1;
create view v1 as select * from v2;
select * from v1;
drop view v2;
create table v2 as select * from t2;

exit

mysqlsh --verbose --no-wizard -h 127.0.0.1 -u root -- util dumpInstance /tmp/dump

cat /tmp/dump/test@v1.pre.sql

Suggested fix:
I'm not sure what mysqlsh should do. In this situation, mysqldump normally fails and exits with:

ERROR 1356 (HY000) at line 1: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

On one hand, it's nice that mysqlsh can still produce a backup in this situation where it's functionally a problem with Mysql server and not mysqlsh, but on the other hand, it's a "silent failure" of sorts.

Maybe the output of dumpInstance should produce WARNING messages? or maybe there should be a flag to indicate whether the user would like the dump process to fail in this situation?

Hi Mr. Lee,

Thank you for your bug report.

However, we do have some additional questions .....

First we need a test case that utilises only our tools. Hence, can you provide a test case without `podman` or other non-MySQL products .....

Second, you do not issue SHOW FIELDS in any of your queries, hence Shell command is not visible.

Thanks in advance .......

Hi,

Thank you for report. Verified as stated. I'm not sure if returning "empty" dump for this view or an error would be a proper solution, let's see what shell team will say. Thanks for reproducible test case.

[msb_8_0_33]$ ~/opt/mysql/8.0.33/bin/mysqlsh  --verbose --no-wizard -h 127.0.0.1 -P 8033 -u msandbox -- util dumpInstance /tmp/dump
verbose: 2023-05-22T15:31:23Z: Loading startup files...
verbose: 2023-05-22T15:31:23Z: Loading plugins...
verbose: 2023-05-22T15:31:23Z: Connecting to MySQL at: mysql://msandbox@127.0.0.1:8033
verbose: 2023-05-22T15:31:23Z: main: tid=11: CONNECTED: 127.0.0.1:8033
verbose: 2023-05-22T15:31:23Z: Now tracking 'sql_mode' system variable.
verbose: 2023-05-22T15:31:23Z: Connecting to MySQL at: mysql://msandbox@127.0.0.1:8033?max-allowed-packet=1073741824&net-read-timeout=86400000
verbose: 2023-05-22T15:31:23Z: util.dumpInstance(): tid=12: CONNECTED: 127.0.0.1:8033
verbose: 2023-05-22T15:31:23Z: Source server: 8.0.33 MySQL Community Server - GPL
Acquiring global read lock
Global read lock acquired
verbose: 2023-05-22T15:31:23Z: Connecting to MySQL at: mysql://msandbox@127.0.0.1:8033?max-allowed-packet=1073741824&net-read-timeout=86400000
Initializing - done
verbose: 2023-05-22T15:31:23Z: Connecting to MySQL at: mysql://msandbox@127.0.0.1:8033?max-allowed-packet=1073741824&net-read-timeout=86400000
verbose: 2023-05-22T15:31:23Z: Connecting to MySQL at: mysql://msandbox@127.0.0.1:8033?max-allowed-packet=1073741824&net-read-timeout=86400000
verbose: 2023-05-22T15:31:23Z: Initializing - done, duration: 0.026747 seconds
verbose: 2023-05-22T15:31:23Z: Connecting to MySQL at: mysql://msandbox@127.0.0.1:8033?max-allowed-packet=1073741824&net-read-timeout=86400000
verbose: 2023-05-22T15:31:23Z: util.dumpInstance(): tid=15: CONNECTED: 127.0.0.1:8033
verbose: 2023-05-22T15:31:23Z: util.dumpInstance(): tid=16: CONNECTED: 127.0.0.1:8033
verbose: 2023-05-22T15:31:23Z: util.dumpInstance(): tid=14: CONNECTED: 127.0.0.1:8033
verbose: 2023-05-22T15:31:23Z: util.dumpInstance(): tid=13: CONNECTED: 127.0.0.1:8033
1 out of 5 schemas will be dumped and within them 3 tables, 1 view.
13 out of 16 users will be dumped.
Gathering information - done
verbose: 2023-05-22T15:31:23Z: Gathering information - done, duration: 0.060174 seconds
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Writing users DDL
verbose: 2023-05-22T15:31:23Z: [Worker003]: Writing metadata for schema `test`
verbose: 2023-05-22T15:31:23Z: [Worker001]: Writing DDL for schema `test`
verbose: 2023-05-22T15:31:23Z: [Worker000]: Writing DDL for view `test`.`v1`
verbose: 2023-05-22T15:31:23Z: [Worker002]: Writing DDL for table `test`.`v2`
verbose: 2023-05-22T15:31:23Z: [Worker003]: Writing DDL for table `test`.`t1`
verbose: 2023-05-22T15:31:23Z: [Worker001]: Writing DDL for table `test`.`t2`
verbose: 2023-05-22T15:31:23Z: Preparing data dump for table `test`.`v2`
verbose: 2023-05-22T15:31:23Z: Could not select columns to be used as an index for table `test`.`v2`. Data will be dumped to multiple files by a single thread.
verbose: 2023-05-22T15:31:23Z: Preparing data dump for table `test`.`t1`
verbose: 2023-05-22T15:31:23Z: Could not select columns to be used as an index for table `test`.`t1`. Data will be dumped to multiple files by a single thread.
verbose: 2023-05-22T15:31:23Z: Preparing data dump for table `test`.`t2`
verbose: 2023-05-22T15:31:23Z: Could not select columns to be used as an index for table `test`.`t2`. Data will be dumped to multiple files by a single thread.
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
verbose: 2023-05-22T15:31:23Z: [Worker001]: Writing metadata for table `test`.`v2`
verbose: 2023-05-22T15:31:23Z: [Worker001]: Writing metadata for table `test`.`t1`
verbose: 2023-05-22T15:31:23Z: [Worker003]: Writing metadata for table `test`.`t2`
verbose: 2023-05-22T15:31:23Z: [Worker003]: Chunking `test`.`v2`, rows: 3, average row length: 5461, rows per chunk: 11719
verbose: 2023-05-22T15:31:23Z: [Worker003]: Table `test`.`v2` does not have a valid index, number of chunks is estimated
verbose: 2023-05-22T15:31:23Z: [Worker003]: Data dump for table `test`.`v2` will be written to 1 file
verbose: 2023-05-22T15:31:23Z: [Worker003]: Chunking `test`.`t1`, rows: 3, average row length: 5461, rows per chunk: 11719
verbose: 2023-05-22T15:31:23Z: [Worker003]: Table `test`.`t1` does not have a valid index, number of chunks is estimated
verbose: 2023-05-22T15:31:23Z: [Worker003]: Data dump for table `test`.`t1` will be written to 1 file
verbose: 2023-05-22T15:31:23Z: [Worker003]: Chunking `test`.`t2`, rows: 3, average row length: 5461, rows per chunk: 11719
verbose: 2023-05-22T15:31:23Z: [Worker003]: Table `test`.`t2` does not have a valid index, number of chunks is estimated
verbose: 2023-05-22T15:31:23Z: [Worker003]: Data dump for table `test`.`t2` will be written to 1 file
verbose: 2023-05-22T15:31:23Z: [Worker001]: Dump of `test`.`t1` (whole table split in chunks) into 'test@t1' took 0.001383 seconds, written 3 rows (6 bytes), longest row has 2 bytes
verbose: 2023-05-22T15:31:23Z: [Worker001]: Dump of `test`.`t2` (whole table split in chunks) into 'test@t2' took 0.000719 seconds, written 3 rows (6 bytes), longest row has 2 bytes
verbose: 2023-05-22T15:31:23Z: [Worker003]: Dump of `test`.`v2` (whole table split in chunks) into 'test@v2' took 0.004730 seconds, written 3 rows (6 bytes), longest row has 2 bytes
verbose: 2023-05-22T15:31:24Z: Writing schema metadata - done, duration: 0.250137 seconds
Writing schema metadata - done
verbose: 2023-05-22T15:31:24Z: Writing DDL - done, duration: 0.249799 seconds
Writing DDL - done
verbose: 2023-05-22T15:31:24Z: Writing table metadata - done, duration: 0.249116 seconds
Writing table metadata - done
Starting data dump
100% (9 rows / ~9 rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
verbose: 2023-05-22T15:31:24Z: Dumping data - done, duration: 0.010286 seconds
Dump duration: 00:00:00s
Total duration: 00:00:00s
Schemas dumped: 1
Tables dumped: 3
Uncompressed data size: 18 bytes
Compressed data size: 72 bytes
Compression ratio: 0.2
Rows written: 9
Bytes written: 72 bytes
Average uncompressed throughput: 18.00 B/s
Average compressed throughput: 72.00 B/s
[msb_8_0_33]$ cat /tmp/dump/
@.done.json             test.json               test@t1@@1.tsv.zst      test@t2@0.tsv.zst       test@t2.json            test@v2@0.tsv.zst       test@v2.json
@.json                  test.sql                test@t1@@1.tsv.zst.idx  test@t2@0.tsv.zst.idx   test@t2.sql             test@v2@0.tsv.zst.idx   test@v2.sql
@.post.sql              test@t1@0.tsv.zst       test@t1.json            test@t2@@1.tsv.zst      test@v1.pre.sql         test@v2@@1.tsv.zst      @.users.sql
@.sql                   test@t1@0.tsv.zst.idx   test@t1.sql             test@t2@@1.tsv.zst.idx  test@v1.sql             test@v2@@1.tsv.zst.idx
[msb_8_0_33]$ cat /tmp/dump/test@v1.pre.sql
-- MySQLShell dump 2.0.1  Distrib Ver 8.0.33 for Linux on x86_64 - for MySQL 8.0.33 (MySQL Community Server (GPL)), for Linux (x86_64)
--
-- Host: 127.0.0.1    Database: test    Table: v1
-- ------------------------------------------------------
-- Server version       8.0.33

--
-- Temporary view structure for view `v1`
--

DROP TABLE IF EXISTS `v1`;
[msb_8_0_33]$

Posted by developer:
 
Added the following note to the MySQL Shell 8.2.0 release notes:
	
util.dumpInstance() generated an empty .pre.sql file for invalid views.
As of this release, invalid views are detected and generate an error. 
Such views must be corrected or excluded from the dump using the excludeTables option.