[MDEV-26447] mysqldump creates temporary MyISAM table for each VIEW - Jira

Thomas Casteleyn created issue - 2021-08-20 09:39

Thomas Casteleyn made changes - 2021-08-20 09:40

Field	Original Value	New Value
Description	The mysqldump tool included with MariaDB client generates SQL for creating a temporary table with the MyISAM engine.	The mysqldump tool included with MariaDB client generates SQL for creating a temporary table with the MyISAM engine. https://github.com/MariaDB/server/blob/3bf42eb21bb8744b12b2b5ddb4f87425e328c36f/client/mysqldump.c#L3283-L3292

Thomas Casteleyn made changes - 2021-08-20 09:42

Description

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

https://github.com/MariaDB/server/blob/3bf42eb21bb8744b12b2b5ddb4f87425e328c36f/client/mysqldump.c#L3283-L3292

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

[client/mysqldump.c#L3283-L3292|https://github.com/MariaDB/server/blob/3bf42eb21bb8744b12b2b5ddb4f87425e328c36f/client/mysqldump.c#L3283-L3292]
{code:c}
          /*
            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");
{code}

Thomas Casteleyn made changes - 2021-08-20 09:47

Description

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

[client/mysqldump.c#L3283-L3292|https://github.com/MariaDB/server/blob/3bf42eb21bb8744b12b2b5ddb4f87425e328c36f/client/mysqldump.c#L3283-L3292]
{code:c}
          /*
            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");
{code}

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

{code:c|title=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");
{code}
[Github|https://github.com/MariaDB/server/blob/3bf42eb21bb8744b12b2b5ddb4f87425e328c36f/client/mysqldump.c#L3283-L3292]

This is problematic when trying to import this into Azure MariaDB service, since that [doesn't support MyISAM|https://docs.microsoft.com/en-us/azure/mysql/concepts-limits].

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..

Thomas Casteleyn made changes - 2021-08-20 14:39

Description

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

{code:c|title=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");
{code}
[Github|https://github.com/MariaDB/server/blob/3bf42eb21bb8744b12b2b5ddb4f87425e328c36f/client/mysqldump.c#L3283-L3292]

This is problematic when trying to import this into Azure MariaDB service, since that [doesn't support MyISAM|https://docs.microsoft.com/en-us/azure/mysql/concepts-limits].

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..

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

{code:c|title=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");
{code}
[Github|https://github.com/MariaDB/server/blob/3bf42eb21bb8744b12b2b5ddb4f87425e328c36f/client/mysqldump.c#L3283-L3292]

This is problematic when trying to import this into Azure MariaDB service, since that [doesn't support MyISAM|https://docs.microsoft.com/en-us/azure/mariadb/concepts-limits].

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..

Thomas Casteleyn made changes - 2021-08-23 10:20

Labels

mysqldump

Sergei Golubchik made changes - 2021-08-24 20:03

Affects Version/s		10.2 [ 14601 ]
Affects Version/s		10.3 [ 22126 ]
Affects Version/s		10.4 [ 22408 ]
Affects Version/s		10.5 [ 23123 ]
Affects Version/s		10.6 [ 24028 ]

Sergei Golubchik made changes - 2021-08-24 20:03

Fix Version/s		10.2 [ 14601 ]
Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.4 [ 22408 ]
Fix Version/s		10.5 [ 23123 ]
Fix Version/s		10.6 [ 24028 ]

Sergei Golubchik made changes - 2021-08-24 20:03

Labels

mysqldump

beginner-friendly mysqldump

Sergei Golubchik made changes - 2021-08-24 20:03

Component/s		Scripts & Clients [ 11002 ]
Component/s	Backup [ 13902 ]

Thomas Casteleyn made changes - 2021-09-09 13:26

Component/s

Backup [ 13902 ]

Thomas Casteleyn made changes - 2021-09-09 13:35

Affects Version/s

10.7 [ 24805 ]

Sergei Golubchik made changes - 2021-12-06 21:35

Workflow

MariaDB v3 [ 124485 ]

MariaDB v4 [ 143106 ]

Thomas Casteleyn made changes - 2022-02-14 09:34

Affects Version/s

10.8 [ 26121 ]

Thomas Casteleyn made changes - 2022-02-14 09:35

Affects Version/s

10.9 [ 26905 ]

Daniel Black made changes - 2022-07-30 03:31

Assignee

Daniel Black [ danblack ]

Daniel Black made changes - 2022-07-30 03:32

Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.3 [ 22126 ]

Daniel Black made changes - 2022-07-30 03:32

Status

Open [ 1 ]

In Progress [ 3 ]

Daniel Black made changes - 2022-07-30 03:32

Assignee	Daniel Black [ danblack ]	Vicențiu Ciorbaru [ cvicentiu ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Daniel Black made changes - 2022-07-31 05:01

Assignee

Vicențiu Ciorbaru [ cvicentiu ]

Anel Husakovic [ anel ]

Daniel Black made changes - 2022-08-02 16:51

Fix Version/s		10.3.36 [ 27513 ]
Fix Version/s		10.4.26 [ 27511 ]
Fix Version/s		10.5.17 [ 27509 ]
Fix Version/s		10.6.9 [ 27507 ]
Fix Version/s		10.7.5 [ 27505 ]
Fix Version/s		10.8.4 [ 27503 ]
Fix Version/s		10.9.2 [ 27115 ]
Fix Version/s		10.10.1 [ 27913 ]
Fix Version/s	10.4 [ 22408 ]
Fix Version/s	10.5 [ 23123 ]
Fix Version/s	10.6 [ 24028 ]
Assignee	Anel Husakovic [ anel ]	Daniel Black [ danblack ]
Resolution		Fixed [ 1 ]
Status	In Review [ 10002 ]	Closed [ 6 ]

MariaDB Server

mysqldump creates temporary MyISAM table for each VIEW

Details

Description

Attachments

Activity

People

Dates

Git Integration