Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Possible data loss in source database while exporting FEDERATED table data #433

Closed
fifonik opened this Issue Dec 7, 2018 · 5 comments

Comments

Projects
None yet
2 participants
@fifonik
Copy link

fifonik commented Dec 7, 2018

Steps to reproduce this issue

  1. Create databases, user & tables using the following code:
DROP USER IF EXISTS '_user'@'%';
CREATE USER '_user'@'%' IDENTIFIED BY '_password';
GRANT USAGE, SELECT, INSERT, UPDATE, DELETE ON *.* TO '_user'@'%';
FLUSH PRIVILEGES;


DROP DATABASE IF EXISTS `_data`;
CREATE DATABASE `_data`;

USE `_data`;
CREATE TABLE `a` (`id` INT UNSIGNED NOT NULL);
INSERT INTO `a` (`id`) VALUES (1), (2), (3), (4), (5);


DROP DATABASE IF EXISTS `_source`;
CREATE DATABASE `_source`;
USE `_source`;
CREATE TABLE `a` (`id` INT UNSIGNED NOT NULL) ENGINE=FEDERATED CONNECTION='mysql://_user:_password@127.0.0.1/_data/a';

CREATE TABLE `b` (`id` INT UNSIGNED NOT NULL);
INSERT INTO `b` (`id`) VALUES (6), (7), (8), (9), (10);


DROP DATABASE IF EXISTS `_target`;
CREATE DATABASE `_target`;

  1. Check content of tables _data.a, _source.a, _source.b;
  2. Right click over database "_source" | "Export database as SQL"
  3. Specify the following options: Tables: "Create", Data: "Delete + insert", Output: "Database", Database: "_target";
  4. Export;
  5. Check content of tables _data.a, _source.a, _source.b, _target.a, _target.b => data in table _data.a is lost;

Current behavior

Federated table _target.a created correctly.
However, then DELETE FROM _target.b is issued and this deletes all data in _data.a.

Expected behavior

HeidiSQL must not touch data while exporting federated tables (mysqldump did not try do dump data).
Federated table is just a "link" so creating federated table in target database will automatically make data in that table available.

Environment

  • HeidiSQL version: 9.5.0.5372 64 bit
  • Database system and version: MySQL, MariaDB (with federated tables supported and enabled)

@fifonik fifonik changed the title Possible data loss through federated tables while exporting data Possible data loss in source database while exporting data Dec 7, 2018

@fifonik

This comment has been minimized.

Copy link
Author

fifonik commented Mar 14, 2019

Is it possible to get it fixed as:

  • I believe it is critical as data loss possible during operation that should be safe
  • The fix is straightforward, something like this:
        Output('-- '+_('Data exporting was unselected.')+CRLF, False, True, True, False, False);
+   end else if DBObj.Engine = 'FEDERATED' then begin
+     if menuExportAddComments.Checked then
+        Output('-- '+f_('Table data not exported because this is %s table which holds its data in linked table.', [DBObj.Engine])+CRLF, False, True, True, False, False);
    end else if DBObj.Engine = 'MRG_MYISAM' then begin

Or even like this:

-   end else if DBObj.Engine = 'MRG_MYISAM' then begin
+   end else if (DBObj.Engine = 'MRG_MYISAM' or DBObj.Engine = 'FEDERATED') then begin

Thanks.

@ansgarbecker ansgarbecker added this to the v10.2 milestone Mar 14, 2019

@ansgarbecker ansgarbecker changed the title Possible data loss in source database while exporting data Possible data loss in source database while exporting FEDERATED table data Mar 14, 2019

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

ansgarbecker commented Mar 14, 2019

Thanks for your code suggestion.

Should be fixed in the next build.

Also, MRG_MyISAM tables were affected, caused by a case sensitive comparison with "MRG_MYISAM" (capitalized "Y").

@fifonik

This comment has been minimized.

Copy link
Author

fifonik commented Mar 14, 2019

Thanks!
I have not checked the code and though that the engine's name is normalized (==uppercased).

@fifonik

This comment has been minimized.

Copy link
Author

fifonik commented Mar 14, 2019

Downloaded. Checked -- no data dumped.
Many thanks.

The only tiny suggestion -- add newline after the "-- Table data not exported because this is ..." comment to separate it visually from the following command.

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

ansgarbecker commented Mar 15, 2019

Just added that line break to both "skip-comments".

Thanks for your quick feedback!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.