Uploaded image for project: 'MariaDB MaxScale'
  1. MariaDB MaxScale
  2. MXS-4143

Able to export columns data with table structure for only those selected columns like sqlYog

    XMLWordPrintable

Details

    • New Feature
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • 24.02.0
    • maxgui
    • None
    • MXS-SPRINT-194, MXS-SPRINT-195, MXS-SPRINT-196, MXS-SPRINT-197, MXS-SPRINT-199

    Description

      Actual Table in the Database server:

      CREATE TABLE `slow_log` (
      `start_time` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6),
      `user_host` mediumtext NOT NULL,
      `query_time` time(6) NOT NULL,
      `lock_time` time(6) NOT NULL,
      `rows_sent` int(11) NOT NULL,
      `rows_examined` int(11) NOT NULL,
      `db` varchar(512) NOT NULL,
      `last_insert_id` int(11) NOT NULL,
      `insert_id` int(11) NOT NULL,
      `server_id` int(10) unsigned NOT NULL,
      `sql_text` mediumtext NOT NULL,
      `thread_id` bigint(21) unsigned NOT NULL,
      `rows_affected` int(11) NOT NULL
      ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log';

      If we write a select query from the SQL editor for the above table like below.

      SELECT sql_text, COUNT(SUBSTR(sql_text,1,60)) AS execution_count, user_host, AVG(query_time),MIN(query_time),MAX(query_time), rows_sent, rows_examined FROM mysql.slow_log WHERE db='test_db'
      AND user_host LIKE '%mbs%' GROUP BY SUBSTR(sql_text,1,60) ORDER BY 2;

      Then we should be able to export the sql file with the customized table structure and data..

      After running and exporting the above query then if we open the .sql file like below. Below is the custom table structure and data.

      create table `slow_log` (
      `sql_text` text ,
      `execution_count` bigint (21),
      `user_host` text ,
      `avg(query_time)` Decimal (19),
      `min(query_time)` time ,
      `max(query_time)` time ,
      `rows_sent` int (11),
      `rows_examined` int (11)
      );
      insert into `slow_log` (`sql_text`, `execution_count`, `user_host`, `avg(query_time)`, `min(query_time)`, `max(query_time)`, `rows_sent`, `rows_examined`) values(<Values are removed...>);

      We should be able to export the .sql file with the below options with the select/deselect custom columns,.
      1. Structure Only
      2. Data Only
      3. Structure and Data

      Attaching the screenshot for your reference.

      Attachments

        Issue Links

          Activity

            People

              thien.ly Duong Thien Ly
              naresh.chandra@copart.com Naresh Chandra
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.