[MXS-4143] Able to export columns data with table structure for only those selected columns like sqlYog Created: 2022-05-20  Updated: 2024-01-11  Resolved: 2024-01-11

Status: Closed
Project: MariaDB MaxScale
Component/s: maxgui
Affects Version/s: None
Fix Version/s: 24.02.0

Type: New Feature Priority: Major
Reporter: Naresh Chandra Assignee: Duong Thien Ly
Resolution: Fixed Votes: 0
Labels: None

Attachments: PNG File image-2022-05-20-14-49-17-867.png    
Issue Links:
Blocks
is blocked by MXS-4883 Extend /sql/:id/queries to return res... Closed
Sub-Tasks:
Key
Summary
Type
Status
Assignee
MXS-4876 Split query Sub-Task Closed Duong Thien Ly  
MXS-4877 Add `SQL` option for the exporting in... Sub-Task Closed Duong Thien Ly  
MXS-4878 Add fields selection for the exportin... Sub-Task Closed Duong Thien Ly  
MXS-4883 Extend /sql/:id/queries to return res... Sub-Task Closed markus makela  
Sprint: 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.


Generated at Thu Feb 08 04:26:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.