URGENT: [Enterprise 9.0.791] ALL rows updated on a table when only ONE row modified in editor!

[tfrancois]

Hello Devart team,

There is a CRITICAL and devasting issue with the latest version of dbForge that has mentioned in other posts that is NOT being addressed - and this is a MAJOR issue that poses serious threat to the integrity of data for businesses using this product.

On certain tables, when a single row is modified the entire table, meaning ALL ROWS are updated/overwritten with the same update that was meant only for the single row selected. This has MAJOR ramifications for data integrity and security and needs to please be addressed and corrected as soon as possible - respectfully, there should be no more important issue than this on your priority list. This poses serious risk to your entire user base of this product and a fix has to be released to resolve this issue as it already poses risks of liability and other issues.

Prior to this discovery, I have always felt this is an excellent product that has no worthy rival in its class. Please fix this issue IMMEDIATELY for your loyal user base who rely heavily on this software. Luckily, I have backups and restore my data in the meanwhile, but until a fix is released I too must refrain from making any changes with dbForge until this is resolved. Please respond accordingly. Thank you.

Other related posts:
- viewtopic.php?f=23&t=56406&sid=373ebad6 ... eb3abe5da6
- viewtopic.php?f=23&t=56494

[UPDATE] Some helpful information:
It appears the table where I was making changes did have a primary index (row ID) BUT that column is marked as INVISIBLE in my table and I know (and have posted a request on this forum) for dbForge to add support for invisible columns. Not sure if that it is what is causing this data overwriting issue, but its someplace to start for dev team.

[dzhanhira]

Hi,

Could you please provide a specific example of such a case?

We would like to have: a table script and steps with screenshots so that we can reproduce this issue on our side.

[tfrancois]

Hello Support @dzhanhira,

In response to your request for replicating the issue I discovered, I found the right combination that creates the major bug I found.

Here is a create table SQL that is similar in nature:

Code: Select all

CREATE TABLE table1 (
  row_id int UNSIGNED INVISIBLE NOT NULL AUTO_INCREMENT,
  firstname varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  lastname varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  active bit(1) NOT NULL DEFAULT b'1',
  lastmodified datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (row_id, active),
  UNIQUE INDEX UK_table1 (firstname, lastname, row_id)
)
ENGINE = INNODB,
AUTO_INCREMENT = 1,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

Please note that the table has the following characteristics:

• The primary key consists of 2 columns

• The first column in the primary key is set to INVISIBLE (supported in MySQL 8.0+)

• The second column is a BIT column (not sure if this occurs if this was just boolean but shouldn't matter!)

Now, let's make sure the table has some data in it:

Code: Select all

INSERT INTO table1(firstname, lastname, active) VALUES
('John', 'Williams', True),
('Patrick', 'Davis', True),
('Robert', 'Woods', True),
('Albert', 'Einstein', True);

To replicate the issue, open the table for editing rows and edit a single row value in either the first name or last name column. Hit Refresh. You will find that ALL the rows have been updated with the same change you did in the single row!

Please look into this issue - this is a major data integrity risk that I am not sure why its happening.

In the interest of complete transparency, my database is running on MySQL 8.0.23 on AWS Aurora.

Thanks and please advise.

[dzhanhira]

Hi,

Thanks for such a detailed description of this case!

Kindly be informed that we were able to reproduce this bug and we are going to fix it in one of the next releases.

Once there is an update from the investigation or the fixed version is available for download, we will inform you.

[tfrancois]

Hello @dzhanhira and support team,

Respectfully, there have been 2 updates to dbForge since I reported this major issue and after applying both updates, the issue I mentioned here has still not been resolved. This is a major issue that I truly have to believe requires more urgency and supercede all other bug fixes/enhancements, no? I'm disappointed that it appears that it doesn't seem as if its being addressed as quickly as I thought it would be, given the seriousness in the nature of the bug we've identified that clearly risks data integrity. Any timeline on a release for this fix please? I urge, again respectfully, that there should be no higher priority given the damage this bug can do on such a massive scale. Please advise. Thank you.

[dzhanhira]

Dear Customer,

We want to inform you that due to Russian aggression which caused the war against Ukraine the response time to your request may increase. We want all our agents to be safe during this time and make all our efforts to handle all cases as soon as possible, however, unfortunately, some of the aspects of the current situation are beyond our control.

We are sorry that answering/working on your requests takes a lot of time. Please be sure that we are doing our best.

Today I had another round of meeting with our Product team regarding this case, so let me please clarify what we have right now.

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.

By default, we open the Data Editor in Read-Only mode. If the user puts it into edit mode, then in this situation, we show a warning that there is no unique key.

We believe that the developer or administrator had reason to assign the INVISIBLE attribute to the column and cannot include it in the resulting data set (thereby obtaining a full-fledged PRIMARY KEY for the table), and without this, safe operations with data are impossible.

Unfortunately, we cannot completely disable the editing mode, we have already tried to do this, but our users were extremely outraged by this, explaining this approximately as follows - this is our data, and we know whether it is possible to edit it or not.

To be able to edit the data in such a table, you must execute the query SELECT <column_list> FROM <table_name>; By explicitly specifying an invisible column in the <column_list> list, then there will be no problems with editing.

Regarding the WHERE clause, we will try to fix it, but we still believe that editing data when there is no unique key is extremely risky.

With best regards from Ukraine,
Devart team

[tfrancois]

Dear @dzhanhira,

My absolute sincerest apologies for seemingly being so insensitive to the plight of the people in Ukraine, and not knowing or fully realizing how that affected the Devart team and their families.

I want you to know that the vast majority of the American people stand with Ukraine unequivocally and without exception. We as a free people sometimes take fore granted our liberties and freedoms and seem to look the other way when freedom-loving people elsewhere in our world are suffering. Unfortunately, most average citizens are not able to impact the kind of change to bring this pain to an end directly, but at the very very least, we can express our solidarity to our human brethren and pray to our higher power that the tragedies we are witnessing come to an end as soon as possible. I apologize sincerely for having been totally unaware of how this war has been impacting Devart and appreciate all of your hard work and efforts to continue to deliver on your EXCELLENT products - the only one(s) I use - and will continue to use - in your market space. I will continue to hold Ukraine, including more specifically all those at Devart, in my thoughts and prayers and urge you and your families to please try to stay safe and out of harms way as your first and most urgent priority always.

Thank you for your reply and hope we can find a fix that would both enable the ability to set key columns invisible while being able to make changes to data without fear of the issue discussed herein. I'm sure at some point a solution can be found but understand now that there are bigger priorities at this time. Long live Ukraine!

[tfrancois]

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.

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.

[dzhanhira]

Thanks for your reply! We will investigate it and try to fix it, once there is an update, we are going to inform you.

[tfrancois]

Still disappointed to report that with the release of version v.9.1.8, this STILL continues to be an issue. When are we going to resolve this MAJOR issue please Devart?

[dzhanhira]

Could you please clarify which issue is not fixed? The reported one in this post was fixed by the latest version.

[tfrancois]

Thank you for your reply. I can respectfully confirm that issue is still NOT fixed.

I confirmed this by performing the exact same test as in my original post above. I created the table with the SQL provided above. I then populated it with the test records by running the accompanying INSERT statement above.

Then, I performed an edit on one of the last name fields and hit Refresh. Issue APPEARS resolved. Until I perform a second update on a different row, hit Refresh and all the rows are updated with the newly modified last name.



To repeat, it appears to be fixed on first update but same error occurs on a second update in a different row! Thank you.

[dzhanhira]

Hi,

Thanks for your post!

Kindly be informed that we were able to reproduce this bug by your steps and

currently, we are investigating it.

Once there is an update, we will inform you.

[dzhanhira]

Hi All,

We have released a new version with the fix for this issue.

Kindly note that you can download it on our website to upgrade within the tool.

[michabbb]

thanks!!!