Hello again @dzhanhira and Support Team,
Based on your information below, I did some digging and would like to offer some guidance or suggestion on how to resolve this issue in dbForge.
dzhanhira wrote: ↑Thu 19 May 2022 10:03
When executing the Retrieve Data command, we send a SELECT * FROM <table_name> query to the server;
You have assigned the INVISIBLE attribute to one of the columns. This means that this column (with data) will not appear in the list of columns in the result of a SELECT * FROM <table_name> query. In this case, the INVISIBLE column is part of the PRIMARY KEY. As a result, the returned data set does not contain KEY columns that allow you to safely perform data editing operations.
Here is a stored procedure that runs and retrieves all data from a table, including invisible columns. The caveat to this method is that it requires the use of a prepared statement in order to access the information_schema table directly - so I am not sure if that is something the IDE can do when user selects to view table data:
Code: Select all
CREATE PROCEDURE `get_data`(IN v_table VARCHAR(255))
READS SQL DATA
BEGIN
DECLARE v_query VARCHAR(5000);
SET SESSION group_concat_max_len = 1024000;
SELECT CONCAT('SELECT ', GROUP_CONCAT(distinct c.COLUMN_NAME ORDER BY c.ORDINAL_POSITION) , ' FROM ',v_table, ' LIMIT 1000;') INTO v_query from information_schema.`COLUMNS` c where c.table_name = v_table and table_schema=DATABASE();
SET @sql = v_query;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
FYI, notice the limit 1000 I added to mimick the default behavior of the IDE today. The thought being additional SP parameters could be passed in to modify the LIMIT clause as needed.
The beauty of this approach is that the column visibility does not affect the output. Now, to determine which columns are marked as INVISIBLE by the table author, you can check the EXTRA column value in the information_schema table (as shown below) and perhaps do something visual in the IDE to let the user know which columns are invisible as opposed to not.
From MySQL documentation:
https://dev.mysql.com/doc/refman/8.0/en ... n-metadata
Code: Select all
mysql> SELECT TABLE_NAME, COLUMN_NAME, EXTRA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';
+------------+-------------+-----------+
| TABLE_NAME | COLUMN_NAME | EXTRA |
+------------+-------------+-----------+
| t1 | i | |
| t1 | j | |
| t1 | k | INVISIBLE |
+------------+-------------+-----------+
I'm hoping a mixture of these techniques would allow the developers to add support for Invisible columns (albeit it appears with some work) in a future release soon. As there is no way of knowing who would be making use of this important new feature in MySQL 8, I'd assume the sooner the better for safety reasons. Thank you again as always.