[MDEV-26447] mysqldump creates temporary MyISAM table for each VIEW Created: 2021-08-20  Updated: 2022-08-02  Resolved: 2022-08-02

Status: Closed
Project: MariaDB Server
Component/s: Backup, Scripts & Clients
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
Fix Version/s: 10.3.36, 10.4.26, 10.5.17, 10.6.9, 10.7.5, 10.8.4, 10.9.2, 10.10.1

Type: Bug Priority: Minor
Reporter: Thomas Casteleyn Assignee: Daniel Black
Resolution: Fixed Votes: 0
Labels: beginner-friendly, mysqldump


 Description   

The mysqldump tool included with MariaDB client generates SQL for creating a temporary table with the MyISAM engine.

client/mysqldump.c#L3283-L3292

 
          /*
 
            Stand-in tables are always MyISAM tables as the default
 
            engine might have a column-limit that's lower than the
 
            number of columns in the view, and MyISAM support is
 
            guaranteed to be in the server anyway.
 
          */
 
          fprintf(sql_file,
 
                  "\n) ENGINE=MyISAM */;\n"
 
                  "SET character_set_client = @saved_cs_client;\n");

Github

This is problematic when trying to import this into Azure MariaDB service, since that doesn't support MyISAM.

I have seen that MySQL's mysqldump produces temporary VIEWs with SELECT 1 AS fieldname, that might be a better option to consider as well..

 Comments   
Comment by Daniel Black [ 2022-07-30 ]

Can I please get a review of PR 2214.

Comment by Daniel Black [ 2022-08-01 ]

Aria was the logical change from MyISAM.

Right, Aria isn't sufficient a substitution if its not there on Azure. I haven't found column limits for InnoDB or Memory engine which the comment describes.

SQL_MODE disabling NO_ENGINE_SUBSTITUTION is probably a good change to include too as Aria isn't necessary there.

Comment by Daniel Black [ 2022-08-02 ]

I took the VIEW based approach as suggested.

Thanks for the bug report.

Generated at Thu Feb 08 09:45:23 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.