Resolve mysqldump Backup error: couldn ' t execute ' SHOW fields from Unknown error 1356

Server environment:

1. [Email protected] mysql]# cat/etc/redhat-release
   CentOS Linux release 7.3.1611 (Core)
   [Email protected] mysql]# uname-a
   Linux localhost.localdomain 3.10.0-514.21.1.el7.x86_64 #1 SMP Thu may 17:04:51 UTC x86_64 x86_64 x86_64 gnu/linux
   [[email protected] mysql]# CentOS Linux release 7.3.1611 (Core)
   
   
   

MySQL version number:

Mysql> select version ();
+------------+
| Version () |
+------------+
| 5.7.13-log |
+------------+
1 row in Set (0.00 sec)

Error specific situation:

1. [Email protected] tmp]# mysqldump-uroot-p--default-character-set=utf8--triggers-r--hex-blob--flush-logs--events --single-transaction--master-data=2--all-databases|gzip >/tmp/134-20170831.sql.gz
   Enter Password:
   Mysqldump:couldn ' t execute ' SHOW fields from ' v_product_info ': Unknown error 1356 (1356)

Before, because the database is mainly used for testing, the amount of data is not large, so the direct use of mysqldump backup, there is no problem. Today according to the error message, is ' v_product_info ' this view out of the question;

Here's how to troubleshoot the problem:

2. Mysql> SELECT * FROM information_schema.tables where table_name like '%v_product_info% ' and table_type= ' view ' \g;
   
   mysql> use POSEIDON;
   
   mysql> SHOW CREATE VIEW v_product_info\g;

3. The following is the view content:
   CREATE algorithm = UNDEFINED definer = ' dev_oheimdall_user ' @ '% ' SQL SECURITY definer VIEW ' V_product_info ' as SELECT
   ' A '. ' ID ' as ' id ',
   ' A '. ' Product_Name ' as ' product_name ',
   ' A '. ' Feechannel_name ' as ' feechannel_name ',
   ' d '. ' Branch ' as ' branch ',
   ' A '. ' Product_city ' as ' product_city ',
   ' A '. ' Product_type ' as ' product_type ',
   ' A '. ' Mortgage_lend_terms ' as ' mortgage_lend_terms ',
   ' C '. ' Sparehouse_require ' as ' sparehouse_require ',
   ' A '. ' Lend_time_start ' as ' Lend_time_start ',
   ' A '. ' Lend_time_end ' as ' lend_time_end ',
   ' A '. ' Month_rate_start ' as ' Month_rate_start ',
   ' A '. ' Month_rate_end ' as ' month_rate_end ',
   ' A '. ' Repayment_mode ' as ' Repayment_mode ',
   ' A '. ' Gmt_create ' as ' gmt_create ',
   ' A '. ' Status_name ' as ' status_name ',
   ' A '. ' Creater ' as ' creater ',
   (
   Case
   When (
   ' A '. ' id ' = ' b '. ' Product_basic_id '
   ) Then
   ' B '. ' Credit '
   When (
   ' A '. ' id ' = ' c '. ' product_basic_id '
   ) Then
   ' C '. ' Credit_require '
   END
   As ' credit '
   From
   (
   (
   (
   ' Product_basic ' a '
   Left joins ' Product_credit_admit ' B ' on (
   (
   ' A '. ' id ' = ' b '. ' Product_basic_id '
   )
   )
   )
   Left joins ' Product_mortgage_admit ' C ' on (
   (
   ' A '. ' id ' = ' c '. ' product_basic_id '
   )
   )
   )
   Left JOIN ' Fund_channel ' d ' on (
   (
   ' A '. ' feechannel_id ' = ' d '. ' ID '
   )
   )
   )

Solution to the problem:

1. Treatment methods:
   Mysql> DESC Product_basic;
   Error 1146 (42S02): Unknown error 1146
   Mysql> DESC Product_mortgage_admit;
   Error 1146 (42S02): Unknown error 1146
   mysql> DROP VIEW V_product_info;
   Query OK, 0 rows affected (0.10 sec)

Then back up and find that it's going to be done quickly.

This article from "Lotus Pond Jun Tao 51cto Blog" blog, declined reprint!

Resolve mysqldump Backup error: couldn ' t execute ' SHOW fields from Unknown error 1356